Excluding isnull=False testing failing on reverse optional relations

[Bas Peschier]

This one is related to #13768.

When executing <queryset>.exclude(<reverse relation>__isnull=False) SQL is generated which can contain NULL-values in a subquery when the relation is optional (null=True), always resulting in an empty resultset, irregardless of other values in the subquery.

This is the SQL generated:

class Outer(models.Model):
    name    = models.CharField(max_length=1)

class Inner(models.Model):
    outer   = models.ForeignKey(Outer, null=True)

Outer.objects.exclude(inner__isnull=False):

SELECT "test_outer"."id", "test_outer"."name" FROM "test_outer" WHERE NOT (("test_outer"."id" IN (SELECT U1."outer_id" FROM "test_inner" U1 WHERE U1."id" IS NOT NULL) AND NOT ("test_outer"."id" IS NULL))) LIMIT 21

The subquery could include a test for the relation-field (U1."outer_id" IS NOT NULL) to exclude unusable NULL-values, which will make this work. SQL-generation in Django is one of the few areas I am not comfortable with, so I have no relation to *where* to fix this.

I have attached a unittest to check all isnull-tests, just to be sure.

[Bas Peschier]

unittest for checking isnull on reverse relations

[Bas Peschier]

models for unittest

[Bas Peschier]

Patch + extension of null_queries doctest

[anonymous]

Found to time to dive into Django's SQL generation and created patch for this case.

[anonymous]

Tested it with django 1.1.2 and this patch fixes alot of issues for me! (I have a stack of workarounds for this bug).
Thank you!

Hope this gets applied for the next release.

[Bas Peschier]

Updated patch with unittest instead of doctest

[Marcos Moyano]

I can confirm the patch worked against revision 14551

[Daishiman]

Confirmed here as well.

[Russell Keith-Magee]

In [15458]:

Fixed #13815 -- Ensure that reverse exclude lookups on nullable foreign keys exclude null values. Thanks to bpeschier for the report and patch.

[Russell Keith-Magee]

In [15460]:

[1.2.X] Fixed #13815 -- Ensure that reverse exclude lookups on nullable foreign keys exclude null values. Thanks to bpeschier for the report and patch.

Backport of r15458 from trunk.