Opened 5 years ago

Last modified 2 years ago

#31150 closed Bug

SELECT DISTINCT is not not so distict — at Version 1

Reported by: Johannes Maron Owned by: nobody
Component: Database layer (models, ORM) Version: 3.0
Severity: Release blocker Keywords:
Cc: Simon Charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Johannes Maron)

Sadly there is more regression in Django 3.0.2 even after #31094.

Background: It's the same query as #31094. I tried upgrading to Django 3.0.2 and now I get duplicate results. Even tho they query should be distinct. Where on 2.2 the queryset yields 490 results, it's 519 on 3.0.

A quick diff on the queries still reveals a different grouped by section:

This is the new query on 3.0.2:

SELECT DISTINCT "camps_offer"."id",
                "camps_offer"."title",
                "camps_offer"."slug",
                "camps_offer"."is_active",
                "camps_offer"."modified",
                "camps_offer"."created",
                "camps_offer"."provider_id",
                "camps_offer"."activity_type",
                "camps_offer"."description",
                "camps_offer"."highlights",
                "camps_offer"."important_information",
                "camps_offer"."min_age",
                "camps_offer"."max_age",
                "camps_offer"."food",
                "camps_offer"."video",
                "camps_offer"."accommodation",
                "camps_offer"."accommodation_type",
                "camps_offer"."room_type",
                "camps_offer"."room_size_min",
                "camps_offer"."room_size_max",
                "camps_offer"."external_url",
                "camps_offer"."application_form",
                "camps_offer"."caseload",
                "camps_offer"."field_trips",
                MIN(T4."retail_price") AS "min_retail_price",
                (SELECT U0."id"
                 FROM "camps_servicepackage" U0
                          INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
                 WHERE (U0."company_id" = 1 AND U0."option" = "camps_offer"."activity_type" AND
                        ST_Contains(U2."locations", T4."position"))
                 LIMIT 1)              AS "in_package",
                "camps_provider"."id",
                "camps_provider"."title",
                "camps_provider"."slug",
                "camps_provider"."is_active",
                "camps_provider"."modified",
                "camps_provider"."created",
                "camps_provider"."logo",
                "camps_provider"."description",
                "camps_provider"."video",
                "camps_provider"."external_url",
                "camps_provider"."terms",
                "camps_provider"."cancellation_policy",
                "camps_provider"."privacy_policy",
                "camps_provider"."application_form"
FROM "camps_offer"
         LEFT OUTER JOIN "camps_bookingoption" ON ("camps_offer"."id" = "camps_bookingoption"."offer_id")
         INNER JOIN "camps_provider" ON ("camps_offer"."provider_id" = "camps_provider"."id")
         INNER JOIN "camps_bookingoption" T4 ON ("camps_offer"."id" = T4."offer_id")
WHERE ("camps_offer"."is_active" = True AND "camps_provider"."is_active" = True AND
       T4."end" >= STATEMENT_TIMESTAMP() AND T4."is_active" = True AND "camps_offer"."max_age" >= 5 AND
       "camps_offer"."min_age" <= 13 AND (SELECT U0."id"
                                          FROM "camps_servicepackage" U0
                                                   INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
                                          WHERE (U0."company_id" = 1 AND U0."option" = "camps_offer"."activity_type" AND
                                                 ST_Contains(U2."locations", T4."position"))
                                          LIMIT 1) IS NOT NULL)
GROUP BY "camps_offer"."id", T4."position", "camps_provider"."id"
ORDER BY "camps_offer"."created" ASC

And what it was (and should be) on 2.2.9:

SELECT DISTINCT "camps_offer"."id",
                "camps_offer"."title",
                "camps_offer"."slug",
                "camps_offer"."is_active",
                "camps_offer"."modified",
                "camps_offer"."created",
                "camps_offer"."provider_id",
                "camps_offer"."activity_type",
                "camps_offer"."description",
                "camps_offer"."highlights",
                "camps_offer"."important_information",
                "camps_offer"."min_age",
                "camps_offer"."max_age",
                "camps_offer"."food",
                "camps_offer"."video",
                "camps_offer"."accommodation",
                "camps_offer"."accommodation_type",
                "camps_offer"."room_type",
                "camps_offer"."room_size_min",
                "camps_offer"."room_size_max",
                "camps_offer"."external_url",
                "camps_offer"."application_form",
                "camps_offer"."caseload",
                "camps_offer"."field_trips",
                MIN(T4."retail_price") AS "min_retail_price",
                (SELECT U0."id"
                 FROM "camps_servicepackage" U0
                          INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
                 WHERE (U0."company_id" = 1 AND U0."option" = ("camps_offer"."activity_type") AND
                        ST_Contains(U2."locations", (T4."position")))
                 LIMIT 1)              AS "in_package",
                "camps_provider"."id",
                "camps_provider"."title",
                "camps_provider"."slug",
                "camps_provider"."is_active",
                "camps_provider"."modified",
                "camps_provider"."created",
                "camps_provider"."logo",
                "camps_provider"."description",
                "camps_provider"."video",
                "camps_provider"."external_url",
                "camps_provider"."terms",
                "camps_provider"."cancellation_policy",
                "camps_provider"."privacy_policy",
                "camps_provider"."application_form"
FROM "camps_offer"
         LEFT OUTER JOIN "camps_bookingoption" ON ("camps_offer"."id" = "camps_bookingoption"."offer_id")
         INNER JOIN "camps_provider" ON ("camps_offer"."provider_id" = "camps_provider"."id")
         INNER JOIN "camps_bookingoption" T4 ON ("camps_offer"."id" = T4."offer_id")
WHERE ("camps_offer"."is_active" = True AND "camps_provider"."is_active" = True AND
       T4."end" >= (STATEMENT_TIMESTAMP()) AND T4."is_active" = True AND (SELECT U0."id"
                                                                          FROM "camps_servicepackage" U0
                                                                                   INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
                                                                          WHERE (U0."company_id" = 1 AND
                                                                                 U0."option" = ("camps_offer"."activity_type") AND
                                                                                 ST_Contains(U2."locations", (T4."position")))
                                                                          LIMIT 1) IS NOT NULL)
GROUP BY "camps_offer"."id",
         (SELECT U0."id"
          FROM "camps_servicepackage" U0
                   INNER JOIN "camps_region" U2 ON (U0."region_id" = U2."id")
          WHERE (U0."company_id" = 1 AND U0."option" = ("camps_offer"."activity_type") AND
                 ST_Contains(U2."locations", (T4."position")))
          LIMIT 1), "camps_provider"."id"
ORDER BY "camps_offer"."created" ASC

Change History (1)

comment:1 by Johannes Maron, 5 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top