OriginChain
02 · sql

The SQL you'd write on paper. Now actually works.

Window functions, correlated subqueries, aggregate over expression, CASE WHEN, predicate pushdown, EXPLAIN ANALYZE - the SQL surface customers asked for in demos, now shipped.

surface, as shipped
28 constructs · all shipping today
SELECT … WHERE
JOIN, CROSS JOIN, comma joins
GROUP BY + aggregates COUNT, SUM, AVG, MIN, MAX
Aggregate over expression (SUM(qty * price))
COUNT(DISTINCT col)
DISTINCT on SUM / AVG / MIN / MAX
HAVING + HAVING OR
ORDER BY, ORDER BY <position>
LIMIT N OFFSET M
Window functions: ROW_NUMBER / RANK / DENSE_RANK PARTITION BY + ORDER BY
Window functions: LAG / LEAD with offset + default
Window aggregates: SUM / AVG / COUNT / MIN / MAX OVER cumulative per partition
IN / NOT IN / EXISTS (subquery) - uncorrelated
Correlated subqueries (EXISTS / IN / scalar) execute per outer row; EXISTS rewrites to a semi-join
LIKE / NOT LIKE / ILIKE
IS NULL / IS NOT NULL
Column-vs-column comparisons in WHERE
Expressions in WHERE + SELECT
CASE WHEN expression
Column aliases (AS)
SELECT *, col AS x
CREATE TABLE DDL
Predicate pushdown to secondary indexes
EXPLAIN + EXPLAIN ANALYZE with per-node cost
JOINs across up to 32 tables left-deep planner; cap raised from 5
Materialized views (on-demand refresh) install / refresh / read
Foreign keys on-delete: NoAction, Restrict, SetNull. Cascade and SetDefault deferred.
CHECK constraints literals, comparisons, AND/OR/NOT, IS [NOT] NULL, IN. 3-valued logic.
how we picked the surface

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, correlated EXISTS and IN that route through the same index walker as the outer WHERE.

Window functions landed in the same slice. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and the SUM / AVG / COUNT / MIN / MAX cumulative aggregates all run as a streaming partition walk on top of the index scan - no extra storage, no spool. Explicit frame clauses are the one piece we still refuse rather than silently approximate.

examples, real syntax
window function
SELECT
  user_id,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY ts DESC
  ) AS rn
FROM payments;

Streaming partition walk on the index scan. No spool, no extra storage.

correlated EXISTS
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.

CASE WHEN in projection
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.

aggregate over expression
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.

predicate pushdown

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.

measured
  • indexed point lookup < 5 ms
  • GROUP BY on 10k rows < 50 ms
  • correlated EXISTS over 100k < 80 ms

Read the technical reference, then try it.

SQL docs → Architecture Try it