Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.6.18, 10.11.8
-
None
-
None
Description
To reproduce this problem, import attached schema tt (tt.zip) as an example. Then, run this:
set session optimizer_switch='split_materialized=on';
|
explain SELECT `ServerID`,
|
`ServerIP`, `ServerPort`, ROUND(IFNULL(CAST(`SessionCount` AS UNSIGNED),CONVERT(0,UNSIGNED)) / 10) AS `SessionBucket`
|
FROM `Servers`
|
LEFT JOIN (SELECT `Servers_ServerID`,CONVERT(COUNT(1),UNSIGNED) AS `SessionCount` FROM `Session` GROUP BY `Servers_ServerID`) `SC` ON (`ServerID` = `SC`.`Servers_ServerID`);
|
On linux, you will see "LATERAL DERIVED" as select_type:
+------+-----------------+------------+-------+------------------------+------------------------+---------+---------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+-------+------------------------+------------------------+---------+---------------------+------+-------------+
|
| 1 | PRIMARY | Servers | index | NULL | Server_UNIQUE | 164 | NULL | 30 | Using index |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | tt.Servers.ServerID | 2 | |
|
| 2 | LATERAL DERIVED | Session | ref | fk_Session_Servers_idx | fk_Session_Servers_idx | 4 | tt.Servers.ServerID | 25 | Using index |
|
+------+-----------------+------------+-------+------------------------+------------------------+---------+---------------------+------+-------------+
|
On Windows, you cannot get this optimization. See the attached images.
Tested on Windows releases 10.11.8 and 10.6.18.
