#27862: Exists() feature generates invalid SQL query on postgres backend
-------------------------------------+-------------------------------------
Reporter: Vasily Stepanov | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Queryset SubQuery | Triage Stage:
Exists | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Vasily Stepanov:
Old description:
> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/072.0d8bfcbb7b71b39c92db295ed31e36b1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
