MySQL Bugs: #34407: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish

Bug #34407	Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COE
Submitted:	8 Feb 2008 9:28	Modified:	11 Feb 2008 10:38
Reporter:	Syam lal	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	COERCIBLE, for operation 'UNION', Illegal mix of collations, implicit, latin1_swedish_ci, utf8_general_ci

Description:
When I run the following query in version 5.0.45 using MySQL Editor like Navicat, it works fine. But it doesn't work when i run it form PHP5.2.5, I'm getting the following error:
 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'

Query: 
(SELECT `ht_news`.`news_id` AS `id`, `ht_news`.`news_title` AS `title`, `ht_news`.`news_title` AS `client`, `ht_news`.`news_descr` AS `descr`, `ht_news`.`news_title` AS `categories`, `ht_news`.`news_date` AS `date`, `ht_news`.`news_img` AS `news_img`, if(`ht_news`.`news_enable` = 'Y','news','') As `rsstype` FROM `ht_news` WHERE `ht_news`.`news_enable` = 'Y') UNION (SELECT `ht_projects`.`prj_id` AS `id`, `ht_projects`.`prj_name` AS `title`, `ht_clients`.`cl_name` AS `client`, `ht_projects`.`prj_descr` AS `descr`, `ht_projects`.`prj_categories` AS `categories`, `ht_projects`.`prj_completion_date` AS `date`, if(`ht_projects`.`prj_enable` = 'Y','','') AS `news_img`, if(`ht_projects`.`prj_enable` = 'Y','project','') AS `rsstype` FROM `ht_projects`, `ht_clients` WHERE `ht_projects`.`prj_enable` = 'Y' AND `ht_clients`.`cl_id` = `ht_projects`.`cl_id`) ORDER BY `date` DESC

How to repeat:
Few MySQL Settings from my.ini

default-character-set=latin1
default-character-set=latin1
default-storage-engine=INNODB

My tables are set to utf8 / utf8_general_ci

This occured when i upgraded my MySQL to 5.0.45.

Table Script can be found in Private Comments section

Steps:
Create few records in the table and try using the query specified in Description section, it is working in Navicat (MySQL Editor) but not working when I'm calling the same form PHP.  It shows " Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'"

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You have in my.ini:

default-character-set=latin1
default-character-set=latin1
default-storage-engine=INNODB

Also you have in the query:

SELECT (...`HT_NEWS`.`NEWS_IMG` AS `NEWS_IMG`, ...) UNION SELECT (... IF(`HT_PROJECTS`.`PRJ_ENABLE` = 'Y','','') AS `NEWS_IMG`, ...) ...

So for first table you use column collation which is utf8_general_ci and for second client collation which is latin1_swedish_ci

See also http://dev.mysql.com/doc/refman/5.0/en/internationalization-localization.html