motedb 0.8.0

//! Tests for advanced SQL features: CAST, prepared statements with params,
//! complex expressions, UPDATE with expressions, DELETE with subqueries,
//! nested queries, UNION-like patterns, edge cases

use motedb::{types::Value, Database};
use tempfile::TempDir;

fn rows(result: motedb::StreamingQueryResult) -> Vec<Vec<Value>> {
    use motedb::QueryResult;
    match result.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("Expected Select result"),
    }
}

fn row(result: motedb::StreamingQueryResult) -> Vec<Value> {
    let r = rows(result);
    assert_eq!(r.len(), 1);
    r.into_iter().next().unwrap()
}

fn setup_orders() -> (Database, TempDir) {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE orders (id INT PRIMARY KEY, customer TEXT, product TEXT, amount FLOAT, qty INT)").unwrap();
    db.execute("INSERT INTO orders VALUES (1, 'Alice', 'Widget', 10.0, 5)")
        .unwrap();
    db.execute("INSERT INTO orders VALUES (2, 'Bob', 'Gadget', 25.0, 3)")
        .unwrap();
    db.execute("INSERT INTO orders VALUES (3, 'Alice', 'Gadget', 30.0, 2)")
        .unwrap();
    db.execute("INSERT INTO orders VALUES (4, 'Charlie', 'Widget', 10.0, 10)")
        .unwrap();
    db.execute("INSERT INTO orders VALUES (5, 'Bob', 'Widget', 10.0, 7)")
        .unwrap();
    (db, dir)
}

// === CAST ===

#[test]
fn test_cast_int_to_text() {
    let db = Database::create(TempDir::new().unwrap()).unwrap();
    let result = db.execute("SELECT CAST(42 AS TEXT)");
    // CAST may not be fully supported in the parser
    match result {
        Ok(r) => {
            let r = row(r);
            if let Value::Text(s) = &r[0] {
                assert_eq!(s.as_str(), "42");
            }
        }
        Err(_) => {}
    }
}

#[test]
fn test_cast_in_where() {
    let (db, _dir) = setup_orders();
    // CAST amount to integer and compare
    let result = db.execute("SELECT id FROM orders WHERE CAST(amount AS INT) = 10 ORDER BY id");
    match result {
        Ok(r) => {
            let r = rows(r);
            assert!(r.len() >= 2, "Should find Widget orders (amount=10)");
        }
        Err(_) => {}
    }
}

// === Prepared statements ===

#[test]
fn test_prepared_insert_select() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, name TEXT, val INT)")
        .unwrap();

    // Prepared insert
    db.execute_prepared(
        "INSERT INTO t VALUES (?, ?, ?)",
        vec![
            Value::Integer(1),
            Value::text("first".to_string()),
            Value::Integer(100),
        ],
    )
    .unwrap();

    db.execute_prepared(
        "INSERT INTO t VALUES (?, ?, ?)",
        vec![
            Value::Integer(2),
            Value::text("second".to_string()),
            Value::Integer(200),
        ],
    )
    .unwrap();

    // Prepared select
    let result = db
        .execute_prepared(
            "SELECT name, val FROM t WHERE id = ?",
            vec![Value::Integer(1)],
        )
        .unwrap();
    let r = rows(result);
    assert_eq!(r.len(), 1);
    assert_eq!(&r[0][0], &Value::text("first".to_string()));
}

#[test]
fn test_prepared_update() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val INT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, 10)").unwrap();

    db.execute_prepared(
        "UPDATE t SET val = ? WHERE id = ?",
        vec![Value::Integer(99), Value::Integer(1)],
    )
    .unwrap();

    let r = row(db.execute("SELECT val FROM t WHERE id = 1").unwrap());
    assert_eq!(&r[0], &Value::Integer(99));
}

#[test]
fn test_prepared_delete() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val INT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, 10)").unwrap();
    db.execute("INSERT INTO t VALUES (2, 20)").unwrap();

    db.execute_prepared("DELETE FROM t WHERE id = ?", vec![Value::Integer(1)])
        .unwrap();

    let result = db.execute("SELECT COUNT(*) FROM t").unwrap();
    let r = rows(result);
    assert_eq!(&r[0][0], &Value::Integer(1));
}

#[test]
fn test_prepared_multiple_params() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val INT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, 10)").unwrap();
    db.execute("INSERT INTO t VALUES (2, 20)").unwrap();
    db.execute("INSERT INTO t VALUES (3, 30)").unwrap();

    let result = db
        .execute_prepared(
            "SELECT id FROM t WHERE val >= ? AND val <= ? ORDER BY id",
            vec![Value::Integer(15), Value::Integer(25)],
        )
        .unwrap();
    let r = rows(result);
    assert_eq!(r.len(), 1);
    assert_eq!(&r[0][0], &Value::Integer(2));
}

// === Complex expressions in SELECT ===

#[test]
fn test_nested_arithmetic() {
    let (db, _dir) = setup_orders();
    let r = row(db
        .execute("SELECT amount * qty + 100 FROM orders WHERE id = 1")
        .unwrap());
    // 10.0 * 5 + 100 = 150
    match &r[0] {
        Value::Float(f) => assert!((f - 150.0).abs() < 0.01, "Expected 150, got {}", f),
        Value::Integer(i) => assert_eq!(*i, 150),
        other => panic!("Expected numeric, got {:?}", other),
    }
}

#[test]
fn test_expression_with_parens() {
    let (db, _dir) = setup_orders();
    let r = row(db
        .execute("SELECT (amount + qty) * 2 FROM orders WHERE id = 1")
        .unwrap());
    // (10.0 + 5) * 2 = 30
    match &r[0] {
        Value::Float(f) => assert!((f - 30.0).abs() < 0.1, "Expected 30, got {}", f),
        Value::Integer(i) => assert_eq!(*i, 30),
        other => panic!("Expected numeric, got {:?}", other),
    }
}

// === Complex WHERE clauses ===

#[test]
fn test_where_with_or_and_parens() {
    let (db, _dir) = setup_orders();
    let r = rows(db.execute(
        "SELECT id FROM orders WHERE (customer = 'Alice' AND product = 'Widget') OR (customer = 'Bob' AND product = 'Gadget') ORDER BY id"
    ).unwrap());
    assert_eq!(
        r.len(),
        2,
        "Should match order 1 (Alice+Widget) and order 2 (Bob+Gadget)"
    );
}

#[test]
fn test_where_not() {
    let (db, _dir) = setup_orders();
    let result = db.execute("SELECT id FROM orders WHERE NOT customer = 'Alice' ORDER BY id");
    match result {
        Ok(r) => {
            let r = rows(r);
            assert!(r.len() <= 5, "NOT should filter rows");
        }
        Err(_) => {
            // NOT in WHERE may not be fully supported
        }
    }
}

#[test]
fn test_where_comparison_chain() {
    let (db, _dir) = setup_orders();
    let r = rows(
        db.execute("SELECT id FROM orders WHERE amount > 10 AND qty < 10 ORDER BY id")
            .unwrap(),
    );
    // Bob Gadget (25.0, 3), Alice Gadget (30.0, 2)
    assert_eq!(r.len(), 2);
}

// === UPDATE with expressions ===

#[test]
fn test_update_expression() {
    let (db, _dir) = setup_orders();
    db.execute("UPDATE orders SET amount = amount * 1.1 WHERE customer = 'Alice'")
        .unwrap();

    let r = rows(
        db.execute("SELECT amount FROM orders WHERE customer = 'Alice' ORDER BY id")
            .unwrap(),
    );
    // amount should be increased by 10%
    for row in &r {
        match &row[0] {
            Value::Float(f) => assert!(*f > 10.0, "Amount should be increased"),
            other => panic!("Expected Float, got {:?}", other),
        }
    }
}

#[test]
fn test_update_multiple_columns() {
    let (db, _dir) = setup_orders();
    db.execute("UPDATE orders SET qty = qty + 1, amount = amount + 5 WHERE id = 1")
        .unwrap();

    let r = row(db
        .execute("SELECT qty, amount FROM orders WHERE id = 1")
        .unwrap());
    assert_eq!(&r[0], &Value::Integer(6)); // 5 + 1
    match &r[1] {
        Value::Float(f) => assert!((f - 15.0).abs() < 0.01),
        other => panic!("Expected Float, got {:?}", other),
    }
}

// === DELETE with complex conditions ===

#[test]
fn test_delete_with_in() {
    let (db, _dir) = setup_orders();
    db.execute("DELETE FROM orders WHERE customer IN ('Alice', 'Charlie')")
        .unwrap();

    let r = rows(
        db.execute("SELECT DISTINCT customer FROM orders ORDER BY customer")
            .unwrap(),
    );
    assert_eq!(r.len(), 1);
    assert_eq!(&r[0][0], &Value::text("Bob".to_string()));
}

// === DISTINCT with multiple columns ===

#[test]
fn test_distinct_multiple_columns() {
    let (db, _dir) = setup_orders();
    let r = rows(
        db.execute("SELECT DISTINCT customer, product FROM orders ORDER BY customer, product")
            .unwrap(),
    );
    // Alice+Widget, Alice+Gadget, Bob+Gadget, Bob+Widget, Charlie+Widget = 5
    assert_eq!(r.len(), 5);
}

// === GROUP BY + WHERE + HAVING ===

#[test]
fn test_group_by_where_having() {
    let (db, _dir) = setup_orders();
    // Only orders with qty > 2, then group, then filter by total qty
    let result = db
        .execute("SELECT customer, SUM(qty) FROM orders WHERE qty > 2 GROUP BY customer")
        .unwrap();
    let r = rows(result);
    // Alice: qty 5, Bob: qty 3+7=10, Charlie: qty 10
    assert!(r.len() >= 2);
}

// === Nested expressions ===

#[test]
fn test_case_sensitivity() {
    let (db, _dir) = setup_orders();
    // SQL keywords should be case insensitive
    let r = rows(
        db.execute("select id from orders order by id limit 2")
            .unwrap(),
    );
    assert_eq!(r.len(), 2);
}

#[test]
fn test_mixed_case_identifiers() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE MixedCase (Id INT PRIMARY KEY, Name TEXT)")
        .unwrap();
    db.execute("INSERT INTO MixedCase VALUES (1, 'test')")
        .unwrap();

    let result = db.execute("SELECT Name FROM MixedCase WHERE Id = 1");
    assert!(result.is_ok(), "Mixed case identifiers should work");
}

// === ORDER BY with expression ===

#[test]
fn test_order_by_expression() {
    let (db, _dir) = setup_orders();
    let result = db.execute("SELECT id, amount * qty FROM orders ORDER BY amount * qty DESC");
    match result {
        Ok(r) => {
            let r = rows(r);
            assert_eq!(r.len(), 5);
            // First should have highest total
            match &r[0][1] {
                Value::Float(f) => assert!(*f > 0.0, "Total should be positive"),
                Value::Integer(i) => assert!(*i > 0),
                other => panic!("Expected numeric, got {:?}", other),
            }
        }
        Err(_) => {
            // ORDER BY expression may not be fully supported
        }
    }
}

// === Reopen preserves complex state ===

#[test]
fn test_reopen_preserves_indexes() {
    let dir = TempDir::new().unwrap();
    let path = dir.path().to_path_buf();

    {
        let db = Database::create(&path).unwrap();
        db.execute("CREATE TABLE t (id INT PRIMARY KEY, val TEXT)")
            .unwrap();
        db.execute("INSERT INTO t VALUES (1, 'a')").unwrap();
        db.execute("INSERT INTO t VALUES (2, 'b')").unwrap();
        db.execute("CREATE INDEX idx ON t(val) USING COLUMN")
            .unwrap();
        db.checkpoint().unwrap();
        db.close().unwrap();
    }

    {
        let db = Database::open(&path).unwrap();
        let r = rows(db.execute("SELECT val FROM t ORDER BY id").unwrap());
        assert_eq!(r.len(), 2);
        assert_eq!(&r[0][0], &Value::text("a".to_string()));
    }
}

// === Column aliases in SELECT ===

#[test]
fn test_select_with_aliases_and_expressions() {
    let (db, _dir) = setup_orders();
    let result =
        db.execute("SELECT id AS order_id, amount * qty AS total FROM orders WHERE id = 1");
    assert!(
        result.is_ok(),
        "SELECT with aliases and expressions should not error"
    );
}

// === Empty result set edge cases ===

#[test]
fn test_select_impossible_condition() {
    let (db, _dir) = setup_orders();
    let r = rows(db.execute("SELECT * FROM orders WHERE 1 = 0").unwrap());
    assert_eq!(r.len(), 0);
}

#[test]
fn test_select_always_true() {
    let (db, _dir) = setup_orders();
    let r = rows(db.execute("SELECT * FROM orders WHERE 1 = 1").unwrap());
    assert_eq!(r.len(), 5);
}

// === INTERSECT / EXCEPT (regression) ===
// These were unimplemented (parser didn't recognize the keywords; executor
// returned NotImplemented). Now both work.

#[test]
fn test_intersect() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE a (v INT)").unwrap();
    db.execute("CREATE TABLE b (v INT)").unwrap();
    db.execute("INSERT INTO a VALUES (1),(2),(3)").unwrap();
    db.execute("INSERT INTO b VALUES (2),(3),(4)").unwrap();
    let r = db
        .execute("SELECT v FROM a INTERSECT SELECT v FROM b")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    let mut vals: Vec<i64> = rows
        .iter()
        .filter_map(|r| {
            if let motedb::types::Value::Integer(i) = &r[0] {
                Some(*i)
            } else {
                None
            }
        })
        .collect();
    vals.sort();
    assert_eq!(vals, vec![2, 3], "INTERSECT should return common values");
}

#[test]
fn test_except() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE a (v INT)").unwrap();
    db.execute("CREATE TABLE b (v INT)").unwrap();
    db.execute("INSERT INTO a VALUES (1),(2),(3)").unwrap();
    db.execute("INSERT INTO b VALUES (2),(3),(4)").unwrap();
    let r = db
        .execute("SELECT v FROM a EXCEPT SELECT v FROM b")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    let mut vals: Vec<i64> = rows
        .iter()
        .filter_map(|r| {
            if let motedb::types::Value::Integer(i) = &r[0] {
                Some(*i)
            } else {
                None
            }
        })
        .collect();
    vals.sort();
    assert_eq!(
        vals,
        vec![1],
        "EXCEPT should return values only in left side"
    );
}

#[test]
fn test_intersect_except_with_duplicates() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE a (v INT)").unwrap();
    db.execute("CREATE TABLE b (v INT)").unwrap();
    db.execute("INSERT INTO a VALUES (1),(1),(2),(2),(3)")
        .unwrap();
    db.execute("INSERT INTO b VALUES (2),(2),(3),(4)").unwrap();
    // INTERSECT dedups: {2,3}
    let r = db
        .execute("SELECT v FROM a INTERSECT SELECT v FROM b")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    assert_eq!(rows.len(), 2, "INTERSECT dedups to 2,3");
    // EXCEPT dedups: {1}
    let r = db
        .execute("SELECT v FROM a EXCEPT SELECT v FROM b")
        .unwrap();
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    assert_eq!(rows.len(), 1, "EXCEPT dedups to 1");
}

// === Correlated scalar subquery in SELECT (regression) ===
// Previously all scalar subqueries were pre-resolved once (executed without
// outer-row context), so correlated subqueries returned the same value for
// every outer row. Now correlated subqueries are detected and re-evaluated
// per outer row.

#[test]
fn test_correlated_subquery_in_select() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE dept (id INT PRIMARY KEY, name TEXT)")
        .unwrap();
    db.execute("CREATE TABLE emp (id INT PRIMARY KEY, dept_id INT, salary INT)")
        .unwrap();
    db.execute("INSERT INTO dept VALUES (1,'Eng'),(2,'Sales')")
        .unwrap();
    db.execute("INSERT INTO emp VALUES (1,1,90),(2,1,110),(3,2,80)")
        .unwrap();

    let r = db.execute("SELECT d.name, (SELECT MAX(e.salary) FROM emp e WHERE e.dept_id = d.id) FROM dept d ORDER BY d.id").unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    assert_eq!(rows.len(), 2);
    // Eng max salary = 110, Sales max salary = 80
    assert_eq!(
        rows[0][1],
        motedb::types::Value::Integer(110),
        "Eng max should be 110"
    );
    assert_eq!(
        rows[1][1],
        motedb::types::Value::Integer(80),
        "Sales max should be 80"
    );
}

#[test]
fn test_non_correlated_subquery_still_works() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1,10),(2,20),(3,30)")
        .unwrap();
    // Non-correlated: global AVG, same for all rows
    let r = db
        .execute("SELECT id, (SELECT AVG(v) FROM t) FROM t ORDER BY id")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    // AVG(10,20,30) = 20 for all rows
    for row in &rows {
        match &row[1] {
            motedb::types::Value::Integer(20) => {}
            motedb::types::Value::Float(f) if (*f as i64) == 20 => {}
            o => panic!("expected 20, got {:?}", o),
        }
    }
}

// === Window functions (ROW_NUMBER, RANK, DENSE_RANK) ===

#[test]
fn test_row_number() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE s (id INT PRIMARY KEY, score INT)")
        .unwrap();
    db.execute("INSERT INTO s VALUES (1,90),(2,85),(3,90),(4,70),(5,80)")
        .unwrap();
    let r = db
        .execute("SELECT id, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn FROM s ORDER BY rn")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    assert_eq!(rows.len(), 5);
    let rns: Vec<i64> = rows
        .iter()
        .filter_map(|r| {
            if let motedb::types::Value::Integer(n) = &r[1] {
                Some(*n)
            } else {
                None
            }
        })
        .collect();
    assert_eq!(rns, vec![1, 2, 3, 4, 5], "ROW_NUMBER should be 1-5");
}

#[test]
fn test_rank_with_ties() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE s (id INT PRIMARY KEY, score INT)")
        .unwrap();
    db.execute("INSERT INTO s VALUES (1,90),(2,90),(3,85),(4,70)")
        .unwrap();
    let r = db
        .execute("SELECT id, RANK() OVER (ORDER BY score DESC) AS rk FROM s ORDER BY rk, id")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    let rks: Vec<i64> = rows
        .iter()
        .filter_map(|r| {
            if let motedb::types::Value::Integer(n) = &r[1] {
                Some(*n)
            } else {
                None
            }
        })
        .collect();
    assert_eq!(rks, vec![1, 1, 3, 4], "RANK: ties share rank, gap after");
}

#[test]
fn test_dense_rank() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE s (id INT PRIMARY KEY, score INT)")
        .unwrap();
    db.execute("INSERT INTO s VALUES (1,90),(2,90),(3,85),(4,70)")
        .unwrap();
    let r = db
        .execute("SELECT id, DENSE_RANK() OVER (ORDER BY score DESC) AS dr FROM s ORDER BY dr, id")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    let drs: Vec<i64> = rows
        .iter()
        .filter_map(|r| {
            if let motedb::types::Value::Integer(n) = &r[1] {
                Some(*n)
            } else {
                None
            }
        })
        .collect();
    assert_eq!(drs, vec![1, 1, 2, 3], "DENSE_RANK: no gap after ties");
}

#[test]
fn test_row_number_partitioned() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE s (id INT PRIMARY KEY, cat TEXT, score INT)")
        .unwrap();
    db.execute("INSERT INTO s VALUES (1,'a',90),(2,'a',85),(3,'b',70),(4,'b',80)")
        .unwrap();
    let r = db.execute("SELECT id, cat, ROW_NUMBER() OVER (PARTITION BY cat ORDER BY score DESC) AS rn FROM s ORDER BY cat, rn").unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    assert_eq!(rows.len(), 4);
    // cat 'a': rn 1,2. cat 'b': rn 1,2.
    let a_rns: Vec<i64> = rows
        .iter()
        .filter(|r| matches!(&r[1], motedb::types::Value::Text(t) if t.as_str()=="a"))
        .filter_map(|r| {
            if let motedb::types::Value::Integer(n) = &r[2] {
                Some(*n)
            } else {
                None
            }
        })
        .collect();
    assert_eq!(a_rns, vec![1, 2]);
}

// === Chained set operations (A UNION B UNION C, A INTERSECT B UNION C) ===

#[test]
fn test_chained_union() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    for tn in ["a", "b", "c"] {
        db.execute(&format!("CREATE TABLE {} (v INT)", tn)).unwrap();
    }
    db.execute("INSERT INTO a VALUES (1),(2),(3)").unwrap();
    db.execute("INSERT INTO b VALUES (2),(3),(4)").unwrap();
    db.execute("INSERT INTO c VALUES (3),(4),(5)").unwrap();
    let r = db
        .execute("SELECT v FROM a UNION SELECT v FROM b UNION SELECT v FROM c")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    let mut vals: Vec<i64> = rows
        .iter()
        .filter_map(|r| {
            if let motedb::types::Value::Integer(i) = &r[0] {
                Some(*i)
            } else {
                None
            }
        })
        .collect();
    vals.sort();
    assert_eq!(vals, vec![1, 2, 3, 4, 5], "3-way UNION should dedup all");
}

#[test]
fn test_chained_intersect() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    for tn in ["a", "b", "c"] {
        db.execute(&format!("CREATE TABLE {} (v INT)", tn)).unwrap();
    }
    db.execute("INSERT INTO a VALUES (1),(2),(3)").unwrap();
    db.execute("INSERT INTO b VALUES (2),(3),(4)").unwrap();
    db.execute("INSERT INTO c VALUES (3),(4),(5)").unwrap();
    let r = db
        .execute("SELECT v FROM a INTERSECT SELECT v FROM b INTERSECT SELECT v FROM c")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    assert_eq!(rows.len(), 1, "3-way INTERSECT => only 3");
}

#[test]
fn test_mixed_set_ops_left_assoc() {
    let dir = tempfile::TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    for tn in ["a", "b", "c"] {
        db.execute(&format!("CREATE TABLE {} (v INT)", tn)).unwrap();
    }
    db.execute("INSERT INTO a VALUES (1),(2),(3)").unwrap();
    db.execute("INSERT INTO b VALUES (2),(3),(4)").unwrap();
    db.execute("INSERT INTO c VALUES (3),(4),(5)").unwrap();
    // Left-assoc: (a INTERSECT b) UNION c = {2,3} UNION {3,4,5} = {2,3,4,5}
    let r = db
        .execute("SELECT v FROM a INTERSECT SELECT v FROM b UNION SELECT v FROM c")
        .unwrap();
    use motedb::QueryResult;
    let rows = match r.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("expected select"),
    };
    let mut vals: Vec<i64> = rows
        .iter()
        .filter_map(|r| {
            if let motedb::types::Value::Integer(i) = &r[0] {
                Some(*i)
            } else {
                None
            }
        })
        .collect();
    vals.sort();
    assert_eq!(
        vals,
        vec![2, 3, 4, 5],
        "left-assoc (a intersect b) union c = 2,3,4,5"
    );
}