Bug with GROUP_CONCAT

thomas_w · Post by **thomas_w** » Sun 10 Apr 2011 11:55

devart Studio show wrong result using GROUP_CONCAT

CREATE TABLE obst (
 anzahl INT NOT NULL,
 art VARCHAR(20) NOT NULL,
 saison VARCHAR(20) NOT NULL
);

INSERT INTO obst VALUES
(1, 'Apfel', 'Sommer'),
(2, 'Apfel', 'Winter'),
(3, 'Bananen', 'Sommer'),
(4, 'Bananen', 'Winter'),
(5, 'Birnen', 'Sommer'),
(6, 'Birnen', 'Winter');

devart Professional Editon 4.50.339

SELECT saison, GROUP_CONCAT( art,  ';', anzahl  ORDER BY art SEPARATOR ';') AS summen 
 FROM obst
GROUP BY saison;
=======================================================================================================
|                      saison                      |                      summen                      |
=======================================================================================================
|                      Sommer                      | 41 70 66 65 6C 3B 31 3B 42 61 6E 61 6E 65 6E 3B  |
|                                                  |          33 3B 42 69 72 6E 65 6E 3B 35           |
-------------------------------------------------------------------------------------------------------
|                      Winter                      | 41 70 66 65 6C 3B 32 3B 42 61 6E 61 6E 65 6E 3B  |
|                                                  |          34 3B 42 69 72 6E 65 6E 3B 36           |
-------------------------------------------------------------------------------------------------------


SELECT saison, GROUP_CONCAT( art,  ';', anzahl  ORDER BY art SEPARATOR ';') AS summen 
 FROM obst
GROUP BY saison;
+--------+----------------------------+
| saison | summen                     |
+--------+----------------------------+
| Sommer | Apfel;1;Bananen;3;Birnen;5 |
| Winter | Apfel;2;Bananen;4;Birnen;6 |
+--------+----------------------------+
2 rows in set (0.00 sec)

mysql>

with kind regards,
Thomas

Alexz · Post by **Alexz** » Mon 11 Apr 2011 07:14

You should use Data Viewer to view the results in this case. This tool window shows the data from cells that have large values or binary data (for example, BLOB). GROUP_CONCAT returns BLOB in this case because different types of values are used in this clause.

If you want to view the value as you wrote, you should use the CONVERT function. For example:

Code: Select all

SELECT
  saison,
  CONVERT(GROUP_CONCAT( art,  ';', anzahl  ORDER BY art SEPARATOR ';') USING utf8) AS summen
FROM
  obst
GROUP BY
  saison;

thomas_w · Post by **thomas_w** » Mon 11 Apr 2011 16:34

Uups, I have to change my Query to get the correct result? Sorry, but that is misfit and not handsome. Maybe you find some other solution.

with kind regards,
Thomas

Alexz · Post by **Alexz** » Tue 12 Apr 2011 10:41

Unfortunately, the 'GROUP_CONCAT' function returns BLOB, as I wrote. So, if you want to have a text representation of this value, you should change your query. By the way, we have another suggestion concerning this query modification:

Code: Select all

SELECT 
  saison,
  GROUP_CONCAT( art, ';', CONVERT(anzahl USING UTF8) ORDER BY art SEPARATOR ';') AS summen 
FROM obst 
GROUP BY saison;

Also we decided to implement a small feature for the Data Export tool. For such columns, you will be able to choose either format 'String' or 'HEX String' (at the Options page of the Data Export wizard). This feature will be available in dbForge Studio for MySQL v5.00.
If you choose 'String', the value will be exported as in the MySQL console output. But, if you choose 'HEX String', the value will be exported as in the current version of the product (as you showed in the first post of this topic).

We'll inform you when the new build is available for download.

Alexz · Post by **Alexz** » Thu 21 Apr 2011 07:30

dbForge Studio for MySQL v5.0 is available for download.

thomas_w · Post by **thomas_w** » Fri 22 Apr 2011 14:48

Perfekt! 5.0 work well...

Code: Select all

SELECT saison
     , group_concat(art, ';', anzahl ORDER BY art SEPARATOR ';') AS summen
FROM
  obst
GROUP BY
  saison; 


=======================================================================================================
|                      saison                      |                      summen                      |
=======================================================================================================
|                      Sommer                      |            Apfel;1;Bananen;3;Birnen;5            |
-------------------------------------------------------------------------------------------------------
|                      Winter                      |            Apfel;2;Bananen;4;Birnen;6            |
-------------------------------------------------------------------------------------------------------

with kind regards,
Thomas

thomas_w · Post by **thomas_w** » Wed 01 Jun 2011 17:05

This problem is back again (5.0.33)

Code: Select all

SELECT saison
     , group_concat(art, ';', anzahl ORDER BY art SEPARATOR ';') AS summen
FROM
  obst
GROUP BY
  saison;

Export Result is correct:

Code: Select all

=======================================================================================================
|                      saison                      |                      summen                      |
=======================================================================================================
|                      Sommer                      |            Apfel;1;Bananen;3;Birnen;5            |
-------------------------------------------------------------------------------------------------------
|                      Winter                      |            Apfel;2;Bananen;4;Birnen;6            |
-------------------------------------------------------------------------------------------------------

but dbForge Result-Grid show some hex result like my first message.

with kind regards,
Thomas

Alexz · Post by **Alexz** » Thu 02 Jun 2011 07:50

thomas_w wrote:This problem is back again (5.0.33)
Export Result is correct:
but dbForge Result-Grid show some hex result like my first message.

As we wrote before, we added the 'HEX string' format to the Data Export tool to resolve the described problem.
As for Data Editor, we didn't make any fixes, because you should rewrite your statement to view the data you want (according to MySQL documentation):

Code: Select all

SELECT 
  saison, 
  GROUP_CONCAT( art, ';', CONVERT(anzahl USING UTF8) ORDER BY art SEPARATOR ';') AS summen 
FROM obst 
GROUP BY saison;

Or, if you don't want to use CONVERT function, you can view your data in Data Viewer.

Alexz · Post by **Alexz** » Thu 02 Jun 2011 08:13

By the way, we've checked the same situation on MySQL server v5.5, and MySQL returns TEXT column instead of LONGBLOB as it did in early versions, and everything works as you want.
Note that you should use non-binary collation for the 'art' column.