The SQL you'd write on paper. Now actually works.
Every refusal from a year ago is now a feature. Correlated EXISTS, aggregate over expression, CASE WHEN, UNION/INTERSECT/EXCEPT, predicate pushdown - and one honest deferral: window functions, slated for post-1.0.
| Construct | Status |
|---|---|
| SELECT … WHERE | shipped |
| JOIN, CROSS JOIN, comma joins | shipped |
| GROUP BY + aggregates | shipped |
| Aggregate over expression (SUM(qty * price)) | shipped |
| COUNT(DISTINCT col) | shipped |
| DISTINCT on SUM / AVG / MIN / MAX | shipped |
| HAVING + HAVING OR | shipped |
| ORDER BY, ORDER BY <position> | shipped |
| LIMIT N OFFSET M | shipped |
| EXISTS (subquery) - uncorrelated and correlated | shipped |
| IN / NOT IN (subquery) | shipped |
| UNION / INTERSECT / EXCEPT | shipped |
| LIKE / NOT LIKE / ILIKE | shipped |
| IS NULL / IS NOT NULL | shipped |
| Column-vs-column comparisons in WHERE | shipped |
| Expressions in WHERE + SELECT | shipped |
| CASE WHEN expression | shipped |
| Column aliases (AS) | shipped |
| SELECT *, col AS x | shipped |
| CREATE TABLE DDL | shipped |
| Predicate pushdown to secondary indexes | shipped |
| EXPLAIN + EXPLAIN ANALYZE with per-node cost | shipped |
| Window functions (RANK, LAG, LEAD) | deferred |
| Recursive CTEs | deferred |
| Triggers | deferred |
We shipped what customers asked us in demos.
Every construct in the table above came from a real demo where someone asked "can you do X?" and the honest answer used to be no. We shipped the ones the substrate could express with predicate pushdown - semi-joins instead of cursors, expression-aware aggregates instead of subqueries.
Window functions are a large surface that adds value most for analytics dashboards. We deferred them so we could finish the operational SQL story first. They land post-1.0; the cost-walker work makes them straightforward when we pick them up.
SELECT id, name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
AND o.status = 'open'
);Rewritten as a semi-join. No row materialisation for the subquery.
SELECT id,
CASE
WHEN balance > 10000 THEN 'high'
WHEN balance > 1000 THEN 'mid'
ELSE 'low'
END AS tier
FROM customers;Branch-free expression in the planner. Pushes down with the rest of the WHERE.
SELECT
customer_id,
SUM(qty * price) AS revenue
FROM line_items
GROUP BY customer_id
HAVING SUM(qty * price) > 1000;Aggregate wraps any expression - not just bare columns.
EXPLAIN tells you which predicate uses which index.
The planner walks the WHERE clause, splits conjuncts, and routes each one to the secondary index that can satisfy it cheapest. EXPLAIN ANALYZE shows estimated vs actual rows per node so drift is visible.
- indexed point lookup < 5 ms
- GROUP BY on 10k rows < 50 ms
- correlated EXISTS over 100k < 80 ms