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

Not all columns in super-aggregate rows in a SELECT ... WITH ROLLUP are NULL

    XMLWordPrintable

Details

    Description

      When executing SELECT ... WITH ROLLUP with multiple SELECT columns identical to the GROUP BY column, only one (the first?) column is NULL in the super-aggregate row:

      CREATE TABLE t (a INT,b INT) ENGINE=INNODB;
      insert into t (a, b) select seq / 20, seq from seq_1_to_100;
      SELECT a, a, count(b) FROM t GROUP BY a WITH ROLLUP;
      a	a	count(b)
      0	0	9
      1	1	20
      2	2	20
      3	3	20
      4	4	20
      5	5	11
      NULL	5	100
      

      compare this with the following:

      MariaDB [test]> SELECT a,a+1, count(b) FROM t GROUP BY a WITH ROLLUP;
      +------+------+----------+
      | a    | a+1  | count(b) |
      +------+------+----------+
      |    0 |    1 |        9 |
      |    1 |    2 |       20 |
      |    2 |    3 |       20 |
      |    3 |    4 |       20 |
      |    4 |    5 |       20 |
      |    5 |    6 |       11 |
      | NULL | NULL |      100 |
      +------+------+----------+
      7 rows in set (0.005 sec)
      

      In the first example, the two a's are two separate Item_field's, whereas in the second example, the a in a + 1 is an Item_ref, presumably pointing to the first column a.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              ycp Yuchen Pei
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.