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

Bug #1002508 reported by Igor Babaev on 2012-05-21
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
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  Edit
Everyone can see this information.

Other bug subscribers