#24142: extra() field overwritten when using union on two querysets
----------------------------------------------+--------------------
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.
--
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/049.f570584151086be05e1fcea32b1e9bbd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
