On Aug 4, 2009, at 4:58 AM, Pavlos Christoforou wrote: > Hello all, > > We are currently evaluating sqlite for using it as the > base engine for a financial reporting module. We have some > fairly complex queries which yield strange results. We have > tried to isolate the problem below. Please see select queries > at the end and associated comments which detail the issue:
Which version are you using? The output of running this script with 3.6.16 here is: x|cum_sum_x|total 10.0|10.0|110.0 10.0|20.0|110.0 20.0|40.0|110.0 30.0|70.0|110.0 40.0|110.0|110.0 x|cum_sum_x|total 10.0|10.0|110.0 10.0|20.0|110.0 20.0|40.0|110.0 30.0|70.0|110.0 40.0|110.0|110.0 sum(x) 110.0 sum(x) 110.0 SQL error near line 64: misuse of aggregate: max() Dan. > =============================== > > create table Test (x FLOAT); > > insert into Test values (10); > insert into Test values (10); > insert into Test values (20); > insert into Test values (30); > insert into Test values (40); > > > create view TestViewWeird > as > select max(t1.x) as x, > sum(t2.x) as cum_sum_x, > -- bad sql below but is should yield consistent results IMO > (select sum(x) from Test) as total > from Test as t1 inner join Test as t2 > on t2.oid <= t1.oid > -- only difference is expression below > and total * t1.x > 0 > -- > group by t1.oid > ; > > create view TestView > as > select max(t1.x) as x, > sum(t2.x) as cum_sum_x, > -- bad sql below but is should yield consistent results IMO > (select sum(x) from Test) as total > from Test as t1 inner join Test as t2 > on t2.oid <= t1.oid > -- only difference is expression below > and (select sum(x) from Test) * t1.x > 0 > -- > group by t1.oid > ; > > > create view TestViewSegFault as > select max(t1.x) as x, > sum(t2.x) as cum_sum_x, > -- only difference is expression below > max((select sum(x) from Test)) as total > from Test as t1 inner join Test as t2 > on t2.oid <= t1.oid > and total * t1.x > 0 > -- > group by t1.oid > ; > > > .headers on > > -- below queries yield exactly the same results > select * from TestView; > select * from TestViewWeird; > > -- the first query below returns a value but the second returns a > null. > select sum(x) from TestView; > select sum(x) from TestViewWeird; > > -- below seg faults > select * from TestViewSegFault; > > ========================================== > > what are we doing wrong? Any help or pointers to appropriate docs > will be greatly appreciated. > > Thanks > > Pavlos > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
