Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
Description
MySQL [test]> CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=LevelDB; |
Query OK, 0 rows affected (0.35 sec) |
|
|
MySQL [test]> INSERT INTO t1 VALUES (1,8),(2,8); |
Query OK, 2 rows affected (0.00 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MySQL [test]>
|
MySQL [test]> SELECT * FROM ( SELECT * FROM t1 ) AS alias |
-> WHERE alias.i IN ( SELECT i FROM t1 WHERE i <= 9 ); |
+----+------+ |
| pk | i |
|
+----+------+ |
| 1 | 8 |
|
| 2 | 8 |
|
| 1 | 8 |
|
| 2 | 8 |
|
+----+------+ |
4 rows in set (0.00 sec) |
MySQL [test]> EXPLAIN EXTENDED
|
-> SELECT * FROM ( SELECT * FROM t1 ) AS alias |
-> WHERE alias.i IN ( SELECT i FROM t1 WHERE i <= 9 ); |
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------------------------------+ |
| 1 | PRIMARY | t1 | range | i | i | 5 | NULL | 10 | 100.00 | Using where; Using index; LooseScan | |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | test.t1.i | 10 | 100.00 | NULL | |
| 2 | DERIVED | t1 | index | NULL | i | 5 | NULL | 1000 | 100.00 | Using index | |
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------------------------------+ |
3 rows in set, 1 warning (0.00 sec) |
|
|
MySQL [test]>
|
MySQL [test]> SHOW WARNINGS;
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Note | 1003 | /* select#1 */ select `alias`.`pk` AS `pk`,`alias`.`i` AS `i` from (/* select#2 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i` AS `i` from `test`.`t1`) `alias` semi join (`test`.`t1`) where ((`alias`.`i` = `test`.`t1`.`i`) and (`test`.`t1`.`i` <= 9)) | |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.00 sec) |
MyISAM produces the expected result:
MySQL [test]> CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=MyISAM; |
Query OK, 0 rows affected (0.23 sec) |
|
|
MySQL [test]> INSERT INTO t1 VALUES (1,8),(2,8); |
Query OK, 2 rows affected (0.00 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MySQL [test]>
|
MySQL [test]> SELECT * FROM ( SELECT * FROM t1 ) AS alias |
-> WHERE alias.i IN ( SELECT i FROM t1 WHERE i <= 9 ); |
+----+------+ |
| pk | i |
|
+----+------+ |
| 1 | 8 |
|
| 2 | 8 |
|
+----+------+ |
2 rows in set (0.01 sec) |
MySQL [test]> EXPLAIN EXTENDED
|
-> SELECT * FROM ( SELECT * FROM t1 ) AS alias |
-> WHERE alias.i IN ( SELECT i FROM t1 WHERE i <= 9 ); |
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------------------------------+ |
| 1 | PRIMARY | t1 | index | i | i | 5 | NULL | 2 | 100.00 | Using where; Using index; LooseScan | |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | test.t1.i | 2 | 100.00 | NULL | |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | |
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------------------------------+ |
3 rows in set, 1 warning (0.00 sec) |
|
|
MySQL [test]>
|
MySQL [test]> SHOW WARNINGS;
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Note | 1003 | /* select#1 */ select `alias`.`pk` AS `pk`,`alias`.`i` AS `i` from (/* select#2 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i` AS `i` from `test`.`t1`) `alias` semi join (`test`.`t1`) where ((`alias`.`i` = `test`.`t1`.`i`) and (`test`.`t1`.`i` <= 9)) | |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.00 sec) |
revision-id: psergey@askmonty.org-20130327181232-q2i2big0pvr38uad
|
revno: 4819
|
branch-nick: mysql-5.6-leveldb
|