rudb 0.4.2

//! `CREATE VIEW`, from both ends: the catalog rules and what a view does when it is selected from.
//!
//! Every sentence asserted here was read off the pinned duckdb on server2 rather than decided here,
//! which is v2.0.0-dev84237 at cc7e7bac7f, the commit the grammar is vendored from. The awkward ones
//! are awkward because the binary is. A collision names the type that is already there. A drop of
//! the wrong type says which is which even under `IF EXISTS`, and quotes the name. A short column
//! list renames a prefix rather than being an error, and a long one is refused. An insert into a
//! view says `is not an table`, article and all. And a view that expands forever comes back with its
//! name double quoted twice over.
//!
//! The last test is the reason the rest of them exist. `CREATE VIEW hits AS SELECT * FROM
//! read_parquet(...)` is what lets a published benchmark query that says `FROM hits` run against
//! rudb with nothing about it changed, which is what makes a number comparable to duckdb's.

use rudb::Database;
use rudb_common::Value;

/// The ClickBench fixture, ten thousand real rows of the real schema.
const HITS: &str = concat!(env!("CARGO_MANIFEST_DIR"), "/testdata/hits.parquet");

/// A database with the statements already run, panicking on the first that does not.
fn ran(statements: &[&str]) -> Database {
    let database = Database::new();
    for sql in statements {
        database.execute(sql).unwrap_or_else(|error| panic!("{sql} failed: {error}"));
    }
    database
}

/// The error the last statement gives, after the ones in front of it were run.
fn refused(statements: &[&str], sql: &str) -> String {
    let database = ran(statements);
    let error = database.execute(sql).expect_err(&format!("{sql} should be refused"));
    error.to_string()
}

#[test]
fn a_view_answers_with_the_rows_its_body_produces() {
    let database = ran(&["CREATE VIEW v AS SELECT 1 AS x, 'a' AS y"]);
    let result = database.query("SELECT * FROM v").expect("the view is there");
    assert_eq!(result.names(), ["x", "y"]);
    assert_eq!(result.len(), 1);
    assert_eq!(result.value_at(0, 0), Value::Integer(1));
}

#[test]
fn a_view_is_bound_again_at_every_reference_rather_than_frozen_when_it_was_made() {
    // The measured behaviour, and the whole reason the catalog keeps the text instead of a plan.
    // duckdb answers a view over `SELECT * FROM t` with a column that was added to `t` after the
    // view was created, so the star is expanded when the view is read and not when it is made.
    let database = ran(&[
        "CREATE TABLE t (i INTEGER)",
        "INSERT INTO t VALUES (1)",
        "CREATE VIEW v AS SELECT * FROM t",
        "DROP TABLE t",
        "CREATE TABLE t (i INTEGER, j INTEGER)",
        "INSERT INTO t VALUES (1, 2)",
    ]);
    let result = database.query("SELECT * FROM v").expect("the view follows the new table");
    assert_eq!(result.names(), ["i", "j"]);
}

#[test]
fn a_view_over_a_table_that_is_gone_complains_when_it_is_read() {
    let error = refused(
        &["CREATE TABLE t (i INTEGER)", "CREATE VIEW v AS SELECT * FROM t", "DROP TABLE t"],
        "SELECT * FROM v",
    );
    assert_eq!(error, "Catalog Error: Table with name t does not exist!");
}

#[test]
fn a_view_over_a_table_that_was_never_there_is_refused_when_it_is_made() {
    let error = refused(&[], "CREATE VIEW v AS SELECT * FROM nope");
    assert_eq!(error, "Catalog Error: Table with name nope does not exist!");
}

#[test]
fn a_view_and_a_table_are_one_namespace_and_the_message_names_what_is_already_there() {
    // The type in the sentence is the one in the way, not the one being made. It was the other way
    // round in v1.5.1 and upstream changed it, which is an argument for pinning the reference binary
    // to the vendored commit and not to whatever is released.
    assert_eq!(
        refused(&["CREATE VIEW v AS SELECT 1"], "CREATE TABLE v (i INTEGER)"),
        "Catalog Error: View with name \"v\" already exists!"
    );
    assert_eq!(
        refused(&["CREATE TABLE t (i INTEGER)"], "CREATE VIEW t AS SELECT 1"),
        "Catalog Error: Table with name \"t\" already exists!"
    );
    assert_eq!(
        refused(&["CREATE VIEW v AS SELECT 1"], "CREATE VIEW v AS SELECT 2"),
        "Catalog Error: View with name \"v\" already exists!"
    );
}

#[test]
fn dropping_one_as_the_other_says_which_is_which_even_under_if_exists() {
    assert_eq!(
        refused(&["CREATE VIEW v AS SELECT 1"], "DROP TABLE v"),
        "Catalog Error: Existing object \"v\" is of type View, trying to drop type Table"
    );
    assert_eq!(
        refused(&["CREATE TABLE t (i INTEGER)"], "DROP VIEW t"),
        "Catalog Error: Existing object \"t\" is of type Table, trying to drop type View"
    );
    // `IF EXISTS` is about the name not being there, not about it being something else.
    assert_eq!(
        refused(&["CREATE VIEW v AS SELECT 1"], "DROP TABLE IF EXISTS v"),
        "Catalog Error: Existing object \"v\" is of type View, trying to drop type Table"
    );
}

#[test]
fn a_dropped_view_is_gone_and_dropping_one_that_never_was_is_fine_under_if_exists() {
    let database = ran(&["CREATE VIEW v AS SELECT 1", "DROP VIEW v", "DROP VIEW IF EXISTS v"]);
    let error = database.query("SELECT * FROM v").expect_err("it is gone");
    // A read says table whatever the name might have been, because a query asking for `v` is asking
    // for something to read and does not care which of the two it would have been.
    assert_eq!(error.to_string(), "Catalog Error: Table with name v does not exist!");
    // A drop says which of the two it was dropping, because `DROP VIEW` said so.
    assert_eq!(
        refused(&[], "DROP VIEW gone"),
        "Catalog Error: View with name gone does not exist!"
    );
    assert_eq!(
        refused(&[], "DROP TABLE gone"),
        "Catalog Error: Table with name gone does not exist!"
    );
}

#[test]
fn or_replace_and_if_not_exists_in_one_statement_is_refused_by_the_parser() {
    // duckdb has no create rule with room for both and says so before it binds anything. The
    // vendored grammar has room for both, so rudb refuses it at the same stage with the same
    // sentence, and the same sentence covers the table form.
    let sentence = "Parser Error: Cannot specify both OR REPLACE and IF NOT EXISTS within single \
                    create statement";
    assert_eq!(refused(&[], "CREATE OR REPLACE VIEW IF NOT EXISTS v AS SELECT 1"), sentence);
    assert_eq!(refused(&[], "CREATE OR REPLACE TABLE IF NOT EXISTS t (i INTEGER)"), sentence);
}

#[test]
fn a_column_list_renames_a_prefix_and_a_list_longer_than_the_body_is_refused() {
    let database = ran(&["CREATE VIEW v (a) AS SELECT 1 AS x, 2 AS y"]);
    let result = database.query("SELECT * FROM v").expect("a short list is not an error");
    // The list runs out after the first column and the second keeps the name the body gave it.
    assert_eq!(result.names(), ["a", "y"]);
    assert_eq!(
        refused(&[], "CREATE VIEW v (a, b, c) AS SELECT 1, 2"),
        "Binder Error: More VIEW aliases than columns in query result"
    );
}

#[test]
fn a_renamed_column_answers_to_the_new_name_and_not_to_the_old_one() {
    let database = ran(&["CREATE VIEW v (a) AS SELECT 1 AS x"]);
    assert_eq!(database.value("SELECT v.a FROM v").expect("the new name"), Value::Integer(1));
    database.query("SELECT v.x FROM v").expect_err("the body's name is not visible through it");
}

#[test]
fn a_view_takes_an_alias_and_a_column_list_where_it_is_referenced() {
    let database = ran(&["CREATE VIEW v AS SELECT 1 AS x"]);
    let result = database.query("SELECT w.y FROM v AS w (y)").expect("aliased twice over");
    assert_eq!(result.names(), ["y"]);
    assert_eq!(result.value_at(0, 0), Value::Integer(1));
}

#[test]
fn or_replace_replaces_the_body_and_the_names_and_if_not_exists_keeps_the_first_one() {
    let replaced =
        ran(&["CREATE VIEW v AS SELECT 1 AS x", "CREATE OR REPLACE VIEW v AS SELECT 2 AS y"]);
    let result = replaced.query("SELECT * FROM v").expect("the second body");
    assert_eq!(result.names(), ["y"]);
    assert_eq!(result.value_at(0, 0), Value::Integer(2));

    let kept =
        ran(&["CREATE VIEW v AS SELECT 1 AS x", "CREATE VIEW IF NOT EXISTS v AS SELECT 2 AS y"]);
    let result = kept.query("SELECT * FROM v").expect("the first body");
    assert_eq!(result.names(), ["x"]);
}

#[test]
fn a_view_that_would_expand_forever_says_so_rather_than_running_out_of_stack() {
    // A cycle cannot be written directly, because the name a view is being created under does not
    // exist while its body binds. It takes `OR REPLACE` to close the loop after the fact.
    let error = refused(
        &[
            "CREATE VIEW a AS SELECT 1 AS x",
            "CREATE VIEW b AS SELECT * FROM a",
            "CREATE OR REPLACE VIEW a AS SELECT * FROM b",
        ],
        "SELECT * FROM a",
    );
    assert_eq!(
        error,
        "Binder Error: infinite recursion detected: attempting to recursively bind view \"\"a\"\""
    );
}

#[test]
fn writing_to_a_view_is_refused_in_the_binarys_own_grammar() {
    assert_eq!(
        refused(&["CREATE VIEW v AS SELECT 1 AS x"], "INSERT INTO v VALUES (2)"),
        "Catalog Error: v is not an table"
    );
}

#[test]
fn a_query_through_a_view_reads_the_columns_it_names_and_not_the_ones_the_body_lists() {
    // The reason a view expands inline instead of becoming a node. What the binder hands over is a
    // hundred columns wide when the view says `SELECT *`, and what runs has to be as narrow as the
    // query. On the real ClickBench partition this is the difference between 3.39 seconds and 0.009
    // seconds for the count, because a scan of no columns is a read of the Parquet footer.
    let database = ran(&[
        "CREATE TABLE t (a INTEGER, b VARCHAR, c INTEGER)",
        "CREATE VIEW v AS SELECT * FROM t",
    ]);
    assert_eq!(
        database.plan("SELECT COUNT(*) FROM v").expect("a plan"),
        "Project #3 [#2.0::BIGINT AS \"count_star()\"]\n  Aggregate #2 groups=[] aggregates=[count_star()::BIGINT]\n    Project #1 []\n      Get memory.main.t AS t #0 []\n"
    );
    // The filter is under the view's own projection rather than over it, which is filter pushdown,
    // and that is what lets the projection be one column wide instead of two.
    assert_eq!(
        database.plan("SELECT b FROM v WHERE c > 1").expect("a plan"),
        "Project #2 [#1.0::VARCHAR AS b]\n  Project #1 [#0.0::VARCHAR AS b]\n    Filter (#0.1::INTEGER > 1::INTEGER)::BOOLEAN\n      Get memory.main.t AS t #0 [b::VARCHAR, c::INTEGER]\n"
    );
}

#[test]
fn a_view_over_a_parquet_file_runs_the_published_benchmark_sql_unmodified() {
    // The point of the whole feature. These four are ClickBench q1, q2, q3 and q5 as published,
    // character for character, semicolons and all, and the only thing that makes them run against
    // a Parquet file is the view. The answers are the ones in `testdata/clickbench-answers.txt`,
    // which duckdb wrote over this same fixture.
    let sql = format!("CREATE VIEW hits AS SELECT * FROM read_parquet('{HITS}')");
    let database = ran(&[&sql]);

    assert_eq!(database.value("SELECT COUNT(*) FROM hits;").expect("q1"), Value::BigInt(10_000));
    assert_eq!(
        database.value("SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;").expect("q2"),
        Value::BigInt(4_844)
    );
    assert_eq!(
        database.value("SELECT COUNT(DISTINCT UserID) FROM hits;").expect("q5"),
        Value::BigInt(141)
    );

    let result = database
        .query("SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;")
        .expect("q3");
    assert_eq!(result.len(), 1);
    assert_eq!(result.value_at(0, 1), Value::BigInt(10_000));
    assert_eq!(result.value_at(0, 2), Value::Double(699.5));
}

/// A file holding a database the statements were run against, and the path it is at.
///
/// Opened, written to and dropped, so what comes back is a committed file and not a handle. Every
/// test below reopens it, because the question all of them ask is what the file says rather than
/// what the session that wrote it remembered.
fn written(tag: &str, statements: &[&str]) -> std::path::PathBuf {
    let path = std::env::temp_dir().join(format!("rudb-view-{tag}-{}.rudb", std::process::id()));
    let _ = std::fs::remove_file(&path);
    let database =
        Database::open(path.to_str().expect("a UTF-8 temporary path")).expect("a native database");
    for sql in statements {
        database.execute(sql).unwrap_or_else(|error| panic!("{sql} failed: {error}"));
    }
    drop(database);
    path
}

/// The database in a file that was already written, opened again.
fn reopened(path: &std::path::Path) -> Database {
    Database::open(path.to_str().expect("a UTF-8 temporary path")).expect("the file opens")
}

#[test]
fn a_view_is_still_there_when_the_file_is_opened_again() {
    let path = written(
        "plain",
        &[
            "CREATE TABLE t (x INTEGER)",
            "INSERT INTO t VALUES (1), (2), (-3)",
            "CREATE VIEW v AS SELECT x FROM t WHERE x > 0",
        ],
    );
    let database = reopened(&path);
    assert_eq!(
        database.value("SELECT count(*) FROM v").expect("the view is there"),
        Value::BigInt(2)
    );
    // The column list came out of the file rather than out of a bind, so it is the answer before
    // anything in this process has looked at the body. That is the pin's answer after an open too.
    let bound = database
        .query("SELECT column_count, is_bound FROM duckdb_views() WHERE view_name = 'v'")
        .expect("the catalog table");
    assert_eq!(bound.value_at(0, 0), Value::BigInt(1));
    assert_eq!(bound.value_at(0, 1), Value::Boolean(true));
    drop(database);
    let _ = std::fs::remove_file(&path);
}

#[test]
fn the_alias_list_and_the_written_statement_survive_the_file_too() {
    let path = written(
        "aliases",
        &[
            "CREATE TABLE t (x INTEGER, s VARCHAR)",
            "INSERT INTO t VALUES (1, 'one')",
            "CREATE VIEW v (a) AS SELECT x, s FROM t",
        ],
    );
    let database = reopened(&path);
    let result = database.query("SELECT a, s FROM v").expect("a renames the first column only");
    assert_eq!(result.value_at(0, 0), Value::Integer(1));
    assert_eq!(result.value_at(0, 1), Value::Varchar("one".to_string()));
    assert_eq!(
        database.value("SELECT sql FROM duckdb_views() WHERE view_name = 'v'").expect("the sql"),
        Value::Varchar("CREATE VIEW v (a) AS SELECT x, s FROM t;".to_string())
    );
    drop(database);
    let _ = std::fs::remove_file(&path);
}

#[test]
fn a_dropped_view_does_not_come_back_out_of_the_file() {
    let path = written("dropped", &["CREATE VIEW v AS SELECT 1 AS x"]);
    let database = reopened(&path);
    database.execute("DROP VIEW v").expect("it is there to drop");
    drop(database);
    let database = reopened(&path);
    assert_eq!(
        database.value("SELECT count(*) FROM duckdb_views() WHERE NOT internal").expect("a count"),
        Value::BigInt(0)
    );
    drop(database);
    let _ = std::fs::remove_file(&path);
}

/// A database with no table in it is still a database and a view is a reason to have one.
#[test]
fn a_view_over_no_table_at_all_is_written_and_read_back() {
    let path = written("tableless", &["CREATE VIEW v AS SELECT 42 AS answer"]);
    let database = reopened(&path);
    assert_eq!(database.value("SELECT answer FROM v").expect("the view"), Value::Integer(42));
    drop(database);
    let _ = std::fs::remove_file(&path);
}

/// A temporary view goes when the session does, the same way a temporary table does.
#[test]
fn a_temporary_view_is_not_written_to_the_file() {
    let path = written(
        "temporary",
        &["CREATE VIEW kept AS SELECT 1 AS x", "CREATE TEMPORARY VIEW gone AS SELECT 2 AS x"],
    );
    let database = reopened(&path);
    let names = database
        .query("SELECT view_name FROM duckdb_views() WHERE NOT internal ORDER BY view_name")
        .expect("the catalog table");
    assert_eq!(names.len(), 1);
    assert_eq!(names.value_at(0, 0), Value::Varchar("kept".to_string()));
    drop(database);
    let _ = std::fs::remove_file(&path);
}

/// The views the engine ships with are in every catalog already, so a file must not name them.
#[test]
fn the_internal_views_are_not_written_into_the_file() {
    let path = written("internal", &["CREATE VIEW v AS SELECT 1 AS x"]);
    let database = reopened(&path);
    // One of each, and no doubles. A file that had written the engine's own would show them twice
    // here, once from the file and once from the catalog every session starts with.
    let doubled = database
        .value("SELECT count(*) FROM (SELECT view_name FROM duckdb_views() GROUP BY view_name HAVING count(*) > 1)")
        .expect("a count of names that appear twice");
    assert_eq!(doubled, Value::BigInt(0));
    drop(database);
    let _ = std::fs::remove_file(&path);
}

/// A view made against a database whose tables are already committed reaches the file too.
///
/// The checkpoint this takes writes a new catalog over the same pages rather than the whole file
/// again, which is asserted where it can be seen, in `rudb_native`'s
/// `restating_the_views_leaves_every_table_where_it_was`. What is worth asserting from out here is
/// that the path is taken at all and that the table is still readable through the generation it
/// commits, since carrying the wrong directory pointers forward is how that would go wrong.
#[test]
fn a_view_made_over_a_table_already_in_the_file_is_written_too() {
    let path = written("restate", &["CREATE TABLE t AS SELECT range AS x FROM range(50000)"]);
    let database = reopened(&path);
    database.execute("CREATE VIEW v AS SELECT count(*) FROM t").expect("a view");
    drop(database);
    let database = reopened(&path);
    assert_eq!(database.value("SELECT * FROM v").expect("the view"), Value::BigInt(50_000));
    assert_eq!(database.value("SELECT count(*) FROM t").expect("the table"), Value::BigInt(50_000));
    assert_eq!(database.value("SELECT max(x) FROM t").expect("the pages"), Value::BigInt(49_999));
    drop(database);
    let _ = std::fs::remove_file(&path);
}