#35028: psycopg3: distinct query iteration causes
psycopg.errors.InvalidColumnReference: for SELECT DISTINCT, ORDER BY
expressions must appear in select list
-------------------------------------+-------------------------------------
Reporter: Richard | Owner: nobody
Ebeling |
Type: Bug | Status: new
Component: Database | Version: 5.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35028>
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/0107018c5a5a8308-b74657b3-5465-453c-86b2-1cd6eaee9a19-000000%40eu-central-1.amazonses.com.
