The number of expected rows to be examined is off for a query with ORDER BY

Bug #1002508 reported by Igor Babaev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Confirmed
Medium
Igor Babaev

Bug 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 |
+------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+

Changed in maria:
status: New → Confirmed
importance: Undecided → Medium
assignee: nobody → Igor Babaev (igorb-seattle)
milestone: none → 5.5
Revision history for this message
Igor Babaev (igorb-seattle) wrote :

See also bug #13528826 from mysql-trunk.

description: updated
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.