citusdata / citusdata/citus

Non-deterministic subqueries in DML on reference table should not be pushed down

Open
#4,810 1 comment 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.