mysql – SQL Error [1292] “Truncated incorrect INTEGER value” – with Varchar columns and CASE. Why?

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

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 SRC.A into TGT.C)


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.