revision-id: 1edec241473a122553fa9a27be995f3aef52654e (mariadb-10.3.7-171-g1edec241473) parent(s): 5a1868b58d26b286b6ad433096e7184895953311 author: Varun Gupta committer: Varun Gupta timestamp: 2018-09-12 18:10:16 +0530 message: MDEV-17137: Syntax errors with VIEW using MEDIAN The syntax error happened because we had not implemented a different print for percentile functions. The syntax is a bit different when we use percentile functions as window functions in comparision to normal window functions. Implemented a seperate print function for percentile functions --- mysql-test/main/win_percentile.result | 18 ++++++++++++++++++ mysql-test/main/win_percentile.test | 12 ++++++++++++ sql/item_windowfunc.cc | 17 +++++++++++++++++ sql/item_windowfunc.h | 1 + sql/sql_window.cc | 19 ++++++++++++++++--- sql/sql_window.h | 2 ++ 6 files changed, 66 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/win_percentile.result b/mysql-test/main/win_percentile.result index f46fad80d16..6ae18488697 100644 --- a/mysql-test/main/win_percentile.result +++ b/mysql-test/main/win_percentile.result @@ -348,3 +348,21 @@ MEDIAN(`a2`) OVER (PARTITION BY `pk`) FROM t1; MEDIAN(`a1`) OVER () MEDIAN(`a2`) OVER (PARTITION BY `pk`) DROP TABLE t1; +# +# MDEV-17137: Syntax errors with VIEW using MEDIAN +# +CREATE TABLE t1(val int); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE VIEW v1 AS SELECT MEDIAN(val) OVER() FROM t1; +select * from v1; +MEDIAN(val) OVER() +2.0000000000 +2.0000000000 +2.0000000000 +select median(val) OVER () FROM t1; +median(val) OVER () +2.0000000000 +2.0000000000 +2.0000000000 +drop table t1; +drop view v1; diff --git a/mysql-test/main/win_percentile.test b/mysql-test/main/win_percentile.test index e39af8cfd39..a709b38d2e6 100644 --- a/mysql-test/main/win_percentile.test +++ b/mysql-test/main/win_percentile.test @@ -127,3 +127,15 @@ SELECT MEDIAN(`a1`) OVER (), MEDIAN(`a2`) OVER (PARTITION BY `pk`) FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-17137: Syntax errors with VIEW using MEDIAN +--echo # + +CREATE TABLE t1(val int); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE VIEW v1 AS SELECT MEDIAN(val) OVER() FROM t1; +select * from v1; +select median(val) OVER () FROM t1; +drop table t1; +drop view v1; diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc index 3ad0527384d..b68cb37329f 100644 --- a/sql/item_windowfunc.cc +++ b/sql/item_windowfunc.cc @@ -545,6 +545,11 @@ void Item_sum_hybrid_simple::update_field() void Item_window_func::print(String *str, enum_query_type query_type) { + if (only_single_element_order_list()) + { + print_percentile_functions(str, query_type); + return; + } window_func()->print(str, query_type); str->append(" over "); #ifndef DBUG_OFF @@ -554,3 +559,15 @@ void Item_window_func::print(String *str, enum_query_type query_type) #endif window_spec->print(str, query_type); } +void Item_window_func::print_percentile_functions(String *str, enum_query_type query_type) +{ + window_func()->print(str, query_type); + str->append(" within group "); + str->append('('); + window_spec->print_order(str,query_type); + str->append(')'); + str->append(" over "); + str->append('('); + window_spec->print_partition(str,query_type); + str->append(')'); +} diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h index b3e23748246..268182a2788 100644 --- a/sql/item_windowfunc.h +++ b/sql/item_windowfunc.h @@ -1311,6 +1311,7 @@ class Item_window_func : public Item_func_or_sum bool resolve_window_name(THD *thd); void print(String *str, enum_query_type query_type); + void print_percentile_functions(String *str, enum_query_type query_type); Item *get_copy(THD *thd) { return 0; } diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 38fdd8ab80b..e4d69f2fa5e 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -82,19 +82,32 @@ void Window_spec::print(String *str, enum_query_type query_type) { str->append('('); + print_partition(str, query_type); + print_order(str, query_type); + + if (window_frame) + window_frame->print(str, query_type); + str->append(')'); +} + +void +Window_spec::print_partition(String *str, enum_query_type query_type) +{ if (partition_list->first) { str->append(STRING_WITH_LEN(" partition by ")); st_select_lex::print_order(str, partition_list->first, query_type); } +} + +void +Window_spec::print_order(String *str, enum_query_type query_type) +{ if (order_list->first) { str->append(STRING_WITH_LEN(" order by ")); st_select_lex::print_order(str, order_list->first, query_type); } - if (window_frame) - window_frame->print(str, query_type); - str->append(')'); } bool diff --git a/sql/sql_window.h b/sql/sql_window.h index 392f89e8f03..bf59f00d764 100644 --- a/sql/sql_window.h +++ b/sql/sql_window.h @@ -147,6 +147,8 @@ class Window_spec : public Sql_alloc } void print(String *str, enum_query_type query_type); + void print_order(String *str, enum_query_type query_type); + void print_partition(String *str, enum_query_type query_type); };