[Django] #34125: Limitting QuerySet crashes on union() with a single non-empty query

[Django]

#34125: Limitting QuerySet crashes on union() with a single non-empty query
-------------------------------------+-------------------------------------
Reporter: th3hamm0r | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: dev
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 |
-------------------------------------+-------------------------------------
We have an union-query, that works up until django 4.1.2, but stopped
working with the latest dev-version of django.
Basically, one QuerySet is non-empty, and the other one is empty,
simplified:
{{{
q1 = PageLogEntry.objects.all().order_by()
q2 = q1.union(PageLogEntry.objects.none())
q2.order_by()[:1]
}}}

With PostgreSQL this leads to the following SQL (I've reduced the SQL to
one field only -> "..."):
{{{
(SELECT "wagtailcore_pagelogentry"."id", ... FROM
"wagtailcore_pagelogentry" LIMIT 1) LIMIT 1;
}}}

which results in this error:
{{{
psycopg2.errors.SyntaxError: multiple LIMIT clauses not allowed
LINE 1: ..."."page_id" FROM "wagtailcore_pagelogentry" LIMIT 1) LIMIT 1
}}}

With django 4.1.2 the following valid SQL is generated:

{{{
(SELECT "wagtailcore_pagelogentry"."id", ... FROM
"wagtailcore_pagelogentry") LIMIT 1;
}}}

With sqlite no errors are thrown in the dev-version.

Note: In our actual queries, we use ordering (we remove ordering in the
inner queries with `order_by()` and apply an ordering to the resulting
union-queryset), but that does not change anything, since the limit seems
to be the issue, so I've removed ordering to minimize the SQL.

I've found a similar, fixed bug #32116, but in that case order_by had an
issue.

--
Ticket URL: <https://code.djangoproject.com/ticket/34125>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#34125: Limitting QuerySet crashes on union() with a single non-empty query
-------------------------------------+-------------------------------------

Reporter: Stefan Hammer | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: dev
(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
-------------------------------------+-------------------------------------

Changes (by bcail):

* cc: bcail (added)

Comment:

Using the following diff:
{{{
diff --git a/tests/queries/tests.py b/tests/queries/tests.py
index 5163fc5cb1..31651df114 100644
--- a/tests/queries/tests.py
+++ b/tests/queries/tests.py
@@ -3199,6 +3199,11 @@ class UnionTests(unittest.TestCase):
)
self.check_union(ObjectB, Q1, Q2)

+ def test_union_empty_queryset(self):
+ q1 = ObjectA.objects.all().order_by()
+ q2 = q1.union(ObjectA.objects.none())
+ print(q2.order_by()[:1])
+

class DefaultValuesInsertTest(TestCase):
def test_no_extra_params(self):
}}}

git bisect says that 3d734c09ff0138441dfe0a59010435871d17950f is the first
bad commit.

--
Ticket URL: <https://code.djangoproject.com/ticket/34125#comment:1>