Q objects to match an empty string in CheckConstraint results in invalid SQL using MySQL

[Phil Gyford]

Using a constraint like that below results in a migration that generates invalid SQL when using a MariaDB (v10.3) database. I think it's OK using SQLite, but I haven't tried Postgresql.

from django.db import models
from django.db.models import Q

class Person(models.Model):
    firstname = models.CharField(max_length=50, blank=True, null=False, default="")

    class Meta:
        constraints = [
            models.CheckConstraint(
                name="%(app_label)s_%(class)s_firstname",
                check=(Q(firstname__exact="")),
            )
        ]

Running manage.py makemigrations results in a migration with this operation:

constraint=models.CheckConstraint(
    check=models.Q(("firstname__exact", "")),
    name="myapp_person_firstname",
),

And running manage.py sqlmigrate myapp 0002 shows this (note there should be a pair of empty quotes between = and ) at the end):

ALTER TABLE `Person` ADD CONSTRAINT `myapp_person_firstname` CHECK (`firstname` = );

Unsurprisingly, running the migration results in:

django.db.utils.ProgrammingError: (1064, "1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1", '42000')

[Mariusz Felisiak]

Thanks for this ticket, however it works for me with MariaDB, MySQL, and other built-in backends (I checked with Django 3.0, 3.1, 3.2, 4.0, 4.1, and on the current main branch)

--
-- Create constraint sample_othermodel_firstname on model othermodel
--
ALTER TABLE `sample_othermodel` ADD CONSTRAINT `sample_othermodel_firstname` CHECK (`firstname` = '');

[Phil Gyford]

Fair enough!

Very strange. I’ve tried several variations and always had the same error, but I’ve only tried it with this one MariaDB-using project, and one other that used SQLite (that didn’t have the error).