Sorted group_concat in procedure

Help
1

GROUP_CONCAT (emp_id ORDER BY ord_id SEPARATOR’, ') is not working as expected in the procedure ?

2

Could you please provide a repro?

3

create table codes(code char(1), code_idx int(11));

insert into codes(code, code_idx) values
(‘r’,1),
(‘e’,2),
(‘a’,3),
(‘d’,4),
(‘s’,5);

select group_concat(code order by code_idx SEPARATOR ‘’) from codes ;

result:
reads

delimiter //

create or replace procedure example_proc() returns void AS

DECLARE

str query(a text) = select group_concat(code order by code_idx SEPARATOR ‘’) from codes ;
final_str text;

BEGIN

final_str = scalar(str);

echo select final_str as t_;

END ;
delimiter //

call example_proc();

result:
a3r1e2d4s5

Hope this helps!

4

This is a known bug which we are tracking internally.

The issue is specifically with using GROUP_CONCAT() with Query Type Variables inside Stored Procedure. SCALAR() and COLLECT() built-in use Query Type Variables behind the scenes.

We will let you know once we have released a version with fix for this issue included.

5

@Saksham Was this issue resolved? I tried the group_concat in TVF its not working, producing the same issue as mentioned above

6

It’s fixed on 7.0.23+ / 7.1.7+ / 7.3