sql-cli 1.82.3

# 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"
# P29, FIXED 2026-08-08. A boolean operator FOLLOWING an `IN (...)` predicate was
# not parsed: until P13 stage 1 the remainder was silently discarded (4 rows
# instead of 2), after it the query was a hard error. Root cause was IN being
# applied outside the OR/AND hierarchy — see P30 below, same bug.

[[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 = "DIFFER"
# 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.
# In the event the P29/P30 precedence fix covered both, since they share
# `parse_in_operator`. Keep it: it is the control that proves that.
#
# It now DIFFERs on a *different* finding, which the parse error had been hiding:
# the subquery yields the scores of ids 1-4, which include id 3's NULL, and
# `NULL IN (.., NULL, ..)` matches for us where DuckDB gives UNKNOWN. 3 rows vs 2.
# That is P18 (`= NULL` matches instead of yielding UNKNOWN) reaching IN through
# the shared equality — see in_list_with_null_literal below, which isolates it
# without a subquery. Re-pin to AGREE when P18 is fixed.

[[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"
# P30, FIXED 2026-08-08, and it was the more alarming of the pair: with the
# operands this way round the query PARSED and returned ZERO rows where the
# answer is 2 (ids 1 and 2, both score 50).
#
# Filed as an evaluation bug; it was not. IN was applied at the top of
# parse_expression, outside the OR/AND hierarchy, so this parsed as
#   InList { expr: (team = 'alpha' AND score), values: [50, 70] }
# — "is this boolean one of 50 or 70?" — false for every row. Same root cause as
# P29 above; the operand order only decided whether the mis-parse produced a
# wrong answer or leftover tokens.

[[case]]
id = "in_list_then_or"
data = "null_edges.csv"
sql = "SELECT id, team, score FROM null_edges WHERE score IN (50) OR team = 'beta' ORDER BY id"
# Added with the P29/P30 fix. The defect was in how IN bound relative to the
# whole OR/AND hierarchy, so OR needs pinning in both operand orders too — AND
# alone would not have caught a fix that only reached parse_logical_and.

[[case]]
id = "or_then_in_list"
data = "null_edges.csv"
sql = "SELECT id, team, score FROM null_edges WHERE team = 'beta' OR score IN (50) ORDER BY id"
# Added with the P29/P30 fix; the OR mirror of and_then_in_list.

[[case]]
id = "in_list_with_null_literal"
data = "null_edges.csv"
sql = "SELECT id FROM null_edges WHERE score IN (50, NULL) ORDER BY id"
expect = "DIFFER"
# P18, found 2026-08-08 while fixing P29/P30 — the literal-list form that
# isolates what in_subquery_then_and trips over, with no subquery involved.
# A NULL in the list matches every NULL-scored row: we return ids 1,2,3,10,11,12
# where DuckDB returns 1,2. IN is built on the same equality as `= NULL`.
#
# Discriminating pair: where_in_with_null_col (tier 10) AGREEs — a NULL column
# value against a list with NO NULL in it is excluded correctly. The variable
# that matters is a NULL *in the list*, not a NULL in the column.

[[case]]
id = "in_list_between_two_conditions"
data = "null_edges.csv"
sql = "SELECT id, team, score FROM null_edges WHERE team = 'alpha' AND score IN (50, 70) AND id < 3 ORDER BY id"
# Added with the P29/P30 fix. IN in the MIDDLE of a chain — the case that needs
# parse_comparison to both consume the IN and hand control back to the AND loop.
# The old top-level placement could not express this at all.