spg-engine 7.37.21

//! v7.37.7 (sentori Epic 3 P1) — `GENERATED ALWAYS AS (<expr>) STORED`
//! stored computed-column acceptance.
//!
//! sentori's lone usage is `issues.search_vector tsvector GENERATED
//! ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' ||
//! coalesce(description, ''))) STORED`. The cases below pin that
//! shape end-to-end plus the more general INSERT-vs-UPDATE recompute
//! contract.
//!
//! Out of scope: GENERATED ALWAYS AS (expr) VIRTUAL (parser rejects),
//! GENERATED BY DEFAULT AS expression (PG carve-out — only STORED at
//! v7.37.7).

use spg_engine::{Engine, QueryResult};
use spg_storage::Value;

fn rows(e: &mut Engine, sql: &str) -> Vec<Vec<Value<'static>>> {
    let r = e
        .execute(sql)
        .unwrap_or_else(|err| panic!("{sql}: {err:?}"));
    let QueryResult::Rows { rows, .. } = r else {
        panic!("expected Rows for {sql}");
    };
    rows.into_iter().map(|r| r.values).collect()
}

fn one_text(e: &mut Engine, sql: &str) -> String {
    let mut rs = rows(e, sql);
    let row = rs.pop().expect("one row");
    match row.into_iter().next().expect("one col") {
        Value::Text(s) => s.into_owned(),
        other => panic!("expected text, got {other:?}"),
    }
}

fn one_i64(e: &mut Engine, sql: &str) -> i64 {
    let mut rs = rows(e, sql);
    let row = rs.pop().expect("one row");
    match row.into_iter().next().expect("one col") {
        Value::BigInt(n) => n,
        Value::Int(n) => i64::from(n),
        other => panic!("expected integer, got {other:?}"),
    }
}

/// INSERT into a table with a stored generated column auto-fills the
/// computed cell from sibling columns, even when the user does not
/// list it in the column list.
#[test]
fn insert_auto_fills_generated_stored_column() {
    let mut e = Engine::new();
    e.execute(
        "CREATE TABLE box_with_area (
            id BIGINT NOT NULL,
            w BIGINT NOT NULL,
            h BIGINT NOT NULL,
            area BIGINT GENERATED ALWAYS AS (w * h) STORED
         )",
    )
    .expect("CREATE TABLE with GENERATED column");
    e.execute("INSERT INTO box_with_area (id, w, h) VALUES (1, 3, 4)")
        .expect("INSERT routes through generated recompute");
    assert_eq!(
        one_i64(&mut e, "SELECT area FROM box_with_area WHERE id = 1"),
        12
    );
}

/// A user-supplied value in the generated column slot is overwritten
/// by the recomputed value — PG-strict "you don't get to set it"
/// semantics.
#[test]
fn insert_rejects_user_supplied_generated_value() {
    // v7.38 (read01 P6.41) — PG rejects an explicit value for a generated
    // column ("cannot insert a non-DEFAULT value into column …") rather than
    // silently ignoring it; the column list must omit the generated column.
    let mut e = Engine::new();
    e.execute(
        "CREATE TABLE box_with_area (
            id BIGINT NOT NULL,
            w BIGINT NOT NULL,
            h BIGINT NOT NULL,
            area BIGINT GENERATED ALWAYS AS (w * h) STORED
         )",
    )
    .unwrap();
    assert!(
        e.execute("INSERT INTO box_with_area (id, w, h, area) VALUES (1, 5, 6, 999)")
            .is_err(),
        "explicit value for a generated column must be rejected"
    );
    // Omitting the generated column works and computes it.
    e.execute("INSERT INTO box_with_area (id, w, h) VALUES (1, 5, 6)")
        .expect("omitting the generated column is fine");
    assert_eq!(
        one_i64(&mut e, "SELECT area FROM box_with_area WHERE id = 1"),
        30,
    );
}

/// UPDATEing a sibling column recomputes the stored generated column.
#[test]
fn update_recomputes_generated_stored_column() {
    let mut e = Engine::new();
    e.execute(
        "CREATE TABLE box_with_area (
            id BIGINT NOT NULL,
            w BIGINT NOT NULL,
            h BIGINT NOT NULL,
            area BIGINT GENERATED ALWAYS AS (w * h) STORED
         )",
    )
    .unwrap();
    e.execute("INSERT INTO box_with_area (id, w, h) VALUES (1, 2, 5)")
        .unwrap();
    assert_eq!(
        one_i64(&mut e, "SELECT area FROM box_with_area WHERE id = 1"),
        10
    );
    e.execute("UPDATE box_with_area SET w = 7 WHERE id = 1")
        .unwrap();
    // 7 * 5 = 35; the engine recomputes area even though UPDATE only
    // bound `w`.
    assert_eq!(
        one_i64(&mut e, "SELECT area FROM box_with_area WHERE id = 1"),
        35
    );
}

/// sentori-shaped probe — tsvector materialised by concatenating
/// COALESCEd title + description through to_tsvector. After INSERT,
/// the generated column holds the canonical tsvector text;
/// SELECTing it back round-trips the text form.
#[test]
fn sentori_issues_search_vector_shape() {
    let mut e = Engine::new();
    e.execute(
        "CREATE TABLE issues (
            id BIGINT NOT NULL,
            title TEXT,
            description TEXT,
            search_vector TSVECTOR GENERATED ALWAYS AS (
                to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(description, ''))
            ) STORED
         )",
    )
    .expect("CREATE TABLE issues");
    e.execute(
        "INSERT INTO issues (id, title, description) VALUES \
         (1, 'login flow', 'race when token expires')",
    )
    .expect("INSERT issue");
    // The computed tsvector renders as a canonical lexeme list. With
    // the `simple` config no stop-words / stemming kicks in, so each
    // unique token survives. Order is canonical; we just look for two
    // representative lexemes the original text supplied.
    let rendered = one_text(
        &mut e,
        "SELECT CAST(search_vector AS TEXT) FROM issues WHERE id = 1",
    );
    assert!(
        rendered.contains("login"),
        "expected 'login' in tsvector text: {rendered}"
    );
    assert!(
        rendered.contains("token"),
        "expected 'token' in tsvector text: {rendered}"
    );
    // NULL title + non-null description still produces a non-empty
    // tsvector (COALESCE feeds an empty string instead of NULL).
    e.execute(
        "INSERT INTO issues (id, title, description) VALUES \
         (2, NULL, 'orphan')",
    )
    .expect("INSERT NULL title");
    let rendered2 = one_text(
        &mut e,
        "SELECT CAST(search_vector AS TEXT) FROM issues WHERE id = 2",
    );
    assert!(
        rendered2.contains("orphan"),
        "expected 'orphan' in tsvector text: {rendered2}"
    );
}

#[test]
fn drop_expression_converts_to_plain_column() {
    // v7.38 (read01 U10) — ALTER COLUMN … DROP EXPRESSION de-generates a
    // stored column: a supplied value is then accepted verbatim instead of
    // being recomputed. Existing rows keep their computed value.
    // Live-PG18.4-verified: (5,10) then (10,999).
    let mut e = Engine::new();
    e.execute("CREATE TABLE t(id int, g int GENERATED ALWAYS AS (id*2) STORED)")
        .unwrap();
    e.execute("INSERT INTO t(id) VALUES(5)").unwrap();
    e.execute("ALTER TABLE t ALTER COLUMN g DROP EXPRESSION")
        .unwrap();
    // Now g is a plain column — a direct value is stored as given.
    e.execute("INSERT INTO t(id,g) VALUES(10, 999)").unwrap();
    let out = rows(&mut e, "SELECT id,g FROM t ORDER BY id");
    assert_eq!(out[0], vec![Value::Int(5), Value::Int(10)]);
    assert_eq!(out[1], vec![Value::Int(10), Value::Int(999)]);
    // DROP EXPRESSION on a non-generated column errors.
    assert!(
        e.execute("ALTER TABLE t ALTER COLUMN id DROP EXPRESSION")
            .is_err()
    );
}

#[test]
fn set_expression_swaps_and_recomputes() {
    // v7.38 (read01 U12) — ALTER COLUMN … SET EXPRESSION AS (expr) swaps a
    // stored generated column's expression and recomputes existing rows.
    // Live-PG18.4-verified: (5,50) then (7,70).
    let mut e = Engine::new();
    e.execute("CREATE TABLE t(id int, g int GENERATED ALWAYS AS (id*2) STORED)")
        .unwrap();
    e.execute("INSERT INTO t(id) VALUES(5)").unwrap();
    e.execute("ALTER TABLE t ALTER COLUMN g SET EXPRESSION AS (id*10)")
        .unwrap();
    e.execute("INSERT INTO t(id) VALUES(7)").unwrap();
    let out = rows(&mut e, "SELECT id,g FROM t ORDER BY id");
    assert_eq!(out[0], vec![Value::Int(5), Value::Int(50)]); // recomputed
    assert_eq!(out[1], vec![Value::Int(7), Value::Int(70)]); // new expr
    // SET EXPRESSION on a non-generated column errors.
    assert!(
        e.execute("ALTER TABLE t ALTER COLUMN id SET EXPRESSION AS (id*5)")
            .is_err()
    );
}