Bug #79040 please merge more temptables

Submitted: 30 Oct 2015 21:49 Modified: 5 Nov 2015 14:07
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[30 Oct 2015 21:49] Domas Mituzas
Description:
this merges:
explain select * from ( select * from t1 ) x limit 1;

this does not:
 explain select * from ( select * from t1 group by a ) x limit 1;

this actually ends up scanning whole table, even if outer select would terminate inner select after just one row read: 

mysql> explain select * from ( select * from t1 group by a ) x limit 1;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 3328256 |   100.00 | NULL  |
|  2 | DERIVED     | t1         | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL | 3328256 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

How to repeat:
this repro is simplification of many different problems we observe in our environment. 
please be nice and don't laugh at it.

CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
)

select * from ( select * from t1 group by a ) x limit 1;

Suggested fix:
Whatever Evgeny Potemkin showed as proof of concept few years ago.
[5 Nov 2015 14:07] MySQL Verification Team
Hi Mr. Mituzas,

I have studied your report carefully and I have no doubts that this is fully justified feature request, that should get a proper priority.

Verified.
[31 May 2017 8:42] Vladislav Rastrusny
Has it ever got a proper priority for the last 2 years?

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.