How do I select floating-point infinity literals from a SQLite database?-CodePudding

I have a SQLite database with a table named measurements, which contains some experimental data measured by a power meter. They're stored as data type REAL in a field named power_dbm. In some measurements, the readings can be off the scale, the meter cannot obtain a reading due to overrange or underrange, thus the values Inf and -Inf are used to represent these values.

For example, here is some of the data.

sqlite> SELECT power_dbm, typeof(power_dbm) from measurements;
-10.312|real
-9.908|real
-11.344|real
-Inf|real

As you see, there's a floating-point number of negative infinity. Unfortunate I don't know the correct syntax to select these values from the database. I tried:

sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm=-Inf;
Error: no such column: Inf

sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm='-Inf'
(nothing returned)

sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm='-infinity';
(nothing returned)

sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm=-1/0;
(nothing returned)

sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm is -1/0;
(incorrect results returned, power_dbm is null instead of negative infinity)

What is the correct syntax to specify the literal value of positive or negative infinity in SQL/SQLite? Any additional references to relevant documentation will also greatly be appreciated.

CodePudding user response：

Solution 1: Use an impossibly-huge floating-point literal

I found writing an impossibly-huge floating-point literal such as 9e999 is one way to construct Inf/-Inf.

sqlite> SELECT 9e999;
Inf

sqlite> SELECT -9e999;
-Inf

sqlite> SELECT freq_hz, power_dbm, typeof(power_dbm) FROM measurements
   ...> WHERE power_dbm=-9e999;
370000000|-Inf|real
440000000|-Inf|real
510000000|-Inf|real
580000000|-Inf|real

Solution 2: Use `ieee754()` with hardcoded exponent/significand for Infinity

You can construct Inf and -Inf manually from an exponent and significand in the IEEE 754 representation via function ieee754(). This is the original solution I used before I found the 9e999 trick...

sqlite> SELECT ieee754(4503599627370496, 972);
Inf

sqlite> SELECT ieee754(-4503599627370496, 972);
-Inf

sqlite> SELECT freq_hz, power_dbm, typeof(power_dbm) FROM measurements
   ...> WHERE power_dbm=ieee754(-4503599627370496, 972);
370000000|-Inf|real
440000000|-Inf|real
510000000|-Inf|real
580000000|-Inf|real

The detailed usage of ieee754() and other related functions is documented in Floating Point Numbers.

Solution 3: Use Parameterized SQL queries

Using ieee754(-4503599627370496, 972) feels like a more rigorous construction than a random value like 9e999, but 9e999 is adequate for typing commands in a console by hand when all you want is having a quick look of the data, so correctness is not a real concern.

In an actual program, one would be using parameterized SQL queries instead of handling strings anyway, simply passing the native Infinity constant (e.g. Python's math.inf) in your programming language is sufficient.

$ python3
Python 3.9.9 (main, Nov 19 2021, 00:00:00) 
[GCC 10.3.1 20210422 (Red Hat 10.3.1-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import math
>>> import sqlite3
>>> DB_FILE = "./data.db"
>>> con = sqlite3.connect(DB_FILE)
>>> cur = con.cursor()
>>> cur.execute(
...     "SELECT power_dbm from measurements "
...     "WHERE power_dbm=?", (-math.inf,)).fetchall()
[(-inf,), (-inf,), (-inf,), (-inf,)]

Solution 1: Use an impossibly-huge floating-point literal

Solution 2: Use ieee754() with hardcoded exponent/significand for Infinity

Solution 3: Use Parameterized SQL queries

Solution 2: Use `ieee754()` with hardcoded exponent/significand for Infinity