Loosescan reports different result than other semijoin methods

[Igor Babaev]

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)

[Sergey Petrunia (sergefp)]

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.