revision-id: 6373c8599004e15a6e23816302de9ca310787111 (mariadb-10.3.6-16-g6373c85) parent(s): cff60be7fe159fdcb2517ce8441610ad512aa7d0 author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-19 18:35:03 -0700 message: MDEV-15902 Assertion `n < m_size' failed, sql_array.h:64: Element_type& Bounds_checked_array<Element_type>::operator[] (size_t) [with Element_type = Item*; size_t = long unsigned int] In sql_yacc.yy the semantic actions for the MEDIAN window function lacked a call of st_select_lex::prepare_add_window_spec(). This function saves the head of the thd->lex->order_list into lex->save_order_list in order this head to be restored in st_select_lex::add_window_spec after the specification of the window function has been parsed. Without a call of prepare_add_window_spec() when add_window_spec() was called the head of an empty list was copied into thd->lex->order_list (instead of assumed saved head this list). This made the list thd->lex->order_list invalid and potentially could cause many different problems. --- mysql-test/main/derived_cond_pushdown.result | 44 ++++++++++++++-------------- mysql-test/main/win_percentile.result | 15 ++++++++++ mysql-test/main/win_percentile.test | 15 ++++++++++ sql/sql_yacc.yy | 3 +- 4 files changed, 54 insertions(+), 23 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 1b7aeda..82f621c 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15130,31 +15130,31 @@ cte as (select median(f2) over (partition by f1) as k1 from t1 order by f1), cte1 as (select median(f4) over (partition by f1) as k2 from t1) select k1,k2 from cte1, cte; k1 k2 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 +1.0000000000 9.0000000000 +1.0000000000 9.0000000000 1.0000000000 8.0000000000 -1.0000000000 8.0000000000 -1.0000000000 8.0000000000 -1.0000000000 8.0000000000 -1.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 -0.0000000000 8.0000000000 +1.0000000000 0.0000000000 +1.0000000000 9.0000000000 +0.0000000000 9.0000000000 +0.0000000000 9.0000000000 0.0000000000 8.0000000000 +0.0000000000 0.0000000000 +0.0000000000 9.0000000000 +0.0000000000 9.0000000000 +0.0000000000 9.0000000000 0.0000000000 8.0000000000 +0.0000000000 0.0000000000 +0.0000000000 9.0000000000 +0.0000000000 9.0000000000 +0.0000000000 9.0000000000 0.0000000000 8.0000000000 +0.0000000000 0.0000000000 +0.0000000000 9.0000000000 +0.0000000000 9.0000000000 +0.0000000000 9.0000000000 0.0000000000 8.0000000000 +0.0000000000 0.0000000000 +0.0000000000 9.0000000000 explain with cte as (select median(f2) over (partition by f1) as k1 from t1 order by f1), cte1 as (select median(f4) over (partition by f1) as k2 from t1) @@ -15162,6 +15162,6 @@ select k1,k2 from cte1, cte; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -3 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort -2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary +3 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary +2 DERIVED t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort drop table t1; diff --git a/mysql-test/main/win_percentile.result b/mysql-test/main/win_percentile.result index c51e2e6..d1b205e 100644 --- a/mysql-test/main/win_percentile.result +++ b/mysql-test/main/win_percentile.result @@ -324,3 +324,18 @@ median(score) over (partition by name) c 4.0000000000 4.0000000000 4.0000000000 4.0000000000 drop table t1; +# +# MDEV-13352: MEDIAN window function over a table with virtual column +# in select with CTE and ORDER BY +# +CREATE TABLE t1 (f1 int ,f2 int ,f3 int, f4 int, v1 int AS (-f4) virtual); +INSERT INTO t1(f1,f2,f3,f4) VALUES +(1,10,100,10), (7,11,112,15), (3,14,121,12); +WITH CTE AS (SELECT MIN(f3) OVER () FROM t1) +SELECT MEDIAN(f3) OVER () FROM t1 +ORDER BY f1, f2, f3, f4, v1; +MEDIAN(f3) OVER () +112.0000000000 +112.0000000000 +112.0000000000 +DROP TABLE t1; diff --git a/mysql-test/main/win_percentile.test b/mysql-test/main/win_percentile.test index 468d8cf..233b21d 100644 --- a/mysql-test/main/win_percentile.test +++ b/mysql-test/main/win_percentile.test @@ -102,3 +102,18 @@ select median(score) over (partition by name), percentile_cont(0.8) within grou select median(score) over (partition by name), percentile_cont(0.9) within group(order by score) over (partition by name) as c from t1; select median(score) over (partition by name), percentile_cont(1) within group(order by score) over (partition by name) as c from t1; drop table t1; + +--echo # +--echo # MDEV-13352: MEDIAN window function over a table with virtual column +--echo # in select with CTE and ORDER BY +--echo # + +CREATE TABLE t1 (f1 int ,f2 int ,f3 int, f4 int, v1 int AS (-f4) virtual); +INSERT INTO t1(f1,f2,f3,f4) VALUES + (1,10,100,10), (7,11,112,15), (3,14,121,12); + +WITH CTE AS (SELECT MIN(f3) OVER () FROM t1) +SELECT MEDIAN(f3) OVER () FROM t1 +ORDER BY f1, f2, f3, f4, v1; + +DROP TABLE t1; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d69156c..93704cd 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11257,10 +11257,11 @@ percentile_function: { Item *args= new (thd->mem_root) Item_decimal(thd, "0.5", 3, thd->charset()); - if (($$ == NULL) || (thd->is_error())) + if ((args == NULL) || (thd->is_error())) { MYSQL_YYABORT; } + Select->prepare_add_window_spec(thd); if (add_order_to_list(thd, $3,FALSE)) MYSQL_YYABORT; $$= new (thd->mem_root) Item_sum_percentile_cont(thd, args);