#32673: ProgrammingError resulting from invalid SQL for nested models.Q
instances
-------------------------------------+-------------------------------------
Reporter: Charles Lirsac | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Charles Lirsac:
Old description:
> 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.
>
> 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).
New description:
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).
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32673#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.184ce9a77b819382944bee3f38f53653%40djangoproject.com.
