Bug #16747 concat using if statement reports illegal mix of collations
Submitted: 24 Jan 2006 11:04 Modified: 24 Feb 2006 13:13
Reporter: Ron Gommers Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mySQL 5.0.18-nt OS:Windows (W2K 5.00.2195)
Assigned to: CPU Architecture:Any

[24 Jan 2006 11:04] Ron Gommers 
Description:
When trying to concat a one character field with the output of an if statement, error 1267 is returned. To me this looks sort of the same as on bug 6519, or maybe I'm doing something wrong....
ref: http://bugs.mysql.com/bug.php?id=6519

select 
,concat(o.status,'!')
,if(length(o.info)=0,' ','!')
,concat(o.status,if(length(o.info)=0,' ','!'))
from orders o 

The first 2 work perfectly, the third one fails with the following message:

Error 1267: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'

p.s. o.status is a varchar(1) and o.info is a TEXT field

How to repeat:
see description

Suggested fix:
workaround ?
[24 Jan 2006 11:49] Hartmut Holzgraefe 
please add "SHOW CREATE TABLE orders;" and "SHOW VARIABLES;" output
[24 Jan 2006 15:43] Ron Gommers 
show create table and show variables output

Attachment: output.txt (text/plain), 26.47 KiB.
[25 Feb 2006 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".