1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
3 --source suite/funcs_1/storedproc/load_sp_tb.inc
4 --------------------------------------------------------------------------------
6 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
7 --------------------------------------------------------------------------------
8 DROP DATABASE IF EXISTS db_storedproc;
9 DROP DATABASE IF EXISTS db_storedproc_1;
10 CREATE DATABASE db_storedproc;
11 CREATE DATABASE db_storedproc_1;
13 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
14 engine = <engine_to_be_tested>;
15 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
16 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
17 engine = <engine_to_be_tested>;
18 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
19 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
20 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
21 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
22 engine = <engine_to_be_tested>;
23 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
25 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
26 engine = <engine_to_be_tested>;
27 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
29 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
30 engine = <engine_to_be_tested>;
31 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
33 Warning 1265 Data truncated for column 'f3' at row 1
34 Warning 1265 Data truncated for column 'f3' at row 2
35 Warning 1265 Data truncated for column 'f3' at row 3
36 Warning 1265 Data truncated for column 'f3' at row 4
37 Warning 1265 Data truncated for column 'f3' at row 5
38 Warning 1265 Data truncated for column 'f3' at row 6
39 Warning 1265 Data truncated for column 'f3' at row 7
40 Warning 1265 Data truncated for column 'f3' at row 8
41 Warning 1265 Data truncated for column 'f3' at row 9
42 Warning 1265 Data truncated for column 'f3' at row 10
43 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
44 engine = <engine_to_be_tested>;
45 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
47 Warning 1265 Data truncated for column 'f3' at row 1
48 Warning 1265 Data truncated for column 'f3' at row 2
49 Warning 1265 Data truncated for column 'f3' at row 3
50 Warning 1265 Data truncated for column 'f3' at row 4
51 Warning 1265 Data truncated for column 'f3' at row 5
52 Warning 1265 Data truncated for column 'f3' at row 6
53 Warning 1265 Data truncated for column 'f3' at row 7
54 Warning 1265 Data truncated for column 'f3' at row 8
55 Warning 1265 Data truncated for column 'f3' at row 9
56 Warning 1265 Data truncated for column 'f3' at row 10
57 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
58 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
59 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
60 engine = <engine_to_be_tested>;
61 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
62 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
63 engine = <engine_to_be_tested>;
64 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
66 Section 3.1.10 - CALL checks:
67 --------------------------------------------------------------------------------
70 Testcase 3.1.10.2 + 3.1.10.5:
71 -----------------------------
73 2. Ensure that a procedure cannot be called if the appropriate privileges do not
75 5. Ensure that a function cannot be executed if the appropriate privileges do
77 --------------------------------------------------------------------------------
78 DROP PROCEDURE IF EXISTS sp31102;
79 DROP FUNCTION IF EXISTS fn31105;
80 create user 'user_1'@'localhost';
81 create user 'user_2'@'localhost';
82 GRANT CREATE ROUTINE ON db_storedproc.* TO 'user_1'@'localhost';
83 GRANT SELECT ON db_storedproc.* TO 'user_2'@'localhost';
86 user_1@localhost db_storedproc
87 CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
89 SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
91 CREATE FUNCTION fn31105(n INT) RETURNS INT
98 user_2@localhost db_storedproc
100 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
102 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
106 root@localhost db_storedproc
109 a` a` 1000-01-01 -5000 a` -5000
113 GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
116 user_2@localhost db_storedproc
119 a` a` 1000-01-01 -5000 a` -5000
126 root@localhost db_storedproc
127 REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
131 a` a` 1000-01-01 -5000 a` -5000
136 user_2@localhost db_storedproc
138 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
140 ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
143 root@localhost db_storedproc
144 DROP PROCEDURE sp31102;
145 DROP FUNCTION fn31105;
146 DROP USER 'user_1'@'localhost';
147 DROP USER 'user_2'@'localhost';
152 Ensure that a function can never be called.
153 --------------------------------------------------------------------------------
154 DROP FUNCTION IF EXISTS fn1;
155 CREATE FUNCTION fn1(a int) returns int
161 ERROR 42000: PROCEDURE db_storedproc.fn1 does not exist
167 Ensure that a procedure can never be executed.
168 --------------------------------------------------------------------------------
169 DROP PROCEDURE IF EXISTS sp1;
170 DROP FUNCTION IF EXISTS sp1;
171 CREATE PROCEDURE sp1()
176 ERROR 42000: FUNCTION db_storedproc.sp1 does not exist
182 Ensure that the ROW_COUNT() SQL function always returns the correct number of
183 rows affected by the execution of a stored procedure.
184 --------------------------------------------------------------------------------
185 DROP PROCEDURE IF EXISTS sp_ins_1;
186 DROP PROCEDURE IF EXISTS sp_ins_3;
187 DROP PROCEDURE IF EXISTS sp_upd;
188 DROP PROCEDURE IF EXISTS sp_ins_upd;
189 DROP PROCEDURE IF EXISTS sp_del;
190 DROP PROCEDURE IF EXISTS sp_with_rowcount;
191 CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
192 INSERT INTO temp SELECT * FROM t10;
193 CREATE PROCEDURE sp_ins_1()
195 INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
197 CREATE PROCEDURE sp_ins_3()
199 INSERT INTO temp VALUES ('abc', 'xyz', '19490523', 100, 'uvw', 1000);
200 INSERT INTO temp VALUES ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
201 INSERT INTO temp VALUES ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
203 CREATE PROCEDURE sp_upd()
205 UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
207 CREATE PROCEDURE sp_ins_upd()
210 INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
211 INSERT INTO temp VALUES ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
212 INSERT INTO temp VALUES ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
213 INSERT INTO temp VALUES ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
215 SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
216 UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
218 CREATE PROCEDURE sp_del()
220 DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
222 CREATE PROCEDURE sp_with_rowcount()
225 INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
226 ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
227 ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
228 ('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
230 SELECT row_count() AS 'row_count() after insert';
231 SELECT row_count() AS 'row_count() after select row_count()';
232 SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
233 UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
234 SELECT row_count() AS 'row_count() after update';
235 SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
236 DELETE FROM temp WHERE temp.f1 = 'updated_2';
237 SELECT row_count() AS 'row_count() after delete';
245 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
246 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
247 a` a` 1000-01-01 -5000 a` -5000
248 aaa aaa 1000-01-02 -4999 aaa -4999
249 abaa abaa 1000-01-03 -4998 abaa -4998
250 abc abc 2005-10-03 100 uvw 1000
251 acaaa acaaa 1000-01-04 -4997 acaaa -4997
252 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
253 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
254 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
255 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
262 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
263 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
264 a` a` 1000-01-01 -5000 a` -5000
265 aaa aaa 1000-01-02 -4999 aaa -4999
266 abaa abaa 1000-01-03 -4998 abaa -4998
267 abc abc 2005-10-03 100 uvw 1000
268 abc xyz 1949-05-23 100 uvw 1000
269 abc xyz 1989-11-09 100 uvw 1000
270 abc xyz 2005-10-24 100 uvw 1000
271 acaaa acaaa 1000-01-04 -4997 acaaa -4997
272 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
273 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
274 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
275 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
282 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
283 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
284 a` a` 1000-01-01 -5000 a` -5000
285 aaa aaa 1000-01-02 -4999 aaa -4999
286 abaa abaa 1000-01-03 -4998 abaa -4998
287 acaaa acaaa 1000-01-04 -4997 acaaa -4997
288 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
289 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
290 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
291 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
292 updated abc 2005-10-03 100 uvw 1000
293 updated xyz 1949-05-23 100 uvw 1000
294 updated xyz 1989-11-09 100 uvw 1000
295 updated xyz 2005-10-24 100 uvw 1000
315 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
316 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
317 a` a` 1000-01-01 -5000 a` -5000
318 aaa aaa 1000-01-02 -4999 aaa -4999
319 abaa abaa 1000-01-03 -4998 abaa -4998
320 acaaa acaaa 1000-01-04 -4997 acaaa -4997
321 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
322 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
323 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
324 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
325 qwe xyz 1998-03-26 100 uvw 1000
326 updated abc 2005-10-03 100 uvw 1000
327 updated xyz 1949-05-23 100 uvw 1000
328 updated xyz 1989-11-09 100 uvw 1000
329 updated xyz 2005-10-24 100 uvw 1000
330 updated_2 abc 1989-11-09 100 uvw 1000
331 updated_2 abc 2000-11-09 100 uvw 1000
332 updated_2 abc 2005-11-07 100 uvw 1000
339 a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
340 a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
341 a` a` 1000-01-01 -5000 a` -5000
342 aaa aaa 1000-01-02 -4999 aaa -4999
343 abaa abaa 1000-01-03 -4998 abaa -4998
344 acaaa acaaa 1000-01-04 -4997 acaaa -4997
345 adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
346 aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
347 afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
348 agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
349 updated abc 2005-10-03 100 uvw 1000
350 updated xyz 1949-05-23 100 uvw 1000
351 updated xyz 1989-11-09 100 uvw 1000
352 updated xyz 2005-10-24 100 uvw 1000
354 CALL sp_with_rowcount();
355 row_count() after insert
357 row_count() after select row_count()
364 row_count() after update
369 updated_2 abc 1989-11-09
370 updated_2 abc 2000-11-09
371 row_count() after delete
378 qwe xyz 1998-03-26 100 uvw 1000
379 qwe xyz 2005-11-07 100 uvw 1000
380 DROP PROCEDURE sp_ins_1;
381 DROP PROCEDURE sp_ins_3;
382 DROP PROCEDURE sp_upd;
383 DROP PROCEDURE sp_ins_upd;
384 DROP PROCEDURE sp_del;
385 DROP PROCEDURE sp_with_rowcount;
391 Ensure that the mysql_affected_rows() C API function always returns the correct
392 number of rows affected by the execution of a stored procedure.
393 --------------------------------------------------------------------------------
395 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
396 --------------------------------------------------------------------------------
397 DROP DATABASE IF EXISTS db_storedproc;
398 DROP DATABASE IF EXISTS db_storedproc_1;
400 . +++ END OF SCRIPT +++
401 --------------------------------------------------------------------------------