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

MySQLmysql-5.7

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.

Best Answer

Seems like a bug in mysql5.7 - it works both in mysql5.6 and mysql8.0.

I have no idea why it can be like that, but I was able to reproduce that with simpler insert statement:

insert into TGT(C)
select
    CASE
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL THEN NULL
    END AS C
from SRC;

Seems like some implicit conversion makde by mysql. Forcing explicitly convert helps:

insert into TGT(C)
select
    CASE
        WHEN if(Z in ('XYZ'), convert(A, char(63)), NULL) IS NULL THEN NULL
    END AS C
from SRC;

(but converting whole case..end does not.

Related Question