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.
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.
How to repeat:
mysql> set sql_mode = "";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql>
mysql>
mysql> 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;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `t1` (
-> `i` int(11) DEFAULT NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO s1 (p_id, s_id) VALUES (1, 1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO s1 (p_id, s_id) VALUES (1, 1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM s1;
+----+------+------+
| id | s_id | p_id |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
+----+------+------+
2 rows in set (0.00 sec)
mysql>
mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> DELIMITER ;;
mysql> CREATE TRIGGER trg_s1 before INSERT ON `s1` FOR EACH ROW
-> IF 1 THEN
-> SET @sum = @sum + 1;
-> END IF ;;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> 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;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> SHOW WARNINGS;
Empty set (0.00 sec)
mysql> 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;
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 2
mysql> SHOW WARNINGS;
+---------+------+------------------------------+
| Level | Code | Message |
+---------+------+------------------------------+
| Warning | 1048 | Column 'p_id' cannot be null |
| Warning | 1048 | Column 'p_id' cannot be null |
+---------+------+------------------------------+
2 rows in set (0.00 sec)
#
# ==== No unexpected warning ====
#
mysql> 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;
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 2
mysql> SHOW WARNINGS;
+---------+------+------------------------------+
| Level | Code | Message |
+---------+------+------------------------------+
| Warning | 1048 | Column 'p_id' cannot be null |
| Warning | 1048 | Column 'p_id' cannot be null |
+---------+------+------------------------------+
2 rows in set (0.00 sec)
mysql> select * from t1 t JOIN s1 s ON s.s_id =t.i WHERE t.i=1;
+------+----+------+------+
| i | id | s_id | p_id |
+------+----+------+------+
| 1 | 1 | 1 | 3 |
| 1 | 2 | 1 | 3 |
+------+----+------+------+
2 rows in set (0.00 sec)
mysql> SHOW WARNINGS;
Empty set (0.00 sec)
mysql> 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;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 2
#
# ==== No unexpected warning ====
#
mysql> show warnings;
+---------+------+------------------------------+
| Level | Code | Message |
+---------+------+------------------------------+
| Warning | 1048 | Column 'p_id' cannot be null |
| Warning | 1048 | Column 'p_id' cannot be null |
+---------+------+------------------------------+
2 rows in set (0.00 sec)
Suggested fix:
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 */