[Django] #23605: ORM neglects to use aliases it has set up when certain multiple subqueries are used
Django
are used
----------------------------------------------+----------------------------
Reporter: ris | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords: orm subquery
Triage Stage: Unreviewed | alias
Easy pickings: 0 | Has patch: 0
| UI/UX: 0
----------------------------------------------+----------------------------
Using django git 9d7a4ea20510a2e35fdeac21d67f3f4c17634c25
Example models.py:
{{{
from django.db import models
class ModelA ( models.Model ):
pass
class ModelB ( models.Model ):
modela_fk = models.ForeignKey ( ModelA )
modelc_fk = models.ForeignKey ( "ModelC" )
field_b0 = models.IntegerField ( null = True )
field_b1 = models.BooleanField ()
class ModelC ( models.Model ):
field_c0 = models.FloatField ()
}}}
Given the following query (yes, totally redundant subclauses noted):
{{{
ModelA.objects.filter (
Q ( pk__in = ModelA.objects.filter ( Q ( modelb__field_b0__gte =
1000000 / F ( "modelb__modelc_fk__field_c0" ) )
& Q ( modelb__field_b1__exact = True )
& ~Q ( modelb__pk__in = ModelB.objects.filter (
~(
Q ( field_b1__exact = True )
& Q ( field_b0__gte = 1000000 / F ( "modelc_fk__field_c0"
) )
)
) )
).filter ( modelb__field_b1__exact = True )
)
}}}
Used against postgres 9.1 generates the error:
{{{
ProgrammingError: invalid reference to FROM-clause entry for table
"dummy_modelb"
LINE 1: ...") AND U0."field_b0" IS NOT NULL)) AND V1."id" = ("dummy_mod...
^
HINT: Perhaps you meant to reference the table alias "v1".
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/23605>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm subquery alias | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by timgraham):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
Did it work in older versions of Django? If so, could you bisect to the
commit in Django where things broke?
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:1>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm subquery alias | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
Using django git 9d7a4ea20510a2e35fdeac21d67f3f4c17634c25
Example models.py:
{{{
from django.db import models
) )
}}}
Used against postgres 9.1 generates the error:
{{{
ProgrammingError: invalid reference to FROM-clause entry for table
"dummy_modelb"
LINE 1: ...") AND U0."field_b0" IS NOT NULL)) AND V1."id" = ("dummy_mod...
^
HINT: Perhaps you meant to reference the table alias "v1".
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:2>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm subquery alias | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by ris):
(added missing trailing bracket in query in description)
Interestingly, switch the order of the .filters in the outermost subquery,
making it:
{{{
ModelA.objects.filter (
Q ( pk__in = ModelA.objects.filter ( modelb__field_b1__exact = True
).filter (
Q ( modelb__field_b0__gte = 1000000 / F (
"modelb__modelc_fk__field_c0" ) )
& Q ( modelb__field_b1__exact = True )
& ~Q ( modelb__pk__in = ModelB.objects.filter (
~(
Q ( field_b1__exact = True )
& Q ( field_b0__gte = 1000000 / F ( "modelc_fk__field_c0"
) )
)
) )
)
) )
}}}
and it works fine.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:3>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm subquery alias | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by ris):
(haven't tried older versions of django yet)
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:4>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm subquery alias | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by ris):
For what it's worth, this works in django 1.4 (ancient I know, but the
last version we have in production right now).
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:5>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm subquery alias | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akaariai):
Smells like relabeled_clone problem. I'll investigate.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:6>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm subquery alias | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by ris):
From IRC:
{{{
<akaariai_> it is a bit hard to see if I am fixing the query to work as in
PostgreSQL can parse the query, or actually produce correct results
<ris> akaariai_: well, the inner double-NOT'ed subquery is to test for a
ModelA object _all of whose_ ModelB objects conform to certain criteria
<ris> those criteria being
<ris> Q ( modelb__field_b0__gte = 1000000 / F (
"modelb__modelc_fk__field_c0" ) ) & Q ( modelb__field_b1__exact = True )
<ris> akaariai_: so it's saying "ModelA instances who have a ModelB
instance that complies to X but _not_ ModelA instances that have any
ModelB instances that _dont_ comply to X" (X = Q ( modelb__field_b0__gte =
1000000 / F ( "modelb__modelc_fk__field_c0" ) ) & Q (
modelb__field_b1__exact = True ) here)
<ris> akaariai_: hence the odd double-negative-wrapped-subquery
<ris> the outermost subquery .filter ( modelb__field_b1__exact = True ) is
exactly the sort of appendage I'd like to be able to remove if I were able
to test for Q-equality
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:7>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: orm subquery alias | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akaariai):
The problem was that django.db.models.sql.Query didn't have
relabeled_clone() method. See PR3323 for proposed fix.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:8>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Keywords: orm subquery alias | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:9>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by ris):
(hooray! thank you)
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:10>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by ris):
Hmm.. PR3323 doesn't fix this for me. Instead I get
{{{
ProgrammingError: missing FROM-clause entry for table "W1"
LINE 1: ...") AND U0."field_b0" IS NOT NULL)) AND V1."id" = ("W1"."id")...
}}}
Possibly related: another old bug of mine: #18726
(don't know whether this discussion should continue here or on the pull
request)
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:11>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
Comment:
Yes, the test fails on PostgreSQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:12>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
The problem is that W1 must not be quoted, but this doesn't work correctly
in the patched version. The alias W1 comes from the outer query, so the
inner query doesn't know it is an alias and hence it gets quoted. I'll
check how ugly a fix for this will be.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:13>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
Comment:
Another try.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:14>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by ris):
Hmm latest version of PR3323 still fails buildbot test on postgres.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:15>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:16>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.7
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"5c481db29572a387651681b43d5d4523f96b3793"]:
{{{
#!CommitTicketReference repository=""
revision="5c481db29572a387651681b43d5d4523f96b3793"
Fixed #23605 -- Fixed nested subquery regression
Added relabeled_clone() method to sql.Query to fix the problem. It
manifested itself in rare cases where at least double nested subquery's
filter condition might target non-existing alias.
Thanks to Trac alias ris for reporting the problem.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:17>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.7
(models, ORM) | Resolution: fixed
Severity: Normal | Triage Stage: Accepted
Keywords: orm subquery alias | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"01f2cf2aecc932d43b20b55fc19a8fa440457b5f"]:
{{{
#!CommitTicketReference repository=""
revision="01f2cf2aecc932d43b20b55fc19a8fa440457b5f"
[1.7.x] Fixed #23605 -- Fixed nested subquery regression
Added relabeled_clone() method to sql.Query to fix the problem. It
manifested itself in rare cases where at least double nested subquery's
filter condition might target non-existing alias.
Thanks to Trac alias ris for reporting the problem.
Backport of 5c481db29572a387651681b43d5d4523f96b3793 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:18>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: orm subquery alias | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by ris):
Hmm. That's interesting. Just upgraded to 1.7.2 and the test case posted
here is indeed fixed. However it doesn't fix ''our'' specific test case
that this test was distilled from.
So that means somewhere in the distillation from our test case to this
abstract one there is another bug lurking.
Should probably open it as another bug if I can pin this one down to
another failing test case.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:19>
Django
are used
Reporter: ris | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.7
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: orm subquery alias | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by ris):
Ok, I've created ticket #24090 covering the new variant of this bug.
--
Ticket URL: <https://code.djangoproject.com/ticket/23605#comment:20>