matrixorigin / matrixorigin/matrixone
[Bug]: LEFT JOIN can hang after a table_stats patch selects a RIGHT JOIN runtime-filter plan
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### Is there an existing issue for the same bug?
- [x] I searched open and closed issues for the same `LEFT JOIN` / runtime-filter / `table_stats` timeout and did not find a match.
### Branch Name
`feature/stats-plan-fuzz`
### Commit ID
`6091b418fd529badca4e1855843bd38804de396f`
### Other Environment Information
- OS: Linux amd64
- Go: `go1.26.4`
- MatrixOne: embedded base cluster, one CN
- Session: `max_dop=4`, `optimizer_hints="forceOneCN=1"`
- Detection method: black-box randomized statistics through the public MySQL and `table_stats(..., 'patch', ...)` paths
- Deterministic random case: seed `20260805`, case `1`
### Actual Behavior
A `LEFT JOIN` over two seven-row tables completes immediately before patching statistics. After applying the statistics payload below to only `random_build`, `EXPLAIN` completes but the query does not return within 5 seconds and is canceled with `context deadline exceeded`.
This reproduced in repeated fresh embedded-cluster runs. A black-box reduction also established:
- the complete patch on `random_build` reproduces the timeout;
- shuffle-range fields alone do not reproduce it;
- the non-shuffle fields on `random_build` alone produce the same `RIGHT JOIN` plus runtime-filter plan but the query completes;
- the observed timeout therefore requires a combination present in the complete public statistics payload. No production implementation was inspected to reach this result.
The plan in the failing run is:
```text
Project
-> Sort
Sort Key: p.k INTERNAL, p.payload INTERNAL, b.k INTERNAL, b.payload INTERNAL
-> Join
Join Type: RIGHT
Join Cond: (b.k = p.k)
Runtime Filter Build: #[-1,0]
-> Table Scan on stats_plan_repro.random_build
Runtime Filter Probe: b.k
-> Table Scan on stats_plan_repro.random_probe
```
### Expected Behavior
Statistics may change the physical plan, but must not prevent the query from completing or change its result. The query should return the same 9 rows as it does before the patch.
### Steps to Reproduce
Run the following through a MySQL connection as an account that can set `moadmin` and patch table statistics:
```sql
set role moadmin;
drop database if exists stats_plan_repro;
create database stats_plan_repro;
use stats_plan_repro;
set @@max_dop = 4;
set session optimizer_hints = "forceOneCN=1";
create table random_probe (k bigint, payload bigint not null) cluster by k;
create table random_build (k bigint, payload bigint not null) cluster by k;
create table random_dimension (k bigint, payload bigint not null) cluster by k;
insert into random_probe values
(null, 5), (-1, 7), (1, 10), (2, 20), (2, 21), (4, 40), (8, 80);
insert into random_build values
(null, 100), (-1, 101), (2, 200), (2, 201), (3, 300), (4, 400), (8, 800);
insert into random_dimension values
(null, 1000), (-1, 1001), (2, 2000), (4, 4000), (5, 5000), (8, 8000);
-- Baseline: completes immediately and returns 9 rows.
select p.k, p.payload, b.k, b.payload
from random_probe p left join random_build b on p.k = b.k
order by p.k, p.payload, b.k, b.payload;
-- Apply the deterministic black-box stats profile to random_build.
select table_cnt
from table_stats(
'stats_plan_repro.random_build',
'patch',
'{"table_cnt":865603090,"block_number":2048,"accurate_object_number":63,"ndv_map":{"k":1000000000,"payload":1999999},"min_val_map":{"k":-775366527,"payload":137745772},"max_val_map":{"k":50001,"payload":-602710606},"null_cnt_map":{"k":49999,"payload":238144845},"size_map":{"k":731959262275,"payload":0},"shuffle_range_map":{"k":{"overlap":0.94,"uniform":0.99,"result":[768091512,768097616,768098072,768104102,768112017,768114806,768116952,768120775,768123374,768131317,768136230,768138738,768143973,768150930,768157400,768162438,768165536,768171357,768175016,768175657,768184783,768192248,768200916,768202090,768211688,768215323,768221241,768226983,768233772,768242330,768248593,768255101,768262044,768272031,768278683,768284883,768288556,768293462,768297332,768298883,768304333,768313573,768321358,768330977,768335385,768338786,768338796,768341433,768348527,768351797,768360547,768361193,768362320,768363342,768369921,768371169,768374712,768380600,768383605,768389571,768394242,768403039,768408636,768415509]},"payload":{"overlap":0.96,"uniform":0.31,"result":[268485071,268488201,268494202,268499843,268501323,268504853,268508765,268510054,268519329,268526714,268535045,268540191,268541041,268543195,268549680,268559328,268560314,268562523,268569412,268573232,268573924,268582536,268586464,268589509,268597278,268597728,268600053,268604908,268611090,268619192,268624402,268627187,268630618,268637597,268639291,268640737,268642798,268648446,268652115,268660179,268668885,268670039,268676135,268680643,268686225,268694310,268695562,268701147,268703409,268713099,268720403,268724870,268730642,268739500,268743457,268750951,268751091,268754050,268762777,268767955,268768054,268777692,268783804,268790881]}}}'
) g;
-- EXPLAIN completes and shows RIGHT JOIN plus runtime filter.
explain
select p.k, p.payload, b.k, b.payload
from random_probe p left join random_build b on p.k = b.k
order by p.k, p.payload, b.k, b.payload;
-- Reproduced behavior: does not return within 5 seconds.
select p.k, p.payload, b.k, b.payload
from random_probe p left join random_build b on p.k = b.k
order by p.k, p.payload, b.k, b.payload;
```
### Additional Information
The original randomized suite uses two deterministic seeds, 128 profiles per seed, two execution modes, and eight query shapes. Before shuffle-range fields were included, 256 profiles completed 2,048 `EXPLAIN` operations and 2,048 query executions without a failure. With the complete profile above, the first stable failure is `seed=20260805 / case=1 / one-cn / left-join`.
Each operation is independently time-bounded so the detector records the first failing profile and preserves the full replay payload.
Contributor guide
Assessment
This issue has not been assessed yet.