# PLAN_SQLANCER.md — Differential Semantic Proving via SQLancer

**Status:** Proposed
**Date:** 2026-03-17
**Driver:** Open
**Relates to:** Priority 1-1 of `PLAN_TEST_EVALS_FINAL_REPORT.md`

---

## 1. Motivation

`pg_trickle` is a Differential View Maintenance (DVM) engine that takes standard PostgreSQL view definitions and incrementally maintains them. A fundamental requirement of a DVM engine is that **the incrementally maintained stream table must always strictly equal the exact output of the base query executed natively by PostgreSQL in a single snapshot.** 

Currently, we test this invariant using static, human-written queries (like the TPC-H benchmarks). However, SQL is vastly expansive. Our `dvm::parser` and delta-join logic are vulnerable to edge cases involving:
*   Three-valued logic (`NULL` comparisons).
*   Nested aggregations, correlated subqueries, and window functions.
*   Complex casting, JSONB traversal, and array coercions inside the projection layer.

**Solution:** Integrate [SQLancer](https://github.com/sqlancer/sqlancer) (Structured Query Language Analyzer). SQLancer is a rigorous fuzzer that dynamically generates valid, insanely complex schemas and queries to test database engines. By turning SQLancer's oracles on `pg_trickle`, we can algorithmically search for parser panics, DAG generation failures, and subtle logic bugs in our dataflow operators.

---

## 2. Goals & Expected ROI

*   **Zero-Panic Guarantee in the Parser:** Expose our Rust `dvm::parser` and DAG builder to thousands of syntactically valid but convoluted SQL patterns. Ensure that if we do not support a feature (like a specific window sub-clause), we return a clean, informative `PgTrickleError` rather than crashing the Postgres backend.
*   **Semantic Equivalence Proving:** Use SQLancer's mathematical test oracles, such as NoREC (Non-Optimizing Reference Engine Construction) or TLP (Ternary Logic Partitioning), to prove that our DVM Rust operators accurately mirror PostgreSQL's C execution engine edge-cases (especially around outer joins and null handling).
*   **Automated Bug Minimization:** When a bug is found randomly by SQLancer, it provides exact, reproducible SQL seeds to generate a fast, new regression test for `proptest`.

---

## 3. Implementation Steps

SQLancer is written in Java and executed as a standalone external process against a running database.

### Step 1: Establish the Fuzzing Environment
1.  Add a `just sqlancer` command that spins up a targeted E2E base Postgres container with the `pg_trickle` extension installed and shared memory properly allocated.
2.  Install SQLancer in an isolated Docker container configured to specifically test the PostgreSQL dialect. 

### Step 2: The Crash-Test Oracle (Parser Fuzzing)
The easiest immediate win is purely defensive—verifying that creating stream tables doesn't crash Postgres.
1.  Configure SQLancer to run against standard PostgreSQL, creating randomized base tables and views.
2.  Write an intermediary Python or Rust script that acts as a proxy: whenever SQLancer attempts to execute a `SELECT ...` query to verify an oracle, our script intercepts the query, prepends `SELECT pgtrickle.create_stream_table('temp_out', $$ ... $$, 'calculated', 'DIFFERENTIAL');`, and throws it at the database.
3.  **Assert:** The query must either succeed or fail with a known `pg_trickle` error code. If the database connection drops (meaning the background worker or SPI panicked and killed the backend), the test fails, capturing the seed.

### Step 3: The Equivalence Oracle (Logic Validation)
Once the parser is robust, we must prove the data output is correct. This is effectively NoREC targeted at our operators.
1.  Allow SQLancer to generate its chaotic schema and base data.
2.  For every query `Q` generated by SQLancer, execute `Q` natively on PostgreSQL to get the `expected_result`.
3.  Simultaneously, wrap `Q` in `create_stream_table()`, perform a `pgtrickle.refresh_stream_table()`, and query the underlying stream table to get the `pg_trickle_result`.
4.  **Assert:** Use multiset symmetric difference checking. `expected_result` must exactly equal `pg_trickle_result`. If they differ, our DVM operators (like diff-joins or diff-aggregates) have a logical bug.

### Step 4: Stateful DML Fuzzing
SQLancer allows for interleaving random DDL/DML. We can use this to break our DAG update ordering. 
1.  Create a stream table over the random SQLancer tables.
2.  Let SQLancer run thousands of mixed `INSERT/UPDATE/DELETE` statements against the base tables.
3.  Call `pgtrickle.refresh()`.
4.  Compare the stream table against the native materialized view of the same query.

---

## 4. Risks & Considerations

*   **Feature Parity Noise:** SQLancer generates incredibly complex standard Postgres SQL. `pg_trickle` intentionally does not yet support all of Postgres (e.g., specific lateral join shapes or certain window configurations). The fuzzer will hit a lot of `ERROR: feature not supported by pg_trickle`. We must configure SQLancer's Postgres dialect generator (or our parsing proxy) to ignore these expected exits so they do not flag as test failures.
*   **Execution Time:** Deep fuzzing takes hours. This cannot be part of the standard `just test-all` CI blocking suite.
*   **Infrastructure Layout:** SQLancer should run as a nightly scheduled GitHub Action (a "Chaos specific" pipeline) that alerts the team in the morning if a random seed broke the build, rather than running synchronously on PRs.

## 5. Definition of Done
- A `docker-compose` or `just` workflow exists that spins up Postgres + pg_trickle + SQLancer.
- A nightly GitHub Action runs SQLancer against `main` for 1 hour, verifying parser stability (no panics).
- "Expected" unsupported features are cleanly ignored.