|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2);
|
|
CREATE TABLE t2 (b INT);
|
INSERT INTO t2 VALUES (3),(4);
|
|
CREATE TABLE t3 (c INT PRIMARY KEY) ENGINE=HEAP;
|
|
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON (t2.b >= t3.c) ON (t1.a < t2.b);
|
|
bb-11.0 d8531ea4b3
|
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON (t2.b >= t3.c) ON (t1.a < t2.b);
|
a b c
|
|
EXPLAIN EXTENDED
|
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON (t2.b >= t3.c) ON (t1.a < t2.b);
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t1`.`a` < `test`.`t2`.`b` and `test`.`t2`.`b` >= `test`.`t3`.`c`) where 1
|
The baseline produces the result set as expected:
|
10.11 936436ef4
|
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON (t2.b >= t3.c) ON (t1.a < t2.b);
|
a b c
|
1 NULL NULL
|
2 NULL NULL
|
|
EXPLAIN EXTENDED
|
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON (t2.b >= t3.c) ON (t1.a < t2.b);
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
|
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 0 0.00 Using join buffer (flat, BNL join)
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t1`.`a` < `test`.`t2`.`b` and `test`.`t2`.`b` >= `test`.`t3`.`c`) where 1
|
|