[SQLite] INSERT ... RETURNING ... with a REAL column reports a type error in some cases

[glommer]

I have hit the following backtrace while calling row.get<f64, _> on an AnyRow over the results of a SQLite query:

thread '<unnamed>' panicked at 'not implemented', /Users/glauber/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.5.7/src/any/types.rs:28:1
stack backtrace:
   0: rust_begin_unwind
             at /rustc/f1edd0429582dd29cccacaf50fd134b05593bd9c/library/std/src/panicking.rs:517:5
   1: core::panicking::panic_fmt
             at /rustc/f1edd0429582dd29cccacaf50fd134b05593bd9c/library/core/src/panicking.rs:100:14
   2: core::panicking::panic
             at /rustc/f1edd0429582dd29cccacaf50fd134b05593bd9c/library/core/src/panicking.rs:50:5
   3: sqlx_core::any::types::<impl sqlx_core::types::Type<sqlx_core::any::database::Any> for f64>::type_info
             at /Users/glauber/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.5.7/src/any/type.rs:12:17
   4: sqlx_core::error::mismatched_types
             at /Users/glauber/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.5.7/src/error.rs:143:9
   5: sqlx_core::row::Row::try_get
             at /Users/glauber/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.5.7/src/row.rs:124:29
   6: sqlx_core::row::Row::get
             at /Users/glauber/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.5.7/src/row.rs:73:9
   7: chisel_server::query::engine::relational_row_to_json

The query is an INSERT INTO with a RETURNING clause and the sqlite table has the following schema:

CREATE TABLE `table_name` ( `id` text UNIQUE PRIMARY KEY, `first_name` text, `last_name` text, `age` integer, `human` integer, `height` real );

What makes me quite certain that this is a bug is that everything works well if height is 5.11 but it fails if it is 5.

I confirmed that in the sqlite shell, the values do show up as 5.0 and therefore a real.

I can call try_get_unchecked and convert to a string, and then I see that the string is printed as "5" (without the dot)

[espindola]

Reduced testcase. Create a db with

$ sqlite3 foobar.db 'create table foo (bar REAL)'
$ cargo run

and test with

use sqlx::sqlite::SqlitePoolOptions;
use sqlx::Executor;
use sqlx::Row;
use sqlx::SqlitePool;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let v: f64 = 5.0;
    let pool: SqlitePool = SqlitePoolOptions::new()
        .connect("sqlite://foobar.db?mode=rw")
        .await?;
    let insert_query = format!("INSERT INTO foo(bar) VALUES ({}) RETURNING *", v);
    let insert_query = sqlx::query(&insert_query);
    let row = pool.fetch_one(insert_query).await?;
    assert_eq!(row.get::<f64, _>(0), v);
    Ok(())
}

Works with 5.1, crashes with 5.0.

[abonander]

There's a confounding issue here, being in Row::try_get(), when the requested type doesn't match the actual type, it calls Type<Any>::type_info() which is hard-wired to panic: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/any/type.rs#L11-L12

That's a known issue already, #1408.

The actual problem is likely that SQLite is storing the value as an integer since it doesn't have a fractional value, and when we ask it the type of the value it tells us that it's an integer and not a floating point.

However, what's strange is that the SQLite docs suggest that this shouldn't happen with a column using the REAL type affinity: https://www.sqlite.org/datatype3.html#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.)

[abonander]

Oh, I think it might be a database bug!

If you insert the value and select it back in separate statements, it works as expected. This test completes normally:

    conn.execute("CREATE TABLE foo(bar REAL)").await?;
    conn.execute("INSERT INTO foo(bar) VALUES(5.0)").await?;
    
    let (bar,): (f64,) = sqlx::query_as("SELECT * FROM foo").fetch_one(&mut conn).await?;

    assert_eq!(bar, 5.0);

It's only when you do INSERT ... RETURNING * that it hiccups:

    // error occurred while decoding column 0: mismatched types; Rust type `f64` (as SQL type `REAL`) is not compatible with SQL type `INTEGER`
    let (bar,): (f64,) = sqlx::query_as("INSERT INTO foo(bar) VALUES (5.0) RETURNING *")
        .fetch_one(&mut conn)
        .await?;

    assert_eq!(bar, 5.0);

[abonander]

Submitted a bug report on the SQLite forum: https://www.sqlite.org/forum/forumpost/e0c7574ab2

[glommer]

@abonander Thank you so much for following up on this!

[drhsqlite]

Oh, I think it might be a database bug!

It is. You can see it using the SQLite CLI by entering the following SQL:

CREATE TABLE t1(x REAL);
INSERT INTO t1(x) VALUES(5) RETURNING x;

You get back "5" instead of "5.0" as you should. The problem should be fixed now at
https://www.sqlite.org/src/info/4711fb69547f4f17

[abonander]

Incredible turnaround, thank you!

[abonander]

Closed by #1763