[Django] #18726: Combination of F() expression with query seems to confuse sql compiler's table aliases
Django
table aliases
----------------------------------------------+--------------------
Reporter: bugs@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Hi,
I have a query that causes the sql compiler to output invalid sql. Running
a query of the form (excuse the coarse anonymization):
{{{#!python
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/18726>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
table aliases
Reporter: bugs@… | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.4
(models, ORM) | Resolution: needsinfo
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by akaariai):
* status: new => closed
* needs_better_patch: => 0
* resolution: => needsinfo
* needs_tests: => 0
* needs_docs: => 0
Comment:
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:
1. The F-expr is added to the query.
2. add_filter sees the exclude + multijoin condition and decides that a
subquery is needed.
3. the filter expression is added to the subquery, the condition is
against the F-expr, and it has alias T4 in the outer query.
4. split_exclude() calls bump_prefix, which pushes all T aliases to U
aliases. The T4 is changed to U4, but this is incorrect as the reference
should still be to T4.
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:1>
Django
table aliases
Reporter: bugs@… | Owner: nobody
Component: Database layer | Version: 1.4
(models, ORM) | Resolution: needsinfo
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Comment (by bugs@…):
"if you can provide more information."
Would a test case in the form of a lone models.py that triggers the
problem count?
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:2>
Django
table aliases
Reporter: bugs@… | Owner: nobody
Component: Database layer | Version: 1.4
(models, ORM) | Resolution: needsinfo
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Changes (by charettes):
* cc: charette.s@… (added)
Comment:
Yes. It doesn't have to be fully integrated into django's test suite.
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:3>
Django
table aliases
Reporter: bugs@… | Owner: nobody
Type: Bug | Status: reopened
(models, ORM) | Resolution:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Changes (by bugs@…):
* status: closed => reopened
* resolution: needsinfo =>
Comment:
Ok here we are - a models.py that causes the problem:
{{{#!python
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:
{{{#!python
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" = ...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:4>
Django
table aliases
Reporter: bugs@… | Owner: nobody
Type: Bug | Status: reopened
Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* stage: Unreviewed => Accepted
Comment:
The error is confirmed on master. It is not caused by what I suspected in
comment:1.
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:5>
Django
table aliases
Reporter: bugs@… | Owner: nobody
Type: Bug | Status: reopened
Component: Database layer | Version: 1.4
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
I rewrote the test case into expressions regress. I know at least of two
different bugs happening here:
1. The joins are "reverse trimmed" in a way that causes the query error
in split_exclude() -> set_start().
2. 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
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:6>
Django
table aliases
Reporter: bugs@… | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by jonaskoelker):
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 :-)
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:8>
Django
table aliases
Reporter: bugs@… | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by jonaskoelker):
Bug #21703 seems to be related to (if not the same as) this. Bug #20788
and bug #21748 pertain to queries with a similar structure; they ''may''
be related.
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:9>
Django
table aliases
Reporter: bugs@… | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* owner: nobody => felixxm
* status: new => assigned
* version: 1.4 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:10>
Django
table aliases
Reporter: bugs@… | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* Attachment "18726.diff" added.
Django
table aliases
Reporter: bugs@… | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* status: assigned => closed
* resolution: => fixed
Comment:
Fixed in 9dc367dc10594ad024c83d398a8e3c3f8f221446.
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:11>
Django
table aliases
Reporter: bugs@… | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by GitHub <noreply@…>):
In [changeset:"9ab1d5559681d3adde4fcfc98a19a7396ed6a42e" 9ab1d55]:
{{{
#!CommitTicketReference repository=""
revision="9ab1d5559681d3adde4fcfc98a19a7396ed6a42e"
Refs #18726 -- Added test for excluding circular related fields with F()
expression.
Fixed in f19a4945e1191e1696f1ad8e6cdc6f939c702728.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18726#comment:12>