SQLite Forum

Arithmetic errors
Login

Arithmetic errors

(1) By anonymous on 2020-07-15 05:46:46 [link] [source]

Open an in-memory database;
Execute, for example, select 5/0;
SQLite will not spit out an appropriate error message.

(2) By Keith Medcalf (kmedcalf) on 2020-07-15 11:18:17 in reply to 1 [link] [source]

The result of 5/0 is a NaN (Not A Number). SQLite3 returns NULL for operations that result in NaN.

(3) By anonymous on 2020-07-15 12:20:35 in reply to 2 [link] [source]

Which is correct because the result is undefined. But if there is no error shown then how could I test for this condition? For example like:

 select case isnumber(5/0) when 0 then 'NaN' else 5/0  end;      

Which is not possible because there is no function like isnumber(). Are there alternatives?

(4) By Warren Young (wyoung) on 2020-07-15 12:48:17 in reply to 3 [source]

(5) By Keith Medcalf (kmedcalf) on 2020-07-15 13:06:47 in reply to 3 [link] [source]

use the builtin typeof() function. This returns the type of the argument: null, text, blob, integer, real as a text string.

sqlite> select typeof(a), a from (select 5/0 as a);
┌───────────┬───┐
│ typeof(a) │ a │
├───────────┼───┤
│ null      │   │
└───────────┴───┘

Alternatively, simply see if it is null:

sqlite> select case when a is null then 'NaN' else a end from (select 5/0 as a);
┌───────────────────────────────────────────┐
│ case when a is null then 'NaN' else a end │
├───────────────────────────────────────────┤
│ NaN                                       │
└───────────────────────────────────────────┘

Or even more simply using the ifnull builtin function:

sqlite> select ifnull(a,'NaN') from (select 5/0 as a);
┌─────────────────┐
│ ifnull(a,'NaN') │
├─────────────────┤
│ NaN             │
└─────────────────┘

Except, of course, that NULL is a valid value in further arithmetic calculations that will be handled correctly, whereas the text string 'NaN' is not.

(6) By anonymous on 2020-07-16 07:34:53 in reply to 5 [link] [source]

THX for the explanation