Is this what you would expect from this Q query?

[Phoebe Bright]

I have this line in my code where the functions return an ID and what I wanted was to select all records that did not belong to one of these users.

    Carbon.objects.filter(~Q(user__in = [limbo_user(), system_user(), retire_user()]))

What I expected to get was 

SELECT ••• FROM "web_carbon" WHERE NOT(("web_carbon"."user_id" IN (5, 2, 4))

but what I actually got was

SELECT ••• FROM "web_carbon" WHERE NOT(("web_carbon"."user_id" IN (5, 2, 4) AND "web_carbon"."user_id" ISNOT NULL))

So it always returns a null set.  Is this correct behaviour and if so, does anyone know what the correct syntax is?

[tim]

Hi Phoebe,

I cannot reproduce this on Django's master branch. What version of Django are you using? It's possible it was fixed since then. Could you provide a minimal set of models to reproduce?

If upgrading to a more recent version of Djanago doesn't fix the problem, you might try .exclude(user__in= rather than .filter(~Q if that works for your query.

Tim

[Phoebe Bright]

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?

Phoebe.

[tim]

Please provide your models so I can reproduce it myself. It's quite difficult (at least for me) to help otherwise.

[akaariai]

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

[Phoebe Bright]

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.