id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27862	Exists() feature generates invalid SQL query on postgres backend	Vasily Stepanov	nobody	"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 quotas and execute it manually."	Bug	new	Database layer (models, ORM)	master	Normal		Queryset SubQuery Exists		Unreviewed	0	0	0	0	0	0