#31150: SELECT DISTINCT is not not so distict
-------------------------------------+-------------------------------------
Reporter: Johannes | Owner: nobody
Hoppe |
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Release | Keywords:
blocker |
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31150>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.26823233e8becb6efd1ba9082e3c35ec%40djangoproject.com.
