id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
37097	Nested double-unioned query for model with ordering fails on Postgres/MySql	Shai Berger	Jacob Walls	"Yep. Sorry about the title, that's the most succinct I can make it.

Add the following test in {{{django/tests/queries/test_qs_combinators.py}}}, say after {{{test_union_in_with_ordering_and_slice}}}

{{{#!python
    def test_double_union_in_with_ordering(self):
        qs1 = Author.objects.filter(num__gt=7)
        qs2 = Author.objects.filter(num__lt=2)
        qs3 = Author.objects.filter(num=5)
        self.assertQuerySetEqual(
            # Query formatted to allow easy commenting-out of parts
            Author.objects.exclude(id__in=qs1
                                          .union(qs2, all=True)
                                          .union(qs3, all=True)
                                          .values(""id"")),
            []
        )
}}}

On Postgres, this passes on 6.0, but fails on the main branch (towards 6.1 as I write this). The error is 
{{{
 django.db.utils.ProgrammingError: each UNION query must have the same number of columns
LINE 1: ...) ORDER BY ""__orderbycol2"" ASC) UNION ALL (SELECT ""U0"".""id"" ...
 }}}
because the query (formatted for some readability) is 
{{{#!sql
SELECT ""queries_author"".""id"", ""queries_author"".""name"", ""queries_author"".""num"", ""queries_author"".""extra_id"" 
FROM ""queries_author"" 
WHERE NOT (
  ""queries_author"".""id"" IN (
    (
      (
        SELECT ""U0"".""id"" AS ""id"", ""U0"".""name"" AS ""__orderbycol2"" 
        FROM ""queries_author"" ""U0"" 
        WHERE ""U0"".""num"" > 7
        ORDER BY ""U0"".""name"" ASC
      ) UNION ALL (
        SELECT ""U0"".""id"" AS ""id"", ""U0"".""name"" AS ""__orderbycol2""
        FROM ""queries_author"" ""U0""
        WHERE ""U0"".""num"" < 2
        ORDER BY ""U0"".""name"" ASC
      ) 
      ORDER BY ""__orderbycol2"" ASC           -- I think this is the culprit
    ) UNION ALL (
      SELECT ""U0"".""id"" AS ""id""
      FROM ""queries_author"" ""U0""
      WHERE ""U0"".""num"" = 5
      ORDER BY ""U0"".""name"" ASC
    )
  )
) 
ORDER BY ""queries_author"".""name"" ASC
}}}

Notes:
- Double union is required, a single union works
- This happens when the ordering is defined in the model Meta, but not if you just add ordering on the queries. I suspect this is because having the ordering in the Meta makes the query equivalent to
{{{#!python
Author.objects.exclude(id__in=qs1
                              .union(qs2, all=True)
                              .order_by(""name"")    # This is implied
                              .union(qs3, all=True)
                              .values(""id""))
}}}
- On Oracle and Sqlite, the inner order is removed by the fix of #36938. I haven't actually tested it on MySql, but I believe it has the same relevant feature flags and so should behave the same.
- The test as written fails on Sqlite on 6.0, with {{{django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements}}} -- similar tests are skipped there; but it passes on Postgres, and passes on Sqlite on main."	Bug	assigned	Database layer (models, ORM)	dev	Release blocker			Shai Berger Simon Charette	Accepted	1	0	0	0	0	0