Changes between Initial Version and Version 2 of Ticket #30203


Ignore:
Timestamp:
Feb 24, 2019, 9:40:24 AM (6 years ago)
Author:
Nikolas
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #30203

    • Property Resolution → needsinfo
    • Property Status new → closed

  • Ticket #30203 – Description

    initial v2  
    1 if dont use union - all fine, generated SQL:
     1Simplified for example:
     2have simple query
    23
    34
    45{{{
    5 SELECT "service_transaction"."phone_number_id",
    6        "rate"."service_id",
    7        COUNT("rate"."service_id") AS "rate__service_id__count"
    8 FROM "service_transaction"
    9 INNER JOIN "phone_number" ON ("service_transaction"."phone_number_id" = "phone_number"."id")
    10 INNER JOIN "rate" ON ("service_transaction"."rate_id" = "rate"."id")
    11 WHERE ("phone_number"."deactivated" IS NULL
    12        AND ("service_transaction"."sms_last_id" IS NOT NULL
    13             OR "service_transaction"."deactivated" > (STATEMENT_TIMESTAMP()))
    14        AND "rate"."country_id" = 2)
    15 GROUP BY "service_transaction"."phone_number_id",
    16          "rate"."service_id"
     6    qs1: QuerySet = User.objects.filter(id__gt=10)
     7
     8    qs1 = qs1.values('is_active').annotate(id=Count('pk'))
     9}}}
     10
     11generate:
     12
     13{{{
     14SELECT "suser_customuser"."is_active",
     15       COUNT("suser_customuser"."id") AS "id"
     16FROM "suser_customuser"
     17WHERE "suser_customuser"."id" > 10
     18GROUP BY "suser_customuser"."is_active"
     19}}}
     20
     21all correct, now i wont use union
     22
     23{{{
     24    qs1: QuerySet = User.objects.filter(id__gt=10)
     25    qs2: QuerySet = User.objects.filter(id__lt=10)
     26
     27    qs1 = qs1.values('is_active').annotate(id=Count('pk'))
     28
     29}}}
     30
     31now get not correct SQL:
     32
     33{{{
     34  (SELECT "suser_customuser"."is_active",
     35          "suser_customuser"."id"
     36   FROM "suser_customuser"
     37   WHERE "suser_customuser"."id" > 10)
     38UNION
     39  (SELECT "suser_customuser"."is_active",
     40          "suser_customuser"."id"
     41   FROM "suser_customuser"
     42   WHERE "suser_customuser"."id" < 10)
    1743}}}
    1844
    1945
    20 but if before use union get:
    21 
     46expected:
    2247
    2348{{{
    24   (SELECT "service_transaction"."phone_number_id",
    25           "rate"."service_id"
    26    FROM "service_transaction"
    27    INNER JOIN "phone_number" ON ("service_transaction"."phone_number_id" = "phone_number"."id")
    28    INNER JOIN "rate" ON ("service_transaction"."rate_id" = "rate"."id")
    29    WHERE ("phone_number"."deactivated" IS NULL
    30           AND ("service_transaction"."sms_last_id" IS NOT NULL
    31                OR "service_transaction"."deactivated" > (STATEMENT_TIMESTAMP()))
    32           AND "rate"."country_id" = 2))
     49select *
     50from
     51  ((SELECT "suser_customuser"."is_active",
     52          "suser_customuser"."id"
     53   FROM "suser_customuser"
     54   WHERE "suser_customuser"."id" > 10)
    3355UNION
    34   (SELECT "banned_phone_number"."phone_number_id",
    35           "banned_phone_number"."service_id"
    36    FROM "banned_phone_number"
    37    INNER JOIN "phone_number" ON ("banned_phone_number"."phone_number_id" = "phone_number"."id")
    38    WHERE ("phone_number"."deactivated" IS NULL
    39           AND "banned_phone_number"."user_id" = 4))
     56  (SELECT "suser_customuser"."is_active",
     57          "suser_customuser"."id"
     58   FROM "suser_customuser"
     59   WHERE "suser_customuser"."id" < 10)) U0
     60group by is_active, id
    4061}}}
    4162
    4263
    43 my code very simple:
    44 
    45 {{{
    46         active_service_transaction = active_service_transaction.union(activate_banned_phone_numbers) # all fine if dont use union
    47 .
    48 .
    49 .
    50         active_service_transaction = active_service_transaction.annotate(Count('rate__service_id'))
    51 }}}
    52 
    53 i think this is bug, if not bug must generate exception
    54 
    55 th, sorry my poor English
     64i think this is bug, if not why not show any warning or exception?
     65or just say how rewrite query for correct use Subquery
Back to Top