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:
===============================
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
