Non-deterministic subqueries in DML on reference table should not be pushed down
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
We liberally push down DML commands on reference tables with subqueries that may return different results on different replicas (e.g. limit without order by).
```
update ref set b = 1 where a in (select a from ref where b <> 1 limit 5);
```
This could cause replicas to diverge. We should reject these queries in the router planner and then let them go through recursive planning to get consistent results on all replicas, like we already do when there is a volatile function.
For non-deterministic correlated subqueries, we should probably error out (perhaps allow them with citus.subquery_pushdown).
Contributor guide
Research direction
Start in the router planner and compare handling of non-deterministic subqueries with the existing volatile-function path. Reproduce the reference-table UPDATE example, then verify that unsafe uncorrelated subqueries avoid pushdown and that correlated cases follow the intended error or configuration behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, postgresql, sql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100