Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 923246
Re: Loosescan reports different result than other semijoin methods
The cause of the bug seems to be the same as with the MySQL bug: with mrr=on, the optimizer picks the query plan with "Using index condition; Using where; Rowid-ordered scan; LooseScan". This is incorrect, because LooseScan requires records to be produced in key order, while "Rowid-ordered scan" doesnt not guarantee any ordering.