revision-id: 0d99049dbcd7cc5e3dd309dd7e3df627863fcb81 (mariadb-10.3.7-115-g0d99049dbcd) parent(s): 340c8a2a32dcbe0bb9bc88bd0a6bda5d5e76ed02 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-11 02:31:28 +0530 message: MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions Aggregate function has: - initialization code - code that is run for every row - code that generates a result For an empty table we don't run the middle part but for custom aggregate we are running the middle part also. Fixed this by only allowing the generation of result. As soon as we encounter the first FETCH GROUP NEXT ROW instruction with empty tables, we should exit and tell the handler that there is nothing to fetch and return the result. --- mysql-test/main/custom_aggregate_functions.result | 44 ++++++++++++++++++++--- mysql-test/main/custom_aggregate_functions.test | 43 ++++++++++++++++++++++ sql/sp_head.cc | 41 ++++++++++++++++----- 3 files changed, 115 insertions(+), 13 deletions(-) diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result index 4060d6665f6..e85145c040c 100644 --- a/mysql-test/main/custom_aggregate_functions.result +++ b/mysql-test/main/custom_aggregate_functions.result @@ -84,8 +84,7 @@ return (select count(*) + f2( i - 1) from t1 where id = i); end if; end| select f2(1)| -f2(1) -3 +ERROR 02000: No data - zero rows fetched, selected, or processed select f2(2)| ERROR HY000: Recursive stored functions and triggers are not allowed select f2(3)| @@ -109,7 +108,7 @@ f1(sal) 6000 select f1(sal) from t1 where 1=0; f1(sal) -NULL +0 drop function f1; create aggregate function f1(x int) returns int begin @@ -464,7 +463,7 @@ f1(sal) set @param= 5; execute test using @param; f1(sal) -NULL +0 deallocate prepare test; drop function f2; prepare test from "select f1(sal) from t1 where id>= ?"; @@ -1153,3 +1152,40 @@ i sum(i) NULL 8 drop function agg_sum; drop table t1; +# +# MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions +# +CREATE AGGREGATE FUNCTION test1(p_value TEXT) +RETURNS BOOL +BEGIN +DECLARE CONTINUE HANDLER +FOR NOT FOUND +BEGIN +RETURN FALSE; +END; +FETCH GROUP NEXT ROW; +RETURN TRUE; +END| +CREATE OR REPLACE TABLE t1 (n TEXT); +SELECT test1(n) FROM t1; +test1(n) +0 +CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT) +RETURNS BOOL +BEGIN +DECLARE CONTINUE HANDLER +FOR NOT FOUND +BEGIN +RETURN FALSE; +END; +FETCH GROUP NEXT ROW; +FETCH GROUP NEXT ROW; +FETCH GROUP NEXT ROW; +RETURN TRUE; +END| +SELECT test2(n) FROM t1; +test2(n) +0 +drop function test1; +drop function test2; +drop table t1; diff --git a/mysql-test/main/custom_aggregate_functions.test b/mysql-test/main/custom_aggregate_functions.test index ab799b48bdb..2c981ac518d 100644 --- a/mysql-test/main/custom_aggregate_functions.test +++ b/mysql-test/main/custom_aggregate_functions.test @@ -69,6 +69,7 @@ begin return (select count(*) + f2( i - 1) from t1 where id = i); end if; end| +--error 1329 select f2(1)| # Since currently recursive functions are disallowed ER_SP_NO_RECURSION # error will be returned, once we will allow them error about @@ -965,3 +966,45 @@ select i, sum(i) from t1 group by i with rollup; # Cleanup drop function agg_sum; drop table t1; + +--echo # +--echo # MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions +--echo # + +delimiter |; +CREATE AGGREGATE FUNCTION test1(p_value TEXT) + RETURNS BOOL +BEGIN + DECLARE CONTINUE HANDLER + FOR NOT FOUND + BEGIN + RETURN FALSE; + END; + FETCH GROUP NEXT ROW; + RETURN TRUE; +END| + +delimiter ;| +CREATE OR REPLACE TABLE t1 (n TEXT); +SELECT test1(n) FROM t1; + +delimiter |; +CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT) + RETURNS BOOL + BEGIN + DECLARE CONTINUE HANDLER + FOR NOT FOUND + BEGIN + RETURN FALSE; + END; + FETCH GROUP NEXT ROW; + FETCH GROUP NEXT ROW; + FETCH GROUP NEXT ROW; + RETURN TRUE; + END| + +delimiter ;| +SELECT test2(n) FROM t1; +drop function test1; +drop function test2; +drop table t1; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index c1c938dd9e7..3828cb5237b 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -4406,6 +4406,37 @@ sp_instr_agg_cfetch::execute(THD *thd, uint *nextp) { DBUG_ENTER("sp_instr_agg_cfetch::execute"); int res= 0; + if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT) + { + if (!thd->spcont->quit_func) + { + my_message(ER_SP_FETCH_NO_DATA, + ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0)); + thd->spcont->quit_func= TRUE; + thd->spcont->pause_state= FALSE; + } + else + { + /* + required when we don't come across the return statement. + An example would be + create aggregate function f1(x int) returns int + begin + declare mini int default 0; + declare continue handler for not found set mini=-1; + LOOP + FETCH GROUP NEXT ROW; + set mini = mini + x; + END LOOP; + return 0; + end| + So here we would never execute the RETURN statement, so here + we force to quit the function execution. + */ + thd->spcont->pause_state= TRUE; + } + DBUG_RETURN(res); + } if (!thd->spcont->instr_ptr) { *nextp= m_ip+1; @@ -4416,15 +4447,7 @@ sp_instr_agg_cfetch::execute(THD *thd, uint *nextp) else { thd->spcont->pause_state= FALSE; - if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT) - { - my_message(ER_SP_FETCH_NO_DATA, - ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0)); - res= -1; - thd->spcont->quit_func= TRUE; - } - else - *nextp= m_ip + 1; + *nextp= m_ip + 1; } DBUG_RETURN(res); }