In a MySQL 5.7 environment, when trying to insert a varchar value into a varchar field, I get the error:
SQL Error (1292) (22001): Data truncation: Truncated incorrect INTEGER value: ‘1876301/347’
The source and target columns are both of the datatype
Here’s a small demo to reproduce an example of the error (it occurs on the last insert):
create table SRC ( Z varchar(20) null, A varchar(63) null, B varchar(30) null ); insert into SRC select 'XYZ', '1876301/347', null ; create table TGT ( C varchar(63) null ) ;
To generate the error, now run this: (is the 3rd
WHEN that matches, meaning it’s trying to insert the value from
insert into TGT ( C ) select CASE WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NULL THEN NULL WHEN if(Z in ('XYZ'), A, NULL) IS NULL AND B IS NOT NULL THEN B WHEN if(Z in ('XYZ'), A, NULL) IS NOT NULL AND B IS NULL THEN A WHEN if(Z in ('XYZ'), A, NULL) IS NOT NULL AND if(Z in ('XYZ'), A, NULL) = B THEN A ELSE if(Z in ('XYZ'), A, NULL) END AS C from SRC
It seems to be related to there being a case statement, as, when I try to simply insert the same value directly, it works fine.
insert into TGT ( C ) select A from SRC
It also works fine with the
CASE STATEMENT if I use
INSERT IGNORE INTO..., but I’d rather not use that.