path: root/mysql-test/t/custom_aggregate_functions.test

create table t2 (sal int(10));
delimiter |;

create aggregate function f1(x INT) returns int
begin
  declare continue handler for not found return 0;
  loop
    fetch group next row;
    insert into t2 (sal) values (x);
  end loop;
end|

delimiter ;|

create table t1 (sal int(10),id int(10));
INSERT INTO t1 (sal,id) VALUES (5000,1);
INSERT INTO t1 (sal,id) VALUES (2000,1);
INSERT INTO t1 (sal,id) VALUES (1000,1);
select f1(sal) from t1 where id>= 1;
select * from t2;
drop table t2;
drop function f1;

delimiter |;
--error ER_INVALID_AGGREGATE_FUNCTION
create aggregate function f1(x INT) returns INT
begin
  insert into t1(sal) values (x);
  return x;
end|

--error ER_NOT_AGGREGATE_FUNCTION
create function f1(x INT) returns INT
begin
  set x=5;
  fetch group next row;
return x+1;
end |

create aggregate function f1(x INT) returns INT
begin
  declare continue handler for not found return x;
  loop
    fetch group next row;
  end loop;
end |
delimiter ;|

select f1(1);
show create function f1;
--error ER_PARSE_ERROR
alter function f1 aggregate none;
show create function f1;
select f1(1);
drop function f1;


delimiter |;


create aggregate function f2(i int) returns int
begin
  FEtCH GROUP NEXT ROW;
  if i <= 0 then
    return 0;
  elseif i = 1  then
    return (select count(*) from t1 where id = i);
  else
    return (select count(*) + f2( i - 1) from t1 where id = i);
  end if;
end|
select f2(1)|
# Since currently recursive functions are disallowed ER_SP_NO_RECURSION
# error will be returned, once we will allow them error about
# insufficient number of locked tables will be returned instead.
--error ER_SP_NO_RECURSION
select f2(2)|
--error ER_SP_NO_RECURSION
select f2(3)|
drop function f2|

create aggregate function f1(x int) returns int
begin
  declare mini int default 0;
  declare continue handler for not found return mini;
  loop
    fetch group next row;
    set mini= mini+x;
    fetch group next row;
  end loop;
end|


delimiter ;|

select f1(10);
select f1(sal) from t1;
select f1(sal) from t1 where 1=0;
drop function f1;
delimiter |;


#WITHOUT RETURN STATEMENT IN AGGREGATE FUNCTIONS
--error 1320
create aggregate function f1(x int) returns int
begin
  declare mini int default 0;
  LOOP
    FETCH GROUP NEXT ROW;
    set mini = mini + x;
  END LOOP;
end|

#without handler
create aggregate function f1(x int) returns int
begin
  declare mini int default 0;
  LOOP
    FETCH GROUP NEXT ROW;
    set mini = mini + x;
  END LOOP;
  return -1;
end|

--error 1329
select f1(sal) from t1|
drop function f1|

#without loop
create aggregate function f1(x int) returns int
begin
  declare mini int default 0;
  declare continue handler for not found return mini;
    FETCH GROUP NEXT ROW;
    set mini = mini + x;
end|

--error 1321
select f1(sal) from t1|
drop function f1|


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|

--error 1321
select f1(sal) from t1|
drop function f1|
drop table t1|

delimiter ;|

# primary indexing

create table t1 (sal int, id int, val int, counter int, primary key(id));
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 1, 10, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 2, 16, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 3, 18, 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 4, 15, 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 5, 11, 5);

delimiter |;

create aggregate function f1(x INT) returns double
begin
  declare  z double default 0;
  declare continue handler for not found return z;
  loop
    fetch group next row;
    set z= z+x;
  end loop;
end|

delimiter ;|

--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by val;
--sorted_result
select id, f1(sal) from t1;
--sorted_result
select id, f1(sal) from t1 where id>= 1;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by val;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by val;
drop table t1;

#unique index

create table t1 (sal int, id int, val int, counter int, primary key(id), unique key(val));

INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 1, 10, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 2, NULL, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 3, 18, 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 4, 15, 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 5, 11, 5);

--sorted_result
select id, f1(sal) from t1;
--sorted_result
select id, f1(sal) from t1 where id>= 1;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by val;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by val;
drop table t1;

# compound indexing
create table t1 (sal int, id int, val int, counter int, primary key(id), INDEX name (val,counter));

INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 1, 10, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 2, 10, 4);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 3, 18, 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 4, 11, 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 5, 11, 5);
--sorted_result
select id, f1(sal) from t1;
--sorted_result
select id, f1(sal) from t1 where id>= 1;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by val;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by counter order by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by counter;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by id;
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by val order by val;
drop table t1;
drop function f1;

# prepared statement with aggregate functions

delimiter |;

create aggregate function f1(x INT) returns double
begin
  declare  z double default 0;
  declare continue handler for not found return z;
  loop
    fetch group next row;
    set z= z+x;
  end loop;
end|

create aggregate function f2() returns double
begin
  declare z int default 0;
  declare continue handler for not found return z;
  loop
    fetch group next row;
    set z = z+1;
  end loop;
end|

delimiter ;|

create table t1 (sal int, id int, val int, counter int);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 10, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 1, 16, 5);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 2, 18, 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 3, 15, 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 4, 11, 4);

prepare test from "select f2() from t1 where id>= ?";
set @param= 2;
execute test using @param;
execute test using @param;
execute test using @param;
execute test using @param;
set @param= 1;
execute test using @param;
set @param= 3;
execute test using @param;
set @param= 4;
execute test using @param;
deallocate prepare test;

prepare test from "select f1(sal) from t1 where id>= ?";
set @param= 2;
execute test using @param;
execute test using @param;
execute test using @param;
execute test using @param;
set @param= 1;
execute test using @param;
set @param= 3;
execute test using @param;
set @param= 4;
execute test using @param;
set @param= 5;
execute test using @param;
deallocate prepare test;

drop function f2;

prepare test from "select f1(sal) from t1 where id>= ?";
set @param= 2;
execute test using @param;
drop function f1;

create function f1(x int) returns int
   return -1;

execute test using @param;

drop function f1;

delimiter |;

create aggregate function f1(x INT) returns double
begin
  declare  z double default 0;
  declare continue handler for not found return z;
  loop
    fetch group next row;
    set z= z+x;
  end loop;
end|

delimiter ;|

execute test using @param;

deallocate prepare test;

drop table t1;
drop function f1;

create table t1 (sal int, id int, val varchar(10), counter int);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 'ab', 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 1, 'cd', 5);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 'ef', 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 3, 'gh', 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 4, 'ij', 4);

create table t2 (sal int, id int, val int, counter int);
INSERT INTO t2 (sal, id, val, counter) VALUES (1000, 2, 10, 2);
INSERT INTO t2 (sal, id, val, counter) VALUES (2000, 1, 16, 5);
INSERT INTO t2 (sal, id, val, counter) VALUES (6000, 2, 18, 1);
INSERT INTO t2 (sal, id, val, counter) VALUES (5000, 3, 15, 3);
INSERT INTO t2 (sal, id, val, counter) VALUES (3000, 4, 11, 4);
delimiter |;

create aggregate function f1(x double) returns double
begin
  declare  z double default 0;
  declare continue handler for not found return z;
  loop
  fetch group next row;
  set z= z+x;
  end loop;
end|

create aggregate function f2(x INT) returns CHAR(10)
 begin
   declare mini INT default 0;
   declare continue handler for not found return mini;
   loop
       fetch group next row;
       set mini= mini + x;
   end loop;
end|

create aggregate function f3(x INT) returns CHAR(10)
 begin
   declare mini INT default 0;
   declare continue handler for not found return mini;
   loop
       fetch group next row;
     set mini= mini + x;
       fetch group next row;
       set mini= mini - x;
   end loop;
end|

create aggregate function f4(x INT, y varchar(10)) returns varchar(1000)
begin
   declare str varchar(1000) default '';
   declare continue handler for not found return str;
   loop
       fetch group next row;
     set str= concat(str,y);
   end loop;
end|

create aggregate function f5(x INT) returns varchar(1000)
begin
   declare z int default 0;
   DECLARE cur1 CURSOR FOR SELECT sal FROM test.t2;
   declare continue handler for not found return 0;
   loop
       fetch group next row;
       set z = z+x;
   end loop;
end|



create function f6(x int) returns int
return (select f1(sal) from t1)|

delimiter ;|

select f1(sal) from t1;

# group by test

--sorted_result
select f1(sal) from t1 where id>= 1 group by counter;

# multiple fetch statements in the loop
--sorted_result
select f3(sal) from t1;

# incorrect column type
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
select f2(val) from t1;

#subquery
--sorted_result
select val, id, c from (select f1(sal) as c from t2) as t1, t2;

#multiple calls to an aggregate function
--sorted_result
select f1(sal),f1(val), f1(id), f1(sal) from t2;

#string type, also more than one areguments
--sorted_result
select f4(sal, val) from t1;

#select f1((select sal from t2 where id= 1))  from t1;
--sorted_result
select c from (select f1(sal) as c from t2) as t1;

# this fails as more than one row is returned
#select f1((select val from t2 where id > 1))  from t1;

select f1((select val from t2 where 0 > 1))  from t1;
select f1((select val from t2 where id= 1))  from t1;

select f5(sal) from t1;

SELECT f1(sal)*f1(sal) FROM t1;

--sorted_result
SELECT (SELECT f1(sal) FROM t1) FROM t2;
--sorted_result
select id, f1(sal) from t1;
--sorted_result
select id, f1(sal) from t1 where id>= 1;
--sorted_result
select f1(sal), f1(sal) from t1 where id>= 1 group by counter;
--sorted_result
select f1(sal), f1(sal) from t1 where id>= 1 group by id ;
--sorted_result
select f1(sal) from t1 where id>= 1 group by id ;
select f1(sal) from t1 where id>= 1 order by counter;
select f1(sal) from t1 where id>= 1 group by id order by counter;
select counter, id, f1(sal) from t1 where id>= 1 group by id order by counter;
select id, f1(sal) from t1 where id>= 1 group by id order by counter;
drop table t1;
drop table t2;
drop function f1;
drop function f2;
drop function f3;
drop function f4;
drop function f5;
drop function f6;


delimiter |;

# aggregate AND function

create aggregate function f1(x INT) returns INT
begin
  declare z double default 1000;
  declare continue handler for not found return z;
  loop
  fetch group next row;
  set z= (z&x);
  end loop;
end|

delimiter ;|

create table t1 (sal int, id int, val int, counter int);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 2, 10, 2);
INSERT INTO t1 (sal, id, val, counter) VALUES (7000, 1, 16, 5);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 2, 18, 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 3, 15, 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (3000, 4, 11, 4);
INSERT INTO t1 (sal, id, val, counter) VALUES (2000, 5, 10, 7);
INSERT INTO t1 (sal, id, val, counter) VALUES (5000, 7, 13, 8);
INSERT INTO t1 (sal, id, val, counter) VALUES (6000, 6, 19, 9);
INSERT INTO t1 (sal, id, val, counter) VALUES (7000, 7, 12, 0);
INSERT INTO t1 (sal, id, val, counter) VALUES (4000, 6, 14, 1);
INSERT INTO t1 (sal, id, val, counter) VALUES (8000, 5, 19, 3);
INSERT INTO t1 (sal, id, val, counter) VALUES (9000, 4, 11, 4);
INSERT INTO t1 (sal, id, val, counter) VALUES (1000, 3, 11, 2);

select f1(sal) from t1 where id>= 1;
drop function f1;

delimiter |;

# aggregate AVG function

create aggregate function f1(x INT) returns double
begin
  declare z double default 0;
  declare count double default 0;
  declare continue handler for not found return z/count;
  loop
  fetch group next row;
  set z= z+x;
  set count= count+1;
  end loop;
end|

delimiter ;|
select f1(sal) from t1 where id>= 1;
drop function f1;
delimiter |;

# aggregate MAX function

create aggregate function f1(x INT) returns INT
begin
  declare maxi INT default -1;
  declare continue handler for not found return maxi;
  loop
  fetch group next row;
  if maxi < x then
     set maxi= x;
  end if;
  end loop;
end|

delimiter ;|
select f1(sal) from t1 where id>= 1;
drop function f1;
delimiter |;

# aggregate MIN function

create aggregate function f1(x INT) returns double
begin
  declare mini INT default 100000;
  declare continue handler for not found return mini;
  loop
  fetch group next row;
  if mini > x then
    set mini = x;
  end if;
  end loop;
end|

delimiter ;|
select f1(sal) from t1 where id>= 1;
drop function f1;
delimiter |;

# aggregate XOR function

create aggregate function f1(x INT) returns double
begin
  declare z double default 0;
  declare continue handler for not found return z;
  loop
  fetch group next row;
  set z= z^x;
  end loop;
end|

delimiter ;|
select f1(sal) from t1 where id>= 1;
drop function f1;
delimiter |;

# aggregate SUM function

create aggregate function f1(x INT) returns INT
begin
  declare z int default 0;
  declare continue handler for not found return z;
  loop
  fetch group next row;
  set z= z+x;
  end loop;
end|

delimiter ;|
select f1(sal) from t1 where id>= 1;
delimiter |;


create aggregate function f2() returns INT
begin
  declare z double default 0;
  declare continue handler for not found return z;
  loop
    fetch group next row;
    set z= z+1;
  end loop;
end|

delimiter ;|

# no parameters
select f2() from t1;

create table t2 (sal int, id int);
INSERT INTO t2 (sal, id) VALUES (NULL, 1);
INSERT INTO t2 (sal, id) VALUES (2000, 1);
INSERT INTO t2 (sal, id) VALUES (3000, 1);

# null values
select f1(sal) from t2;

# no tables
select f1(1);

# aggregate function called from regular functions
create function f3() returns int
return (select f1(sal) from t1);
select f3();

create function f4() returns INT
return 1;

# regular functions called from aggregate functions
delimiter |;
create aggregate function f5() returns INT
begin
  declare z double default 0;
  declare continue handler for not found return z;
  loop
  fetch group next row;
  set z= z+f3();
  end loop;
end|

delimiter ;|
select f5() from t2;
delimiter |;

# aggregate functions called from aggregate functions
create aggregate function f6(x INT) returns INT
begin
  declare z int default 0;
  declare continue handler for not found return z;
  loop
    fetch group next row;
    if x then
      set z= z+(select f1(sal) from t1);
    end if;
  end loop;
end|

delimiter ;|
select f6(sal) from t2;

# GROUP BY AND ORDER BY
--sorted_result
select id, f1(sal) from t1 where id>= 1 group by id;
--sorted_result
select counter, f1(sal) from t1 where id>= 1 group by counter;
--sorted_result
select val, f1(sal) from t1 where id>= 1 group by val;
--sorted_result
select counter, f1(sal) from t1 where id>= 1 group by id order by counter;
--sorted_result
select counter, id, f1(sal), f1(sal) from t1 where id>= 1 group by id order by counter;
--sorted_result
select counter, id, f1(sal), sum(distinct sal) from t1 where id>= 1 group by id order by counter desc;


##### insert aggregate function value into a table ######
create table t3 (i int);
INSERT INTO t3 (i) select f1(sal) from t1;
select * from t3;

delimiter |;

create aggregate function f7(x INT) returns INT
begin
  declare z int default 0;
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE a,b,c  INT;
  DECLARE cur1 CURSOR FOR SELECT id FROM test.t2;
  declare continue handler for not found return z;

    outer_loop: LOOP
       FETCH GROUP NEXT ROW;
       set z= z+x;
         inner_block: begin
         DECLARE cur2 CURSOR FOR SELECT id FROM test.t2;
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
         OPEN cur2;

         read_loop: LOOP
          FETCH cur2 INTO a;
          IF done THEN
           CLOSE cur2;
           LEAVE read_loop;
          END IF;
         END LOOP read_loop;

      end inner_block;
    END LOOP outer_loop;

end|

delimiter ;|
select f7(sal) from t1;

drop table t1;
drop table t2;
drop table t3;
drop function f1;
drop function f2;
drop function f3;
drop function f4;
drop function f5;
drop function f6;
drop function f7;

delimiter |;
create aggregate function f1(x date) returns date
begin
  declare continue handler for not found return x;
  loop
    fetch group next row;
  end loop;
end|
delimiter ;|
select f1('2001-01-01'),cast(f1('2001-01-01') as time);
drop function f1;