Code

Opened 5 years ago

Closed 5 years ago

#10390 closed (fixed)

"exact" should be NULL-safe comparison

Reported by: tallfred 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 tallfred 5 years ago.

Download all attachments as: .zip

Change History (5)

Changed 5 years ago by tallfred

comment:1 Changed 5 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Design decision needed

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

comment:3 Changed 5 years ago 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.

comment:4 Changed 5 years ago by tallfred

  • Resolution set to fixed
  • Status changed from new to closed

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 Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.