SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
--source suite/funcs_1/storedproc/load_sp_tb.inc
--------------------------------------------------------------------------------
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS db_storedproc;
DROP DATABASE IF EXISTS db_storedproc_1;
CREATE DATABASE db_storedproc;
CREATE DATABASE db_storedproc_1;
USE db_storedproc;
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
USE db_storedproc_1;
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
USE db_storedproc;
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
Warnings:
Warning 1265 Data truncated for column 'f3' at row 1
Warning 1265 Data truncated for column 'f3' at row 2
Warning 1265 Data truncated for column 'f3' at row 3
Warning 1265 Data truncated for column 'f3' at row 4
Warning 1265 Data truncated for column 'f3' at row 5
Warning 1265 Data truncated for column 'f3' at row 6
Warning 1265 Data truncated for column 'f3' at row 7
Warning 1265 Data truncated for column 'f3' at row 8
Warning 1265 Data truncated for column 'f3' at row 9
Warning 1265 Data truncated for column 'f3' at row 10
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
Warnings:
Warning 1265 Data truncated for column 'f3' at row 1
Warning 1265 Data truncated for column 'f3' at row 2
Warning 1265 Data truncated for column 'f3' at row 3
Warning 1265 Data truncated for column 'f3' at row 4
Warning 1265 Data truncated for column 'f3' at row 5
Warning 1265 Data truncated for column 'f3' at row 6
Warning 1265 Data truncated for column 'f3' at row 7
Warning 1265 Data truncated for column 'f3' at row 8
Warning 1265 Data truncated for column 'f3' at row 9
Warning 1265 Data truncated for column 'f3' at row 10
create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
Section 3.1.10 - CALL checks:
--------------------------------------------------------------------------------
USE db_storedproc;
Testcase 3.1.10.2 + 3.1.10.5:
-----------------------------
2. Ensure that a procedure cannot be called if the appropriate privileges do not
exist.
5. Ensure that a function cannot be executed if the appropriate privileges do
not exist.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp31102;
DROP FUNCTION IF EXISTS fn31105;
create user 'user_1'@'localhost';
create user 'user_2'@'localhost';
GRANT CREATE ROUTINE ON db_storedproc.* TO 'user_1'@'localhost';
GRANT SELECT ON db_storedproc.* TO 'user_2'@'localhost';
FLUSH PRIVILEGES;
user_1@localhost db_storedproc
CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
BEGIN
SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
END//
CREATE FUNCTION fn31105(n INT) RETURNS INT
BEGIN
DECLARE res INT;
SET res = n * n;
RETURN res;
END//
user_2@localhost db_storedproc
CALL sp31102();
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
SELECT fn31105( 9 );
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
connection default;
USE db_storedproc;
root@localhost db_storedproc
CALL sp31102();
f1 f2 f3 f4 f5 f6
a` a` 1000-01-01 -5000 a` -5000
SELECT fn31105( 9 );
fn31105( 9 )
81
GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
FLUSH PRIVILEGES;
user_2@localhost db_storedproc
CALL sp31102();
f1 f2 f3 f4 f5 f6
a` a` 1000-01-01 -5000 a` -5000
SELECT fn31105( 9 );
fn31105( 9 )
81
connection default;
USE db_storedproc;
root@localhost db_storedproc
REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
FLUSH PRIVILEGES;
CALL sp31102();
f1 f2 f3 f4 f5 f6
a` a` 1000-01-01 -5000 a` -5000
SELECT fn31105( 9 );
fn31105( 9 )
81
user_2@localhost db_storedproc
CALL sp31102();
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.sp31102'
SELECT fn31105( 9 );
ERROR 42000: execute command denied to user 'user_2'@'localhost' for routine 'db_storedproc.fn31105'
USE db_storedproc;
root@localhost db_storedproc
DROP PROCEDURE sp31102;
DROP FUNCTION fn31105;
DROP USER 'user_1'@'localhost';
DROP USER 'user_2'@'localhost';
Testcase 3.1.10.3:
------------------
Ensure that a function can never be called.
--------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1(a int) returns int
BEGIN
set @b = 0.9 * a;
return @b;
END//
CALL fn1();
ERROR 42000: PROCEDURE db_storedproc.fn1 does not exist
DROP FUNCTION fn1;
Testcase 3.1.10.6:
------------------
Ensure that a procedure can never be executed.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
DROP FUNCTION IF EXISTS sp1;
CREATE PROCEDURE sp1()
BEGIN
SELECT * from t10;
END//
SELECT sp1();
ERROR 42000: FUNCTION db_storedproc.sp1 does not exist
DROP PROCEDURE sp1;
Testcase 3.1.10.7:
------------------
Ensure that the ROW_COUNT() SQL function always returns the correct number of
rows affected by the execution of a stored procedure.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_ins_1;
DROP PROCEDURE IF EXISTS sp_ins_3;
DROP PROCEDURE IF EXISTS sp_upd;
DROP PROCEDURE IF EXISTS sp_ins_upd;
DROP PROCEDURE IF EXISTS sp_del;
DROP PROCEDURE IF EXISTS sp_with_rowcount;
CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
INSERT INTO temp SELECT * FROM t10;
CREATE PROCEDURE sp_ins_1()
BEGIN
INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
END//
CREATE PROCEDURE sp_ins_3()
BEGIN
INSERT INTO temp VALUES ('abc', 'xyz', '19490523', 100, 'uvw', 1000);
INSERT INTO temp VALUES ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
INSERT INTO temp VALUES ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
END//
CREATE PROCEDURE sp_upd()
BEGIN
UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
END//
CREATE PROCEDURE sp_ins_upd()
BEGIN
BEGIN
INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
INSERT INTO temp VALUES ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
INSERT INTO temp VALUES ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
INSERT INTO temp VALUES ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
END;
SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
END//
CREATE PROCEDURE sp_del()
BEGIN
DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
END//
CREATE PROCEDURE sp_with_rowcount()
BEGIN
BEGIN
INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
END;
SELECT row_count() AS 'row_count() after insert';
SELECT row_count() AS 'row_count() after select row_count()';
SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
SELECT row_count() AS 'row_count() after update';
SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
DELETE FROM temp WHERE temp.f1 = 'updated_2';
SELECT row_count() AS 'row_count() after delete';
END//
CALL sp_ins_1();
SELECT row_count();
row_count()
1
SELECT * FROM temp;
f1 f2 f3 f4 f5 f6
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
a` a` 1000-01-01 -5000 a` -5000
aaa aaa 1000-01-02 -4999 aaa -4999
abaa abaa 1000-01-03 -4998 abaa -4998
abc abc 2005-10-03 100 uvw 1000
acaaa acaaa 1000-01-04 -4997 acaaa -4997
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
CALL sp_ins_3();
SELECT row_count();
row_count()
1
SELECT * FROM temp;
f1 f2 f3 f4 f5 f6
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
a` a` 1000-01-01 -5000 a` -5000
aaa aaa 1000-01-02 -4999 aaa -4999
abaa abaa 1000-01-03 -4998 abaa -4998
abc abc 2005-10-03 100 uvw 1000
abc xyz 1949-05-23 100 uvw 1000
abc xyz 1989-11-09 100 uvw 1000
abc xyz 2005-10-24 100 uvw 1000
acaaa acaaa 1000-01-04 -4997 acaaa -4997
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
CALL sp_upd();
SELECT row_count();
row_count()
4
SELECT * FROM temp;
f1 f2 f3 f4 f5 f6
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
a` a` 1000-01-01 -5000 a` -5000
aaa aaa 1000-01-02 -4999 aaa -4999
abaa abaa 1000-01-03 -4998 abaa -4998
acaaa acaaa 1000-01-04 -4997 acaaa -4997
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
updated abc 2005-10-03 100 uvw 1000
updated xyz 1949-05-23 100 uvw 1000
updated xyz 1989-11-09 100 uvw 1000
updated xyz 2005-10-24 100 uvw 1000
CALL sp_ins_upd();
COUNT( f1 ) f1
1 aaa
1 abaa
1 acaaa
1 adaaaa
1 aeaaaaa
1 afaaaaaa
1 agaaaaaaa
1 a^aaaaaaaa
1 a_aaaaaaaaa
1 a`
4 qwe
4 updated
SELECT row_count();
row_count()
3
SELECT * FROM temp;
f1 f2 f3 f4 f5 f6
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
a` a` 1000-01-01 -5000 a` -5000
aaa aaa 1000-01-02 -4999 aaa -4999
abaa abaa 1000-01-03 -4998 abaa -4998
acaaa acaaa 1000-01-04 -4997 acaaa -4997
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
qwe xyz 1998-03-26 100 uvw 1000
updated abc 2005-10-03 100 uvw 1000
updated xyz 1949-05-23 100 uvw 1000
updated xyz 1989-11-09 100 uvw 1000
updated xyz 2005-10-24 100 uvw 1000
updated_2 abc 1989-11-09 100 uvw 1000
updated_2 abc 2000-11-09 100 uvw 1000
updated_2 abc 2005-11-07 100 uvw 1000
CALL sp_del();
SELECT row_count();
row_count()
4
SELECT * FROM temp;
f1 f2 f3 f4 f5 f6
a^aaaaaaaa a^aaaaaaaa 1000-01-09 -4992 a^aaaaaaaa -4992
a_aaaaaaaaa a_aaaaaaaaa 1000-01-10 -4991 a_aaaaaaaaa -4991
a` a` 1000-01-01 -5000 a` -5000
aaa aaa 1000-01-02 -4999 aaa -4999
abaa abaa 1000-01-03 -4998 abaa -4998
acaaa acaaa 1000-01-04 -4997 acaaa -4997
adaaaa adaaaa 1000-01-05 -4996 adaaaa -4996
aeaaaaa aeaaaaa 1000-01-06 -4995 aeaaaaa -4995
afaaaaaa afaaaaaa 1000-01-07 -4994 afaaaaaa -4994
agaaaaaaa agaaaaaaa 1000-01-08 -4993 agaaaaaaa -4993
updated abc 2005-10-03 100 uvw 1000
updated xyz 1949-05-23 100 uvw 1000
updated xyz 1989-11-09 100 uvw 1000
updated xyz 2005-10-24 100 uvw 1000
DELETE FROM temp;
CALL sp_with_rowcount();
row_count() after insert
4
row_count() after select row_count()
-1
f1 f2 f3
qwe abc 1989-11-09
qwe abc 2000-11-09
qwe xyz 1998-03-26
qwe xyz 2005-11-07
row_count() after update
2
f1 f2 f3
qwe xyz 1998-03-26
qwe xyz 2005-11-07
updated_2 abc 1989-11-09
updated_2 abc 2000-11-09
row_count() after delete
2
SELECT row_count();
row_count()
0
SELECT * FROM temp;
f1 f2 f3 f4 f5 f6
qwe xyz 1998-03-26 100 uvw 1000
qwe xyz 2005-11-07 100 uvw 1000
DROP PROCEDURE sp_ins_1;
DROP PROCEDURE sp_ins_3;
DROP PROCEDURE sp_upd;
DROP PROCEDURE sp_ins_upd;
DROP PROCEDURE sp_del;
DROP PROCEDURE sp_with_rowcount;
DROP TABLE temp;
Testcase 3.1.10.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.
--------------------------------------------------------------------------------
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS db_storedproc;
DROP DATABASE IF EXISTS db_storedproc_1;
. +++ END OF SCRIPT +++
--------------------------------------------------------------------------------