[MDEV-30342] Wrong "Truncated incorrect DECIMAL value" warning/error in recent releases Created: 2023-01-04  Updated: 2023-02-23  Resolved: 2023-01-09

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.10, 10.7.7, 10.8.6, 10.9.4, 10.10.2, 10.11.1
Fix Version/s: 10.11.2, 11.0.1, 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Blocker
Reporter: Hartmut Holzgraefe Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Problem/Incident
is caused by MDEV-29540 Incorrect sequence values in INSERT S... Closed

 Description   

The below worked fine so far, as expected, but starts to throw unexpected 

ERROR 1292 (22007) at line 15: Truncated incorrect DECIMAL value: '#'

in most recent releases. I looked over the 10.3.37 (lowest version release I can reproduce this on) release notes and changelog page, but could not find any change that seemed to be related to this change in behavior ...

drop table if exists t1;
 
drop table if exists t2;
 
 
 
create table t1(c1 varchar(1));
 
create table t2(c1 varchar(1));
 
 
 
set session sql_mode = STRICT_TRANS_TABLES;
 
 
 
insert into t1(c1) values('#');
 
 
 
insert into t2(c1)
 
  select if(c1 = '#', c1 = 0, c1) as c1
 
  from t1;



 Comments   
Comment by Sergei Golubchik [ 2023-01-07 ]

caused by https://github.com/MariaDB/server/commit/8c389393695

Comment by Daniel Black [ 2023-01-07 ]

For review: https://github.com/MariaDB/server/pull/2422

Comment by Daniel Black [ 2023-01-07 ]

While definitely a regression, isn't the current behaviour correct in emitting an error on the if function returning two different types? So is this actually a bug?

Taking a CREATE TABLE .. SELECT case

create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1
 
 
 
: query 'create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1' failed: 1292: Truncated incorrect DECIMAL value: '#'

This produces the same error even before MDEV-29540.

Comment by Sergei Golubchik [ 2023-01-08 ]

No, the current behavior is wrong. There are few considerations here:

  • sql_mode is STRICT_TRANS_TABLES but the test has no transactional tables, so CREATE ... SELECT should only produce a warning
  • but the original meaning of the STRICT_TRANS_TABLES was kind of "be strict if the statement can be completely rolled back", and unlike INSERT ... SELECT a CREATE ... SELECT can be "completely rolled back" even for non-transactional tables. In that logic, CREATE ... SELECT behaves correctly and INSERT ... SELECT did behave correctly before 8c389393695.
  • still currently we mainly interpret STRICT_TRANS_TABLES as "modifying transactional tables" and here the warning happens not on INSERT as such, but in the comparison. In that logic, this warning should be never treated as an error in any sql_mode and any table, transactional or not.

This should be eventually solved in MDEV-19362 (see also the discussion in comments). For now let's just fix the regression and restore the old behavior.

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