SQLite Forum

Bug? INSERT ... RETURNING ... with a REAL column exposes small integer optimization
Login

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);

And then SELECT that value back:

SELECT bar FROM foo;

Then 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;

Then sqlite3_column_type(stmt, 0) incorrectly (?) reports SQLITE_INTEGER.

Similarly, we also use sqlite3_column_value() and sqlite3_value_type() in certain cases which reports the same. I'm guessing however that there is a single underlying cause for both.

(2) By Richard Hipp (drh) on 2021-12-29 04:34:26 in reply to 1 [link] [source]

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.

(3) By Austin (austin_launchbadge) on 2022-09-13 22:03:24 in reply to 2 [link] [source]

I believe this issue may have regressed or perhaps the fix was incomplete as we just got a bug report for SQLx 0.6.1 (linking SQLite 3.38.0) that has the same problem: https://github.com/launchbadge/sqlx/issues/2093

To confirm, I just grabbed the latest binary release from the downloads page (3.39.2) and executed the following two commands:

> ./sqlite3
SQLite version 3.39.3 2022-09-05 11:02:23
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE data_table(id INTEGER PRIMARY KEY AUTOINCREMENT, number REAL);
sqlite> INSERT INTO data_table (number) VALUES (1.0) RETURNING id, number;
1|1