Opened 14 months ago

Closed 14 months ago

Last modified 2 weeks ago

#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 David Sanders)

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 David Sanders, 14 months ago

Triage Stage: UnreviewedAccepted

Thanks for the report 👍

comment:2 by David Sanders, 14 months ago

Description: modified (diff)

(formatting sql in description to make it a little clearer for others)

comment:3 by Mariusz Felisiak, 14 months ago

Resolution: duplicate
Status: newclosed
Triage Stage: AcceptedUnreviewed

I'm pretty sure it's a duplicate of #25245.

comment:4 by Hana Burtin, 2 weeks ago

[Retracting my comment] I though I fond something, but just got lost in my brackets count

Last edited 2 weeks ago by Hana Burtin (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top