[Django] #33905: Validation of check constraints involving Postgres' lower()/upper() functions produce invalid sql

[Django]

#33905: Validation of check constraints involving Postgres' lower()/upper()
functions produce invalid sql
-----------------------------------------+------------------------
Reporter: David Sanders | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: dev
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
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 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?)

--
Ticket URL: <https://code.djangoproject.com/ticket/33905>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#33905: Validation of check constraints involving Postgres' lower()/upper()
functions produce invalid sql

-------------------------------+--------------------------------------

Reporter: David Sanders | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: dev

Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------+--------------------------------------
Changes (by David Sanders):

* has_patch: 0 => 1

Comment:

PR: https://github.com/django/django/pull/15931

--
Ticket URL: <https://code.djangoproject.com/ticket/33905#comment:1>

[Django]

#33905: Validation of check constraints involving Postgres' lower()/upper()
functions produce invalid sql
-------------------------------+--------------------------------------
Reporter: David Sanders | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------------------------

Description changed by David Sanders:

Old description:

New description:

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?)

--

--
Ticket URL: <https://code.djangoproject.com/ticket/33905#comment:2>