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

[28 Feb 2013 20:43] Aurelien Marchand
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
[28 Feb 2013 20:45] Aurelien Marchand
One more step to reproduce the issue:
after creating the view, do:

SELECT * from v1;

This will show the error.
[1 Mar 2013 7:45] Valeriy Kravchuk
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)
[1 Mar 2013 13:19] MySQL Verification Team
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 >
[8 Mar 2018 23:44] Roy Lyseng
Posted by developer:
 
Fixed in 5.6.10 and later.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.