Debian Codegraph @

mysql-test/suite/funcs_1/r/innodb_storedproc_10.result

raw
1
2 --source suite/funcs_1/storedproc/load_sp_tb.inc
3 --------------------------------------------------------------------------------
4
5 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
6 --------------------------------------------------------------------------------
7 DROP DATABASE IF EXISTS db_storedproc;
8 DROP DATABASE IF EXISTS db_storedproc_1;
9 CREATE DATABASE db_storedproc;
10 CREATE DATABASE db_storedproc_1;
11 USE db_storedproc;
12 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
13 engine = <engine_to_be_tested>;
14 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
15 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
16 engine = <engine_to_be_tested>;
17 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
18 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
19 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
20 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
21 engine = <engine_to_be_tested>;
22 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
23 USE db_storedproc_1;
24 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
25 engine = <engine_to_be_tested>;
26 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
27 USE db_storedproc;
28 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
29 engine = <engine_to_be_tested>;
30 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7;
31 Warnings:
32 Warning 1265 Data truncated for column 'f3' at row 1
33 Warning 1265 Data truncated for column 'f3' at row 2
34 Warning 1265 Data truncated for column 'f3' at row 3
35 Warning 1265 Data truncated for column 'f3' at row 4
36 Warning 1265 Data truncated for column 'f3' at row 5
37 Warning 1265 Data truncated for column 'f3' at row 6
38 Warning 1265 Data truncated for column 'f3' at row 7
39 Warning 1265 Data truncated for column 'f3' at row 8
40 Warning 1265 Data truncated for column 'f3' at row 9
41 Warning 1265 Data truncated for column 'f3' at row 10
42 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
43 engine = <engine_to_be_tested>;
44 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8;
45 Warnings:
46 Warning 1265 Data truncated for column 'f3' at row 1
47 Warning 1265 Data truncated for column 'f3' at row 2
48 Warning 1265 Data truncated for column 'f3' at row 3
49 Warning 1265 Data truncated for column 'f3' at row 4
50 Warning 1265 Data truncated for column 'f3' at row 5
51 Warning 1265 Data truncated for column 'f3' at row 6
52 Warning 1265 Data truncated for column 'f3' at row 7
53 Warning 1265 Data truncated for column 'f3' at row 8
54 Warning 1265 Data truncated for column 'f3' at row 9
55 Warning 1265 Data truncated for column 'f3' at row 10
56 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
57 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
58 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
59 engine = <engine_to_be_tested>;
60 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
61 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
62 engine = <engine_to_be_tested>;
63 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
64
65 Section 3.1.10 - CALL checks:
66 --------------------------------------------------------------------------------
67 USE db_storedproc;
68
69 Testcase 3.1.10.2 + 3.1.10.5:
70 -----------------------------
71
72 2. Ensure that a procedure cannot be called if the appropriate privileges do not
73 exist.
74 5. Ensure that a function cannot be executed if the appropriate privileges do
75 not exist.
76 --------------------------------------------------------------------------------
77 DROP PROCEDURE IF EXISTS sp31102;
78 DROP FUNCTION IF EXISTS fn31105;
79 create user 'user_1'@'localhost';
80 create user 'user_2'@'localhost';
81 GRANT CREATE ROUTINE ON db_storedproc.* TO 'user_1'@'localhost';
82 GRANT SELECT ON db_storedproc.* TO 'user_2'@'localhost';
83 FLUSH PRIVILEGES;
84 connect user2_1, localhost, user_1, , db_storedproc;
85
86 user_1@localhost db_storedproc
87 CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
88 BEGIN
89 SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
90 END//
91 CREATE FUNCTION fn31105(n INT) RETURNS INT
92 BEGIN
93 DECLARE res INT;
94 SET res = n * n;
95 RETURN res;
96 END//
97 disconnect user2_1;
98 connect user2_2, localhost, user_2, , db_storedproc;
99
100 user_2@localhost db_storedproc
101 CALL sp31102();
102 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
103 SELECT fn31105( 9 );
104 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
105 connection default;
106 USE db_storedproc;
107
108 root@localhost db_storedproc
109 CALL sp31102();
110 f1 f2 f3 f4 f5 f6
111 a` a` 1000-01-01 -5000 a` -5000
112 SELECT fn31105( 9 );
113 fn31105( 9 )
114 81
115 GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
116 FLUSH PRIVILEGES;
117 disconnect user2_2;
118 connect user2_3, localhost, user_2, , db_storedproc;
119
120 user_2@localhost db_storedproc
121 CALL sp31102();
122 f1 f2 f3 f4 f5 f6
123 a` a` 1000-01-01 -5000 a` -5000
124 SELECT fn31105( 9 );
125 fn31105( 9 )
126 81
127 disconnect user2_3;
128 connection default;
129 USE db_storedproc;
130
131 root@localhost db_storedproc
132 REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
133 FLUSH PRIVILEGES;
134 CALL sp31102();
135 f1 f2 f3 f4 f5 f6
136 a` a` 1000-01-01 -5000 a` -5000
137 SELECT fn31105( 9 );
138 fn31105( 9 )
139 81
140 connect user2_4, localhost, user_2, , db_storedproc;
141
142 user_2@localhost db_storedproc
143 CALL sp31102();
144 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
145 SELECT fn31105( 9 );
146 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
147 disconnect user2_4;
148 connection default;
149 USE db_storedproc;
150
151 root@localhost db_storedproc
152 DROP PROCEDURE sp31102;
153 DROP FUNCTION fn31105;
154 DROP USER 'user_1'@'localhost';
155 DROP USER 'user_2'@'localhost';
156
157 Testcase 3.1.10.3:
158 ------------------
159
160 Ensure that a function can never be called.
161 --------------------------------------------------------------------------------
162 DROP FUNCTION IF EXISTS fn1;
163 CREATE FUNCTION fn1(a int) returns int
164 BEGIN
165 set @b = 0.9 * a;
166 return @b;
167 END//
168 CALL fn1();
169 ERROR 42000: PROCEDURE db_storedproc.fn1 does not exist
170 DROP FUNCTION fn1;
171
172 Testcase 3.1.10.6:
173 ------------------
174
175 Ensure that a procedure can never be executed.
176 --------------------------------------------------------------------------------
177 DROP PROCEDURE IF EXISTS sp1;
178 DROP FUNCTION IF EXISTS sp1;
179 CREATE PROCEDURE sp1()
180 BEGIN
181 SELECT * from t10;
182 END//
183 SELECT sp1();
184 ERROR 42000: FUNCTION db_storedproc.sp1 does not exist
185 DROP PROCEDURE sp1;
186
187 Testcase 3.1.10.7:
188 ------------------
189
190 Ensure that the ROW_COUNT() SQL function always returns the correct number of
191 rows affected by the execution of a stored procedure.
192 --------------------------------------------------------------------------------
193 DROP PROCEDURE IF EXISTS sp_ins_1;
194 DROP PROCEDURE IF EXISTS sp_ins_3;
195 DROP PROCEDURE IF EXISTS sp_upd;
196 DROP PROCEDURE IF EXISTS sp_ins_upd;
197 DROP PROCEDURE IF EXISTS sp_del;
198 DROP PROCEDURE IF EXISTS sp_with_rowcount;
199 CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
200 INSERT INTO temp SELECT * FROM t10;
201 CREATE PROCEDURE sp_ins_1()
202 BEGIN
203 INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
204 END//
205 CREATE PROCEDURE sp_ins_3()
206 BEGIN
207 INSERT INTO temp VALUES ('abc', 'xyz', '19490523', 100, 'uvw', 1000);
208 INSERT INTO temp VALUES ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
209 INSERT INTO temp VALUES ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
210 END//
211 CREATE PROCEDURE sp_upd()
212 BEGIN
213 UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
214 END//
215 CREATE PROCEDURE sp_ins_upd()
216 BEGIN
217 BEGIN
218 INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
219 INSERT INTO temp VALUES ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
220 INSERT INTO temp VALUES ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
221 INSERT INTO temp VALUES ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
222 END;
223 SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
224 UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
225 END//
226 CREATE PROCEDURE sp_del()
227 BEGIN
228 DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
229 END//
230 CREATE PROCEDURE sp_with_rowcount()
231 BEGIN
232 BEGIN
233 INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
234 ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
235 ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
236 ('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
237 END;
238 SELECT row_count() AS 'row_count() after insert';
239 SELECT row_count() AS 'row_count() after select row_count()';
240 SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
241 UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
242 SELECT row_count() AS 'row_count() after update';
243 SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
244 DELETE FROM temp WHERE temp.f1 = 'updated_2';
245 SELECT row_count() AS 'row_count() after delete';
246 END//
247 CALL sp_ins_1();
248 SELECT row_count();
249 row_count()
250 1
251 SELECT * FROM temp;
252 f1 f2 f3 f4 f5 f6
253 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
254 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
255 a` a` 1000-01-01 -5000 a` -5000
256 aaa aaa 1000-01-02 -4999 aaa -4999
257 abaa abaa 1000-01-03 -4998 abaa -4998
258 abc abc 2005-10-03 100 uvw 1000
259 acaaa acaaa 1000-01-04 -4997 acaaa -4997
260 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
261 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
262 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
263 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
264 CALL sp_ins_3();
265 SELECT row_count();
266 row_count()
267 3
268 SELECT * FROM temp;
269 f1 f2 f3 f4 f5 f6
270 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
271 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
272 a` a` 1000-01-01 -5000 a` -5000
273 aaa aaa 1000-01-02 -4999 aaa -4999
274 abaa abaa 1000-01-03 -4998 abaa -4998
275 abc abc 2005-10-03 100 uvw 1000
276 abc xyz 1949-05-23 100 uvw 1000
277 abc xyz 1989-11-09 100 uvw 1000
278 abc xyz 2005-10-24 100 uvw 1000
279 acaaa acaaa 1000-01-04 -4997 acaaa -4997
280 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
281 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
282 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
283 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
284 CALL sp_upd();
285 SELECT row_count();
286 row_count()
287 4
288 SELECT * FROM temp;
289 f1 f2 f3 f4 f5 f6
290 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
291 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
292 a` a` 1000-01-01 -5000 a` -5000
293 aaa aaa 1000-01-02 -4999 aaa -4999
294 abaa abaa 1000-01-03 -4998 abaa -4998
295 acaaa acaaa 1000-01-04 -4997 acaaa -4997
296 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
297 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
298 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
299 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
300 updated abc 2005-10-03 100 uvw 1000
301 updated xyz 1949-05-23 100 uvw 1000
302 updated xyz 1989-11-09 100 uvw 1000
303 updated xyz 2005-10-24 100 uvw 1000
304 CALL sp_ins_upd();
305 COUNT( f1 ) f1
306 1 a_aaaaaaaaa
307 1 a`
308 1 a^aaaaaaaa
309 1 aaa
310 1 abaa
311 1 acaaa
312 1 adaaaa
313 1 aeaaaaa
314 1 afaaaaaa
315 1 agaaaaaaa
316 4 qwe
317 4 updated
318 SELECT row_count();
319 row_count()
320 7
321 SELECT * FROM temp;
322 f1 f2 f3 f4 f5 f6
323 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
324 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
325 a` a` 1000-01-01 -5000 a` -5000
326 aaa aaa 1000-01-02 -4999 aaa -4999
327 abaa abaa 1000-01-03 -4998 abaa -4998
328 acaaa acaaa 1000-01-04 -4997 acaaa -4997
329 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
330 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
331 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
332 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
333 qwe xyz 1998-03-26 100 uvw 1000
334 updated abc 2005-10-03 100 uvw 1000
335 updated xyz 1949-05-23 100 uvw 1000
336 updated xyz 1989-11-09 100 uvw 1000
337 updated xyz 2005-10-24 100 uvw 1000
338 updated_2 abc 1989-11-09 100 uvw 1000
339 updated_2 abc 2000-11-09 100 uvw 1000
340 updated_2 abc 2005-11-07 100 uvw 1000
341 CALL sp_del();
342 SELECT row_count();
343 row_count()
344 4
345 SELECT * FROM temp;
346 f1 f2 f3 f4 f5 f6
347 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
348 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
349 a` a` 1000-01-01 -5000 a` -5000
350 aaa aaa 1000-01-02 -4999 aaa -4999
351 abaa abaa 1000-01-03 -4998 abaa -4998
352 acaaa acaaa 1000-01-04 -4997 acaaa -4997
353 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
354 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
355 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
356 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
357 updated abc 2005-10-03 100 uvw 1000
358 updated xyz 1949-05-23 100 uvw 1000
359 updated xyz 1989-11-09 100 uvw 1000
360 updated xyz 2005-10-24 100 uvw 1000
361 DELETE FROM temp;
362 CALL sp_with_rowcount();
363 row_count() after insert
364 4
365 row_count() after select row_count()
366 -1
367 f1 f2 f3
368 qwe abc 1989-11-09
369 qwe abc 2000-11-09
370 qwe xyz 1998-03-26
371 qwe xyz 2005-11-07
372 row_count() after update
373 2
374 f1 f2 f3
375 qwe xyz 1998-03-26
376 qwe xyz 2005-11-07
377 updated_2 abc 1989-11-09
378 updated_2 abc 2000-11-09
379 row_count() after delete
380 2
381 SELECT row_count();
382 row_count()
383 8
384 SELECT * FROM temp;
385 f1 f2 f3 f4 f5 f6
386 qwe xyz 1998-03-26 100 uvw 1000
387 qwe xyz 2005-11-07 100 uvw 1000
388 DROP PROCEDURE sp_ins_1;
389 DROP PROCEDURE sp_ins_3;
390 DROP PROCEDURE sp_upd;
391 DROP PROCEDURE sp_ins_upd;
392 DROP PROCEDURE sp_del;
393 DROP PROCEDURE sp_with_rowcount;
394 DROP TABLE temp;
395
396 Testcase 3.1.10.8:
397 ------------------
398
399 Ensure that the mysql_affected_rows() C API function always returns the correct
400 number of rows affected by the execution of a stored procedure.
401 --------------------------------------------------------------------------------
402
403 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
404 --------------------------------------------------------------------------------
405 DROP DATABASE IF EXISTS db_storedproc;
406 DROP DATABASE IF EXISTS db_storedproc_1;
407
408 . +++ END OF SCRIPT +++
409 --------------------------------------------------------------------------------