Arithmetic errors
(1) By anonymous on 2020-07-15 05:46:46 [link]
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
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]
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]
[Well-trodden ground](http://sqlite.1065341.n5.nabble.com/Handling-of-IEEE-754-nan-and-inf-in-SQLite-td27874.html).
(5) By Keith Medcalf (kmedcalf) on 2020-07-15 13:06:47 in reply to 3 [link]
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]
THX for the explanation