#34538 closed Bug (duplicate)
Incorrect query generated with on subquery WHERE depending on the order of the Q() objects
Reported by: | Alex | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.2 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
I have reproduced this error on
Django 4.1.9 and Mysql 5.7.
Django 4.2.1 and Mysql 8.0, Postgres 14.1 and Sqlite 3.
Models:
from django.db import models class Child(models.Model): pass class ParentLink(models.Model): enabled = models.BooleanField(db_index=True) class ChildLink(models.Model): enabled = models.BooleanField(db_index=True) origin_child = models.ForeignKey( Child, on_delete=models.PROTECT, related_name='origin_children', ) parent_link = models.ForeignKey( ParentLink, on_delete=models.PROTECT, related_name='child_links', )
Data
Child.objects.create(id=1) Child.objects.create(id=2) ParentLink.objects.create(id=1, enabled=True) ParentLink.objects.create(id=2, enabled=True) ChildLink.objects.create(id=1, enabled=True, origin_child_id=1, parent_link_id=1) ChildLink.objects.create(id=2, enabled=True, origin_child_id=2, parent_link_id=2) ChildLink.objects.create(id=3, enabled=True, origin_child_id=2, parent_link_id=1) ChildLink.objects.create(id=4, enabled=True, origin_child_id=1, parent_link_id=2)
This code generates the correct SQL query and returns the correct results (0 results for the test data)
( ParentLink.objects .filter( ~Q(child_links__origin_child_id=1) | Q(child_links__origin_child_id=1, child_links__enabled=False), enabled=True ) )
SQL query generated in Postgres
SELECT "testquery_parentlink"."id", "testquery_parentlink"."enabled" FROM "testquery_parentlink" LEFT OUTER JOIN "testquery_childlink" ON ("testquery_parentlink"."id" = "testquery_childlink"."parent_link_id") WHERE ((NOT (exists (SELECT 1 AS "a" FROM "testquery_childlink" u1 WHERE (u1."origin_child_id" = 1 AND u1."parent_link_id" = ("testquery_parentlink"."id")) LIMIT 1)) OR (NOT "testquery_childlink"."enabled" AND "testquery_childlink"."origin_child_id" = 1)) AND "testquery_parentlink"."enabled")
This one returns incorrect results (Returns both ParentLinks in the test data)
( ParentLink.objects .filter( Q(child_links__origin_child_id=1, child_links__enabled=False) | ~Q(child_links__origin_child_id=1), enabled=True ) )
SQL query generated in Postgres
SELECT "testquery_parentlink"."id", "testquery_parentlink"."enabled" FROM "testquery_parentlink" LEFT OUTER JOIN "testquery_childlink" ON ("testquery_parentlink"."id" = "testquery_childlink"."parent_link_id") WHERE (((NOT "testquery_childlink"."enabled" AND "testquery_childlink"."origin_child_id" = 1) OR NOT (exists (SELECT 1 AS "a" FROM "testquery_childlink" u1 WHERE (u1."origin_child_id" = 1 AND u1."id" = ("testquery_childlink"."id") AND "testquery_childlink"."parent_link_id" = ("testquery_parentlink"."id")) LIMIT 1))) AND "testquery_parentlink"."enabled")
The WHERE in the subquery in the second case is incorrect.
Change History (4)
comment:1 by , 19 months ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 19 months ago
Description: | modified (diff) |
---|
(formatting sql in description to make it a little clearer for others)
comment:3 by , 19 months ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Triage Stage: | Accepted → Unreviewed |
I'm pretty sure it's a duplicate of #25245.
comment:4 by , 6 months ago
[Retracting my comment] I though I fond something, but just got lost in my brackets count
Thanks for the report 👍