Details
-
Bug
-
Status: Needs Feedback (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
-
None
-
None
-
Ubuntu 22.04
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).