Loosescan reports different result than other semijoin methods

Bug #923246 reported by Igor Babaev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Critical
Sergey Petrunia

Bug 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)

Changed in maria:
importance: Undecided → Critical
milestone: none → 5.3
status: New → Confirmed
assignee: nobody → Sergey Petrunia (sergefp)
Revision history for this message
Sergey Petrunia (sergefp) wrote :

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.

Changed in maria:
status: Confirmed → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.