Opened 14 years ago

Closed 11 years ago

#13937 closed Bug (fixed)

Sub-query (exclude) fails when joining

Reported by: Yeago Owned by:
Component: Database layer (models, ORM) Version: 1.2
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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)

Change History (8)

comment:1 by Yeago, 14 years ago

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

comment:2 by Daishiman, 13 years ago

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

comment:3 by k0001, 13 years ago

Owner: changed from nobody to k0001

I'm checking this right now.

comment:4 by k0001, 13 years ago

Owner: k0001 removed

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.

comment:5 by Russell Keith-Magee, 13 years ago

Triage Stage: UnreviewedAccepted

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).

comment:6 by Graham King, 13 years ago

Severity: Normal
Type: Bug

comment:7 by Philippe Raoult, 13 years ago

Easy pickings: unset
UI/UX: unset

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...

comment:8 by Anssi Kääriäinen, 11 years ago

Resolution: fixed
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top