Incorrect query generated with on subquery WHERE depending on the order of the Q() objects

[Alex]

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.

[David Sanders]

Thanks for the report 👍

[David Sanders]

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

[Mariusz Felisiak]

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

[Hana Burtin]

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.