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: | |
| 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
[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.