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

Wrong result of UNION .. ORDER BY GROUP_CONCAT()

    XMLWordPrintable

    Details

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT t1 VALUES (1),(2),(3); 
      SELECT 1 AS a UNION SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY GROUP_CONCAT(a ORDER BY 1);
      

      returns this result:

      +------+
      | a    |
      +------+
      |    1 |
      +------+
      

      This is wrong. The right part of the union alone:

      SELECT * FROM t1 GROUP BY a WITH ROLLUP;
      

      returns

      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      | NULL |
      +------+
      

      The result of a UNION can't return less rows than UNION parts alone.

      The same problem is repeatable with other aggregate functions:

      SELECT 1 AS a UNION SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY AVG(a);
      SELECT 1 AS a UNION SELECT * FROM t1 GROUP BY a WITH ROLLUP ORDER BY SUM(a);
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: