Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2(EOL)
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
-
MDEV-8909 union parser cleanup
-
- Closed
-
Activity
Assignee | Alexander Barkov [ bar ] |
Labels | 10.2-rc |
Priority | Major [ 3 ] | Critical [ 2 ] |
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} |
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} |
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} |
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} |
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} |
Assignee | Alexander Barkov [ bar ] | Vicentiu Ciorbaru [ cvicentiu ] |
Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Sergei Petrunia [ psergey ] |
Assignee | Sergei Petrunia [ psergey ] | Vicentiu Ciorbaru [ cvicentiu ] |
Status | Open [ 1 ] | In Progress [ 3 ] |
Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Sergei Petrunia [ psergey ] |
Status | In Progress [ 3 ] | In Review [ 10002 ] |
Assignee | Sergei Petrunia [ psergey ] | Vicentiu Ciorbaru [ cvicentiu ] |
Status | In Review [ 10002 ] | Stalled [ 10000 ] |
issue.field.resolutiondate | 2017-02-14 05:51:15.0 | 2017-02-14 05:51:15.979 |
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 ] |
Workflow | MariaDB v3 [ 75774 ] | MariaDB v4 [ 150456 ] |