On 4/24/16, Jean-Luc Hainaut <jean-luc.hainaut at unamur.be> wrote:
> select PID,TOTALQ
> from (select PID, sum(QTY) as TOTALQ
> from D
> group by PID
> union
> select PID, 0 as TOTALQ
> from P
> where PID not in (select PID from D)
> )
> where TOTALQ < 10
> order by PID;
>
> With SQLite 3.10 and 3.12.2 the query fails with the message:
>
> "Error: misuse of agregate: sum()"
>
Your work-around until the problem is fixed (probably in SQLite
version 3.13.0) is to put the aggregate part of the compound subquery
last instead of first; like this:
select PID,TOTALQ
from (select PID, 0 as TOTALQ
from P
where PID not in (select PID from D)
union
select PID, sum(QTY) as TOTALQ
from D
group by PID
)
where TOTALQ < 10
order by PID;
The new optimization that leads to this problem is suppose to be
disabled if the subquery is an aggregate.
(https://www.sqlite.org/src/artifact/30217121bd?ln=3759-3763).
However, it appears that the test for whether or not the subquery is
an aggregate (https://www.sqlite.org/src/artifact/30217121bd?ln=3789)
is only looking at the last SELECT in the compound query.
--
D. Richard Hipp
drh at sqlite.org
