On Thursday, November 14, 2013 2:17:43 PM UTC+2, Phoebe Bright wrote: > > After a long time trying to create a simple version that would replicate > the error, and failing, I've tracked it through the original code. > > Line 1140 in django/db/models/sql/query.py in function build_filter > > if current_negated and (lookup_type != 'isnull' or value is False): > self.promote_joins(join_list) > if (lookup_type != 'isnull' and ( > self.is_nullable(targets[0]) or > self.alias_map[join_list[-1]].join_type == > self.LOUTER)): > # The condition added here will be SQL like this: > # NOT (col IS NOT NULL), where the first NOT is added in > # upper layers of code. The reason for addition is that if > col > # is null, then col != someval will result in SQL "unknown" > # which isn't the same as in Python. The Python None > handling > # is wanted, and it can be gotten by > # (col IS NULL OR col != someval) > # <=> > # NOT (col IS NOT NULL AND col = someval). > clause.add((Constraint(alias, targets[0].column, None), > 'isnull', False), AND) <---- this is adding the spurious clause > > This is django version 1.6 and happens using both the original Q version > and using your suggested .exclude version. > Having found it I'm still not sure what it means or how to avoid it being > triggered! > > Any suggestions? >
I believe the query is actually correct. The idea is that .exclude(condition) produces complement of .filter(condition). The simplest example I can generate is using raw SQL on PostgreSQL: akaariai=# create temp table foo(id integer); CREATE TABLE akaariai=# insert into foo values(null); INSERT 0 1 -- What .filter(id__in=[1, 2, 3]) will do: akaariai=# select * from foo where id in (1, 2, 3); id ---- (0 rows) -- If the "id is not null" condition isn't there akaariai=# select * from foo where not (id in (1, 2, 3)); id ---- (0 rows) -- Note: we get still zero rows while we should get the complement, that is one row. -- Add in the id is not null condition akaariai=# select * from foo where not (id in (1, 2, 3) and id is not null); id ---- (1 row) -- Now we got the complement of the .filter() query. The problem comes from this feature in SQL: NULL in (1, 2, 3) => unknown NOT (NULL in (1, 2, 3)) => unknown so, NOT (condition) doesn't produce complement of (condition) in SQL! To avoid that problem we have to add the AND id IS NOT NULL into the condition. - Anssi -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at http://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/133e9b0b-1e93-47fc-82ac-4a97d0cca021%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
