# v0.3.0 — Correctness for Complex SQL and Security Scanning

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

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

> Deep correctness fixes for advanced SQL patterns — HAVING clauses, FULL
> OUTER JOINs, and correlated subqueries — plus automated security analysis
> tooling and TPC-H enhancements.

---

## What problem does this solve?

Systematic testing of the differential engine against complex SQL patterns
revealed that certain constructs could produce incorrect results in
differential mode. These are not edge cases to be worked around — they are
patterns used in real reporting queries. v0.3.0 fixes the most important ones
and introduces automated security scanning to the codebase.

---

## HAVING Clause Correctness

`HAVING` filters groups after aggregation — for example, "show me only
departments where the average salary exceeds £50,000." In differential mode,
a group can cross the HAVING threshold in either direction as rows are added
or removed, requiring careful handling of group appearance and disappearance.

The differential engine was incorrectly handling cases where a group's
aggregate crossed the HAVING threshold due to partial changes. This is now
fixed: groups appear in the stream table exactly when they satisfy the HAVING
condition, and disappear immediately when they no longer do.

---

## FULL OUTER JOIN Correctness

`FULL OUTER JOIN` combines two tables, keeping all rows from both sides even
when there is no matching row on the other side. Computing the delta of a
FULL OUTER JOIN is significantly more complex than an INNER JOIN — when a
row appears on one side with no match on the other, it creates a null-padded
result; when a match later appears, that null-padded result must be removed
and replaced.

The differential engine's handling of FULL OUTER JOINs was corrected to
properly track these join state transitions.

---

## EXISTS + HAVING Combination

A particularly tricky combination — `EXISTS` subqueries inside queries with
`HAVING` clauses — could produce phantom rows (results that should not exist)
or missing rows in the differential output. The interaction between the
semi-join logic and the HAVING filter was corrected.

---

## Correlated Subquery Improvements

Correlated subqueries (subqueries that reference columns from the outer
query) are among the most complex SQL patterns to handle incrementally. This
release improves the engine's handling of several correlated subquery patterns
that were producing incorrect differentials.

---

## Static Application Security Testing (SAST)

The pg_trickle codebase now runs automated security analysis using SAST tools
on every commit. This catches patterns like potential integer overflows,
unchecked memory operations, and other security-relevant issues before they
reach production.

---

## TPC-H Enhancements

The TPC-H benchmark test suite was expanded with additional correctness
assertions and multi-cycle tests — verifying that after many refresh cycles
with ongoing source changes, the stream table results remain correct.

---

## Scope

v0.3.0 is a significant correctness release targeting complex SQL patterns
that are common in analytical queries. The HAVING, FULL OUTER JOIN, and
correlated subquery fixes affect users with more sophisticated stream table
definitions. The SAST toolchain is an internal quality improvement.