Description:
I am performing a JOIN of two tables "one" and "two" on a VARCHAR field "value". In table "one" the field has the BINARY attribute set and in field "two" it does not. I get the following error:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_bin,IMPLICIT) for operation '=' (1267)
The coercibility of both one.value and two.value is 2 (IMPLICIT).
How to repeat:
CREATE TABLE `one` (
`ID` bigint(20) NOT NULL auto_increment,
`value` varchar(255) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `two` (
`ID` bigint(20) NOT NULL auto_increment,
`value` varchar(255) character set latin1 collate latin1_bin
NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `one` (value) VALUES ('testing');
INSERT INTO `two` (value) VALUES ('testing');
SELECT * FROM `one` LEFT JOIN `two` ON `one`.`value` = `two`.`value`;
Suggested fix:
The comparison between two values that have a coercibility of 2 should result in a collation auto-cast being performed. This would allow a statement like that in "how to repeat" work and would ensure that valid MySQL 4.0 code would continue to work in MySQL 4.1.
The end-user workaround is to make sure that both fields have the BINARY attribute or to specify it explicitly within the SELECT statement. But it doesn't seem like these schema or code changes should be required in order to update to MySQL 4.1.
SELECT * FROM `one` LEFT JOIN `two` ON BINARY `one`.`value` = `two`.`value`;
Description: I am performing a JOIN of two tables "one" and "two" on a VARCHAR field "value". In table "one" the field has the BINARY attribute set and in field "two" it does not. I get the following error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_bin,IMPLICIT) for operation '=' (1267) The coercibility of both one.value and two.value is 2 (IMPLICIT). How to repeat: CREATE TABLE `one` ( `ID` bigint(20) NOT NULL auto_increment, `value` varchar(255) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `two` ( `ID` bigint(20) NOT NULL auto_increment, `value` varchar(255) character set latin1 collate latin1_bin NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `one` (value) VALUES ('testing'); INSERT INTO `two` (value) VALUES ('testing'); SELECT * FROM `one` LEFT JOIN `two` ON `one`.`value` = `two`.`value`; Suggested fix: The comparison between two values that have a coercibility of 2 should result in a collation auto-cast being performed. This would allow a statement like that in "how to repeat" work and would ensure that valid MySQL 4.0 code would continue to work in MySQL 4.1. The end-user workaround is to make sure that both fields have the BINARY attribute or to specify it explicitly within the SELECT statement. But it doesn't seem like these schema or code changes should be required in order to update to MySQL 4.1. SELECT * FROM `one` LEFT JOIN `two` ON BINARY `one`.`value` = `two`.`value`;