Bug #110962 JOIN a table with trigger report unexpected WARNINGS
Submitted: 9 May 2023 13:07 Modified: 10 May 2023 11:15
Reporter: Shun Yi (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.42, 8.0.33 OS:Any
Assigned to: CPU Architecture:Any
Tags: contributions

[9 May 2023 13:07] Shun Yi 
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 */
[9 May 2023 13:16] Shun Yi 
patch to fix this problem:

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: Bugfix-JOIN-a-table-with-trigger-report-unexpected-W.patch (application/octet-stream, text), 5.73 KiB.
[9 May 2023 13:26] MySQL Verification Team 
Hello Shun Yi,

Thank you for the report and contribution.

regards,
Umesh
[10 May 2023 11:15] Shun Yi 
m_count_cuted_fields_saved is not set to its default value, this causes subsequent update join operations to continue to return a warning or ERROR.

If there is an ERROR once, all subsequent update joins on this table will continue to be ERROR, even if the values for update are not null.

This will cause all update join operations on this table to fail.

============== How to repeat ==============

mysql> set sql_mode = STRICT_TRANS_TABLES;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set sql_mode = STRICT_TRANS_TABLES;
Query OK, 0 rows affected, 1 warning (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;
ERROR 1048 (23000): Column 'p_id' cannot be null
mysql> UPDATE t1 t JOIN s1 s ON s.s_id =t.i SET p_id = IF(1=1, 1, p_id) WHERE t.i=1;
ERROR 1048 (23000): Column 'p_id' cannot be null
mysql> UPDATE t1 t JOIN s1 s ON s.s_id =t.i SET p_id = IF(1=1, 2, p_id) WHERE t.i=1;
ERROR 1048 (23000): Column 'p_id' cannot be null
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;
ERROR 1048 (23000): Column 'p_id' cannot be null