Incorrect SQL being generated in certain inheritance cases.

[Alex Gaynor]

Given the models

from django.db import models

class A(models.Model):
    pass

class B(A):
   pass

class C(A):
   pass

The query:

>>> A.objects.filter(b__isnull=False)

Generates bad SQL (specifically it omits the needed join). Worth noting that:

>>> A.objects.exclude(b=None)

works correctly.

[Alex Gaynor]

a test case

[Alex Gaynor]

Changed the summary, it wasn't totally accurate.

[Jarek Zgoda]

(In [12205]) Fixed #12567: updated Polish translation of GIS terms. Thanks pigletto for patch.

[Jarek Zgoda]

Sorry, closed wrong ticket.

[coleifer]

For reference, this is the SQL generated by the two operations:

A.objects.filter(b__isnull=False)
SELECT "test_app_a"."id", "test_app_a"."a_field" 
FROM "test_app_a" 
WHERE "test_app_a"."id" IS NOT NULL 
LIMIT 21

A.objects.exclude(b=None)
SELECT "test_app_a"."id", "test_app_a"."a_field" 
FROM "test_app_a" 
WHERE 
    NOT (
        "test_app_a"."id" IN (
            SELECT U0."id" FROM "test_app_a" U0 
            LEFT OUTER JOIN "test_app_b" U1 
                ON (U0."id" = U1."a_ptr_id") 
            WHERE U1."a_ptr_id" IS NULL
        )
    ) 
LIMIT 21

[coleifer]

So there is a bit in the query's add_filter() method that blocks join promotion if value != True. value == False seems like a valid expression, as the above example illustrates, so I simply removed that clause. I included Alex's test case in the diff.

[coleifer]

Alex pointed out that switching the promote_alias_chain bit to promote all isnull lookups would have a bad effect on queries, as isnull=False is way fast on an inner join. I poked around in trim_joins() and special-cased the break in the while loop, but ended up with some failing regressions tests generally when Q objects and pipes were used with querysets.

I ended up by choosing to skip the trim_joins() step altogether if it was an isnull=False lookup with joins. All tests are passing, including the one alex provided, and as you can see, the joins are being evaluated correctly:

In [10]: A.objects.exclude(b=None)
Out[10]: [<A: A object>]

In [11]: connection.queries.pop()
Out[11]: 
{'sql': u'SELECT "test_app_a"."id", "test_app_a"."a_field" FROM "test_app_a" WHERE NOT ("test_app_a"."id" IN (SELECT U0."id" FROM "test_app_a" U0 LEFT OUTER JOIN "test_app_b" U1 ON (U0."id" = U1."a_ptr_id") WHERE U1."a_ptr_id" IS NULL)) LIMIT 21',
 'time': '0.000'}

In [12]: A.objects.filter(b__isnull=False)
Out[12]: [<A: A object>]

In [13]: connection.queries.pop()
Out[13]: 
{'sql': u'SELECT "test_app_a"."id", "test_app_a"."a_field" FROM "test_app_a" INNER JOIN "test_app_b" ON ("test_app_a"."id" = "test_app_b"."a_ptr_id") WHERE "test_app_b"."a_ptr_id" IS NOT NULL LIMIT 21',
 'time': '0.000'}

[coleifer]

updated patch to respect query optimization on isnull=False & inner joins

[Russell Keith-Magee]

There's a lot of similarity here with #12153; I don't think it's the same problem, but it's in a very similar area.

Either way, Not critical for 1.2

[Julien Phalip]

The tests would need to be rewritten using unittests since this is now Django's preferred way.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Anssi Kääriäinen <akaariai@…>]

In 630b9df42f771e90d9beb1766d4e7aa2107bd82d:

Fixed #12567 -- Incorrect SQL in model inheritance case

An isnull lookup produced incorrect SQL. This was already fixed
earlier, so only tests added.