Re: Union Fails due to character sets

[Alexander Barkov]

Which version of MySQL are you using?
This problem existed in the past, but was fixed.
It should now work without having to specify CONVERT()
when mixing an utf8 string constant consisting
of ascii letters, and a latin1 column.

This script works fine for me with 4.1.19:

SET NAMES utf8;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (A int, B int);
CREATE TABLE t2 (A int, B int, C varchar(32) character set latin1);
INSERT INTO t1 VALUES (1,1);
INSERT INTO t2 VALUES (2,2,'Test');
SELECT
A,
B,
'SHIPPED' as Status
FROM t1
UNION
SELECT
A,
B,
C as Status
FROM t2;