MySQL Bugs: #68529: GROUP_CONCAT(.... ORDER BY ...) prevents ORDER BY if using a VIEW

Bug #68529	GROUP_CONCAT(.... ORDER BY ...) prevents ORDER BY if using a VIEW
Submitted:	28 Feb 2013 20:43	Modified:	8 Mar 2018 23:44
Reporter:	Aurelien Marchand	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0/5.1/5.5	OS:	Linux (gentoo/i686)
Assigned to:		CPU Architecture:	Any
Tags:	GROUP_CONCAT ORDER BY VIEW

Description:
Beware a bug in MySQL; it took me a moment to identify that issue:

if using "GROUP_CONCAT(... ORDER BY ....) AS alias1" then you CANNOT finish the query with "GROUP BY .... ORDER BY field1, alias1" if using a VIEW. It will die with error #1250.

How to repeat:
CREATE TABLE t1 (id1 int);
CREATE TABLE t2 (id2 int, id1 int);

INSERT INTO t1 (1),(2),(3);
INSERT INTO t2 (1,1), (2,1), (3,2), (4,2), (5,3);

CREATE VIEW v1 AS 
SELECT
  t1.id1,
  group_concat(id2 order by id2) as grouped_ids
FROM
  t1 INNER JOIN
  t2 USING(id1)
GROUP BY
  id1
ORDER BY
  id1, grouped_ids

Suggested fix:
allow ordering even if there is ordering in the group_concat

One more step to reproduce the issue:
after creating the view, do:

SELECT * from v1;

This will show the error.

Please, check with a newer version, 5.0.91+ at least. This is what I see in 5.6.10 (after adding missing VALUES keyword):

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1 (id1 int);
Query OK, 0 rows affected (0.94 sec)

mysql> CREATE TABLE t2 (id2 int, id1 int);
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1,1), (2,1), (3,2), (4,2), (5,3);
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   t1.id1,
    ->   group_concat(id2 order by id2) as grouped_ids
    -> FROM
    ->   t1 INNER JOIN
    ->   t2 USING(id1)
    -> GROUP BY
    ->   id1
    -> ORDER BY
    ->   id1, grouped_ids;
+------+-------------+
| id1  | grouped_ids |
+------+-------------+
|    1 | 1,2         |
|    2 | 3,4         |
|    3 | 5           |
+------+-------------+
3 rows in set (0.20 sec)

mysql> create view v1 as
    -> SELECT
    ->   t1.id1,
    ->   group_concat(id2 order by id2) as grouped_ids
    -> FROM
    ->   t1 INNER JOIN
    ->   t2 USING(id1)
    -> GROUP BY
    ->   id1
    -> ORDER BY
    ->   id1, grouped_ids;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from v1;
+------+-------------+
| id1  | grouped_ids |
+------+-------------+
|    1 | 1,2         |
|    2 | 3,4         |
|    3 | 5           |
+------+-------------+
3 rows in set (0.03 sec)

Error 1250 presents on 5.0,5.1,5.5. On another hand 5.6.10 behaves how Valeriy commented.

d:\dbs>d:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

<cut>

mysql 5.0 >INSERT INTO t2 VALUES (1,1), (2,1), (3,2), (4,2), (5,3);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.0 >CREATE VIEW v1 AS
    -> SELECT
    ->   t1.id1,
    ->   group_concat(id2 order by id2) as grouped_ids
    -> FROM
    ->   t1 INNER JOIN
    ->   t2 USING(id1)
    -> GROUP BY
    ->   id1
    -> ORDER BY
    ->   id1, grouped_ids;
Query OK, 0 rows affected (0.03 sec)

mysql 5.0 > select * from v1;
ERROR 1250 (42000): Table 't2' from one of the SELECTs cannot be used in field list

d:\dbs>d:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.69-Win X64 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

<CUT>

mysql 5.1 > select * from v1;
ERROR 1250 (42000): Table 't2' from one of the SELECTs cannot be used in field list

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.31 Source distribution
<CUT>

mysql 5.5 > select * from v1;
ERROR 1250 (42000): Table 't2' from one of the SELECTs cannot be used in field list
mysql 5.5 >

Posted by developer:
 
Fixed in 5.6.10 and later.