Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.6.18, 10.11.8
-
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.
