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

          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

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Assignee Alexander Barkov [ bar ]
            elenst Elena Stepanova made changes -
            Labels 10.2-rc
            bar Alexander Barkov made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            Description MySQL-5.7 executes this script without errors:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(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;
            DROP TABLE t1;
            {code}

            MariaDB returns "Invalid use of group function" for all SELECT queries.
            		MySQL-5.7 executes this script without errors:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(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;
            DROP TABLE t1;
            {code}

            MariaDB returns "Invalid use of group function" for all SELECT queries.

            The same problem is repeatable with {{MAX}} instead of {{GROUP_CONCAT}}.
            All {{SELECT}} queries below work fine in {{MySQL-8.0.0}}, but most of them fail in {{MariaDB}}:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(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;
            DROP TABLE t1;
            {code}
            bar Alexander Barkov made changes -
            Description MySQL-5.7 executes this script without errors:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,GROUP_CONCAT(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;
            DROP TABLE t1;
            {code}

            MariaDB returns "Invalid use of group function" for all SELECT queries.

            The same problem is repeatable with {{MAX}} instead of {{GROUP_CONCAT}}.
            All {{SELECT}} queries below work fine in {{MySQL-8.0.0}}, but most of them fail in {{MariaDB}}:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(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;
            DROP TABLE t1;
            {code}
            		I create a table like this:

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            {code}

            {code:sql}

            {code}
            MySQL-5.7 executes this script without errors:

            MariaDB returns "Invalid use of group function" for all SELECT queries.

            The same problem is repeatable with {{MAX}} instead of {{GROUP_CONCAT}}.
            All {{SELECT}} queries below work fine in {{MySQL-8.0.0}}, but most of them fail in {{MariaDB}}:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(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;
            DROP TABLE t1;
            {code}
            bar Alexander Barkov made changes -
            Description I create a table like this:

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            {code}

            {code:sql}

            {code}
            MySQL-5.7 executes this script without errors:

            MariaDB returns "Invalid use of group function" for all SELECT queries.

            The same problem is repeatable with {{MAX}} instead of {{GROUP_CONCAT}}.
            All {{SELECT}} queries below work fine in {{MySQL-8.0.0}}, but most of them fail in {{MariaDB}}:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(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;
            DROP TABLE t1;
            {code}
            		I create a table like this:

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            {code}

            These queries normally return results:
            {code:sql}
            (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;
            {code}

            These queries return the "Invalid use of group function" error:
            {code:sql}
            (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;
            {code}
            In MySQL-5.7 all above queries work without errors.


            The same problem is repeatable with {{MAX}} instead of {{GROUP_CONCAT}}.
            All {{SELECT}} queries below work fine in {{MySQL-8.0.0}}, but most of them fail in {{MariaDB}}:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(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;
            DROP TABLE t1;
            {code}
            bar Alexander Barkov made changes -
            Description I create a table like this:

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            {code}

            These queries normally return results:
            {code:sql}
            (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;
            {code}

            These queries return the "Invalid use of group function" error:
            {code:sql}
            (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;
            {code}
            In MySQL-5.7 all above queries work without errors.


            The same problem is repeatable with {{MAX}} instead of {{GROUP_CONCAT}}.
            All {{SELECT}} queries below work fine in {{MySQL-8.0.0}}, but most of them fail in {{MariaDB}}:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            (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 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));
            (SELECT a FROM t1 GROUP BY a ORDER BY a,MAX(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;
            DROP TABLE t1;
            {code}
            		I create a table like this:

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            {code}

            These queries normally return results:
            {code:sql}
            (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;
            {code}

            These queries return the "Invalid use of group function" error:
            {code:sql}
            (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;
            {code}
            In MySQL-5.7 all above queries work without errors.


            These queries work in both {{MariaDB}} and {{MySQL}}:
            {code:sql}
            (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;
            {code}

            These queries fail in {{MariaDB}}, but work in {{MySQL}}:
            {code:sql}
            (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;
            {code}
            bar Alexander Barkov made changes -
            Description I create a table like this:

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            {code}

            These queries normally return results:
            {code:sql}
            (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;
            {code}

            These queries return the "Invalid use of group function" error:
            {code:sql}
            (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;
            {code}
            In MySQL-5.7 all above queries work without errors.


            These queries work in both {{MariaDB}} and {{MySQL}}:
            {code:sql}
            (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;
            {code}

            These queries fail in {{MariaDB}}, but work in {{MySQL}}:
            {code:sql}
            (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;
            {code}
            		I create a table like this:

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10),(20),(30);
            {code}

            These queries normally return results:
            {code:sql}
            (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;
            {code}

            These queries return the "Invalid use of group function" error:
            {code:sql}
            (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;
            {code}
            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}}:
            {code:sql}
            (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;
            {code}

            These queries fail in {{MariaDB}}, but work in {{MySQL}}:
            {code:sql}
            (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;
            {code}
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Vicentiu Ciorbaru [ cvicentiu ]
            cvicentiu Vicențiu Ciorbaru made changes -
            Assignee Vicentiu Ciorbaru [ cvicentiu ] Sergei Petrunia [ psergey ]
            cvicentiu Vicențiu Ciorbaru made changes -
            Assignee Sergei Petrunia [ psergey ] Vicentiu Ciorbaru [ cvicentiu ]
            cvicentiu Vicențiu Ciorbaru made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            cvicentiu Vicențiu Ciorbaru made changes -
            Assignee Vicentiu Ciorbaru [ cvicentiu ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Vicentiu Ciorbaru [ cvicentiu ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            cvicentiu Vicențiu Ciorbaru made changes -
            issue.field.resolutiondate 2017-02-14 05:51:15.0 2017-02-14 05:51:15.979
            cvicentiu Vicențiu Ciorbaru made changes -
            Component/s Optimizer [ 10200 ]
            Fix Version/s 10.2.4 [ 22116 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 75774 ] MariaDB v4 [ 150456 ]

            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.