[Django] #27862: Exists() feature generates invalid SQL query on postgres backend
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
Old description:
New description:
SQL works as expected, if you remove these quotes and execute it manually.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: Queryset SubQuery | Triage Stage: Accepted
Exists |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: master => 1.11
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: Queryset SubQuery | Triage Stage: Accepted
Exists |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Matthew Schinckel (added)
Comment:
Matt, are you interested in looking into this?
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:3>
Django
-------------------------------------+-------------------------------------
| Schinckel
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: Queryset SubQuery | Triage Stage: Accepted
Exists |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => Matthew Schinckel
Comment:
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!
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:4>
Django
-------------------------------------+-------------------------------------
| Schinckel
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: Queryset SubQuery | Triage Stage: Accepted
Exists |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:5>
Django
-------------------------------------+-------------------------------------
| Schinckel
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Exists |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: Queryset SubQuery Exists => Queryset Subquery Exists
Comment:
Okay, progress. It seems that the alias is not in
`self.query.external_aliases`, but I think it should be.
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:6>
Django
-------------------------------------+-------------------------------------
| Schinckel
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: Queryset Subquery | Triage Stage: Accepted
Exists |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
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
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:7>
Django
-------------------------------------+-------------------------------------
| Schinckel
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution:
Exists | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:8>
Django
-------------------------------------+-------------------------------------
| Schinckel
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: Queryset Subquery | Triage Stage: Ready for
Exists | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"f48bc7c3dbd204eefb3c19016b1e4906ac26bee3" f48bc7c]:
{{{
#!CommitTicketReference repository=""
revision="f48bc7c3dbd204eefb3c19016b1e4906ac26bee3"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:9>
Django
-------------------------------------+-------------------------------------
| Schinckel
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: Queryset Subquery | Triage Stage: Ready for
Exists | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"2864bb3ba6576cbd2676052344a445a6616a6679" 2864bb3]:
{{{
#!CommitTicketReference repository=""
revision="2864bb3ba6576cbd2676052344a445a6616a6679"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27862#comment:10>