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

[bugs@…]

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.

[Anssi Kääriäinen]

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.

[bugs@…]

"if you can provide more information."

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

[Simon Charette]

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

[bugs@…]

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" = ...

[Anssi Kääriäinen]

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

[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 ​https://github.com/akaariai/django/compare/ticket_18726

[Jonas Kölker]

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 :-)

[Jonas Kölker]

(The code from comment:8)

[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.

[Mariusz Felisiak]

Fixed in 9dc367dc10594ad024c83d398a8e3c3f8f221446.

[GitHub <noreply@…>]

In 9ab1d55:

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

Fixed in f19a4945e1191e1696f1ad8e6cdc6f939c702728.