Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0, 10.1, 10.2, 10.3, 10.4
-
None
Description
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (8),(0); |
CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (4,'j'),(6,'v'); |
CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES ('b'),('c'); |
 |
MariaDB [test]> set optimizer_use_condition_selectivity=1; |
Query OK, 0 rows affected (0.003 sec) |
|
MariaDB [test]> explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
|
+------+--------------+-------------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | PRIMARY | t1 | index | a | a | 5 | NULL | 2 | Using where; Using index |
|
| 2 | SUBQUERY | <subquery3> | ALL | distinct_key | NULL | NULL | NULL | 1 | |
|
| 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer (flat, BNL join) |
|
| 3 | MATERIALIZED | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
|
+------+--------------+-------------+-------+---------------+------+---------+------+------+-------------------------------------------------+
|
4 rows in set (0.005 sec)
|
MariaDB [test]> set optimizer_use_condition_selectivity=4;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> explain SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+--------------------------+
|
| 1 | PRIMARY | t1 | index | a | a | 5 | NULL | 2 | Using where; Using index |
|
| 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
|
| 2 | SUBQUERY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | test.t2.c | 1 | |
|
| 3 | MATERIALIZED | t3 | ALL | NULL | NULL | NULL | NULL | 2 | |
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+--------------------------+
|
4 rows in set (0.003 sec)
|
The <subquery3> is materialised and has implicit grouping, so we are sure that it would have 1 record, but in the second case the estimate is 2.