revision-id: 5b0b73e9922f895cbae5c3ba48d1b65c45ed69bb (mariadb-10.2.19-72-g5b0b73e9922) parent(s): 74659e55b7dffcdbb7f8b2cac4ccea92dacebd70 author: Varun Gupta committer: Varun Gupta timestamp: 2018-12-19 00:08:25 +0530 message: MDEV-15424: Unreasonable SQL Error (1356) on select from view While printing a view containing a window function we were printing it as an Item_field object instead of an Item_window_func object. This is incorrect and this leads to us throwing an error ER_VIEW_INVALID. Fixed by adjusting the Item_ref:print function. Also made UDF function aware if there arguments have window function. --- mysql-test/r/udf.result | 14 ++++++++++++++ mysql-test/r/win.result | 18 ++++++++++++++++++ mysql-test/t/udf.test | 13 +++++++++++++ mysql-test/t/win.test | 13 +++++++++++++ sql/item.cc | 4 +++- sql/item_func.cc | 2 ++ 6 files changed, 63 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index 98aa2b50fc6..3a84caa828a 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -465,3 +465,17 @@ a b Hello HL DROP FUNCTION METAPHON; DROP TABLE t1; +# +# MDEV-15424: Unreasonal SQL Error (1356) on select from view +# +CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; +create table t1(a int , b int); +insert into t1 values(100, 54), (200, 199); +create view v1 as select myfunc_int(max(a) over (order by b) , b) from t1; +select * from v1; +myfunc_int(max(a) over (order by b) , b) +154 +399 +drop view v1; +drop function myfunc_int; +drop table t1; diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index e902d62326e..2e80c2c961b 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3470,3 +3470,21 @@ SELECT DISTINCT MIN(b1) OVER () FROM t1; MIN(b1) OVER () 1 drop table t1; +# +# MDEV-15424: Unreasonal SQL Error (1356) on select from view +# +create table t1 (id int, n1 int); +insert into t1 values (1,1), (2,1), (3,2), (4,4); +create view v1 as SELECT ifnull(max(n1) over (partition by n1),'aaa') FROM t1; +explain select * from v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using temporary +select * from v1; +ifnull(max(n1) over (partition by n1),'aaa') +1 +1 +2 +4 +drop table t1; +drop view v1; diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 42a813b0782..881e753de7d 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -528,3 +528,16 @@ DROP FUNCTION METAPHON; #INSERT INTO t1 (a) VALUES ('Hello'); #SELECT * FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-15424: Unreasonal SQL Error (1356) on select from view +--echo # +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO"; +create table t1(a int , b int); +insert into t1 values(100, 54), (200, 199); +create view v1 as select myfunc_int(max(a) over (order by b) , b) from t1; +select * from v1; +drop view v1; +drop function myfunc_int; +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index b0e1a16fae6..18bdfa31691 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2227,3 +2227,16 @@ EXPLAIN SELECT DISTINCT MIN(b1) OVER () FROM t1; SELECT DISTINCT MIN(b1) OVER () FROM t1; drop table t1; + +--echo # +--echo # MDEV-15424: Unreasonal SQL Error (1356) on select from view +--echo # + +create table t1 (id int, n1 int); +insert into t1 values (1,1), (2,1), (3,2), (4,4); + +create view v1 as SELECT ifnull(max(n1) over (partition by n1),'aaa') FROM t1; +explain select * from v1; +select * from v1; +drop table t1; +drop view v1; diff --git a/sql/item.cc b/sql/item.cc index 1c0b6cc4043..761c9fbec3d 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -7815,7 +7815,9 @@ void Item_ref::print(String *str, enum_query_type query_type) { if (ref) { - if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF && + if ((*ref)->type() != Item::CACHE_ITEM && + (*ref)->type() != Item::WINDOW_FUNC_ITEM && + ref_type() != VIEW_REF && !table_name && name && alias_name_used) { THD *thd= current_thd; diff --git a/sql/item_func.cc b/sql/item_func.cc index 512c8fccab0..89ca25bbfd4 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -3484,6 +3484,8 @@ udf_handler::fix_fields(THD *thd, Item_func_or_sum *func, if (item->maybe_null) func->maybe_null=1; func->with_sum_func= func->with_sum_func || item->with_sum_func; + func->with_window_func= func->with_window_func || + item->with_window_func; func->with_field= func->with_field || item->with_field; func->with_param= func->with_param || item->with_param; func->with_subselect|= item->with_subselect;