[Django] #24142: extra() field overwritten when using union on two querysets
Django
----------------------------------------------+--------------------
Reporter: dryice | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Here's how to reproduce it:
- follow step 1 and step 2 of the tutorial to make the Question and Choice
model
- fill in the DB with data:
polls_question;
||= id =||= question_text =||= pub_date =||
|| 1||111||2015-01-13 06:05:58 ||
|| 2||222||2015-01-13 06:06:09 ||
|| 3||333||2015-01-13 06:06:17 ||
polls_choice;
|| id =||= choice_text =||= votes =||= question_id =||
|| 1||1aa||3||1 ||
|| 2||1bb||8||1 ||
|| 3||2aa||9||2 ||
|| 4||2bb||3||2 ||
|| 5||3aa||4||3 ||
|| 6||3bb||2||3 ||
- in the Django shell:
{{{
>>> qs = Question.objects.all()
>>> qs1 =
qs.filter(choice__votes__gte=5).annotate(choice_count=Count('choice'))
>>> for i in qs1:
... print i, i.choice_count
...
111 1
222 1
>>> qs2 = qs.exclude(id__in=qs1.values_list("id",
flat=True)).extra(select={'choice_count': '1-1'})
>>> for i in qs2:
... print i, i.choice_count
...
333 0
>>> for i in qs1 | qs2:
... print i, i.choice_count
...
111 1
222 1
333 2
}}}
Note in the last line, I would expect
{{{333 0}}}
while it gives {{{333 2}}}
Checking the SQL used:
{{{
>>> qs3 = qs1 | qs2
>>> print qs1.query
SELECT "polls_question"."id", "polls_question"."question_text",
"polls_question"."pub_date", COUNT("polls_choice"."id") AS "choice_count"
FROM "polls_question" INNER JOIN "polls_choice" ON ( "polls_question"."id"
= "polls_choice"."question_id" ) WHERE "polls_choice"."votes" >= 5 GROUP
BY "polls_question"."id", "polls_question"."question_text",
"polls_question"."pub_date"
>>> print qs2.query
SELECT (1-1) AS "choice_count", "polls_question"."id",
"polls_question"."question_text", "polls_question"."pub_date" FROM
"polls_question" WHERE NOT ("polls_question"."id" IN (SELECT U0."id" FROM
"polls_question" U0 INNER JOIN "polls_choice" U1 ON ( U0."id" =
U1."question_id" ) WHERE U1."votes" >= 5 GROUP BY U0."id",
U0."question_text", U0."pub_date"))
>>> print qs3.query
SELECT (1-1) AS "choice_count", "polls_question"."id",
"polls_question"."question_text", "polls_question"."pub_date",
COUNT("polls_choice"."id") AS choice_count FROM "polls_question" LEFT
OUTER JOIN "polls_choice" ON ( "polls_question"."id" =
"polls_choice"."question_id" ) WHERE ("polls_choice"."votes" >= 5 OR NOT
("polls_question"."id" IN (SELECT U0."id" FROM "polls_question" U0 INNER
JOIN "polls_choice" U1 ON ( U0."id" = U1."question_id" ) WHERE U1."votes"
>= 5 GROUP BY U0."id", U0."question_text", U0."pub_date"))) GROUP BY
"polls_question"."id", "polls_question"."question_text",
"polls_question"."pub_date", (1-1)
}}}
It looks the "|" operator didn't check if there's extra field with the
same name on the two sides, and
{{{COUNT("polls_choice"."id") AS choice_count}}}
overwrote
{{{SELECT (1-1) AS "choice_count"}}}
I understand once we started using extra() we are on the edge. But
maybe this is something that could be fixed? Thanks!
--
Ticket URL: <https://code.djangoproject.com/ticket/24142>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: dryice | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
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
* stage: Unreviewed => Accepted
* needs_tests: => 0
* needs_docs: => 0
Comment:
Maybe it can be fixed, but the latest thinking I've heard is that we're
trying to make the functionality of `extra()` available through better
APIs so we can deprecate it, so please don't be surprised if this gets a
"won't fix".
--
Ticket URL: <https://code.djangoproject.com/ticket/24142#comment:1>
Django
Reporter: dryice | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by dryice):
Thanks! It's great to hear something better is planned :)
--
Ticket URL: <https://code.djangoproject.com/ticket/24142#comment:2>
Django
Reporter: dryice | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* keywords: => QuerySet.extra
--
Ticket URL: <https://code.djangoproject.com/ticket/24142#comment:3>
Django
Reporter: dryice | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.7
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => wontfix
Comment:
We are no longer fixing bugs with `QuerySet.extra()` per
[https://groups.google.com/d/topic/django-
developers/FojuU0syO8Y/discussion discussion on django-developers].
--
Ticket URL: <https://code.djangoproject.com/ticket/24142#comment:4>