Group_concat with order by in procedure is showing the order by field value

Help Procedural SQL
1

Hello I am using the version 7.0.2 of memsql

We have a strange behavior using group_concat in a procedure

create table FRED(id bigint(19) primary key auto_increment, name varchar(10), value varchar(10), pkey char(3), is_unit char(1));

insert into FRED (name, value, pkey, is_unit) values
(‘abc’,‘val’,‘1234’,‘A’),
(‘abc’,‘u’,‘1234’,‘B’),
(‘def’,‘val1’,‘1234’,‘A’),
(‘def’,‘u1’,‘1234’,‘B’);

select name, group_concat(value order by is_unit asc separator ’ ’ ) as res from FRED
group by name,pkey;

this gives the expected result
name res

abc val u
def val1 u1

calling that in a procedure gives another result

delimiter //
create or replace procedure test_concat() returns query(name varchar(10), res varchar(20)) as
declare
q query(name varchar(10), res varchar(20))= select name, group_concat(value order by is_unit asc separator ’ ') as res from FRED group by name, pkey;
begin
return q;
end //

echo test_concat();

name res

abc valA uB
def val1A u1B

The is_unit is shown in the group_concat and it should not be

Thanks

2

It looks like you found a bug. I will file it.

Do you have a workaround?

3

Hi Hanson,

Yes we have a workaround , we are using Array of Records and managing concatenation with the help of FOR LOOPS.

Thanks

Frederic