1
#### suite/funcs_1/storedproc/storedproc_10.inc
3
--source suite/funcs_1/storedproc/load_sp_tb.inc
5
# ==============================================================================
6
# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
10
## 1. Ensure that a properly defined procedure can always be called, assuming the appropriate privileges exist.
11
#- 2. Ensure that a procedure cannot be called if the appropriate privileges do not exist.
12
## 3. Ensure that a function can never be called.
13
## 4. Ensure that a properly defined function can always be executed, assuming the appropriate privileges exist.
14
#- 5. Ensure that a function cannot be executed if the appropriate privileges do not exist.
15
## 6. Ensure that a procedure can never be executed.
16
## 7. Ensure that the ROW_COUNT() SQL function always returns the correct number of rows affected by the execution of a stored procedure.
17
## 8. Ensure that the mysql_affected_rows() C API function always returns the correct number of rows affected by the execution of a stored procedure.
19
# ==============================================================================
20
let $message= Section 3.1.10 - CALL checks:;
21
--source include/show_msg80.inc
26
# ------------------------------------------------------------------------------
27
let $message= Testcase 3.1.10.2 + 3.1.10.5:;
28
--source include/show_msg.inc
30
2. Ensure that a procedure cannot be called if the appropriate privileges do not
32
5. Ensure that a function cannot be executed if the appropriate privileges do
34
--source include/show_msg80.inc
37
DROP PROCEDURE IF EXISTS sp31102;
38
DROP FUNCTION IF EXISTS fn31105;
42
create user 'user_1'@'localhost';
44
create user 'user_2'@'localhost';
46
GRANT CREATE ROUTINE ON db_storedproc.* TO 'user_1'@'localhost';
47
GRANT SELECT ON db_storedproc.* TO 'user_2'@'localhost';
50
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
51
connect (user2_1, localhost, user_1, , db_storedproc);
52
--source suite/funcs_1/include/show_connection.inc
55
CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
57
SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
62
CREATE FUNCTION fn31105(n INT) RETURNS INT
72
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
73
connect (user2_2, localhost, user_2, , db_storedproc);
74
--source suite/funcs_1/include/show_connection.inc
77
--error ER_PROCACCESS_DENIED_ERROR
81
# now 'add' EXECUTE to INVOKER
82
--echo connection default;
85
--source suite/funcs_1/include/show_connection.inc
86
# root can execute ...
89
GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
94
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
95
connect (user2_3, localhost, user_2, , db_storedproc);
96
--source suite/funcs_1/include/show_connection.inc
101
# now 'remove' SELECT from INVOKER
102
--echo connection default;
105
--source suite/funcs_1/include/show_connection.inc
106
REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
109
# root can still execute
113
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
114
connect (user2_4, localhost, user_2, , db_storedproc);
115
--source suite/funcs_1/include/show_connection.inc
124
--source suite/funcs_1/include/show_connection.inc
125
DROP PROCEDURE sp31102;
126
DROP FUNCTION fn31105;
127
DROP USER 'user_1'@'localhost';
128
DROP USER 'user_2'@'localhost';
131
# ------------------------------------------------------------------------------
132
let $message= Testcase 3.1.10.3:;
133
--source include/show_msg.inc
135
Ensure that a function can never be called.;
136
--source include/show_msg80.inc
139
DROP FUNCTION IF EXISTS fn1;
143
CREATE FUNCTION fn1(a int) returns int
150
--error ER_SP_DOES_NOT_EXIST
157
# ------------------------------------------------------------------------------
158
let $message= Testcase 3.1.10.6:;
159
--source include/show_msg.inc
161
Ensure that a procedure can never be executed.;
162
--source include/show_msg80.inc
165
DROP PROCEDURE IF EXISTS sp1;
166
DROP FUNCTION IF EXISTS sp1;
170
CREATE PROCEDURE sp1()
176
--error ER_SP_DOES_NOT_EXIST
183
# ------------------------------------------------------------------------------
184
let $message= Testcase 3.1.10.7:;
185
--source include/show_msg.inc
187
Ensure that the ROW_COUNT() SQL function always returns the correct number of
188
rows affected by the execution of a stored procedure.;
189
--source include/show_msg80.inc
190
# Note(mleich): Information taken from a comments in
191
# Bug#21818 Return value of ROW_COUNT() is incorrect for
192
# ALTER TABLE, LOAD DATA
193
# ROW_COUNT() is -1 following any statement which is not DELETE, INSERT
195
# Also, after a CALL statement, ROW_COUNT() will return the value of the
196
# last statement in the stored procedure.
199
DROP PROCEDURE IF EXISTS sp_ins_1;
200
DROP PROCEDURE IF EXISTS sp_ins_3;
201
DROP PROCEDURE IF EXISTS sp_upd;
202
DROP PROCEDURE IF EXISTS sp_ins_upd;
203
DROP PROCEDURE IF EXISTS sp_del;
204
DROP PROCEDURE IF EXISTS sp_with_rowcount;
207
CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
208
INSERT INTO temp SELECT * FROM t10;
211
CREATE PROCEDURE sp_ins_1()
213
INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
216
CREATE PROCEDURE sp_ins_3()
218
INSERT INTO temp VALUES ('abc', 'xyz', '19490523', 100, 'uvw', 1000);
219
INSERT INTO temp VALUES ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
220
INSERT INTO temp VALUES ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
223
CREATE PROCEDURE sp_upd()
225
UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
228
CREATE PROCEDURE sp_ins_upd()
231
INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
232
INSERT INTO temp VALUES ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
233
INSERT INTO temp VALUES ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
234
INSERT INTO temp VALUES ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
236
SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
237
UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
240
CREATE PROCEDURE sp_del()
242
DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
245
CREATE PROCEDURE sp_with_rowcount()
248
INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
249
('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
250
('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
251
('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
253
SELECT row_count() AS 'row_count() after insert';
254
SELECT row_count() AS 'row_count() after select row_count()';
255
SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
256
UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
257
SELECT row_count() AS 'row_count() after update';
258
SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
259
DELETE FROM temp WHERE temp.f1 = 'updated_2';
260
SELECT row_count() AS 'row_count() after delete';
290
CALL sp_with_rowcount();
297
DROP PROCEDURE sp_ins_1;
298
DROP PROCEDURE sp_ins_3;
299
DROP PROCEDURE sp_upd;
300
DROP PROCEDURE sp_ins_upd;
301
DROP PROCEDURE sp_del;
302
DROP PROCEDURE sp_with_rowcount;
306
# ------------------------------------------------------------------------------
307
let $message= Testcase 3.1.10.8:;
308
--source include/show_msg.inc
310
Ensure that the mysql_affected_rows() C API function always returns the correct
311
number of rows affected by the execution of a stored procedure.;
312
--source include/show_msg80.inc
314
#FIXME: 3.1.10.8: to be added later.
316
# ==============================================================================
317
# USE the same .inc to cleanup before and after the test
318
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
320
# ==============================================================================
321
let $message= . +++ END OF SCRIPT +++;
322
--source include/show_msg80.inc
323
# ==============================================================================