sql-cli 1.80.1

SQL query tool for CSV/JSON with both interactive TUI and non-interactive CLI modes - perfect for exploration and automation
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

# 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 (NEW GAP): multi-condition nested-loop joins evaluate an extra condition's
# operands by syntactic position (left_expr->left table, right_expr->right table),
# ignoring the actual alias. `b.price < a.price` (right-table column first) is thus
# evaluated as `a.price < b.price`, returning rows that violate the predicate.
# Same query written `a.price > b.price` AGREEs. Affects INNER and LEFT paths.
expect = "DIFFER"

[[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.