id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34538	Incorrect query generated with on subquery WHERE depending on the order of the Q() objects	Alex	nobody	"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:
{{{
#!python
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
{{{
#!python
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)
{{{
#!python
(
    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
{{{
#!sql
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)
{{{
#!python
(
    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
{{{
#!sql
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.

"	Bug	closed	Database layer (models, ORM)	4.2	Normal	duplicate			Unreviewed	0	0	0	0	0	0