Edit File: innodb_storedproc_10.result
--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 +++ --------------------------------------------------------------------------------