CAST(expr AS CHAR) - default charset & collation

[R C]

Hi, the manual says "CAST(expr AS CHAR) treats the expression as a string with the default character set." However, check out this little test case:


CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `test`.`foo` (
`num` INT( 11 ) NOT NULL DEFAULT '0',
`str` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
) ENGINE = MYISAM

Then I do a query:

SELECT * FROM `foo` WHERE str = CAST(num AS CHAR)

I get the error:
"#1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' "


I cannot figure out why CAST expr AS CHAR is being treated as "utf8_unicode_ci". Does anybody know why it's behaving this way?

Thanks



Edited 1 time(s). Last edit at 01/13/2009 04:55PM by R C.