Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result in maria-5.3-wl21 with ORDER BY + LIMIT
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)
Re: Wrong result in maria-5.3-wl21 with ORDER BY + LIMIT
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.
Launchpad bug id: 695442
Re: Wrong result in maria-5.3-wl21 with ORDER BY + LIMIT
Not reproducible with maria-5.1-wl21