Hi all,
I've found that an sql request that I expected to fail, but it didn't. On
the face of it that is good news but there is a potential downside. I wonder
if my expectation is wrong or if this is a bug which so far hasn't been
caught.
The problem deals with dividing by 0. As far as I can remember, in every
programming language that I have ever used and in all databases that I've
used, if you try and divide by 0 the process will fail with a 'divide by
zero' error. Sqlite doesn't seem to do that, it instead returns NULL.
Here is my test script.
select sqlite_version();
drop table t1;
create table t1
(col1 text not null
,col2 real not null
,col3 real not null);
insert into t1 values('A',1,0);
insert into t1 values('B',2,0);
insert into t1 values('C',3,0);
select * from t1;
select dtl.col1
,dtl.col2
,dtl.col2 / tots.tot_value as col_pct
from t1 as dtl
cross join (select sum(col2) as tot_value from t1) as tots;
select dtl.col1
,dtl.col2
,dtl.col2 / tots.tot_value as col_pct
from t1 as dtl
cross join (select sum(col3) as tot_value from t1) as tots;
select col1,col2 / col3
from t1;
And the output from running this using the sqlite shell program (v3.8.6.0)
is:
3.8.6
A|1.0|0.0
B|2.0|0.0
C|3.0|0.0
A|1.0|0.166666666666667
B|2.0|0.333333333333333
C|3.0|0.5
A|1.0|
B|2.0|
C|3.0|
A|
B|
C|
I also get the same behaviour under 3.8.4.3 and 3.17.6.2.
I've changed my original SQL where I found this to use NULLIF(col3,0) -
which is what I'd normally do if I might encounter this situation in SQL.
My concern is that this is a bug and if it should get fixed in a later build
then any code that I've got which inadvertently relies on this will then
fail.
Should trying to divide by 0 result in an error?
Many thanks,
Dave
Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: <http://www.ward-analytics.com> http://www.ward-analytics.com
Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
