Comments - Stored Aggregate Functions

In attempting to short circuit some custom aggregate functions I discovered that custom aggregate functions must loop through entire cursor set to avoid erroneous results.

Here are 2 examples of trivial aggregate functions that return the "first" item in the aggregate cursor.

The first one (aggFirst_int) returns results as expected, storing the first item in the list, then looping through the cursor until the NOT FOUND HANDLER returns the result.

The second example does the same, but does not loop through the cursor until the NOT FOUND HANDLER is triggered, and returns the result immediately.

<<CODE>>
DELIMITER //

CREATE OR REPLACE DEFINER=`root`@`localhost` AGGREGATE FUNCTION `aggFirst_int`(`reqInt` INT) RETURNS int(11)
BEGIN
	DECLARE CONTINUE HANDLER FOR NOT FOUND
 		RETURN @iRet;
	FETCH GROUP NEXT ROW;
	SET @iRet = reqInt;
 	WHILE TRUE DO	
		FETCH GROUP NEXT ROW;
	END WHILE;
	RETURN -1;
END //

CREATE OR REPLACE DEFINER=`root`@`localhost` AGGREGATE FUNCTION `aggFirst_int2`(`reqInt` INT) RETURNS int(11)
BEGIN
	DECLARE CONTINUE HANDLER FOR NOT FOUND
 		RETURN @iRet;
	FETCH GROUP NEXT ROW;
	SET @iRet = reqInt;
	RETURN @iRet;
END //

DELIMITER ;

SELECT MAX(fInt), MIN(fInt), COUNT(fInt), aggFirst_int(fInt), aggFirst_int2(fInt), fChar
	FROM (SELECT "a" AS fChar, 1 AS fInt
		UNION SELECT "b", 4 
		UNION SELECT "b", 2 
		UNION SELECT "c", 3) AS t
	GROUP BY fChar;

<</CODE>>

This produced the following results:

<<CODE>>
MAX(fInt), MIN(fInt), COUNT(fInt), aggFirst_int(fInt), aggFirst_int2(fInt), fChar
1, 	1, 	1, 	1, 	4, 	a
4, 	2, 	2, 	2, 	3, 	b
3, 	3, 	1, 	3, 	3, 	c
<</CODE>>

The built in aggregate functions all return the expected results, as does aggFirst_int(), but aggFirst_int2() returns results that were clearly processed from other rows.