diff options
Diffstat (limited to 'mysql-test/main/sp-innodb.test')
| -rw-r--r-- | mysql-test/main/sp-innodb.test | 204 |
1 files changed, 204 insertions, 0 deletions
diff --git a/mysql-test/main/sp-innodb.test b/mysql-test/main/sp-innodb.test new file mode 100644 index 00000000..e44a853e --- /dev/null +++ b/mysql-test/main/sp-innodb.test @@ -0,0 +1,204 @@ + +--source include/have_innodb.inc + +--disable_warnings +drop table if exists t1,t2; +drop procedure if exists p1; +--enable_warnings + +--echo # +--echo #MDEV-6985: MariaDB crashes on stored procedure call +--echo # +CREATE TABLE `t1` ( + `ID` int(11) NOT NULL, + PRIMARY KEY (`ID`) +) ENGINE=InnoDB; + +CREATE TABLE `t2` ( + `ID` int(11) NOT NULL, + `DATE` datetime DEFAULT NULL, + PRIMARY KEY (`ID`) +) ENGINE=InnoDB; + +--delimiter ;; + +CREATE PROCEDURE `p1`() +BEGIN + DECLARE _mySelect CURSOR FOR + SELECT DISTINCT t1.ID + FROM t1 + LEFT JOIN t2 AS t2 ON + t2.ID = t1.ID + AND t2.DATE = ( + SELECT MAX(T3.DATE) FROM t2 AS T3 WHERE T3.ID = t2.ID AND T3.DATE<=NOW() + ) + WHERE t1.ID = 1; + OPEN _mySelect; + CLOSE _mySelect; +END ;; +--delimiter ; + +CALL p1(); +CALL p1(); + +drop procedure p1; +drop table t1,t2; + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--echo +--echo # +--echo # BUG 16041903: CONTINUE HANDLER NOT INVOKED +--echo # IN A STORED FUNCTION AFTER A LOCK WAIT TIMEOUT +--echo # + +--echo +--echo # Save and set lock wait timeout +SET @lock_wait_timeout_saved= @@lock_wait_timeout; +SET @innodb_lock_wait_timeout_saved= @@innodb_lock_wait_timeout; +SET @@lock_wait_timeout= 1; +SET @@innodb_lock_wait_timeout= 1; + +--echo +--echo # Create a function with exit handler: +DELIMITER //; +CREATE FUNCTION f1() RETURNS VARCHAR(20) +BEGIN + DECLARE EXIT HANDLER FOR SQLSTATE '42S02' RETURN 'No such table'; + INSERT INTO no_such_table VALUES (1); +END// + +--echo +--echo # Create a function calling f1(): +CREATE FUNCTION f2() RETURNS VARCHAR(20) +BEGIN + RETURN f1(); +END// + +--echo +--echo # Create a function provoking deadlock: +CREATE FUNCTION f3() RETURNS VARCHAR(20) +BEGIN + UPDATE t1 SET i= 1 WHERE i= 1; + RETURN 'Will never get here'; +END// + +--echo +--echo # Create a function calling f3, to create +--echo # a deadlock indirectly: +CREATE FUNCTION f4() RETURNS VARCHAR(20) +BEGIN + RETURN f3(); +END// +DELIMITER ;// + +--echo +--echo # Open another connection, create and initialize a table +--echo # to be used for provoking deadlock, put a lock on the table: +connect (con1,localhost,root,,); +CREATE TABLE t1 (i INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +SET AUTOCOMMIT= 0; +UPDATE t1 SET i=1 WHERE i=1; + +--echo +--echo # On the default connection, do an update to provoke a +--echo # deadlock, then call the function with handler. This case +--echo # fails without the patch (with error ER_NO_SUCH_TABLE): +--connection default +SET AUTOCOMMIT= 0; +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET i=1 WHERE i=1; +SELECT f1() AS 'f1():'; + +--echo +--echo # Provoke another deadlock, then call the function with +--echo # handler indirectly. This case fails without the patch +--echo # (with error ER_NO_SUCH_TABLE): +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET i= 1 WHERE i= 1; +SELECT f2() AS 'f2():'; + +--echo +--echo # Provoke yet another deadlock, but now from within a function, +--echo # then call the function with handler. This succeeds even +--echo # without the patch because is_fatal_sub_stmt_error is reset +--echo # in restore_sub_stmt after the failing function has been +--echo # executed. The test case is included anyway for better coverage: +--error ER_LOCK_WAIT_TIMEOUT +SELECT f3() AS 'f3():'; +SELECT f1() AS 'f1():'; + +--echo # Provoke yet another deadlock, but now from within a function, +--echo # calling another function, then call the function with handler. +--echo # This succeeds even without the patch because +--echo # is_fatal_sub_stmt_error is reset in restore_sub_stmt after +--echo # the failing function has been executed. The test case is +--echo # included anyway for better coverage: +--error ER_LOCK_WAIT_TIMEOUT +SELECT f4() AS 'f4():'; +SELECT f1() AS 'f1():'; + +--echo +--echo # Disconnect, drop functions and table: +--disconnect con1 +DROP FUNCTION f4; +DROP FUNCTION f3; +DROP FUNCTION f2; +DROP FUNCTION f1; +DROP TABLE t1; + +--echo +--echo # Reset lock wait timeouts +SET @@lock_wait_timeout= @lock_wait_timeout_saved; +SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; + +--echo # +--echo # BUG 16041903: End of test case +--echo # + +--echo # +--echo # MDEV-15035: SP using query with outer join and a parameter +--echo # in ON expression +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1), (2); + +CREATE TABLE t2 ( + id int NOT NULL, + id_foo int NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (1, 1); + +--disable_warnings +DROP PROCEDURE IF EXISTS test_proc; +--enable_warnings + +DELIMITER |; +CREATE PROCEDURE test_proc(IN param int) +LANGUAGE SQL +READS SQL DATA +BEGIN + SELECT DISTINCT f.id + FROM t1 f + LEFT OUTER JOIN t2 b ON b.id_foo = f.id + WHERE (param <> 0 OR b.id IS NOT NULL); +END| +DELIMITER ;| + +CALL test_proc(0); +CALL test_proc(1); + +DROP PROCEDURE IF EXISTS test_proc; +DROP TABLE t1, t2; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc |