use super::*;
use uqa_execution::mutation::{dispatch, insert::table::InsertPlanning};
use uqa_sql::plan::{CommandPlan, RelationalPlan};
#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn analytical_fixture_single_insert_fits_default_retention(#[case] provider: Provider) {
use std::fmt::Write;
let fixture: serde_json::Value = serde_json::from_str(include_str!(concat!(
env!("CARGO_MANIFEST_DIR"),
"/../../benchmarks/analytical/manifest.json"
)))
.unwrap();
let directory = tempfile::tempdir().unwrap();
let path = directory.path().join("analytical-values.db");
let engine = provider.open(&path);
engine
.sql(fixture["schema_sql"].as_str().unwrap(), &[])
.unwrap();
engine.sql("SET work_mem TO '4MB'", &[]).unwrap();
let memory = engine.query_retention_control().unwrap().memory().clone();
assert_eq!(memory.limit(), 64 << 20);
let count = fixture["rows"].as_u64().unwrap();
let seed = fixture["seed"].as_u64().unwrap();
let mut sql = String::from("INSERT INTO lineitem (id, return_flag, line_status, quantity, extended_price, discount, ship_day) VALUES ");
for id in 0..count {
if id != 0 {
sql.push_str(", ");
}
let sample = id ^ seed;
write!(
sql,
"({id}, '{}', '{}', {}, {}, {}, {})",
["A", "N", "R"][sample as usize % 3],
["F", "O"][(sample as usize / 3) % 2],
1 + sample % 50,
10_000 + sample % 90_000,
sample % 11,
sample % 2_500,
)
.unwrap();
}
engine.sql(&sql, &[]).unwrap();
let error = engine.sql(&sql, &[]).unwrap_err();
assert_eq!(error.sqlstate(), Some("23505"), "{provider:?}: {error}");
drop(error);
engine.sql("BEGIN; SAVEPOINT rejected", &[]).unwrap();
let retained = memory.used();
let occupied = memory.reserve(memory.available() - (16 << 10)).unwrap();
let error = engine.sql(&sql, &[]).unwrap_err();
assert_eq!(error.sqlstate(), Some("53200"), "{provider:?}: {error}");
drop(error);
assert_eq!(memory.used(), retained + occupied.bytes());
drop(occupied);
engine.sql("ROLLBACK TO rejected; COMMIT", &[]).unwrap();
assert_analytical_fixture(&engine, &fixture);
engine.close().unwrap();
drop(engine);
assert_eq!(memory.used(), 0);
if !matches!(provider, Provider::Memory) {
let reopened = provider.open(&path);
assert_analytical_fixture(&reopened, &fixture);
reopened.close().unwrap();
}
}
fn assert_analytical_fixture(engine: &Engine, fixture: &serde_json::Value) {
let rows = engine
.sql(
"SELECT COUNT(*), MIN(id), MAX(id), CAST(SUM(id) AS BIGINT) FROM lineitem",
&[],
)
.unwrap();
for (column, value) in [20_000, 0, 19_999, 199_990_000].into_iter().enumerate() {
assert_eq!(rows.value_at(0, column), Some(&Value::Int(value)));
}
drop(rows);
let rows = engine
.sql(fixture["queries"]["q1"].as_str().unwrap(), &[])
.unwrap();
let expected = [
("A", "F", 3285, 82163),
("A", "O", 3283, 85338),
("N", "F", 3285, 85448),
("N", "O", 3282, 82026),
("R", "F", 3283, 82055),
("R", "O", 3282, 85308),
];
assert_eq!(rows.rows.len(), expected.len());
for (row, (flag, status, count, quantity)) in expected.into_iter().enumerate() {
assert_eq!(rows.value_at(row, 0), Some(&Value::Str(flag.into())));
assert_eq!(rows.value_at(row, 1), Some(&Value::Str(status.into())));
assert_eq!(rows.value_at(row, 2), Some(&Value::Int(count)));
assert_eq!(rows.value_at(row, 3), Some(&Value::Int(quantity)));
}
drop(rows);
let rows = engine
.sql(fixture["queries"]["q6"].as_str().unwrap(), &[])
.unwrap();
assert_eq!(rows.value_at(0, 0), Some(&Value::Int(879_578_310)));
drop(rows);
let scan = fixture["queries"]["scan"].as_str().unwrap();
let rows = engine.sql(scan, &[]).unwrap();
assert_eq!(rows.rows.len(), 15_602);
let prices: i64 = (0..rows.rows.len())
.map(|index| match rows.value_at(index, 1) {
Some(Value::Int(value)) => *value,
other => panic!("expected extended price: {other:?}"),
})
.sum();
assert_eq!(prices, 345_536_285);
let cursor = engine.sql_cursor(scan, &[]).unwrap();
assert_eq!(cursor.summary().row_count, rows.rows.len());
assert!(!cursor.summary().spilled_to_disk);
let mut position = 0;
for batch in cursor {
let batch = batch.unwrap();
let columns = batch.columns();
for (id, price) in columns[0].values.iter().zip(&columns[1].values) {
assert_eq!(Some(id), rows.value_at(position, 0));
assert_eq!(Some(price), rows.value_at(position, 1));
position += 1;
}
}
assert_eq!(position, rows.rows.len());
}
#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn native_set_returning_values_admit_private_commands_before_copying(#[case] provider: Provider) {
let directory = tempfile::tempdir().unwrap();
let engine = provider.open(&directory.path().join("set-returning-values-owner.db"));
engine
.sql(
"CREATE TABLE items(id integer PRIMARY KEY,flag integer DEFAULT 7)",
&[],
)
.unwrap();
let sql = "INSERT INTO public.items VALUES(generate_series(1,2),DEFAULT) RETURNING id,flag";
let UnifiedPlan::Command(command) =
UnifiedPlan::lower(uqa_sql::compile(sql).unwrap().remove(0))
else {
panic!("command")
};
let CommandPlan::Insert(mut plan) = *command else {
panic!("INSERT")
};
let UnifiedPlan::Query(mut unused) =
UnifiedPlan::lower(uqa_sql::compile("SELECT 1").unwrap().remove(0))
else {
panic!("query")
};
let RelationalPlan::QueryBlock(block) = &mut unused.root else {
panic!("block")
};
block.projections[0].expr = uqa_sql::ScalarExpr::Literal(Value::Str("x".repeat(4 << 20)));
plan.subqueries.push(*unused);
let resources = engine.query_retention_control().unwrap();
let memory = resources.memory();
let baseline = memory.used();
let occupied = memory.reserve(memory.available() - (64 << 10)).unwrap();
let mut outcome = None;
let measured = allocation_counter::measure(|| {
outcome = Some(
dispatch::run_insert(
&engine.mutation_statement_context(),
InsertPlanning {
inference: engine.inference_context(),
returning: engine.returning_analysis_context(),
prune_source_outputs:
uqa_planner::mutation_outputs::prune_unused_query_outputs_owned,
},
&plan,
&[],
None,
)
.map(drop),
);
});
let error = outcome.unwrap().unwrap_err();
assert_eq!(error.sqlstate(), Some("53200"), "{provider:?}: {error}");
assert!(
measured.bytes_total < 256 << 10,
"{provider:?}: {measured:?}"
);
drop(error);
assert_eq!(memory.used(), baseline + occupied.bytes());
drop(occupied);
assert_eq!(memory.used(), baseline);
let rows = engine.sql(sql, &[]).unwrap();
assert_eq!(rows.rows.len(), 2);
assert_eq!(rows.value_at(0, 0), Some(&Value::Int(1)));
assert_eq!(rows.value_at(1, 0), Some(&Value::Int(2)));
assert_eq!(rows.value_at(0, 1), Some(&Value::Int(7)));
drop(rows);
engine.close().unwrap();
drop(engine);
assert_eq!(memory.used(), 0);
}
#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn set_returning_values_preserve_postgresql_defaults_rules_and_reopen(#[case] provider: Provider) {
let directory = tempfile::tempdir().unwrap();
let path = directory.path().join("set-returning-values-reference.db");
let engine = provider.open(&path);
engine.sql("CREATE TABLE items(a integer DEFAULT 9,b integer,c integer[]); CREATE VIEW v AS SELECT b,c,a FROM items; CREATE TABLE observed(id integer,flag integer DEFAULT 7); CREATE TABLE audit(flag integer); CREATE RULE logflag AS ON INSERT TO observed DO ALSO INSERT INTO audit VALUES(NEW.flag)",&[]).unwrap();
for (sql, first, array) in [
(
"INSERT INTO items VALUES(DEFAULT,generate_series(1,2),ARRAY[5]) RETURNING a,b,c[1]",
1,
5,
),
(
"INSERT INTO items(b,c[1]) VALUES(generate_series(3,4),7) RETURNING a,b,c[1]",
3,
7,
),
(
"INSERT INTO v VALUES(generate_series(5,6),ARRAY[11],DEFAULT) RETURNING a,b,c[1]",
5,
11,
),
] {
let rows = engine
.sql(sql, &[])
.unwrap_or_else(|error| panic!("{provider:?}: {sql}: {error}"));
assert_eq!(rows.rows.len(), 2);
for row in 0..2 {
assert_eq!(rows.value_at(row, 0), Some(&Value::Int(9)));
assert_eq!(rows.value_at(row, 1), Some(&Value::Int(first + row as i64)));
assert_eq!(rows.value_at(row, 2), Some(&Value::Int(array)));
}
}
let rows = engine
.sql(
"INSERT INTO observed VALUES(generate_series(1,2),5) RETURNING id,flag",
&[],
)
.unwrap();
assert_eq!(rows.rows.len(), 2);
assert_eq!(rows.value_at(0, 0), Some(&Value::Int(1)));
assert_eq!(rows.value_at(1, 0), Some(&Value::Int(2)));
assert_eq!(rows.value_at(0, 1), Some(&Value::Int(5)));
drop(rows);
engine.sql("CREATE RULE logid AS ON INSERT TO observed DO ALSO INSERT INTO audit VALUES(NEW.id); BEGIN; SAVEPOINT rejected",&[]).unwrap();
let error = engine
.sql("INSERT INTO observed VALUES(generate_series(3,4),6)", &[])
.unwrap_err();
assert_eq!(error.sqlstate(), Some("0A000"), "{provider:?}: {error}");
engine.sql("ROLLBACK TO rejected; COMMIT", &[]).unwrap();
assert_rows(&engine);
engine.close().unwrap();
drop(engine);
if !matches!(provider, Provider::Memory) {
let reopened = provider.open(&path);
assert_rows(&reopened);
reopened.close().unwrap();
}
}
fn assert_rows(engine: &Engine) {
let rows = engine
.sql("SELECT a,b,c[1] FROM items ORDER BY b", &[])
.unwrap();
assert_eq!(rows.rows.len(), 6);
for row in 0..6 {
assert_eq!(rows.value_at(row, 0), Some(&Value::Int(9)));
assert_eq!(rows.value_at(row, 1), Some(&Value::Int(row as i64 + 1)));
assert_eq!(
rows.value_at(row, 2),
Some(&Value::Int([5, 5, 7, 7, 11, 11][row]))
);
}
let rows = engine
.sql("SELECT id,flag FROM observed ORDER BY id", &[])
.unwrap();
assert_eq!(rows.rows.len(), 2);
for row in 0..2 {
assert_eq!(rows.value_at(row, 0), Some(&Value::Int(row as i64 + 1)));
assert_eq!(rows.value_at(row, 1), Some(&Value::Int(5)));
}
let audit = engine.sql("SELECT flag FROM audit", &[]).unwrap();
assert_eq!(audit.rows.len(), 1);
assert_eq!(audit.value_at(0, 0), Some(&Value::Int(5)));
}