Use of window functions wraps other columns with Option

[BTOdell]

Bug Description

When using Window Functions and the OVER (...) syntax, Rust/SQLx starts wrapping all of the other columns/fields with Option<T>. In my specific example, I'm using ROW_NUMBER() which is a non-aggregating function, so the other columns will never be NULL.

My guess is that SQLx isn't responsible for parsing the SQL query and is just using what the database is returning back, but maybe SQLx is misinterpreting something from the database or not handling a certain condition properly.

My current workaround is to just throw some unwrap() calls on all the columns that should be non-null, but I'd really prefer to avoid that.

Minimal Reproduction

SELECT
    user.username,
    user.elo,
    ROW_NUMBER() OVER (ORDER BY user.elo DESC, user.most_recent_match DESC, user.id ASC) AS `rank`
FROM user
ORDER BY rank

If I run this query using the query! macro and fetch_all, the user records have username as an Option<String> instead of just a String.

Info

• SQLx version: 0.7.2
• SQLx features enabled: mysql, runtime-tokio, time
• Database server and version: MariaDB 11.1.2 (docker)
• Operating system: Windows host, Linux (ubuntu-jammy) MariaDB docker container
• rustc --version: 1.73.0

[abonander]

If this was Postgres, this would be a SQLx bug since we use the query plan to infer nullability.

However, for MySQL/MariaDB we use the NOT_NULL flag from the response to COM_STMT_PREPARE: https://mariadb.com/kb/en/result-set-packets/#field-details-flag

You can force the query macros to infer the column as not-null using the type override syntax: https://docs.rs/sqlx/latest/sqlx/macro.query.html#force-not-null

[abonander]

Can you provide a table schema with the minimal reproduction? I'd like to open a bug upstream.

[abonander]

It's also worth checking if MySQL responds the same.

[BTOdell]

Here is a fairly minimal table schema that reproduces the issue:

CREATE TABLE `user` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `elo` int(11) NOT NULL,
  `most_recent_match` datetime NOT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `elo` (`elo`)
) ENGINE=InnoDB;

Code:

let results = query!(
    "SELECT
        user.username,
        user.elo,
        ROW_NUMBER() OVER (ORDER BY user.elo DESC, user.most_recent_match DESC, user.id ASC) AS `rank`
    FROM user
    ORDER BY rank"
).fetch_all(&state.db).await?;
for r in results {
    let username: String = r.username;  // <-- mismatched types [E0308] expected `String`, found `Option<String>` 
}

Thanks for the not-null override suggestion. I'll switch to using that workaround.