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

Cursor fetches NULL for aggregate functions

    XMLWordPrintable

    Details

      Description

      This script:

      DROP TABLE IF EXISTS t1;
      DROP PROCEDURE IF EXISTS p1;
      CREATE TABLE t1 (name VARCHAR(10), value INT);
      INSERT INTO t1 VALUES ('a',1);
      INSERT INTO t1 VALUES ('a',1);
      INSERT INTO t1 VALUES ('a',1);
      INSERT INTO t1 VALUES ('b',1);
      INSERT INTO t1 VALUES ('b',1);
      INSERT INTO t1 VALUES ('c',1);
      DELIMITER $$
      CREATE PROCEDURE p1 ()
      BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE v_name VARCHAR(10);
        DECLARE v_total INT;
        DECLARE c CURSOR FOR SELECT name, SUM(value) AS total FROM t1 GROUP BY name;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        OPEN c;
      read_loop:
        LOOP
          FETCH c INTO v_name, v_total;
          IF done THEN
            LEAVE read_loop;
          END IF;
          SELECT v_name, v_total;
        END LOOP;
        CLOSE c;
      END;
      $$
      DELIMITER ;
      CALL p1();
      DROP PROCEDURE p1;
      DROP TABLE t1;
      

      correctly returns these results in 10.1:

      +--------+---------+
      | v_name | v_total |
      +--------+---------+
      | a      |       3 |
      +--------+---------+
      1 row in set (0.00 sec)
       
      +--------+---------+
      | v_name | v_total |
      +--------+---------+
      | b      |       2 |
      +--------+---------+
      1 row in set (0.00 sec)
       
      +--------+---------+
      | v_name | v_total |
      +--------+---------+
      | c      |       1 |
      +--------+---------+
      1 row in set (0.00 sec)
      

      In 10.2 the same script returns wrong results:

      +--------+---------+
      | v_name | v_total |
      +--------+---------+
      | a      |    NULL |
      +--------+---------+
      1 row in set (0.00 sec)
       
      +--------+---------+
      | v_name | v_total |
      +--------+---------+
      | b      |    NULL |
      +--------+---------+
      1 row in set (0.00 sec)
       
      +--------+---------+
      | v_name | v_total |
      +--------+---------+
      | c      |    NULL |
      +--------+---------+
      1 row in set (0.00 sec)
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: