[MariaDB 10.5] Fail to get row value by column name

[billy1624]

Problem

I'm using sqlx version 0.5.9 with MariaDB 10.5 to test the support of RETURNING syntax on SQLx. Found something unusual... I can get value with column index (row.get::<i32, _>(0)), but not column name (row.get::<i32, _>("id")).

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

Source Code

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

#[async_std::main]
async fn main() -> Result<()> {
    let pool = MySqlPoolOptions::new()
        .max_connections(5)
        .connect("mysql://root:root@localhost/returning_tests")
        .await?;

    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?;
    println!("{:#?}", row);

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

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

    Ok(())
}

Full Log

MySqlRow {
    row: Row {
        storage: b"\0\x0c\0\0\0\x0bBakery Shop\0\0\0\0\0\0\xe0?",
        values: [
            Some(
                1..5,
            ),
            Some(
                6..17,
            ),
            Some(
                17..25,
            ),
        ],
    },
    format: Binary,
    columns: [
        MySqlColumn {
            ordinal: 0,
            name: id,
            type_info: MySqlTypeInfo {
                type: Long,
                flags: NOT_NULL | PRIMARY_KEY | AUTO_INCREMENT,
                char_set: 63,
                max_size: Some(
                    11,
                ),
            },
            flags: Some(
                NOT_NULL | PRIMARY_KEY | AUTO_INCREMENT,
            ),
        },
        MySqlColumn {
            ordinal: 1,
            name: name,
            type_info: MySqlTypeInfo {
                type: VarString,
                flags: NOT_NULL | NO_DEFAULT_VALUE,
                char_set: 224,
                max_size: Some(
                    1020,
                ),
            },
            flags: Some(
                NOT_NULL | NO_DEFAULT_VALUE,
            ),
        },
        MySqlColumn {
            ordinal: 2,
            name: profit_margin,
            type_info: MySqlTypeInfo {
                type: Double,
                flags: NOT_NULL | NO_DEFAULT_VALUE,
                char_set: 63,
                max_size: Some(
                    22,
                ),
            },
            flags: Some(
                NOT_NULL | NO_DEFAULT_VALUE,
            ),
        },
    ],
    column_names: {},
}
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: ColumnNotFound("id")', /Users/billy/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-core-0.5.9/src/row.rs:73:37
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

[abonander]

That's interesting cause the names are clearly included in the column info there, they just haven't been extracted to the map for some reason.

[abonander]

I've figured out so far that the problem is that the COM_STMT_PREPARE_OK response tells us that there are 0 columns in the result, which is decidedly wrong. I'm gonna go ahead and open a bug on the MariaDB issue tracker for that.

To save on allocations, we use this response to populate the column_names map, cache it in the prepared statement object, and then share it with the MySqlRow using an Arc. So that's why the map is empty, which breaks column lookup by name.

As a workaround, we can check if this map is empty when decoding the Row message and if so, populate it directly. This does mean that the map can't be amortized, although maybe we could memoize it at least for the current execution.

[abonander]

This also means that until it is fixed in MariaDB, the query!() family of macros will think an INSERT ... RETURNING query returns 0 columns.

[abonander]

Opened a ticket against MariaDB: https://jira.mariadb.org/browse/MDEV-27013

[billy1624]

Thanks!! @abonander

[tpoliaw]

This also appears to be an issue when calling stored procedures. The procedure returns three columns that can be accessed via index but not by name. In a similar way to @billy1624, the MySqlRow that is returned does have the names in the MySqlColumns.

If it's relevant, the procedure definition doesn't use RETURNING syntax.

[joao-conde]

I've figured out so far that the problem is that the COM_STMT_PREPARE_OK response tells us that there are 0 columns in the result, which is decidedly wrong. I'm gonna go ahead and open a bug on the MariaDB issue tracker for that.

The issue seems stale. Is there any way the community of SQLX can pressure MariaDB folks? Where can I voice that the issue is important for me?

As a workaround, we can check if this map is empty when decoding the Row message and if so, populate it directly. This does mean that the map can't be amortized, although maybe we could memoize it at least for the current execution.

What about this possible workaround @abonander ?

[abonander]

What about this possible workaround @abonander ?

That won't fix the query!() macros since they don't actually execute the query at compile time, but it would fix the errors at runtime.