I have found these related issues/pull requests

#3164
#367
#2979

These issues are all more or less having the same issue, whether it be from trying to use a join, or from the column ordinals changing post-compilation due to a table column being modified. Both cases could cause the ordinality to change.

Description

When you use the query_as macro, sometimes in certain scenarios the query will fail with an UnexpectedNullError error, even when the rust struct we're querying into has all the nullable fields wrapped with an Option.
I encountered this issue on a project when the project was compiled in offline mode, prepared against a local test database, and then run against a different staging database later, where the staging database has identical table definitions.
We found that switching to the query_as function, or compiling live against the staging database, both resolve this problem. So, the issue has to be with the macro.

Reproduction steps

I have two DBs: sqlx_test_2 and sqlx_test_1. Both of them have the following table:

CREATE TABLE IF NOT EXISTS TestRecord (
    test_record_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_name VARCHAR(255) NOT NULL,
    test_number INT NOT NULL,
    test_other_number INT UNSIGNED,
    test_last_name VARCHAR(255),
    test_email VARCHAR(255),
    test_is_active TINYINT NOT NULL DEFAULT 0,

    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (test_record_id)
);

However, I've modified sqlx_test_2 to have a different ordering. I can demonstrate that by selecting the ordinal position in the information schema:

Then in rust, we try to load it with the query_as macro, and the query_as function.

#![allow(dead_code)]
use chrono::{DateTime, Utc};
use sqlx::{
    FromRow, MySql, Pool, error::Error as SQLError, mysql::MySqlPoolOptions, query, query_as,
};

#[derive(FromRow, Debug, Clone)]
struct TestRecord {
    test_record_id: u32,
    test_name: String,
    test_number: i32,
    test_other_number: Option<u32>,
    test_last_name: Option<String>,
    test_email: Option<String>,
    test_is_active: i8,
    last_updated: DateTime<Utc>,
}

impl TestRecord {
    async fn insert(conn: &Pool<MySql>, name: &str, number: i32) -> Result<u32, SQLError> {
        let result = query!(
            "INSERT INTO TestRecord(test_name, test_number) VALUES(?, ?)",
            name,
            number
        )
        .execute(conn)
        .await?;

        Ok(result.last_insert_id() as u32)
    }

    async fn load_macro(conn: &Pool<MySql>, record_id: u32) -> Result<Self, SQLError> {
        query_as!(
            Self,
            "SELECT * FROM TestRecord WHERE test_record_id = ?",
            record_id
        )
        .fetch_one(conn)
        .await
    }

    async fn load_fn(conn: &Pool<MySql>, record_id: u32) -> Result<Self, SQLError> {
        query_as("SELECT * FROM TestRecord WHERE test_record_id = ?")
            .bind(record_id)
            .fetch_one(conn)
            .await
    }
}

const CONN_STRING: &str = "mysql://test:example@localhost/sqlx_test_2";

#[tokio::main]
async fn main() {
    let conn: Pool<MySql> = MySqlPoolOptions::new()
        .max_connections(1)
        .connect(CONN_STRING)
        .await
        .expect("Couldn't connect to database.");

    let new_record_id: u32 = TestRecord::insert(&conn, "foo", 42)
        .await
        .expect("Couldn't insert user");

    let new_record_1 = TestRecord::load_macro(&conn, new_record_id).await;

    let new_record_2 = TestRecord::load_fn(&conn, new_record_id).await;

    println!(
        "Done. \nNew record (macro) {:?}\nNew record (fn) {:?}",
        new_record_1, new_record_2
    );
}

When we run with DATABASE_URL set to connect to sqlx_test_1, and CONN_STRING pointing to sqlx_test_2, we find that we get the error when calling query_as!() but not query_as():

The error indicates that the column ordinal index 3 is unexpectedly null. In this particular case, it's trying to load test_other_number which is a nullable unsigned int into test_number which is a not null int.

That's evident by this line in the macro expansion used in TestRecord::load_macro which is actually emitting this error:
let sqlx_query_as_test_number = row.try_get_unchecked:: <i32,_>(3usize)? .into();
Actually points to:

When we recompile with the DATABASE_URL pointing at sqlx_test_2, we get the expected output, which is that both paths give the same result:

Expanding the macro we see that the index is now 2, which is correct:
let sqlx_query_as_test_number = row.try_get_unchecked:: <i32,_>(2usize)? .into();

SQLx version

0.8

Enabled SQLx features

mysql, derive, macros

Database server and version

MySQL (MariaDB 10.6)

Operating system

Ubuntu

Rust version

1.92.0