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.