pingcap / pingcap/tidb

planner: join reorder through projection evaluates filtered expressions too early

Open
#69,904 2 comments 0 reactions 0 assignees View on GitHub
severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

```sql
DROP DATABASE IF EXISTS codex_68295_check;
CREATE DATABASE codex_68295_check;
USE codex_68295_check;

CREATE TABLE err_t1(x INT PRIMARY KEY);
CREATE TABLE err_t2(a INT PRIMARY KEY, b INT);
CREATE TABLE err_t3(a INT PRIMARY KEY);
CREATE TABLE digits(d INT PRIMARY KEY);

INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT INTO err_t1 VALUES (1);
INSERT INTO err_t2 VALUES (1, 0), (2, 1);

INSERT INTO err_t3
SELECT 2+d0.d+10*d1.d+100*d2.d+1000*d3.d
FROM digits d0, digits d1, digits d2, digits d3;

ANALYZE TABLE err_t1, err_t2, err_t3;

SET SESSION tidb_opt_enable_advanced_join_reorder=OFF;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

SET SESSION tidb_opt_join_reorder_through_proj=OFF;
SELECT e1.x, dt.bad
FROM err_t1 e1
JOIN (
SELECT e2.a, 1 / e2.b AS bad
FROM err_t2 e2
JOIN err_t3 e3 ON e2.a=e3.a
) dt ON e1.x=dt.bad;
SHOW WARNINGS;

SET SESSION tidb_opt_join_reorder_through_proj=ON;
SELECT e1.x, dt.bad
FROM err_t1 e1
JOIN (
SELECT e2.a, 1 / e2.b AS bad
FROM err_t2 e2
JOIN err_t3 e3 ON e2.a=e3.a
) dt ON e1.x=dt.bad;
SHOW WARNINGS;
```

### 2. What did you expect to see? (Required)

Both settings should return the same result and warnings. The row `err_t2.a=1` is removed by the inner join because there is no matching row in `err_t3`, so `1 / err_t2.b` should not be evaluated for that row.

### 3. What did you see instead (Required)

Both queries return:

```text
1 | 1.0000
```

With `tidb_opt_join_reorder_through_proj=OFF`, `SHOW WARNINGS` is empty. With it enabled, TiDB returns:

```text
Warning | 1365 | Division by 0
```

The same behavior becomes a query error in strict `INSERT ... SELECT`:

```sql
CREATE TABLE err_out(x INT, bad DECIMAL(10,4));
SET SESSION tidb_opt_join_reorder_through_proj=OFF;

INSERT INTO err_out
SELECT e1.x, dt.bad
FROM err_t1 e1
JOIN (
SELECT e2.a, 1 / e2.b AS bad
FROM err_t2 e2
JOIN err_t3 e3 ON e2.a=e3.a
) dt ON e1.x=dt.bad;
-- succeeds and writes 1 row

TRUNCATE TABLE err_out;
SET SESSION tidb_opt_join_reorder_through_proj=ON;

INSERT INTO err_out
SELECT e1.x, dt.bad
FROM err_t1 e1
JOIN (
SELECT e2.a, 1 / e2.b AS bad
FROM err_t2 e2
JOIN err_t3 e3 ON e2.a=e3.a
) dt ON e1.x=dt.bad;
-- ERROR 1365 (22012): Division by 0
```

### 4. What is your TiDB version? (Required)

```text
Release Version: v8.5.7
Edition: Community
Git Commit Hash: 202b7f47286a1109b5c957401d34c9358d130ae0
Git Branch: HEAD
UTC Build Time: 2026-07-15 02:06:00
GoVersion: go1.25.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
```

The issue is reproducible only when the experimental session variable `tidb_opt_join_reorder_through_proj` is enabled; its default value is `OFF`.

After testing, run:

```sql
DROP DATABASE IF EXISTS codex_68295_check;
```

Contributor guide

Open the contributing guide

Research direction

The issue provides no source file or test path. First run the SQL reproduction with tidb_opt_join_reorder_through_proj OFF and ON, then trace the planner's join-reorder-through-projection path; done means both modes produce identical rows and warnings, and strict INSERT ... SELECT succeeds.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.