extra() field overwritten when using union on two querysets

[dryice]

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!

[Tim Graham]

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".

[dryice]

Thanks! It's great to hear something better is planned :)

[Tim Graham]

We are no longer fixing bugs with QuerySet.extra() per ​discussion on django-developers.