Changes between Initial Version and Version 5 of Ticket #32478


Ignore:
Timestamp:
Feb 23, 2021, 12:04:59 PM (5 years ago)
Author:
Igor Pejic
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #32478

    • Property Component Uncategorized → Database layer (models, ORM)
    • Property Cc Igor Pejic added
    • Property Type Uncategorized → Bug
    • Property Owner changed from nobody to Simon Charette
    • Property Triage Stage Unreviewed → Accepted
    • Property Status new → assigned
    • Property Summary Double OuterRef in Subquery with Case broken in 3.X → Queryset annotation mixing aggregate and subquery doesn't GROUP BY outer column references.

  • Ticket #32478 – Description

    initial v5  
    7777
    7878
     79Full query 2.2:
     80
     81{{{
     82SELECT "aggregation_regress_publisher"."id",
     83       "aggregation_regress_publisher"."name",
     84       "aggregation_regress_publisher"."num_awards",
     85       "aggregation_regress_publisher"."country_id",
     86       CASE
     87           WHEN "aggregation_regress_publisher"."num_awards" >= 2 THEN
     88                  (SELECT V0."id"
     89                   FROM "aggregation_regress_book" V0
     90                   WHERE V0."id" IN
     91                       (SELECT U0."id"
     92                        FROM "aggregation_regress_book" U0
     93                        WHERE U0."name" = ("aggregation_regress_country"."name")
     94                        ORDER BY U0."name" ASC)
     95                   ORDER BY V0."name" ASC
     96                   LIMIT 1)
     97           WHEN "aggregation_regress_publisher"."num_awards" < 0 THEN COUNT(T3."id")
     98           ELSE NULL
     99       END AS "total_books"
     100FROM "aggregation_regress_publisher"
     101INNER JOIN "aggregation_regress_country" ON ("aggregation_regress_publisher"."country_id" = "aggregation_regress_country"."id")
     102LEFT OUTER JOIN "aggregation_regress_publisher" T3 ON ("aggregation_regress_country"."id" = T3."country_id")
     103GROUP BY "aggregation_regress_publisher"."id",
     104  (SELECT V0."id"
     105   FROM "aggregation_regress_book" V0
     106   WHERE V0."id" IN
     107       (SELECT U0."id"
     108        FROM "aggregation_regress_book" U0
     109        WHERE U0."name" = ("aggregation_regress_country"."name")
     110        ORDER BY U0."name" ASC)
     111   ORDER BY V0."name" ASC
     112   LIMIT 1)
     113
     114}}}
     115
     116
     117Full query 3.0:
     118
     119{{{
     120
     121SELECT "aggregation_publisher"."id",
     122       "aggregation_publisher"."name",
     123       "aggregation_publisher"."num_awards",
     124       "aggregation_publisher"."duration",
     125       "aggregation_publisher"."country_id",
     126       CASE
     127           WHEN "aggregation_publisher"."num_awards" >= 2 THEN
     128                  (SELECT V0."id"
     129                   FROM "aggregation_book" V0
     130                   WHERE V0."id" IN
     131                       (SELECT U0."id"
     132                        FROM "aggregation_book" U0
     133                        WHERE U0."name" = "aggregation_country"."name")
     134                   LIMIT 1)
     135           WHEN "aggregation_publisher"."num_awards" < 0 THEN COUNT(T3."id")
     136           ELSE NULL
     137       END AS "total_books"
     138FROM "aggregation_publisher"
     139INNER JOIN "aggregation_country" ON ("aggregation_publisher"."country_id" = "aggregation_country"."id")
     140LEFT OUTER JOIN "aggregation_publisher" T3 ON ("aggregation_country"."id" = T3."country_id")
     141GROUP BY "aggregation_publisher"."id"
     142}}}
Back to Top