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
32673	ProgrammingError resulting from invalid SQL for nested models.Q instances	Charles Lirsac	nobody	"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).

"	Bug	new	Database layer (models, ORM)	3.0	Normal				Unreviewed	0	0	0	0	0	0