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

SP variables do not return expected NULL in summary rows in ROLLUP queries

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • 10.4
    • Stored routines
    • None

    Description

      This script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (id INT);
      INSERT INTO t1 VALUES (1),(2);
      DROP PROCEDURE IF EXISTS p1;
      DELIMITER $$
      CREATE PROCEDURE p1()
      BEGIN
        DECLARE v INT DEFAULT 10;
        SELECT id, 10 AS n, COUNT(*) FROM t1 GROUP BY id, n WITH ROLLUP;
        SELECT id, v, COUNT(*) FROM t1 GROUP BY id, v WITH ROLLUP;
      END;
      $$
      DELIMITER ;
      CALL p1;
      

      produces the following output:

      +------+------+----------+
      | id   | n    | COUNT(*) |
      +------+------+----------+
      |    1 |   10 |        1 |
      |    1 | NULL |        1 |
      |    2 |   10 |        1 |
      |    2 | NULL |        1 |
      | NULL | NULL |        2 |
      +------+------+----------+
       
      +------+------+----------+
      | id   | v    | COUNT(*) |
      +------+------+----------+
      |    1 |   10 |        1 |
      |    1 |   10 |        1 |
      |    2 |   10 |        1 |
      |    2 |   10 |        1 |
      | NULL |   10 |        2 |
      +------+------+----------+
      

      Notice:

      • The first query correctly returns NULL in the column n for summary rows 2,4,5
      • The second query erroneously returns 10 in the same rows

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.