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
13815	Excluding isnull=False testing failing on reverse optional relations	Bas Peschier	nobody	"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."		closed	Database layer (models, ORM)	dev		fixed	isnull, reverse relation		Ready for checkin	1	0	0	0	0	0