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 connect test_con1,localhost,root,,;
34 connect test_con2,localhost,root,,;
35 '#--------------------FN_DYNVARS_034_02-------------------------#'
36 connection test_con1;
37 ## Accessing data and using group_concat on column whose value is greater than 4 ##
38 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
39 id rollno GROUP_CONCAT(name)
40 1 1 Reco
41 2 2 Reco
42 4 3 Reco
43 7 4 Reco
44 Warnings:
45 Warning 1260 Row 1 was cut by group_concat()
46 Warning 1260 Row 2 was cut by group_concat()
47 Warning 1260 Row 3 was cut by group_concat()
48 Warning 1260 Row 4 was cut by group_concat()
49 ## Changing session value of variable and verifying its behavior, ##
50 ## warning should come here ##
51 SET @@session.group_concat_max_len = 10;
52 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
53 id rollno GROUP_CONCAT(name)
54 1 1 Record_1,R
55 2 2 Record_2
56 4 3 Record_4,R
57 7 4 Record_7,R
58 Warnings:
59 Warning 1260 Row 2 was cut by group_concat()
60 Warning 1260 Row 5 was cut by group_concat()
61 Warning 1260 Row 7 was cut by group_concat()
62 '#--------------------FN_DYNVARS_034_03-------------------------#'
63 connection test_con2;
64 ## Verifying initial value of variable. It should be 4 ##
65 SELECT @@session.group_concat_max_len = 4;
66 @@session.group_concat_max_len = 4
67 1
68 ## Setting session value of variable to 20 and verifying variable is concating ##
69 ## column's value to 20 or not ##
70 SET @@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 ##
73 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
74 id rollno GROUP_CONCAT(name)
75 1 1 Record_1,Record_3,Re
76 2 2 Record_2
77 4 3 Record_4,Record_6
78 7 4 Record_7,Record_8
79 Warnings:
80 Warning 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 ##
84 SET @@session.group_concat_max_len = 26;
85 ## Verifying value of name column, it should not give warning now ##
86 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
87 id rollno GROUP_CONCAT(name)
88 1 1 Record_1,Record_3,Record_5
89 2 2 Record_2
90 4 3 Record_4,Record_6
91 7 4 Record_7,Record_8
92 ## Dropping table t1 ##
93 DROP TABLE t1;
94 disconnect test_con2;
95 disconnect test_con1;
96 connection default;
97 CREATE TABLE t1(val VARCHAR(100) PRIMARY KEY) CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci;
98 INSERT INTO t1 VALUES('bar');
99 INSERT INTO t1 VALUES('foo');
100 SET group_concat_max_len = 1073741823;
101 SHOW VARIABLES LIKE 'group_concat_max_len';
102 Variable_name Value
103 group_concat_max_len 1073741823
104 SELECT GROUP_CONCAT(val) AS simple FROM t1;
105 simple
106 bar,foo
107 SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
108 nested
109 bar,foo
110 SET group_concat_max_len = 1073741824;
111 SHOW VARIABLES LIKE 'group_concat_max_len';
112 Variable_name Value
113 group_concat_max_len 1073741824
114 SELECT GROUP_CONCAT(val) AS simple FROM t1;
115 simple
116 bar,foo
117 SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
118 nested
119 bar,foo
120 SET group_concat_max_len = 1073741825;
121 Warnings:
122 Warning 1292 Truncated incorrect group_concat_max_len value: '1073741825'
123 SHOW VARIABLES LIKE 'group_concat_max_len';
124 Variable_name Value
125 group_concat_max_len 1073741824
126 SELECT GROUP_CONCAT(val) AS simple FROM t1;
127 simple
128 bar,foo
129 SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
130 nested
131 bar,foo
132 DROP TABLE t1;
133 SET @@global.group_concat_max_len = @save;