1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
|
SET @save = @@global.group_concat_max_len;
DROP TABLE IF EXISTS t1;
## Creating new table t1 ##
CREATE TABLE t1
(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
rollno INT NOT NULL,
name VARCHAR(30)
);
'#--------------------FN_DYNVARS_034_01-------------------------#'
## Setting initial value of variable to 4 ##
SET @@global.group_concat_max_len = 4;
## Inserting some rows in table ##
INSERT INTO t1(rollno, name) VALUES(1, 'Record_1');
INSERT INTO t1(rollno, name) VALUES(2, 'Record_2');
INSERT INTO t1(rollno, name) VALUES(1, 'Record_3');
INSERT INTO t1(rollno, name) VALUES(3, 'Record_4');
INSERT INTO t1(rollno, name) VALUES(1, 'Record_5');
INSERT INTO t1(rollno, name) VALUES(3, 'Record_6');
INSERT INTO t1(rollno, name) VALUES(4, 'Record_7');
INSERT INTO t1(rollno, name) VALUES(4, 'Record_8');
SELECT * FROM t1 ORDER BY id;
id rollno name
1 1 Record_1
2 2 Record_2
3 1 Record_3
4 3 Record_4
5 1 Record_5
6 3 Record_6
7 4 Record_7
8 4 Record_8
connect test_con1,localhost,root,,;
connect test_con2,localhost,root,,;
'#--------------------FN_DYNVARS_034_02-------------------------#'
connection test_con1;
## Accessing data and using group_concat on column whose value is greater than 4 ##
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
id rollno GROUP_CONCAT(name)
1 1 Reco
2 2 Reco
4 3 Reco
7 4 Reco
Warnings:
Warning 1260 Row 1 was cut by GROUP_CONCAT()
Warning 1260 Row 2 was cut by GROUP_CONCAT()
Warning 1260 Row 3 was cut by GROUP_CONCAT()
Warning 1260 Row 4 was cut by GROUP_CONCAT()
## Changing session value of variable and verifying its behavior, ##
## warning should come here ##
SET @@session.group_concat_max_len = 10;
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
id rollno GROUP_CONCAT(name)
1 1 Record_1,R
2 2 Record_2
4 3 Record_4,R
7 4 Record_7,R
Warnings:
Warning 1260 Row 2 was cut by GROUP_CONCAT()
Warning 1260 Row 5 was cut by GROUP_CONCAT()
Warning 1260 Row 7 was cut by GROUP_CONCAT()
'#--------------------FN_DYNVARS_034_03-------------------------#'
connection test_con2;
## Verifying initial value of variable. It should be 4 ##
SELECT @@session.group_concat_max_len = 4;
@@session.group_concat_max_len = 4
1
## Setting session value of variable to 20 and verifying variable is concating ##
## column's value to 20 or not ##
SET @@session.group_concat_max_len = 20;
## Verifying value of name column, it should not me more than 20 characters ##
## Warning should come here ##
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
id rollno GROUP_CONCAT(name)
1 1 Record_1,Record_3,Re
2 2 Record_2
4 3 Record_4,Record_6
7 4 Record_7,Record_8
Warnings:
Warning 1260 Row 3 was cut by GROUP_CONCAT()
'#--------------------FN_DYNVARS_034_04-------------------------#'
## Setting session value of variable to 26. No warning should appear here ##
## because the value after concatination is less than 30 ##
SET @@session.group_concat_max_len = 26;
## Verifying value of name column, it should not give warning now ##
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
id rollno GROUP_CONCAT(name)
1 1 Record_1,Record_3,Record_5
2 2 Record_2
4 3 Record_4,Record_6
7 4 Record_7,Record_8
## Dropping table t1 ##
DROP TABLE t1;
disconnect test_con2;
disconnect test_con1;
connection default;
CREATE TABLE t1(val VARCHAR(100) PRIMARY KEY) CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci;
INSERT INTO t1 VALUES('bar');
INSERT INTO t1 VALUES('foo');
SET group_concat_max_len = 1073741823;
SHOW VARIABLES LIKE 'group_concat_max_len';
Variable_name Value
group_concat_max_len 1073741823
SELECT GROUP_CONCAT(val) AS simple FROM t1;
simple
bar,foo
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
nested
bar,foo
SET group_concat_max_len = 1073741824;
SHOW VARIABLES LIKE 'group_concat_max_len';
Variable_name Value
group_concat_max_len 1073741824
SELECT GROUP_CONCAT(val) AS simple FROM t1;
simple
bar,foo
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
nested
bar,foo
SET group_concat_max_len = 1073741825;
SHOW VARIABLES LIKE 'group_concat_max_len';
Variable_name Value
group_concat_max_len 1073741825
SELECT GROUP_CONCAT(val) AS simple FROM t1;
simple
bar,foo
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
nested
bar,foo
SET group_concat_max_len = 1073741826;
SHOW VARIABLES LIKE 'group_concat_max_len';
Variable_name Value
group_concat_max_len 1073741826
SELECT GROUP_CONCAT(val) AS simple FROM t1;
simple
bar,foo
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
nested
bar,foo
SET group_concat_max_len = 2147483649;
SHOW VARIABLES LIKE 'group_concat_max_len';
Variable_name Value
group_concat_max_len 2147483649
SELECT GROUP_CONCAT(val) AS simple FROM t1;
simple
bar,foo
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
nested
bar,foo
DROP TABLE t1;
SET @@global.group_concat_max_len = @save;
|
