Group_concat( Concat(…) ) Displays Incorrectly In Grid View

[martin.lester]

CREATE TABLE `test` (

  `testid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `title` TEXT,

  `category` VARCHAR(256) DEFAULT NULL,

  PRIMARY KEY  (`testid`)

) ENGINE=INNODB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

;

INSERT INTO test (title, category)

VALUES

( “SOME-LONG-NAME-A”, “cat1”),

( “SOME-LONG-NAME-B”, “cat2”),

( “SOME-LONG-NAME-C”, “cat3”),

( “SOME-LONG-NAME-D”, “cat4”),

( “SOME-LONG-NAME-E”, “cat5”),

( “SOME-LONG-NAME-F”, “cat1”),

( “SOME-LONG-NAME-G”, “cat2”),

( “SOME-LONG-NAME-H”, “cat3”),

( “SOME-LONG-NAME-I”, “cat4”),

( “SOME-LONG-NAME-J”, “cat1”),

( “SOME-LONG-NAME-K”, “cat2”),

( “SOME-LONG-NAME-L”, “cat3”),

( “SOME-LONG-NAME-M”, “cat1”),

( “SOME-LONG-NAME-N”, “cat2”),

( “SOME-LONG-NAME-O”, “cat1”),

( “SOME-LONG-NAME-P”, “cat2”),

( “SOME-LONG-NAME-Q”, “cat1”)

;

SELECT

    GROUP_CONCAT( CONCAT(title, ‘ (‘, testid, ‘)’) ORDER BY title SEPARATOR ‘, ‘) AS display

FROM test

GROUP BY

    category

;

 

  When I run the select in SQLyog Enterprise – MySQL GUI v11.51 (64 bit) and previous versions the display colomn in “Grid View” or “Form View” is truncated at 63 characters. If you switch to the “Text View” or double click to open the “insert/update blob field” window you see the whole field.

 

e.g.

displayed: SOME-LONG-NAME-A (1), SOME-LONG-NAME-F (6), SOME-LONG-NAME-J (10

data:         SOME-LONG-NAME-A (1), SOME-LONG-NAME-F (6), SOME-LONG-NAME-J (10), SOME-LONG-NAME-M (13), SOME-LONG-NAME-O (15), SOME-LONG-NAME-Q (17)

 

  There are many other grid view text fields that display much longer strings. Normally it is quite obviously truncated, but occasionally the truncation lands at exactly the right point and effectively hides data from you!

[peterlaursen]

Please tell your server version.  This may be an issue with metadata returned from the server (a result set is both data and metadata). We will use the metadata for allocating a memory buffer. 

[peterlaursen]

This is expected behaviour. Try cmmand line client  with the –column-type-info option

 

C:Program FilesMySQLMySQL Server 5.6bin>mysql -uroot -p –port=3308 –column

-type-info

Enter password: ********

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 4

Server version: 5.1.73-community MySQL Community Server (GPL)

 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.

 

mysql> SELECT

    ->     GROUP_CONCAT( CONCAT(title, ‘ (‘, testid, ‘)’) ORDER BY title SEPARAT

OR ‘, ‘) AS display

    -> FROM test

    -> GROUP BY

    ->     category;

 

Field   1:  `display`

Catalog:    `def`

Database:   “

Table:      “

Org_table:  “

Type:       BLOB

Collation:  binary (63)

Length:     1024

Max_length: 134

Decimals:   0

Flags:      BINARY

 

 

+

---

---

+

| display

                                                         |

+

---

---

+

| SOME-LONG-NAME-A (1), SOME-LONG-NAME-F (6), SOME-LONG-NAME-J (10), SOME-LONG-N

AME-M (13), SOME-LONG-NAME-O (15), SOME-LONG-NAME-Q (17) |

| SOME-LONG-NAME-B (2), SOME-LONG-NAME-G (7), SOME-LONG-NAME-K (11), SOME-LONG-N

AME-N (14), SOME-LONG-NAME-P (16)                        |

| SOME-LONG-NAME-C (3), SOME-LONG-NAME-H (8), SOME-LONG-NAME-L (12)

                                                         |

| SOME-LONG-NAME-D (4), SOME-LONG-NAME-I (9)

                                                         |

| SOME-LONG-NAME-E (5)

                                                         |

+

---

---

+

5 rows in set (0.00 sec)

 

mysql>

 

 

Note that metadata read “Type: BLOB” and “Length: 1024” (I checked with multple server versions and it is the same from MySQL 5.1 at least).

 

 

 

With BLOB and TEXT data SQLog will display full data in the BLOB viewer (click on the button in the cell to open Blob Viewer). 

 

I have added a reference to this discussion in our issuetracker here https://code.google.com/p/sqlyog/issues/detail?id=919. It has listed other considerations about when we should and should not use the BLOB viewer.  However we are told by the server here that the result type is 1000 bytes long. Fully padded with characters it would be too long for display in the GRID. Anyway metadata also tell “Max_length: 134”. So we culd use that in combinaton with the user setting proposed in the issue tracker. Curently we don’t. Any *BLOB and *TEXT type (not only plain BLOBs and TEXTs but also TINYBLOB, MEDIUMTEXT etc.) opens in the BLOB viewer.

 

 

I remember that such cases were actually one of the resons why we introduced TEXT-mode in SQLyog.

[peterlaursen]

[attachment=1968:groupconcat.jpg]

 

Please see attached image

[peterlaursen]

[attachment=1969:width.jpg]

 

Besides you may increase the cell-width with the mouse, so that all data display. SQLyog wll even remember the width setting for that particular query (also after a program restart)  

[martin.lester]

Thank you for the prompt reply.

 

SELECT

    GROUP_CONCAT( CONCAT(title, ‘ (‘, testid, ‘)’) ORDER BY title SEPARATOR ‘, ‘) AS display_bad,

    GROUP_CONCAT( title ORDER BY title SEPARATOR ‘, ‘) AS display_ok

FROM test

GROUP BY

    category

;

 

So why does display_bad get truncated and display_ok not get truncated?

Why does the internal CONCAT cause the fields to be truncated in the grid view?

 

I understand the data is there and that I can see it all if I open the blob viewer, but expanding the width of the column has no effect. The characters get cut at exactly 63 chars for display_bad, but for display_ok they all show fine. As I say this is ONLY for the grid view (and form view) the list view is fine.

 

If you post a link on how to upload images I could show you what I see on my screen.

 

One thing I noticed on your command line output is:

  Collation:  binary (63)

 

Is that 63 something I can change? It seems too much of a coincidence that the truncation happens at 63 bytes too.

[martin.lester]

If I added the following to the previous SELECT:

    CONCAT(‘123456789012345678901234567890123456789012345678901234567890’, title, ‘ (‘, testid, ‘)’) AS `concat`

 

I ran the query on the command line with –column-type-info this is the column info:

 

Field   1:  `display_bad`

Catalog:    `def`

Database:   “

Table:      “

Org_table:  “

Type:       BLOB

Collation:  binary (63)

Length:     1048576

Max_length: 134

Decimals:   0

Flags:      BINARY

 

Field   2:  `display_ok`

Catalog:    `def`

Database:   “

Table:      “

Org_table:  “

Type:       BLOB

Collation:  latin1_swedish_ci (8)

Length:     1048576

Max_length: 106

Decimals:   0

Flags:

 

Field   3:  `concat`

Catalog:    `def`

Database:   “

Table:      “

Org_table:  “

Type:       VAR_STRING

Collation:  binary (63)

Length:     8192

Max_length: 80

Decimals:   31

Flags:      BINARY

 

In the Grid View UI the display_ok and `concat` columns display correctly i.e. by expanding the columns all the data is visible. However when I mix the 2 together I get something that truncates in the UI:

 

Type:       BLOB

Collation:  binary (63)

 

For a reason I can’t see. The collation of the GROUP_CONCAT blob has been changed from latin1_swedish_ci (8) to binary (63) because of the presence of the CONCAT. The Grid View is treating this oddly and is truncating the lines at 63 bytes.

 

 

I also tried adding COLLATE to the display_bad SELECT statement:

  GROUP_CONCAT( CONCAT(title, ‘ (‘, testid, ‘)’) ORDER BY title SEPARATOR ‘, ‘) COLLATE latin1_swedish_ci AS display_bad

 

However this gives the error:

  Error Code: 1253

  COLLATION ‘latin1_swedish_ci’ is not valid for CHARACTER SET ‘binary’

 

This is the same error I get if I try to add COLLATE to the `concat` field.

[peterlaursen]

Thanks for the detailed explanation (that I do not fully understand yet).

 

Can you please specify if you think you found a bug/an oddity with MySQL or with SQlyog.  This is not clear to me. If you think it is with SQLyog, please try to clarify what the exact issue is.

[peterlaursen]

From MySQL documentation:

 

http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat “If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.”

 

http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat “The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type is TEXT or BLOB unless group_concat_max_len is less than or equal to 512, in which case the result type is VARCHAR or VARBINARY.”

 

 

Do you see a different behavior? if so it should be reported to bugs.mysql.com. But what is your group_concat_max_len setting (“SHOW VARIABlES LIKE ‘group_concat_max_len’;”). According to http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len default is 1024.  Also note that you may SET if on a SESSION basis. If you “SET SESSION group_concat_max_len = 512;”  on top of your test case, does it change anything? 

[martin.lester]

This is definitely a bug in SQLyog only. All the data comes back correctly from the MySQL server. Also the data is correctly displayed in some places of the UI. The issue I have is how the “Grid View” displays the GROUP_CONCAT( CONCAT () ) field I have created. 

SELECT

    GROUP_CONCAT( CONCAT(title, ' (', testid, ')') ORDER BY title SEPARATOR ', ') AS display_bad,

    GROUP_CONCAT( title ORDER BY title SEPARATOR ', ') AS display_ok,

    CONCAT('123456789012345678901234567890123456789012345678901234567890-', title, ' (', testid, ')') AS `concat`

FROM test

GROUP BY

    category

This query produces 3 columns of data. The first column of data is truncated in the “Grid View” at exactly 64 characters for all rows. So for the first column I see exactly this (I wish I knew how to post an image here!):

display_bad

SOME-LONG-NAME-A (1), SOME-LONG-NAME-F (6), SOME-LONG-NAME-J (10            [134B]

SOME-LONG-NAME-B (2), SOME-LONG-NAME-G (7), SOME-LONG-NAME-K (11            [111B]

SOME-LONG-NAME-C (3), SOME-LONG-NAME-H (8), SOME-LONG-NAME-L (12            [ 65B]

SOME-LONG-NAME-D (4), SOME-LONG-NAME-I (9)                                  [ 42B]

SOME-LONG-NAME-E (5)                                                        [ 20B]

Note there are no … at the end of any of the fields. Also you can see that the truncation is happening at exactly 64 characters, wherever there are more than that nothing more is displayed however wide the field is made. Incidentally if I reduce the column width so less than 64 chars are visible I do start to get the … at the end of each line. The number of bytes displayed is correct.

 

If I change to “List View” I get the following displayed for that column:

display_bad

SOME-LONG-NAME-A (1), SOME-LONG-NAME-F (6), SOME-LONG-NAME-J (10), SOME-LONG-NAME-M (13), SOME-LONG-NAME-O (15), SOME-LONG-NAME-Q (17)

SOME-LONG-NAME-B (2), SOME-LONG-NAME-G (7), SOME-LONG-NAME-K (11), SOME-LONG-NAME-N (14), SOME-LONG-NAME-P (16)

SOME-LONG-NAME-C (3), SOME-LONG-NAME-H (8), SOME-LONG-NAME-L (12)

SOME-LONG-NAME-D (4), SOME-LONG-NAME-I (9)

SOME-LONG-NAME-E (5)

As you can see the data set in SQLyog is correct, it is just the way SQLyog is displaying that specific field in the “Grid View”.

 

This is how similar columns are displayed.First display_ok, a normal GROUP_CONCAT:

display_ok                                                                                                

SOME-LONG-NAME-A, SOME-LONG-NAME-F, SOME-LONG-NAME-J, SOME-LONG-NAME-M, SOME-LONG-NAME-O...[106B]

SOME-LONG-NAME-B, SOME-LONG-NAME-G, SOME-LONG-NAME-K, SOME-LONG-NAME-N, SOME-LONG-NAME-P   [ 88B]               

SOME-LONG-NAME-C, SOME-LONG-NAME-H, SOME-LONG-NAME-L                                       [ 52B]               

SOME-LONG-NAME-D, SOME-LONG-NAME-I                                                         [ 34B]               

SOME-LONG-NAME-E                                                                           [ 16B] 

Note, on the column that gets truncated the UI has correctly replaced the last few chars with … and if I extend the column width more these are replaced with the correct data so that all the data is visible. 

Lastly the `concat` column. this is a CONCAT with 81 characters.

concat                                                                             

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-A (1)  

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-B (2)  

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-C (3)  

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-D (4)  

123456789012345678901234567890123456789012345678901234567890-SOME-LONG-NAME-E (5)  

If I change the width of the column I do not get any … and I also do not see the byte size of the field. The field is simply chopped at the point where the column width stops. Unlike the other 2 columns this truncates the field to the pixel. i.e. I can get the “Grid View” to display half a character depending on the exact width of the column.

 

So in summary:

  GROUP_CONCAT : Displays as much data in the field as I have space on screen. When it truncates it places a … at the end. It always shows the number of bytes at the end of the Grid View cell.

  CONCAT : Displays as much of the data as I have space on the screen. Shows as much of the text as is it can fit in the cell truncating it to the pixel. It never shows the byte size.

  GROUP_CONCAT(CONCAT()) : Never shows more than the first 64 characters of any cell regardless of how wide the column is. When cell width is smaller than 64 characters it does truncate and places a … at the end. It always shows the correct number of bytes at the end of the cell.

 

It seems to me there is an odd UI behaviour here in the Grid View. If I extend the column to full screen width (100’s of chars wide) and I show a GROUP_CONCAT or a CONCAT that has many characters in it, I will see the full set of data for each cell. If I put a CONCAT inside a GROUP_CONCAT I will only see 64 characters.

 

 

p.s. In answer to your question we have increased group_concat_max_len on all DB servers to 1M. It shows in the column info for both GROUP_CONCAT columns as “Length:     1048576″. This query is nowhere near that limit!

[peterlaursen]

To post images ‘click more reply options’, next select a file, ‘attach’ and finally ‘append to post’.

[martin.lester]

[attachment=1971:SQLyog_displaybug1.png]

[attachment=1972:SQLyog_displaybug2.png]

 

In the first image you can see where the text from the GROUP_CONCAT(CONCAT()) is getting truncated

[martin.lester]

[attachment=1973:SQLyog_displaybug3.png]

 

The list view of the same display_bad column.

[Sudhindra Bhat]

Hello,

 

Quoting from MySQL docs for CONCAT() :

 

 

CONCAT(str1, str2, …) 

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent string form. This is a nonbinary string as of MySQL 5.5.3. Before 5.5.3, it is a binary string; to avoid that and produce a nonbinary string, you can use an explicit type cast, as in this example:

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

 

SQLyog grid view only displays the first 64 characters in the grid view of result/table data tab if the TEXT datatype is of ‘binary’ character-set.

 

Hence I would suggest you to cast the ‘testid’ field as CHAR in your queries, since the version of MySQL server you are connecting to appears to be pre 5.5.3. 

 

You will then be able to see the result of the column in the grid view without truncation at 64th character.

 

Regards,

Sudhindra Bhat

[peterlaursen]

Also please note that MySQL 5.1x is not actively supported anymore – refer http://www.mysql.com/support/eol-notice.html “December 4, 2013: MySQL 5.1 covered under Oracle Lifetime Sustaining Support”. Andhttp://www.mysql.com/support/ “No new releases, no new fixes (no error correction for new issues), no new updates. Only pre-existing updates, fixes and alerts are available.”

 

As this is the situation with MySQL 5.1 we will not change anything in SQLyog to handle this better. It works without explicit casting with server versions that are fully supported today.

[martin.lester]

Thanks Sudhindra, that solved my problem. Should have thought of that myself  🙄 

 

As for the ancient version of MySQL we use, we have wanted to move forward for 5+ years but those that make the decisions say the cost/risk analysis shows it doesn’t make sense. We have a vast amount of code with lots of SQL that would need to be tested with a new version and less than 1/2 the number of people on the team so it looks like we’ll be sticking with 5.1 for a while longer yet!

[Author]

Posts