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

MariaDB does not support group functions in some contexts where MySQL does

    XMLWordPrintable

Details

    Description

      I create a table like this:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (10),(20),(30);

      These queries normally return results:

      (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a));
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) LIMIT 1;
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a));
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) LIMIT 1;

      These queries return the "Invalid use of group function" error:

      (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a;
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a LIMIT 1;
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) ORDER BY a;
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) ORDER BY a LIMIT 1;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a));
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) LIMIT 1;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY GROUP_CONCAT(a)) ORDER BY a LIMIT 1;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a));
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) LIMIT 1;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) ORDER BY a;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(a)) ORDER BY a LIMIT 1;

      In MySQL-5.7 all above queries work without errors.

      The same problem is repeatable with MAX instead of GROUP_CONCAT:
      These queries work in both MariaDB and MySQL:

      (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a));
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) LIMIT 1;
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a));
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) LIMIT 1;

      These queries fail in MariaDB, but work in MySQL:

      (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) ORDER BY a;
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) ORDER BY a LIMIT 1;
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) ORDER BY a;
      		 
      (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) ORDER BY a LIMIT 1;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a));
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) LIMIT 1;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) ORDER BY a;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY MAX(a)) ORDER BY a LIMIT 1;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a));
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) LIMIT 1;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) ORDER BY a;
      		 
      SELECT 1 AS a UNION (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(a)) ORDER BY a LIMIT 1;

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-8909 union parser cleanup

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            People

              cvicentiu Vicențiu Ciorbaru
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.