Opened 11 months ago
Last modified 10 months ago
#35028 closed Bug
psycopg3: distinct query iteration causes psycopg.errors.InvalidColumnReference: for SELECT DISTINCT, ORDER BY expressions must appear in select list — at Initial Version
Reported by: | Richard Ebeling | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Normal | Keywords: | |
Cc: | Simon Charette, Florian Apolloner, Daniele Varrazzo, Jonathan Weth | 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
Using PostgreSQL, after upgrading from psycopg2 to psycopg3 (psycopg[binary]==3.1.14
), iterating a distinct queryset of a model with an ordering that uses Value
expressions causes psycopg.errors.InvalidColumnReference
error saying that the ORDER BY
expression must appear in the select list.
This is a minimal reproducible example:
### MODELS from django.db import models class TestModel(models.Model): test_field = models.CharField() class Meta: ordering = [NullIf("test_field", Value(""))] ### TEST from name.models import TestModel from django.test import TestCase class TestTestModel(TestCase): def test_iterating(self): for el in TestModel.objects.all().distinct().iterator(): pass
which gives this output when running:
$ ./manage.py test -k test_iterating Found 1 test(s). Creating test database for alias 'default'... System check identified no issues (0 silenced). E ====================================================================== ERROR: test_iterating (name.tests.test_models.TestTestModel) ---------------------------------------------------------------------- Traceback (most recent call last): File "/.../venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params) File "/.../venv/lib/python3.10/site-packages/psycopg/server_cursor.py", line 294, in execute raise ex.with_traceback(None) psycopg.errors.InvalidColumnReference: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ..._field", $1) FROM "evaluation_testmodel" ORDER BY NULLIF("ev... ^ The above exception was the direct cause of the following exception: Traceback (most recent call last): File ".../tests/test_models.py", line 43, in test_iterating for el in TestModel.objects.all().distinct().iterator(): File "/.../venv/lib/python3.10/site-packages/django/db/models/query.py", line 516, in _iterator yield from iterable File "/.../venv/lib/python3.10/site-packages/django/db/models/query.py", line 91, in __iter__ results = compiler.execute_sql( File "/.../venv/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql cursor.execute(sql, params) File "/.../venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( File "/.../venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) File "/.../venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 100, in _execute with self.db.wrap_database_errors: File "/.../venv/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File "/.../venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params) File "/.../venv/lib/python3.10/site-packages/psycopg/server_cursor.py", line 294, in execute raise ex.with_traceback(None) django.db.utils.ProgrammingError: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ..._field", $1) FROM "evaluation_testmodel" ORDER BY NULLIF("ev... ^ ---------------------------------------------------------------------- Ran 1 test in 0.010s FAILED (errors=1) Destroying test database for alias 'default'...
Removing the .distinct()
call prevents the error.
This is the relevant excerpt from the postgres log:
2023-12-11 19:15:15.900 UTC [18994] name@test_name ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list at character 235 2023-12-11 19:15:15.900 UTC [18994] name@test_name STATEMENT: DECLARE "_django_curs_139772156350464_sync_1" NO SCROLL CURSOR FOR SELECT DISTINCT "name_testmodel"."id", "name_testmodel"."test_field", NULLIF("name_testmodel"."test_field", $1) FROM "name_testmodel" ORDER BY NULLIF("name_testmodel"."test_field", $2) ASC
When using psycopg2, this is the resulting postgres log (without errors):
2023-12-11 19:19:32.456 UTC [19038] name@test_name LOG: statement: DECLARE "_django_curs_140476300926976_sync_1" NO SCROLL CURSOR WITHOUT HOLD FOR SELECT DISTINCT "name_testmodel"."id", "name_testmodel"."test_field", NULLIF("name_testmodel"."test_field", '') FROM "name_testmodel" ORDER BY NULLIF("name_testmodel"."test_field", '') ASC 2023-12-11 19:19:32.456 UTC [19038] name@test_name LOG: statement: FETCH FORWARD 2000 FROM "_django_curs_140476300926976_sync_1" 2023-12-11 19:19:32.456 UTC [19038] name@test_name LOG: statement: CLOSE "_django_curs_140476300926976_sync_1"