Opened 4 years ago

Last modified 3 years 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



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 Jonas Kölker 3 years ago.
(The code from comment:8)

Download all attachments as: .zip

Change History (10)

comment:1 Changed 4 years ago by Anssi Kääriäinen

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
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 Changed 4 years ago by bugs@…

"if you can provide more information."

Would a test case in the form of a lone that triggers the problem count?

comment:3 Changed 4 years ago by Simon Charette

Cc: charette.s@… added

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

comment:4 Changed 4 years ago by bugs@…

Resolution: needsinfo
Status: closedreopened

Ok here we are - a that causes the problem:

from django.db import models

class ModelC ( models.Model ):

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:") INNER JOIN "dummy_modela_m2m_field_r" U5 ON (U4."id" = ...

comment:5 Changed 4 years ago by Anssi Kääriäinen

Triage Stage: UnreviewedAccepted

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

comment:6 Changed 4 years ago by Anssi Kääriäinen

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

comment:7 Changed 4 years ago by Aymeric Augustin

Status: reopenednew

comment:8 Changed 3 years ago by Jonas Kölker

Here's a test case for tests/queries/

    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:




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/
+++ b/django/db/models/sql/
@@ -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 3 years ago by Jonas Kölker

Attachment: test_ticket_18726.diff added

(The code from comment:8)

comment:9 Changed 3 years ago by Jonas Kölker

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