Thanks Dan much appreciated We are using 3.6.2 (the standard version on our ubuntu machines). We will upgrade to 3.6.16
Regards Pavlos ----- "Dan Kennedy" <danielk1...@gmail.com> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
