#1518 "incompatible data types in combination" for case when statement

[John Wang]

Here are the tables I was creating:

DROP TABLE IF EXISTS Customer;

CREATE TABLE Customer (
ID INT NOT NULL AUTO_INCREMENT UNIQUE,
HomePhone VARCHAR(255) DEFAULT NULL,
WorkPhone VARCHAR(255) DEFAULT NULL,
CellPhone VARCHAR(255) DEFAULT NULL,
OtherPhone1 VARCHAR(255) DEFAULT NULL,
OtherPhone2 VARCHAR(255) DEFAULT NULL,
PrimaryPhone BIGINT DEFAULT NULL,
PRIMARY KEY (ID)
);

DROP TABLE IF EXISTS CustomerOptions;

CREATE TABLE CustomerOptions (
ID INT NOT NULL AUTO_INCREMENT UNIQUE,
CustomerId INT DEFAULT NULL,
OptionType INT DEFAULT NULL,
OptionValue VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (ID)
);

Here is the SQL query I need to execut:

SELECT c.Id,
CASE WHEN co.optionValue=1 THEN c.PrimaryPhone
WHEN co.optionValue=2 THEN c.CellPhone
WHEN co.optionValue=3 THEN c.WorkPhone
WHEN co.optionValue=4 THEN c.HomePhone
WHEN co.optionValue=5 THEN c.OtherPhone1
WHEN co.optionValue=6 THEN c.OtherPhone2
WHEN co.optionValue=-1 THEN NULL
END AS TextPhone
FROM CustomerOptions co INNER JOIN Customer c ON
c.id = co.CustomerId AND co.OptionType=10500

It complains about "incompatible data types in combination / Error Code: -5562 / State: 42562"
I think mainly because the PrimaryPhone column is bigint while others are varchar.

a vast system is already built on the above mentioned tables, and changing the column definition isn't really an option.

the same SQL query executes fine on MYSQL (production database).
we are using hsqldb to run unit-tests
so our unit-tests are not passing, but production is working fine.

[Fred Toussi]

MySQL automatically converts numerical values to chracter strings (and strings to numerical values) in comparison and when returning values. HSQLDB does this in a more limited number of sitautions when in MySQL compatibility mode. You can cast the BIGINT value to VARCHAR;

SELECT c.Id,
CASE WHEN co.optionValue=1 THEN CAST (c.PrimaryPhone AS VARCHAR(30))
WHEN co.optionValue=2 THEN c.CellPhone
WHEN co.optionValue=3 THEN c.WorkPhone
WHEN co.optionValue=4 THEN c.HomePhone
WHEN co.optionValue=5 THEN c.OtherPhone1
WHEN co.optionValue=6 THEN c.OtherPhone2
WHEN co.optionValue=-1 THEN NULL
END AS TextPhone
FROM CustomerOptions co INNER JOIN Customer c ON
c.id = co.CustomerId AND co.OptionType=10500

[John Wang]

Hi Fred

Thank you for your reply, I think this is a fine solution.