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';
85
connect(localhost,user_1,,db_storedproc,MYSQL_PORT,MYSQL_SOCK);
87
user_1@localhost db_storedproc
88
CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
90
SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
92
CREATE FUNCTION fn31105(n INT) RETURNS INT
98
connect(localhost,user_2,,db_storedproc,MYSQL_PORT,MYSQL_SOCK);
100
user_2@localhost db_storedproc
102
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
104
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
108
root@localhost db_storedproc
111
a` a` 1000-01-01 -5000 a` -5000
115
GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
117
connect(localhost,user_2,,db_storedproc,MYSQL_PORT,MYSQL_SOCK);
119
user_2@localhost db_storedproc
122
a` a` 1000-01-01 -5000 a` -5000
129
root@localhost db_storedproc
130
REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
134
a` a` 1000-01-01 -5000 a` -5000
138
connect(localhost,user_2,,db_storedproc,MYSQL_PORT,MYSQL_SOCK);
140
user_2@localhost db_storedproc
142
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
144
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
147
root@localhost db_storedproc
148
DROP PROCEDURE sp31102;
149
DROP FUNCTION fn31105;
150
DROP USER 'user_1'@'localhost';
151
DROP USER 'user_2'@'localhost';
156
Ensure that a function can never be called.
157
--------------------------------------------------------------------------------
158
DROP FUNCTION IF EXISTS fn1;
159
CREATE FUNCTION fn1(a int) returns int
165
ERROR 42000: PROCEDURE db_storedproc.fn1 does not exist
171
Ensure that a procedure can never be executed.
172
--------------------------------------------------------------------------------
173
DROP PROCEDURE IF EXISTS sp1;
174
DROP FUNCTION IF EXISTS sp1;
175
CREATE PROCEDURE sp1()
180
ERROR 42000: FUNCTION db_storedproc.sp1 does not exist
186
Ensure that the ROW_COUNT() SQL function always returns the correct number of
187
rows affected by the execution of a stored procedure.
188
--------------------------------------------------------------------------------
189
DROP PROCEDURE IF EXISTS sp_ins_1;
190
DROP PROCEDURE IF EXISTS sp_ins_3;
191
DROP PROCEDURE IF EXISTS sp_upd;
192
DROP PROCEDURE IF EXISTS sp_ins_upd;
193
DROP PROCEDURE IF EXISTS sp_del;
194
DROP PROCEDURE IF EXISTS sp_with_rowcount;
195
CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
196
INSERT INTO temp SELECT * FROM t10;
197
CREATE PROCEDURE sp_ins_1()
199
INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
201
CREATE PROCEDURE sp_ins_3()
203
INSERT INTO temp VALUES ('abc', 'xyz', '19490523', 100, 'uvw', 1000);
204
INSERT INTO temp VALUES ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
205
INSERT INTO temp VALUES ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
207
CREATE PROCEDURE sp_upd()
209
UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
211
CREATE PROCEDURE sp_ins_upd()
214
INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
215
INSERT INTO temp VALUES ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
216
INSERT INTO temp VALUES ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
217
INSERT INTO temp VALUES ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
219
SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
220
UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
222
CREATE PROCEDURE sp_del()
224
DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
226
CREATE PROCEDURE sp_with_rowcount()
229
INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
230
('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
231
('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
232
('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
234
SELECT row_count() AS 'row_count() after insert';
235
SELECT row_count() AS 'row_count() after select row_count()';
236
SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
237
UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
238
SELECT row_count() AS 'row_count() after update';
239
SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
240
DELETE FROM temp WHERE temp.f1 = 'updated_2';
241
SELECT row_count() AS 'row_count() after delete';
249
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
250
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
251
a` a` 1000-01-01 -5000 a` -5000
252
aaa aaa 1000-01-02 -4999 aaa -4999
253
abaa abaa 1000-01-03 -4998 abaa -4998
254
abc abc 2005-10-03 100 uvw 1000
255
acaaa acaaa 1000-01-04 -4997 acaaa -4997
256
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
257
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
258
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
259
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
266
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
267
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
268
a` a` 1000-01-01 -5000 a` -5000
269
aaa aaa 1000-01-02 -4999 aaa -4999
270
abaa abaa 1000-01-03 -4998 abaa -4998
271
abc abc 2005-10-03 100 uvw 1000
272
abc xyz 1949-05-23 100 uvw 1000
273
abc xyz 1989-11-09 100 uvw 1000
274
abc xyz 2005-10-24 100 uvw 1000
275
acaaa acaaa 1000-01-04 -4997 acaaa -4997
276
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
277
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
278
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
279
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
286
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
287
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
288
a` a` 1000-01-01 -5000 a` -5000
289
aaa aaa 1000-01-02 -4999 aaa -4999
290
abaa abaa 1000-01-03 -4998 abaa -4998
291
acaaa acaaa 1000-01-04 -4997 acaaa -4997
292
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
293
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
294
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
295
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
296
updated abc 2005-10-03 100 uvw 1000
297
updated xyz 1949-05-23 100 uvw 1000
298
updated xyz 1989-11-09 100 uvw 1000
299
updated xyz 2005-10-24 100 uvw 1000
319
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
320
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
321
a` a` 1000-01-01 -5000 a` -5000
322
aaa aaa 1000-01-02 -4999 aaa -4999
323
abaa abaa 1000-01-03 -4998 abaa -4998
324
acaaa acaaa 1000-01-04 -4997 acaaa -4997
325
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
326
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
327
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
328
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
329
qwe xyz 1998-03-26 100 uvw 1000
330
updated abc 2005-10-03 100 uvw 1000
331
updated xyz 1949-05-23 100 uvw 1000
332
updated xyz 1989-11-09 100 uvw 1000
333
updated xyz 2005-10-24 100 uvw 1000
334
updated_2 abc 1989-11-09 100 uvw 1000
335
updated_2 abc 2000-11-09 100 uvw 1000
336
updated_2 abc 2005-11-07 100 uvw 1000
343
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
344
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
345
a` a` 1000-01-01 -5000 a` -5000
346
aaa aaa 1000-01-02 -4999 aaa -4999
347
abaa abaa 1000-01-03 -4998 abaa -4998
348
acaaa acaaa 1000-01-04 -4997 acaaa -4997
349
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
350
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
351
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
352
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
353
updated abc 2005-10-03 100 uvw 1000
354
updated xyz 1949-05-23 100 uvw 1000
355
updated xyz 1989-11-09 100 uvw 1000
356
updated xyz 2005-10-24 100 uvw 1000
358
CALL sp_with_rowcount();
359
row_count() after insert
361
row_count() after select row_count()
368
row_count() after update
373
updated_2 abc 1989-11-09
374
updated_2 abc 2000-11-09
375
row_count() after delete
382
qwe xyz 1998-03-26 100 uvw 1000
383
qwe xyz 2005-11-07 100 uvw 1000
384
DROP PROCEDURE sp_ins_1;
385
DROP PROCEDURE sp_ins_3;
386
DROP PROCEDURE sp_upd;
387
DROP PROCEDURE sp_ins_upd;
388
DROP PROCEDURE sp_del;
389
DROP PROCEDURE sp_with_rowcount;
395
Ensure that the mysql_affected_rows() C API function always returns the correct
396
number of rows affected by the execution of a stored procedure.
397
--------------------------------------------------------------------------------
399
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
400
--------------------------------------------------------------------------------
401
DROP DATABASE IF EXISTS db_storedproc;
402
DROP DATABASE IF EXISTS db_storedproc_1;
404
. +++ END OF SCRIPT +++
405
--------------------------------------------------------------------------------