Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#27862 closed Bug (fixed)

Exists() feature generates invalid SQL query on postgres backend

Reported by: Vasily Stepanov Owned by: Matthew Schinckel
Component: Database layer (models, ORM) Version: 1.11
Severity: Release blocker Keywords: Queryset Subquery Exists
Cc: Matthew Schinckel Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (10)

comment:1 Changed 2 years ago by Vasily Stepanov

Description: modified (diff)

comment:2 Changed 2 years ago by Simon Charette

Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted
Version: master1.11

comment:3 Changed 2 years ago by Tim Graham

Cc: Matthew Schinckel added

Matt, are you interested in looking into this?

comment:4 Changed 2 years ago by Matthew Schinckel

Owner: changed from nobody to Matthew Schinckel
Status: newassigned

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!

comment:5 Changed 2 years ago 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.

comment:6 Changed 2 years ago by Matthew Schinckel

Keywords: Subquery added; SubQuery removed

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

Last edited 2 years ago by Matthew Schinckel (previous) (diff)

comment:7 Changed 2 years ago by Matthew Schinckel

Has patch: set

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

comment:8 Changed 2 years ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:9 Changed 2 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

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.

comment:10 Changed 2 years ago by 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

Note: See TracTickets for help on using tickets.
Back to Top