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
|
############## 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 ##
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
--echo ## Changing session value of variable and verifying its behavior, ##
--echo ## warning should come here ##
SET @@session.group_concat_max_len = 10;
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
--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 ##
SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno;
--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;
SET @@global.group_concat_max_len = @save;
|
