qs.exclude(something=123) should not exclude objects with something=None

[Thomas Steinacher <tom@…>]

from django.db import models
class A(models.Model):
    str = models.CharField(null=True, blank=True, max_length=20)
    def __unicode__(self):
        return self.str or 'None'

In [1]: from test import models

In [2]: a = models.A.objects.create(str='a')

In [3]: b = models.A.objects.create(str=None)

In [4]: models.A.objects.exclude(str='a')
Out[4]: []

In [5]: models.A.objects.filter(str__isnull=True).exclude(str='a')
Out[5]: []

In [6]: models.A.objects.all()                                    
Out[6]: [<A: a>, <A: None>]

Expected output:

Out[4]: [<A: None>]  
Out[5]: [<A: None>]  

Tested with trunk & queryset-refactor.

[Johannes Dollinger]

I can reproduce this (r7573 + postgres).

It could be solved easyly by making __exact NULL-safe:

• postgres %s IS NOT DISTINCT FROM ​http://postgresql.com.cn/docs/8.3/static/functions-comparison.html
• mysql %s <=> ​http://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html

But I couldn't find something comparable for sqlite and oracle (except for (a=b OR (a IS NULL AND b IS NULL))) - and I don't know if anything depends on NULL == 1=NULL == NOT (1=NULL) and would break with FALSE == 1<=>NULL != NOT(1<=>NULL) == TRUE.

While trying to come up with a patch I found some dead code that should handle the issue for related tables (see patch, missing [JOIN_TYPE] in if final > 1 ...) and just threw in a similar weakening for the unjoined case.

This will be a backwards incompatible change.

[Malcolm Tredinnick]

Thanks for the patch, but ... one way to tell that a patch isn't quite correct is when the existing test suite doesn't pass when it's applied. :-)

This patch breaks the exclude(field__in=[]) case. I'll fix it before committing and add some tests for the issues in this ticket, but please check the test suite and include tests for any new functionality. It makes things easier.

[Malcolm Tredinnick]

Fixed in [7760].

[Malcolm Tredinnick]

(In [7812]) Modified [7760] to not include a "col is not NULL" fragment for non-nullable fields.

This avoids any use of "pk is not NULL" fragment, which behave inconsistently
in MySQL. Thanks to Russell Keith-Magee for diagnosing the problem and
suggesting the easy fix.

Refs #7076.

[Jacob]

Milestone 1.0 deleted