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

              varun Varun Gupta (Inactive)
              bar Alexander Barkov
              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.