MySQL Bugs: #7093: Case Syntax containing UTF8 column is broken in MySQL 4.1.7

Bug #7093	Case Syntax containing UTF8 column is broken in MySQL 4.1.7
Submitted:	8 Dec 2004 3:56	Modified:	8 Dec 2004 19:35
Reporter:	UnHa Kim	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.7 Standard binary distribution	OS:	Windows (Windows 2000 SP4)
Assigned to:		CPU Architecture:	Any

Description:
'CASE column WHEN [compare-value] THEN result ELSE column END' syntax containing utf8 column is broken.

I used same syntax with euc_kr character set in MySQL 3.2.5x and 4.0.x.

If MySQL 4.1.7 doesn't support that syntax, it should give case syntax parse error(like, 'case syntax does not support column in ELSE command'), not collation error.
If MySQL 4.1.7 is expected to support that syntax, it should check character set and collation first before give such error message.

I ran MySQL on Intel Pentium III with Intel 815 chipset.

How to repeat:
Character set of both Table1 and Col1 should be utf8 with following process.
 - In my.ini, there is a line read 'default-character-set=utf8'.
 - CREATE TABLE Table1 (Col1 VARCHAR(20)) TYPE = InnoDB;

All 3 sentences give same error message.

SELECT CASE Col1 WHEN 'ONE' THEN 1 ELSE Col1 END FROM Table1;
SELECT CASE Col1 WHEN _utf8 'ONE' THEN 1 ELSE Col1 END FROM Table1;
SELECT CASE Col1 WHEN _utf8 'ONE' COLLATE utf8_general_ci THEN 1 ELSE Col1 END FROM Table1;

ERROR 1267 <HY000>: Illegal mix of collations (latin1_swedish_ci, COERCIBLE) and (utf8_general_ci, IMPLICIT) for operation 'case'

CASE syntax in following form with UTF8 column is broken.
'CASE column WHEN [compare-value] THEN result ELSE column END'

I used same syntax without a problem in MySQL 3.2.5x and 4.0.x. (euc_kr as default character set)

If MySQL 4.1.7 doesn't support that syntax, it should give parse error telling that such CASE syntax is not supported. Not a collation error.
If MySQL 4.1.7 is expected to support that syntax, it should check character set and collation first before give such error message.

I ran MySQL on Intel Pentium III with Intel 815 chipset.

I got the same error using version 4.1.7, but it worked like a charm with 4.1.8.