Bug #78041 GROUP_CONCAT() truncation should be an error when strict mode enabled
Submitted: 12 Aug 2015 12:45 Modified: 12 Aug 2015 13:40
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:8.0+ OS:Any
Assigned to: CPU Architecture:Any

[12 Aug 2015 12:45] Morgan Tocker
Description:
GROUP_CONCAT is prone to frequent silent truncation because the default max length is 1024 characters.  Increasing this default would lessen the likelihood of truncation, but this is not necessarily an improvement, since the situation can still occur.

How to repeat:
CREATE TABLE t1 (id int not null primary key auto_increment, b int not null);
INSERT INTO t1 VALUES (NULL, 1);
INSERT INTO t1 select null, 1 from t1;
INSERT INTO t1 select null, 1 from t1 a, t1 b, t1 c;
INSERT INTO t1 select null, 1 from t1 a, t1 b, t1 c;
INSERT INTO t1 select null, 2 from t1;
select group_concat(id) from t1 group by b\G

*************************** 1. row ***************************
group_concat(id): 1,2,3,4,5,6,7,8,9,10,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,2
*************************** 2. row ***************************
group_concat(id): 1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245
2 rows in set, 2 warnings (0.01 sec)

mysql [localhost] {msandbox} (test) > show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 282 was cut by GROUP_CONCAT() |
| Warning | 1260 | Row 488 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Suggested fix:
Convert the warnings to be errors when a strict mode is enabled.
[12 Aug 2015 13:39] Miguel Solorzano
Thank you for the bug report. Verified as described.
[18 Jun 2016 21:27] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0
[6 Jul 2017 13:25] Vijit Coder
I join the petitioner: there should be an error instead silent value cutting. I my case current behavior of GROUP_CONCAT() in the SELECT qury led to a serious hidden bug in the project.

@Omer, can you explain please your last answer? It looks like a set of word, not a sentence. What you tried to say?