Description:
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)
mysql> drop table t1; create table t1(a decimal(10, 2) primary key);
Query OK, 0 rows affected (0.01 sec)
insert into t1 values (0);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (cast('123' as decimal));
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+--------+
| a |
+--------+
| 0.00 |
| 123.00 |
+--------+
2 rows in set (0.00 sec)
mysql> update t1 set a = cast('abc' as decimal);
ERROR 1292 (22007): Truncated incorrect DECIMAL value: 'abc'
mysql> update t1 set a = '456' where a = cast('abc' as decimal);
ERROR 1292 (22007): Truncated incorrect DECIMAL value: 'abc'
mysql> update t1 set a = '456' where a > cast('abc' as decimal);
Query OK, 1 row affected, 3 warnings (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 3
mysql> select * from t1 where a = cast('abc' as decimal);
+------+
| a |
+------+
| 0.00 |
+------+
1 row in set, 2 warnings (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)
In above scenario, there are two similar UPDATE sqls. They should have consistent behavior, no matter ending with error or warning.
Of course, since it is in 'strict_all_table' sql_mode, errors are expected here.
update t1 set a = '456' where a = cast('abc' as decimal);
update t1 set a = '456' where a > cast('abc' as decimal);
How to repeat:
set sql_mode='strict_all_tables';
drop table t1; create table t1(a decimal(10, 2) primary key);
insert into t1 values (0);
insert into t1 values (cast('123' as decimal));
select * from t1;
update t1 set a = cast('abc' as decimal);
update t1 set a = '456' where a = cast('abc' as decimal);
update t1 set a = '456' where a > cast('abc' as decimal);
select * from t1 where a = cast('abc' as decimal);
Suggested fix:
Since it is in sql_mode "strict_all_table", the following sql is expected to trigger "ERROR 1292 (22007): Truncated incorrect DECIMAL value: 'abc'", to be consistent with the above similar sql.
mysql> update t1 set a = '456' where a > cast('abc' as decimal);
Query OK, 1 row affected, 3 warnings (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 3
Description: mysql> set sql_mode='strict_all_tables'; Query OK, 0 rows affected (0.00 sec) mysql> drop table t1; create table t1(a decimal(10, 2) primary key); Query OK, 0 rows affected (0.01 sec) insert into t1 values (0); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (0); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (cast('123' as decimal)); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +--------+ | a | +--------+ | 0.00 | | 123.00 | +--------+ 2 rows in set (0.00 sec) mysql> update t1 set a = cast('abc' as decimal); ERROR 1292 (22007): Truncated incorrect DECIMAL value: 'abc' mysql> update t1 set a = '456' where a = cast('abc' as decimal); ERROR 1292 (22007): Truncated incorrect DECIMAL value: 'abc' mysql> update t1 set a = '456' where a > cast('abc' as decimal); Query OK, 1 row affected, 3 warnings (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 3 mysql> select * from t1 where a = cast('abc' as decimal); +------+ | a | +------+ | 0.00 | +------+ 1 row in set, 2 warnings (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.6.22-log | +------------+ 1 row in set (0.00 sec) In above scenario, there are two similar UPDATE sqls. They should have consistent behavior, no matter ending with error or warning. Of course, since it is in 'strict_all_table' sql_mode, errors are expected here. update t1 set a = '456' where a = cast('abc' as decimal); update t1 set a = '456' where a > cast('abc' as decimal); How to repeat: set sql_mode='strict_all_tables'; drop table t1; create table t1(a decimal(10, 2) primary key); insert into t1 values (0); insert into t1 values (cast('123' as decimal)); select * from t1; update t1 set a = cast('abc' as decimal); update t1 set a = '456' where a = cast('abc' as decimal); update t1 set a = '456' where a > cast('abc' as decimal); select * from t1 where a = cast('abc' as decimal); Suggested fix: Since it is in sql_mode "strict_all_table", the following sql is expected to trigger "ERROR 1292 (22007): Truncated incorrect DECIMAL value: 'abc'", to be consistent with the above similar sql. mysql> update t1 set a = '456' where a > cast('abc' as decimal); Query OK, 1 row affected, 3 warnings (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 3