[MDEV-9404] A wrong execution plan a test case from select_found.test Created: 2016-01-13  Updated: 2016-01-13

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.10, 5.3.13, 5.5, 10.0, 10.1
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Igor Babaev Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: upstream-fixed


 Description   

When working on mdev-8646 I found the following discrepancy between the results of the select_found.test in mysql-5.6 and mariadb-10.
Where mysql-5.6 returns:

 mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
 
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
 
|  1 | SIMPLE      | t1    | system | kid           | NULL | NULL    | NULL |    0 | const row not found |
 
|  1 | SIMPLE      | t2    | ALL    | e_n           | NULL | NULL    | NULL |  200 | Using temporary     |
 
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+

mariadb-10.1 returns

MariaDB [test]> EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 
+------+-------------+-------+--------+---------------+------+---------+------+------+--------------------------------------+
 
| id   | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra                                |
 
+------+-------------+-------+--------+---------------+------+---------+------+------+--------------------------------------+
 
|    1 | SIMPLE      | t1    | system | PRIMARY,kid   | NULL | NULL    | NULL |    0 | const row not found; Using temporary |
 
|    1 | SIMPLE      | t2    | ALL    | NULL          | NULL | NULL    | NULL |  200 |                                      |
 
+------+-------------+-------+--------+---------------+------+---------+------+------+--------------------------------------+

As the select options contain SQL_CALC_FOUND_ROWS the estimate for the examined number of rows should be 200, n

 Comments   
Comment by Igor Babaev [ 2016-01-13 ]

The wrong estimate and the wrong plan (full index scan of t2 instead of full table scan of t2) is due to this wrong code in JOIN::optimize_inner():

/*
 
  Calculate a possible 'limit' of table rows for 'GROUP BY': 'need_tmp'
 
  implies that there will be more postprocessing so the specified
 
  'limit' should not be enforced yet in the call to
 
  'test_if_skip_sort_order'.
 
*/
 
const ha_rows limit = need_tmp ? HA_POS_ERROR : unit->select_limit_cnt;

mysql-5.6 uses a different code here:

 const ha_rows limit = need_tmp ? HA_POS_ERROR : m_select_limit;

Comment by Elena Stepanova [ 2016-01-13 ]

I assume the description was supposed to say that MariaDB 10.1 returns

+------+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
 
| id   | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
 
+------+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
 
|    1 | SIMPLE      | t1    | system | PRIMARY,kid   | NULL | NULL    | NULL |    0 | const row not found |
 
|    1 | SIMPLE      | t2    | index  | NULL          | e_n  | 104     | NULL |   10 |                     |
 
+------+-------------+-------+--------+---------------+------+---------+------+------+---------------------+

where 10 is wrong, it should be 200.

Same in MySQL 5.5, apparently fixed in MySQL 5.6.

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