[MDEV-31980] Unexpected "Truncated incorrect DECIMAL value" error Created: 2023-08-22  Updated: 2024-02-01

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Major
Reporter: Damien Regad Assignee: Alexander Barkov
Resolution: Unresolved Votes: 2
Labels: None
Environment:

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).

 Comments   
Comment by Alexander Barkov [ 2024-01-24 ]

Verified as described. However, it looks like 'Not a Bug', everything works as expected. See explanations below:

1. Works fine on an empty table

The condition is not even evaluated in this example.

SET sql_mode='STRICT_TRANS_TABLES';
 
CREATE OR REPLACE TABLE t1 (a VARCHAR(5));
 
UPDATE t1 SET a='' WHERE a=0;


Query OK, 0 rows affected (0.001 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0

2. Works fine with a varchar column '0' and integer literal 0

There are no reasons for warnings: '0' is compared to 0 without problems, both sides are converted to DECIMAL 0.

SET sql_mode='STRICT_TRANS_TABLES';
 
CREATE OR REPLACE TABLE t1 (a VARCHAR(5));
 
INSERT INTO t1 VALUES (0);
 
UPDATE t1 SET a='' WHERE a=0;


Query OK, 1 row affected (0.002 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0

3. Returns an error: a varchar column '' and an integer literal 0

The varchar column value of '' gets converted to DECIMAL 0 with a warning, strict mode converts the warning to the error.

SET sql_mode='STRICT_TRANS_TABLES';
 
CREATE OR REPLACE TABLE t1 (a VARCHAR(5));
 
INSERT INTO t1 VALUES ('');
 
UPDATE t1 SET a='' WHERE a=0;


ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''

4. Returns an error the other way around: an integer column 0 vs a string literal ''

The string literal value of '' gets converted to DECIMAL 0 with a warning, strict mode converts the warning to the error.

SET sql_mode='STRICT_TRANS_TABLES';
 
CREATE OR REPLACE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (0);
 
UPDATE t1 SET a=1 WHERE a='';


ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''

Comment by Roel Van de Paar [ 2024-01-30 ]

dregad Please see above. Let us know if you have any other questions.

Comment by Damien Regad [ 2024-01-30 ]

Thanks for the analysis and feedback.

The varchar column value of '' gets converted to DECIMAL 0 with a warning, strict mode converts the warning to the error

In that case I would expect that with a CAST, the update would execute without a warning, but it does not...

SET sql_mode='STRICT_TRANS_TABLES';
 
CREATE OR REPLACE TABLE t1 (a VARCHAR(5));
 
INSERT INTO t1 VALUES ('');
 
UPDATE t1 SET a='' WHERE CAST(a AS DECIMAL)=0;

Still throws ERROR 1292 (22007): Truncated incorrect DECIMAL value: ''...

Am I missing something ?

Also, following your logic, shouldn't the insert throw a warning as well when the 0 implicitly gets converted to '' ? 

INSERT INTO t1 VALUES (0);

Generated at Thu Feb 08 10:27:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.