id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34231	Invalid RawSQL expression on Model.validate_constraints	Manuel Raynaud	nobody	"Since django 4.1, contrainst check are made during model validation. 

If we are using a CheckConstraint and the check is using a RawSQL() expression, the generated SQL is invalid. The FROM clause is missing in the generated SQL leading to non existing columns errors.

In commit 667105877e6723c6985399803a364848891513cc a check is also added (models.W045) explaining that RawSQL() expression are not validated during full_clean. Reading this I understand that constraints using RawSQL() expression should be ignored and it is not the case.

Here an example to reproduce:

{{{#!python
class Person(models.Model):
    first_name = models.CharField(
        max_length=30,
        blank=True,
        null=True,
    )
    last_name = models.CharField(
        max_length=30,
        blank=True,
        null=True,
    )

    class Meta:
        db_table = ""person""
        constraints = [
            models.CheckConstraint(
                name=""check"",
                check=models.expressions.RawSQL(
                    ""(first_name IS NULL) = (last_name IS NULL)"",
                    {},
                    models.fields.BooleanField(),
                ),
            ),
        ]

person = Person()
person.full_clean()
}}}

Here the full_clean will generate this error:

{{{
Got a database error calling check() on <Q: (AND: RawSQL((first_name IS NULL) = (last_name IS NULL), {}))>: column ""first_name"" does not exist
LINE 1: SELECT 1 AS ""_check"" WHERE COALESCE((((first_name IS NULL) =...
}}}

And the generated SQL query is:

{{{
SELECT 1 AS ""_check"" WHERE COALESCE((((first_name IS NULL) = (last_name IS NULL))), true); args=(1, True); alias=default
}}}


"	Bug	closed	Database layer (models, ORM)	4.1	Normal	invalid	check constraint rawsql		Unreviewed	0	0	0	0	0	0