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.