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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
############## mysql-test\t\group_concat_max_len_func.test ####################
# #
# Variable Name: group_concat_max_len #
# Scope: GLOBAL | SESSION #
# Access Type: Dynamic #
# Data Type: numeric #
# Default Value: 1024 #
# Minimum value: 4 #
# #
# #
# Creation Date: 2008-03-07 #
# Author: Salman Rawala #
# #
# Last modification: #
# 2008-11-14 mleich Fix Bug#40644 main.group_concat_max_len_func random #
# failures #
# + minor improvements #
# #
# Description: Test Cases of Dynamic System Variable group_concat_max_len #
# that checks the functionality of this variable #
# #
# Reference: #
# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
# #
###############################################################################
SET @save = @@global.group_concat_max_len;
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
#########################
# Creating new table #
#########################
--echo ## Creating new table t1 ##
CREATE TABLE t1
(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
rollno INT NOT NULL,
name VARCHAR(30)
);
--echo '#--------------------FN_DYNVARS_034_01-------------------------#'
########################################################################
# Setting initial value of group_concat_max_len, inserting some rows
# & creating 2 new connections
########################################################################
--echo ## Setting initial value of variable to 4 ##
SET @@global.group_concat_max_len = 4;
--echo ## 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');
# The following "auxiliary" select ensures that all records are on disk
# = result sets got by parallel sessions cannot suffer from effects
# caused by the MyISAM feature "concurrent_inserts".
SELECT * FROM t1 ORDER BY id;
connect (test_con1,localhost,root,,);
connect (test_con2,localhost,root,,);
--echo '#--------------------FN_DYNVARS_034_02-------------------------#'
###############################################################################
# Verifying initial behavior of variable by concatinating values greater than 4
###############################################################################
connection test_con1;
--echo ## Accessing data and using group_concat on column whose value is greater than 4 ##
--disable_ps2_protocol
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
--enable_ps2_protocol
--echo ## Changing session value of variable and verifying its behavior, ##
--echo ## warning should come here ##
SET @@session.group_concat_max_len = 10;
--disable_ps2_protocol
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
--enable_ps2_protocol
--echo '#--------------------FN_DYNVARS_034_03-------------------------#'
##############################################################################
# Verifying behavior of variable by increasing session value of variable #
##############################################################################
connection test_con2;
--echo ## Verifying initial value of variable. It should be 4 ##
SELECT @@session.group_concat_max_len = 4;
--echo ## Setting session value of variable to 20 and verifying variable is concating ##
--echo ## column's value to 20 or not ##
SET @@session.group_concat_max_len = 20;
--echo ## Verifying value of name column, it should not me more than 20 characters ##
--echo ## Warning should come here ##
--disable_ps2_protocol
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
--enable_ps2_protocol
--echo '#--------------------FN_DYNVARS_034_04-------------------------#'
###############################################################################
# Verifying behavior of variable by increasing session value of variable #
# greater than the maximum concat length of name column #
###############################################################################
--echo ## Setting session value of variable to 26. No warning should appear here ##
--echo ## because the value after concatination is less than 30 ##
SET @@session.group_concat_max_len = 26;
--echo ## Verifying value of name column, it should not give warning now ##
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
############################################################
# Disconnecting all connection & dropping table #
############################################################
--echo ## 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';
SELECT GROUP_CONCAT(val) AS simple FROM t1;
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
SET group_concat_max_len = 1073741824;
SHOW VARIABLES LIKE 'group_concat_max_len';
SELECT GROUP_CONCAT(val) AS simple FROM t1;
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
SET group_concat_max_len = 1073741825;
SHOW VARIABLES LIKE 'group_concat_max_len';
SELECT GROUP_CONCAT(val) AS simple FROM t1;
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
SET group_concat_max_len = 1073741826;
SHOW VARIABLES LIKE 'group_concat_max_len';
SELECT GROUP_CONCAT(val) AS simple FROM t1;
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
SET group_concat_max_len = 2147483649;
SHOW VARIABLES LIKE 'group_concat_max_len';
SELECT GROUP_CONCAT(val) AS simple FROM t1;
SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
DROP TABLE t1;
SET @@global.group_concat_max_len = @save;
|
