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

Reply via email to