SQL Command to Insert Inf, NAN and -Inf into sqlite3 database

Question

What is the sql command to insert infinity, negative infinity, and not a number into a real type column in sqlite3 database tables?

i.e what would be in place of value1, value2?

INSERT INTO table (column1,column2 ,..)
VALUES( value1, value2 ,...);

I have tried finding resources for this on the sqlite3 documentation but was unable to find anything.

Inserting "Inf" and "-Inf" as a string and reading it in via C++ gives me a small -0.00... value

Answer 1

Any number that's outside the range of double-precision floats is automatically converted to ±infinity, so e.g. 1e999 will work as literal value for infinity:

sqlite> create table foo(x real);
sqlite> insert into foo values (-1e999),(-1e308),(0),(1e308),(1e999);
sqlite> .mode quote
sqlite> select * from foo order by x;
-Inf
-9.9999999999999996322e+307
0.0
9.9999999999999996322e+307
Inf

Note that these are actual numeric ±infinity values. Text strings would be sorted after numbers, and they would be quoted in output mode quote:

sqlite> insert into foo values ('Inf'),('-Inf');
sqlite> select * from foo order by x;
-Inf
-9.9999999999999996322e+307
0.0
9.9999999999999996322e+307
Inf
'-Inf'
'Inf'

I'm not sure however whether you can actually store a NaN. At the very least it appears that any numeric expression that would evaluate to NaN gets implicitly converted to NULL:

sqlite> select (0*1e999);
NULL
sqlite> select (1e999-1e999);
NULL

though I'm not sure whether that also happens if you directly provide a NaN via the C API.

UPDATE: I've checked the source code. SQLite always converts NaN values to NULL, specifically:

• when the result of floating-point arithmetic is NaN
• when NaN is bound to a parameter using sqlite3_bind_double()
• when a custom SQL function returns NaN using sqlite3_result_double()
• when deserializing a double from the database
• when a NaN literal is encountered while parsing JSON5