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

[Stefan Hammer]

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.

[bcail]

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.

[Mariusz Felisiak]

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.

[Mariusz Felisiak]

Regression test.

[Simon Charette]

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.

[GitHub <noreply@…>]

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.

[Sarah Boyce <42296566+sarahboyce@…>]

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.