summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/func_rollback.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/func_rollback.test
parentInitial commit. (diff)
downloadmariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz
mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/func_rollback.test')
-rw-r--r--mysql-test/main/func_rollback.test497
1 files changed, 497 insertions, 0 deletions
diff --git a/mysql-test/main/func_rollback.test b/mysql-test/main/func_rollback.test
new file mode 100644
index 00000000..0650ae09
--- /dev/null
+++ b/mysql-test/main/func_rollback.test
@@ -0,0 +1,497 @@
+# func_rollback.test
+#
+# Test variations inspired by
+# Bug#12713 Error in a stored function called from a SELECT doesn't cause
+# ROLLBACK of statement
+# Essential of the bug:
+# - A SELECT using a FUNCTION processes a table.
+# - The SELECT affects more than row.
+# - The FUNCTION modifies a table.
+# - When processing the non first matching row, the function fails.
+# But the modification caused by the function when the SELECT processed the
+# first matching row is not reverted.
+#
+# Goal of this test: Attempts to catch a situation where
+# - a statement A involving the execution of one or more functions is run
+# - the function/functions themself contain one or more statements
+# modifying a table
+# - one of the modifying statements within one of the functions fails
+# - the table remains at least partially modified
+#
+# = There is no automatic ROLLBACK of changes caused by the failing
+# statement A.
+# = Statement A is not atomic.
+#
+# Notes:
+# - The table to be modified must use a transactional storage engine.
+# For example MyISAM cannot avoid the situation above.
+# - Some comments assume that the rows of the table t1_select are processed
+# in the order of insertion. That means
+# SELECT f1,f2 FROM t1_select
+# should have the same result set and row order like
+# SELECT f1,f2 FROM t1_select ORDER BY f1;
+# - The manual says that we get in non strict sql mode a warning around INSERT:
+# Inserting NULL into a column that has been declared NOT NULL.
+# For multiple-row INSERT statements or INSERT INTO ... SELECT statements,
+# the column is set to the implicit default value for the column data type.
+#
+# Created:
+# 2008-04-09 mleich
+#
+-- source include/no_view_protocol.inc
+
+let $fixed_bug_35877 = 0;
+
+let $from_select = SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL;
+
+--source include/have_innodb.inc
+let $engine = InnoDB;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1_select;
+DROP TABLE IF EXISTS t1_aux;
+DROP TABLE IF EXISTS t1_not_null;
+DROP VIEW IF EXISTS v1_not_null;
+DROP VIEW IF EXISTS v1_func;
+DROP TABLE IF EXISTS t1_fail;
+DROP FUNCTION IF EXISTS f1_simple_insert;
+DROP FUNCTION IF EXISTS f1_two_inserts;
+DROP FUNCTION IF EXISTS f1_insert_select;
+--enable_warnings
+
+SET SESSION AUTOCOMMIT=0;
+SET SESSION sql_mode = '';
+
+CREATE TABLE t1_select (f1 BIGINT, f2 BIGINT) ENGINE = MEMORY;
+INSERT INTO t1_select(f1,f2) VALUES (1,-1),(2,NULL),(3,0),(4,1),(5,2);
+SELECT * FROM t1_select;
+
+--replace_result $engine <transactional_engine>
+eval
+CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)
+ENGINE = $engine;
+SELECT * FROM t1_not_null;
+
+--replace_result $engine <transactional_engine>
+eval
+CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)
+ENGINE = $engine;
+SELECT * FROM t1_aux;
+COMMIT;
+
+# FUNCTION with "simple" INSERT
+delimiter //;
+CREATE FUNCTION f1_simple_insert(my_f1 INTEGER) RETURNS INTEGER
+BEGIN
+ INSERT INTO t1_not_null SET f1 = 10, f2 = my_f1;
+ RETURN 1;
+END//
+delimiter ;//
+
+--echo
+--echo # One f1_simple_insert execution per row, no NOT NULL violation
+--disable_ps2_protocol
+SELECT f1_simple_insert(1);
+--enable_ps2_protocol
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null;
+#
+--disable_ps2_protocol
+SELECT f1_simple_insert(1) FROM t1_select;
+--enable_ps2_protocol
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null;
+
+--echo
+--echo # One f1_simple_insert execution per row, NOT NULL violation when the
+--echo # SELECT processes the first row.
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(NULL);
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(NULL) FROM t1_select;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+eval SELECT 1 FROM ($from_select) AS t1 WHERE f1_simple_insert(NULL) = 1;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # One f1_simple_insert execution per row, NOT NULL violation when the
+--echo # SELECT processes the non first row
+--error ER_BAD_NULL_ERROR
+eval SELECT f1_simple_insert(f2) FROM ($from_select) AS t1;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(f2) FROM t1_select;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # Two f1_simple_insert executions per row, NOT NULL violation when the
+--echo # SELECT processes the first row.
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(1),f1_simple_insert(NULL);
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(NULL),f1_simple_insert(1);
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # Two f1_simple_insert executions per row, NOT NULL violation when the
+--echo # SELECT processes the non first row
+--error ER_BAD_NULL_ERROR
+eval SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM ($from_select) AS t1;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+eval SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM ($from_select) AS t1;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM t1_select;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM t1_select;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+eval SELECT 1 FROM ($from_select) AS t1
+WHERE 1 = f1_simple_insert(f2) AND 1 = f1_simple_insert(f1);
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # Nested functions, the inner fails
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(f1_simple_insert(NULL)) FROM t1_select;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+--echo
+--echo # Nested functions, the outer fails
+--error ER_BAD_NULL_ERROR
+SELECT f1_simple_insert(f1_simple_insert(1) + NULL) FROM t1_select;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+DROP FUNCTION f1_simple_insert;
+
+# FUNCTION with INSERT ... SELECT
+delimiter //;
+let $f1_insert_select =
+CREATE FUNCTION f1_insert_select(my_f1 INTEGER) RETURNS INTEGER
+BEGIN
+ INSERT INTO t1_not_null SELECT * FROM t1_select WHERE f1 = my_f1;
+ RETURN 1;
+END//
+delimiter ;//
+eval $f1_insert_select;
+#
+--echo
+--echo # f1_insert_select(2), tries to INSERT SELECT one row containing NULL
+--echo # The fact that
+--echo # - SELECT f1_insert_select(2); gives any result set and
+--echo # - t1_not_null gets a row inserted
+--echo # is covered by the manual.
+# Non strict sqlmode + INSERT SELECT --> NULL adjusted to default
+--disable_ps2_protocol
+SELECT f1_insert_select(2);
+--enable_ps2_protocol
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+DROP FUNCTION f1_insert_select;
+#
+SET SESSION sql_mode = 'traditional';
+eval $f1_insert_select;
+--error ER_BAD_NULL_ERROR
+SELECT f1_insert_select(2);
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+DROP FUNCTION f1_insert_select;
+SET SESSION sql_mode = '';
+
+# FUNCTION with two simple INSERTs
+--echo
+--echo # Function tries to
+--echo # 1. INSERT statement: Insert one row with NULL -> NOT NULL violation
+--echo # 2. INSERT statement: Insert one row without NULL
+# I guess the execution of the function becomes aborted just when the
+# error happens.
+delimiter //;
+CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
+BEGIN
+ INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
+ INSERT INTO t1_not_null SET f1 = 10, f2 = 10;
+ RETURN 1;
+END//
+delimiter ;//
+--error ER_BAD_NULL_ERROR
+SELECT f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+DROP FUNCTION f1_two_inserts;
+#
+--echo
+--echo # Function tries to
+--echo # 1. INSERT statement: Insert one row without NULL
+--echo # 2. INSERT statement: Insert one row with NULL -> NOT NULL violation
+delimiter //;
+CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
+BEGIN
+ INSERT INTO t1_not_null SET f1 = 10, f2 = 10;
+ INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
+ RETURN 1;
+END//
+delimiter ;//
+--error ER_BAD_NULL_ERROR
+SELECT f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # Function tries to
+--echo # INSERT statement: Insert two rows
+--echo # first row without NULL
+--echo # second row with NULL -> NOT NULL violation
+--echo # -> NOT NULL violation
+delimiter //;
+let $f1_insert_with_two_rows =
+CREATE FUNCTION f1_insert_with_two_rows() RETURNS INTEGER
+BEGIN
+ INSERT INTO t1_not_null(f1,f2) VALUES (10,10),(10,NULL);
+ RETURN 1;
+END//
+delimiter ;//
+eval $f1_insert_with_two_rows;
+--echo # The fact that
+--echo # - SELECT f1_insert_with_two_rows(); gives any result set and
+--echo # - t1_not_null gets a row inserted
+--echo # is covered by the manual.
+# Non strict sqlmode + multiple-row INSERT --> NULL adjusted to default
+--disable_ps2_protocol
+SELECT f1_insert_with_two_rows();
+--enable_ps2_protocol
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+DROP FUNCTION f1_insert_with_two_rows;
+#
+SET SESSION sql_mode = 'traditional';
+eval $f1_insert_with_two_rows;
+--error ER_BAD_NULL_ERROR
+SELECT f1_insert_with_two_rows();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+SET SESSION sql_mode = '';
+
+--echo
+--echo # FUNCTION in Correlated Subquery
+--error ER_BAD_NULL_ERROR
+SELECT 1 FROM t1_select t1
+WHERE 1 = (SELECT f1_insert_with_two_rows() FROM t1_select t2
+ WHERE t2.f1 = t1.f1);
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # FUNCTION in JOIN
+--error ER_BAD_NULL_ERROR
+SELECT 1 FROM t1_select t1, t1_select t2
+WHERE t1.f1 = t2.f1 AND t2.f1 = f1_insert_with_two_rows();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+SELECT STRAIGHT_JOIN * FROM t1_select t2 RIGHT JOIN t1_select t1
+ON t1.f1 = t1.f1 WHERE 1 = f1_insert_with_two_rows();
+
+DROP FUNCTION f1_insert_with_two_rows;
+
+--echo
+--echo # FUNCTION in UNION
+--error ER_BAD_NULL_ERROR
+SELECT 1
+UNION ALL
+SELECT f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # FUNCTION in INSERT
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1_aux SET f1 = 1, f2 = f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1_aux SELECT 1, f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+SELECT * FROM t1_aux ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1_aux VALUES(1,f1_two_inserts());
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+SELECT * FROM t1_aux ORDER BY f1,f2;
+
+--echo
+--echo # FUNCTION in DELETE
+INSERT INTO t1_aux VALUES (1,1);
+COMMIT;
+--error ER_BAD_NULL_ERROR
+DELETE FROM t1_aux WHERE f1 = f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+SELECT * FROM t1_aux ORDER BY f1,f2;
+
+--echo
+--echo # FUNCTION in UPDATE SET
+# FUNCTION in SET
+--error ER_BAD_NULL_ERROR
+UPDATE t1_aux SET f2 = f1_two_inserts() + 1;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+SELECT * FROM t1_aux ORDER BY f1,f2;
+#
+if ($fixed_bug_35877)
+{
+--echo
+--echo # FUNCTION in UPDATE WHERE
+# Bug#35877 Update .. WHERE with function, constraint violation, crash
+UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+SELECT * FROM t1_aux ORDER BY f1,f2;
+}
+
+--echo
+--echo # FUNCTION in VIEW definition
+CREATE VIEW v1_func AS SELECT f1_two_inserts() FROM t1_select;
+--error ER_BAD_NULL_ERROR
+SELECT * FROM v1_func;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+DROP VIEW v1_func;
+
+--echo
+--echo # FUNCTION in CREATE TABLE ... AS SELECT
+--error ER_BAD_NULL_ERROR
+CREATE TABLE t1_fail AS SELECT f1_two_inserts() FROM t1_select;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+--error ER_BAD_NULL_ERROR
+CREATE TABLE t1_fail AS SELECT * FROM t1_select WHERE 1 = f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+#
+
+--echo
+--echo # FUNCTION in ORDER BY
+--error ER_BAD_NULL_ERROR
+SELECT * FROM t1_select ORDER BY f1,f1_two_inserts();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # FUNCTION in aggregate function
+--error ER_BAD_NULL_ERROR
+SELECT AVG(f1_two_inserts()) FROM t1_select;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+
+--echo
+--echo # FUNCTION in HAVING
+--error ER_BAD_NULL_ERROR
+SELECT 1 FROM t1_select HAVING AVG(f1) = f1_two_inserts() + 2;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+DROP FUNCTION f1_two_inserts;
+
+--echo
+--echo # FUNCTION modifies Updatable VIEW
+CREATE VIEW v1_not_null AS SELECT f1,f2 FROM t1_not_null WITH CHECK OPTION;
+delimiter //;
+CREATE FUNCTION f1_two_inserts_v1() RETURNS INTEGER
+BEGIN
+ INSERT INTO v1_not_null SET f1 = 10, f2 = 10;
+ INSERT INTO v1_not_null SET f1 = 10, f2 = NULL;
+ RETURN 1;
+END//
+delimiter ;//
+--error ER_BAD_NULL_ERROR
+SELECT f1_two_inserts_v1();
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+DROP FUNCTION f1_two_inserts_v1;
+DROP VIEW v1_not_null;
+
+--echo
+--echo # FUNCTION causes FOREIGN KEY constraint violation
+eval
+CREATE TABLE t1_parent (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1))
+ENGINE = $engine;
+INSERT INTO t1_parent VALUES (1,1);
+eval
+CREATE TABLE t1_child (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1),
+FOREIGN KEY (f1) REFERENCES t1_parent(f1))
+ENGINE = $engine;
+--error ER_NO_REFERENCED_ROW_2
+delimiter //;
+CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
+BEGIN
+ INSERT INTO t1_child SET f1 = 1, f2 = 1;
+ INSERT INTO t1_child SET f1 = 2, f2 = 2;
+ RETURN 1;
+END//
+delimiter ;//
+--error ER_NO_REFERENCED_ROW_2
+SELECT f1_two_inserts();
+SELECT * FROM t1_child;
+DROP TABLE t1_child;
+DROP TABLE t1_parent;
+DROP FUNCTION f1_two_inserts;
+
+# Cleanup
+DROP TABLE t1_select;
+DROP TABLE t1_aux;
+DROP TABLE t1_not_null;