Wrong result in maria-5.3-wl21 with ORDER BY + LIMIT

[Philip Stoev]

The test case below returns 1 row with maria-5.3-wl21 and no rows with other trees and other plans. In particular, maria-5.3 produces exactly the same plan and the correct result. Maria-5.2 produces a slightly different plan with a correct result.

Test case:

--source include/have_innodb.inc
CREATE TABLE t2 ( f7 varchar(64), KEY (f7)) ENGINE=InnoDB;
INSERT IGNORE INTO t2 VALUES ('d'),('UALLN'),('d'),('z'),('r'),('YVAKV'),('d'),('TNGZK'),('e'),('xucupaxdyythsgiw'),('why'),('ttugkxucupaxdyyt'),('l'),('LHTKN'),('d'),('o'),('v'),('KGLCJ'),('your');
CREATE TABLE t1 ( f10 int(11), f4 varchar(10), KEY (f10), KEY (f4)) ;
INSERT IGNORE INTO t1 VALUES ('4','e'),('891879424','l'),('-243400704','ectlyqupbk'),('1851981824','of'),('-1495203840','you'),('4','no'),('-1436942336','c'),('891420672','DQQYO'),('608698368','qergldqmec'),('1','x');

SET SESSION optimizer_switch='index_merge_sort_intersection=off';
SELECT t1.f4 AS field1 FROM t2 JOIN t1 ON t2.f7 = t1.f4 WHERE t1.f10 IN ( 9 , 0 , 100 ) ORDER BY field1 LIMIT 1;

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range f10,f4 f4 13 NULL 10 Using where
1 SIMPLE t2 ref f7 f7 67 test.t1.f4 1 Using where; Using index

[Philip Stoev (philip-stoev)]

Not reproducible with maria-5.1-wl21

[Igor Babaev (igorb-seattle)]

The problem can be reproduced for MyISAM as well:

MariaDB [test]> CREATE TABLE t2 ( f7 varchar(64), KEY (f7));
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> INSERT IGNORE INTO t2 VALUES ('d'),('UALLN'),('d'),('z'),('r'),('YVAKV'),('d'),('TNGZK'),('e'),('xucupaxdyythsgiw'),('why'),('ttugkxucupaxdyyt'),('l'),('LHTKN'),('d'),('o'),('v'),('KGLCJ'),('your');
Query OK, 19 rows affected (0.00 sec)
Records: 19 Duplicates: 0 Warnings: 0

MariaDB [test]> CREATE TABLE t1 ( f10 int(11), f4 varchar(10), KEY (f10), KEY (f4)) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT IGNORE INTO t1 VALUES ('4','e'),('891879424','l'),('-243400704','ectlyqupbk'),('1851981824','of'),('-1495203840','you'),('4','no'),('-1436942336','c'),('891420672','DQQYO'),('608698368','qergldqmec'),('1','x');
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

MariaDB [test]>
MariaDB [test]> SET SESSION optimizer_switch='index_merge_sort_intersection=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT t1.f4 AS field1 FROM t2 JOIN t1 ON t2.f7 = t1.f4 WHERE t1.f10 IN ( 9 , 0 , 100 ) ORDER BY field1 LIMIT 1;
+--------+
| field1 |
+--------+
| e |
+--------+
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT t1.f4 AS field1 FROM t2 JOIN t1 ON t2.f7 = t1.f4 WHERE t1.f10 IN ( 9 , 0 , 100 ) ORDER BY field1 LIMIT 1;
+----+-------------+-------+-------+---------------+------+---------+------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------------+------+--------------------------+
| 1 | SIMPLE | t1 | range | f10,f4 | f4 | 13 | NULL | 10 | Using where |
| 1 | SIMPLE | t2 | ref | f7 | f7 | 67 | test.t1.f4 | 2 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------------+------+--------------------------+
2 rows in set (0.00 sec)

[Igor Babaev (igorb-seattle)]

This is a duplicate of bug #694092 that has been already pushed into 5.3, but not in maria-5.3-wl21.
(After I had applied the patch of #694092 the problem disappeared.)
The test case of this bug will be added to the test case of bug #694092 though.