#32673 closed Bug (fixed)
Nested Q() objects raises ProgrammingError on PostgreSQL and Oracle.
Reported by: | Charles Lirsac | Owned by: | Simon Charette |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.0 |
Severity: | Normal | Keywords: | postgresql oracle q |
Cc: | Simon Charette | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
Given the following model:
class Price(models.Model): price = models.IntegerField(null=False) price_previous = models.IntegerField(null=False) on_sale = models.BooleanField(null=False)
The following query used to work in 2.2. but emits a ProgrammingError
on 3.0+ against a Postgres database:
Price.objects.filter( models.Q( on_sale=models.ExpressionWrapper( models.Q(price__lt=models.F('price_previous')), output_field=models.BooleanField(), ) ) )
Traceback:
Traceback (most recent call last): File "<console>", line 5, in <module> File ".../lib/python3.7/site-packages/django/db/models/query.py", line 256, in __repr__ data = list(self[:REPR_OUTPUT_SIZE + 1]) File ".../lib/python3.7/site-packages/django/db/models/query.py", line 280, in __iter__ self._fetch_all() File ".../lib/python3.7/site-packages/django/db/models/query.py", line 1324, in _fetch_all self._result_cache = list(self._iterable_class(self)) File ".../lib/python3.7/site-packages/django/db/models/query.py", line 51, in __iter__ results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size) File ".../lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1169, in execute_sql cursor.execute(sql, params) File ".../lib/python3.7/site-packages/django/db/backends/utils.py", line 98, in execute return super().execute(sql, params) File ".../lib/python3.7/site-packages/django/db/backends/utils.py", line 66, in execute return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) File ".../lib/python3.7/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers return executor(sql, params, many, context) File ".../lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) File ".../lib/python3.7/site-packages/django/db/utils.py", line 90, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File ".../lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: syntax error at or near "<" LINE 1: ..."prices_price"."on_sale" = "prices_price"."price" < "prices_...
The error comes from the way the query ended up compiled, in 3.2 we get:
SELECT "prices_price"."id", "prices_price"."price", "prices_price"."price_previous", "prices_price"."on_sale" FROM "prices_price" WHERE "prices_price"."on_sale" = "prices_price"."price" < "prices_price"."price_previous"
The right hand side of the clause is not wrapped in parentheses. While on 2.2 we get:
SELECT "prices_price"."id", "prices_price"."price", "prices_price"."price_previous", "prices_price"."on_sale" FROM "prices_price" WHERE "prices_price"."on_sale" = ("prices_price"."price" < ("prices_price"."price_previous"))
The right hand side is correctly wrapped (even if some parts are over wrapped).
Sorry if the title is not super accurate, I wasn't sure how to exactly to describe the exact kind of models.Q invocation at play here.
Versions:
- Python 3.7
- Tested agains postgres 9.6, 11 and 13
- Tested against 3.0.14, 3.1.8 and 3.2. It works on 2.2.20
For reference. the following query (valid in 3.0+) is equivalent and generates the same broken SQL:
Price.objects.filter( models.Q( on_sale=models.Q(price__lt=models.F('price_previous')) ) )
I've logged more details and a complete reproduction at https://github.com/lirsacc/django-check-constraint-pg-regression (in the context of a CheckConstraint
and migrations which is where we first saw this).
Change History (7)
comment:1 by , 3 years ago
Description: | modified (diff) |
---|
comment:2 by , 3 years ago
Cc: | added |
---|---|
Keywords: | postgresql oracle q added |
Summary: | ProgrammingError resulting from invalid SQL for nested models.Q instances → Nested Q() objects raises ProgrammingError on PostgreSQL and Oracle. |
Triage Stage: | Unreviewed → Accepted |
comment:3 by , 3 years ago
Has patch: | set |
---|
comment:4 by , 3 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Triage Stage: | Accepted → Ready for checkin |
Thanks for the report.
Regression in 3a505c70e7b228bf1212c067a8f38271ca86ce09.
Reproduced at 6d0cbe42c3d382e5393d4af48185c546bb0ada1f.