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
30203	Union with group by don't generate correct Subquery	Nikolas	nobody	"Simplified for example:
have simple query


{{{
    qs1: QuerySet = User.objects.filter(id__gt=10)

    qs1 = qs1.values('is_active').annotate(id=Count('pk'))
}}}

generate:

{{{
SELECT ""suser_customuser"".""is_active"",
       COUNT(""suser_customuser"".""id"") AS ""id""
FROM ""suser_customuser""
WHERE ""suser_customuser"".""id"" > 10
GROUP BY ""suser_customuser"".""is_active""
}}}

all correct, now i wont use union

{{{
    qs1: QuerySet = User.objects.filter(id__gt=10)
    qs2: QuerySet = User.objects.filter(id__lt=10)

    qs1 = qs1.values('is_active').annotate(id=Count('pk'))

}}}

now get not correct SQL:

{{{
  (SELECT ""suser_customuser"".""is_active"",
          ""suser_customuser"".""id""
   FROM ""suser_customuser""
   WHERE ""suser_customuser"".""id"" > 10)
UNION
  (SELECT ""suser_customuser"".""is_active"",
          ""suser_customuser"".""id""
   FROM ""suser_customuser""
   WHERE ""suser_customuser"".""id"" < 10)
}}}


expected:

{{{
select *
from
  ((SELECT ""suser_customuser"".""is_active"",
          ""suser_customuser"".""id""
   FROM ""suser_customuser""
   WHERE ""suser_customuser"".""id"" > 10)
UNION
  (SELECT ""suser_customuser"".""is_active"",
          ""suser_customuser"".""id""
   FROM ""suser_customuser""
   WHERE ""suser_customuser"".""id"" < 10)) U0
group by is_active, id
}}}


i think this is bug, if not why not show any warning or exception?
or just say how rewrite query for correct use Subquery"	Bug	closed	Database layer (models, ORM)	1.11	Normal	needsinfo	union, group by		Unreviewed	0	0	0	0	0	0