Negated XOR conditions incorrectly use INNER JOIN for nullable relations

[Pravin]

Given the following models:

class Detail(models.Model):
    first = models.CharField(max_length=20)


class Item(models.Model):
    detail = models.ForeignKey(
        Detail,
        models.CASCADE,
        null=True,
    )

The following query should return both items:

from django.db.models import Q

detail = Detail.objects.create(first="match")
with_detail = Item.objects.create(detail=detail)
without_detail = Item.objects.create()

items = Item.objects.filter(
    ~(
        Q(detail__first__isnull=True)
        ^ Q(pk=without_detail.pk)
    )
).order_by("pk")

print(list(items.values_list("pk", flat=True)))

For with_detail, detail__first__isnull and
pk=without_detail.pk are both false.

For without_detail, the missing detail produces a NULL
value and its primary key matches without_detail.pk, so both conditions
are true.

A negated XOR matches when both conditions have the same result. Therefore,
both items should be returned.

Expected result:

[with_detail.pk, without_detail.pk]

Actual result:

[with_detail.pk]

JoinPromoter doesn't handle XOR separately. For join promotion,
a negated XOR is treated as an AND, so an INNER JOIN is considered
safe. The inner join removes without_detail before the conditions are
evaluated.

[Simon Charette]

​I confirmed this has been a bug since the introduction of XOR in c6b4d62fa2c7f73b87f6ae7e8cf1d64ee5312dc5.

The bug effectively lies in JoinPromoter which was never made aware or it.

[Simon Charette]

Left some comments regarding a potential better host for tests and a suggestion to adjust the effective_connector flow.

[Pravin]

Thanks, I moved the tests and update the effective_connector flow.

[Simon Charette]

Thank you for the adjustments.

[Pravin]

Receiving approval from you is more rewarding itself. Thank you for review.

[Sarah Boyce <42296566+sarahboyce@…>]

In 77eea99:

Fixed #37438 -- Fixed join promotion for XOR conditions.

JoinPromoter didn't handle XOR conditions. Because of this, a negated
XOR could use an INNER JOIN and remove rows that should be returned.

Thanks Simon Charette and Sarah Boyce for review.