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
33905	Validation of check constraints against ranges involving PostgreSQL's lower()/upper() functions produces invalid SQL.	David Sanders	David Sanders	"Given a model using the Postgres field IntegerRangeField:


{{{
class Sample(models.Model):
    value_range = IntegerRangeField(null=True, blank=True)

    class Meta:
        constraints = [
            CheckConstraint(
                name=""positive_value_range"",
                check=Q(value_range__startswith__gte=0),
            ),
        ]
}}}

validation of the check constraint produces a DatabaseError:

{{{
sample = Sample()
sample.validate_constraints()

Got a database error calling check() on <Q: (AND: (AND: ('value_range__startswith__gte', 0)))>: operator does not exist: text >= integer
LINE 1: SELECT 1 AS ""_check"" WHERE lower(NULL) >= 0
                                               ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
}}}

This is because Postgres' lower() and upper() functions are overloaded to accept either text or range types - the former for converting case with the latter retrieving the lower/upper bounds of the range.

When an expression LOWER(NULL) is encountered without an underlying column Postgres assumes the resulting expression is text:

{{{
postgres=# select UPPER(NULL);
 upper
-------
 NULL!
(1 row)

postgres=# \gdesc
 Column | Type
--------+------
 upper  | text
(1 row)
}}}

I doubt this is a serious error though because upon inspecting the new constraint validation code any DatabaseError is caught and a warning is logged but it would be nice to have a functioning check for this.

I have written a test & small patch which pretty much just adapts some existing operand casting code for Postgres (I haven't signed the CLA yet though because I'm not sure if someone else might want to dry it up a bit with a common mixin?)"	Bug	closed	contrib.postgres	4.1	Release blocker	fixed		Gagaro	Ready for checkin	1	0	0	0	0	0