use crate::*;
test_case!(nullable, async move {
run!(
r#"
CREATE TABLE Test (
id INTEGER NULL,
num INTEGER,
name TEXT
)"#
);
run!("INSERT INTO Test (id, num, name) VALUES (NULL, 2, \"Hello\")");
run!("INSERT INTO Test (id, num, name) VALUES (1, 9, \"World\")");
run!("INSERT INTO Test (id, num, name) VALUES (3, 4, \"Great\")");
use Value::*;
let test_cases = vec![
(
"SELECT id, num, name FROM Test",
select!(
id | num | name
OptI64 | I64 | Str;
None 2 "Hello".to_owned();
Some(1) 9 "World".to_owned();
Some(3) 4 "Great".to_owned()
),
),
(
"SELECT id, num FROM Test WHERE id = NULL AND name = \'Hello\'",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id IS NULL",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id IS NOT NULL",
select!(
id | num
OptI64 | I64;
Some(1) 9;
Some(3) 4
),
),
(
"SELECT id, num FROM Test WHERE id + 1 IS NULL",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id + 1 IS NOT NULL",
select!(
id | num
OptI64 | I64;
Some(1) 9;
Some(3) 4
),
),
(
"SELECT id, num FROM Test WHERE 100 IS NULL",
select!(id | num),
),
(
"SELECT id, num FROM Test WHERE 100 IS NOT NULL",
select!(
id | num
OptI64 | I64;
None 2;
Some(1) 9;
Some(3) 4
),
),
(
"SELECT id, num FROM Test WHERE 8 + 3 IS NULL",
select!(id | num),
),
(
"SELECT id, num FROM Test WHERE 8 + 3 IS NOT NULL",
select!(
id | num
OptI64 | I64;
None 2;
Some(1) 9;
Some(3) 4
),
),
(
"SELECT id, num FROM Test WHERE NULL IS NULL",
select!(
id | num
OptI64 | I64;
None 2;
Some(1) 9;
Some(3) 4
),
),
(
"SELECT id, num FROM Test WHERE NULL IS NOT NULL",
select!(id | num),
),
(
"SELECT id, num FROM Test WHERE \"NULL\" IS NULL",
select!(id | num),
),
(
"SELECT id, num FROM Test WHERE \"NULL\" IS NOT NULL",
select!(
id | num
OptI64 | I64;
None 2;
Some(1) 9;
Some(3) 4
),
),
(
"SELECT id, num FROM Test WHERE id = NULL + 1;",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id = 1 + NULL;",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id = NULL - 1;",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id = 1 - NULL;",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id = NULL * 1;",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id = 1 * NULL;",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id = NULL / 1;",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id, num FROM Test WHERE id = 1 / NULL;",
select!(
id | num
OptI64 | I64;
None 2
),
),
(
"SELECT id + 1, 1 + id, id - 1, 1 - id, id * 1, 1 * id, id / 1, 1 / id FROM Test WHERE id = NULL;",
select!(
"id + 1" | "1 + id" | "id - 1" | "1 - id" | "id * 1" | "1 * id" | "id / 1" | "1 / id";
OptI64 | OptI64 | OptI64 | OptI64 | OptI64 | OptI64 | OptI64 | OptI64;
None None None None None None None None
),
),
];
for (sql, expected) in test_cases.into_iter() {
test!(Ok(expected), sql);
}
run!("UPDATE Test SET id = 2");
let test_cases = vec![
(
"SELECT id FROM Test",
Ok(select!(
id
OptI64;
Some(2);
Some(2);
Some(2)
)),
),
(
"SELECT id, num FROM Test",
Ok(select!(
id | num
OptI64 | I64;
Some(2) 2;
Some(2) 9;
Some(2) 4
)),
),
(
"INSERT INTO Test VALUES (1, NULL)",
Err(ValueError::NullValueOnNotNullField.into()),
),
];
for (sql, expected) in test_cases.into_iter() {
test!(expected, sql);
}
});
test_case!(nullable_text, async move {
run!(
"
CREATE TABLE Foo (
id INTEGER,
name TEXT NULL
);
"
);
let insert_sqls = [
"INSERT INTO Foo (id, name) VALUES (1, \"Hello\")",
"INSERT INTO Foo (id, name) VALUES (2, Null)",
];
for insert_sql in insert_sqls.iter() {
run!(insert_sql);
}
});