"exact" should be NULL-safe comparison

[Rick Dean]

The database lookup type of "exact" should use a NULL-safe comparison of "<=>".
A patch for MySQL is attached, which I believe is standardized
SQL sytax to work fine for all the backends, but I have not tested the others.

​http://dev.mysql.com/doc/refman/5.1/en/non-typed-operators.html

[Malcolm Tredinnick]

It's not at all clear that this is a good idea. What is the problem you are trying to solve here?

NULLs aren't comparable to anything, so returning something like 1 or 0, making it look like they are comparable isn't usually the right thing to do. It might be you have some particular situation in mind that turns out to be common, reasonable and best solved by this approach, but you'll need to explain that situation.

[Erin Kelly]

This makes sense to me. We already allow foo__exact=None, which is silently converted to foo__isnull=True. One might naively expect to be able to do .filter(foo__exact=F('foo')) and have it effectively be a no-op like .all() (obviously this example is useless, but I'm sure a more reasonable use case could be devised). But the IS NULL conversion doesn't happen in that case, and all the nulls would be filtered out. The proposed patch would fix this.

I'm fairly sure that <=> isn't standard SQL. The PostgreSQL equivalent would be IS NOT DISTINCT FROM. The canonical Oracle implementation is DECODE(column, %s, 1) = 1. I have no idea what SQLite uses.

[Rick Dean]

Sorry for being slow to respond. I didn't see an
email when the other comments were posted.

The use case is Foo.objects.filter(bar=None) on
fields defined as null=True. The svn trunk
now works as the SQL is rewritten to use IS NULL
like ikelly suggested. Foo.objects.filter(Q(bar=None))
and Foo.objects.filter(bar__exact=NULL) are also fine.

Ikelly appears correct about non-standardization. Postgre
doesn't have the "<=>" operator.
​http://www.postgresql.org/docs/8.1/static/functions-comparison.html

 $ mysql -u root mysql -e "select NULL <=> NULL, NULL = NULL"
 +---------------+-------------+
 | NULL <=> NULL | NULL = NULL |
 +---------------+-------------+
 |             1 |        NULL | 
 +---------------+-------------+