Opened 3 years ago

Last modified 14 months ago

#18726 new Bug

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

Reported by: bugs@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
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 (1)

test_ticket_18726.diff (2.2 KB) - added by jonaskoelker 14 months ago.
(The code from comment:8)

Download all attachments as: .zip

Change History (10)

comment:1 Changed 3 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to needsinfo
  • Status changed from new to closed

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 Changed 3 years ago 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?

comment:3 Changed 3 years ago by charettes

  • Cc charette.s@… added

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

comment:4 Changed 3 years ago by bugs@…

  • Resolution needsinfo deleted
  • Status changed from closed to 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 Changed 2 years ago by akaariai

  • Triage Stage changed from Unreviewed to Accepted

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

comment:6 Changed 2 years ago 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

comment:7 Changed 2 years ago by aaugustin

  • Status changed from reopened to new

comment:8 Changed 14 months ago 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 :-)

Changed 14 months ago by jonaskoelker

(The code from comment:8)

comment:9 Changed 14 months ago 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.

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