[MDEV-2662] LP:695442 - Wrong result in maria-5.3-wl21 with ORDER BY + LIMIT Created: 2010-12-29  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug695442.xml    

 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

 Comments   
Comment by Philip Stoev (Inactive) [ 2010-12-29 ]

Re: Wrong result in maria-5.3-wl21 with ORDER BY + LIMIT
Not reproducible with maria-5.1-wl21

Comment by Igor Babaev [ 2010-12-29 ]

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)

Comment by Igor Babaev [ 2010-12-29 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 695442

Generated at Thu Feb 08 06:43:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.