1SET @save = @@global.group_concat_max_len; 2DROP TABLE IF EXISTS t1; 3## Creating new table t1 ## 4CREATE TABLE t1 5( 6id INT NOT NULL AUTO_INCREMENT, 7PRIMARY KEY (id), 8rollno INT NOT NULL, 9name VARCHAR(30) 10); 11'#--------------------FN_DYNVARS_034_01-------------------------#' 12## Setting initial value of variable to 4 ## 13SET @@global.group_concat_max_len = 4; 14## Inserting some rows in table ## 15INSERT INTO t1(rollno, name) VALUES(1, 'Record_1'); 16INSERT INTO t1(rollno, name) VALUES(2, 'Record_2'); 17INSERT INTO t1(rollno, name) VALUES(1, 'Record_3'); 18INSERT INTO t1(rollno, name) VALUES(3, 'Record_4'); 19INSERT INTO t1(rollno, name) VALUES(1, 'Record_5'); 20INSERT INTO t1(rollno, name) VALUES(3, 'Record_6'); 21INSERT INTO t1(rollno, name) VALUES(4, 'Record_7'); 22INSERT INTO t1(rollno, name) VALUES(4, 'Record_8'); 23SELECT * FROM t1 ORDER BY id; 24id rollno name 251 1 Record_1 262 2 Record_2 273 1 Record_3 284 3 Record_4 295 1 Record_5 306 3 Record_6 317 4 Record_7 328 4 Record_8 33connect test_con1,localhost,root,,; 34connect test_con2,localhost,root,,; 35'#--------------------FN_DYNVARS_034_02-------------------------#' 36connection test_con1; 37## Accessing data and using group_concat on column whose value is greater than 4 ## 38SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; 39id rollno GROUP_CONCAT(name) 401 1 Reco 412 2 Reco 424 3 Reco 437 4 Reco 44Warnings: 45Warning 1260 Row 1 was cut by GROUP_CONCAT() 46Warning 1260 Row 2 was cut by GROUP_CONCAT() 47Warning 1260 Row 3 was cut by GROUP_CONCAT() 48Warning 1260 Row 4 was cut by GROUP_CONCAT() 49## Changing session value of variable and verifying its behavior, ## 50## warning should come here ## 51SET @@session.group_concat_max_len = 10; 52SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; 53id rollno GROUP_CONCAT(name) 541 1 Record_1,R 552 2 Record_2 564 3 Record_4,R 577 4 Record_7,R 58Warnings: 59Warning 1260 Row 2 was cut by GROUP_CONCAT() 60Warning 1260 Row 5 was cut by GROUP_CONCAT() 61Warning 1260 Row 7 was cut by GROUP_CONCAT() 62'#--------------------FN_DYNVARS_034_03-------------------------#' 63connection test_con2; 64## Verifying initial value of variable. It should be 4 ## 65SELECT @@session.group_concat_max_len = 4; 66@@session.group_concat_max_len = 4 671 68## Setting session value of variable to 20 and verifying variable is concating ## 69## column's value to 20 or not ## 70SET @@session.group_concat_max_len = 20; 71## Verifying value of name column, it should not me more than 20 characters ## 72## Warning should come here ## 73SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; 74id rollno GROUP_CONCAT(name) 751 1 Record_1,Record_3,Re 762 2 Record_2 774 3 Record_4,Record_6 787 4 Record_7,Record_8 79Warnings: 80Warning 1260 Row 3 was cut by GROUP_CONCAT() 81'#--------------------FN_DYNVARS_034_04-------------------------#' 82## Setting session value of variable to 26. No warning should appear here ## 83## because the value after concatination is less than 30 ## 84SET @@session.group_concat_max_len = 26; 85## Verifying value of name column, it should not give warning now ## 86SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; 87id rollno GROUP_CONCAT(name) 881 1 Record_1,Record_3,Record_5 892 2 Record_2 904 3 Record_4,Record_6 917 4 Record_7,Record_8 92## Dropping table t1 ## 93DROP TABLE t1; 94disconnect test_con2; 95disconnect test_con1; 96connection default; 97SET @@global.group_concat_max_len = @save; 98