Django

Code

Ticket #10390 (closed: fixed)

Opened 2 years ago

Last modified 1 year ago

"exact" should be NULL-safe comparison

Reported by: tallfred Assigned to: nobody
Milestone: Component: Database layer (models, ORM)
Version: SVN Keywords:
Cc: Triage Stage: Design decision needed
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

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

exact_null_safe.diff (497 bytes) - added by tallfred on 03/01/09 16:30:56.

Change History

03/01/09 16:30:56 changed by tallfred

  • attachment exact_null_safe.diff added.

03/01/09 16:34:06 changed by Alex

  • needs_better_patch changed.
  • stage changed from Unreviewed to Design decision needed.
  • needs_tests changed.
  • needs_docs changed.

03/01/09 18:24:23 changed by mtredinnick

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.

03/03/09 17:44:00 changed by ikelly

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.

04/07/09 16:16:03 changed by tallfred

  • status changed from new to closed.
  • resolution set to fixed.

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 | 
 +---------------+-------------+

Add/Change #10390 ("exact" should be NULL-safe comparison)




Change Properties
Action