Bug #84933 same sql mode in 5.6 and 5.7 results in difference results

• Submitted: 10 Feb 2017 3:22
• Modified: 10 Feb 2017 9:25
• Reporter: Ke Lu
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6 5.7, 5.6.35, 5.5.54
• OS: Any
• CPU Architecture: Any
• Tags: replication, SQL_MODE

[10 Feb 2017 3:22] Ke Lu

Description:
SQL statement in 5.6 and 5.7 produce difference results under the same sql mode, and finally breaks the replication.

Master is 5.6.23, slave is a newly upgraded 5.7.15, after some time, we find the replication is broken. The error says:
Error 'Truncated incorrect DOUBLE value: 'null'' on 
Query: 'DELETE FROM db1.table1 WHERE label_id = 'null' AND r_id in (1466,1085,1086)'

Type of label_id is integer, this statement can be executed in 5.6.23 but not 5.7.15.
both are 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' (the default sql_mode in 5.7.15)

How to repeat:
create table t(id int primary key,c1 int);
insert into t values(1,1);

set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
delete from t where c1='a';

you will see the difference.

Suggested fix:
5.6 and 5.7 should have consistency behavior under the same sql mode for the same statement.

[10 Feb 2017 9:25] MySQL Verification Team

Hello Luke!

Thank you for the report and test case.
Imho 5.6 behavior seems to be affected.

Thanks,
Umesh

[10 Feb 2017 9:27] MySQL Verification Team

-- 5.6.35 - affected

root@localhost [test]> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 3 warnings (0.00 sec)

root@localhost [test]> delete from t where c1='a';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test]> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+

With STRICT_TRANS_TABLES, ideally an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled and statement is aborted and rolled back.

-- 5.7.17 - expected

root@localhost [test]> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> delete from t where c1='a';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'
root@localhost [test]> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)