Opened 5 years ago

Closed 23 months ago

#13937 closed Bug (fixed)

Sub-query (exclude) fails when joining

Reported by: subsume 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 5 years ago by subsume

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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

comment:2 Changed 5 years ago by Daishiman

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

comment:3 Changed 5 years ago by k0001

  • Owner changed from nobody to k0001

I'm checking this right now.

comment:4 Changed 5 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 5 years ago by russellm

  • Triage Stage changed from Unreviewed to Accepted

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 4 years ago by graham_king

  • Severity set to Normal
  • Type set to Bug

comment:7 Changed 4 years ago by PhiR

  • 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 23 months ago by akaariai

  • Resolution set to fixed
  • Status changed from new to closed

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