From 0c8cddb86515cf2a9d3d2ae44a3d8321d0cce81f Mon Sep 17 00:00:00 2001 From: "yishun.ys" Date: Tue, 9 May 2023 21:12:39 +0800 Subject: [PATCH] [Bugfix] JOIN a table with trigger report unexpected WARNINGS Description =========== If the sql_mode does not include STRICT_TRANS_TABLES, inserting null into a field with NOT NULL constraint will not generate an error but a warning instead. In this case, if an update join operation is performed on the table with a trigger, subsequent join operations will continue to generate warnings even if the subsequent operations are normal. Analysis ======== Due to the insertion of null, set_field_to_null_with_conversions() is called in Item::save_in_field_inner(), which modifies m_count_cuted_fields_saved to CHECK_FIELD_WARN and returns a warning. However, after the statement has been successfully executed, m_count_cuted_fields_saved is not set to its default value. This causes subsequent join operations to continue to return a warning. Fix === After the statement is executed, modify the value of m_count_cuted_fields_saved to its default value. --- .../r/bugfix_unexpected_warnings.result | 50 ++++++++++++ mysql-test/t/bugfix_unexpected_warnings.test | 80 +++++++++++++++++++ sql/field.cc | 5 +- 3 files changed, 134 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/bugfix_unexpected_warnings.result create mode 100644 mysql-test/t/bugfix_unexpected_warnings.test diff --git a/mysql-test/r/bugfix_unexpected_warnings.result b/mysql-test/r/bugfix_unexpected_warnings.result new file mode 100644 index 00000000000..963f8c789b2 --- /dev/null +++ b/mysql-test/r/bugfix_unexpected_warnings.result @@ -0,0 +1,50 @@ +# +# ==== 0. SET sql_mode as empty ==== +# +SET @save_sql_mode = @@sql_mode; +SET sql_mode = ""; +# +# ==== 1. Create tables and TRIGGER ==== +# +CREATE TABLE `s1` ( +`id` bigint(20) NOT NULL AUTO_INCREMENT, +`s_id` int(11) NOT NULL, +`p_id` bigint(20) NOT NULL, +PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=1; +CREATE TABLE `t1` ( +`i` int(11) DEFAULT NULL +) ENGINE=InnoDB; +INSERT INTO s1 (p_id, s_id) VALUES (1, 1); +INSERT INTO s1 (p_id, s_id) VALUES (1, 1); +SELECT * FROM s1; +id s_id p_id +1 1 1 +2 1 1 +INSERT INTO t1 VALUES (1); +CREATE TRIGGER trg_s1 before INSERT ON `s1` FOR EACH ROW +IF 1 THEN +SET @sum = @sum + 1; +END IF | +# +# ==== 2. Do update join operations +# +# +# ==== No unexpected warning ==== +# +UPDATE t1 t JOIN s1 s ON s.s_id =t.i SET p_id = IF(1=0, null, p_id) WHERE t.i=1; +# +# ==== Expected warning ==== +# +UPDATE t1 t JOIN s1 s ON s.s_id =t.i SET p_id = IF(1=1, null, p_id) WHERE t.i=1; +Warnings: +Warning 1048 Column 'p_id' cannot be null +Warning 1048 Column 'p_id' cannot be null +# +# ==== No unexpected warning ==== +# +UPDATE t1 t JOIN s1 s ON s.s_id =t.i SET p_id = IF(1=1, 3, p_id) WHERE t.i=1; +DROP TRIGGER trg_s1; +DROP TABLE t1; +DROP TABLE s1; +SET @@sql_mode= @save_sql_mode; diff --git a/mysql-test/t/bugfix_unexpected_warnings.test b/mysql-test/t/bugfix_unexpected_warnings.test new file mode 100644 index 00000000000..539c2813478 --- /dev/null +++ b/mysql-test/t/bugfix_unexpected_warnings.test @@ -0,0 +1,80 @@ +# ==== Purpose ==== +# Test to verify that there will be no unexpected warnings. +# +# ==== Description ==== +# If the sql_mode does not include STRICT_TRANS_TABLES, inserting null +# into a field with NOT NULL constraint will not generate an error but +# a warning instead. In this case, if an update join operation is performed +# on the table with a trigger, subsequent join operations will continue +# to generate warnings even if the subsequent operations are normal. +# +# ==== Implementation ==== +# 0. SET sql_mode as empty. +# 1. Create a table with NOT NULL field and create a TRIGGER. +# 2. Do update join operations. + +--echo # +--echo # ==== 0. SET sql_mode as empty ==== +--echo # + +--disable_warnings +SET @save_sql_mode = @@sql_mode; +SET sql_mode = ""; +--enable_warnings + +--echo # +--echo # ==== 1. Create tables and TRIGGER ==== +--echo # + +CREATE TABLE `s1` ( + `id` bigint(20) NOT NULL AUTO_INCREMENT, + `s_id` int(11) NOT NULL, + `p_id` bigint(20) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=1; + +CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=InnoDB; + +INSERT INTO s1 (p_id, s_id) VALUES (1, 1); +INSERT INTO s1 (p_id, s_id) VALUES (1, 1); +SELECT * FROM s1; + +INSERT INTO t1 VALUES (1); + +DELIMITER |; +CREATE TRIGGER trg_s1 before INSERT ON `s1` FOR EACH ROW +IF 1 THEN + SET @sum = @sum + 1; +END IF | +DELIMITER ;| + +--echo # +--echo # ==== 2. Do update join operations +--echo # + +--echo # +--echo # ==== No unexpected warning ==== +--echo # +UPDATE t1 t JOIN s1 s ON s.s_id =t.i SET p_id = IF(1=0, null, p_id) WHERE t.i=1; + +--echo # +--echo # ==== Expected warning ==== +--echo # +UPDATE t1 t JOIN s1 s ON s.s_id =t.i SET p_id = IF(1=1, null, p_id) WHERE t.i=1; + +--echo # +--echo # ==== No unexpected warning ==== +--echo # +UPDATE t1 t JOIN s1 s ON s.s_id =t.i SET p_id = IF(1=1, 3, p_id) WHERE t.i=1; + + + +DROP TRIGGER trg_s1; +DROP TABLE t1; +DROP TABLE s1; + +--disable_warnings +SET @@sql_mode= @save_sql_mode; +--enable_warnings diff --git a/sql/field.cc b/sql/field.cc index e508cc8b4ea..b2e1ee0209c 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -1624,7 +1624,10 @@ type_conversion_status Field::check_constraints(int mysql_errno) */ assert (type() != MYSQL_TYPE_TIMESTAMP); - switch (m_count_cuted_fields_saved) { + enum_check_fields tmp_m_count_cuted_fields_saved = m_count_cuted_fields_saved; + m_count_cuted_fields_saved = CHECK_FIELD_IGNORE; + + switch (tmp_m_count_cuted_fields_saved) { case CHECK_FIELD_WARN: set_warning(Sql_condition::SL_WARNING, mysql_errno, 1); /* fall through */ -- 2.19.1.6.gb485710b