uqa-engine 0.6.3

Engine: schema-aware table store, catalog restore, transactions
Documentation
//
// Unified Query Algebra
//
// Copyright (c) 2023-2026 Cognica, Inc.
//

use crate::tests::providers::Provider;
use uqa_core::Value;

#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn named_explain_borrows_the_selected_generic_executable(#[case] provider: Provider) {
    let directory = tempfile::tempdir().unwrap();
    let engine = provider.open(&directory.path().join("explain-selected.db"));
    let rows = std::iter::repeat_n("(1)", 8192)
        .collect::<Vec<_>>()
        .join(",");
    engine
        .sql(
            &format!("PREPARE large_explanation AS VALUES {rows}; SET plan_cache_mode = force_generic_plan"),
            &[],
        )
        .unwrap();
    check_selected_borrow(&engine);
    engine.sql("DEALLOCATE large_explanation", &[]).unwrap();
    engine.close().unwrap();
}

fn check_selected_borrow(engine: &crate::Engine) {
    let statement = "EXPLAIN (COSTS FALSE, MEMORY, FORMAT JSON) EXECUTE large_explanation";
    engine.sql(statement, &[]).unwrap();
    let selected = engine.session.prepared.read()["large_explanation"]
        .plan
        .clone()
        .unwrap();
    let retained = engine.query_retention_control().unwrap();
    let baseline = retained.memory().used();
    let occupied = retained
        .memory()
        .reserve(retained.memory().available() - 64 * 1024)
        .unwrap();
    let mut output = None;
    let allocation = allocation_counter::measure(|| {
        output = Some(engine.sql(statement, &[]).unwrap());
    });
    let output = output.unwrap();
    let Some(Value::Json(text)) = output.value_at(0, 0) else {
        panic!("JSON explanation");
    };
    let document: serde_json::Value = serde_json::from_str(text).unwrap();
    assert!(document[0]["Planning"]["Memory Used"].as_u64().unwrap() >= 1024);
    assert!(allocation.bytes_total < 256 * 1024, "{allocation:?}");
    drop(output);
    drop(occupied);
    assert_eq!(retained.memory().used(), baseline);
    let statements = engine.session.prepared.read();
    let cached = &statements["large_explanation"];
    assert!(cached.plan.as_ref().unwrap().ptr_eq(&selected));
    assert_eq!((cached.generic_plans, cached.custom_plans), (2, 0));
}

#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn utility_explain_borrows_its_query_until_optimization(#[case] provider: Provider) {
    use uqa_sql::plan::{ExecutablePlanOptimizer, UnifiedPlan};
    let directory = tempfile::tempdir().unwrap();
    let engine = provider.open(&directory.path().join("explain-utility-owner.db"));
    engine.sql("BEGIN", &[]).unwrap();
    let rows = std::iter::repeat_n("(1)", 8192)
        .collect::<Vec<_>>()
        .join(",");
    let mut baseline = None;
    for prefix in [
        "",
        "DECLARE explained_cursor CURSOR FOR ",
        "CREATE TABLE explained_table AS ",
        "CREATE MATERIALIZED VIEW explained_view AS ",
    ] {
        let sql = format!("EXPLAIN (COSTS FALSE, FORMAT JSON) {prefix}VALUES {rows}");
        let plan = engine
            .plan_for_execution(
                UnifiedPlan::lower(uqa_sql::compile(&sql).unwrap().remove(0)),
                &[],
            )
            .unwrap();
        let allocation = allocation_counter::measure(|| {
            let output = uqa_execution::statement::compiled::execute_selected_plan(
                &engine.compiled_statement_context(),
                &plan,
                &[],
            )
            .unwrap();
            assert!(!output.rows.is_empty());
        });
        if let Some(baseline) = baseline {
            assert!(
                allocation.bytes_total < baseline + 128 * 1024,
                "{prefix}: {allocation:?}, query baseline={baseline}"
            );
        } else {
            baseline = Some(allocation.bytes_total);
        }
    }
    engine.sql("ROLLBACK", &[]).unwrap();
    engine.close().unwrap();
}

#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn named_explain_prepares_private_sources_and_preserves_cache_recovery(#[case] provider: Provider) {
    let directory = tempfile::tempdir().unwrap();
    let path = directory.path().join("explain-private-sources.db");
    let engine = provider.open(&path);
    engine.sql("CREATE SEQUENCE explanation_ticks; CREATE TABLE explanation_writes (n bigint); CREATE VIEW explanation_input AS SELECT nextval('explanation_ticks') AS n; PREPARE read_explanation AS WITH q AS MATERIALIZED (SELECT n FROM explanation_input) SELECT n FROM q; PREPARE write_explanation AS INSERT INTO explanation_writes SELECT n FROM explanation_input RETURNING n; SET plan_cache_mode = force_generic_plan", &[]).unwrap();
    let retained = engine.query_retention_control().unwrap();
    for (index, name) in ["read_explanation", "write_explanation"]
        .into_iter()
        .enumerate()
    {
        // Compare private preparation within one catalog snapshot; autocommit refresh may legitimately retire a cached executable after EXPLAIN returns.
        engine
            .sql("BEGIN ISOLATION LEVEL REPEATABLE READ", &[])
            .unwrap();
        engine
            .sql(
                &format!("EXPLAIN (MEMORY, FORMAT JSON) EXECUTE {name}"),
                &[],
            )
            .unwrap();
        let selected = engine.session.prepared.read()[name]
            .plan
            .clone()
            .unwrap_or_else(|| panic!("{provider:?}, {name}: executable in the pinned snapshot"));
        let original = serde_json::to_value(selected.as_ref()).unwrap();
        engine.sql("SAVEPOINT retry_explanation", &[]).unwrap();
        let baseline = retained.memory().used();
        let occupied = retained
            .memory()
            .reserve(retained.memory().available())
            .unwrap();
        let analyze = format!("EXPLAIN (MEMORY, ANALYZE, FORMAT JSON) EXECUTE {name}");
        let error = engine.sql(&analyze, &[]).unwrap_err();
        assert_eq!(
            error.sqlstate(),
            Some("53200"),
            "{provider:?}, {name}: {error}"
        );
        drop(occupied);
        assert_eq!(retained.memory().used(), baseline);
        assert!(
            engine.transaction_failed(),
            "{provider:?}, {name}, depth={}: {error:?}",
            engine.transaction_depth()
        );
        engine.sql("ROLLBACK TO retry_explanation", &[]).unwrap();
        let result = engine.sql(&analyze, &[]).unwrap();
        let Some(Value::Json(text)) = result.value_at(0, 0) else {
            panic!("JSON explanation");
        };
        let document: serde_json::Value = serde_json::from_str(text).unwrap();
        assert_eq!(document[0]["Plan"]["Actual Rows"], 1);
        assert_eq!(
            engine
                .sql("SELECT currval('explanation_ticks')", &[])
                .unwrap()
                .value_at(0, 0),
            Some(&Value::Int(index as i64 + 1))
        );
        drop(result);
        engine.sql("COMMIT", &[]).unwrap();
        assert_eq!(serde_json::to_value(selected.as_ref()).unwrap(), original);
    }
    engine
        .sql(
            "BEGIN ISOLATION LEVEL REPEATABLE READ; EXPLAIN EXECUTE write_explanation",
            &[],
        )
        .unwrap();
    let original = engine.session.prepared.read()["write_explanation"]
        .plan
        .clone()
        .unwrap();
    engine.sql("CREATE OR REPLACE VIEW explanation_input AS SELECT nextval('explanation_ticks') + 100 AS n; EXPLAIN (ANALYZE, MEMORY, FORMAT JSON) EXECUTE write_explanation", &[]).unwrap();
    assert!(engine.session.prepared.read()["write_explanation"]
        .plan
        .as_ref()
        .is_none_or(|current| !current.ptr_eq(&original)));
    assert_eq!(
        engine
            .sql("SELECT max(n) FROM explanation_writes", &[])
            .unwrap()
            .value_at(0, 0),
        Some(&Value::Int(103))
    );
    drop(original);
    engine.sql("COMMIT", &[]).unwrap();
    engine.close().unwrap();
    drop(engine);
    assert_eq!(retained.memory().used(), 0);
    if !matches!(provider, Provider::Memory) {
        let engine = provider.open(&path);
        assert_eq!(
            engine
                .sql("SELECT count(*), max(n) FROM explanation_writes", &[])
                .unwrap()
                .value_at(0, 0),
            Some(&Value::Int(2))
        );
        engine.close().unwrap();
    }
}

#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn named_explanations_retain_their_prepared_inputs_and_native_effects(#[case] provider: Provider) {
    let reference: serde_json::Value = serde_json::from_str(include_str!(
        "../../../../../tests/parity/pg18/explain_named_inputs.expected.json"
    ))
    .unwrap();
    let directory = tempfile::tempdir().unwrap();
    for case in reference["cases"].as_array().unwrap() {
        let name = case["name"].as_str().unwrap();
        let engine = provider.open(&directory.path().join(format!("{name}.db")));
        engine
            .sql(&format!("BEGIN; {}", case["setup"].as_str().unwrap()), &[])
            .unwrap();
        let result = engine
            .sql(case["sql"].as_str().unwrap(), &[])
            .unwrap_or_else(|error| panic!("{name}: {error}"));
        let Some(Value::Json(text)) = result.value_at(0, 0) else {
            panic!("{name}: EXPLAIN JSON carrier");
        };
        let document: serde_json::Value = serde_json::from_str(text).unwrap();
        let plan = &document[0]["Plan"];
        assert_eq!(
            plan["Plan Rows"].as_f64(),
            case["rows"].as_f64(),
            "{name}: {plan}"
        );
        assert_eq!(plan["Plan Width"], case["width"], "{name}: {plan}");
        assert_eq!(
            plan.get("Actual Rows").and_then(serde_json::Value::as_f64),
            case["actual_rows"].as_f64(),
            "{name}: {plan}"
        );
        if let Some(sql) = case["after"].as_str() {
            assert_eq!(
                engine.sql(sql, &[]).unwrap().value_at(0, 0),
                Some(&Value::Int(case["next_value"].as_i64().unwrap())),
                "{name}"
            );
        }
        engine.sql("ROLLBACK", &[]).unwrap();
        engine.close().unwrap();
    }
}