Opened 7 years ago

Last modified 7 years ago

#30203 closed Bug

Union with group by don't generate correct Subquery — at Initial Version

Reported by: Nikolas Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: union, group by
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (0)

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