query! macro infers type as Option for primary key

[imnyxz]

I have found these related issues/pull requests

related to https://github.com/launchbadge/sqlx/issues/1923

Description

Expected behavior: infer that primary keys can't be optional

Reproduction steps

using sqite driver given a sample table:

CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY,
);

the following code: let row = query!("SELECT id FROM users") infers the type of the inner Record (after stripping away errors, etc.) as row.id being Option<i64>

SQLx version

0.8.6

Enabled SQLx features

"runtime-tokio", "sqlite"

Database server and version

sqlite

Operating system

macOS

Rust version

1.88

[iamjpotts]

sqlite is why it is typed as Option - it allows nulls in PK columns.

[Lege19]

Last paragraph of https://sqlite.org/lang_createtable.html#the_primary_key:

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allows NULLs in most PRIMARY KEY columns.

So this behaviour is wrong in:

• STRICT tables
• WITHOUT ROWID tables
• INTEGER PRIMARY KEYs

[Lege19]

It looks like sqlx uses pragma_table_info for this in https://github.com/Lege19/sqlx/blob/main/sqlx-sqlite/src/connection/explain.rs#L370. From my testing, this gives the correct result in the case of STRICT tables and WITHOUT ROWID tables. It's just in the case of INTEGER PRIMARY KEYs that it gets it wrong.

This case is complex, and will probably require special handling in SQLx. See: https://sqlite.org/lang_createtable.html#rowid and https://sqlite.org/autoinc.html.

The summary is that INTEGER PRIMARY KEYs in a table that is not WITHOUT ROWID become aliases to the implicit rowid column, which is NOT NULL. There's also a special case for INTEGER PRIMARY KEY DESC not becoming an alias for rowid to maintain backwards compatibility with an ancient bug.