3
# Test variations inspired by
4
# Bug#12713 Error in a stored function called from a SELECT doesn't cause
5
# ROLLBACK of statement
6
# Essential of the bug:
7
# - A SELECT using a FUNCTION processes a table.
8
# - The SELECT affects more than row.
9
# - The FUNCTION modifies a table.
10
# - When processing the non first matching row, the function fails.
11
# But the modification caused by the function when the SELECT processed the
12
# first matching row is not reverted.
14
# Goal of this test: Attempts to catch a situation where
15
# - a statement A involving the execution of one or more functions is run
16
# - the function/functions themself contain one or more statements
18
# - one of the modifying statements within one of the functions fails
19
# - the table remains at least partially modified
21
# = There is no automatic ROLLBACK of changes caused by the failing
23
# = Statement A is not atomic.
26
# - The table to be modified must use a transactional storage engine.
27
# For example MyISAM cannot avoid the situation above.
28
# - Some comments assume that the rows of the table t1_select are processed
29
# in the order of insertion. That means
30
# SELECT f1,f2 FROM t1_select
31
# should have the same result set and row order like
32
# SELECT f1,f2 FROM t1_select ORDER BY f1;
33
# - The manual says that we get in non strict sql mode a warning around INSERT:
34
# Inserting NULL into a column that has been declared NOT NULL.
35
# For multiple-row INSERT statements or INSERT INTO ... SELECT statements,
36
# the column is set to the implicit default value for the column data type.
42
let $fixed_bug_35877 = 0;
44
let $from_select = SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL;
46
--source include/have_innodb.inc
50
DROP TABLE IF EXISTS t1_select;
51
DROP TABLE IF EXISTS t1_aux;
52
DROP TABLE IF EXISTS t1_not_null;
53
DROP VIEW IF EXISTS v1_not_null;
54
DROP VIEW IF EXISTS v1_func;
55
DROP TABLE IF EXISTS t1_fail;
56
DROP FUNCTION IF EXISTS f1_simple_insert;
57
DROP FUNCTION IF EXISTS f1_two_inserts;
58
DROP FUNCTION IF EXISTS f1_insert_select;
61
SET SESSION AUTOCOMMIT=0;
62
SET SESSION sql_mode = '';
64
CREATE TABLE t1_select (f1 BIGINT, f2 BIGINT) ENGINE = MEMORY;
65
INSERT INTO t1_select(f1,f2) VALUES (1,-1),(2,NULL),(3,0),(4,1),(5,2);
66
SELECT * FROM t1_select;
68
--replace_result $engine <transactional_engine>
70
CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)
72
SELECT * FROM t1_not_null;
74
--replace_result $engine <transactional_engine>
76
CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)
81
# FUNCTION with "simple" INSERT
83
CREATE FUNCTION f1_simple_insert(my_f1 INTEGER) RETURNS INTEGER
85
INSERT INTO t1_not_null SET f1 = 10, f2 = my_f1;
91
--echo # One f1_simple_insert execution per row, no NOT NULL violation
92
SELECT f1_simple_insert(1);
93
SELECT * FROM t1_not_null ORDER BY f1,f2;
95
SELECT * FROM t1_not_null;
97
SELECT f1_simple_insert(1) FROM t1_select;
98
SELECT * FROM t1_not_null ORDER BY f1,f2;
100
SELECT * FROM t1_not_null;
103
--echo # One f1_simple_insert execution per row, NOT NULL violation when the
104
--echo # SELECT processes the first row.
105
--error ER_BAD_NULL_ERROR
106
SELECT f1_simple_insert(NULL);
107
SELECT * FROM t1_not_null ORDER BY f1,f2;
109
SELECT * FROM t1_not_null ORDER BY f1,f2;
111
--error ER_BAD_NULL_ERROR
112
SELECT f1_simple_insert(NULL) FROM t1_select;
113
SELECT * FROM t1_not_null ORDER BY f1,f2;
115
SELECT * FROM t1_not_null ORDER BY f1,f2;
117
--error ER_BAD_NULL_ERROR
118
eval SELECT 1 FROM ($from_select) AS t1 WHERE f1_simple_insert(NULL) = 1;
119
SELECT * FROM t1_not_null ORDER BY f1,f2;
121
SELECT * FROM t1_not_null ORDER BY f1,f2;
124
--echo # One f1_simple_insert execution per row, NOT NULL violation when the
125
--echo # SELECT processes the non first row
126
--error ER_BAD_NULL_ERROR
127
eval SELECT f1_simple_insert(f2) FROM ($from_select) AS t1;
128
SELECT * FROM t1_not_null ORDER BY f1,f2;
130
SELECT * FROM t1_not_null ORDER BY f1,f2;
132
--error ER_BAD_NULL_ERROR
133
SELECT f1_simple_insert(f2) FROM t1_select;
134
SELECT * FROM t1_not_null ORDER BY f1,f2;
136
SELECT * FROM t1_not_null ORDER BY f1,f2;
139
--echo # Two f1_simple_insert executions per row, NOT NULL violation when the
140
--echo # SELECT processes the first row.
141
--error ER_BAD_NULL_ERROR
142
SELECT f1_simple_insert(1),f1_simple_insert(NULL);
143
SELECT * FROM t1_not_null ORDER BY f1,f2;
145
SELECT * FROM t1_not_null ORDER BY f1,f2;
147
--error ER_BAD_NULL_ERROR
148
SELECT f1_simple_insert(NULL),f1_simple_insert(1);
149
SELECT * FROM t1_not_null ORDER BY f1,f2;
151
SELECT * FROM t1_not_null ORDER BY f1,f2;
154
--echo # Two f1_simple_insert executions per row, NOT NULL violation when the
155
--echo # SELECT processes the non first row
156
--error ER_BAD_NULL_ERROR
157
eval SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM ($from_select) AS t1;
158
SELECT * FROM t1_not_null ORDER BY f1,f2;
160
SELECT * FROM t1_not_null ORDER BY f1,f2;
162
--error ER_BAD_NULL_ERROR
163
eval SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM ($from_select) AS t1;
164
SELECT * FROM t1_not_null ORDER BY f1,f2;
166
SELECT * FROM t1_not_null ORDER BY f1,f2;
168
--error ER_BAD_NULL_ERROR
169
SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM t1_select;
170
SELECT * FROM t1_not_null ORDER BY f1,f2;
172
SELECT * FROM t1_not_null ORDER BY f1,f2;
174
--error ER_BAD_NULL_ERROR
175
SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM t1_select;
176
SELECT * FROM t1_not_null ORDER BY f1,f2;
178
SELECT * FROM t1_not_null ORDER BY f1,f2;
180
--error ER_BAD_NULL_ERROR
181
eval SELECT 1 FROM ($from_select) AS t1
182
WHERE 1 = f1_simple_insert(f2) AND 1 = f1_simple_insert(f1);
183
SELECT * FROM t1_not_null ORDER BY f1,f2;
185
SELECT * FROM t1_not_null ORDER BY f1,f2;
188
--echo # Nested functions, the inner fails
189
--error ER_BAD_NULL_ERROR
190
SELECT f1_simple_insert(f1_simple_insert(NULL)) FROM t1_select;
191
SELECT * FROM t1_not_null ORDER BY f1,f2;
193
SELECT * FROM t1_not_null ORDER BY f1,f2;
195
--echo # Nested functions, the outer fails
196
--error ER_BAD_NULL_ERROR
197
SELECT f1_simple_insert(f1_simple_insert(1) + NULL) FROM t1_select;
198
SELECT * FROM t1_not_null ORDER BY f1,f2;
200
SELECT * FROM t1_not_null ORDER BY f1,f2;
201
DROP FUNCTION f1_simple_insert;
203
# FUNCTION with INSERT ... SELECT
205
let $f1_insert_select =
206
CREATE FUNCTION f1_insert_select(my_f1 INTEGER) RETURNS INTEGER
208
INSERT INTO t1_not_null SELECT * FROM t1_select WHERE f1 = my_f1;
212
eval $f1_insert_select;
215
--echo # f1_insert_select(2), tries to INSERT SELECT one row containing NULL
216
--echo # The fact that
217
--echo # - SELECT f1_insert_select(2); gives any result set and
218
--echo # - t1_not_null gets a row inserted
219
--echo # is covered by the manual.
220
# Non strict sqlmode + INSERT SELECT --> NULL adjusted to default
221
SELECT f1_insert_select(2);
222
SELECT * FROM t1_not_null ORDER BY f1,f2;
224
SELECT * FROM t1_not_null ORDER BY f1,f2;
225
DROP FUNCTION f1_insert_select;
227
SET SESSION sql_mode = 'traditional';
228
eval $f1_insert_select;
229
--error ER_BAD_NULL_ERROR
230
SELECT f1_insert_select(2);
231
SELECT * FROM t1_not_null ORDER BY f1,f2;
233
SELECT * FROM t1_not_null ORDER BY f1,f2;
234
DROP FUNCTION f1_insert_select;
235
SET SESSION sql_mode = '';
237
# FUNCTION with two simple INSERTs
239
--echo # Function tries to
240
--echo # 1. INSERT statement: Insert one row with NULL -> NOT NULL violation
241
--echo # 2. INSERT statement: Insert one row without NULL
242
# I guess the execution of the function becomes aborted just when the
245
CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
247
INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
248
INSERT INTO t1_not_null SET f1 = 10, f2 = 10;
252
--error ER_BAD_NULL_ERROR
253
SELECT f1_two_inserts();
254
SELECT * FROM t1_not_null ORDER BY f1,f2;
256
SELECT * FROM t1_not_null ORDER BY f1,f2;
257
DROP FUNCTION f1_two_inserts;
260
--echo # Function tries to
261
--echo # 1. INSERT statement: Insert one row without NULL
262
--echo # 2. INSERT statement: Insert one row with NULL -> NOT NULL violation
264
CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
266
INSERT INTO t1_not_null SET f1 = 10, f2 = 10;
267
INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
271
--error ER_BAD_NULL_ERROR
272
SELECT f1_two_inserts();
273
SELECT * FROM t1_not_null ORDER BY f1,f2;
275
SELECT * FROM t1_not_null ORDER BY f1,f2;
278
--echo # Function tries to
279
--echo # INSERT statement: Insert two rows
280
--echo # first row without NULL
281
--echo # second row with NULL -> NOT NULL violation
282
--echo # -> NOT NULL violation
284
let $f1_insert_with_two_rows =
285
CREATE FUNCTION f1_insert_with_two_rows() RETURNS INTEGER
287
INSERT INTO t1_not_null(f1,f2) VALUES (10,10),(10,NULL);
291
eval $f1_insert_with_two_rows;
292
--echo # The fact that
293
--echo # - SELECT f1_insert_with_two_rows(); gives any result set and
294
--echo # - t1_not_null gets a row inserted
295
--echo # is covered by the manual.
296
# Non strict sqlmode + multiple-row INSERT --> NULL adjusted to default
297
SELECT f1_insert_with_two_rows();
298
SELECT * FROM t1_not_null ORDER BY f1,f2;
300
SELECT * FROM t1_not_null ORDER BY f1,f2;
301
DROP FUNCTION f1_insert_with_two_rows;
303
SET SESSION sql_mode = 'traditional';
304
eval $f1_insert_with_two_rows;
305
--error ER_BAD_NULL_ERROR
306
SELECT f1_insert_with_two_rows();
307
SELECT * FROM t1_not_null ORDER BY f1,f2;
309
SELECT * FROM t1_not_null ORDER BY f1,f2;
310
SET SESSION sql_mode = '';
313
--echo # FUNCTION in Correlated Subquery
314
--error ER_BAD_NULL_ERROR
315
SELECT 1 FROM t1_select t1
316
WHERE 1 = (SELECT f1_insert_with_two_rows() FROM t1_select t2
317
WHERE t2.f1 = t1.f1);
318
SELECT * FROM t1_not_null ORDER BY f1,f2;
320
SELECT * FROM t1_not_null ORDER BY f1,f2;
323
--echo # FUNCTION in JOIN
324
--error ER_BAD_NULL_ERROR
325
SELECT 1 FROM t1_select t1, t1_select t2
326
WHERE t1.f1 = t2.f1 AND t2.f1 = f1_insert_with_two_rows();
327
SELECT * FROM t1_not_null ORDER BY f1,f2;
329
SELECT * FROM t1_not_null ORDER BY f1,f2;
331
--error ER_BAD_NULL_ERROR
332
SELECT STRAIGHT_JOIN * FROM t1_select t2 RIGHT JOIN t1_select t1
333
ON t1.f1 = t1.f1 WHERE 1 = f1_insert_with_two_rows();
335
DROP FUNCTION f1_insert_with_two_rows;
338
--echo # FUNCTION in UNION
339
--error ER_BAD_NULL_ERROR
342
SELECT f1_two_inserts();
343
SELECT * FROM t1_not_null ORDER BY f1,f2;
345
SELECT * FROM t1_not_null ORDER BY f1,f2;
348
--echo # FUNCTION in INSERT
349
--error ER_BAD_NULL_ERROR
350
INSERT INTO t1_aux SET f1 = 1, f2 = f1_two_inserts();
351
SELECT * FROM t1_not_null ORDER BY f1,f2;
353
SELECT * FROM t1_not_null ORDER BY f1,f2;
355
--error ER_BAD_NULL_ERROR
356
INSERT INTO t1_aux SELECT 1, f1_two_inserts();
357
SELECT * FROM t1_not_null ORDER BY f1,f2;
359
SELECT * FROM t1_not_null ORDER BY f1,f2;
360
SELECT * FROM t1_aux ORDER BY f1,f2;
362
--error ER_BAD_NULL_ERROR
363
INSERT INTO t1_aux VALUES(1,f1_two_inserts());
364
SELECT * FROM t1_not_null ORDER BY f1,f2;
365
SELECT * FROM t1_aux ORDER BY f1,f2;
368
--echo # FUNCTION in DELETE
369
INSERT INTO t1_aux VALUES (1,1);
371
--error ER_BAD_NULL_ERROR
372
DELETE FROM t1_aux WHERE f1 = f1_two_inserts();
373
SELECT * FROM t1_not_null ORDER BY f1,f2;
375
SELECT * FROM t1_not_null ORDER BY f1,f2;
376
SELECT * FROM t1_aux ORDER BY f1,f2;
379
--echo # FUNCTION in UPDATE SET
381
--error ER_BAD_NULL_ERROR
382
UPDATE t1_aux SET f2 = f1_two_inserts() + 1;
383
SELECT * FROM t1_not_null ORDER BY f1,f2;
385
SELECT * FROM t1_not_null ORDER BY f1,f2;
386
SELECT * FROM t1_aux ORDER BY f1,f2;
388
if ($fixed_bug_35877)
391
--echo # FUNCTION in UPDATE WHERE
392
# Bug#35877 Update .. WHERE with function, constraint violation, crash
393
UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts();
394
SELECT * FROM t1_not_null ORDER BY f1,f2;
396
SELECT * FROM t1_not_null ORDER BY f1,f2;
397
SELECT * FROM t1_aux ORDER BY f1,f2;
401
--echo # FUNCTION in VIEW definition
402
CREATE VIEW v1_func AS SELECT f1_two_inserts() FROM t1_select;
403
--error ER_BAD_NULL_ERROR
404
SELECT * FROM v1_func;
405
SELECT * FROM t1_not_null ORDER BY f1,f2;
407
SELECT * FROM t1_not_null ORDER BY f1,f2;
411
--echo # FUNCTION in CREATE TABLE ... AS SELECT
412
--error ER_BAD_NULL_ERROR
413
CREATE TABLE t1_fail AS SELECT f1_two_inserts() FROM t1_select;
414
SELECT * FROM t1_not_null ORDER BY f1,f2;
416
--error ER_BAD_NULL_ERROR
417
CREATE TABLE t1_fail AS SELECT * FROM t1_select WHERE 1 = f1_two_inserts();
418
SELECT * FROM t1_not_null ORDER BY f1,f2;
422
--echo # FUNCTION in ORDER BY
423
--error ER_BAD_NULL_ERROR
424
SELECT * FROM t1_select ORDER BY f1,f1_two_inserts();
425
SELECT * FROM t1_not_null ORDER BY f1,f2;
428
--echo # FUNCTION in aggregate function
429
--error ER_BAD_NULL_ERROR
430
SELECT AVG(f1_two_inserts()) FROM t1_select;
431
SELECT * FROM t1_not_null ORDER BY f1,f2;
434
--echo # FUNCTION in HAVING
435
--error ER_BAD_NULL_ERROR
436
SELECT 1 FROM t1_select HAVING AVG(f1) = f1_two_inserts() + 2;
437
SELECT * FROM t1_not_null ORDER BY f1,f2;
438
DROP FUNCTION f1_two_inserts;
441
--echo # FUNCTION modifies Updatable VIEW
442
CREATE VIEW v1_not_null AS SELECT f1,f2 FROM t1_not_null WITH CHECK OPTION;
444
CREATE FUNCTION f1_two_inserts_v1() RETURNS INTEGER
446
INSERT INTO v1_not_null SET f1 = 10, f2 = 10;
447
INSERT INTO v1_not_null SET f1 = 10, f2 = NULL;
451
--error ER_BAD_NULL_ERROR
452
SELECT f1_two_inserts_v1();
453
SELECT * FROM t1_not_null ORDER BY f1,f2;
455
SELECT * FROM t1_not_null ORDER BY f1,f2;
456
DROP FUNCTION f1_two_inserts_v1;
457
DROP VIEW v1_not_null;
460
--echo # FUNCTION causes FOREIGN KEY constraint violation
462
CREATE TABLE t1_parent (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1))
464
INSERT INTO t1_parent VALUES (1,1);
466
CREATE TABLE t1_child (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1),
467
FOREIGN KEY (f1) REFERENCES t1_parent(f1))
469
--error ER_NO_REFERENCED_ROW_2
471
CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
473
INSERT INTO t1_child SET f1 = 1, f2 = 1;
474
INSERT INTO t1_child SET f1 = 2, f2 = 2;
478
--error ER_NO_REFERENCED_ROW_2
479
SELECT f1_two_inserts();
480
SELECT * FROM t1_child;
482
DROP TABLE t1_parent;
483
DROP FUNCTION f1_two_inserts;
486
DROP TABLE t1_select;
488
DROP TABLE t1_not_null;