electric-sql / electric-sql/electric
Stale rows in materialized view after multi-level subquery join path changes
- Dominant language
- TypeScript
- Stars
- 10.4k
- Forks
- 375
- Avg merge
- 3d 1h
- Merged PRs (30d)
- 18
Description
## Summary
Electric's materialized view retains stale rows when mutations change the join path of a multi-level nested subquery filter. The Postgres oracle query returns the correct result, but Electric's view includes extra rows that no longer match.
## Failing shape
```sql
-- shape_20: 3-level nested subquery through junction table
SELECT id, level_3_id, value FROM level_4
WHERE level_3_id IN (
SELECT id FROM level_3 WHERE level_2_id IN (
SELECT id FROM level_2 WHERE level_1_id IN (
SELECT level_1_id FROM level_1_tags WHERE tag = 'alpha'
)
)
)
```
## Observed behavior
After applying `txn_5` (which includes moves between parent levels and tag mutations), Electric's view contains 18 rows while the Postgres oracle returns 16 rows. The two extra rows are:
| id | level_3_id | value |
|----|-----------|-------|
| l4-3 | l3-3 | v684 |
| l4-9 | l3-3 | v682 |
Both have `level_3_id = l3-3`, suggesting Electric didn't correctly invalidate rows when the join path `level_3 → level_2 → level_1_tags` changed due to parent moves or tag operations in the transaction.
## Reproduction
Branch [`alco/oracle-replay-subquery-bug`](https://github.com/electric-sql/electric/tree/alco/oracle-replay-subquery-bug) contains a deterministic replay test extracted from a generative oracle test run (20 shapes, 40 transactions × 40 mutations).
```bash
mix test --include oracle_replay test/integration/oracle_property_test.exs
```
The failure is fully deterministic — it reproduces on every run at the same step (`txn_5`) with the same mismatch.
## Context
Found via the generative oracle property test (`mix test --include oracle`). The scenario involves a 4-level hierarchy (`level_1` → `level_2` → `level_3` → `level_4`) with junction tag tables at each level. The mutations that trigger the bug include moves (`UPDATE level_N SET level_{N-1}_id = ...`) and tag operations (`INSERT/DELETE` on `level_N_tags`) that alter which rows match the nested subquery filter.
Contributor guide
Assessment
This issue has not been assessed yet.