Debian Codegraph @

mysql-test/suite/sys_vars/t/group_concat_max_len_func.test

raw
1 ############## mysql-test\t\group_concat_max_len_func.test ####################
2 # #
3 # Variable Name: group_concat_max_len #
4 # Scope: GLOBAL | SESSION #
5 # Access Type: Dynamic #
6 # Data Type: numeric #
7 # Default Value: 1024 #
8 # Minimum value: 4 #
9 # #
10 # #
11 # Creation Date: 2008-03-07 #
12 # Author: Salman Rawala #
13 # #
14 # Last modification: #
15 # 2008-11-14 mleich Fix Bug#40644 main.group_concat_max_len_func random #
16 # failures #
17 # + minor improvements #
18 # #
19 # Description: Test Cases of Dynamic System Variable group_concat_max_len #
20 # that checks the functionality of this variable #
21 # #
22 # Reference: #
23 # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
24 # #
25 ###############################################################################
26
27 SET @save = @@global.group_concat_max_len;
28
29 --disable_warnings
30 DROP TABLE IF EXISTS t1;
31 --enable_warnings
32
33 #########################
34 # Creating new table #
35 #########################
36
37 --echo ## Creating new table t1 ##
38 CREATE TABLE t1
39 (
40 id INT NOT NULL AUTO_INCREMENT,
41 PRIMARY KEY (id),
42 rollno INT NOT NULL,
43 name VARCHAR(30)
44 );
45
46 --echo '#--------------------FN_DYNVARS_034_01-------------------------#'
47 ########################################################################
48 # Setting initial value of group_concat_max_len, inserting some rows
49 # & creating 2 new connections
50 ########################################################################
51
52 --echo ## Setting initial value of variable to 4 ##
53 SET @@global.group_concat_max_len = 4;
54
55 --echo ## Inserting some rows in table ##
56 INSERT INTO t1(rollno, name) VALUES(1, 'Record_1');
57 INSERT INTO t1(rollno, name) VALUES(2, 'Record_2');
58 INSERT INTO t1(rollno, name) VALUES(1, 'Record_3');
59 INSERT INTO t1(rollno, name) VALUES(3, 'Record_4');
60 INSERT INTO t1(rollno, name) VALUES(1, 'Record_5');
61 INSERT INTO t1(rollno, name) VALUES(3, 'Record_6');
62 INSERT INTO t1(rollno, name) VALUES(4, 'Record_7');
63 INSERT INTO t1(rollno, name) VALUES(4, 'Record_8');
64 # The following "auxiliary" select ensures that all records are on disk
65 # = result sets got by parallel sessions cannot suffer from effects
66 # caused by the MyISAM feature "concurrent_inserts".
67 SELECT * FROM t1 ORDER BY id;
68
69 connect (test_con1,localhost,root,,);
70 connect (test_con2,localhost,root,,);
71
72
73 --echo '#--------------------FN_DYNVARS_034_02-------------------------#'
74 ###############################################################################
75 # Verifying initial behavior of variable by concatinating values greater than 4
76 ###############################################################################
77
78 connection test_con1;
79
80 --echo ## Accessing data and using group_concat on column whose value is greater than 4 ##
81 --disable_ps2_protocol
82 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
83 --enable_ps2_protocol
84
85 --echo ## Changing session value of variable and verifying its behavior, ##
86 --echo ## warning should come here ##
87
88 SET @@session.group_concat_max_len = 10;
89 --disable_ps2_protocol
90 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
91 --enable_ps2_protocol
92
93 --echo '#--------------------FN_DYNVARS_034_03-------------------------#'
94 ##############################################################################
95 # Verifying behavior of variable by increasing session value of variable #
96 ##############################################################################
97
98 connection test_con2;
99
100 --echo ## Verifying initial value of variable. It should be 4 ##
101 SELECT @@session.group_concat_max_len = 4;
102
103 --echo ## Setting session value of variable to 20 and verifying variable is concating ##
104 --echo ## column's value to 20 or not ##
105 SET @@session.group_concat_max_len = 20;
106
107 --echo ## Verifying value of name column, it should not me more than 20 characters ##
108 --echo ## Warning should come here ##
109 --disable_ps2_protocol
110 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
111 --enable_ps2_protocol
112
113 --echo '#--------------------FN_DYNVARS_034_04-------------------------#'
114 ###############################################################################
115 # Verifying behavior of variable by increasing session value of variable #
116 # greater than the maximum concat length of name column #
117 ###############################################################################
118
119 --echo ## Setting session value of variable to 26. No warning should appear here ##
120 --echo ## because the value after concatination is less than 30 ##
121 SET @@session.group_concat_max_len = 26;
122
123 --echo ## Verifying value of name column, it should not give warning now ##
124 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
125
126
127 ############################################################
128 # Disconnecting all connection & dropping table #
129 ############################################################
130
131 --echo ## Dropping table t1 ##
132 DROP TABLE t1;
133
134 disconnect test_con2;
135 disconnect test_con1;
136
137 connection default;
138
139 CREATE TABLE t1(val VARCHAR(100) PRIMARY KEY) CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci;
140 INSERT INTO t1 VALUES('bar');
141 INSERT INTO t1 VALUES('foo');
142
143 SET group_concat_max_len = 1073741823;
144 SHOW VARIABLES LIKE 'group_concat_max_len';
145 SELECT GROUP_CONCAT(val) AS simple FROM t1;
146 SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
147
148 SET group_concat_max_len = 1073741824;
149 SHOW VARIABLES LIKE 'group_concat_max_len';
150 SELECT GROUP_CONCAT(val) AS simple FROM t1;
151 SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
152
153 SET group_concat_max_len = 1073741825;
154 SHOW VARIABLES LIKE 'group_concat_max_len';
155 SELECT GROUP_CONCAT(val) AS simple FROM t1;
156 SELECT * FROM ( SELECT GROUP_CONCAT(val) AS nested FROM t1) As tmp;
157
158 DROP TABLE t1;
159
160 SET @@global.group_concat_max_len = @save;