Details
-
Bug
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Fixed
-
None
-
None
-
None
Description
create table t11 (a int not null);
insert into t11 values (0),(1),(2),(3);
create table t12 (pk int not null primary key, b int not null);
insert into t12 select a,a from t11;
explain select * from t11 left join t12 on t12.pk=t12.b and t12.b=3;
-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------+
| 1 | SIMPLE | t11 | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | t12 | ALL | NULL | NULL | NULL | NULL | 4 |
-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show warnings\G
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 1. row ***************************
Level: Note
Code: 1003
Message: select `j33`.`t11`.`a` AS `a`,`j33`.`t12`.`pk` AS `pk`,`j33`.`t12`.`b` AS `b` from `j33`.`t11` left join `j33`.`t12` on(((`j33`.`t12`.`pk` = `j33`.`t12`.`b`) and (`j33`.`t12`.`b` = 3))) where 1
- 1. row ***************************
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
mysql> explain extended select * from t11 left join t12 on t12.pk=t12.b and t12.b=3 where t11.a=333;
--------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------
| 1 | SIMPLE | t11 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
| 1 | SIMPLE | t12 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 |
--------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings\G
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 1. row ***************************
Level: Note
Code: 1003
Message: select `j33`.`t11`.`a` AS `a`,`j33`.`t12`.`pk` AS `pk`,`j33`.`t12`.`b` AS `b` from `j33`.`t11` left join `j33`.`t12` on(((`j33`.`t12`.`pk` = 3) and (`j33`.`t12`.`b` = 3))) where (`j33`.`t11`.`a` = 333)
1 row in set (0.00 sec)
- 1. row ***************************
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-