Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.1.10, 5.3.13, 5.5, 10.0, 10.1
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