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

Wrong result with GROUP BY ... WITH ROLLUP

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 5.5.28a
    • None
    • None
    • None
    • Windows server 2003, 64-bit

    Description

      The following query returns different result between MYSQL and MARIADB (5.5.28):

      CREATE TABLE t(a int,b int,c int, amount1 int, amount2 int);
      INSERT INTO t (a, b, c, amount1, amount2) values (1,1,1,1,1);
      INSERT INTO t (a, b, c, amount1, amount2) values (2,2,2,2,1);
      INSERT INTO t (a, b, c, amount1, amount2) values (2,3,3,3,1);
       
      SELECT *,
          IF(`t_reorder`.`b` IS NULL, @ccount1:=amount1, @ccount1) AS `a_amount1`,
          IF(`t_reorder`.`b` IS NULL, @dcount1:=amount2, @dcount1) AS `a_amount2`
      FROM (
      	SELECT *
      		FROM (
        		SELECT `a`, `b`,`c`,
      			SUM(`amount1`) AS `amount1`, SUM(`amount2`) AS `amount2`
      		FROM `t`
      		WHERE (c IN (1,2,3)) AND ((`a` IS NOT NULL) AND (`b` IS NOT NULL))
      		GROUP BY `a`, `b` WITH ROLLUP) `t_rollup`
      	ORDER BY `a`, `b`
      )`t_reorder`;
       
      mysql.exe  Ver 14.14 Distrib 5.5.28, for Win32 (x86) 
      (Correct)
      +------+------+------+---------+---------+-----------+-----------+
      | a    | b    | c    | amount1 | amount2 | a_amount1 | a_amount2 |
      +------+------+------+---------+---------+-----------+-----------+
      | NULL | NULL |    3 |       6 |       3 |         6 |         3 |
      |    1 | NULL |    1 |       1 |       1 |         1 |         1 |
      |    1 |    1 |    1 |       1 |       1 |         1 |         1 |
      |    2 | NULL |    3 |       5 |       2 |         5 |         2 |
      |    2 |    2 |    2 |       2 |       1 |         5 |         2 |
      |    2 |    3 |    3 |       3 |       1 |         5 |         2 |
      +------+------+------+---------+---------+-----------+-----------+
       
      mysql.exe  Ver 15.1 Distrib 5.5.28-MariaDB, for Win32 (x86)
      (Wrong values on a_amount1 and a_amount2)
      ORDER BY `a`, `b` does not have any effect to this qyery
      +------+------+------+---------+---------+-----------+-----------+
      | a    | b    | c    | amount1 | amount2 | a_amount1 | a_amount2 |
      +------+------+------+---------+---------+-----------+-----------+
      |    1 |    1 |    1 |       1 |       1 |         6 |         3 |
      |    1 | NULL |    1 |       1 |       1 |         1 |         1 |
      |    2 |    2 |    2 |       2 |       1 |         1 |         1 |
      |    2 |    3 |    3 |       3 |       1 |         1 |         1 |
      |    2 | NULL |    3 |       5 |       2 |         5 |         2 |
      | NULL | NULL |    3 |       6 |       3 |         6 |         3 |
      +------+------+------+---------+---------+-----------+-----------+

      MariaDB seems to ignore ORDER BY statement after subquery having GROUP BY ... WITH ROLLUP.

      Optimizer_switch settings as per default.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              mitrpaka P Ka
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.