Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24580

Aggregate stored function return always NULL in case of empty input set

    XMLWordPrintable

    Details

      Description

      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 ;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              proust Christian Proust
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration