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

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

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

            It worked in 5.1 and 5.2, but stopped working in 5.3.

            elenst Elena Stepanova added a comment - It worked in 5.1 and 5.2, but stopped working in 5.3.

            I think it is connected to nonsens of having ORDER BY in subquery without LIMIT clause.

            sanja Oleksandr Byelkin added a comment - I think it is connected to nonsens of having ORDER BY in subquery without LIMIT clause.

            Right, some ORDER BY are redundant. Perhaps, instead of returning an error, the optimizer could remove the redundant ORDER BY.

            bar Alexander Barkov added a comment - Right, some ORDER BY are redundant. Perhaps, instead of returning an error, the optimizer could remove the redundant ORDER BY.

            Ok to push.

            psergei Sergei Petrunia added a comment - Ok to push.
            cvicentiu Vicențiu Ciorbaru added a comment - Fixed with https://github.com/mariadb/server/commit/f675eab7dc7f1eb4f4b61bfdd548d9e8052678a4

            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.