NaN turns into NULL when written to column of type REAL

[kevin-dp]

I'm using prepared database queries to write real numbers (including +Infinity, -Infinity, and NaN) to a better-sqlite3 database. However, i stumbled on this strange edge-case for NaN:

import Database from 'better-sqlite3'
const db = new Database(':memory:')
this.db.exec('CREATE TABLE Foo (id INT PRIMARY KEY, real REAL)')
const stmt = this.db.prepare('INSERT INTO Foo (id, real) VALUES (?, ?) RETURNING id, real')

// All of this works
stmt.get([1, 5.0]) // { id: 1, real: 5 }
stmt.get([2, Infinity]) // { id: 2, real: Infinity }
stmt.get([3, -Infinity]) // { id: 3, real: -Infinity }

// Problem: writes `null` instead of `NaN`
stmt.get([4, NaN]) // { id: 4, real: null }

The example above shows that it is possible to write numbers (including Infinity and -Infinity) but fails for NaN. Instead of writing NaN it writes null to the database.

[Prinzhorn]

when written to column of type REAL

First of all there is no REAL column, you might as well do CREATE TABLE Foo (id INT PRIMARY KEY, real KITTENS) because SQLite by default does not have a concept of strict types.

From what I understand SQLite does not have a concept of NaN either so binding NaN via sqlite3_bind_double turns it into NULL.

I might be wrong, feel free to provide evidence of any other driver in other languages supporting NaN (without some higher level ORM-like workaround).

[kevin-dp]

@Prinzhorn yes i was wondering why Infinity and -Infinity work but NaN doesn't. I believe SQLite does not have a concept of any of those 3 values. However, the IEEE 754 standard does have a concept of all three. So i was expecting all 3 to be bound "correctly" or none of them.

[Prinzhorn]

I believe SQLite does not have a concept of any of those 3 values

 $ sqlite3
SQLite version 3.40.1 2022-12-28 14:03:47
sqlite> SELECT 1e999;
Inf

[Prinzhorn]

This is definitely SQLite behavior, see 3.5.9 https://www.sqlite.org/changes.html#version_3_5_9 release notes (via https://groups.google.com/g/sqlite_users/c/aQX_WvVwBS8)

Always convert IEEE floating point NaN values into NULL during processing. (Ticket #3060)

Go fight with the SQLite team over violating IEEE 754 but this doesn't appear to be a better-sqlite3 issue. Technically better-sqlite3 could throw when someone binds NaN, but that would alter SQLite default behavior and people relying on it (so one of the two groups would need to write special handling code). If you only want to store REAL then adding NOT NULL will give you an exception anyway.

[kevin-dp]

I believe SQLite does not have a concept of any of those 3 values

 $ sqlite3
SQLite version 3.40.1 2022-12-28 14:03:47
sqlite> SELECT 1e999;
Inf

Didn't know this, thanks! Makes sense in that case. We will need to do some processing to be able to work with NaN.