1
DROP TABLE IF EXISTS t1_select;
2
DROP TABLE IF EXISTS t1_aux;
3
DROP TABLE IF EXISTS t1_not_null;
4
DROP VIEW IF EXISTS v1_not_null;
5
DROP VIEW IF EXISTS v1_func;
6
DROP TABLE IF EXISTS t1_fail;
7
DROP FUNCTION IF EXISTS f1_simple_insert;
8
DROP FUNCTION IF EXISTS f1_two_inserts;
9
DROP FUNCTION IF EXISTS f1_insert_select;
10
SET SESSION AUTOCOMMIT=0;
11
SET SESSION sql_mode = '';
12
CREATE TABLE t1_select (f1 BIGINT, f2 BIGINT) ENGINE = MEMORY;
13
INSERT INTO t1_select(f1,f2) VALUES (1,-1),(2,NULL),(3,0),(4,1),(5,2);
14
SELECT * FROM t1_select;
21
CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)
22
ENGINE = <transactional_engine>;
23
SELECT * FROM t1_not_null;
25
CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)
26
ENGINE = <transactional_engine>;
30
CREATE FUNCTION f1_simple_insert(my_f1 INTEGER) RETURNS INTEGER
32
INSERT INTO t1_not_null SET f1 = 10, f2 = my_f1;
36
# One f1_simple_insert execution per row, no NOT NULL violation
37
SELECT f1_simple_insert(1);
40
SELECT * FROM t1_not_null ORDER BY f1,f2;
44
SELECT * FROM t1_not_null;
46
SELECT f1_simple_insert(1) FROM t1_select;
53
SELECT * FROM t1_not_null ORDER BY f1,f2;
61
SELECT * FROM t1_not_null;
64
# One f1_simple_insert execution per row, NOT NULL violation when the
65
# SELECT processes the first row.
66
SELECT f1_simple_insert(NULL);
67
ERROR 23000: Column 'f2' cannot be null
68
SELECT * FROM t1_not_null ORDER BY f1,f2;
71
SELECT * FROM t1_not_null ORDER BY f1,f2;
73
SELECT f1_simple_insert(NULL) FROM t1_select;
74
ERROR 23000: Column 'f2' cannot be null
75
SELECT * FROM t1_not_null ORDER BY f1,f2;
78
SELECT * FROM t1_not_null ORDER BY f1,f2;
80
SELECT 1 FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1 WHERE f1_simple_insert(NULL) = 1;
81
ERROR 23000: Column 'f2' cannot be null
82
SELECT * FROM t1_not_null ORDER BY f1,f2;
85
SELECT * FROM t1_not_null ORDER BY f1,f2;
88
# One f1_simple_insert execution per row, NOT NULL violation when the
89
# SELECT processes the non first row
90
SELECT f1_simple_insert(f2) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1;
91
ERROR 23000: Column 'f2' cannot be null
92
SELECT * FROM t1_not_null ORDER BY f1,f2;
95
SELECT * FROM t1_not_null ORDER BY f1,f2;
97
SELECT f1_simple_insert(f2) FROM t1_select;
98
ERROR 23000: Column 'f2' cannot be null
99
SELECT * FROM t1_not_null ORDER BY f1,f2;
102
SELECT * FROM t1_not_null ORDER BY f1,f2;
105
# Two f1_simple_insert executions per row, NOT NULL violation when the
106
# SELECT processes the first row.
107
SELECT f1_simple_insert(1),f1_simple_insert(NULL);
108
ERROR 23000: Column 'f2' cannot be null
109
SELECT * FROM t1_not_null ORDER BY f1,f2;
112
SELECT * FROM t1_not_null ORDER BY f1,f2;
114
SELECT f1_simple_insert(NULL),f1_simple_insert(1);
115
ERROR 23000: Column 'f2' cannot be null
116
SELECT * FROM t1_not_null ORDER BY f1,f2;
119
SELECT * FROM t1_not_null ORDER BY f1,f2;
122
# Two f1_simple_insert executions per row, NOT NULL violation when the
123
# SELECT processes the non first row
124
SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1;
125
ERROR 23000: Column 'f2' cannot be null
126
SELECT * FROM t1_not_null ORDER BY f1,f2;
129
SELECT * FROM t1_not_null ORDER BY f1,f2;
131
SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1;
132
ERROR 23000: Column 'f2' cannot be null
133
SELECT * FROM t1_not_null ORDER BY f1,f2;
136
SELECT * FROM t1_not_null ORDER BY f1,f2;
138
SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM t1_select;
139
ERROR 23000: Column 'f2' cannot be null
140
SELECT * FROM t1_not_null ORDER BY f1,f2;
143
SELECT * FROM t1_not_null ORDER BY f1,f2;
145
SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM t1_select;
146
ERROR 23000: Column 'f2' cannot be null
147
SELECT * FROM t1_not_null ORDER BY f1,f2;
150
SELECT * FROM t1_not_null ORDER BY f1,f2;
152
SELECT 1 FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1
153
WHERE 1 = f1_simple_insert(f2) AND 1 = f1_simple_insert(f1);
154
ERROR 23000: Column 'f2' cannot be null
155
SELECT * FROM t1_not_null ORDER BY f1,f2;
158
SELECT * FROM t1_not_null ORDER BY f1,f2;
161
# Nested functions, the inner fails
162
SELECT f1_simple_insert(f1_simple_insert(NULL)) FROM t1_select;
163
ERROR 23000: Column 'f2' cannot be null
164
SELECT * FROM t1_not_null ORDER BY f1,f2;
167
SELECT * FROM t1_not_null ORDER BY f1,f2;
170
# Nested functions, the outer fails
171
SELECT f1_simple_insert(f1_simple_insert(1) + NULL) FROM t1_select;
172
ERROR 23000: Column 'f2' cannot be null
173
SELECT * FROM t1_not_null ORDER BY f1,f2;
176
SELECT * FROM t1_not_null ORDER BY f1,f2;
178
DROP FUNCTION f1_simple_insert;
179
CREATE FUNCTION f1_insert_select(my_f1 INTEGER) RETURNS INTEGER
181
INSERT INTO t1_not_null SELECT * FROM t1_select WHERE f1 = my_f1;
185
# f1_insert_select(2), tries to INSERT SELECT one row containing NULL
187
# - SELECT f1_insert_select(2); gives any result set and
188
# - t1_not_null gets a row inserted
189
# is covered by the manual.
190
SELECT f1_insert_select(2);
194
Warning 1048 Column 'f2' cannot be null
195
SELECT * FROM t1_not_null ORDER BY f1,f2;
199
SELECT * FROM t1_not_null ORDER BY f1,f2;
201
DROP FUNCTION f1_insert_select;
202
SET SESSION sql_mode = 'traditional';
203
CREATE FUNCTION f1_insert_select(my_f1 INTEGER) RETURNS INTEGER
205
INSERT INTO t1_not_null SELECT * FROM t1_select WHERE f1 = my_f1;
208
SELECT f1_insert_select(2);
209
ERROR 23000: Column 'f2' cannot be null
210
SELECT * FROM t1_not_null ORDER BY f1,f2;
213
SELECT * FROM t1_not_null ORDER BY f1,f2;
215
DROP FUNCTION f1_insert_select;
216
SET SESSION sql_mode = '';
219
# 1. INSERT statement: Insert one row with NULL -> NOT NULL violation
220
# 2. INSERT statement: Insert one row without NULL
221
CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
223
INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
224
INSERT INTO t1_not_null SET f1 = 10, f2 = 10;
227
SELECT f1_two_inserts();
228
ERROR 23000: Column 'f2' cannot be null
229
SELECT * FROM t1_not_null ORDER BY f1,f2;
232
SELECT * FROM t1_not_null ORDER BY f1,f2;
234
DROP FUNCTION f1_two_inserts;
237
# 1. INSERT statement: Insert one row without NULL
238
# 2. INSERT statement: Insert one row with NULL -> NOT NULL violation
239
CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
241
INSERT INTO t1_not_null SET f1 = 10, f2 = 10;
242
INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
245
SELECT f1_two_inserts();
246
ERROR 23000: Column 'f2' cannot be null
247
SELECT * FROM t1_not_null ORDER BY f1,f2;
250
SELECT * FROM t1_not_null ORDER BY f1,f2;
254
# INSERT statement: Insert two rows
255
# first row without NULL
256
# second row with NULL -> NOT NULL violation
257
# -> NOT NULL violation
258
CREATE FUNCTION f1_insert_with_two_rows() RETURNS INTEGER
260
INSERT INTO t1_not_null(f1,f2) VALUES (10,10),(10,NULL);
264
# - SELECT f1_insert_with_two_rows(); gives any result set and
265
# - t1_not_null gets a row inserted
266
# is covered by the manual.
267
SELECT f1_insert_with_two_rows();
268
f1_insert_with_two_rows()
271
Warning 1048 Column 'f2' cannot be null
272
SELECT * FROM t1_not_null ORDER BY f1,f2;
277
SELECT * FROM t1_not_null ORDER BY f1,f2;
279
DROP FUNCTION f1_insert_with_two_rows;
280
SET SESSION sql_mode = 'traditional';
281
CREATE FUNCTION f1_insert_with_two_rows() RETURNS INTEGER
283
INSERT INTO t1_not_null(f1,f2) VALUES (10,10),(10,NULL);
286
SELECT f1_insert_with_two_rows();
287
ERROR 23000: Column 'f2' cannot be null
288
SELECT * FROM t1_not_null ORDER BY f1,f2;
291
SELECT * FROM t1_not_null ORDER BY f1,f2;
293
SET SESSION sql_mode = '';
295
# FUNCTION in Correlated Subquery
296
SELECT 1 FROM t1_select t1
297
WHERE 1 = (SELECT f1_insert_with_two_rows() FROM t1_select t2
298
WHERE t2.f1 = t1.f1);
299
ERROR 23000: Column 'f2' cannot be null
300
SELECT * FROM t1_not_null ORDER BY f1,f2;
303
SELECT * FROM t1_not_null ORDER BY f1,f2;
307
SELECT 1 FROM t1_select t1, t1_select t2
308
WHERE t1.f1 = t2.f1 AND t2.f1 = f1_insert_with_two_rows();
309
ERROR 23000: Column 'f2' cannot be null
310
SELECT * FROM t1_not_null ORDER BY f1,f2;
313
SELECT * FROM t1_not_null ORDER BY f1,f2;
315
SELECT STRAIGHT_JOIN * FROM t1_select t2 RIGHT JOIN t1_select t1
316
ON t1.f1 = t1.f1 WHERE 1 = f1_insert_with_two_rows();
317
ERROR 23000: Column 'f2' cannot be null
318
DROP FUNCTION f1_insert_with_two_rows;
323
SELECT f1_two_inserts();
324
ERROR 23000: Column 'f2' cannot be null
325
SELECT * FROM t1_not_null ORDER BY f1,f2;
328
SELECT * FROM t1_not_null ORDER BY f1,f2;
332
INSERT INTO t1_aux SET f1 = 1, f2 = f1_two_inserts();
333
ERROR 23000: Column 'f2' cannot be null
334
SELECT * FROM t1_not_null ORDER BY f1,f2;
337
SELECT * FROM t1_not_null ORDER BY f1,f2;
339
INSERT INTO t1_aux SELECT 1, f1_two_inserts();
340
ERROR 23000: Column 'f2' cannot be null
341
SELECT * FROM t1_not_null ORDER BY f1,f2;
344
SELECT * FROM t1_not_null ORDER BY f1,f2;
346
SELECT * FROM t1_aux ORDER BY f1,f2;
348
INSERT INTO t1_aux VALUES(1,f1_two_inserts());
349
ERROR 23000: Column 'f2' cannot be null
350
SELECT * FROM t1_not_null ORDER BY f1,f2;
352
SELECT * FROM t1_aux ORDER BY f1,f2;
356
INSERT INTO t1_aux VALUES (1,1);
358
DELETE FROM t1_aux WHERE f1 = f1_two_inserts();
359
ERROR 23000: Column 'f2' cannot be null
360
SELECT * FROM t1_not_null ORDER BY f1,f2;
363
SELECT * FROM t1_not_null ORDER BY f1,f2;
365
SELECT * FROM t1_aux ORDER BY f1,f2;
369
# FUNCTION in UPDATE SET
370
UPDATE t1_aux SET f2 = f1_two_inserts() + 1;
371
ERROR 23000: Column 'f2' cannot be null
372
SELECT * FROM t1_not_null ORDER BY f1,f2;
375
SELECT * FROM t1_not_null ORDER BY f1,f2;
377
SELECT * FROM t1_aux ORDER BY f1,f2;
381
# FUNCTION in VIEW definition
382
CREATE VIEW v1_func AS SELECT f1_two_inserts() FROM t1_select;
383
SELECT * FROM v1_func;
384
ERROR 23000: Column 'f2' cannot be null
385
SELECT * FROM t1_not_null ORDER BY f1,f2;
388
SELECT * FROM t1_not_null ORDER BY f1,f2;
392
# FUNCTION in CREATE TABLE ... AS SELECT
393
CREATE TABLE t1_fail AS SELECT f1_two_inserts() FROM t1_select;
394
ERROR 23000: Column 'f2' cannot be null
395
SELECT * FROM t1_not_null ORDER BY f1,f2;
397
CREATE TABLE t1_fail AS SELECT * FROM t1_select WHERE 1 = f1_two_inserts();
398
ERROR 23000: Column 'f2' cannot be null
399
SELECT * FROM t1_not_null ORDER BY f1,f2;
402
# FUNCTION in ORDER BY
403
SELECT * FROM t1_select ORDER BY f1,f1_two_inserts();
404
ERROR 23000: Column 'f2' cannot be null
405
SELECT * FROM t1_not_null ORDER BY f1,f2;
408
# FUNCTION in aggregate function
409
SELECT AVG(f1_two_inserts()) FROM t1_select;
410
ERROR 23000: Column 'f2' cannot be null
411
SELECT * FROM t1_not_null ORDER BY f1,f2;
415
SELECT 1 FROM t1_select HAVING AVG(f1) = f1_two_inserts() + 2;
416
ERROR 23000: Column 'f2' cannot be null
417
SELECT * FROM t1_not_null ORDER BY f1,f2;
419
DROP FUNCTION f1_two_inserts;
421
# FUNCTION modifies Updatable VIEW
422
CREATE VIEW v1_not_null AS SELECT f1,f2 FROM t1_not_null WITH CHECK OPTION;
423
CREATE FUNCTION f1_two_inserts_v1() RETURNS INTEGER
425
INSERT INTO v1_not_null SET f1 = 10, f2 = 10;
426
INSERT INTO v1_not_null SET f1 = 10, f2 = NULL;
429
SELECT f1_two_inserts_v1();
430
ERROR 23000: Column 'f2' cannot be null
431
SELECT * FROM t1_not_null ORDER BY f1,f2;
434
SELECT * FROM t1_not_null ORDER BY f1,f2;
436
DROP FUNCTION f1_two_inserts_v1;
437
DROP VIEW v1_not_null;
439
# FUNCTION causes FOREIGN KEY constraint violation
440
CREATE TABLE t1_parent (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1))
442
INSERT INTO t1_parent VALUES (1,1);
443
CREATE TABLE t1_child (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1),
444
FOREIGN KEY (f1) REFERENCES t1_parent(f1))
446
CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
448
INSERT INTO t1_child SET f1 = 1, f2 = 1;
449
INSERT INTO t1_child SET f1 = 2, f2 = 2;
452
SELECT f1_two_inserts();
453
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1_child`, CONSTRAINT `t1_child_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1_parent` (`f1`))
454
SELECT * FROM t1_child;
457
DROP TABLE t1_parent;
458
DROP FUNCTION f1_two_inserts;
459
DROP TABLE t1_select;
461
DROP TABLE t1_not_null;