release/src/router/mysql/mysql-test/t/func_rollback.test

   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
  42 let $fixed_bug_35877 = 0;
  43
  44 let $from_select = SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL;
  45
  46 --source include/have_innodb.inc
  47 let $engine = InnoDB;
  48
  49 --disable_warnings
  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;
  59 --enable_warnings
  60
  61 SET SESSION AUTOCOMMIT=0;
  62 SET SESSION sql_mode = '';
  63
  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;
  67
  68 --replace_result $engine <transactional_engine>
  69 eval
  70 CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)
  71 ENGINE = $engine;
  72 SELECT * FROM t1_not_null;
  73
  74 --replace_result $engine <transactional_engine>
  75 eval
  76 CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)
  77 ENGINE = $engine;
  78 SELECT * FROM t1_aux;
  79 COMMIT;
  80
  81 # FUNCTION with "simple" INSERT
  82 delimiter //;
  83 CREATE FUNCTION f1_simple_insert(my_f1 INTEGER) RETURNS INTEGER
  84 BEGIN
  85    INSERT INTO t1_not_null SET f1 = 10, f2 = my_f1;
  86    RETURN 1;
  87 END//
  88 delimiter ;//
  89
  90 --echo
  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;
  94 ROLLBACK;
  95 SELECT * FROM t1_not_null;
  96 #
  97 SELECT f1_simple_insert(1) FROM t1_select;
  98 SELECT * FROM t1_not_null ORDER BY f1,f2;
  99 ROLLBACK;
 100 SELECT * 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
 106 SELECT f1_simple_insert(NULL);
 107 SELECT * FROM t1_not_null ORDER BY f1,f2;
 108 ROLLBACK;
 109 SELECT * FROM t1_not_null ORDER BY f1,f2;
 110 #
 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;
 114 ROLLBACK;
 115 SELECT * FROM t1_not_null ORDER BY f1,f2;
 116 #
 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;
 120 ROLLBACK;
 121 SELECT * 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
 127 eval SELECT f1_simple_insert(f2) FROM ($from_select) AS t1;
 128 SELECT * FROM t1_not_null ORDER BY f1,f2;
 129 ROLLBACK;
 130 SELECT * FROM t1_not_null ORDER BY f1,f2;
 131 #
 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;
 135 ROLLBACK;
 136 SELECT * 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
 142 SELECT f1_simple_insert(1),f1_simple_insert(NULL);
 143 SELECT * FROM t1_not_null ORDER BY f1,f2;
 144 ROLLBACK;
 145 SELECT * FROM t1_not_null ORDER BY f1,f2;
 146 #
 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;
 150 ROLLBACK;
 151 SELECT * 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
 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;
 159 ROLLBACK;
 160 SELECT * FROM t1_not_null ORDER BY f1,f2;
 161 #
 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;
 165 ROLLBACK;
 166 SELECT * FROM t1_not_null ORDER BY f1,f2;
 167 #
 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;
 171 ROLLBACK;
 172 SELECT * FROM t1_not_null ORDER BY f1,f2;
 173 #
 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;
 177 ROLLBACK;
 178 SELECT * FROM t1_not_null ORDER BY f1,f2;
 179 #
 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;
 184 ROLLBACK;
 185 SELECT * FROM t1_not_null ORDER BY f1,f2;
 186
 187 --echo
 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;
 192 ROLLBACK;
 193 SELECT * FROM t1_not_null ORDER BY f1,f2;
 194 --echo
 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;
 199 ROLLBACK;
 200 SELECT * FROM t1_not_null ORDER BY f1,f2;
 201 DROP FUNCTION f1_simple_insert;
 202
 203 # FUNCTION with INSERT ... SELECT
 204 delimiter //;
 205 let $f1_insert_select =
 206 CREATE FUNCTION f1_insert_select(my_f1 INTEGER) RETURNS INTEGER
 207 BEGIN
 208    INSERT INTO t1_not_null SELECT * FROM t1_select WHERE f1 = my_f1;
 209    RETURN 1;
 210 END//
 211 delimiter ;//
 212 eval $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
 221 SELECT f1_insert_select(2);
 222 SELECT * FROM t1_not_null ORDER BY f1,f2;
 223 ROLLBACK;
 224 SELECT * FROM t1_not_null ORDER BY f1,f2;
 225 DROP FUNCTION f1_insert_select;
 226 #
 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;
 232 ROLLBACK;
 233 SELECT * FROM t1_not_null ORDER BY f1,f2;
 234 DROP FUNCTION f1_insert_select;
 235 SET 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.
 244 delimiter //;
 245 CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
 246 BEGIN
 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;
 250 END//
 251 delimiter ;//
 252 --error ER_BAD_NULL_ERROR
 253 SELECT f1_two_inserts();
 254 SELECT * FROM t1_not_null ORDER BY f1,f2;
 255 ROLLBACK;
 256 SELECT * FROM t1_not_null ORDER BY f1,f2;
 257 DROP 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
 263 delimiter //;
 264 CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
 265 BEGIN
 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;
 269 END//
 270 delimiter ;//
 271 --error ER_BAD_NULL_ERROR
 272 SELECT f1_two_inserts();
 273 SELECT * FROM t1_not_null ORDER BY f1,f2;
 274 ROLLBACK;
 275 SELECT * 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
 283 delimiter //;
 284 let $f1_insert_with_two_rows =
 285 CREATE FUNCTION f1_insert_with_two_rows() RETURNS INTEGER
 286 BEGIN
 287    INSERT INTO t1_not_null(f1,f2) VALUES (10,10),(10,NULL);
 288    RETURN 1;
 289 END//
 290 delimiter ;//
 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;
 299 ROLLBACK;
 300 SELECT * FROM t1_not_null ORDER BY f1,f2;
 301 DROP FUNCTION f1_insert_with_two_rows;
 302 #
 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;
 308 ROLLBACK;
 309 SELECT * FROM t1_not_null ORDER BY f1,f2;
 310 SET SESSION sql_mode = '';
 311
 312 --echo
 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;
 319 ROLLBACK;
 320 SELECT * FROM t1_not_null ORDER BY f1,f2;
 321
 322 --echo
 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;
 328 ROLLBACK;
 329 SELECT * FROM t1_not_null ORDER BY f1,f2;
 330 #
 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();
 334
 335 DROP FUNCTION f1_insert_with_two_rows;
 336
 337 --echo
 338 --echo # FUNCTION in UNION
 339 --error ER_BAD_NULL_ERROR
 340 SELECT 1
 341 UNION ALL
 342 SELECT f1_two_inserts();
 343 SELECT * FROM t1_not_null ORDER BY f1,f2;
 344 ROLLBACK;
 345 SELECT * FROM t1_not_null ORDER BY f1,f2;
 346
 347 --echo
 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;
 352 ROLLBACK;
 353 SELECT * FROM t1_not_null ORDER BY f1,f2;
 354 #
 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;
 358 ROLLBACK;
 359 SELECT * FROM t1_not_null ORDER BY f1,f2;
 360 SELECT * FROM t1_aux ORDER BY f1,f2;
 361 #
 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;
 366
 367 --echo
 368 --echo # FUNCTION in DELETE
 369 INSERT INTO t1_aux VALUES (1,1);
 370 COMMIT;
 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;
 374 ROLLBACK;
 375 SELECT * FROM t1_not_null ORDER BY f1,f2;
 376 SELECT * 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
 382 UPDATE t1_aux SET f2 = f1_two_inserts() + 1;
 383 SELECT * FROM t1_not_null ORDER BY f1,f2;
 384 ROLLBACK;
 385 SELECT * FROM t1_not_null ORDER BY f1,f2;
 386 SELECT * FROM t1_aux ORDER BY f1,f2;
 387 #
 388 if ($fixed_bug_35877)
 389 {
 390 --echo
 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;
 395 ROLLBACK;
 396 SELECT * FROM t1_not_null ORDER BY f1,f2;
 397 SELECT * FROM t1_aux ORDER BY f1,f2;
 398 }
 399
 400 --echo
 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;
 406 ROLLBACK;
 407 SELECT * FROM t1_not_null ORDER BY f1,f2;
 408 DROP VIEW v1_func;
 409
 410 --echo
 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;
 415 #
 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;
 419 #
 420
 421 --echo
 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;
 426
 427 --echo
 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;
 432
 433 --echo
 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;
 439
 440 --echo
 441 --echo # FUNCTION modifies Updatable VIEW
 442 CREATE VIEW v1_not_null AS SELECT f1,f2 FROM t1_not_null WITH CHECK OPTION;
 443 delimiter //;
 444 CREATE FUNCTION f1_two_inserts_v1() RETURNS INTEGER
 445 BEGIN
 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;
 449 END//
 450 delimiter ;//
 451 --error ER_BAD_NULL_ERROR
 452 SELECT f1_two_inserts_v1();
 453 SELECT * FROM t1_not_null ORDER BY f1,f2;
 454 ROLLBACK;
 455 SELECT * FROM t1_not_null ORDER BY f1,f2;
 456 DROP FUNCTION f1_two_inserts_v1;
 457 DROP VIEW v1_not_null;
 458
 459 --echo
 460 --echo # FUNCTION causes FOREIGN KEY constraint violation
 461 eval
 462 CREATE TABLE t1_parent (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1))
 463 ENGINE = $engine;
 464 INSERT INTO t1_parent VALUES (1,1);
 465 eval
 466 CREATE TABLE t1_child (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1),
 467 FOREIGN KEY (f1) REFERENCES t1_parent(f1))
 468 ENGINE = $engine;
 469 --error ER_NO_REFERENCED_ROW_2
 470 delimiter //;
 471 CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
 472 BEGIN
 473    INSERT INTO t1_child SET f1 = 1, f2 = 1;
 474    INSERT INTO t1_child SET f1 = 2, f2 = 2;
 475    RETURN 1;
 476 END//
 477 delimiter ;//
 478 --error ER_NO_REFERENCED_ROW_2
 479 SELECT f1_two_inserts();
 480 SELECT * FROM t1_child;
 481 DROP TABLE t1_child;
 482 DROP TABLE t1_parent;
 483 DROP FUNCTION f1_two_inserts;
 484
 485 # Cleanup
 486 DROP TABLE t1_select;
 487 DROP TABLE t1_aux;
 488 DROP TABLE t1_not_null;