Aghh, the light has gone one. The issue is in my models:
from django.db import models
from django.contrib.auth.models import User
class Carbon(models.Model):
user = models.ForeignKey(User, blank=True, null=True) <-----
allowing null causes extra clause to be added in queryset
kg = models.DecimalField(max_digits=18, decimal_places=6, default=1)
To reproduce:
from web.models import *
from django.contrib.auth.models import User
u1=User.objects.create_user('A', 'a...@test.com', 'test')
u2=User.objects.create_user('B', 'b...@test.com', 'test')
u3=User.objects.create_user('C', 'c...@test.com', 'test')
u4=User.objects.create_user('D', 'd...@test.com', 'test')
Carbon.objects.create(user=u1, kg=10)
Carbon.objects.create(user=u2, kg=10)
Carbon.objects.create(user=u3, kg=10)
Carbon.objects.create(user=u4, kg=10)
rest = Carbon.objects.filter(~Q(user__in =
[u1,u2,u3])).aggregate(total=Sum('kg'))
rest2 = Carbon.objects.exclude(user__in =
[u1,u2,u3]).aggregate(total=Sum('kg'))
from django.db import connection
connection.queries
So simple in the end.
Thank you both for helping resolve this. Will tighten up my model and
remove option for a blank user.
Phoebe.
On Thursday, 14 November 2013 16:15:31 UTC, akaariai wrote:
>
> 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/1cd0e599-67ff-4067-a23d-ed2d06017dac%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
