unsupported type NULL error only on my machine

[tmvkrpxl0]

Bug Description

sqlx::query_file!("src/queries/leaderboard.sql") this has error:

unsupported type NULL of column #1 ("user")

the leaderboard.sql in question is:

select
	`user`,
	count(
		distinct date(`joined`, '+09:00')
	) as days,
	coalesce(
		sum(unixepoch(coalesce(`left`, 'now')) - unixepoch(`joined`)),
	0) as `total_duration`
from
	`vc_activities`
group by
	`user`
order by
	`total_duration` desc
limit 5

but this error does not happen on my friend's pc.

Minimal Reproduction

https://github.com/tmvkrpxl0/sqlx-report
I used sqlx-cli to setup database like this:

sqlx database create
sqlx migrate run

Then ran cargo build and got the same error

Info

• SQLx version: 0.8.2, Both release on crate.io and git clone have this issue
• SQLx features enabled: ["runtime-tokio", "sqlite"].
• Database server and version: Me: (3.37.2), My friend: (3.46.1)
• Operating system: Me: (KDE Neon 22.04), My friend: (Arch linux)
• rustc --version: rustc 1.81.0

[rijkvp]

I am getting the same error on a big aggregation query that also uses a GROUP BY statement (version 0.8.2). If I remove the GROUP BY there are no issues.

It seems like it incorrectly infers the type of the column to be NULL which is not supported by the compile-time verifier, as demonstrated by the following example:

use sqlx::SqliteConnection;

let mut conn = SqliteConnection::connect("sqlite::memory:").await.unwrap();
let res = sqlx::query!("SELECT NULL").fetch_one(&mut conn).await.unwrap(); // unsupported type NULL of column #1 ("NULL")

[JavaDerg]

I'm having the same error, seemly triggered by the user of GROUP BY.

SELECT i.status, COUNT(l.liker) as likes
    FROM ips i
    LEFT OUTER JOIN likes l
    ON i.ip = l.target
    WHERE ip = ?
    GROUP BY i.status

Works fine in 0.7.4

[uckelman]

I get the same error on 0.8.3 with this query:

SELECT 1 AS present
FROM owners
WHERE user_id = ? AND project_id = ?
LIMIT 1

It worked without any problems in 0.7.4.

Changing it to SELECT user_id makes 0.8.3 stop producing an error.

The relevant tables are defined like this:

CREATE TABLE users(
  user_id INTEGER PRIMARY KEY NOT NULL,
  username TEXT NOT NULL,
  UNIQUE(username)
);

CREATE TABLE owners(
  user_id INTEGER NOT NULL,
  project_id INTEGER NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(user_id),
  FOREIGN KEY(project_id) REFERENCES projects(project_id),
  UNIQUE(user_id, project_id)
);

CREATE TABLE projects (
  project_id INTEGER PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  normalized_name TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  modified_at INTEGER NOT NULL,
  modified_by INTEGER NOT NULL,
  revision INTEGER NOT NULL,
  description TEXT NOT NULL,
  game_title TEXT NOT NULL,
  game_title_sort TEXT NOT NULL,
  game_publisher TEXT NOT NULL,
  game_year TEXT NOT NULL,
  game_players_min INTEGER,
  game_players_max INTEGER,
  game_length_min INTEGER,
  game_length_max INTEGER,
  readme TEXT NOT NULL,
  image TEXT,
  UNIQUE(name),
  UNIQUE(normalized_name),
  FOREIGN KEY(project_id, image) REFERENCES images(project_id, filename),
  FOREIGN KEY(modified_by) REFERENCES users(user_id)
);

CREATE TABLE images (
  project_id INTEGER NOT NULL,
  filename TEXT NOT NULL,
  url TEXT NOT NULL,
  published_at INTEGER NOT NULL,
  published_by INTEGER NOT NULL,
  FOREIGN KEY(project_id) REFERENCES projects(project_id),
  FOREIGN KEY(published_by) REFERENCES users(user_id),
  UNIQUE(project_id, filename)
);

[tyrelr]

I was able to simplify the original query, while still reproducing the original bug.

select
	`user`
from
	`vc_activities`

It look to me like the column type 'String' isn't recognized as a valid column type name.
So there are probably 2 potential fixes, for someone who wants to:

• Make a PR to add 'string' to impl FromStr for DataType within sqlx-sqlite/src/type_info.rs (note that I'm not certain whether that's the only place that would need it).
• or, change the declared table column type to one which sqlite3 expects (Text, Char, or Clob)
  • https://sqlite.org/datatype3.html#affinity_name_examples
  • Note that sqlite3 doc's specifically calls out that it interprets 'String' type as affinity of 'Int', which is probably not what you intend.