revision-id: 621ade377068a00aa73502e75ae5ed0ccee9d573 (mariadb-10.2.16-222-g621ade37706) parent(s): 1c6b982e02eeaa75bb6c2f2a3c2b64491dd6d3c8 author: Varun Gupta committer: Varun Gupta timestamp: 2018-11-02 22:25:11 +0530 message: MDEV-14791: Crash with order by expression containing window functions The issue here is that for a window function in the ORDER BY clause, we were not creating an extra field in the temporary table for the window function (which is contained in an expression). So a call to split_sum_func is added to handle this case Also we need to update all items that contain a window function in the temp table during window function computation as filesort would need these values to be updated to calculate the ORDER BY clause of the select --- mysql-test/r/win.result | 29 +++++++++++++++++++++++++++++ mysql-test/t/win.test | 25 +++++++++++++++++++++++++ sql/sql_select.cc | 24 ++---------------------- sql/sql_window.cc | 27 +++++++++++++++++++++++++++ 4 files changed, 83 insertions(+), 22 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 790b264fc09..905a7e49a06 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3361,3 +3361,32 @@ Esben Tuning 31 68.7500 Kaolin Tuning 88 68.7500 Tatiana Tuning 83 68.7500 drop table t1; +# +# MDEV-14791: Crash with order by expression containing window functions +# +CREATE TABLE t1 (b1 int, b2 int); +INSERT INTO t1 VALUES (1,1),(0,0); +explain +SELECT b1 +from t1 +order by row_number() over (ORDER BY b2) + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +SELECT b1 +from t1 +order by row_number() over (ORDER BY b2) + 1; +b1 +0 +1 +explain SELECT b1 +from t1 +order by row_number() over (ORDER BY b2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +SELECT b1 +from t1 +order by row_number() over (ORDER BY b2); +b1 +0 +1 +DROP TABLE t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index e9c8ee05773..b13c1471346 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2119,3 +2119,28 @@ SELECT name, test, score, FROM t1 ORDER BY test, name; drop table t1; + +--echo # +--echo # MDEV-14791: Crash with order by expression containing window functions +--echo # + +CREATE TABLE t1 (b1 int, b2 int); +INSERT INTO t1 VALUES (1,1),(0,0); + +explain +SELECT b1 +from t1 +order by row_number() over (ORDER BY b2) + 1; + +SELECT b1 +from t1 +order by row_number() over (ORDER BY b2) + 1; + +explain SELECT b1 +from t1 +order by row_number() over (ORDER BY b2); + +SELECT b1 +from t1 +order by row_number() over (ORDER BY b2); +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0cdecf1bf2e..70e4d1d37f8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -933,7 +933,8 @@ JOIN::prepare(TABLE_LIST *tables_init, item->max_length))) real_order= TRUE; - if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM) + if ((item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM) || + item->with_window_func) item->split_sum_func(thd, ref_ptrs, all_fields, 0); } if (!real_order) @@ -26681,27 +26682,6 @@ AGGR_OP::end_send() } else { - /* - In case we have window functions present, an extra step is required - to compute all the fields from the temporary table. - In case we have a compound expression such as: expr + expr, - where one of the terms has a window function inside it, only - after computing window function values we actually know the true - final result of the compounded expression. - - Go through all the func items and save their values once again in the - corresponding temp table fields. Do this for each row in the table. - */ - if (join_tab->window_funcs_step) - { - Item **func_ptr= join_tab->tmp_table_param->items_to_copy; - Item *func; - for (; (func = *func_ptr) ; func_ptr++) - { - if (func->with_window_func) - func->save_in_result_field(true); - } - } rc= evaluate_join_record(join, join_tab, 0); } } diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 465c6ae032c..9fb10339e35 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -2511,11 +2511,38 @@ bool save_window_function_values(List<Item_window_func>& window_functions, TABLE *tbl, uchar *rowid_buf) { List_iterator_fast<Item_window_func> iter(window_functions); + JOIN_TAB *join_tab= tbl->reginfo.join_tab; tbl->file->ha_rnd_pos(tbl->record[0], rowid_buf); store_record(tbl, record[1]); while (Item_window_func *item_win= iter++) item_win->save_in_field(item_win->result_field, true); + /* + In case we have window functions present, an extra step is required + to compute all the fields from the temporary table. + In case we have a compound expression such as: expr + expr, + where one of the terms has a window function inside it, only + after computing window function values we actually know the true + final result of the compounded expression. + + Go through all the func items and save their values once again in the + corresponding temp table fields. Do this for each row in the table. + + This needs to be done earlier because ORDER BY clause can also have + a window function, so we need to make sure all the fields of the temp.table + are updated before we do the filesort. So is best to update the other fields + that contain the window functions along with the computation of window + functions. + */ + + Item **func_ptr= join_tab->tmp_table_param->items_to_copy; + Item *func; + for (; (func = *func_ptr) ; func_ptr++) + { + if (func->with_window_func) + func->save_in_result_field(true); + } + int err= tbl->file->ha_update_row(tbl->record[1], tbl->record[0]); if (err && err != HA_ERR_RECORD_IS_THE_SAME) return true;