Code

Opened 6 years ago

Closed 6 years ago

#7330 closed (duplicate)

Filtering on multiple indirect relations to same table gives incorrect SQL

Reported by: Anossov Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: qsrf, join
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

I came across the following problem: if you filter through two foreign keys to same table, it gets referenced only once, like this:

class C(model):
    somefield = IntegerField()

# Every B is related to a C
class B(model):
    c = ForeignKey(C)

# Every A is related (differently) to two Bs, and indirectly to two different Cs
class A(model):
    b1 = ForeignKey(B, db_column='b1')
    b2 = ForeignKey(B, db_column='b2')

A.objects.filter(b1__c = 1).filter(b2__c = 2).query.as_sql()

# SELECT ...
# FROM `A`
# INNER JOIN `B` ON (`A`.`b1` = `B`.`id`)
# INNER JOIN `B` T4 ON (`A`.`b2` = T4.`id`)
# WHERE `B`.`c_id` = 1  AND `B`.`c_id` = 2
#
# This is wrong, it should be
# SELECT ...
# FROM `A`
# INNER JOIN `B` ON (`A`.`b1` = `B`.`id`)
# INNER JOIN `B` T4 ON (`A`.`b2` = T4.`id`)
# WHERE `B`.`c_id` = 1  AND `T4`.`c_id` = 2

# If we filter deeper, it gets more obvious:
A.objects.filter(b1__c__somefield = 1).filter(b2__c__somefield = 2).query.as_sql()

# SELECT ...
# FROM `A`
# INNER JOIN `B` ON (`A`.`b1` = `B`.`id`)
# INNER JOIN `C` ON (`B`.`c_id` = `C`.`id`)
# INNER JOIN `B` T4 ON (`A`.`b2` = T4.`id`)
# WHERE `C`.`somefield` = 1  AND `C`.`somefield` = 2
#
# This is wrong, should be
# SELECT ...
# FROM `A`
# INNER JOIN `B` ON (`A`.`b1` = `B`.`id`)
# INNER JOIN `C` ON (`B`.`c_id` = `C`.`id`)
# INNER JOIN `B` T4 ON (`A`.`b2` = T4.`id`)
# INNER JOIN `C` T5 ON (`T4`.`c_id` = `T5`.`id`)
# WHERE `C`.`somefield` = 1  AND `T5`.`somefield` = 2

Suggested solution is to call sql.query.join in sql.query.setup_joins with always_create=dupe_multis not only in m2m fields, but in o2o and m2o fields as well (see patch). I'm not sure if it's not breaking anything though, I should test it.

Attachments (1)

query.py.diff (648 bytes) - added by Anossov 6 years ago.

Download all attachments as: .zip

Change History (4)

Changed 6 years ago by Anossov

comment:1 Changed 6 years ago by Anossov

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

Patch certainly breaks filters, so never mind it.. yet the problem exists.

comment:2 Changed 6 years ago by oyvind

Same as #7277 , #7125

comment:3 Changed 6 years ago by jacob

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

I don't think this is related to #7277, but it is indeed a duplicate of #7125.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.