Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3, 10.4, 10.5, 10.6, 10.8, 10.9, 10.10, 10.11, 11.0
-
None
Description
SET max_recursive_iterations = 100000; |
drop table if exists t; |
create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte; |
select max(a) over( ) from t; |
The output from the client, connected to the server running with all defaults.
It's reproducible with MTR just as well, but I wanted to rule out all differences in server configuration between MTR and regular server as well as between MariaDB's MTR and MySQL's MTR.
|
11.0.1 release |
MariaDB [test]> select max(a) over( ) from t; |
...
|
+----------------+ |
30000 rows in set (31.360 sec) |
Test case for MySQL differs in the name of the variable only:
SET cte_max_recursion_depth = 100000; |
drop table if exists t; |
create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte; |
select max(a) over( ) from t; |
|
8.0.28 release |
mysql> select max(a) over( ) from t; |
...
|
+----------------+ |
30000 rows in set (0.04 sec) |
So, it's 30 sec vs 0.04 sec on a release bintar.
Execution plans:
|
MySQL |
mysql> explain format=json select max(a) over( ) from t \G |
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": { |
"select_id": 1, |
"cost_info": { |
"query_cost": "3059.35" |
},
|
"windowing": { |
"windows": [ |
{
|
"name": "<unnamed window>", |
"frame_buffer": { |
"using_temporary_table": true, |
"optimized_frame_evaluation": true |
},
|
"functions": [ |
"max" |
]
|
}
|
],
|
"table": { |
"table_name": "t", |
"access_type": "ALL", |
"rows_examined_per_scan": 30351, |
"rows_produced_per_join": 30351, |
"filtered": "100.00", |
"cost_info": { |
"read_cost": "24.25", |
"eval_cost": "3035.10", |
"prefix_cost": "3059.35", |
"data_read_per_join": "474K" |
},
|
"used_columns": [ |
"a" |
]
|
}
|
}
|
}
|
}
|
1 row in set, 1 warning (0.00 sec) |
 |
mysql> show warnings;
|
+-------+------+----------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+----------------------------------------------------------------------------------------+ |
| Note | 1003 | /* select#1 */ select max(`test`.`t`.`a`) OVER () AS `max(a) over( )` from `test`.`t` | |
+-------+------+----------------------------------------------------------------------------------------+ |
|
MariaDB |
EXPLAIN: {
|
"query_block": { |
"select_id": 1, |
"cost": 4.9214646, |
"window_functions_computation": { |
"sorts": [ |
{
|
"filesort": { |
"sort_key": "a" |
}
|
}
|
],
|
"temporary_table": { |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t", |
"access_type": "ALL", |
"loops": 1, |
"rows": 29623, |
"cost": 4.9214646, |
"filtered": 100 |
}
|
}
|
]
|
}
|
}
|
}
|
}
|
1 row in set (0.000 sec) |