#27862: Exists() feature generates invalid SQL query on postgres backend
-------------------------------------+-------------------------------------
Reporter: Vasily | Owner: nobody
Stepanov |
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) | Keywords: Queryset SubQuery
Severity: Normal | Exists
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/27862>
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/057.815450e3388b85962a791c4a5c28f478%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
