fv-streams-sql 0.4.0

The SQL front door for FusionVault Streams: a streaming SQL statement parses to a small typed logical plan and lowers to the same topology the TOML frontend produces — one runtime, two front doors. No DataFusion planner, no fork; expressions compile through the value dialect as the steps already do.
Documentation
# fv-streams-sql

The **SQL front door** for [FusionVault Streams](https://github.com/FusionVault/streams).

A streaming SQL statement parses to a small **typed logical plan** and lowers to the *same*
topology the TOML frontend produces (its `steps` are the engine's own JSON vocabulary). One runtime,
two front doors: SQL inherits checkpointing, the exchange, exactness and `--join` because it emits
the identical steps a hand-written pipeline would.

```rust
let topo = fv_streams_sql::compile(
    "INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'",
)?;
```

## Design

- **No DataFusion `LogicalPlan` / physical layer, no fork.** We lower the `sqlparser` AST to our own
  IR, which *models streaming* (DataFusion's logical plan does not); the errors are streaming-aware
  and ours, and the IR is the seam a future optimiser plugs into. (See `design/…-phase5-…` §10.)
- **Expressions reuse the value dialect.** SQL scalar expressions unparse to the value-dialect
  strings the `expression` steps already carry, compiled through `fv-value-datafusion` at build time
  exactly as the TOML path does — one expression layer, so the two front doors cannot drift.

## The supported SQL surface

Exactly what lowers to a `spec` operator — everything else is rejected at plan time with an error
that names the supported shape.

| SQL | lowers to |
|---|---|
| `INSERT INTO <sink> SELECT … FROM <source> [WHERE …]` | a stateless stage (`filter` / `applyExpression` / `select`) |
| `SELECT *, <expr> AS name …` | keep every column + the computed one (no trailing `select`) |
| `GROUP BY TUMBLE(ts, INTERVAL …), <keys>` + `count/sum/avg/min/max` | `windowedAggregate` (tumbling), keyed |
| `GROUP BY HOP(ts, INTERVAL <slide>, INTERVAL <size>), …` | `windowedAggregate` (sliding) |
| `GROUP BY SESSION(ts, INTERVAL <gap>), …` | `sessionAggregate` |
| a trailing `INTERVAL` on any window (`TUMBLE(ts, INTERVAL <size>, INTERVAL <lateness>)`, `HOP(…, INTERVAL <lateness>)`, `SESSION(…, INTERVAL <lateness>)`) | the window's `allowedLatenessMs` (how long a closed window still accepts late events; omitted → the engine default) |
| `WITH <name> AS (<subquery>), … <outer>` | **multi-stage**: each CTE (or a subquery feeding a join/aggregate) materialises as its own stage under `<name>`, referenced downstream — the SQL form of the TOML `[[transforms]]` DAG |
| `count(DISTINCT col)` / `count(DISTINCT CASE … END)` | `countDistinct` (a `CASE`/expression argument becomes a pre-aggregate `applyExpression` column) |
| a computed `SELECT` column over `windowStart`/group/aggregates | a post-aggregate `applyExpression` (e.g. `substring(epochToIso(windowStart), 0, 10) AS day`) |
| `… GROUP BY …, TUMBLE(…) EMIT CHANGES EVERY <n> ROWS\|MS` | the **updating aggregate** (early firing) — the `emit` block |
| `FROM a JOIN b ON a.k = b.k AND ts BETWEEN …` | `streamJoin` (interval) |
| `FROM stream JOIN <bounded> ON a.k = b.k` | the **lookup join** (right side = the bounded table) |
| `FROM (SELECT …, ROW_NUMBER() OVER (PARTITION BY g ORDER BY c [DESC], [tie]) AS rn FROM s [WHERE …]) WHERE rn <= n` | `topN` (a second `ORDER BY` column is the tie-break) |

Scalar expressions render into the value dialect: `=`→`==`, `<>`→`!=`, `AND`→`&&`, `OR`→`||`, SQL
`||` concat→`+`, `CASE`→`?:`, `IS [NOT] NULL`→`== null`/`!= null`, `SUBSTRING(x, a, b)`→
`substring(x, a, b)`; function names keep their written (camelCase) case, since the dialect's are
case-sensitive.

**Rejected at plan time** (each with a message naming the supported shape): a bare `SELECT` with no
sink; a `GROUP BY` with neither a window function nor `EMIT CHANGES`; `EMIT CHANGES` on a stateless /
session shape or without a cadence; a comma join (`FROM a, b`); a JOIN whose key columns don't share a
name; `SELECT *, <bare column>`; `HAVING`; `UNION`/`INTERSECT`/`EXCEPT`; `WITH RECURSIVE` (a streaming
CTE has no fixpoint); a non-`INSERT` statement. (`ORDER BY` over an unbounded stream, correlated
subqueries and DDL are likewise not part of the subset.)

Multi-stage worked examples (each e2e-exact against its TOML twin's oracle): **q13** — `WITH bids AS
(SELECT *, auction % N AS key … WHERE type='bid') SELECT * FROM bids JOIN side ON bids.key = side.key`
(a computed key, then the lookup join); **q5** — a sliding windowed count CTE then a per-window
`ROW_NUMBER() <= 1`; **q4** — four CTEs (two filtered streams, their interval join, a per-auction
`max`) then an `avg` per category, five stages with two nested windowed aggregates.

## In a pipeline

A pipeline config declares its datasets as usual and puts the query in a `sql` field instead of
`[[transforms]]`:

```toml
name = "enrich"
[brokers]
default = "localhost:9092"
[datasets.bids]
topic = "bids"
[datasets.out]
topic = "out"
sql = "INSERT INTO out SELECT auction, price * 0.908 AS eur FROM bids WHERE type = 'bid'"
```