# v0.12.0 — 3-Table Join Fix, Developer Tools, and Automated Fuzzing

> **Full technical details:** [v0.12.0.md-full.md](v0.12.0.md-full.md)

**Status: ✅ Released** | **Scope: Medium** (~4 weeks)

> A critical correctness fix for joins across three or more tables, new
> developer diagnostic functions, automated SQL fuzzing with SQLancer,
> and the scalability groundwork for hundreds of stream tables.

---

## What problem does this solve?

The most significant correctness issue discovered after v0.11.0 involved
joins across three or more tables — a very common pattern in real-world
queries. When such joins were refreshed differentially, phantom rows (rows
that should not exist) could appear in the result under specific sequences
of changes. This bug, known internally as EC-01, required a deep fix in the
join delta computation algorithm.

---

## EC-01: Three-Table Join Phantom Row Fix

When computing the delta for a join of three or more tables, the engine
needed to enumerate which combinations of rows from the different tables had
changed. A subtle bug in how the "unchanged" portion of each table was
handled caused extra rows — phantoms — to appear in the output under certain
DELETE and UPDATE sequences.

The fix required restructuring how the join delta algorithm partitions its
work into parts, ensuring that each combination of changes is counted exactly
once.

*In plain terms:* if your stream table joins orders, customers, and products
together, the differential results were occasionally wrong. This is now
corrected, and the fix is validated with a comprehensive property test and
TPC-H regression suite.

---

## Developer Diagnostic Functions

New SQL functions to help developers understand what pg_trickle is doing:

- **`pgtrickle.show_delta_sql(name)`** — displays the SQL that the
  differential engine would generate for a given stream table, without
  executing it. Useful for understanding why a query is slow or falling back
  to FULL.
- **`pgtrickle.validate_query(sql)`** — checks whether a given SQL query is
  supported by the differential engine and explains any limitations.
- **`pgtrickle.dag_dot()`** — exports the complete dependency graph in DOT
  format for visualisation in Graphviz or similar tools.

---

## SQLancer Fuzzing Integration

**SQLancer** is an open-source tool that automatically generates random SQL
queries and tests them for correctness by comparing the results against
equivalent queries. pg_trickle's differential engine is now fuzz-tested by
SQLancer in the CI pipeline — thousands of randomly generated queries are
verified each run.

---

## CDC Benchmarks

A new benchmark suite measures the overhead of change-data capture triggers
under high write volumes. This provides a baseline for detecting performance
regressions in the CDC path and gives users data to evaluate whether
trigger-based CDC is appropriate for their write volumes.

---

## Scalability Foundations

Internal refactoring to prepare for deployments with hundreds of stream
tables:

- The scheduler's catalog query was redesigned to remain O(1) as the number
  of stream tables grows
- Shared memory allocation for the stream table registry was made dynamic
  rather than fixed at startup

---

## Scope

v0.12.0 fixes a critical correctness bug (EC-01) affecting three-table joins,
adds developer-facing diagnostic tools, and establishes automated fuzzing
as an ongoing quality gate. The scalability refactoring sets the stage for
deployments that were previously impractical.