graphitesql 0.0.12

//! Phase 9: scalar subqueries and `IN (SELECT …)` (uncorrelated).

#![cfg(feature = "std")]

use graphitesql::{Connection, Value};

fn setup() -> Connection {
    let mut c = Connection::open_memory().unwrap();
    c.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, a INT)")
        .unwrap();
    c.execute("INSERT INTO t(a) VALUES (10), (20), (30), (40)")
        .unwrap();
    c.execute("CREATE TABLE u(id INTEGER PRIMARY KEY, a INT)")
        .unwrap();
    c.execute("INSERT INTO u(a) VALUES (20), (40)").unwrap();
    c
}

#[test]
fn in_select() {
    let c = setup();
    let r = c
        .query("SELECT a FROM t WHERE a IN (SELECT a FROM u) ORDER BY a")
        .unwrap();
    let got: Vec<i64> = r
        .rows
        .iter()
        .map(|row| match row[0] {
            Value::Integer(v) => v,
            _ => panic!(),
        })
        .collect();
    assert_eq!(got, vec![20, 40]);

    let r = c
        .query("SELECT count(*) FROM t WHERE a NOT IN (SELECT a FROM u)")
        .unwrap();
    assert_eq!(r.rows[0][0], Value::Integer(2)); // 10 and 30
}

#[test]
fn scalar_subquery() {
    let c = setup();
    // As a standalone value.
    let r = c.query("SELECT (SELECT max(a) FROM t)").unwrap();
    assert_eq!(r.rows[0][0], Value::Integer(40));

    // In a projection and in a predicate.
    let r = c
        .query("SELECT a, (SELECT count(*) FROM u) AS uc FROM t WHERE a = 30")
        .unwrap();
    assert_eq!(r.rows[0][0], Value::Integer(30));
    assert_eq!(r.rows[0][1], Value::Integer(2));

    let r = c
        .query("SELECT a FROM t WHERE a > (SELECT min(a) FROM u) ORDER BY a")
        .unwrap();
    let got: Vec<i64> = r
        .rows
        .iter()
        .map(|row| match row[0] {
            Value::Integer(v) => v,
            _ => panic!(),
        })
        .collect();
    assert_eq!(got, vec![30, 40]); // a > 20
}

#[test]
fn scalar_subquery_no_rows_is_null() {
    let c = setup();
    let r = c.query("SELECT (SELECT a FROM u WHERE a = 999)").unwrap();
    assert_eq!(r.rows[0][0], Value::Null);
}

// ---- correlated subqueries & EXISTS -----------------------------------------

fn ints(c: &Connection, sql: &str) -> Vec<i64> {
    c.query(sql)
        .unwrap()
        .rows
        .iter()
        .map(|row| match row[0] {
            Value::Integer(v) => v,
            ref o => panic!("not int: {o:?}"),
        })
        .collect()
}

#[test]
fn correlated_exists() {
    let c = setup();
    // t rows that also appear in u.
    let got = ints(
        &c,
        "SELECT a FROM t WHERE EXISTS (SELECT 1 FROM u WHERE u.a = t.a) ORDER BY a",
    );
    assert_eq!(got, vec![20, 40]);
}

#[test]
fn correlated_not_exists() {
    let c = setup();
    let got = ints(
        &c,
        "SELECT a FROM t WHERE NOT EXISTS (SELECT 1 FROM u WHERE u.a = t.a) ORDER BY a",
    );
    assert_eq!(got, vec![10, 30]);
}

#[test]
fn correlated_scalar_in_projection() {
    let c = setup();
    // Count of u rows less than each t.a.
    let r = c
        .query("SELECT t.a, (SELECT count(*) FROM u WHERE u.a < t.a) FROM t ORDER BY t.a")
        .unwrap();
    let got: Vec<(i64, i64)> = r
        .rows
        .iter()
        .map(|row| match (&row[0], &row[1]) {
            (Value::Integer(a), Value::Integer(b)) => (*a, *b),
            _ => panic!(),
        })
        .collect();
    // u = {20,40}: for 10->0, 20->0, 30->1, 40->1
    assert_eq!(got, vec![(10, 0), (20, 0), (30, 1), (40, 1)]);
}

#[test]
fn correlated_in_where_predicate() {
    let c = setup();
    // t.a greater than the matching-or-any u.a (correlated comparison).
    let got = ints(
        &c,
        "SELECT a FROM t WHERE a > (SELECT min(u.a) FROM u WHERE u.a >= t.a) - 1 ORDER BY a",
    );
    // For each t.a: min u.a >= t.a, minus 1. 10->min(20,40)=20-1=19, 10>19? no.
    // 20->20-1=19, 20>19 yes. 30->40-1=39,30>39 no. 40->40-1=39,40>39 yes.
    assert_eq!(got, vec![20, 40]);
}

#[test]
fn subquery_in_from() {
    let c = setup();
    // Derived table as the sole source.
    let got = ints(
        &c,
        "SELECT x FROM (SELECT a AS x FROM t WHERE a > 15) ORDER BY x",
    );
    assert_eq!(got, vec![20, 30, 40]);

    // Aggregate over a derived table.
    let r = c
        .query("SELECT count(*), sum(x) FROM (SELECT a AS x FROM t WHERE a > 15)")
        .unwrap();
    assert_eq!(r.rows[0][0], Value::Integer(3));
    assert_eq!(r.rows[0][1], Value::Integer(90));

    // Derived table joined against a base table.
    let got = ints(
        &c,
        "SELECT sub.a FROM (SELECT a FROM t WHERE a >= 20) AS sub \
         JOIN u ON sub.a = u.a ORDER BY sub.a",
    );
    assert_eq!(got, vec![20, 40]);
}

#[test]
fn correlated_against_sqlite3() {
    use std::process::Command;
    if Command::new("sqlite3").arg("--version").output().is_err() {
        eprintln!("sqlite3 not found; skipping");
        return;
    }
    // Build identical data in sqlite and graphitesql, run a battery of correlated
    // queries, compare output.
    let setup_sql = "CREATE TABLE t(id INTEGER PRIMARY KEY, a INT, g INT);\
        CREATE TABLE u(id INTEGER PRIMARY KEY, t_id INT, w INT);";
    let mut inserts = String::new();
    for i in 1..=12i64 {
        inserts += &format!(
            "INSERT INTO t(id,a,g) VALUES ({i},{},{});",
            i * 3 % 7,
            i % 3
        );
    }
    for i in 1..=18i64 {
        inserts += &format!(
            "INSERT INTO u(id,t_id,w) VALUES ({i},{},{});",
            i % 12 + 1,
            i % 5
        );
    }

    let path = std::env::temp_dir().join(format!("gsql-corr-{}.db", std::process::id()));
    let path = path.to_string_lossy().into_owned();
    let _ = std::fs::remove_file(&path);
    let out = Command::new("sqlite3")
        .arg(&path)
        .arg(format!("{setup_sql}{inserts}"))
        .output()
        .unwrap();
    assert!(out.status.success());

    let mut g = Connection::open_memory().unwrap();
    for s in setup_sql.split(';') {
        if !s.trim().is_empty() {
            g.execute(s).unwrap();
        }
    }
    for s in inserts.split(';') {
        if !s.trim().is_empty() {
            g.execute(s).unwrap();
        }
    }

    let queries = [
        "SELECT a FROM t WHERE EXISTS (SELECT 1 FROM u WHERE u.t_id = t.id) ORDER BY id",
        "SELECT id FROM t WHERE NOT EXISTS (SELECT 1 FROM u WHERE u.t_id = t.id) ORDER BY id",
        "SELECT id, (SELECT count(*) FROM u WHERE u.t_id = t.id) FROM t ORDER BY id",
        "SELECT id, (SELECT sum(w) FROM u WHERE u.t_id = t.id) FROM t ORDER BY id",
        "SELECT id FROM t WHERE (SELECT count(*) FROM u WHERE u.t_id = t.id) > 1 ORDER BY id",
        "SELECT t.id, t.a FROM t WHERE t.a > (SELECT avg(w) FROM u WHERE u.t_id = t.id) ORDER BY t.id",
        "SELECT count(*) FROM t WHERE EXISTS (SELECT 1 FROM u WHERE u.t_id = t.id AND u.w = t.g)",
    ];
    for q in queries {
        let want = {
            let o = Command::new("sqlite3").arg(&path).arg(q).output().unwrap();
            String::from_utf8_lossy(&o.stdout).trim_end().to_string()
        };
        let r = g.query(q).unwrap();
        let got = r
            .rows
            .iter()
            .map(|row| {
                row.iter()
                    .map(|v| match v {
                        Value::Null => String::new(),
                        Value::Integer(i) => i.to_string(),
                        Value::Real(r) => format!("{r}"),
                        Value::Text(s) => s.clone(),
                        Value::Blob(b) => b.iter().map(|x| format!("{x:02x}")).collect(),
                    })
                    .collect::<Vec<_>>()
                    .join("|")
            })
            .collect::<Vec<_>>()
            .join("\n");
        assert_eq!(got, want, "query: {q}");
    }
    let _ = std::fs::remove_file(&path);
}

#[test]
fn in_values_clause() {
    // `x IN (VALUES …)` — a VALUES clause is a query body, like `IN (SELECT …)`.
    let mut c = Connection::open_memory().unwrap();
    c.execute("CREATE TABLE t(a)").unwrap();
    c.execute("INSERT INTO t VALUES (2),(5),(9)").unwrap();

    assert_eq!(
        ints(&c, "SELECT a FROM t WHERE a IN (VALUES(2),(9)) ORDER BY a"),
        vec![2, 9]
    );
    assert_eq!(
        ints(
            &c,
            "SELECT a FROM t WHERE a NOT IN (VALUES(2),(9)) ORDER BY a"
        ),
        vec![5]
    );
    // Scalar membership in a constant VALUES set.
    assert_eq!(ints(&c, "SELECT 1 IN (VALUES(1),(2),(3))"), vec![1]);
    assert_eq!(ints(&c, "SELECT 5 IN (VALUES(1),(2))"), vec![0]);
    // Row-value membership: the LHS tuple is matched against a multi-column VALUES.
    assert_eq!(ints(&c, "SELECT (1,2) IN (VALUES(1,2),(3,4))"), vec![1]);
    assert_eq!(ints(&c, "SELECT (1,2) IN (VALUES(5,6),(7,8))"), vec![0]);
}

#[test]
fn derived_column_inherits_affinity_and_collation() {
    // A derived-table column inherits its origin column's affinity and collation
    // (a direct reference, through parens / an explicit COLLATE), matching sqlite;
    // an expression column is BLOB affinity + BINARY collation.
    let mut c = Connection::open_memory().unwrap();
    c.execute("CREATE TABLE u(a TEXT COLLATE NOCASE, n INT)")
        .unwrap();
    c.execute("INSERT INTO u VALUES ('A',5),('b',1)").unwrap();

    let txt = |sql: &str| -> Vec<String> {
        c.query(sql)
            .unwrap()
            .rows
            .iter()
            .map(|r| match &r[0] {
                Value::Text(s) => s.clone(),
                Value::Integer(i) => i.to_string(),
                Value::Null => String::new(),
                o => panic!("unexpected: {o:?}"),
            })
            .collect()
    };
    // NOCASE collation flows through the derived table → 'a' matches 'A'.
    assert_eq!(
        txt("SELECT a FROM (SELECT a FROM u) WHERE a = 'a'"),
        vec!["A"]
    );
    assert_eq!(
        txt("SELECT a FROM (SELECT a AS a FROM u) WHERE a IN ('a') ORDER BY a"),
        vec!["A"]
    );
    assert_eq!(
        txt("SELECT count(*) FROM (SELECT a FROM u) WHERE a = 'B'"),
        vec!["1"]
    );
    // Through `*` and an aliased reference, too.
    assert_eq!(
        txt("SELECT x FROM (SELECT a AS x FROM u) WHERE x = 'a'"),
        vec!["A"]
    );
    // An explicit COLLATE in the subquery overrides: BINARY here → 'a' != 'A'.
    assert!(txt("SELECT a FROM (SELECT a COLLATE BINARY AS a FROM u) WHERE a = 'a'").is_empty());
    // Collation flows through NESTED single-source derived tables, any depth.
    assert_eq!(
        txt("SELECT a FROM (SELECT a FROM (SELECT a FROM u)) WHERE a = 'a'"),
        vec!["A"]
    );
    assert_eq!(
        txt("SELECT x FROM (SELECT a AS x FROM (SELECT a FROM (SELECT a FROM u))) WHERE x = 'a'"),
        vec!["A"]
    );

    // INTEGER affinity inherited through a derived column: '2' coerces to 2.
    let mut c = Connection::open_memory().unwrap();
    c.execute("CREATE TABLE t(a INT)").unwrap();
    c.execute("INSERT INTO t VALUES (2),(10)").unwrap();
    assert_eq!(
        c.query("SELECT a FROM (SELECT a FROM t) WHERE a = '2'")
            .unwrap()
            .rows
            .len(),
        1
    );
}

#[test]
fn scalar_subquery_must_return_one_column() {
    // A scalar subquery used as a value must yield exactly one column; sqlite
    // rejects `(SELECT 1, 2)` ("sub-select returns 2 columns - expected 1")
    // rather than silently taking the first column.
    let mut c = Connection::open_memory().unwrap();
    let e = c.query("SELECT (SELECT 1, 2)").unwrap_err();
    assert!(
        format!("{e}").contains("sub-select returns 2 columns"),
        "{e}"
    );
    // In a WHERE/comparison position too.
    assert!(c
        .query("SELECT * FROM (SELECT 1) WHERE (SELECT 1, 2)")
        .is_err());
    // `SELECT *` over a multi-column table in scalar position is rejected.
    c.execute("CREATE TABLE t(a, b)").unwrap();
    c.execute("INSERT INTO t VALUES (1, 2)").unwrap();
    assert!(c.query("SELECT (SELECT * FROM t)").is_err());
    // A single-column scalar subquery still works.
    assert_eq!(
        c.query("SELECT (SELECT a FROM t)").unwrap().rows[0][0],
        Value::Integer(1)
    );
    assert_eq!(
        c.query("SELECT (SELECT max(a) FROM t)").unwrap().rows[0][0],
        Value::Integer(1)
    );
    // Row-value / IN subqueries (a separate path) keep working with >1 column.
    assert_eq!(
        c.query("SELECT count(*) FROM t WHERE (a, b) IN (SELECT a, b FROM t)")
            .unwrap()
            .rows[0][0],
        Value::Integer(1)
    );
}

#[test]
fn row_value_compared_to_row_subquery() {
    // `(a, b) OP (SELECT x, y)` (and the swapped form) compares the row value
    // against the subquery's first row, element-wise — sqlite supports it; graphite
    // used to error. An empty subquery makes the comparison NULL; a width mismatch
    // errors.
    let mut c = Connection::open_memory().unwrap();
    c.execute("CREATE TABLE t(a, b)").unwrap();
    c.execute("INSERT INTO t VALUES (1, 2), (3, 4), (1, 9)")
        .unwrap();
    let n = |q: &str| c.query(q).unwrap().rows[0][0].clone();
    assert_eq!(n("SELECT (1,2) = (SELECT 1, 2)"), Value::Integer(1));
    assert_eq!(n("SELECT (1,2) <> (SELECT 3, 4)"), Value::Integer(1));
    assert_eq!(n("SELECT (1,2) < (SELECT 1, 3)"), Value::Integer(1));
    assert_eq!(n("SELECT (1,2) > (SELECT 1, 1)"), Value::Integer(1));
    // Swapped (subquery on the left).
    assert_eq!(n("SELECT (SELECT 1, 2) = (1, 2)"), Value::Integer(1));
    assert_eq!(n("SELECT (SELECT 3, 4) > (1, 2)"), Value::Integer(1));
    // Against table rows.
    assert_eq!(
        n("SELECT count(*) FROM t WHERE (a, b) = (SELECT a, b FROM t WHERE a = 3)"),
        Value::Integer(1)
    );
    // An empty subquery → NULL; a NULL element → NULL.
    assert_eq!(n("SELECT (1,2) = (SELECT 1, 2 WHERE 0)"), Value::Null);
    assert_eq!(n("SELECT (1, NULL) = (SELECT 1, 2)"), Value::Null);
    // A width mismatch is an error.
    assert!(c.query("SELECT (1,2) = (SELECT 1)").is_err());
    // A genuine scalar subquery (one value expected) still rejects >1 column.
    assert!(c.query("SELECT (SELECT 1, 2)").is_err());
}