Opened 12 years ago

Closed 5 years ago

Last modified 5 years ago

#18726 closed Bug (fixed)

Combination of F() expression with query seems to confuse sql compiler's table aliases

Reported by: bugs@… 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)

test_ticket_18726.diff (2.2 KB ) - added by Jonas Kölker 10 years ago.
(The code from comment:8)
18726.diff (512 bytes ) - added by Mariusz Felisiak 6 years ago.

Download all attachments as: .zip

Change History (14)

comment:1 by Anssi Kääriäinen, 12 years ago

Resolution: needsinfo
Status: newclosed

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.

comment:2 by bugs@…, 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 Simon Charette, 12 years ago

Cc: charette.s@… added

Yes. It doesn't have to be fully integrated into django's test suite.

comment:4 by bugs@…, 12 years ago

Resolution: needsinfo
Status: closedreopened

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 Anssi Kääriäinen, 11 years ago

Triage Stage: UnreviewedAccepted

The error is confirmed on master. It is not caused by what I suspected in comment:1.

comment:6 by Anssi Kääriäinen, 11 years ago

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

comment:7 by Aymeric Augustin, 11 years ago

Status: reopenednew

comment:8 by Jonas Kölker, 10 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 :-)

by Jonas Kölker, 10 years ago

Attachment: test_ticket_18726.diff added

(The code from comment:8)

comment:9 by Jonas Kölker, 10 years ago

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.

comment:10 by Mariusz Felisiak, 6 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned
Version: 1.4master

by Mariusz Felisiak, 6 years ago

Attachment: 18726.diff added

comment:11 by Mariusz Felisiak, 5 years ago

Resolution: fixed
Status: assignedclosed
Last edited 5 years ago by Mariusz Felisiak (previous) (diff)

comment:12 by GitHub <noreply@…>, 5 years ago

In 9ab1d55:

Refs #18726 -- Added test for excluding circular related fields with F() expression.

Fixed in f19a4945e1191e1696f1ad8e6cdc6f939c702728.

Note: See TracTickets for help on using tickets.
Back to Top