revision-id: 5e48501d43feb0c53f35ff74de34db73a038c2ad (mariadb-10.3.7-54-g5e48501d43f) parent(s): b534a7b89ef39cd75bfe09524be044bc16ea2f48 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-03 16:03:30 +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 geneartion 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 | 17 +++++---- 3 files changed, 91 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 3a9e83d9d09..1f70dcf9244 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -4403,6 +4403,13 @@ 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) + { + my_message(ER_SP_FETCH_NO_DATA, + ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0)); + res= -1; + thd->spcont->quit_func= TRUE; + } if (!thd->spcont->instr_ptr) { *nextp= m_ip+1; @@ -4413,15 +4420,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); }