Bug #15949 union + illegal mix of collations (IMPLICIT + COERCIBLE)
Submitted: 23 Dec 2005 2:09 Modified: 15 Mar 2006 14:04
Reporter: Timothy Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:Any (any)
Assigned to: Alexander Barkov CPU Architecture:Any

[23 Dec 2005 2:09] Timothy Smith
Description:
See How to Repeat.

Since the two collations have different precedence, I expect that one will be coerced to the other in order to make the query work OK.

How to repeat:

-- I'm using default settings for everything

drop table if exists foo;
create table foo (foo varchar(100)) collate ascii_bin;
insert into foo (foo) values ("foo");
select foo from foo union select 'bar' as foo from dual;

Running this gives me:

mysql> select foo from foo union select 'bar' as foo from dual;
ERROR 1267 (HY000): Illegal mix of collations (ascii_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'

Suggested fix:

Issue "set names ascii", or something similar, to work around this.
[23 Dec 2005 2:10] Timothy Smith
I'm sorry, I forgot to mention that I tried this with the most recent 5.0 code, and also with a recent 4.1.
[19 Feb 2006 12:59] Jan Rabenberg
Similar problem:
SELECT persoons_id, achternaam, 
COUNT( IF(onderdeel='800 meter',1,NULL) ) AS '800 m'
FROM baan_uitslagen
WHERE loopnaam = 'loopdriekamp' 
GROUP BY persoons_id 
UNION
SELECT ' ', 'Aantal', 
COUNT( IF(onderdeel='800 meter',1,NULL) ) 
FROM baan_uitslagen 
WHERE loopnaam = 'loopdriekamp' 
GROUP BY loopnaam ;
ERROR Message #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'UNION' 
Workaround:
... UNION
SELECT _latin1 ' ' COLLATE latin1_swedish_ci, _latin1 'Aantal' COLLATE latin1_swedish_ci  ...   ## other parts unchanged. 

Why is the utf8_general_ci (the communication collation) NOT automatically coerced to latin1_swedish_ci?
Is there a better workaround?
[1 Mar 2006 14:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3323
[6 Mar 2006 14:18] Alexander Barkov
Pushed into 4.1.19  and 5.0.20. 

Cannot push into 5.1.x yet, due to many unmerged changes.
[14 Mar 2006 16:45] Mike Hillyer
Is this in 5.1 yet? Do we have a version number?
[15 Mar 2006 5:05] Alexander Barkov
Merged into 5.1.8
[15 Mar 2006 14:04] Paul Dubois
Noted in 4.1.19, 5.0.20, 5.1.8 changelogs.

Character set conversion of string constants for
<literal>UNION</literal> of constant and table column was not
done when it was safe to do so. (Bug #15949)