Hello,
When executing a set of queries written some years ago (let's call it a
"regression test"!), I found that one of them now fails with a strange message.
It executes correctly until version 3.8.5 (perhaps later) but fails from
version 3.10 (perhaps earlier).
I have simplified the problem as follow:
- table P(PID) represents products,
- table D(PID,QTY) represents order details (PID identifies a product
and QTY specifies the quantity ordered of this product).
In SQL:
create table P(PID integer not null primary key);
create table D(PID integer not null references P,
QTY integer not null);
insert into P values (1),(2),(3),(4);
insert into D values (1,5),(1,10),(3,6),(3,2),(4,12);
The following query computes, for each product, the sum of quantities ordered.
It also includes quantity 0 for products not referenced by D:
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)
)
order by PID;
As expected, it provides, through the SQLite3 shell:
1|15
2|0
3|8
4|12
The problem arises when we add a "where" clause involving computed column
TOTALQ:
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()"
while with SQLite 3.8.5, it provides the correct answer:
2|0
3|8
Rewriting the "from" clause as a "with" query or creating a view (with and
without the problematic "where" clause) then querying show the same behaviour.
It also appears that removing the second argument of the union "solves" the
problem.
Has anybody observed this problem?
Thanks for future help
Jean-Luc Hainaut
Prof. Jean-Luc Hainaut
Facult? d'Informatique
University of Namur
Rue Grandgagnage, 21
B-5000 - Namur (Belgium)
E-mail : jlhainaut at info.fundp.ac.be, jean-luc.hainaut at unamur.be
http://www.info.fundp.ac.be/libd
