|
With an aggregated function, the output results is consistently NULL when the input set is empty.
In this example, the call to aggregate_count(v) should be equivalent to COUNT(NULLIF(v, 1)).
DELIMITER //
|
-- Function created as per documentation:
|
-- https://mariadb.com/kb/en/stored-aggregate-functions/#examples
|
CREATE AGGREGATE FUNCTION aggregate_count(x INT) RETURNS INT
|
BEGIN
|
DECLARE count_students INT DEFAULT 0;
|
DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN count_students;
|
|
LOOP
|
FETCH GROUP NEXT ROW;
|
IF x THEN
|
SET count_students = count_students+1;
|
END IF;
|
END LOOP;
|
END //
|
|
-- Nominal case (no where clause)
|
WITH q(v) AS (
|
VALUES
|
(0),
|
(1),
|
(2)
|
)
|
SELECT COUNT(NULLIF(v, 1)), aggregate_count(v)
|
FROM q
|
//
|
|
-- Error case: no value are available in input
|
WITH q(v) AS (
|
VALUES
|
(0),
|
(1),
|
(2)
|
)
|
SELECT COUNT(NULLIF(v, 1)), aggregate_count(v)
|
FROM q
|
WHERE FALSE
|
//
|
|
DROP FUNCTION aggregate_count //
|
DELIMITER ;
|
|