Debian Codegraph @

mysql-test/suite/sys_vars/r/group_concat_max_len_func.result

raw
1 SET @save = @@global.group_concat_max_len;
2 DROP TABLE IF EXISTS t1;
3 ## Creating new table t1 ##
4 CREATE TABLE t1
5 (
6 id INT NOT NULL AUTO_INCREMENT,
7 PRIMARY KEY (id),
8 rollno INT NOT NULL,
9 name VARCHAR(30)
10 );
11 '#--------------------FN_DYNVARS_034_01-------------------------#'
12 ## Setting initial value of variable to 4 ##
13 SET @@global.group_concat_max_len = 4;
14 ## Inserting some rows in table ##
15 INSERT INTO t1(rollno, name) VALUES(1, 'Record_1');
16 INSERT INTO t1(rollno, name) VALUES(2, 'Record_2');
17 INSERT INTO t1(rollno, name) VALUES(1, 'Record_3');
18 INSERT INTO t1(rollno, name) VALUES(3, 'Record_4');
19 INSERT INTO t1(rollno, name) VALUES(1, 'Record_5');
20 INSERT INTO t1(rollno, name) VALUES(3, 'Record_6');
21 INSERT INTO t1(rollno, name) VALUES(4, 'Record_7');
22 INSERT INTO t1(rollno, name) VALUES(4, 'Record_8');
23 SELECT * FROM t1 ORDER BY id;
24 id rollno name
25 1 1 Record_1
26 2 2 Record_2
27 3 1 Record_3
28 4 3 Record_4
29 5 1 Record_5
30 6 3 Record_6
31 7 4 Record_7
32 8 4 Record_8
33 ## Creating two new connections ##
34 '#--------------------FN_DYNVARS_034_02-------------------------#'
35 ## Connecting with test_con1 ##
36 ## Accessing data and using group_concat on column whose value is greater than 4 ##
37 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
38 id rollno GROUP_CONCAT(name)
39 1 1 Reco
40 2 2 Reco
41 4 3 Reco
42 7 4 Reco
43 Warnings:
44 Warning 1260 Row 1 was cut by GROUP_CONCAT()
45 Warning 1260 Row 2 was cut by GROUP_CONCAT()
46 Warning 1260 Row 3 was cut by GROUP_CONCAT()
47 Warning 1260 Row 4 was cut by GROUP_CONCAT()
48 ## Changing session value of variable and verifying its behavior, ##
49 ## warning should come here ##
50 SET @@session.group_concat_max_len = 10;
51 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
52 id rollno GROUP_CONCAT(name)
53 1 1 Record_1,R
54 2 2 Record_2
55 4 3 Record_4,R
56 7 4 Record_7,R
57 Warnings:
58 Warning 1260 Row 2 was cut by GROUP_CONCAT()
59 Warning 1260 Row 5 was cut by GROUP_CONCAT()
60 Warning 1260 Row 7 was cut by GROUP_CONCAT()
61 '#--------------------FN_DYNVARS_034_03-------------------------#'
62 ## Connecting with new connection test_con2 ##
63 ## Verifying initial value of variable. It should be 4 ##
64 SELECT @@session.group_concat_max_len = 4;
65 @@session.group_concat_max_len = 4
66 1
67 ## Setting session value of variable to 20 and verifying variable is concating ##
68 ## column's value to 20 or not ##
69 SET @@session.group_concat_max_len = 20;
70 ## Verifying value of name column, it should not me more than 20 characters ##
71 ## Warning should come here ##
72 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
73 id rollno GROUP_CONCAT(name)
74 1 1 Record_1,Record_3,Re
75 2 2 Record_2
76 4 3 Record_4,Record_6
77 7 4 Record_7,Record_8
78 Warnings:
79 Warning 1260 Row 3 was cut by GROUP_CONCAT()
80 '#--------------------FN_DYNVARS_034_04-------------------------#'
81 ## Setting session value of variable to 26. No warning should appear here ##
82 ## because the value after concatination is less than 30 ##
83 SET @@session.group_concat_max_len = 26;
84 ## Verifying value of name column, it should not give warning now ##
85 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
86 id rollno GROUP_CONCAT(name)
87 1 1 Record_1,Record_3,Record_5
88 2 2 Record_2
89 4 3 Record_4,Record_6
90 7 4 Record_7,Record_8
91 ## Dropping table t1 ##
92 DROP TABLE t1;
93 ## Disconnecting both the connection ##
94 SET @@global.group_concat_max_len = @save;