#28296: Subquery with annotation fails without tweaking
-------------------------------------+-------------------------------------
Reporter: László Károlyi | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by László Károlyi):
Hello Simon, thanks for getting back to me.
Replying to [comment:1 Simon Charette]:
> It's not clear to me why you not expecting a `GROUP BY` statement to be
present in the subquery when you are explicitly grouping by `id` by using
`values('id')` before annotating. Please see the section about aggregation
and the usage of `values()` to `GROUP BY` in the
[https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#order-of-
annotate-and-values-clauses aggregation documentation].
The reason for why I needed to get rid of the `GROUP BY` clause was
because it broke the `COUNT()` on the SQL level. I recognized this after
going down to the SQL level and fiddling around with native SQL queries to
find out why I'm getting `NULL` values at the counts. So here's a
simplified version how the clause with `GROUP BY` looks, and what results
it brings, copying from the MariaDB command line:
{{{#!sql
MariaDB [ticketshop]> select ticketshop_event_performance.id, (select
count(u0.`id`) as count from ticketshop_booking_seatreservation u0 where
(ticketshop_event_performance.id=(u0.performance_id)) and status='xxx'
group by u0.id) as count from ticketshop_event_performance limit 1;
+-----+-------+
| id | count |
+-----+-------+
| 134 | NULL |
+-----+-------+
1 row in set (0.00 sec)
}}}
As you can see, it returns `NULL` in the count column. And so I removed
the `GROUP BY` to see if it works that way:
{{{#!sql
MariaDB [ticketshop]> select ticketshop_event_performance.id, (select
count(u0.`id`) as count from ticketshop_booking_seatreservation u0 where
(ticketshop_event_performance.id=(u0.performance_id)) and status='xxx') as
count from ticketshop_event_performance limit 1;
+-----+-------+
| id | count |
+-----+-------+
| 134 | 0 |
+-----+-------+
1 row in set (0.00 sec)
}}}
See, it's returning proper values now. So that is why I went back to the
ORM level and played around with it until I managed to remove the `GROUP
BY` clause from the subquery. Basically, empirical testing.
Also it's worth mentioning that when I managed to remove the `GROUP BY` on
the ORM level, executing the query without specifying the output field
resulted in an exception when evaluating the query the first time, but not
the second time:
{{{#!python
In [8]: from ticketshop.booking.choices import TAKEN_TYPES
In [9]: rq = SeatReservation.objects.filter(
...: performance=OuterRef(name='pk'),
status__in=TAKEN_TYPES
...: ).values('id').annotate(count=Count('id')).values('count')
In [10]: rq.query.group_by = []
In [11]: a = Performance.objects.annotate(count=Subquery(rq))
In [12]: a[0].count
### LOTS OF TRACEBACK CUT
# ~/Work/venv/lib/python3.6/site-packages/django/db/models/expressions.py
in _resolve_output_field(self) line 280:
# FieldError: Expression contains mixed types. You must set output_field
In [13]: a[0].count
Out[13]: 0
}}}
> Also, is there a reason you are not simply using
[https://docs.djangoproject.com/en/1.11/ref/models/conditional-expressions
/#conditional-aggregation conditional aggregation] for this query instead?
>
> {{{#!python
> Performance.objects.annotate(
> reserved_seats=Count(
> Case(When(seat_reservations__status__in=TAKEN_TYPES,
then='seat_reservations')),
> ),
> )
> }}}
>
> Note that I'm assuming `SeatReservation.performance.related_name ==
'seat_reservations'` above.
Thanks for the hint, I wasn't aware of this method, I'll look into it
later.
--
Ticket URL: <https://code.djangoproject.com/ticket/28296#comment:2>
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/065.79f19eb4d6db721fcb1347807cead193%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
