Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
None
-
None
Description
Bug #49845 from bugs.mysql.com still can be reproduced in mariadb-5.3:
MariaDB [test]> set optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
-----------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | range | kp1 | kp1 | 5 | NULL | 48 | Using index condition; Using where; LooseScan |
| 1 | PRIMARY | t4 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.c | 1 | Using index; FirstMatch(t1) |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using join buffer (flat, BNL join) |
-----------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [test]> set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
-----------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | range | kp1 | kp1 | 5 | NULL | 48 | Using index condition; Using where; Rowid-ordered scan; LooseScan |
| 1 | PRIMARY | t4 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.c | 1 | Using index; FirstMatch(t1) |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using join buffer (flat, BNL join) |
-----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [test]> select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
------
| a |
------
| 0 |
| 0 |
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 5 |
| 5 |
| 6 |
| 6 |
| 7 |
| 7 |
| 8 |
| 8 |
| 9 |
| 9 |
| 10 |
| 10 |
| 11 |
| 11 |
| 12 |
| 12 |
| 13 |
| 13 |
| 14 |
| 14 |
| 15 |
| 15 |
| 16 |
| 16 |
| 17 |
| 17 |
| 18 |
| 18 |
| 19 |
| 19 |
------
40 rows in set (0.01 sec)
MariaDB [test]> set optimizer_switch='loosescan=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
------
| a |
------
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
------
20 rows in set (0.00 sec)