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

IFNULL() does not work well WITH ROLLUP

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.0, 10.1
    • N/A
    • OTHER
    • None

    Description

      Look at this test case:

      MariaDB [test]> CREATE OR REPLACE TABLE t (a INT, b INT) ENGINE = InnoDB;
      Query OK, 0 rows affected (0.34 sec)
       
      MariaDB [test]> INSERT INTO t VALUES
          ->         (1, 1),
          ->         (1, 2),
          ->         (2, 1),
          ->         (NULL, 1);
      Query OK, 4 rows affected (0.09 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> SELECT a, COUNT(*)
          ->         FROM t
          ->         GROUP BY a
          ->         WITH ROLLUP;
      +------+----------+
      | a    | COUNT(*) |
      +------+----------+
      | NULL |        1 |
      |    1 |        2 |
      |    2 |        1 |
      | NULL |        4 |
      +------+----------+
      4 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT IFNULL(a, 1), COUNT(*)
          ->         FROM t
          ->         GROUP BY a
          ->         WITH ROLLUP;
      +--------------+----------+
      | IFNULL(a, 1) | COUNT(*) |
      +--------------+----------+
      |            1 |        1 |
      |            1 |        2 |
      |            2 |        1 |
      |            1 |        4 |
      +--------------+----------+
      4 rows in set (0.00 sec)

      The first result is what one expects, the second result is not. I'm not sure if it is a bug or an expected behavior, but it makes the query useless.

      Since WITH ROLLUP adds rows with NULL, the IFNULL() function in the SELECT clause should be applied after WITH ROLLUP.

      Attachments

        Activity

          People

            Unassigned Unassigned
            f_razzoli Federico Razzoli
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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