Unknown SQLITE error unsupported type NULL of column #1

[OvermindDL1]

When attempting to perform a DELETE FROM servers WHERE id = 42 RETURNING name sqlx is returning an error of unsupported type NULL of column #1 ("name") for the given table of:

CREATE TABLE servers (
    id       INTEGER PRIMARY KEY NOT NULL,
    name     TEXT                NOT NULL
);

And yet running that same sql via the official sqlite client works correctly and returns the expected value.

The listed version of libsqlite3-sys is 0.22.2, which comes bundled with sqlite version 3.35.4 according to the sqlite3 C source included with it, which does support the above SQL, which is confirmed as I installed the same local client version of 3.35.4 and that is what I tested the same above SQL in and it worked there.

The specific code that I am using is:

	let result = sqlx::query_scalar!(
		"DELETE FROM servers WHERE id = ? RETURNING name",
		id
	)
	.fetch_one(&mut db)
	.await;

And it fails to compile due to failing the check at compile-time, full error is:

error: unsupported type NULL of column #1 ("name")
   --> src/main.rs:127:15
    |
127 |       let result = sqlx::query_scalar!(
    |  __________________^
128 | |         "DELETE FROM servers WHERE id = ? RETURNING name",
129 | |         id
130 | |     )
    | |_____^
    |
    = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

error: aborting due to previous error

[abonander]

Looks like this is probably a duplicate of #1246 which was already closed by a PR and should be released in 0.5.7 soon.

[OvermindDL1]

It looks like it might, it being closed would be why I didn't see it, nice! Looking forward then!

[SuperiorJT]

I'm currently experiencing this issue with using sqlx pulled from the master branch, so I don't think #1246 solves this. I get the error any time I return data from the sqlite database. Some examples of simple queries that do this:

// I have not figured out a way to suppress the error here
// since casting is not allowed.
sqlx::query!(
        r#"INSERT INTO welcome (guild_id, enabled) VALUES (?1, ?2) RETURNING id"#,
        data.guild_id.to_string(),
        data.enabled)
    .fetch_one(&self.pool)
    .await?;

struct WelcomeModule {
    pub id: i64,
    pub guild_id: String,
    pub enabled: bool,
}

// Ideally I do "SELECT *", but I suppress the error by casting
// SELECT id, guild_id, enabled as "enabled: _"
sqlx::query_as!(
         WelcomeModule,
         r#"SELECT id, guild_id, enabled FROM welcome WHERE guild_id = ?"#,
         guild_id)
     .fetch_one(&self.pool)
     .await?;

These are against a simple table with three columns:

CREATE TABLE "welcome" (
	"id"	INTEGER NOT NULL UNIQUE,
	"guild_id"	TEXT NOT NULL DEFAULT 0,
	"enabled"	NUMERIC NOT NULL DEFAULT 0,
	PRIMARY KEY("id" AUTOINCREMENT)
);

[Owez]

Can confirm, I'm getting the error on a fresh 0.5 pull of sqlx today happening with dates specifically, as seen here:

error: unsupported type NULL of column #3 ("at_time")
94 | /             sqlx::query_as!(
95 | |                 Population,
96 | |                 "SELECT * FROM population WHERE battle_id=?",
97 | |                 self.id
98 | |             )
   | |_____________^

error: unsupported type NULL of column #5 ("last_edited")
94 | /             sqlx::query_as!(
95 | |                 Population,
96 | |                 "SELECT * FROM population WHERE battle_id=?",
97 | |                 self.id
98 | |             )
   | |_____________^

error: unsupported type NULL of column #6 ("submitted")
94 | /             sqlx::query_as!(
95 | |                 Population,
96 | |                 "SELECT * FROM population WHERE battle_id=?",
97 | |                 self.id
98 | |             )
   | |_____________^

For a migration which looks like:

CREATE TABLE population (
    battle_id INTEGER NOT NULL,
    counted INTEGER NOT NULL,
    at_time TIMESTAMP WITH TIME ZONE NOT NULL,
    description VARCHAR(150),
    last_edited TIMESTAMP WITH TIME ZONE,
    submitted TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (battle_id, at_time),
    FOREIGN KEY (battle_id) REFERENCES battle(id)
);

[abonander]

We recently had an overhaul of the typechecking code in the SQLite driver. If anyone can reproduce this issue on 0.6.0, please open a new issue.

[yzernik]

@abonander I am getting similar errors ("unsupported type NULL of column...") in some of my queries:
This is the PR that reproduces the error: https://github.com/yzernik/squeakroad/pull/419/files
If you check out the master branch, it should compile and sqlx prepare should work fine.

I believe that it is a valid SQL query. I am using sqlx v0.6

$ cargo sqlx prepare --database-url sqlite://db.sqlite --check
   Compiling squeakroad v0.0.2 (/home/yzernik/work/squeakroad)
error: unsupported type NULL of column #2 ("amount_change_sat")
    --> src/models.rs:2597:39
     |
2597 |           let account_balance_changes = sqlx::query!("
     |  _______________________________________^
2598 | | SELECT * FROM
2599 | | (select orders.seller_user_id as user_id, orders.seller_credit_sat as amount_change_sat, 'received_order' as event_type, orders.public_id...
2600 | | from
...    |
2628 | | OFFSET ?
2629 | | ;", limit, offset)
     | |__________________^
     |
     = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: unsupported type NULL of column #3 ("event_type")
    --> src/models.rs:2597:39
     |
2597 |           let account_balance_changes = sqlx::query!("
     |  _______________________________________^
2598 | | SELECT * FROM
2599 | | (select orders.seller_user_id as user_id, orders.seller_credit_sat as amount_change_sat, 'received_order' as event_type, orders.public_id...
2600 | | from
...    |
2628 | | OFFSET ?
2629 | | ;", limit, offset)
     | |__________________^
     |
     = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

[sasacocic]

I believe I might have reproduced something similar to this in #2171 @abonander