Bug? INSERT ... RETURNING ... with a REAL column exposes small integer optimization
(1) By Austin (austin_launchbadge) on 2021-12-29 00:33:52 [source]
I'm a maintainer of SQLx, a SQL client for Rust.
We recently had an issue opened on our tracker that initially looked to be a bug on our end, but further investigation has led me to believe that it may be a bug in SQLite: launchbadge/sqlx#1596
Essentially, I think the issue boils down to the new
INSERT ... RETURNING ... feature not respecting the following guarantee regarding columns with a
REAL type affinity: SQLite Data Types: Type Affinity
A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)
For example, given the following table:
CREATE TABLE foo(bar REAL);
If you have an
INSERT statement like such:
INSERT INTO foo(bar) VALUES (5.0);
SELECT that value back:
SELECT bar FROM foo;
sqlite3_column_type(stmt, 0) correctly reports
SQLITE_FLOAT for that statement.
However, if you do:
INSERT INTO foo(bar) VALUES (5.0) RETURNING bar;
sqlite3_column_type(stmt, 0) incorrectly (?) reports
Similarly, we also use
sqlite3_value_type() in certain cases which reports the same. I'm guessing however that there is a single underlying cause for both.
Thanks for the bug report.
Your analysis is correct - SQLite was holding the value internally as an integer so that it could write it out as an integer into the B-Tree, but then it failed to convert it back to a floating-point number prior to returning it. The problem should be fixed by check-in 4711fb69547f4f17.