[MDEV-622] LP:1002508 - The number of expected rows to be examined is off for a query with ORDER BY Created: 2012-05-21  Updated: 2012-11-22  Resolved: 2012-11-22

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

Type: Bug Priority: Minor
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1002508.xml    

 Description   

If to create and populate tables t1,t2 with the commands

CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
INSERT INTO t1 VALUES
(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
INSERT INTO t2 VALUES
(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);

then Mariadb 5.5 will return the following explain output for the query
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a

MariaDB [test]> EXPLAIN
-> SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

------------------------------------------------------------------------------+

1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index

------------------------------------------------------------------------------+

The expected number of the examined rows from t1 is 4 though it should be 8 as for the following query:

MariaDB [test]> EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

------------------------------------------------------------------------------+

1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index

------------------------------------------------------------------------------+



 Comments   
Comment by Rasmus Johansson (Inactive) [ 2012-05-21 ]

Launchpad bug id: 1002508

Comment by Igor Babaev [ 2012-05-21 ]

Re: The number of expected rows to be examined is off for a query with ORDER BY
See also bug #13528826 from mysql-trunk.

Comment by Igor Babaev [ 2012-11-20 ]

The bug is reproducible with mariadb-5.2/5.3 as well.

Comment by Igor Babaev [ 2012-11-22 ]

The fix was pushed into mariadb-5.5 (rev 3575).

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