MySQL Bugs: #32423: Character set specified on column w/o collation leads to err 1271 in UNION

Bug #32423	Character set specified on column w/o collation leads to err 1271 in UNION
Submitted:	15 Nov 2007 23:16	Modified:	29 Nov 2007 16:53
Reporter:	Mattias Thorslund	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
This one got me seriously confused. Despite there being a default character set and collation for the table, it seems that the specification of a character set (same as default!) without collation causes this problem. It is manifested when trying to run a UNION query.

Also tested and verified on 4.1.12.

How to repeat:
Setup:
======

Note the CHARSET definition in test1.textCol.

CREATE TABLE `test1` (
  `recordID` int(10) unsigned NOT NULL auto_increment,
  `textCol` varchar(5) CHARSET utf8 NOT NULL default '',
  PRIMARY KEY  (`recordID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `test2` (
  `recordID` int(10) unsigned NOT NULL auto_increment,
  `textCol` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`recordID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Test:
=====
SELECT textCol FROM test1 UNION SELECT textCol FROM test2;

Result:
=======
ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'

Suggested fix:
I would have expected the default collation used for test1.textCol to be the default collation for the table (and therefore no conflict).

Thank you for a bug report. Verified just as described.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Chapter 9.1.3.4. "Column Character Set and Collation" (http://dev.mysql.com/doc/refman/5.1/en/charset-column.html) says :
"If CHARACTER SET X was specified without COLLATE, then character set X and its default collation are used.".

According to 9.1.11. "Character Sets and Collations That MySQL Supports" (http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html) the default collation for the utf-8 charset is  utf8_general_ci.

As a result the collation of test1.textCol (utf8_general_ci, the default for the column charset utf8) differs from the collation of test2.textCol (utf8_unicode_ci, inherited from the table collation specifier) and you're getting the error.