Sub-query (exclude) fails when joining

[Yeago]

I have a suprisingly simple-looking query that fails in 1.2 using MySQL:

   Consumer.objects.filter(pwi__status__name='PWI Approved').exclude(contact__date__range=('2010-05-03','2010-05-04'))

This produces some suspicious SQL:

   SELECT * FROM `consumer_consumer` 
   WHERE (`consumer_consumer`.`race` = %s  
      AND NOT ((`consumer_consumer`.`id` 
         IN (
              SELECT U1.`consumer_id` FROM `base_contact` U1 
                 WHERE U1.`date` BETWEEN %s and %s
         ) AND NOT (`consumer_consumer`.`id` IS NULL))))

Take a look at this last line. I'm not sure what mechanism is adding this line, however, there is some wisdom in it because if this is transposed into the sub-query ("AND NOT UI.consumer_id IS NULL"), we have a perfectly functioning query. I can't imagine why we would want to make sure our model's primary key isn't null.

the Consumer model is rather boring, the contact model goes:

class Contact(models.Model):

consumer = models.ForeignKey('consumer.Consumer,null=True,blank=True)

[Yeago]

I got my examples mixed up there. pwi_statusname='wtvr' should be race='american'

[Daishiman]

Can you provide a sample models.py file to test it in other backends?

[k0001]

I'm checking this right now.

[k0001]

Could you tell a bit more about your scenario? Please provide some sample models, your expected results and your actual results.

Appart from —needlessly? I'm not sure— checking if the Primary Key is not null, I wonder why would this fail for you.

[Russell Keith-Magee]

For posterity, the full set of models are:

class Consumer(models.Model):
    race = models.CharField(max_length=100)

class Contact(models.Model):
    consumer = models.ForeignKey(Consumer,null=True,blank=True)
    date = models.DateField()

The IS NULL clause is being added because the foreign key is nullable, so the query engine is making sure that you aren't joining on a Contact entry that has a date in range, but has no related Consumer. However, it seems to be picking the wrong table alias (using the base table, instead of U1).

[Philippe Raoult]

I've just added a patch to #12823 which fixes a similar bug. Could you possibly check if the bug still happens ? It seems to work for me but YMMV...

[Anssi Kääriäinen]

This is fixed in master, the generated query is:

SELECT "queries_consumer"."id", "queries_consumer"."race" FROM "queries_consumer" WHERE ("queries_consumer"."race" = PWI Approved  AND NOT ("queries_consumer"."id" IN (SELECT U1."consumer_id" FROM "queries_contact" U1 WHERE (U1."date" BETWEEN 2010-05-03 and 2010-05-04 AND U1."consumer_id" IS NOT NULL))))

I will close this without adding tests, I am pretty confident that similar conditions are already tested in master.