[sqlite] Is this a regression?
On 4/24/16, Richard Hipp wrote: > > The problem is apparently caused by the optimization added by check-in > https://www.sqlite.org/src/info/6df18e949d367629 on 2015-06-02 and > first released in version 3.8.11 on 2015-07-27. > Ticket: https://www.sqlite.org/src/info/f7f8c97e97597 Fix: https://www.sqlite.org/src/info/ec215f94ac9748c0 -- D. Richard Hipp drh at sqlite.org
[sqlite] Is this a regression?
On 4/24/16, Jean-Luc Hainaut 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
[sqlite] Is this a regression?
On 4/24/16, Jean-Luc Hainaut wrote: > > 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). > > > Has anybody observed this problem? > As far as we know, you are the first to observer this problem. The problem is apparently caused by the optimization added by check-in https://www.sqlite.org/src/info/6df18e949d367629 on 2015-06-02 and first released in version 3.8.11 on 2015-07-27. -- D. Richard Hipp drh at sqlite.org
[sqlite] Is this a regression?
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
