cannot simplify the predicate #64216
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE t1 (a int PRIMARY KEY, b int);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
EXPLAIN FORMAT='brief' SELECT STRAIGHT_JOIN * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
2. What did you expect to see? (Required)
In Mysql
+----------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=0.7 rows=1) (actual time=0.172..0.172 rows=0 loops=1) |
| -> Table scan on t1 (cost=0.35 rows=1) (actual time=0.161..0.161 rows=0 loops=1) |
| -> Single-row index lookup on t2 using PRIMARY (a = t1.a) (cost=0.35 rows=1) (never executed) |
+----------------------------------------------------------------------------------------------------+
3. What did you see instead (Required)
EXPLAIN FORMAT='brief' SELECT STRAIGHT_JOIN * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
id estRows task access object operator info
MergeJoin 3.84 root inner join, left key:join_outer.t1.a, right key:join_outer.t2.a, other cond:or(eq(join_outer.t1.a, join_outer.t2.a), eq(join_outer.t1.a, join_outer.t2.b))
├─TableReader(Build) 3.84 root data:Selection
│ └─Selection 3.84 cop[tikv] or(eq(join_outer.t2.a, join_outer.t2.a), eq(join_outer.t2.a, join_outer.t2.b))
│ └─TableFullScan 4.00 cop[tikv] table:t2 keep order:true
└─TableReader(Probe) 9.00 root data:TableFullScan
└─TableFullScan 9.00 cop[tikv] table:t1 keep order:true