Opened 2 years ago

Closed 2 years ago

Last modified 4 months ago

#34125 closed Bug (fixed)

Limiting QuerySet crashes on union() with a single non-empty query

Reported by: Stefan Hammer Owned by: Simon Charette
Component: Database layer (models, ORM) Version: dev
Severity: Release blocker Keywords:
Cc: bcail, Simon Charette Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (1)

test-34125.diff (765 bytes ) - added by Mariusz Felisiak 2 years ago.
Regression test.

Download all attachments as: .zip

Change History (9)

comment:1 by bcail, 2 years ago

Cc: bcail added

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.

comment:2 by Tim Graham, 2 years ago

Summary: Limitting QuerySet crashes on union() with a single non-empty queryLimiting QuerySet crashes on union() with a single non-empty query

comment:3 by Mariusz Felisiak, 2 years ago

Cc: Simon Charette added
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

Thanks for the report! I attached a regression test which generates the following SQL on PostgreSQL:

  (SELECT "queries_number"."id",
          "queries_number"."num",
          "queries_number"."other_num",
          "queries_number"."another_num"
   FROM "queries_number"
   WHERE "queries_number"."num" <= 0
   LIMIT 1)
LIMIT 1;

Regression in 3d734c09ff0138441dfe0a59010435871d17950f.
Reproduced at de2c2127b66e77a034c01c81753c5c08e651a5b4.

Last edited 2 years ago by Mariusz Felisiak (previous) (diff)

comment:4 by Simon Charette, 2 years ago

Owner: changed from nobody to Simon Charette
Status: newassigned

by Mariusz Felisiak, 2 years ago

Attachment: test-34125.diff added

Regression test.

comment:5 by Simon Charette, 2 years ago

Has patch: set

comment:6 by Simon Charette, 2 years ago

The patch in its current form should address the reported issue and also cover other use cases that were not covered when dealing with slicing combined queries that are sliced themselves.

comment:7 by GitHub <noreply@…>, 2 years ago

Resolution: fixed
Status: assignedclosed

In c2cc807:

Fixed #34125 -- Fixed sliced QuerySet.union() crash on a single non-empty queryset.

The bug existed since sliced query union was added but was elevated to
query union slices by moving the .exists() optimization to the compiler
in 3d734c09ff0138441dfe0a59010435871d17950f.

Thanks Stefan Hammer for the report.

comment:8 by Sarah Boyce <42296566+sarahboyce@…>, 4 months ago

In 9cb8baa0:

Fixed #35559 -- Avoided unnecessary query on sliced union of empty queries.

While refs #34125 focused on the SQL correctness of slicing of union of
potentially empty queries it missed an optimization opportunity to avoid
performing a query at all when all queries are empty.

Thanks Lucidiot for the report.

Note: See TracTickets for help on using tickets.
Back to Top