Exists() feature generates invalid SQL query on postgres backend

[Vasily Stepanov]

Exists() feature, implemented in #27149 ticket, not working properly on postgres backend.
This happens only in complex queries, where django has to use table aliases.
Consider the following models:

class Foo(models.Model):
    spam1 = models.ForeignKey('Spam', on_delete=models.CASCADE, related_name='+')
    spam2 = models.ForeignKey('Spam', on_delete=models.CASCADE, related_name='+')


class Bar(models.Model):
    name1 = models.CharField(max_length=200)
    name2 = models.CharField(max_length=200)


class Spam(models.Model):
    name = models.CharField(max_length=200)

And the code:

bars = models.Bar.objects.filter(
        name1=OuterRef('spam1__name'),
        name2=OuterRef('spam2__name'))

qs = models.Foo.objects.annotate(bars=Exists(bars))
qs = qs.filter(bars=True)

This SQL generated using django.db.backends.postgresql backend:

SELECT
    "demo_foo"."id", "demo_foo"."spam1_id",
    "demo_foo"."spam2_id",
    EXISTS(
        SELECT U0."id", U0."name1", U0."name2"
          FROM "demo_bar" U0
         WHERE (U0."name2" = ("demo_spam"."name") AND U0."name1" = ("T3"."name"))) AS "bars"
  FROM "demo_foo"
INNER JOIN "demo_spam" ON ("demo_foo"."spam2_id" = "demo_spam"."id")
INNER JOIN "demo_spam" T3 ON ("demo_foo"."spam1_id" = T3."id")
WHERE EXISTS(
    SELECT U0."id", U0."name1", U0."name2"
      FROM "demo_bar" U0
     WHERE (U0."name2" = ("demo_spam"."name") AND U0."name1" = ("T3"."name"))) = True

which ends up with this error:

django.db.utils.ProgrammingError: missing FROM-clause entry for table "T3"
LINE 1: ...."name1" = ("demo_spam"."name") AND U0."name2" = ("T3"."name...
                                                             ^

This happens because of quotes around T3.

SQL works as expected, if you remove these quotes and execute it manually.

[Tim Graham]

Matt, are you interested in looking into this?

[Matthew Schinckel]

It looks to be a difference between the quoting of the alias in the tables clause. I'll have a look.

Nicely presented bug report!

[Matthew Schinckel]

Confirmed that it's a problem with Subquery, and not just Exists.

However, I don't believe Subquery is doing anything with the alias: I don't know where the quoting is being added.

[Matthew Schinckel]

Okay, progress. It seems that the alias is not in compiler.query.external_aliases, but I think it should be.

[Matthew Schinckel]

So, I think I have a fix for this: it fixes this specific use case, but I'm at a loss as to if it will break something else.

PR is at ​https://github.com/django/django/pull/8115

[Tim Graham <timograham@…>]

In f48bc7c:

Fixed #27862 -- Fixed incorrectly quoted table aliases in Subquery SQL.

Add aliases from resolved querysets to the parent query's external
aliases to prevent those aliases from being quoted.

Thanks to Vasily Stepanov for the report and Tim Graham for the review.

[Tim Graham <timograham@…>]

In 2864bb3:

[1.11.x] Fixed #27862 -- Fixed incorrectly quoted table aliases in Subquery SQL.

Add aliases from resolved querysets to the parent query's external
aliases to prevent those aliases from being quoted.

Thanks to Vasily Stepanov for the report and Tim Graham for the review.

Backport of f48bc7c3dbd204eefb3c19016b1e4906ac26bee3 from master