sql-cli 1.82.3

# Tier 4 — joins. The harness loads one source file per case (table = file stem),
# so joins are expressed within a single source: self-join, and joins to derived
# tables / CTEs built from that source. Multi-file joins need a harness extension
# (tracked in README roadmap) and are not covered yet.

[[case]]
id = "self_join_base"
data = "trades.csv"
sql = "SELECT a.symbol AS s, a.price AS hi, b.price AS lo FROM trades a JOIN trades b ON a.symbol = b.symbol AND a.price > b.price"
# FIXED (P4): the base table can now be re-referenced with aliases in a join.

[[case]]
id = "self_join_aggregate"
data = "trades.csv"
sql = "SELECT a.symbol AS symbol, COUNT(*) AS pairs FROM trades a JOIN trades b ON a.symbol = b.symbol GROUP BY a.symbol ORDER BY a.symbol"
# Pins P4: base self-join feeding a GROUP BY aggregate.

[[case]]
id = "self_left_join_base"
data = "trades.csv"
sql = "SELECT a.symbol AS s, a.price AS ap, b.price AS bp FROM trades a LEFT JOIN trades b ON a.symbol = b.symbol AND a.price > b.price"
# Pins P4: LEFT self-join of the base table (unmatched left rows keep NULL bp).
# Written left-table-first to avoid the separate operand-orientation bug (P7).

[[case]]
id = "join_condition_operand_order"
data = "trades.csv"
sql = "SELECT a.price AS ap, b.price AS bp FROM trades a JOIN trades b ON a.symbol = b.symbol AND b.price < a.price"
# P7 (FIXED 2026-07-12): multi-condition nested-loop joins used to evaluate an
# extra condition's operands by syntactic position (left_expr->left table,
# right_expr->right table), ignoring the actual alias, so `b.price < a.price`
# (right-table column first) was silently evaluated as `a.price < b.price`.
# Operands are now routed to their owning table by alias qualifier, so this
# AGREEs regardless of operand order.

[[case]]
id = "right_join_multi_condition"
data = "trades.csv"
sql = "SELECT a.symbol AS s, a.price AS ap, b.price AS bp FROM trades a RIGHT JOIN trades b ON a.symbol = b.symbol AND a.price < b.price"
# P8 (FIXED 2026-07-17): multi-condition RIGHT JOIN used to reuse the LEFT
# nested-loop with the tables swapped and the join alias unchanged, so the
# swapped-in FROM table's columns were labelled with the wrong alias (a.* values
# surfaced as bp, b.* as ap) and NULLs landed on the wrong side (b instead of a).
# A dedicated `nested_loop_join_right_multi` now emits columns in [FROM, joined]
# order with the alias only on the joined table, so this AGREEs.

[[case]]
id = "join_derived_table"
data = "international_sales.csv"
sql = "SELECT s.region AS region, s.amount AS amount, agg.total AS total FROM international_sales s JOIN (SELECT region, SUM(amount) AS total FROM international_sales GROUP BY region) agg ON s.region = agg.region"

[[case]]
id = "join_cte"
data = "international_sales.csv"
sql = "WITH agg AS (SELECT region, SUM(amount) AS total FROM international_sales GROUP BY region) SELECT s.region AS region, agg.total AS total FROM international_sales s JOIN agg ON s.region = agg.region"

[[case]]
id = "left_join_derived"
data = "international_sales.csv"
sql = "SELECT s.country AS country, agg.total AS total FROM international_sales s LEFT JOIN (SELECT region, SUM(amount) AS total FROM international_sales GROUP BY region) agg ON s.region = agg.region"

[[case]]
id = "join_with_filter"
data = "international_sales.csv"
sql = "SELECT s.country AS country, agg.total AS total FROM international_sales s JOIN (SELECT region, SUM(amount) AS total FROM international_sales GROUP BY region) agg ON s.region = agg.region WHERE agg.total > 5000"

[[case]]
id = "cross_join_constant"
data = "trades.csv"
sql = "SELECT t.symbol AS symbol, c.k AS k FROM trades t CROSS JOIN (SELECT 1 AS k) c"
# FIXED (P5): FROM-less subquery now yields exactly one row via DUAL, so this
# CROSS JOIN produces 92 rows and AGREEs with DuckDB.

[[case]]
id = "cross_join_constant_multicol"
data = "trades.csv"
sql = "SELECT t.symbol AS symbol, c.a AS a, c.b AS b FROM trades t CROSS JOIN (SELECT 1 AS a, 2 AS b) c"
# Pins P5 for a multi-column FROM-less subquery: still exactly one constant row.

[[case]]
id = "from_less_derived_main"
data = "trades.csv"
sql = "SELECT x.k AS k, x.n AS n FROM (SELECT 1 AS k, 2 AS n) x"
# Pins P5 for a FROM-less subquery used directly as the main FROM: one row.

# --- P27: OR in a JOIN ON clause ---

[[case]]
id = "join_on_or_condition"
data = "null_edges.csv"
sql = "SELECT a.id AS aid, b.id AS bid FROM null_edges a INNER JOIN null_edges b ON a.id = b.id OR a.id = b.partner_id ORDER BY a.id, b.id"
expect = "GAP"
# P27, added 2026-08-02. Only the first condition is parsed. Until P13 stage 1
# the `OR ...` remainder was silently DISCARDED, so the join ran on a truncated
# predicate and returned wrong rows with no error — examples/chemistry.sql had
# been shipping exactly that. It is a parse error now, which is why this sits in
# GAP: a refusal is the correct intermediate state.
#
# AND in a join condition already works and is well covered by the P7/P8 cases
# above, so this is specifically OR.