sql-cli 1.82.2

# Tier 2 — WHERE: comparisons, boolean logic, IN, BETWEEN, LIKE, NULL, subqueries.

[[case]]
id = "where_numeric_gt"
data = "trades.csv"
sql = "SELECT symbol, price FROM trades WHERE price > 185"

[[case]]
id = "where_string_eq"
data = "trades.csv"
sql = "SELECT symbol, price FROM trades WHERE symbol = 'AAPL'"

[[case]]
id = "where_and_or"
data = "international_sales.csv"
sql = "SELECT * FROM international_sales WHERE region = 'Europe' AND amount > 1000 OR currency = 'GBP'"

[[case]]
id = "where_in_list"
data = "international_sales.csv"
sql = "SELECT * FROM international_sales WHERE currency IN ('USD', 'EUR')"

[[case]]
id = "where_not_in"
data = "international_sales.csv"
sql = "SELECT * FROM international_sales WHERE currency NOT IN ('USD')"

[[case]]
id = "where_between"
data = "trades.csv"
sql = "SELECT symbol, price FROM trades WHERE price BETWEEN 180 AND 190"

[[case]]
id = "where_like_prefix"
data = "instruments.csv"
sql = "SELECT instrument_id, name FROM instruments WHERE name LIKE 'A%'"

[[case]]
id = "where_like_contains"
data = "instruments.csv"
sql = "SELECT instrument_id, name FROM instruments WHERE name LIKE '%Stock%'"

[[case]]
id = "where_is_null"
data = "instruments.csv"
sql = "SELECT instrument_id FROM instruments WHERE coupon_rate IS NULL"

[[case]]
id = "where_is_not_null"
data = "instruments.csv"
sql = "SELECT instrument_id FROM instruments WHERE coupon_rate IS NOT NULL"

[[case]]
id = "where_not_expr"
data = "trades.csv"
sql = "SELECT symbol FROM trades WHERE NOT (price > 185)"

[[case]]
id = "where_arith_predicate"
data = "trades.csv"
sql = "SELECT symbol, price, volume FROM trades WHERE price * volume > 200000"

[[case]]
id = "where_in_subquery"
data = "international_sales.csv"
sql = "SELECT region, amount FROM international_sales WHERE amount > (SELECT AVG(amount) FROM international_sales)"

[[case]]
id = "where_in_subquery_set"
data = "trades.csv"
sql = "SELECT symbol FROM trades WHERE symbol IN (SELECT symbol FROM trades WHERE price > 185)"

[[case]]
id = "select_alias_in_in_subquery"
data = "trades.csv"
sql = "SELECT symbol, price * 2 AS dbl FROM trades WHERE dbl IN (SELECT price * 2 FROM trades WHERE price > 185)"
# Was P11: a SELECT alias on the LHS of an IN-subquery errored ("Column 'dbl'
# not found"). Fixed 2026-07-25 in two layers: (1) WhereAliasExpander migrated
# onto walk::map_children, which visits the subquery LHS operand (the alias)
# while leaving the subquery body opaque; (2) the substituted IN-list's LHS may
# be an arbitrary expression (price*2), which the IN-subquery path never lifted,
# so evaluate_in_list/between now evaluate an expression LHS via the arithmetic
# evaluator. Now AGREE.

# --- P29 / P30: IN combined with another condition ---

[[case]]
id = "in_list_then_and"
data = "null_edges.csv"
sql = "SELECT id FROM null_edges WHERE score IN (50, 70) AND team = 'alpha' ORDER BY id"
expect = "GAP"
# P29. A boolean operator FOLLOWING an `IN (...)` predicate is not parsed. Until
# P13 stage 1 the remainder was silently discarded — this query returned 4 rows
# (the `IN` alone) instead of 2, with no error. Now a parse error, which is why
# it sits in GAP. `AND` works fine everywhere else, including after LIKE and
# BETWEEN, so this is specific to IN.

[[case]]
id = "in_subquery_then_and"
data = "null_edges.csv"
sql = "SELECT id FROM null_edges WHERE score IN (SELECT score FROM null_edges WHERE id < 5) AND team = 'alpha' ORDER BY id"
expect = "GAP"
# P29, the subquery form — pinned separately because the IN-list and IN-subquery
# paths are built differently (see P11) and a fix to one need not reach the other.

[[case]]
id = "and_then_in_list"
data = "null_edges.csv"
sql = "SELECT id, team, score FROM null_edges WHERE team = 'alpha' AND score IN (50, 70) ORDER BY id"
expect = "DIFFER"
# P30, and the more alarming of the pair: with the operands the other way round
# this PARSES and returns ZERO rows where the answer is 2 (ids 1 and 2, both
# score 50). Still live after P13 stage 1 — it is an evaluation bug, not a parse
# one. `where_in_with_null_col` (IN with no other condition) AGREEs, so IN alone
# is fine; it is the combination that fails.