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屋!