matrixorigin / matrixorigin/matrixone

[Bug]: LEFT JOIN can hang after a table_stats patch selects a RIGHT JOIN runtime-filter plan

Open
#26,707 0 comments 0 reactions 1 assignee Claimed by @aptend View on GitHub
kind/bug needs-triage
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

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.