mariadb/mysql-test/main/sp-innodb.test

--source include/have_innodb.inc

--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;

--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;