query_as! offline mode mismatched types expected struct X, found enum std::option::Option

[Tarang]

I noticed there i a similar issue #93

I am using the latest version of SQLx so I don't believe that issue was solved:

cargo sqlx --version
cargo-sqlx 0.5.5

To create the offline bundle and to recreate the error I run:

cargo sqlx prepare

I'm using the offline package with Postgres with the sqlx::query_as! macro and SQLx seems to think every column I have is nullable. So I get a generic error: mismatched types expected struct [i32, String, ..], found enum `std::option::Option

Its a bit random how it happens but its very frustrating when you make all your values Option< > eventually it realises and switches back to the opposite error where it expected Option but found an i32 or whatever the underlying struct was.

I have noticed the only pattern is when it has this error, every single struct is considered nullable vs incorrect ones singled out out of a list. So you either have SQLx think they are all nullable, or all of them are interpreted as the correct types.

[jakehemmerle]

Also having this issue with this version trying to do that command

[0xdeafbeef]

Same issue

create table balances
(
    event_hash      BYTEA UNIQUE,
    lp_address      VARCHAR NOT NULL,
    base_address    VARCHAR NOT NULL,
    counter_address VARCHAR NOT NULL,
    base            VARCHAR NOT NULL,
    counter         VARCHAR NOT NULL,
    base_volume     DECIMAL NOT NULL,
    counter_volume  DECIMAL NOT NULL,
    lp_volume       DECIMAL NOT NULL,
    timestamp_block INT     NOT NULL,
    created_at      BIGINT  NOT NULL,
    PRIMARY KEY (lp_address, created_at)
);

#[derive(Clone, Debug, serde::Serialize, serde::Deserialize, Eq, PartialEq)]
pub struct BalanceFromDb {
    pub event_hash: Option<Vec<u8>>,
    pub lp_address: String,
    pub base_address: String,
    pub counter_address: String,
    pub base: String,
    pub counter: String,
    pub base_volume: Decimal,
    pub counter_volume: Decimal,
    pub lp_volume: Decimal,
    pub timestamp_block: i32,
    pub created_at: i64,
}

[KappaShilaff]

[0xdeafbeef]

Any updates?

[abonander]

It's a bit annoying but you can use overrides to force nullable/not nullable: https://docs.rs/sqlx/0.5.7/sqlx/macro.query.html#overrides-cheatsheet

If you're always compiling in offline mode (with SQLX_OFFLINE=1) then the inferred nullability shouldn't change as that's encoded in the sqlx-data.json. If it is changing despite compiling in offline mode, that's a really weird bug.

However, arbitrary changes to the query, or even compiling against a development vs production database, or different versions of Postgres, can change the query plan enough to where it breaks nullability inference.

In this case what would really help is to provide the output of EXPLAIN (VERBOSE, FORMAT JSON) <query> as that's what the macros use for nullability inference. Ideally, the output for before and after changing the query so we can compare the differences.

[Tarang]

I've used the same database all the time, the only thing ive changed are the queries and have gradually added more.

It makes it very difficult to make the sqlx-data.json file in the first place. Is there anything in SQL queries that triggers this behaviour with SQLX? I could alter my queries to try and avoid the errors.

[YpeKingma]

This happened to me (sqlx 0.5.9, psql 11.14) for a foreign key referencing a primary key defined like this:

pk_name smallint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY

The foreign key is defined in another table as:

fk_name smallint REFERENCES pk_table_name

With this definition there is this compilation error message for an sqlx query_as! macro:

expected type i16 found enum std::option::Option<i16>

Adding NOT NULL to the foreign key definition fixed this:

fk_name smallint REFERENCES pk_table_name NOT NULL

I could change the foreign key definition since the table is still in early development,
so dropping the table and creating it again is no problem yet.
I did not need an sqlx override to force not nullable.

[Dragonink]

This happened to me (sqlx 0.5.13 using SQLite) on primary keys.

I have one table and associated structs that works perfectly:

CREATE TABLE libraries (
    id INTEGER PRIMARY KEY,
    /* ... */
);

struct DbLibrary {
    pub id: i64,
    // ...
}

Then I added two tables like the previous one, but for those two I got the error expected i64, found Option<i64> on the id INTEGER PRIMARY KEY fields of both tables.
Though forcing the value not to be nullable in the query or adding the NOT NULL constraint works, how can SQLx think that an INTEGER PRIMARY KEY can be nullable in those two tables but not in the first one?
The sole difference between both tables and the first one is that I defined indexes and triggers for the two problematic tables.

[Jayshua]

On sqlite with sqlx version 0.6.2.

I have a table defined like this:

create table account (
	id integer primary key autoincrement,
	email text not null,
	created datetime not null
);

This works fine:

sqlx::query_as!(Account, "select id, email, created from account where account.email = ?", email)

However, when I add a unique index to the email column so that two accounts can't have the same email:

create table account (
	id integer primary key autoincrement,
	email text not null unique,
	password_hash text not null
);

I get this error when compiling the exact same query:

error[E0308]: mismatched types
   --> src/data/account.rs:116:28
    |
116 |     let account: Account = sqlx::query_as!(Account, "select id, email, created from account where account.email = ?", email)
    |                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected `i64`, found enum `std::option::Option`
    |
    = note: expected type `i64`
               found enum `std::option::Option<i64>`
    = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

I can fix it by casting the id column:

sqlx::query_as!(Account, "select id as 'id!', email, password_hash, created from account where account.email = ?", email)

I can also fix it by searching on a different column:

sqlx::query_as!(Account, "select id, email, password_hash, created from account where account.id = ?", id)

[wsantos]

I had the same problem, after adding unique constraints to my email and username columns, I was able to add NOT NULL to the schema and fix it without needing to override in the query, check:

#2660 (comment)