Details
Major Description
With strict mode (STRICT_TRANS_TABLES) enabled, implicit type conversion yields unexpected results:
set sql_mode='STRICT_TRANS_TABLES';
|
drop table if exists test;
|
create table test (a varchar(5));
|
Updating with an implicit type conversion in the where clause works when the table is empty, or when there is data matching the criteria:
MariaDB [test]> update test set a='' where a = 0;
|
Query OK, 0 rows affected (0.000 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
|
|
MariaDB [test]> insert into test values (0);
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [test]> update test set a='' where a = 0;
|
Query OK, 1 row affected (0.001 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
But when there is no match, an unexpected error occurs:
MariaDB [test]> update test set a='' where a = 0;
|
ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''
|
With MySQL 8.0.33 this works just fine (but triggers a warning).