Bug #24449 SUBSTRING(LONGTEXT_FIELD, ...) displaying weird behaviour with GROUP CONCAT

• Submitted: 21 Nov 2006 1:17
• Modified: 10 Mar 2007 21:23
• Reporter: Roland Bouman
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.27-max, 5.1.12
• OS: Linux (ubuntu etchy)
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: column_type, columns.column_type, conversion, substring

[21 Nov 2006 1:17] Roland Bouman

Description:
The column_type column of the columns view in the information_schema has some very annoying behaviour when used with group_concat

It's not always easy to reproduce but I have on case.

How to repeat:
mysql>  select group_concat(substring(column_type,1,character_length(column_type))) from information_schema.columns \G
*************************** 1. row ***************************
group_concat(substring(column_type,1,character_length(column_type))): ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
1 row in set (0.02 sec)

Suggested fix:
The substring(column_type,1,character_length(column_type)) should of course evaluate to the same value as column_type.

However, the values mystically dissapear after the function is applied. 

It's strange: select substring(column_type,1,character_length(column_type)) from information_schema.columns \G

does display the values correctly

[21 Nov 2006 8:15] Hartmut Holzgraefe

verified, this is actually a problem with LONGTEXT fields in general and not specific to I_S.columns, it can also be reproduced with:

CREATE TABLE `t1` (`c` LONGTEXT);
INSERT INTO `t1` VALUES ('abc'),('def');
SELECT GROUP_CONCAT(SUBSTRING(c,1,1)) FROM t1;

when changing the column type to TEXT all works fine

[21 Nov 2006 8:17] Hartmut Holzgraefe

mysqltest test case

Attachment: bug24449.tar.gz (application/x-gunzip, text), 739 bytes.

[22 Nov 2006 23:07] Roland Bouman

changed category as this problem seems to be generic.

[10 Mar 2007 21:23] Evgeny Potemkin

Duplicate of the bug#15757.