Unknown SQlite error: unsupported type NULL of column #3 ("time")

[Spindel]

Unlike #1246 and #1350 This seems to be around function parsing in SQLite:

error: unsupported type NULL of column #3 ("time")
   --> loggerdb/src/datastore.rs:317:35
    |
317 |           let get_last_all_points = sqlx::query!(
    |  ___________________________________^
318 | |             "\
319 | | SELECT name, value, MAX(time) as time \
320 | | FROM logdata \
...   |
323 | | ORDER BY time ASC",
324 | |         );
    | |_________^
    |
    = note: this error originates in the macro `$crate::sqlx_macros::expand_query` (in Nightly builds, run with -Z macro-backtrace for more info)

With a code snippet like:

        let get_last_all_points = sqlx::query!("
SELECT name, value, MAX(time) as time 
FROM logdata 
JOIN sensor USING(s_id) 
GROUP BY name 
ORDER BY time ASC");

( Note that it's SQLite specific feature that MAX function pairs with GROUP BY automatically. )

And a simplified schema like this:

CREATE TABLE IF NOT EXISTS sensor (
        s_id integer primary key,
        name varchar not null
);

CREATE TABLE IF NOT EXISTS logdata (
        id integer primary key,
        s_id integer not null references sensor,
        value varchar not null,
        time integer not null

The error also persists if one tries to override the type of the "MAX(time) as time" using the "time!" syntax.

Only tested with version v0.5.9

[vbakc]

v0.6.1 sqlite

SELECT name, COUNT(tbl2.id) AS count
FROM tbl1
INNER JOIN tbl2 ON tbl1.id = tbl2.tbl1_id
GROUP BY id

unsupported type NULL of column #2 ("count")

[fdietze]

What needs to be done to solve this issue?

[riesha]

For anyone who comes across this issue, putting the COUNT(*) (or i assume any other function) FIRST before other fields seems to fix it

// DOES NOT WORK
let Ok(count) = sqlx::query!("SELECT author_id, COUNT(author_id) AS count FROM messages WHERE guild_id=? GROUP BY author_id LIMIT 5",guild_id).fetch_one(db).await else {};

// WORKS
let Ok(count) = sqlx::query!("SELECT COUNT(author_id) AS count, author_id FROM messages WHERE guild_id=? GROUP BY author_id LIMIT 5",guild_id).fetch_one(db).await else {};```

[Black616Angel]

This issue is not resolved though. The same problem occurs for other functions. I get the same error when doing:

SELECT MAX(amount) from table GROUP BY name;

@fdietze: Aggregate functions in sqlite need to be correctly parsed to get rid of those errors. Although mostly it should suffice to take the type of the inner column name.

[TheLuckyCoder]

I'm still having this issue in 0.8.0

[rrauch]

Same here (on 0.8.1), even the workaround above (specify function first) does not work.