1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
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
|
