SQL Error [1292] "Truncated incorrect INTEGER value" - with Varchar columns and CASE. Why?

Question

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.

Answer 1

I received this message doing an insert. The select worked fine but when prefixed with "insert into blablabla" it hated my guts. The problem turned out the be the character set of the target varchar. My dev environ defaulted to "latin" when creating a new varchar for a new table, but the varchar in the source table was "utf8" with a "utf8_unicode_ci" collation. When I corrected that in the new destination table, MySQL and I were on much better terms.

Answer 2

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.

Answer 3

(too big for a comment)

    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

can be collapsed to

    WHEN Z = 'XYZ' OR A IS NULL  THEN B

The rest can possibly be simplified. (The simplifications may make the error go away.)