1# func_rollback.test 2# 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. 13# 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 17# modifying a table 18# - one of the modifying statements within one of the functions fails 19# - the table remains at least partially modified 20# 21# = There is no automatic ROLLBACK of changes caused by the failing 22# statement A. 23# = Statement A is not atomic. 24# 25# Notes: 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. 37# 38# Created: 39# 2008-04-09 mleich 40# 41 42let $fixed_bug_35877 = 0; 43 44let $from_select = SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL; 45 46--source include/have_innodb.inc 47let $engine = InnoDB; 48 49--disable_warnings 50DROP TABLE IF EXISTS t1_select; 51DROP TABLE IF EXISTS t1_aux; 52DROP TABLE IF EXISTS t1_not_null; 53DROP VIEW IF EXISTS v1_not_null; 54DROP VIEW IF EXISTS v1_func; 55DROP TABLE IF EXISTS t1_fail; 56DROP FUNCTION IF EXISTS f1_simple_insert; 57DROP FUNCTION IF EXISTS f1_two_inserts; 58DROP FUNCTION IF EXISTS f1_insert_select; 59--enable_warnings 60 61SET SESSION AUTOCOMMIT=0; 62SET SESSION sql_mode = ''; 63 64CREATE TABLE t1_select (f1 BIGINT, f2 BIGINT) ENGINE = MEMORY; 65INSERT INTO t1_select(f1,f2) VALUES (1,-1),(2,NULL),(3,0),(4,1),(5,2); 66SELECT * FROM t1_select; 67 68--replace_result $engine <transactional_engine> 69eval 70CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL) 71ENGINE = $engine; 72SELECT * FROM t1_not_null; 73 74--replace_result $engine <transactional_engine> 75eval 76CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT) 77ENGINE = $engine; 78SELECT * FROM t1_aux; 79COMMIT; 80 81# FUNCTION with "simple" INSERT 82delimiter //; 83CREATE FUNCTION f1_simple_insert(my_f1 INTEGER) RETURNS INTEGER 84BEGIN 85 INSERT INTO t1_not_null SET f1 = 10, f2 = my_f1; 86 RETURN 1; 87END// 88delimiter ;// 89 90--echo 91--echo # One f1_simple_insert execution per row, no NOT NULL violation 92SELECT f1_simple_insert(1); 93SELECT * FROM t1_not_null ORDER BY f1,f2; 94ROLLBACK; 95SELECT * FROM t1_not_null; 96# 97SELECT f1_simple_insert(1) FROM t1_select; 98SELECT * FROM t1_not_null ORDER BY f1,f2; 99ROLLBACK; 100SELECT * FROM t1_not_null; 101 102--echo 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 106SELECT f1_simple_insert(NULL); 107SELECT * FROM t1_not_null ORDER BY f1,f2; 108ROLLBACK; 109SELECT * FROM t1_not_null ORDER BY f1,f2; 110# 111--error ER_BAD_NULL_ERROR 112SELECT f1_simple_insert(NULL) FROM t1_select; 113SELECT * FROM t1_not_null ORDER BY f1,f2; 114ROLLBACK; 115SELECT * FROM t1_not_null ORDER BY f1,f2; 116# 117--error ER_BAD_NULL_ERROR 118eval SELECT 1 FROM ($from_select) AS t1 WHERE f1_simple_insert(NULL) = 1; 119SELECT * FROM t1_not_null ORDER BY f1,f2; 120ROLLBACK; 121SELECT * FROM t1_not_null ORDER BY f1,f2; 122 123--echo 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 127eval SELECT f1_simple_insert(f2) FROM ($from_select) AS t1; 128SELECT * FROM t1_not_null ORDER BY f1,f2; 129ROLLBACK; 130SELECT * FROM t1_not_null ORDER BY f1,f2; 131# 132--error ER_BAD_NULL_ERROR 133SELECT f1_simple_insert(f2) FROM t1_select; 134SELECT * FROM t1_not_null ORDER BY f1,f2; 135ROLLBACK; 136SELECT * FROM t1_not_null ORDER BY f1,f2; 137 138--echo 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 142SELECT f1_simple_insert(1),f1_simple_insert(NULL); 143SELECT * FROM t1_not_null ORDER BY f1,f2; 144ROLLBACK; 145SELECT * FROM t1_not_null ORDER BY f1,f2; 146# 147--error ER_BAD_NULL_ERROR 148SELECT f1_simple_insert(NULL),f1_simple_insert(1); 149SELECT * FROM t1_not_null ORDER BY f1,f2; 150ROLLBACK; 151SELECT * FROM t1_not_null ORDER BY f1,f2; 152 153--echo 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 157eval SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM ($from_select) AS t1; 158SELECT * FROM t1_not_null ORDER BY f1,f2; 159ROLLBACK; 160SELECT * FROM t1_not_null ORDER BY f1,f2; 161# 162--error ER_BAD_NULL_ERROR 163eval SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM ($from_select) AS t1; 164SELECT * FROM t1_not_null ORDER BY f1,f2; 165ROLLBACK; 166SELECT * FROM t1_not_null ORDER BY f1,f2; 167# 168--error ER_BAD_NULL_ERROR 169SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM t1_select; 170SELECT * FROM t1_not_null ORDER BY f1,f2; 171ROLLBACK; 172SELECT * FROM t1_not_null ORDER BY f1,f2; 173# 174--error ER_BAD_NULL_ERROR 175SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM t1_select; 176SELECT * FROM t1_not_null ORDER BY f1,f2; 177ROLLBACK; 178SELECT * FROM t1_not_null ORDER BY f1,f2; 179# 180--error ER_BAD_NULL_ERROR 181eval SELECT 1 FROM ($from_select) AS t1 182WHERE 1 = f1_simple_insert(f2) AND 1 = f1_simple_insert(f1); 183SELECT * FROM t1_not_null ORDER BY f1,f2; 184ROLLBACK; 185SELECT * FROM t1_not_null ORDER BY f1,f2; 186 187--echo 188--echo # Nested functions, the inner fails 189--error ER_BAD_NULL_ERROR 190SELECT f1_simple_insert(f1_simple_insert(NULL)) FROM t1_select; 191SELECT * FROM t1_not_null ORDER BY f1,f2; 192ROLLBACK; 193SELECT * FROM t1_not_null ORDER BY f1,f2; 194--echo 195--echo # Nested functions, the outer fails 196--error ER_BAD_NULL_ERROR 197SELECT f1_simple_insert(f1_simple_insert(1) + NULL) FROM t1_select; 198SELECT * FROM t1_not_null ORDER BY f1,f2; 199ROLLBACK; 200SELECT * FROM t1_not_null ORDER BY f1,f2; 201DROP FUNCTION f1_simple_insert; 202 203# FUNCTION with INSERT ... SELECT 204delimiter //; 205let $f1_insert_select = 206CREATE FUNCTION f1_insert_select(my_f1 INTEGER) RETURNS INTEGER 207BEGIN 208 INSERT INTO t1_not_null SELECT * FROM t1_select WHERE f1 = my_f1; 209 RETURN 1; 210END// 211delimiter ;// 212eval $f1_insert_select; 213# 214--echo 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 221SELECT f1_insert_select(2); 222SELECT * FROM t1_not_null ORDER BY f1,f2; 223ROLLBACK; 224SELECT * FROM t1_not_null ORDER BY f1,f2; 225DROP FUNCTION f1_insert_select; 226# 227SET SESSION sql_mode = 'traditional'; 228eval $f1_insert_select; 229--error ER_BAD_NULL_ERROR 230SELECT f1_insert_select(2); 231SELECT * FROM t1_not_null ORDER BY f1,f2; 232ROLLBACK; 233SELECT * FROM t1_not_null ORDER BY f1,f2; 234DROP FUNCTION f1_insert_select; 235SET SESSION sql_mode = ''; 236 237# FUNCTION with two simple INSERTs 238--echo 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 243# error happens. 244delimiter //; 245CREATE FUNCTION f1_two_inserts() RETURNS INTEGER 246BEGIN 247 INSERT INTO t1_not_null SET f1 = 10, f2 = NULL; 248 INSERT INTO t1_not_null SET f1 = 10, f2 = 10; 249 RETURN 1; 250END// 251delimiter ;// 252--error ER_BAD_NULL_ERROR 253SELECT f1_two_inserts(); 254SELECT * FROM t1_not_null ORDER BY f1,f2; 255ROLLBACK; 256SELECT * FROM t1_not_null ORDER BY f1,f2; 257DROP FUNCTION f1_two_inserts; 258# 259--echo 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 263delimiter //; 264CREATE FUNCTION f1_two_inserts() RETURNS INTEGER 265BEGIN 266 INSERT INTO t1_not_null SET f1 = 10, f2 = 10; 267 INSERT INTO t1_not_null SET f1 = 10, f2 = NULL; 268 RETURN 1; 269END// 270delimiter ;// 271--error ER_BAD_NULL_ERROR 272SELECT f1_two_inserts(); 273SELECT * FROM t1_not_null ORDER BY f1,f2; 274ROLLBACK; 275SELECT * FROM t1_not_null ORDER BY f1,f2; 276 277--echo 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 283delimiter //; 284let $f1_insert_with_two_rows = 285CREATE FUNCTION f1_insert_with_two_rows() RETURNS INTEGER 286BEGIN 287 INSERT INTO t1_not_null(f1,f2) VALUES (10,10),(10,NULL); 288 RETURN 1; 289END// 290delimiter ;// 291eval $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 297SELECT f1_insert_with_two_rows(); 298SELECT * FROM t1_not_null ORDER BY f1,f2; 299ROLLBACK; 300SELECT * FROM t1_not_null ORDER BY f1,f2; 301DROP FUNCTION f1_insert_with_two_rows; 302# 303SET SESSION sql_mode = 'traditional'; 304eval $f1_insert_with_two_rows; 305--error ER_BAD_NULL_ERROR 306SELECT f1_insert_with_two_rows(); 307SELECT * FROM t1_not_null ORDER BY f1,f2; 308ROLLBACK; 309SELECT * FROM t1_not_null ORDER BY f1,f2; 310SET SESSION sql_mode = ''; 311 312--echo 313--echo # FUNCTION in Correlated Subquery 314--error ER_BAD_NULL_ERROR 315SELECT 1 FROM t1_select t1 316WHERE 1 = (SELECT f1_insert_with_two_rows() FROM t1_select t2 317 WHERE t2.f1 = t1.f1); 318SELECT * FROM t1_not_null ORDER BY f1,f2; 319ROLLBACK; 320SELECT * FROM t1_not_null ORDER BY f1,f2; 321 322--echo 323--echo # FUNCTION in JOIN 324--error ER_BAD_NULL_ERROR 325SELECT 1 FROM t1_select t1, t1_select t2 326WHERE t1.f1 = t2.f1 AND t2.f1 = f1_insert_with_two_rows(); 327SELECT * FROM t1_not_null ORDER BY f1,f2; 328ROLLBACK; 329SELECT * FROM t1_not_null ORDER BY f1,f2; 330# 331--error ER_BAD_NULL_ERROR 332SELECT STRAIGHT_JOIN * FROM t1_select t2 RIGHT JOIN t1_select t1 333ON t1.f1 = t1.f1 WHERE 1 = f1_insert_with_two_rows(); 334 335DROP FUNCTION f1_insert_with_two_rows; 336 337--echo 338--echo # FUNCTION in UNION 339--error ER_BAD_NULL_ERROR 340SELECT 1 341UNION ALL 342SELECT f1_two_inserts(); 343SELECT * FROM t1_not_null ORDER BY f1,f2; 344ROLLBACK; 345SELECT * FROM t1_not_null ORDER BY f1,f2; 346 347--echo 348--echo # FUNCTION in INSERT 349--error ER_BAD_NULL_ERROR 350INSERT INTO t1_aux SET f1 = 1, f2 = f1_two_inserts(); 351SELECT * FROM t1_not_null ORDER BY f1,f2; 352ROLLBACK; 353SELECT * FROM t1_not_null ORDER BY f1,f2; 354# 355--error ER_BAD_NULL_ERROR 356INSERT INTO t1_aux SELECT 1, f1_two_inserts(); 357SELECT * FROM t1_not_null ORDER BY f1,f2; 358ROLLBACK; 359SELECT * FROM t1_not_null ORDER BY f1,f2; 360SELECT * FROM t1_aux ORDER BY f1,f2; 361# 362--error ER_BAD_NULL_ERROR 363INSERT INTO t1_aux VALUES(1,f1_two_inserts()); 364SELECT * FROM t1_not_null ORDER BY f1,f2; 365SELECT * FROM t1_aux ORDER BY f1,f2; 366 367--echo 368--echo # FUNCTION in DELETE 369INSERT INTO t1_aux VALUES (1,1); 370COMMIT; 371--error ER_BAD_NULL_ERROR 372DELETE FROM t1_aux WHERE f1 = f1_two_inserts(); 373SELECT * FROM t1_not_null ORDER BY f1,f2; 374ROLLBACK; 375SELECT * FROM t1_not_null ORDER BY f1,f2; 376SELECT * FROM t1_aux ORDER BY f1,f2; 377 378--echo 379--echo # FUNCTION in UPDATE SET 380# FUNCTION in SET 381--error ER_BAD_NULL_ERROR 382UPDATE t1_aux SET f2 = f1_two_inserts() + 1; 383SELECT * FROM t1_not_null ORDER BY f1,f2; 384ROLLBACK; 385SELECT * FROM t1_not_null ORDER BY f1,f2; 386SELECT * FROM t1_aux ORDER BY f1,f2; 387# 388if ($fixed_bug_35877) 389{ 390--echo 391--echo # FUNCTION in UPDATE WHERE 392# Bug#35877 Update .. WHERE with function, constraint violation, crash 393UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts(); 394SELECT * FROM t1_not_null ORDER BY f1,f2; 395ROLLBACK; 396SELECT * FROM t1_not_null ORDER BY f1,f2; 397SELECT * FROM t1_aux ORDER BY f1,f2; 398} 399 400--echo 401--echo # FUNCTION in VIEW definition 402CREATE VIEW v1_func AS SELECT f1_two_inserts() FROM t1_select; 403--error ER_BAD_NULL_ERROR 404SELECT * FROM v1_func; 405SELECT * FROM t1_not_null ORDER BY f1,f2; 406ROLLBACK; 407SELECT * FROM t1_not_null ORDER BY f1,f2; 408DROP VIEW v1_func; 409 410--echo 411--echo # FUNCTION in CREATE TABLE ... AS SELECT 412--error ER_BAD_NULL_ERROR 413CREATE TABLE t1_fail AS SELECT f1_two_inserts() FROM t1_select; 414SELECT * FROM t1_not_null ORDER BY f1,f2; 415# 416--error ER_BAD_NULL_ERROR 417CREATE TABLE t1_fail AS SELECT * FROM t1_select WHERE 1 = f1_two_inserts(); 418SELECT * FROM t1_not_null ORDER BY f1,f2; 419# 420 421--echo 422--echo # FUNCTION in ORDER BY 423--error ER_BAD_NULL_ERROR 424SELECT * FROM t1_select ORDER BY f1,f1_two_inserts(); 425SELECT * FROM t1_not_null ORDER BY f1,f2; 426 427--echo 428--echo # FUNCTION in aggregate function 429--error ER_BAD_NULL_ERROR 430SELECT AVG(f1_two_inserts()) FROM t1_select; 431SELECT * FROM t1_not_null ORDER BY f1,f2; 432 433--echo 434--echo # FUNCTION in HAVING 435--error ER_BAD_NULL_ERROR 436SELECT 1 FROM t1_select HAVING AVG(f1) = f1_two_inserts() + 2; 437SELECT * FROM t1_not_null ORDER BY f1,f2; 438DROP FUNCTION f1_two_inserts; 439 440--echo 441--echo # FUNCTION modifies Updatable VIEW 442CREATE VIEW v1_not_null AS SELECT f1,f2 FROM t1_not_null WITH CHECK OPTION; 443delimiter //; 444CREATE FUNCTION f1_two_inserts_v1() RETURNS INTEGER 445BEGIN 446 INSERT INTO v1_not_null SET f1 = 10, f2 = 10; 447 INSERT INTO v1_not_null SET f1 = 10, f2 = NULL; 448 RETURN 1; 449END// 450delimiter ;// 451--error ER_BAD_NULL_ERROR 452SELECT f1_two_inserts_v1(); 453SELECT * FROM t1_not_null ORDER BY f1,f2; 454ROLLBACK; 455SELECT * FROM t1_not_null ORDER BY f1,f2; 456DROP FUNCTION f1_two_inserts_v1; 457DROP VIEW v1_not_null; 458 459--echo 460--echo # FUNCTION causes FOREIGN KEY constraint violation 461eval 462CREATE TABLE t1_parent (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1)) 463ENGINE = $engine; 464INSERT INTO t1_parent VALUES (1,1); 465eval 466CREATE TABLE t1_child (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1), 467FOREIGN KEY (f1) REFERENCES t1_parent(f1)) 468ENGINE = $engine; 469--error ER_NO_REFERENCED_ROW_2 470delimiter //; 471CREATE FUNCTION f1_two_inserts() RETURNS INTEGER 472BEGIN 473 INSERT INTO t1_child SET f1 = 1, f2 = 1; 474 INSERT INTO t1_child SET f1 = 2, f2 = 2; 475 RETURN 1; 476END// 477delimiter ;// 478--error ER_NO_REFERENCED_ROW_2 479SELECT f1_two_inserts(); 480SELECT * FROM t1_child; 481DROP TABLE t1_child; 482DROP TABLE t1_parent; 483DROP FUNCTION f1_two_inserts; 484 485# Cleanup 486DROP TABLE t1_select; 487DROP TABLE t1_aux; 488DROP TABLE t1_not_null; 489