Rusqlite 查询与 SUM 恐慌与“InvalidColumnType(0, Null)"； [英] Rusqlite query with SUM panics with "InvalidColumnType(0, Null)"

问题描述

我有这个工作代码:

extern crate rusqlite;使用 rusqlite::Connection;fn 主(){let conn = Connection::open("db.sqlite").unwrap();conn.execute("CREATE TABLE toto (size INTEGER NOT NULL DEFAULT 0);", &[]).unwrap();conn.execute("INSERT INTO toto(size) VALUES (42);", &[]).unwrap();让过滤器 = 0;让 i: i64 = conn.query_row("SELECT SUM(size) FROM toto", &[], |r| r.get(0)).unwrap();println!("咕咕");println!("Cocoucou: {}", i);}

但是如果我换了

"SELECT SUM(size) FROM toto", &[]

到

"SELECT SUM(size) FROM toto WHERE size=?1", &[&filter]

然后重新运行，它会恐慌:

rm db.sqliteRUST_BACKTRACE=1 货物运行

 在 0.0 秒内完成开发 [未优化 + 调试信息] 目标运行 `target/debug/testsqlite`线程main"在Err"值上的Result::unwrap()"处恐慌:InvalidColumnType(0, Null)"，/checkout/src/libcore/result.rs:860

如果我在 sqliteman 中执行这个查询，它可以工作，那么为什么 rusqlite 会恐慌?

解决方案

如果我直接在 sqlite 命令行实用程序中运行您的查询，没有价值，结果是NULL:

sqlite>SELECT SUM(size) FROM toto WHERE size=42;sqlite>SELECT coalesce(SUM(size), 'NULL!') FROM toto WHERE size=42;空！sqlite>

由于没有与 where 子句匹配的行，总和为 NULL:

<块引用>

如果没有非 NULL 输入行，则 sum() 返回 NULL 但 total() 返回 0.0

您的 NULL 结果无法转换为 i64，因此会出现错误:

<块引用>

InvalidColumnType(0, Null)

如果你把那个错误打印出来，它会说同样的话:

let i: i64 = conn.query_row("SELECT SUM(size) FROM toto WHERE size = ?1", &[&filter], |r| {匹配 r.get_checked(0) {好的(v) =>Ⅴ、错误(e) =>panic!("query_row 错误:{}", e),}}).expect("选择失败");

<块引用>

query_row 错误:索引处无效的列类型 Null:0

修复 Rust 端

如果您使用 Option 更新代码以解决 NULL，它将正确执行:

let i: Option= conn.query_row("SELECT SUM(size) FROM toto WHERE size = ?1",&[&filter],|r|r.get(0)).expect("选择失败");

然后您可以使用 i.unwrap_or(0).

使用 TOTAL

在 SQL 端修复它

让我:f64 = conn.query_row("SELECT TOTAL(size) FROM toto WHERE size = ?1",&[&filter],|r|r.get(0)).expect("选择失败");

请注意，我们切换到了 f64.

使用 COALESCE

在 SQL 端修复它

让我:i64 = conn.query_row("SELECT COALESCE(SUM(size), 0) FROM toto WHERE size = ?1",&[&filter],|r|r.get(0)).expect("选择失败");

I have this working code:

extern crate rusqlite;

use rusqlite::Connection;

fn main() {
    let conn = Connection::open("db.sqlite").unwrap();

    conn.execute("CREATE TABLE toto (size INTEGER NOT NULL DEFAULT 0);", &[]).unwrap();
    conn.execute("INSERT INTO toto(size) VALUES (42);", &[]).unwrap();

    let filter = 0;
    let i: i64 = conn.query_row("SELECT SUM(size) FROM toto", &[], |r| r.get(0)).unwrap();
    println!("Coucou");
    println!("Coucou: {}", i);
}

But if I switch

"SELECT SUM(size) FROM toto", &[]

to

"SELECT SUM(size) FROM toto WHERE size=?1", &[&filter]

and re-run, it panics:

rm db.sqlite
RUST_BACKTRACE=1 cargo run

        Finished dev [unoptimized + debuginfo] target(s) in 0.0 secs
         Running `target/debug/testsqlite`
    thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: InvalidColumnType(0, Null)', /checkout/src/libcore/result.rs:860

If I execute this query in sqliteman, it works, so why does rusqlite panic?

解决方案

If I run your query directly in the sqlite command line utility, there is no value, the result is NULL:

sqlite> SELECT SUM(size) FROM toto WHERE size=42;

sqlite> SELECT coalesce(SUM(size), 'NULL!') FROM toto WHERE size=42;
NULL!
sqlite>

Since there are no rows matching the where clause, the sum is NULL:

If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0

Your NULL result cannot be converted to an i64, so you get an error:

InvalidColumnType(0, Null)

If you print that error out, it says the same thing:

let i: i64 = conn.query_row("SELECT SUM(size) FROM toto WHERE size = ?1", &[&filter], |r| {
    match r.get_checked(0) {
        Ok(v) => v,
        Err(e) => panic!("query_row error: {}", e),
    }
}).expect("select failed");

query_row error: Invalid column type Null at index: 0

Fixing it Rust-side

If you update your code to account for a NULL by using an Option, it will correctly execute:

let i: Option<i64> = conn.query_row(
    "SELECT SUM(size) FROM toto WHERE size = ?1",
    &[&filter],
    |r| r.get(0)
).expect("select failed");

You can then use i.unwrap_or(0).

Fixing it SQL-side with TOTAL

let i: f64 = conn.query_row(
    "SELECT TOTAL(size) FROM toto WHERE size = ?1",
    &[&filter],
    |r| r.get(0)
).expect("select failed");

Note that we switched to a f64.

Fixing it SQL-side with COALESCE

let i: i64 = conn.query_row(
    "SELECT COALESCE(SUM(size), 0) FROM toto WHERE size = ?1",
    &[&filter],
    |r| r.get(0)
).expect("select failed");

这篇关于Rusqlite 查询与 SUM 恐慌与“InvalidColumnType(0, Null)"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！