List:Commits« Previous MessageNext Message »
From:mleich Date:April 9 2008 1:56pm
Subject:bk commit into 5.1 tree (mleich:1.2588) BUG#12713
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of mleich.  When mleich does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-04-09 15:56:25+02:00, mleich@stripped +2 -0
  Additional tests inspired by Bug
     #12713 Error in a stored function called from a
            SELECT doesn't cause ROLLBACK of statem

  mysql-test/r/func_rollback.result@stripped, 2008-04-09 15:56:00+02:00, mleich@stripped +461 -0
    Expected results

  mysql-test/r/func_rollback.result@stripped, 2008-04-09 15:56:00+02:00, mleich@stripped +0 -0

  mysql-test/t/func_rollback.test@stripped, 2008-04-09 15:56:00+02:00, mleich@stripped +488 -0
    New test

  mysql-test/t/func_rollback.test@stripped, 2008-04-09 15:56:00+02:00, mleich@stripped +0 -0

diff -Nrup a/mysql-test/r/func_rollback.result b/mysql-test/r/func_rollback.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/func_rollback.result	2008-04-09 15:56:00 +02:00
@@ -0,0 +1,461 @@
+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;
+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;
+f1	f2
+1	-1
+2	NULL
+3	0
+4	1
+5	2
+CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)
+ENGINE = <transactional_engine>;
+SELECT * FROM t1_not_null;
+f1	f2
+CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)
+ENGINE = <transactional_engine>;
+SELECT * FROM t1_aux;
+f1	f2
+COMMIT;
+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//
+
+# One f1_simple_insert execution per row, no NOT NULL violation
+SELECT f1_simple_insert(1);
+f1_simple_insert(1)
+1
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+10	1
+ROLLBACK;
+SELECT * FROM t1_not_null;
+f1	f2
+SELECT f1_simple_insert(1) FROM t1_select;
+f1_simple_insert(1)
+1
+1
+1
+1
+1
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+10	1
+10	1
+10	1
+10	1
+10	1
+ROLLBACK;
+SELECT * FROM t1_not_null;
+f1	f2
+
+# One f1_simple_insert execution per row, NOT NULL violation when the
+# SELECT processes the first row.
+SELECT f1_simple_insert(NULL);
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT f1_simple_insert(NULL) FROM t1_select;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT 1 FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1 WHERE f1_simple_insert(NULL) = 1;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# One f1_simple_insert execution per row, NOT NULL violation when the
+# SELECT processes the non first row
+SELECT f1_simple_insert(f2) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT f1_simple_insert(f2) FROM t1_select;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# Two f1_simple_insert executions per row, NOT NULL violation when the
+# SELECT processes the first row.
+SELECT f1_simple_insert(1),f1_simple_insert(NULL);
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT f1_simple_insert(NULL),f1_simple_insert(1);
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# Two f1_simple_insert executions per row, NOT NULL violation when the
+# SELECT processes the non first row
+SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT f1_simple_insert(f1),f1_simple_insert(f2) FROM t1_select;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT f1_simple_insert(f2),f1_simple_insert(f1) FROM t1_select;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT 1 FROM (SELECT 1 AS f1,1 AS f2 UNION ALL SELECT 1,NULL) AS t1
+WHERE 1 = f1_simple_insert(f2) AND 1 = f1_simple_insert(f1);
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# Nested functions, the inner fails
+SELECT f1_simple_insert(f1_simple_insert(NULL)) FROM t1_select;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# Nested functions, the outer fails
+SELECT f1_simple_insert(f1_simple_insert(1) + NULL) FROM t1_select;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+DROP FUNCTION f1_simple_insert;
+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;
+
+# f1_insert_select(2), tries to INSERT SELECT one row containing NULL
+# The fact that
+# - SELECT f1_insert_select(2);     gives any result set    and
+# - t1_not_null gets a row inserted
+# is covered by the manual.
+SELECT f1_insert_select(2);
+f1_insert_select(2)
+1
+Warnings:
+Warning	1048	Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+2	0
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+DROP FUNCTION f1_insert_select;
+SET SESSION sql_mode = 'traditional';
+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;
+SELECT f1_insert_select(2);
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+DROP FUNCTION f1_insert_select;
+SET SESSION sql_mode = '';
+
+# Function tries to
+#    1. INSERT statement: Insert one row with NULL -> NOT NULL violation
+#    2. INSERT statement: Insert one row without NULL
+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//
+SELECT f1_two_inserts();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+DROP FUNCTION f1_two_inserts;
+
+# Function tries to
+#    1. INSERT statement: Insert one row without NULL
+#    2. INSERT statement: Insert one row with NULL -> NOT NULL violation
+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//
+SELECT f1_two_inserts();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# Function tries to
+#    INSERT statement: Insert two rows
+#                      first row without NULL
+#                      second row with NULL -> NOT NULL violation
+#       -> NOT NULL violation
+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;
+# The fact that
+# - SELECT f1_insert_with_two_rows();     gives any result set    and
+# - t1_not_null gets a row inserted
+# is covered by the manual.
+SELECT f1_insert_with_two_rows();
+f1_insert_with_two_rows()
+1
+Warnings:
+Warning	1048	Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+10	0
+10	10
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+DROP FUNCTION f1_insert_with_two_rows;
+SET SESSION sql_mode = 'traditional';
+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;
+SELECT f1_insert_with_two_rows();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SET SESSION sql_mode = '';
+
+# FUNCTION in Correlated Subquery
+SELECT 1 FROM t1_select t1
+WHERE 1 = (SELECT f1_insert_with_two_rows() FROM t1_select t2
+WHERE t2.f1 = t1.f1);
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# FUNCTION in JOIN
+SELECT 1 FROM t1_select t1, t1_select t2
+WHERE t1.f1 = t2.f1 AND t2.f1 = f1_insert_with_two_rows();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT STRAIGHT_JOIN * FROM t1_select t2 RIGHT JOIN t1_select t1
+ON t1.f1 = t1.f1 WHERE 1 = f1_insert_with_two_rows();
+ERROR 23000: Column 'f2' cannot be null
+DROP FUNCTION f1_insert_with_two_rows;
+
+# FUNCTION in UNION
+SELECT 1
+UNION ALL
+SELECT f1_two_inserts();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# FUNCTION in INSERT
+INSERT INTO t1_aux SET f1 = 1, f2 = f1_two_inserts();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+INSERT INTO t1_aux SELECT 1, f1_two_inserts();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT * FROM t1_aux ORDER BY f1,f2;
+f1	f2
+INSERT INTO t1_aux VALUES(1,f1_two_inserts());
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT * FROM t1_aux ORDER BY f1,f2;
+f1	f2
+
+# FUNCTION in DELETE
+INSERT INTO t1_aux VALUES (1,1);
+COMMIT;
+DELETE FROM t1_aux WHERE f1 = f1_two_inserts();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT * FROM t1_aux ORDER BY f1,f2;
+f1	f2
+1	1
+
+# FUNCTION in UPDATE SET
+UPDATE t1_aux SET f2 = f1_two_inserts() + 1;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+SELECT * FROM t1_aux ORDER BY f1,f2;
+f1	f2
+1	1
+
+# FUNCTION in VIEW definition
+CREATE VIEW v1_func AS SELECT f1_two_inserts() FROM t1_select;
+SELECT * FROM v1_func;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+DROP VIEW v1_func;
+
+# FUNCTION in CREATE TABLE ... AS SELECT
+CREATE TABLE t1_fail AS SELECT f1_two_inserts() FROM t1_select;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+CREATE TABLE t1_fail AS SELECT * FROM t1_select WHERE 1 = f1_two_inserts();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# FUNCTION in ORDER BY
+SELECT * FROM t1_select ORDER BY f1,f1_two_inserts();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# FUNCTION in aggregate function
+SELECT AVG(f1_two_inserts()) FROM t1_select;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+
+# FUNCTION in HAVING
+SELECT 1 FROM t1_select HAVING AVG(f1) = f1_two_inserts() + 2;
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+DROP FUNCTION f1_two_inserts;
+
+# FUNCTION modifies Updatable VIEW
+CREATE VIEW v1_not_null AS SELECT f1,f2 FROM t1_not_null WITH CHECK OPTION;
+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//
+SELECT f1_two_inserts_v1();
+ERROR 23000: Column 'f2' cannot be null
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+ROLLBACK;
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+f1	f2
+DROP FUNCTION f1_two_inserts_v1;
+DROP VIEW v1_not_null;
+
+# FUNCTION causes FOREIGN KEY constraint violation
+CREATE TABLE t1_parent (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1))
+ENGINE = InnoDB;
+INSERT INTO t1_parent VALUES (1,1);
+CREATE TABLE t1_child (f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1),
+FOREIGN KEY (f1) REFERENCES t1_parent(f1))
+ENGINE = InnoDB;
+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//
+SELECT f1_two_inserts();
+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`))
+SELECT * FROM t1_child;
+f1	f2
+DROP TABLE t1_child;
+DROP TABLE t1_parent;
+DROP FUNCTION f1_two_inserts;
+DROP TABLE t1_select;
+DROP TABLE t1_aux;
+DROP TABLE t1_not_null;
diff -Nrup a/mysql-test/t/func_rollback.test b/mysql-test/t/func_rollback.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/func_rollback.test	2008-04-09 15:56:00 +02:00
@@ -0,0 +1,488 @@
+# 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
+#
+
+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
+SELECT f1_simple_insert(1);
+SELECT * FROM t1_not_null ORDER BY f1,f2;
+ROLLBACK;
+SELECT * FROM t1_not_null;
+#
+SELECT f1_simple_insert(1) FROM t1_select;
+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
+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 = '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
+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;
+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;
Thread
bk commit into 5.1 tree (mleich:1.2588) BUG#12713mleich9 Apr