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

Wrong result with GROUP BY ... WITH ROLLUP

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 5.5.28a
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      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

              • Assignee:
                Unassigned
                Reporter:
                mitrpaka P Ka
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: