Description:
bash-2.05# mysql --version
mysql Ver 13.5 Distrib 4.1.0-alpha, for sun-solaris2.9 (sparc)
When I try to "order by" a select that uses "GROUP_CONCAT" I recive as return an empty column.
Check the "How to repeat".
Many Thanks for your help,
Best Regards,
Curzio
How to repeat:
Description of the 3 involved tables:
CREATE TABLE Contacts (
cont_id INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
loc_id INT UNSIGNED NOT NULL REFERENCES Locations(loc_id),
firstname CHAR(32) NOT NULL,
lastname CHAR(32),
email CHAR(64) UNIQUE NOT NULL,
PRIMARY KEY (cont_id)
);
CREATE TABLE Tasks (
task_id INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
t_name CHAR(32) UNIQUE NOT NULL,
description TEXT,
template TEXT,
PRIMARY KEY (task_id)
);
CREATE TABLE Assignments (
a_cont_id INT UNSIGNED NOT NULL REFERENCES Contacts(cont_id),
a_task_id INT UNSIGNED NOT NULL REFERENCES Tasks(task_id),
PRIMARY KEY (a_cont_id,a_task_id)
);
-- some data for testing:
INSERT INTO Contacts VALUES (1,1,'cont 1','cont 1','cont 1'),
(2,1,'cont2','cont2','cont2'),(3,2,'cont 3','act','act'),
(4,1,'cont 4','cont 4','cont 4'),(5,3,'cont 5','cont 5','cont 6'),
(6,4,'Richi','gfdgdf','gfdgdfcom'),(7,3,'ouio','ouio','oiuiou'),
(8,5,'Michael','Tdfgarda','mgdfgfdsun'),(9,5,'fdsfs','fsdfs','dfsdfsdf'),
(10,6,'fsdfsd','fsd','fsfds'),(11,7,'fdsfs','fdsfsd','fsdfsd'),
(12,7,'fsdf','sfsdfds','fdsfsdf');
INSERT INTO Tasks VALUES
(1,'Task 1','Task 1\r\nTizuizu','Task 1\r\nTask 1\r\nTask 1\r\n7zu'),
(2,'Task 2','Task 2\r\nTask 2','Task 2\r\nTask 2\r\nTask 2\r\n'),
(4,'Task 4','Task 4\r\nTask 4','Task 4\r\nTask 4\r\nTask 4gaasdasdas'),
(5,'Task 5','Task 5\r\nTask 5','Task 5\r\nTask 5\r\nTask 556\r\n45646'),
(6,'Task 6','Task 6\r\nasda','Task 6\r\nTask 6\r\nTask 6gfhfgh');
INSERT INTO Assignments VALUES (1,1),(1,5),(2,2),(3,4),(3,5),(4,1),(4,4),(4,6),
(5,2),(5,4),(5,6),(6,2),(6,4),(7,1),(7,4),(8,1),(8,5),(8,6),(9,5),(9,6),(10,1),
(10,2),(10,4),(10,5),(11,5),(11,6),(12,5);
---------------
The query witout the "order by" (works fine):
mysql> select firstname,cont_id,GROUP_CONCAT(t_name separator ',') as task_list from Contacts,Assignments,Tasks where (cont_id=a_cont_id and a_task_id=task_id) group by cont_id ;
+-----------+---------+-----------------------------+
| firstname | cont_id | task_list |
+-----------+---------+-----------------------------+
| cont 1 | 1 | Task 1,Task 5 |
| cont2 | 2 | Task 2 |
| cont 3 | 3 | Task 4,Task 5 |
| cont 4 | 4 | Task 1,Task 4,Task 6 |
| cont 5 | 5 | Task 2,Task 4,Task 6 |
| Richi | 6 | Task 2,Task 4 |
| ouio | 7 | Task 1,Task 4 |
| Michael | 8 | Task 1,Task 5,Task 6 |
| fdsfs | 9 | Task 5,Task 6 |
| fsdfsd | 10 | Task 1,Task 2,Task 4,Task 5 |
| fdsfs | 11 | Task 5,Task 6 |
| fsdf | 12 | Task 5 |
+-----------+---------+-----------------------------+
12 rows in set (0.01 sec)
The same select as before, but with "order by firstname":
mysql> select firstname,cont_id,GROUP_CONCAT(t_name separator ',') as task_list from Contacts,Assignments,Tasks where (cont_id=a_cont_id and a_task_id=task_id) group by cont_id order by firstname;
+-----------+---------+-----------+
| firstname | cont_id | task_list |
+-----------+---------+-----------+
| cont 1 | 1 | |
| cont 3 | 3 | |
| cont 4 | 4 | |
| cont 5 | 5 | |
| cont2 | 2 | |
| fdsfs | 9 | |
| fdsfs | 11 | |
| fsdf | 12 | |
| fsdfsd | 10 | |
| Michael | 8 | |
| ouio | 7 | |
| Richi | 6 | |
+-----------+---------+-----------+
12 rows in set (0.00 sec)
mysql>
I don't know if it's the same problem, but this doesen't works too:
mysql> select * from (select firstname,cont_id,GROUP_CONCAT(t_name separator ',') as task_list from Contacts,Assignments,Tasks where (cont_id=a_cont_id and a_task_id=task_id) group by cont_id) as t;
+-----------+---------+-----------+
| firstname | cont_id | task_list |
+-----------+---------+-----------+
| cont 1 | 1 | |
| cont2 | 2 | |
| cont 3 | 3 | |
| cont 4 | 4 | |
| cont 5 | 5 | |
| Richi | 6 | |
| ouio | 7 | |
| Michael | 8 | |
| fdsfs | 9 | |
| fsdfsd | 10 | |
| fdsfs | 11 | |
| fsdf | 12 | |
+-----------+---------+-----------+
12 rows in set (0.01 sec)
Suggested fix:
No idea.
Description: bash-2.05# mysql --version mysql Ver 13.5 Distrib 4.1.0-alpha, for sun-solaris2.9 (sparc) When I try to "order by" a select that uses "GROUP_CONCAT" I recive as return an empty column. Check the "How to repeat". Many Thanks for your help, Best Regards, Curzio How to repeat: Description of the 3 involved tables: CREATE TABLE Contacts ( cont_id INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT, loc_id INT UNSIGNED NOT NULL REFERENCES Locations(loc_id), firstname CHAR(32) NOT NULL, lastname CHAR(32), email CHAR(64) UNIQUE NOT NULL, PRIMARY KEY (cont_id) ); CREATE TABLE Tasks ( task_id INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT, t_name CHAR(32) UNIQUE NOT NULL, description TEXT, template TEXT, PRIMARY KEY (task_id) ); CREATE TABLE Assignments ( a_cont_id INT UNSIGNED NOT NULL REFERENCES Contacts(cont_id), a_task_id INT UNSIGNED NOT NULL REFERENCES Tasks(task_id), PRIMARY KEY (a_cont_id,a_task_id) ); -- some data for testing: INSERT INTO Contacts VALUES (1,1,'cont 1','cont 1','cont 1'), (2,1,'cont2','cont2','cont2'),(3,2,'cont 3','act','act'), (4,1,'cont 4','cont 4','cont 4'),(5,3,'cont 5','cont 5','cont 6'), (6,4,'Richi','gfdgdf','gfdgdfcom'),(7,3,'ouio','ouio','oiuiou'), (8,5,'Michael','Tdfgarda','mgdfgfdsun'),(9,5,'fdsfs','fsdfs','dfsdfsdf'), (10,6,'fsdfsd','fsd','fsfds'),(11,7,'fdsfs','fdsfsd','fsdfsd'), (12,7,'fsdf','sfsdfds','fdsfsdf'); INSERT INTO Tasks VALUES (1,'Task 1','Task 1\r\nTizuizu','Task 1\r\nTask 1\r\nTask 1\r\n7zu'), (2,'Task 2','Task 2\r\nTask 2','Task 2\r\nTask 2\r\nTask 2\r\n'), (4,'Task 4','Task 4\r\nTask 4','Task 4\r\nTask 4\r\nTask 4gaasdasdas'), (5,'Task 5','Task 5\r\nTask 5','Task 5\r\nTask 5\r\nTask 556\r\n45646'), (6,'Task 6','Task 6\r\nasda','Task 6\r\nTask 6\r\nTask 6gfhfgh'); INSERT INTO Assignments VALUES (1,1),(1,5),(2,2),(3,4),(3,5),(4,1),(4,4),(4,6), (5,2),(5,4),(5,6),(6,2),(6,4),(7,1),(7,4),(8,1),(8,5),(8,6),(9,5),(9,6),(10,1), (10,2),(10,4),(10,5),(11,5),(11,6),(12,5); --------------- The query witout the "order by" (works fine): mysql> select firstname,cont_id,GROUP_CONCAT(t_name separator ',') as task_list from Contacts,Assignments,Tasks where (cont_id=a_cont_id and a_task_id=task_id) group by cont_id ; +-----------+---------+-----------------------------+ | firstname | cont_id | task_list | +-----------+---------+-----------------------------+ | cont 1 | 1 | Task 1,Task 5 | | cont2 | 2 | Task 2 | | cont 3 | 3 | Task 4,Task 5 | | cont 4 | 4 | Task 1,Task 4,Task 6 | | cont 5 | 5 | Task 2,Task 4,Task 6 | | Richi | 6 | Task 2,Task 4 | | ouio | 7 | Task 1,Task 4 | | Michael | 8 | Task 1,Task 5,Task 6 | | fdsfs | 9 | Task 5,Task 6 | | fsdfsd | 10 | Task 1,Task 2,Task 4,Task 5 | | fdsfs | 11 | Task 5,Task 6 | | fsdf | 12 | Task 5 | +-----------+---------+-----------------------------+ 12 rows in set (0.01 sec) The same select as before, but with "order by firstname": mysql> select firstname,cont_id,GROUP_CONCAT(t_name separator ',') as task_list from Contacts,Assignments,Tasks where (cont_id=a_cont_id and a_task_id=task_id) group by cont_id order by firstname; +-----------+---------+-----------+ | firstname | cont_id | task_list | +-----------+---------+-----------+ | cont 1 | 1 | | | cont 3 | 3 | | | cont 4 | 4 | | | cont 5 | 5 | | | cont2 | 2 | | | fdsfs | 9 | | | fdsfs | 11 | | | fsdf | 12 | | | fsdfsd | 10 | | | Michael | 8 | | | ouio | 7 | | | Richi | 6 | | +-----------+---------+-----------+ 12 rows in set (0.00 sec) mysql> I don't know if it's the same problem, but this doesen't works too: mysql> select * from (select firstname,cont_id,GROUP_CONCAT(t_name separator ',') as task_list from Contacts,Assignments,Tasks where (cont_id=a_cont_id and a_task_id=task_id) group by cont_id) as t; +-----------+---------+-----------+ | firstname | cont_id | task_list | +-----------+---------+-----------+ | cont 1 | 1 | | | cont2 | 2 | | | cont 3 | 3 | | | cont 4 | 4 | | | cont 5 | 5 | | | Richi | 6 | | | ouio | 7 | | | Michael | 8 | | | fdsfs | 9 | | | fsdfsd | 10 | | | fdsfs | 11 | | | fsdf | 12 | | +-----------+---------+-----------+ 12 rows in set (0.01 sec) Suggested fix: No idea.