Opened 8 years ago

Closed 8 years ago

#10390 closed (fixed)

"exact" should be NULL-safe comparison

Reported by: Rick Dean Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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

Attachments (1)

exact_null_safe.diff (497 bytes) - added by Rick Dean 8 years ago.

Download all attachments as: .zip

Change History (5)

Changed 8 years ago by Rick Dean

Attachment: exact_null_safe.diff added

comment:1 Changed 8 years ago by Alex Gaynor

Triage Stage: UnreviewedDesign decision needed

comment:2 Changed 8 years ago by 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.

comment:3 Changed 8 years ago by Ian 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.

comment:4 Changed 8 years ago by Rick Dean

Resolution: fixed
Status: newclosed

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 | 
 +---------------+-------------+
Note: See TracTickets for help on using tickets.
Back to Top