3

I am experiencing a strange issue with the GROUP_CONCAT function in my instance, where using it to group together integers works just fine, but applying it to a varchar doesn't work at all.

The data is along the lines of: 

CUSTOMERID  LOCATIONNAME    PRODUCTID   UNITS   ID  PRODUCTNAME
1           Location            65       100    1   Product 65
1           Location            66       100    2   Product 66
1           Location            67       100    3   Product 67
1           Location            68       100    4   Product 68
1           Location            68       100    5   Product 68

When I run: SELECT GROUP_CONCAT(DISTINCT lm.ProductID) as Brand FROM locationMap lm WHERE (lm.CustomerID = 1);

It returns 65, 66, 67, 68 and works properly.

When I run: SELECT GROUP_CONCAT(DISTINCT lm.ProductName) as Brand FROM locationMap lm WHERE (lm.CustomerID = 1);

It returns only the first item (Product 65).

I set up an SQL Fiddle (http://www.sqlfiddle.com/#!2/2392f/3) to test it out, and everything functions properly there. Can anyone offer a suggestion for what might be wrong in my environment that would cause this? I'm running 5.5.30-MariaDB-30.1

Mihai
  • 26,325
  • 7
  • 66
  • 81
dwilkinson
  • 51
  • 3
  • 2
    In [fiddle](http://www.sqlfiddle.com/#!2/2392f/6) it shows 4 products name ? – M Khalid Junaid Aug 24 '14 at 20:24
  • 1
    ProductName looks like a denormalization with that data. Hope that isn't the real data. – user207421 Aug 24 '14 at 20:26
  • This is just dummy data. Everything works properly in the fiddle, but in the actual environment it only returns one result in ProductName example. – dwilkinson Aug 24 '14 at 20:33
  • @dwilkinson when you run `show variables like '%group_concat_max_len%'` on your machine what value you see in value column ? – M Khalid Junaid Aug 24 '14 at 20:40
  • Apparently you have to `set group_concat_max_len=1024*1024*1024*4 - 1;` less than 4Gb https://mariadb.atlassian.net/browse/MDEV-4677 – Mihai Aug 24 '14 at 20:50
  • @m-khalid-junaid I should have included that info in the post. It is currently set to 1024 which should be ok. – dwilkinson Aug 24 '14 at 20:57
  • @mihai I read over that article earlier - I think that my settings should be reasonable there. It does indicate that there are some bugs in MariaDB regarding this issue though - but I am trying such a basic operation here, that it should really work. – dwilkinson Aug 24 '14 at 20:58
  • Maybe upgrade to the latest version,if you dont have it already. – Mihai Aug 24 '14 at 21:13
  • 1
    Could you download [MySQL 5.5.30](http://downloads.mysql.com/archives/community/) and see if MariaDB and MySQL exhibit the same issue? If your query works on MySQL 5.5.30, let MariaDB team know. On MariaDB, create a test table like the one in SQL Fiddle and see if your issue is repeatable (just to cross-check data) – zedfoxus Aug 25 '14 at 01:55
  • I just tried your queries with MariaDB 5.5.35 and it works fine as expected. (5.5.35-MariaDB-33.0) – Jaydee Aug 25 '14 at 14:43

1 Answers1

2

Verified that upgrading the server from 5.5.30-MariaDB-30.1 to 5.5.38-MariaDB-35.2 has resolved this issue. Thanks to everyone for their suggestions.

dwilkinson
  • 51
  • 3