alopex-cli 0.8.8

use std::io::Write;
use std::process::{Command, Stdio};

#[cfg_attr(not(feature = "lane_ci"), ignore)]
#[test]
fn sql_reads_from_stdin_pipe() {
    let mut child = Command::new(env!("CARGO_BIN_EXE_alopex"))
        .args(["--in-memory", "--output", "json", "sql"])
        .stdin(Stdio::piped())
        .stdout(Stdio::piped())
        .stderr(Stdio::piped())
        .spawn()
        .expect("spawn alopex");

    let sql = r#"
CREATE TABLE stdin_test (id INTEGER PRIMARY KEY, qty INTEGER);
INSERT INTO stdin_test (id, qty) VALUES (1, 3), (2, 1), (3, 5);
SELECT * FROM stdin_test;
WITH renamed(identifier) AS (SELECT 42) SELECT identifier FROM renamed;
WITH RECURSIVE counter(n) AS (
    SELECT 1 UNION ALL SELECT n + 1 FROM counter WHERE n < 3
) SELECT n FROM counter ORDER BY n;
SELECT id, SUM(qty) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS framed FROM stdin_test ORDER BY id;
SELECT id,
       FIRST_VALUE(id) OVER (ORDER BY qty) AS first_id,
       LAST_VALUE(id) OVER (ORDER BY qty) AS last_id,
       NTH_VALUE(id, 2) OVER (ORDER BY qty) AS second_id,
       NTILE(2) OVER (ORDER BY qty) AS bucket,
       PERCENT_RANK() OVER (ORDER BY qty) AS percent_rank,
       CUME_DIST() OVER (ORDER BY qty) AS cume_dist
FROM stdin_test ORDER BY id;
SELECT id, ROW_NUMBER() OVER ranked AS row_number
FROM stdin_test
WINDOW base AS (), ranked AS (base ORDER BY qty DESC, id)
QUALIFY row_number <= 2
ORDER BY id;
VALUES (2, 'b'), (1, 'a') ORDER BY column1;
SELECT TRUE IS TRUE AS truth_value,
       NULL IS DISTINCT FROM 1 AS distinct_null,
       (1, 2) < (1, 3) AS row_less,
       (1, NULL) = (1, NULL) AS row_unknown;
SELECT TRY_CAST('42' AS INTEGER) AS parsed,
       TRY_CAST('bad' AS INTEGER) AS rejected,
       TRY_CAST([1.0, 2.0] AS VECTOR(3)) AS wrong_dimension;
VALUES (2), (2), (1) ORDER BY column1 DESC FETCH FIRST 1 ROW WITH TIES;
SELECT id FROM stdin_test ORDER BY id OFFSET 1 ROW FETCH NEXT 1 + 1 ROWS ONLY;
SELECT DISTINCT ON (id % 2) id % 2 AS parity, id, qty FROM stdin_test ORDER BY parity, qty, id;
SELECT COUNT(*) FILTER (WHERE qty > 2) AS heavy,
       SUM(qty) FILTER (WHERE FALSE) AS none,
       GROUP_CONCAT(CAST(id AS TEXT) ORDER BY qty DESC) AS by_qty,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY qty) AS median
FROM stdin_test;
SELECT qty, COUNT(*) AS c, GROUPING(qty) AS g FROM stdin_test
GROUP BY ROLLUP(qty) ORDER BY g, qty NULLS FIRST;
SELECT t.id, top.qty AS top_qty FROM stdin_test AS t(id, qty) CROSS JOIN LATERAL
(SELECT s.qty FROM stdin_test AS s WHERE s.qty <= t.qty ORDER BY s.qty DESC LIMIT 1) AS top
ORDER BY t.id;
SELECT u.unnest FROM UNNEST([1.0, 2.0]) AS u ORDER BY u.unnest;
"#;

    {
        let stdin = child.stdin.as_mut().expect("stdin");
        stdin.write_all(sql.as_bytes()).expect("write stdin");
    }

    let output = child.wait_with_output().expect("wait");
    assert!(
        output.status.success(),
        "status: {:?}\nstderr:\n{}",
        output.status.code(),
        String::from_utf8_lossy(&output.stderr)
    );

    let stdout = String::from_utf8_lossy(&output.stdout);
    let value: serde_json::Value =
        serde_json::from_str(&stdout).expect("json output should be JSON");
    let sets = value
        .as_array()
        .expect("json output should be an array of result sets");
    assert_eq!(
        sets.len(),
        18,
        "one result set per statement\nstdout:\n{stdout}"
    );
    let select_rows = sets[2].as_array().expect("SELECT result set");
    let found = select_rows.iter().any(|row| {
        row.get("id")
            .and_then(|v| v.as_i64())
            .is_some_and(|id| id == 1)
    });
    assert!(
        found,
        "expected SELECT result set to include id=1\nstdout:\n{}\nstderr:\n{}",
        stdout,
        String::from_utf8_lossy(&output.stderr)
    );

    let cte_rows = sets[3].as_array().expect("CTE SELECT result set");
    assert_eq!(cte_rows, &[serde_json::json!({ "identifier": 42 })]);

    let recursive_rows = sets[4].as_array().expect("recursive CTE SELECT result set");
    assert_eq!(
        recursive_rows,
        &[
            serde_json::json!({ "n": 1 }),
            serde_json::json!({ "n": 2 }),
            serde_json::json!({ "n": 3 }),
        ]
    );

    let frame_rows = sets[5].as_array().expect("window frame SELECT result set");
    assert_eq!(
        frame_rows,
        &[
            serde_json::json!({ "id": 1, "framed": 4 }),
            serde_json::json!({ "id": 2, "framed": 9 }),
            serde_json::json!({ "id": 3, "framed": 6 }),
        ]
    );

    let extended_window_rows = sets[6]
        .as_array()
        .expect("extended window SELECT result set");
    assert_eq!(
        extended_window_rows,
        &[
            serde_json::json!({
                "id": 1,
                "first_id": 2,
                "last_id": 1,
                "second_id": 1,
                "bucket": 1,
                "percent_rank": 0.5,
                "cume_dist": 2.0 / 3.0,
            }),
            serde_json::json!({
                "id": 2,
                "first_id": 2,
                "last_id": 2,
                "second_id": null,
                "bucket": 1,
                "percent_rank": 0.0,
                "cume_dist": 1.0 / 3.0,
            }),
            serde_json::json!({
                "id": 3,
                "first_id": 2,
                "last_id": 3,
                "second_id": 1,
                "bucket": 2,
                "percent_rank": 1.0,
                "cume_dist": 1.0,
            }),
        ]
    );

    let qualify_rows = sets[7]
        .as_array()
        .expect("named WINDOW/QUALIFY SELECT result set");
    assert_eq!(
        qualify_rows,
        &[
            serde_json::json!({ "id": 1, "row_number": 2 }),
            serde_json::json!({ "id": 3, "row_number": 1 }),
        ]
    );

    let values_rows = sets[8].as_array().expect("VALUES result set");
    assert_eq!(
        values_rows,
        &[
            serde_json::json!({ "column1": 1, "column2": "a" }),
            serde_json::json!({ "column1": 2, "column2": "b" }),
        ]
    );

    let predicate_rows = sets[9].as_array().expect("predicate result set");
    assert_eq!(
        predicate_rows,
        &[serde_json::json!({
            "truth_value": true,
            "distinct_null": true,
            "row_less": true,
            "row_unknown": null,
        })]
    );

    let try_cast_rows = sets[10].as_array().expect("TRY_CAST result set");
    assert_eq!(
        try_cast_rows,
        &[serde_json::json!({
            "parsed": 42,
            "rejected": null,
            "wrong_dimension": null,
        })]
    );

    let with_ties_rows = sets[11].as_array().expect("WITH TIES result set");
    assert_eq!(
        with_ties_rows,
        &[
            serde_json::json!({ "column1": 2 }),
            serde_json::json!({ "column1": 2 }),
        ]
    );

    let fetch_rows = sets[12].as_array().expect("OFFSET/FETCH result set");
    assert_eq!(
        fetch_rows,
        &[
            serde_json::json!({ "id": 2 }),
            serde_json::json!({ "id": 3 }),
        ]
    );

    let distinct_on_rows = sets[13].as_array().expect("DISTINCT ON result set");
    assert_eq!(
        distinct_on_rows,
        &[
            serde_json::json!({ "parity": 0, "id": 2, "qty": 1 }),
            serde_json::json!({ "parity": 1, "id": 1, "qty": 3 }),
        ]
    );

    let aggregate_clause_rows = sets[14]
        .as_array()
        .expect("aggregate FILTER / WITHIN GROUP result set");
    assert_eq!(
        aggregate_clause_rows,
        &[serde_json::json!({
            "heavy": 2,
            "none": null,
            "by_qty": "3,1,2",
            "median": 3,
        })]
    );

    let rollup_rows = sets[15].as_array().expect("ROLLUP result set");
    assert_eq!(
        rollup_rows,
        &[
            serde_json::json!({ "qty": 1, "c": 1, "g": 0 }),
            serde_json::json!({ "qty": 3, "c": 1, "g": 0 }),
            serde_json::json!({ "qty": 5, "c": 1, "g": 0 }),
            serde_json::json!({ "qty": null, "c": 3, "g": 1 }),
        ]
    );

    // CROSS JOIN LATERAL over an alias-renamed base table (issue #151).
    let lateral_rows = sets[16].as_array().expect("LATERAL result set");
    assert_eq!(
        lateral_rows,
        &[
            serde_json::json!({ "id": 1, "top_qty": 3 }),
            serde_json::json!({ "id": 2, "top_qty": 1 }),
            serde_json::json!({ "id": 3, "top_qty": 5 }),
        ]
    );

    let unnest_rows = sets[17].as_array().expect("UNNEST result set");
    assert_eq!(
        unnest_rows,
        &[
            serde_json::json!({ "unnest": 1.0 }),
            serde_json::json!({ "unnest": 2.0 }),
        ]
    );
}

#[cfg_attr(not(feature = "lane_ci"), ignore)]
#[test]
fn cast_failure_reports_stable_public_error() {
    let mut child = Command::new(env!("CARGO_BIN_EXE_alopex"))
        .args(["--in-memory", "--output", "json", "sql"])
        .stdin(Stdio::piped())
        .stdout(Stdio::piped())
        .stderr(Stdio::piped())
        .spawn()
        .expect("spawn alopex");
    child
        .stdin
        .as_mut()
        .expect("stdin")
        .write_all(b"SELECT CAST('bad' AS INTEGER);")
        .expect("write stdin");

    let output = child.wait_with_output().expect("wait");
    assert!(!output.status.success(), "CAST failure must fail the CLI");
    let stderr = String::from_utf8_lossy(&output.stderr);
    assert!(stderr.contains("ALOPEX-E004"), "stderr:\n{stderr}");
    assert!(
        stderr.contains("cannot cast Text to INTEGER"),
        "stderr:\n{stderr}"
    );
    assert!(!stderr.contains("TypedExpr"), "stderr:\n{stderr}");
    assert!(!stderr.contains("MessagePack"), "stderr:\n{stderr}");
}

#[cfg_attr(not(feature = "lane_ci"), ignore)]
#[test]
fn with_ties_without_order_by_and_bind_parameters_report_stable_errors() {
    for (sql, expected) in [
        (
            "SELECT 1 FETCH FIRST 1 ROW WITH TIES;",
            "FETCH ... WITH TIES requires ORDER BY",
        ),
        ("SELECT 1 LIMIT ?;", "bind parameters are not yet supported"),
    ] {
        let mut child = Command::new(env!("CARGO_BIN_EXE_alopex"))
            .args(["--in-memory", "--output", "json", "sql"])
            .stdin(Stdio::piped())
            .stdout(Stdio::piped())
            .stderr(Stdio::piped())
            .spawn()
            .expect("spawn alopex");
        child
            .stdin
            .as_mut()
            .expect("stdin")
            .write_all(sql.as_bytes())
            .expect("write stdin");

        let output = child.wait_with_output().expect("wait");
        assert!(!output.status.success(), "`{sql}` must fail the CLI");
        let stderr = String::from_utf8_lossy(&output.stderr);
        assert!(stderr.contains(expected), "`{sql}` stderr:\n{stderr}");
    }
}

#[cfg_attr(not(feature = "lane_ci"), ignore)]
#[test]
fn distinct_on_order_by_mismatch_reports_stable_error() {
    let mut child = Command::new(env!("CARGO_BIN_EXE_alopex"))
        .args(["--in-memory", "--output", "json", "sql"])
        .stdin(Stdio::piped())
        .stdout(Stdio::piped())
        .stderr(Stdio::piped())
        .spawn()
        .expect("spawn alopex");
    child
        .stdin
        .as_mut()
        .expect("stdin")
        .write_all(
            b"CREATE TABLE t (a INTEGER, b INTEGER); \
              SELECT DISTINCT ON (a) a FROM t ORDER BY b;",
        )
        .expect("write stdin");

    let output = child.wait_with_output().expect("wait");
    assert!(
        !output.status.success(),
        "DISTINCT ON prefix mismatch must fail the CLI"
    );
    let stderr = String::from_utf8_lossy(&output.stderr);
    assert!(
        stderr.contains("SELECT DISTINCT ON expressions must match initial ORDER BY expressions"),
        "stderr:\n{stderr}"
    );
}

#[cfg_attr(not(feature = "lane_ci"), ignore)]
#[test]
fn aggregate_clause_misuse_reports_stable_errors() {
    for (sql, expected) in [
        (
            "CREATE TABLE t (v INTEGER); SELECT SUM(v) WITHIN GROUP (ORDER BY v) FROM t;",
            "WITHIN GROUP is only valid for ordered-set aggregate functions",
        ),
        (
            "CREATE TABLE t (v INTEGER); SELECT PERCENTILE_DISC(0.5) FROM t;",
            "WITHIN GROUP (ORDER BY ...) is required for PERCENTILE_DISC",
        ),
        (
            "CREATE TABLE t (v INTEGER); SELECT ABS(v) FILTER (WHERE v > 0) FROM t;",
            "FILTER (WHERE ...) is only valid for aggregate functions",
        ),
    ] {
        let mut child = Command::new(env!("CARGO_BIN_EXE_alopex"))
            .args(["--in-memory", "--output", "json", "sql"])
            .stdin(Stdio::piped())
            .stdout(Stdio::piped())
            .stderr(Stdio::piped())
            .spawn()
            .expect("spawn alopex");
        child
            .stdin
            .as_mut()
            .expect("stdin")
            .write_all(sql.as_bytes())
            .expect("write stdin");

        let output = child.wait_with_output().expect("wait");
        assert!(!output.status.success(), "`{sql}` must fail the CLI");
        let stderr = String::from_utf8_lossy(&output.stderr);
        assert!(stderr.contains("ALOPEX-T007"), "`{sql}` stderr:\n{stderr}");
        assert!(stderr.contains(expected), "`{sql}` stderr:\n{stderr}");
        assert!(!stderr.contains("TypedExpr"), "`{sql}` stderr:\n{stderr}");
    }
}