#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 , 16 months ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 16 months ago
Description: | modified (diff) |
---|
(formatting sql in description to make it a little clearer for others)
comment:3 by , 16 months ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Triage Stage: | Accepted → Unreviewed |
I'm pretty sure it's a duplicate of #25245.
comment:4 by , 3 months ago
Looks like the issue is the priorities of operators in postgres:
postgres=# select false and false or true; ?column? t
postgres=# select false and true or false; ?column? f
And I think we are expecting the same operation priority as python:
>>> 1 & 0 | 1 <<< 1 >>> 1 & 1 | 0 <<< 1
To make things simpler: in python OR operators (|
and or
) are evaluated before AND operators (&
and and
), while for postgres they have the same priority order.
To solve this issue and similar ones, we might want to always add brackets around OR
operator.
Thanks for the report 👍