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