electric-sql / electric-sql/electric

Stale rows in materialized view after multi-level subquery join path changes

Open
#3,892 1 comment 0 reactions 0 assignees View on GitHub
reliability shape-with-subqueries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.