[SQLite 3.35.0+] Getting row values from RETURNING statement

[billy1624]

Problem

I'm using sqlx version 0.5.9 with SQLite 3.35 to test the support of RETURNING syntax on SQLx. Found something unusual... seems that if I use "`" character to quote column names it will affect SQLx getting value from resulting row?

- Querying `INSERT ... RETURNING id, name, profit_margin`, I was able to get values using `row.get::<i32, _>("id")`.
- Querying `INSERT ... RETURNING `id`, `name`, `profit_margin`, I wasn't able to get values using `row.get::<i32, _>("id")` but `row.get::<i32, _>("`id`")` works.

Is this the intended behaviour or it's a bug? Thanks!!

Source Code

use sqlx::{sqlite::*, *};

#[async_std::main]
async fn main() -> Result<()> {
    let pool = SqlitePoolOptions::new()
        .max_connections(5)
        .connect("sqlite::memory:")
        .await?;

    sqlx::query("CREATE TABLE `bakery` ( `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` text NOT NULL, `profit_margin` real NOT NULL )")
        .execute(&pool)
        .await?;

    // INSERT ... RETURNING id, name, profit_margin
    let mut query = sqlx::query("INSERT INTO `bakery` (`name`, `profit_margin`) VALUES (?, ?) RETURNING id, name, profit_margin");
    query = query.bind("Bakery Shop");
    query = query.bind(0.5);
    let row = query.fetch_one(&pool).await?;

    // Working...
    dbg!(row.get::<i32, _>("id"));
    dbg!(row.get::<String, _>("name"));
    dbg!(row.get::<f64, _>("profit_margin"));

    // INSERT ... RETURNING `id`, `name`, `profit_margin`
    let mut query = sqlx::query("INSERT INTO `bakery` (`name`, `profit_margin`) VALUES (?, ?) RETURNING `id`, `name`, `profit_margin`");
    query = query.bind("Bakery Shop");
    query = query.bind(0.5);
    let row = query.fetch_one(&pool).await?;

    // Working...
    dbg!(row.get::<i32, _>(0));
    dbg!(row.get::<String, _>(1));
    dbg!(row.get::<f64, _>(2));

    // Somehow this is Working... but this is unexpected
    dbg!(row.get::<i32, _>("`id`"));
    dbg!(row.get::<String, _>("`name`"));
    dbg!(row.get::<f64, _>("`profit_margin`"));

    // Not Working... panicked at 'called `Result::unwrap()` on an `Err` value: ColumnNotFound("id")
    dbg!(row.get::<i32, _>("id"));
    dbg!(row.get::<String, _>("name"));
    dbg!(row.get::<f64, _>("profit_margin"));

    Ok(())
}

Output Log

[src/main.rs:19] row.get::<i32, _>("id") = 1
[src/main.rs:20] row.get::<String, _>("name") = "Bakery Shop"
[src/main.rs:21] row.get::<f64, _>("profit_margin") = 0.5
[src/main.rs:29] row.get::<i32, _>(0) = 2
[src/main.rs:30] row.get::<String, _>(1) = "Bakery Shop"
[src/main.rs:31] row.get::<f64, _>(2) = 0.5
[src/main.rs:34] row.get::<i32, _>("`id`") = 2
[src/main.rs:35] row.get::<String, _>("`name`") = "Bakery Shop"
[src/main.rs:36] row.get::<f64, _>("`profit_margin`") = 0.5
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: ColumnNotFound("id")', /Users/billy/.cargo/registry/src/github.aaakk.us.kg-1ecc6299db9ec823/sqlx-core-0.5.9/src/row.rs:73:37
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

[billy1624]

Hey @abonander, I'm closing this issue. Because this is fixed if we use double quotes around SQLite identifiers instead of back-ticks. Thanks!