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

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration