Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1, 10.2, 10.3
-
None
Description
based on https://bugs.mysql.com/bug.php?id=79040
create table t1 (i int); |
insert into t1 select seq from seq_1_to_1000; |
select * from ( select * from t1)dt limit 1; # select `test`.`t1`.`i` AS `i` from `test`.`t1` limit 1 |
select * from ( select * from t1 group by i)dt limit 1; #/* select#1 */ select `dt`.`i` AS `i` from (/* select#2 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by `test`.`t1`.`i`) `dt` limit 1 |
MariaDB [test]> explain extended select * from (select * from t1 group by i)dt;
|
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | |
|
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using temporary; Using filesort |
|
+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|
2 rows in set, 1 warning (0.001 sec)
|
|
MariaDB [test]> explain extended select * from (select * from t1)dt;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
1 row in set, 1 warning (0.001 sec)
|
|
MariaDB [test]> select * from (select * from t1)dt limit 1;
|
+------+
|
| i |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> show status like '%Handler_read_rnd%';
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 1 |
|
+--------------------------+-------+
|
3 rows in set (0.002 sec)
|
|
MariaDB [test]> select * from ( select * from t1 group by i)dt limit 1;
|
+------+
|
| i |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.067 sec)
|
|
MariaDB [test]> show status like '%Handler_read_rnd%';
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_rnd | 1000 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 2004 |
|
+--------------------------+-------+
|
based on https://bugs.mysql.com/bug.php?id=92994
create table t1 (i int); |
insert into t1 select seq from seq_1_to_1000; |
|
(select * from t1 limit 100) union all (select * from t1 limit 100) limit 1; |
explain extended (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1; |
MariaDB [test]> explain extended (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | |
|
| 2 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|
2 rows in set, 1 warning (0.003 sec)
|
|
MariaDB [test]> flush status;
|
Query OK, 0 rows affected (0.001 sec)
|
|
MariaDB [test]> (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
|
+------+
|
| i |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.008 sec)
|
|
MariaDB [test]> show status like '%Handler_read_rnd%';
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 200 |
|
+--------------------------+-------+
|
3 rows in set (0.006 sec)
|
|
Attachments
Issue Links
- links to