Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
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 |
Attachments
Issue Links
- is caused by
-
MDEV-26974 Improve selectivity and related costs in optimizer
- Closed