Opened 6 years ago

Closed 3 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 Changed 6 years ago by Yeago

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

comment:2 Changed 6 years ago by Daishiman

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

comment:3 Changed 6 years ago by k0001

Owner: changed from nobody to k0001

I'm checking this right now.

comment:4 Changed 6 years ago by k0001

Owner: k0001 deleted

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 Changed 6 years ago by Russell Keith-Magee

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 Changed 6 years ago by Graham King

Severity: Normal
Type: Bug

comment:7 Changed 5 years ago by Philippe Raoult

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 Changed 3 years ago by Anssi Kääriäinen

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