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

            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value

            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.
            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}

            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.
            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}

            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.
            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 ]

            Ok to push.

            psergei Sergei Petrunia added a comment - Ok to push.
            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 ]
            cvicentiu Vicențiu Ciorbaru added a comment - Fixed with https://github.com/mariadb/server/commit/f675eab7dc7f1eb4f4b61bfdd548d9e8052678a4
            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.