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	"if dont use union - all fine, generated SQL:


{{{
SELECT ""service_transaction"".""phone_number_id"",
       ""rate"".""service_id"",
       COUNT(""rate"".""service_id"") AS ""rate__service_id__count""
FROM ""service_transaction""
INNER JOIN ""phone_number"" ON (""service_transaction"".""phone_number_id"" = ""phone_number"".""id"")
INNER JOIN ""rate"" ON (""service_transaction"".""rate_id"" = ""rate"".""id"")
WHERE (""phone_number"".""deactivated"" IS NULL
       AND (""service_transaction"".""sms_last_id"" IS NOT NULL
            OR ""service_transaction"".""deactivated"" > (STATEMENT_TIMESTAMP()))
       AND ""rate"".""country_id"" = 2)
GROUP BY ""service_transaction"".""phone_number_id"",
         ""rate"".""service_id""
}}}


but if before use union get:


{{{
  (SELECT ""service_transaction"".""phone_number_id"",
          ""rate"".""service_id""
   FROM ""service_transaction""
   INNER JOIN ""phone_number"" ON (""service_transaction"".""phone_number_id"" = ""phone_number"".""id"")
   INNER JOIN ""rate"" ON (""service_transaction"".""rate_id"" = ""rate"".""id"")
   WHERE (""phone_number"".""deactivated"" IS NULL
          AND (""service_transaction"".""sms_last_id"" IS NOT NULL
               OR ""service_transaction"".""deactivated"" > (STATEMENT_TIMESTAMP()))
          AND ""rate"".""country_id"" = 2))
UNION
  (SELECT ""banned_phone_number"".""phone_number_id"",
          ""banned_phone_number"".""service_id""
   FROM ""banned_phone_number""
   INNER JOIN ""phone_number"" ON (""banned_phone_number"".""phone_number_id"" = ""phone_number"".""id"")
   WHERE (""phone_number"".""deactivated"" IS NULL
          AND ""banned_phone_number"".""user_id"" = 4))
}}}


my code very simple:

{{{
        active_service_transaction = active_service_transaction.union(activate_banned_phone_numbers) # all fine if dont use union
.
.
.
        active_service_transaction = active_service_transaction.annotate(Count('rate__service_id'))
}}}

i think this is bug, if not bug must generate exception

th, sorry my poor English"	Bug	new	Database layer (models, ORM)	1.11	Normal		union, group by		Unreviewed	0	0	0	0	0	0