#35028: psycopg3: distinct query iteration causes
psycopg.errors.InvalidColumnReference: for SELECT DISTINCT, ORDER BY
expressions must appear in select list
-------------------------------------+-------------------------------------
Reporter: Richard Ebeling | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 5.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 Richard Ebeling:
Old 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"
> }}}
New 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 "name_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 "name_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#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/0107018c5a5ce4c3-f6236a7a-0775-40c7-8ee5-933066c55b40-000000%40eu-central-1.amazonses.com.
