# Native optimization counterexamples

`counterexamples.py` captures twelve executions in DuckDB 1.4.4, with Polars 1.44.1
and PyArrow 25.0.1 checking every input and complete query result. It uses one thread
and a fresh connection per query. It generates synthetic inputs locally; no user data
or external dataset is needed.

```fish
uv run explorer/scripts/engines/counterexamples.py --output new-capture
uv run explorer/scripts/engines/counterexamples.py --output new-capture --verify
```

The default output is `explorer/static/engines/counterexamples`. Generation refuses to
replace an existing proof. Verification regenerates inputs in a temporary directory,
compares every result, checks native operator estimates/cardinalities and the stated
pairwise differences, and hashes the preserved original profiles and Parquet files.
Timings and exact byte counters are not required to match a fresh run. Only temporary
workspace paths are normalized in profiles.

The two published Parquet files have identical 100,000-row data, 25 row groups and
uncompressed columns. One has min/max statistics; the other does not. ID 43 is in one
bounded group. Integer equality, absent statistics and equality after a VARCHAR cast
all return total 731. The two latter executions report larger `total_bytes_read` values
in the recorded run, although all three report 100,000 scanner rows. These counters
are not physical disk traffic or a row-group access trace. Both integer and cast
predicates appear inside READ_PARQUET; placement does not establish range pruning.

Two 10,000-row tables have exactly the same marginal distributions for a and b.
One contains all pairs 0–99; the other has a=b. The predicate a<10 AND b>=90 returns
100 versus zero matches while both scan estimates are 2,000. The skew comparison
has 100 versus 9,505 matches for key=0 with the same row count and key domain.

For the refresh experiment, ANALYZE precedes an UPDATE from key=id%100 to key=id.
The scan estimate remains 103 until another ANALYZE, then becomes 2. The updated
answer remains one row. This is observed DuckDB 1.4.4 behavior, not a universal claim
about statistics maintenance or an exact-estimate guarantee.

The join comparison runs identical SQL with normal optimization and with
`join_order,build_side_probe_side` disabled. Both return count 1,000 and sum 45,004,500.
The earlier join produces 100 versus 1,000 rows. Dynamic filtering reduces the fact
scan in both; the written sequence does not materialize the hypothetical unfiltered
million-row join. No timing ranking is claimed.

Sources: [DuckDB join controls](https://duckdb.org/docs/current/guides/performance/join_operations),
[ANALYZE](https://duckdb.org/docs/current/sql/statements/analyze),
[Parquet pushdown](https://duckdb.org/docs/stable/data/parquet/overview).
Current documentation may describe newer engine versions; the original profiles and
pinned executable recipe establish these specific observations.