#18726 closed Bug (fixed)
Combination of F() expression with query seems to confuse sql compiler's table aliases
Reported by: | Owned by: | Mariusz Felisiak | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | charette.s@… | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Hi,
I have a query that causes the sql compiler to output invalid sql. Running a query of the form (excuse the coarse anonymization):
AThroughModel.objects.exclude ( foreignkeyfield_a__m2mfield_b__m2mfield_c = F ( "foreignkeyfield_x__foreignkeyfield_y__foreignkeyfield_z" ) )
this causes an exception along the lines of:
DatabaseError: missing FROM-clause entry for table "u4" LINE 1: ...) INNER JOIN "m2mfield_b_join_table_name" U5 ON (U4."id" = ...
Where AThroughModel is a model that's used as a "through" in an m2mfield from foreignkeyfield_x's model to foreignkeyfield_a's model.
Interestingly, it works when you exchange the .exclude() with a .filter().
This happens both on django 1.3.1 and 1.4.1 with a postgres backend & psycopg2.
Again, sorry about the dumb anonymization, but my boss would be a bit funny about exposing model structure. A full (non) working test case might be a bit tricky as such.
Attachments (2)
Change History (14)
comment:1 by , 12 years ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
comment:2 by , 12 years ago
"if you can provide more information."
Would a test case in the form of a lone models.py that triggers the problem count?
comment:3 by , 12 years ago
Cc: | added |
---|
Yes. It doesn't have to be fully integrated into django's test suite.
comment:4 by , 12 years ago
Resolution: | needsinfo |
---|---|
Status: | closed → reopened |
Ok here we are - a models.py that causes the problem:
from django.db import models class ModelC ( models.Model ): pass class ModelB ( models.Model ): m2m_field_q = models.ManyToManyField ( ModelC ) class ModelA ( models.Model ): m2m_field_r = models.ManyToManyField ( ModelB ) class ModelY ( models.Model ): fk_field_s = models.ForeignKey ( ModelC ) class ModelX ( models.Model ): fk_field_t = models.ForeignKey ( ModelY ) m2m_field_u = models.ManyToManyField ( ModelA , through = "ModelXThroughA" ) class ModelXThroughA ( models.Model ): fk_field_v = models.ForeignKey ( ModelX ) fk_field_w = models.ForeignKey ( ModelA )
which upon running:
ModelXThroughA.objects.exclude ( fk_field_w__m2m_field_r__m2m_field_q = F ( "fk_field_v__fk_field_t__fk_field_s" ) )
results in:
DatabaseError: missing FROM-clause entry for table "u4" LINE 1: ...id") INNER JOIN "dummy_modela_m2m_field_r" U5 ON (U4."id" = ...
comment:5 by , 12 years ago
Triage Stage: | Unreviewed → Accepted |
---|
The error is confirmed on master. It is not caused by what I suspected in comment:1.
comment:6 by , 12 years ago
I rewrote the test case into expressions regress. I know at least of two different bugs happening here:
- The joins are "reverse trimmed" in a way that causes the query error in split_exclude() -> set_start().
- The F() expr is added to the query, then split_exclude() need is seen, and the F() expr is again added to the subquery.
Even after bypassing the two above issues there is still something else going on.
I am not sure if the test case is correct - at least it shows the U4 reference error, see https://github.com/akaariai/django/compare/ticket_18726
comment:7 by , 12 years ago
Status: | reopened → new |
---|
comment:8 by , 11 years ago
Here's a test case for tests/queries/tests.py
:
def test_ticket_18726(self): a = ObjectA.objects.create(name="alpha") b = ObjectB.objects.create(name="beta", objecta=a, num=int()) c = ObjectC.objects.create(name="charlie", objectb=b, objecta=None) def test(*args, **kwds): ObjectB.objects.filter(*args, **kwds).count() try: ObjectB.objects.exclude(*args, **kwds).count() except DatabaseError as e: self.fail(repr(e)) test(objecta__objectb__name='beta') test(objectc__objectb__name=F('name')) #test(objecta__objectb__name=F('name')) test(objecta__objectb__name=F('objecta__name')) test(objecta__objectb__name=F('objecta__objectb__name')) test(objecta__objectb__name=F('objectc__objectb__name')) test(objecta__objectb__name=F('objectc__objectb__objecta__name')) #test(objecta__objectb__name=F('objectc__name')) test(name=F('objecta__objectb__name')) test(objecta__name=F('objecta__objectb__name')) #test(objectc__name=F('objecta__objectb__name'))
The commented-out lines exhibit the error. The other ones don't.
The first failure (test(objecta__objectb__name=F('name'))
) goes away if you do this:
--- a/django/db/models/sql/query.py +++ b/django/db/models/sql/query.py @@ -1882,7 +1882,7 @@ class Query(object): # in `trim_start` if self.alias_map[self.tables[pos + 1]].join_type != self.LOUTER: select_fields = [r[0] for r in join_field.related_fields] select_alias = self.tables[pos + 1] - self.unref_alias(self.tables[pos]) + # self.unref_alias(self.tables[pos]) extra_restriction = join_field.get_extra_restriction( self.where_class, None, self.tables[pos + 1]) if extra_restriction:
Note that self.alias_refcount['U1'] == 0
for some self
of type Query
, at some point during the execution of the first test case (if you don't comment out the unreffing). The stuff in trim_start
before unref_alias
looks like it *might* be implicated, but I can't tell for sure. Also, even if you comment out unref_alias
the other two failures remain.
Also, observe that you don't need a many-to-many relationship if you do a reverse lookup on a foreign key (but path.m2m
is still true in trim_start
).
I hope this helps :-)
comment:9 by , 11 years ago
comment:10 by , 7 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Version: | 1.4 → master |
by , 7 years ago
Attachment: | 18726.diff added |
---|
comment:11 by , 6 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
Fixed in f19a4945e1191e1696f1ad8e6cdc6f939c702728.
I have an idea of what is happening, but can't test if my suspicion is correct as there isn't a test case in this ticket.
So, my guess is this:
Can you verify if my guess is correct?
Even if the above is correct I wonder if the query would produce correct results if bump_prefix was fixed.
I am going to close this needsinfo, as currently it is almost impossible to verify the bug in this ticket. Please reopen if you can provide more information.