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 --echo ## Creating two new connections ##
70 connect (test_con1,localhost,root,,);
71 connect (test_con2,localhost,root,,);
72
73
74 --echo '#--------------------FN_DYNVARS_034_02-------------------------#'
75 ###############################################################################
76 # Verifying initial behavior of variable by concatinating values greater than 4
77 ###############################################################################
78
79 --echo ## Connecting with test_con1 ##
80 connection test_con1;
81
82 --source include/turn_off_only_full_group_by.inc
83
84 --echo ## Accessing data and using group_concat on column whose value is greater than 4 ##
85 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
86
87 --echo ## Changing session value of variable and verifying its behavior, ##
88 --echo ## warning should come here ##
89
90 SET @@session.group_concat_max_len = 10;
91 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
92
93 --echo '#--------------------FN_DYNVARS_034_03-------------------------#'
94 ##############################################################################
95 # Verifying behavior of variable by increasing session value of variable #
96 ##############################################################################
97
98 --echo ## Connecting with new connection test_con2 ##
99 connection test_con2;
100
101 --source include/turn_off_only_full_group_by.inc
102
103 --echo ## Verifying initial value of variable. It should be 4 ##
104 SELECT @@session.group_concat_max_len = 4;
105
106 --echo ## Setting session value of variable to 20 and verifying variable is concating ##
107 --echo ## column's value to 20 or not ##
108 SET @@session.group_concat_max_len = 20;
109
110 --echo ## Verifying value of name column, it should not me more than 20 characters ##
111 --echo ## Warning should come here ##
112 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
113
114
115 --echo '#--------------------FN_DYNVARS_034_04-------------------------#'
116 ###############################################################################
117 # Verifying behavior of variable by increasing session value of variable #
118 # greater than the maximum concat length of name column #
119 ###############################################################################
120
121 --echo ## Setting session value of variable to 26. No warning should appear here ##
122 --echo ## because the value after concatination is less than 30 ##
123 SET @@session.group_concat_max_len = 26;
124
125 --echo ## Verifying value of name column, it should not give warning now ##
126 SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
127
128
129 ############################################################
130 # Disconnecting all connection & dropping table #
131 ############################################################
132
133 --echo ## Dropping table t1 ##
134 DROP TABLE t1;
135
136 --echo ## Disconnecting both the connection ##
137 disconnect test_con2;
138 disconnect test_con1;
139
140 connection default;
141
142 SET @@global.group_concat_max_len = @save;
143