2
--source suite/funcs_1/storedproc/load_sp_tb.inc
3
--------------------------------------------------------------------------------
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;
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;
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;
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' into table t7;
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' into table t8;
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;
65
Section 3.1.10 - CALL checks:
66
--------------------------------------------------------------------------------
69
Testcase 3.1.10.2 + 3.1.10.5:
70
-----------------------------
72
2. Ensure that a procedure cannot be called if the appropriate privileges do not
74
5. Ensure that a function cannot be executed if the appropriate privileges do
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';
85
user_1@localhost db_storedproc
86
CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
88
SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
90
CREATE FUNCTION fn31105(n INT) RETURNS INT
97
user_2@localhost db_storedproc
99
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
101
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
105
root@localhost db_storedproc
108
a` a` 1000-01-01 -5000 a` -5000
112
GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
115
user_2@localhost db_storedproc
118
a` a` 1000-01-01 -5000 a` -5000
125
root@localhost db_storedproc
126
REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
130
a` a` 1000-01-01 -5000 a` -5000
135
user_2@localhost db_storedproc
137
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
139
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
142
root@localhost db_storedproc
143
DROP PROCEDURE sp31102;
144
DROP FUNCTION fn31105;
145
DROP USER 'user_1'@'localhost';
146
DROP USER 'user_2'@'localhost';
151
Ensure that a function can never be called.
152
--------------------------------------------------------------------------------
153
DROP FUNCTION IF EXISTS fn1;
154
CREATE FUNCTION fn1(a int) returns int
160
ERROR 42000: PROCEDURE db_storedproc.fn1 does not exist
166
Ensure that a procedure can never be executed.
167
--------------------------------------------------------------------------------
168
DROP PROCEDURE IF EXISTS sp1;
169
DROP FUNCTION IF EXISTS sp1;
170
CREATE PROCEDURE sp1()
175
ERROR 42000: FUNCTION db_storedproc.sp1 does not exist
181
Ensure that the ROW_COUNT() SQL function always returns the correct number of
182
rows affected by the execution of a stored procedure.
183
--------------------------------------------------------------------------------
184
DROP PROCEDURE IF EXISTS sp_ins_1;
185
DROP PROCEDURE IF EXISTS sp_ins_3;
186
DROP PROCEDURE IF EXISTS sp_upd;
187
DROP PROCEDURE IF EXISTS sp_ins_upd;
188
DROP PROCEDURE IF EXISTS sp_del;
189
DROP PROCEDURE IF EXISTS sp_with_rowcount;
190
CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
191
INSERT INTO temp SELECT * FROM t10;
192
CREATE PROCEDURE sp_ins_1()
194
INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
196
CREATE PROCEDURE sp_ins_3()
198
INSERT INTO temp VALUES ('abc', 'xyz', '19490523', 100, 'uvw', 1000);
199
INSERT INTO temp VALUES ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
200
INSERT INTO temp VALUES ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
202
CREATE PROCEDURE sp_upd()
204
UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
206
CREATE PROCEDURE sp_ins_upd()
209
INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
210
INSERT INTO temp VALUES ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
211
INSERT INTO temp VALUES ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
212
INSERT INTO temp VALUES ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
214
SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
215
UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
217
CREATE PROCEDURE sp_del()
219
DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
221
CREATE PROCEDURE sp_with_rowcount()
224
INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
225
('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
226
('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
227
('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
229
SELECT row_count() AS 'row_count() after insert';
230
SELECT row_count() AS 'row_count() after select row_count()';
231
SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
232
UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
233
SELECT row_count() AS 'row_count() after update';
234
SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
235
DELETE FROM temp WHERE temp.f1 = 'updated_2';
236
SELECT row_count() AS 'row_count() after delete';
244
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
245
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
246
a` a` 1000-01-01 -5000 a` -5000
247
aaa aaa 1000-01-02 -4999 aaa -4999
248
abaa abaa 1000-01-03 -4998 abaa -4998
249
abc abc 2005-10-03 100 uvw 1000
250
acaaa acaaa 1000-01-04 -4997 acaaa -4997
251
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
252
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
253
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
254
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
261
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
262
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
263
a` a` 1000-01-01 -5000 a` -5000
264
aaa aaa 1000-01-02 -4999 aaa -4999
265
abaa abaa 1000-01-03 -4998 abaa -4998
266
abc abc 2005-10-03 100 uvw 1000
267
abc xyz 1949-05-23 100 uvw 1000
268
abc xyz 1989-11-09 100 uvw 1000
269
abc xyz 2005-10-24 100 uvw 1000
270
acaaa acaaa 1000-01-04 -4997 acaaa -4997
271
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
272
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
273
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
274
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
281
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
282
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
283
a` a` 1000-01-01 -5000 a` -5000
284
aaa aaa 1000-01-02 -4999 aaa -4999
285
abaa abaa 1000-01-03 -4998 abaa -4998
286
acaaa acaaa 1000-01-04 -4997 acaaa -4997
287
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
288
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
289
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
290
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
291
updated abc 2005-10-03 100 uvw 1000
292
updated xyz 1949-05-23 100 uvw 1000
293
updated xyz 1989-11-09 100 uvw 1000
294
updated xyz 2005-10-24 100 uvw 1000
314
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
315
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
316
a` a` 1000-01-01 -5000 a` -5000
317
aaa aaa 1000-01-02 -4999 aaa -4999
318
abaa abaa 1000-01-03 -4998 abaa -4998
319
acaaa acaaa 1000-01-04 -4997 acaaa -4997
320
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
321
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
322
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
323
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
324
qwe xyz 1998-03-26 100 uvw 1000
325
updated abc 2005-10-03 100 uvw 1000
326
updated xyz 1949-05-23 100 uvw 1000
327
updated xyz 1989-11-09 100 uvw 1000
328
updated xyz 2005-10-24 100 uvw 1000
329
updated_2 abc 1989-11-09 100 uvw 1000
330
updated_2 abc 2000-11-09 100 uvw 1000
331
updated_2 abc 2005-11-07 100 uvw 1000
338
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
339
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
340
a` a` 1000-01-01 -5000 a` -5000
341
aaa aaa 1000-01-02 -4999 aaa -4999
342
abaa abaa 1000-01-03 -4998 abaa -4998
343
acaaa acaaa 1000-01-04 -4997 acaaa -4997
344
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
345
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
346
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
347
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
348
updated abc 2005-10-03 100 uvw 1000
349
updated xyz 1949-05-23 100 uvw 1000
350
updated xyz 1989-11-09 100 uvw 1000
351
updated xyz 2005-10-24 100 uvw 1000
353
CALL sp_with_rowcount();
354
row_count() after insert
356
row_count() after select row_count()
363
row_count() after update
368
updated_2 abc 1989-11-09
369
updated_2 abc 2000-11-09
370
row_count() after delete
377
qwe xyz 1998-03-26 100 uvw 1000
378
qwe xyz 2005-11-07 100 uvw 1000
379
DROP PROCEDURE sp_ins_1;
380
DROP PROCEDURE sp_ins_3;
381
DROP PROCEDURE sp_upd;
382
DROP PROCEDURE sp_ins_upd;
383
DROP PROCEDURE sp_del;
384
DROP PROCEDURE sp_with_rowcount;
390
Ensure that the mysql_affected_rows() C API function always returns the correct
391
number of rows affected by the execution of a stored procedure.
392
--------------------------------------------------------------------------------
394
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
395
--------------------------------------------------------------------------------
396
DROP DATABASE IF EXISTS db_storedproc;
397
DROP DATABASE IF EXISTS db_storedproc_1;
399
. +++ END OF SCRIPT +++
400
--------------------------------------------------------------------------------