Debian Codegraph @

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

raw
1 ############# mysql-test\t\sql_select_limit_func.test #####################
2 # #
3 # Variable Name: sql_select_limit #
4 # Scope: GLOBAL, SESSION #
5 # Access Type: Dynamic #
6 # Data Type: NUMERIC #
7 # Default Value: 4294967295 #
8 # Values: 1-4294967295 #
9 # #
10 # #
11 # Creation Date: 2008-02-25 #
12 # Author: Sharique Abdullah #
13 # #
14 # Description: Test Cases of Dynamic System Variable "sql_select_limit" #
15 # that checks behavior of this variable in the following ways#
16 # * Functionality based on different values #
17 # #
18 # Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html #
19 # #
20 ###########################################################################
21
22 --echo ** Setup **
23 --echo
24 #
25 # Setup
26 #
27
28 SET @session_sql_select_limit = @@SESSION.sql_select_limit;
29 SET @global_sql_select_limit = @@GLOBAL.sql_select_limit;
30
31 #
32 # Creating tables
33 #
34
35 CREATE TEMPORARY TABLE t1(a int PRIMARY KEY, b varchar(20));
36 CREATE TEMPORARY TABLE t2(a int PRIMARY KEY, b varchar(20));
37 CREATE TEMPORARY TABLE t3(a int PRIMARY KEY, b varchar(20));
38
39 INSERT INTO t1 VALUES(1, 'val1');
40 INSERT INTO t1 VALUES(2, 'val2');
41 INSERT INTO t1 VALUES(3, 'val3');
42 INSERT INTO t1 VALUES(4, 'val4');
43 INSERT INTO t1 VALUES(5, 'val5');
44 INSERT INTO t1 VALUES(6, 'val6');
45 INSERT INTO t1 VALUES(7, 'val7');
46 INSERT INTO t1 VALUES(8, 'val8');
47 INSERT INTO t1 VALUES(9, 'val9');
48
49 INSERT INTO t2 VALUES(5, 'val5');
50 INSERT INTO t2 VALUES(6, 'val6');
51 INSERT INTO t2 VALUES(7, 'val7');
52 INSERT INTO t2 VALUES(8, 'val8');
53 INSERT INTO t2 VALUES(9, 'val9');
54
55
56
57 --echo '#-----------------------------FN_DYNVARS_165_01-----------------#'
58 --echo
59 --echo Value DEFAULT
60 --echo
61 #
62 # Value DEFAULT
63 #
64
65 --enable_info
66
67 SET SESSION sql_select_limit = DEFAULT;
68
69 SELECT * FROM t1;
70 --echo Expecting affected rows: 9
71
72 SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
73 --echo Expecting affected rows: 5
74
75
76 --echo '#-----------------------------FN_DYNVARS_165_02---------------#'
77 #
78 # Small value
79 #
80
81 SET SESSION sql_select_limit = 2;
82
83 SELECT * FROM t1;
84 --echo Expecting affected rows: 2
85
86 SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
87 --echo Expecting affected rows: 2
88
89 SET SESSION sql_select_limit = 0;
90
91 SELECT * FROM t1;
92 --echo Expecting affected rows: 0
93
94 SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
95 --echo Expecting affected rows: 0
96
97 --echo '#-----------------------------FN_DYNVARS_165_03---------------#'
98 #
99 # Small value with LIMIT Clause
100 #
101
102 SET SESSION sql_select_limit = 2;
103
104 SELECT * FROM t1 LIMIT 4;
105 --echo Expecting affected rows: 4
106
107 SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a LIMIT 3;
108 --echo Expecting affected rows: 3
109
110 --echo '#-----------------------------FN_DYNVARS_165_04----------------#'
111 #
112 # Small value with Store procedure
113 #
114
115 SET SESSION sql_select_limit = 2;
116
117 delimiter |;
118
119 CREATE PROCEDURE TestProc()
120 BEGIN
121 SELECT * FROM t1;
122 END|
123 delimiter ;|
124
125 call TestProc();
126 --echo Expecting affected rows: 9
127
128 DROP PROCEDURE TestProc;
129
130 delimiter |;
131
132 CREATE PROCEDURE TestProc()
133 BEGIN
134 SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;
135 END|
136 delimiter ;|
137
138 call TestProc();
139 --echo Expecting affected rows: 5
140
141 DROP PROCEDURE TestProc;
142
143 --echo '#-----------------------------FN_DYNVARS_165_05-----------------#'
144 #
145 # Small value with Non result producing SELECT statements
146 #
147
148 SET SESSION sql_select_limit = 2;
149
150 CREATE TEMPORARY TABLE t4(a int PRIMARY KEY, b varchar(20)) SELECT * FROM t2;
151 --echo Expecting Records: 5
152
153 INSERT INTO t3 SELECT * FROM t1;
154 --echo Expecting Records: 9
155
156 --echo '#-----------------------------FN_DYNVARS_165_06---------------------#'
157 #
158 # Session data integrity check & GLOBAL Value check
159 #
160 --disable_info
161
162 SET GLOBAL sql_select_limit = 2;
163
164 --echo ** Connecting con_int2 using root **
165 connect (con_int1,localhost,root,,);
166
167 --echo ** Connection con_int1 **
168 connection con_int1;
169 SELECT @@SESSION.sql_select_limit;
170 --echo 2 Expected
171
172 SET SESSION sql_select_limit = 10;
173
174 --echo ** Connecting con_int2 using root **
175 connect (con_int2,localhost,root,,);
176
177 --echo ** Connection con_int2 **
178 connection con_int2;
179 SELECT @@SESSION.sql_select_limit;
180 --echo 2 Expected
181
182 SET SESSION sql_select_limit = 12;
183
184 --echo ** Connection con_int2 **
185 connection con_int2;
186 SELECT @@SESSION.sql_select_limit;
187 --echo 12 Expected
188
189 --echo ** Connection con_int1 **
190 connection con_int1;
191 SELECT @@SESSION.sql_select_limit;
192 --echo 10 Expected
193
194 SELECT @@GLOBAL.sql_select_limit;
195 --echo 2 Expected
196
197 --echo ** Connection default **
198 connection default;
199
200 --echo Disconnecting Connections con_int1, con_int2
201 disconnect con_int1;
202 disconnect con_int2;
203
204
205 #
206 # Cleanup
207 #
208
209 --disable_info
210
211 SET @@SESSION.sql_select_limit = @session_sql_select_limit;
212 SET @@GLOBAL.sql_select_limit = @global_sql_select_limit;
213
214 DROP TABLE t1;
215 DROP TABLE t2;
216 DROP TABLE t3;
217 DROP TABLE t4;