Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
This is the range access part of MDEV-17493.
create table t2(a int); |
insert into t2 values (0),(1); |
create table t1 (a int, b int, key(a)); |
insert into t1 select A.a + B.a* 10 + C.a * 100, 12345 from ten A, ten B, ten C; |
Outer join with single-table on the inner side is able to use the ON expression to construct range access:
mysql> explain select * from ten left join t1 on ten.a=t1.b and t1.a<5;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 5 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
add another table there and range access is not used anymore:
mysql> explain select * from ten left join (t1 join t2) on ten.a=t1.b and t1.a<5;
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 1000 | Using where; Using join buffer (incremental, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
MDEV-17493 Partition pruning doesn't work for nested outer joins
-
- Closed
-
Activity
Field | Original Value | New Value |
---|---|---|
Description |
This is the range access part of {code:sql} create table t2(a int); insert into t2 values (0),(1); create table t1 (a int, b int, key(a)); insert into t1 select A.a + B.a* 10 + C.a * 100, 12345 from ten A, ten B, ten C; {code} {noformat} mysql> explain select * from ten left join t1 on ten.a=t1.b and t1.a<5; +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 5 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ {noformat} {noformat} mysql> explain select * from ten left join (t1 join t2) on ten.a=t1.b and t1.a<5; +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) | | 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 1000 | Using where; Using join buffer (incremental, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ {noformat} |
This is the range access part of {code:sql} create table t2(a int); insert into t2 values (0),(1); create table t1 (a int, b int, key(a)); insert into t1 select A.a + B.a* 10 + C.a * 100, 12345 from ten A, ten B, ten C; {code} Outer join with single-table on the inner side is able to use the ON expression to construct range access: {noformat} mysql> explain select * from ten left join t1 on ten.a=t1.b and t1.a<5; +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 5 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ {noformat} {noformat} mysql> explain select * from ten left join (t1 join t2) on ten.a=t1.b and t1.a<5; +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) | | 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 1000 | Using where; Using join buffer (incremental, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ {noformat} |
Description |
This is the range access part of {code:sql} create table t2(a int); insert into t2 values (0),(1); create table t1 (a int, b int, key(a)); insert into t1 select A.a + B.a* 10 + C.a * 100, 12345 from ten A, ten B, ten C; {code} Outer join with single-table on the inner side is able to use the ON expression to construct range access: {noformat} mysql> explain select * from ten left join t1 on ten.a=t1.b and t1.a<5; +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 5 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ {noformat} {noformat} mysql> explain select * from ten left join (t1 join t2) on ten.a=t1.b and t1.a<5; +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) | | 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 1000 | Using where; Using join buffer (incremental, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ {noformat} |
This is the range access part of {code:sql} create table t2(a int); insert into t2 values (0),(1); create table t1 (a int, b int, key(a)); insert into t1 select A.a + B.a* 10 + C.a * 100, 12345 from ten A, ten B, ten C; {code} Outer join with single-table on the inner side is able to use the ON expression to construct range access: {noformat} mysql> explain select * from ten left join t1 on ten.a=t1.b and t1.a<5; +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 5 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------------------+ {noformat} add another table there and range access is not used anymore: {noformat} mysql> explain select * from ten left join (t1 join t2) on ten.a=t1.b and t1.a<5; +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ | 1 | SIMPLE | ten | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) | | 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 1000 | Using where; Using join buffer (incremental, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+ {noformat} |
Link |
This issue relates to |
Assignee | Sergei Petrunia [ psergey ] |
Component/s | Optimizer [ 10200 ] |
Fix Version/s | 10.3.11 [ 23141 ] | |
Resolution | Fixed [ 1 ] | |
Status | Open [ 1 ] | Closed [ 6 ] |
Affects Version/s | 5.5 [ 15800 ] | |
Affects Version/s | 10.0 [ 16000 ] | |
Affects Version/s | 10.1 [ 16100 ] | |
Affects Version/s | 10.2 [ 14601 ] | |
Affects Version/s | 10.3 [ 22126 ] |
Workflow | MariaDB v3 [ 90208 ] | MariaDB v4 [ 155090 ] |
http://lists.askmonty.org/pipermail/commits/2018-October/013030.html