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 [link] [source]
(5) By Keith Medcalf (kmedcalf) on 2020-07-15 13:06:47 in reply to 3 [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