1 2--source include/have_innodb.inc 3 4--disable_warnings 5drop table if exists t1,t2; 6drop procedure if exists p1; 7--enable_warnings 8 9--echo # 10--echo #MDEV-6985: MariaDB crashes on stored procedure call 11--echo # 12CREATE TABLE `t1` ( 13 `ID` int(11) NOT NULL, 14 PRIMARY KEY (`ID`) 15) ENGINE=InnoDB; 16 17CREATE TABLE `t2` ( 18 `ID` int(11) NOT NULL, 19 `DATE` datetime DEFAULT NULL, 20 PRIMARY KEY (`ID`) 21) ENGINE=InnoDB; 22 23--delimiter ;; 24 25CREATE PROCEDURE `p1`() 26BEGIN 27 DECLARE _mySelect CURSOR FOR 28 SELECT DISTINCT t1.ID 29 FROM t1 30 LEFT JOIN t2 AS t2 ON 31 t2.ID = t1.ID 32 AND t2.DATE = ( 33 SELECT MAX(T3.DATE) FROM t2 AS T3 WHERE T3.ID = t2.ID AND T3.DATE<=NOW() 34 ) 35 WHERE t1.ID = 1; 36 OPEN _mySelect; 37 CLOSE _mySelect; 38END ;; 39--delimiter ; 40 41CALL p1(); 42CALL p1(); 43 44drop procedure p1; 45drop table t1,t2; 46 47# Save the initial number of concurrent sessions 48--source include/count_sessions.inc 49 50--echo 51--echo # 52--echo # BUG 16041903: CONTINUE HANDLER NOT INVOKED 53--echo # IN A STORED FUNCTION AFTER A LOCK WAIT TIMEOUT 54--echo # 55 56--echo 57--echo # Save and set lock wait timeout 58SET @lock_wait_timeout_saved= @@lock_wait_timeout; 59SET @innodb_lock_wait_timeout_saved= @@innodb_lock_wait_timeout; 60SET @@lock_wait_timeout= 1; 61SET @@innodb_lock_wait_timeout= 1; 62 63--echo 64--echo # Create a function with exit handler: 65DELIMITER //; 66CREATE FUNCTION f1() RETURNS VARCHAR(20) 67BEGIN 68 DECLARE EXIT HANDLER FOR SQLSTATE '42S02' RETURN 'No such table'; 69 INSERT INTO no_such_table VALUES (1); 70END// 71 72--echo 73--echo # Create a function calling f1(): 74CREATE FUNCTION f2() RETURNS VARCHAR(20) 75BEGIN 76 RETURN f1(); 77END// 78 79--echo 80--echo # Create a function provoking deadlock: 81CREATE FUNCTION f3() RETURNS VARCHAR(20) 82BEGIN 83 UPDATE t1 SET i= 1 WHERE i= 1; 84 RETURN 'Will never get here'; 85END// 86 87--echo 88--echo # Create a function calling f3, to create 89--echo # a deadlock indirectly: 90CREATE FUNCTION f4() RETURNS VARCHAR(20) 91BEGIN 92 RETURN f3(); 93END// 94DELIMITER ;// 95 96--echo 97--echo # Open another connection, create and initialize a table 98--echo # to be used for provoking deadlock, put a lock on the table: 99connect (con1,localhost,root,,); 100CREATE TABLE t1 (i INT) ENGINE=InnoDB; 101INSERT INTO t1 VALUES (1); 102SET AUTOCOMMIT= 0; 103UPDATE t1 SET i=1 WHERE i=1; 104 105--echo 106--echo # On the default connection, do an update to provoke a 107--echo # deadlock, then call the function with handler. This case 108--echo # fails without the patch (with error ER_NO_SUCH_TABLE): 109--connection default 110SET AUTOCOMMIT= 0; 111--error ER_LOCK_WAIT_TIMEOUT 112UPDATE t1 SET i=1 WHERE i=1; 113SELECT f1() AS 'f1():'; 114 115--echo 116--echo # Provoke another deadlock, then call the function with 117--echo # handler indirectly. This case fails without the patch 118--echo # (with error ER_NO_SUCH_TABLE): 119--error ER_LOCK_WAIT_TIMEOUT 120UPDATE t1 SET i= 1 WHERE i= 1; 121SELECT f2() AS 'f2():'; 122 123--echo 124--echo # Provoke yet another deadlock, but now from within a function, 125--echo # then call the function with handler. This succeeds even 126--echo # without the patch because is_fatal_sub_stmt_error is reset 127--echo # in restore_sub_stmt after the failing function has been 128--echo # executed. The test case is included anyway for better coverage: 129--error ER_LOCK_WAIT_TIMEOUT 130SELECT f3() AS 'f3():'; 131SELECT f1() AS 'f1():'; 132 133--echo # Provoke yet another deadlock, but now from within a function, 134--echo # calling another function, then call the function with handler. 135--echo # This succeeds even without the patch because 136--echo # is_fatal_sub_stmt_error is reset in restore_sub_stmt after 137--echo # the failing function has been executed. The test case is 138--echo # included anyway for better coverage: 139--error ER_LOCK_WAIT_TIMEOUT 140SELECT f4() AS 'f4():'; 141SELECT f1() AS 'f1():'; 142 143--echo 144--echo # Disconnect, drop functions and table: 145--disconnect con1 146DROP FUNCTION f4; 147DROP FUNCTION f3; 148DROP FUNCTION f2; 149DROP FUNCTION f1; 150DROP TABLE t1; 151 152--echo 153--echo # Reset lock wait timeouts 154SET @@lock_wait_timeout= @lock_wait_timeout_saved; 155SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; 156 157--echo # 158--echo # BUG 16041903: End of test case 159--echo # 160 161--echo # 162--echo # MDEV-15035: SP using query with outer join and a parameter 163--echo # in ON expression 164--echo # 165 166CREATE TABLE t1 ( 167 id int NOT NULL, 168 PRIMARY KEY (id) 169) ENGINE=InnoDB; 170 171INSERT INTO t1 VALUES (1), (2); 172 173CREATE TABLE t2 ( 174 id int NOT NULL, 175 id_foo int NOT NULL, 176 PRIMARY KEY (id) 177) ENGINE=InnoDB; 178 179INSERT INTO t2 VALUES (1, 1); 180 181--disable_warnings 182DROP PROCEDURE IF EXISTS test_proc; 183--enable_warnings 184 185DELIMITER |; 186CREATE PROCEDURE test_proc(IN param int) 187LANGUAGE SQL 188READS SQL DATA 189BEGIN 190 SELECT DISTINCT f.id 191 FROM t1 f 192 LEFT OUTER JOIN t2 b ON b.id_foo = f.id 193 WHERE (param <> 0 OR b.id IS NOT NULL); 194END| 195DELIMITER ;| 196 197CALL test_proc(0); 198CALL test_proc(1); 199 200DROP PROCEDURE IF EXISTS test_proc; 201DROP TABLE t1, t2; 202 203# Wait till we reached the initial number of concurrent sessions 204--source include/wait_until_count_sessions.inc 205