MySQL Bugs: #15949: union + illegal mix of collations (IMPLICIT + COERCIBLE)

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

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.

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.

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?

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

Pushed into 4.1.19  and 5.0.20. 

Cannot push into 5.1.x yet, due to many unmerged changes.

Is this in 5.1 yet? Do we have a version number?

Merged into 5.1.8

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)

I am seeing the exact same issue in MySQL 8.0.19 fourteen years later!

This looks like a regression.

```
(1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'UNION'")
```

I'm having this problem, only in version 8.
Are there any predictions for correction in version 8.0.18?