planner: join reorder through projection evaluates filtered expressions too early
- 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
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