1DROP TABLE IF EXISTS t1_select; 2DROP TABLE IF EXISTS t1_aux; 3DROP TABLE IF EXISTS t1_not_null; 4DROP VIEW IF EXISTS v1_not_null; 5DROP VIEW IF EXISTS v1_func; 6DROP TABLE IF EXISTS t1_fail; 7DROP FUNCTION IF EXISTS f1_simple_insert; 8DROP FUNCTION IF EXISTS f1_two_inserts; 9DROP FUNCTION IF EXISTS f1_insert_select; 10SET SESSION AUTOCOMMIT=0; 11SET SESSION sql_mode = ''; 12CREATE TABLE t1_select (f1 BIGINT, f2 BIGINT) ENGINE = MEMORY; 13INSERT INTO t1_select(f1,f2) VALUES (1,-1),(2,NULL),(3,0),(4,1),(5,2); 14SELECT * FROM t1_select; 15f1 f2 161 -1 172 NULL 183 0 194 1 205 2 21CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL) 22ENGINE = <transactional_engine>; 23SELECT * FROM t1_not_null; 24f1 f2 25CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT) 26ENGINE = <transactional_engine>; 27SELECT * FROM t1_aux; 28f1 f2 29COMMIT; 30CREATE FUNCTION f1_simple_insert(my_f1 INTEGER) RETURNS INTEGER 31BEGIN 32INSERT INTO t1_not_null SET f1 = 10, f2 = my_f1; 33RETURN 1; 34END// 35 36# One f1_simple_insert execution per row, no NOT NULL violation 37SELECT f1_simple_insert(1); 38f1_simple_insert(1) 391 40SELECT * FROM t1_not_null ORDER BY f1,f2; 41f1 f2 4210 1 43ROLLBACK; 44SELECT * FROM t1_not_null; 45f1 f2 46SELECT f1_simple_insert(1) FROM t1_select; 47f1_simple_insert(1) 481 491 501 511 521 53SELECT * FROM t1_not_null ORDER BY f1,f2; 54f1 f2 5510 1 5610 1 5710 1 5810 1 5910 1 60ROLLBACK; 61SELECT * FROM t1_not_null; 62f1 f2 63 64# One f1_simple_insert execution per row, NOT NULL violation when the 65# SELECT processes the first row. 66SELECT f1_simple_insert(NULL); 67ERROR 23000: Column 'f2' cannot be null 68SELECT * FROM t1_not_null ORDER BY f1,f2; 69f1 f2 70ROLLBACK; 71SELECT * FROM t1_not_null ORDER BY f1,f2; 72f1 f2 73SELECT f1_simple_insert(NULL) FROM t1_select; 74ERROR 23000: Column 'f2' cannot be null 75SELECT * FROM t1_not_null ORDER BY f1,f2; 76f1 f2 77ROLLBACK; 78SELECT * FROM t1_not_null ORDER BY f1,f2; 79f1 f2 80SELECT 1 FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1 WHERE f1_simple_insert(NULL) = 1; 81ERROR 23000: Column 'f2' cannot be null 82SELECT * FROM t1_not_null ORDER BY f1,f2; 83f1 f2 84ROLLBACK; 85SELECT * FROM t1_not_null ORDER BY f1,f2; 86f1 f2 87 88# One f1_simple_insert execution per row, NOT NULL violation when the 89# SELECT processes the non first row 90SELECT f1_simple_insert(f2) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1; 91ERROR 23000: Column 'f2' cannot be null 92SELECT * FROM t1_not_null ORDER BY f1,f2; 93f1 f2 94ROLLBACK; 95SELECT * FROM t1_not_null ORDER BY f1,f2; 96f1 f2 97SELECT f1_simple_insert(f2) FROM t1_select; 98ERROR 23000: Column 'f2' cannot be null 99SELECT * FROM t1_not_null ORDER BY f1,f2; 100f1 f2 101ROLLBACK; 102SELECT * FROM t1_not_null ORDER BY f1,f2; 103f1 f2 104 105# Two f1_simple_insert executions per row, NOT NULL violation when the 106# SELECT processes the first row. 107SELECT f1_simple_insert(1),f1_simple_insert(NULL); 108ERROR 23000: Column 'f2' cannot be null 109SELECT * FROM t1_not_null ORDER BY f1,f2; 110f1 f2 111ROLLBACK; 112SELECT * FROM t1_not_null ORDER BY f1,f2; 113f1 f2 114SELECT f1_simple_insert(NULL),f1_simple_insert(1); 115ERROR 23000: Column 'f2' cannot be null 116SELECT * FROM t1_not_null ORDER BY f1,f2; 117f1 f2 118ROLLBACK; 119SELECT * FROM t1_not_null ORDER BY f1,f2; 120f1 f2 121 122# Two f1_simple_insert executions per row, NOT NULL violation when the 123# SELECT processes the non first row 124SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1; 125ERROR 23000: Column 'f2' cannot be null 126SELECT * FROM t1_not_null ORDER BY f1,f2; 127f1 f2 128ROLLBACK; 129SELECT * FROM t1_not_null ORDER BY f1,f2; 130f1 f2 131SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1; 132ERROR 23000: Column 'f2' cannot be null 133SELECT * FROM t1_not_null ORDER BY f1,f2; 134f1 f2 135ROLLBACK; 136SELECT * FROM t1_not_null ORDER BY f1,f2; 137f1 f2 138SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM t1_select; 139ERROR 23000: Column 'f2' cannot be null 140SELECT * FROM t1_not_null ORDER BY f1,f2; 141f1 f2 142ROLLBACK; 143SELECT * FROM t1_not_null ORDER BY f1,f2; 144f1 f2 145SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM t1_select; 146ERROR 23000: Column 'f2' cannot be null 147SELECT * FROM t1_not_null ORDER BY f1,f2; 148f1 f2 149ROLLBACK; 150SELECT * FROM t1_not_null ORDER BY f1,f2; 151f1 f2 152SELECT 1 FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1 153WHERE 1 = f1_simple_insert(f2) AND 1 = f1_simple_insert(f1); 154ERROR 23000: Column 'f2' cannot be null 155SELECT * FROM t1_not_null ORDER BY f1,f2; 156f1 f2 157ROLLBACK; 158SELECT * FROM t1_not_null ORDER BY f1,f2; 159f1 f2 160 161# Nested functions, the inner fails 162SELECT f1_simple_insert(f1_simple_insert(NULL)) FROM t1_select; 163ERROR 23000: Column 'f2' cannot be null 164SELECT * FROM t1_not_null ORDER BY f1,f2; 165f1 f2 166ROLLBACK; 167SELECT * FROM t1_not_null ORDER BY f1,f2; 168f1 f2 169 170# Nested functions, the outer fails 171SELECT f1_simple_insert(f1_simple_insert(1) + NULL) FROM t1_select; 172ERROR 23000: Column 'f2' cannot be null 173SELECT * FROM t1_not_null ORDER BY f1,f2; 174f1 f2 175ROLLBACK; 176SELECT * FROM t1_not_null ORDER BY f1,f2; 177f1 f2 178DROP FUNCTION f1_simple_insert; 179CREATE FUNCTION f1_insert_select(my_f1 INTEGER) RETURNS INTEGER 180BEGIN 181INSERT INTO t1_not_null SELECT * FROM t1_select WHERE f1 = my_f1; 182RETURN 1; 183END; 184 185# f1_insert_select(2), tries to INSERT SELECT one row containing NULL 186# The fact that 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. 190SELECT f1_insert_select(2); 191f1_insert_select(2) 1921 193SELECT * FROM t1_not_null ORDER BY f1,f2; 194f1 f2 1952 0 196ROLLBACK; 197SELECT * FROM t1_not_null ORDER BY f1,f2; 198f1 f2 199DROP FUNCTION f1_insert_select; 200SET SESSION sql_mode = 'traditional'; 201CREATE FUNCTION f1_insert_select(my_f1 INTEGER) RETURNS INTEGER 202BEGIN 203INSERT INTO t1_not_null SELECT * FROM t1_select WHERE f1 = my_f1; 204RETURN 1; 205END; 206SELECT f1_insert_select(2); 207ERROR 23000: Column 'f2' cannot be null 208SELECT * FROM t1_not_null ORDER BY f1,f2; 209f1 f2 210ROLLBACK; 211SELECT * FROM t1_not_null ORDER BY f1,f2; 212f1 f2 213DROP FUNCTION f1_insert_select; 214SET SESSION sql_mode = ''; 215 216# Function tries to 217# 1. INSERT statement: Insert one row with NULL -> NOT NULL violation 218# 2. INSERT statement: Insert one row without NULL 219CREATE FUNCTION f1_two_inserts() RETURNS INTEGER 220BEGIN 221INSERT INTO t1_not_null SET f1 = 10, f2 = NULL; 222INSERT INTO t1_not_null SET f1 = 10, f2 = 10; 223RETURN 1; 224END// 225SELECT f1_two_inserts(); 226ERROR 23000: Column 'f2' cannot be null 227SELECT * FROM t1_not_null ORDER BY f1,f2; 228f1 f2 229ROLLBACK; 230SELECT * FROM t1_not_null ORDER BY f1,f2; 231f1 f2 232DROP FUNCTION f1_two_inserts; 233 234# Function tries to 235# 1. INSERT statement: Insert one row without NULL 236# 2. INSERT statement: Insert one row with NULL -> NOT NULL violation 237CREATE FUNCTION f1_two_inserts() RETURNS INTEGER 238BEGIN 239INSERT INTO t1_not_null SET f1 = 10, f2 = 10; 240INSERT INTO t1_not_null SET f1 = 10, f2 = NULL; 241RETURN 1; 242END// 243SELECT f1_two_inserts(); 244ERROR 23000: Column 'f2' cannot be null 245SELECT * FROM t1_not_null ORDER BY f1,f2; 246f1 f2 247ROLLBACK; 248SELECT * FROM t1_not_null ORDER BY f1,f2; 249f1 f2 250 251# Function tries to 252# INSERT statement: Insert two rows 253# first row without NULL 254# second row with NULL -> NOT NULL violation 255# -> NOT NULL violation 256CREATE FUNCTION f1_insert_with_two_rows() RETURNS INTEGER 257BEGIN 258INSERT INTO t1_not_null(f1,f2) VALUES (10,10),(10,NULL); 259RETURN 1; 260END; 261# The fact that 262# - SELECT f1_insert_with_two_rows(); gives any result set and 263# - t1_not_null gets a row inserted 264# is covered by the manual. 265SELECT f1_insert_with_two_rows(); 266f1_insert_with_two_rows() 2671 268SELECT * FROM t1_not_null ORDER BY f1,f2; 269f1 f2 27010 0 27110 10 272ROLLBACK; 273SELECT * FROM t1_not_null ORDER BY f1,f2; 274f1 f2 275DROP FUNCTION f1_insert_with_two_rows; 276SET SESSION sql_mode = 'traditional'; 277CREATE FUNCTION f1_insert_with_two_rows() RETURNS INTEGER 278BEGIN 279INSERT INTO t1_not_null(f1,f2) VALUES (10,10),(10,NULL); 280RETURN 1; 281END; 282SELECT f1_insert_with_two_rows(); 283ERROR 23000: Column 'f2' cannot be null 284SELECT * FROM t1_not_null ORDER BY f1,f2; 285f1 f2 286ROLLBACK; 287SELECT * FROM t1_not_null ORDER BY f1,f2; 288f1 f2 289SET SESSION sql_mode = ''; 290 291# FUNCTION in Correlated Subquery 292SELECT 1 FROM t1_select t1 293WHERE 1 = (SELECT f1_insert_with_two_rows() FROM t1_select t2 294WHERE t2.f1 = t1.f1); 295ERROR 23000: Column 'f2' cannot be null 296SELECT * FROM t1_not_null ORDER BY f1,f2; 297f1 f2 298ROLLBACK; 299SELECT * FROM t1_not_null ORDER BY f1,f2; 300f1 f2 301 302# FUNCTION in JOIN 303SELECT 1 FROM t1_select t1, t1_select t2 304WHERE t1.f1 = t2.f1 AND t2.f1 = f1_insert_with_two_rows(); 305ERROR 23000: Column 'f2' cannot be null 306SELECT * FROM t1_not_null ORDER BY f1,f2; 307f1 f2 308ROLLBACK; 309SELECT * FROM t1_not_null ORDER BY f1,f2; 310f1 f2 311SELECT STRAIGHT_JOIN * FROM t1_select t2 RIGHT JOIN t1_select t1 312ON t1.f1 = t1.f1 WHERE 1 = f1_insert_with_two_rows(); 313ERROR 23000: Column 'f2' cannot be null 314DROP FUNCTION f1_insert_with_two_rows; 315 316# FUNCTION in UNION 317SELECT 1 318UNION ALL 319SELECT f1_two_inserts(); 320ERROR 23000: Column 'f2' cannot be null 321SELECT * FROM t1_not_null ORDER BY f1,f2; 322f1 f2 323ROLLBACK; 324SELECT * FROM t1_not_null ORDER BY f1,f2; 325f1 f2 326 327# FUNCTION in INSERT 328INSERT INTO t1_aux SET f1 = 1, f2 = f1_two_inserts(); 329ERROR 23000: Column 'f2' cannot be null 330SELECT * FROM t1_not_null ORDER BY f1,f2; 331f1 f2 332ROLLBACK; 333SELECT * FROM t1_not_null ORDER BY f1,f2; 334f1 f2 335INSERT INTO t1_aux SELECT 1, f1_two_inserts(); 336ERROR 23000: Column 'f2' cannot be null 337SELECT * FROM t1_not_null ORDER BY f1,f2; 338f1 f2 339ROLLBACK; 340SELECT * FROM t1_not_null ORDER BY f1,f2; 341f1 f2 342SELECT * FROM t1_aux ORDER BY f1,f2; 343f1 f2 344INSERT INTO t1_aux VALUES(1,f1_two_inserts()); 345ERROR 23000: Column 'f2' cannot be null 346SELECT * FROM t1_not_null ORDER BY f1,f2; 347f1 f2 348SELECT * FROM t1_aux ORDER BY f1,f2; 349f1 f2 350 351# FUNCTION in DELETE 352INSERT INTO t1_aux VALUES (1,1); 353COMMIT; 354DELETE FROM t1_aux WHERE f1 = f1_two_inserts(); 355ERROR 23000: Column 'f2' cannot be null 356SELECT * FROM t1_not_null ORDER BY f1,f2; 357f1 f2 358ROLLBACK; 359SELECT * FROM t1_not_null ORDER BY f1,f2; 360f1 f2 361SELECT * FROM t1_aux ORDER BY f1,f2; 362f1 f2 3631 1 364 365# FUNCTION in UPDATE SET 366UPDATE t1_aux SET f2 = f1_two_inserts() + 1; 367ERROR 23000: Column 'f2' cannot be null 368SELECT * FROM t1_not_null ORDER BY f1,f2; 369f1 f2 370ROLLBACK; 371SELECT * FROM t1_not_null ORDER BY f1,f2; 372f1 f2 373SELECT * FROM t1_aux ORDER BY f1,f2; 374f1 f2 3751 1 376 377# FUNCTION in VIEW definition 378CREATE VIEW v1_func AS SELECT f1_two_inserts() FROM t1_select; 379SELECT * FROM v1_func; 380ERROR 23000: Column 'f2' cannot be null 381SELECT * FROM t1_not_null ORDER BY f1,f2; 382f1 f2 383ROLLBACK; 384SELECT * FROM t1_not_null ORDER BY f1,f2; 385f1 f2 386DROP VIEW v1_func; 387 388# FUNCTION in CREATE TABLE ... AS SELECT 389CREATE TABLE t1_fail AS SELECT f1_two_inserts() FROM t1_select; 390ERROR 23000: Column 'f2' cannot be null 391SELECT * FROM t1_not_null ORDER BY f1,f2; 392f1 f2 393CREATE TABLE t1_fail AS SELECT * FROM t1_select WHERE 1 = f1_two_inserts(); 394ERROR 23000: Column 'f2' cannot be null 395SELECT * FROM t1_not_null ORDER BY f1,f2; 396f1 f2 397 398# FUNCTION in ORDER BY 399SELECT * FROM t1_select ORDER BY f1,f1_two_inserts(); 400ERROR 23000: Column 'f2' cannot be null 401SELECT * FROM t1_not_null ORDER BY f1,f2; 402f1 f2 403 404# FUNCTION in aggregate function 405SELECT AVG(f1_two_inserts()) FROM t1_select; 406ERROR 23000: Column 'f2' cannot be null 407SELECT * FROM t1_not_null ORDER BY f1,f2; 408f1 f2 409 410# FUNCTION in HAVING 411SELECT 1 FROM t1_select HAVING AVG(f1) = f1_two_inserts() + 2; 412ERROR 23000: Column 'f2' cannot be null 413SELECT * FROM t1_not_null ORDER BY f1,f2; 414f1 f2 415DROP FUNCTION f1_two_inserts; 416 417# FUNCTION modifies Updatable VIEW 418CREATE VIEW v1_not_null AS SELECT f1,f2 FROM t1_not_null WITH CHECK OPTION; 419CREATE FUNCTION f1_two_inserts_v1() RETURNS INTEGER 420BEGIN 421INSERT INTO v1_not_null SET f1 = 10, f2 = 10; 422INSERT INTO v1_not_null SET f1 = 10, f2 = NULL; 423RETURN 1; 424END// 425SELECT f1_two_inserts_v1(); 426ERROR 23000: Column 'f2' cannot be null 427SELECT * FROM t1_not_null ORDER BY f1,f2; 428f1 f2 429ROLLBACK; 430SELECT * FROM t1_not_null ORDER BY f1,f2; 431f1 f2 432DROP FUNCTION f1_two_inserts_v1; 433DROP VIEW v1_not_null; 434 435# FUNCTION causes FOREIGN KEY constraint violation 436CREATE TABLE t1_parent (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1)) 437ENGINE = InnoDB; 438INSERT INTO t1_parent VALUES (1,1); 439CREATE TABLE t1_child (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1), 440FOREIGN KEY (f1) REFERENCES t1_parent(f1)) 441ENGINE = InnoDB; 442CREATE FUNCTION f1_two_inserts() RETURNS INTEGER 443BEGIN 444INSERT INTO t1_child SET f1 = 1, f2 = 1; 445INSERT INTO t1_child SET f1 = 2, f2 = 2; 446RETURN 1; 447END// 448SELECT f1_two_inserts(); 449ERROR 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`)) 450SELECT * FROM t1_child; 451f1 f2 452DROP TABLE t1_child; 453DROP TABLE t1_parent; 454DROP FUNCTION f1_two_inserts; 455DROP TABLE t1_select; 456DROP TABLE t1_aux; 457DROP TABLE t1_not_null; 458