Bug #20123 #1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swed

Description:
Salam guys i have a query and receiving the error like this 
#1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' 

query is here 
SELECT c.claim_id, c.claim_identifier, c.revision, c.status, case when c.date_sent != '0000-00-00 00:00:00' then DATE_FORMAT(c.date_sent,'%m/%d/%Y') else DATE_FORMAT(c.timestamp,'%m/%d/%Y') end as timestamp, sum(cl.amount) as amount, concat_ws(', ',p.last_name,p.first_name) as patient_name, p.record_number, p.identifier as patient_identifier, co.name as facility_name, pa.name as payer_name, date_format(min(cl.date_of_treatment),'%m/%d/%Y') date_of_treatment FROM fblatest_revision lr left join fbclaim c using (claim_identifier,revision) left join fbclaimline cl using(claim_id) left join fbperson as p on p.claim_id = c.claim_id and p.type='FBPatient' and p.`index` = 0 left join fbcompany as co on p.claim_id = co.claim_id and co.type='FBTreatingFacility' and co.`index` = 0 left join fbcompany as pa on p.claim_id = pa.claim_id and pa.type='FBPayer' and pa.`index` = 0 GROUP BY c.claim_id 

setup for database is as follows 
CREATE TABLE fbclaim ( 
claim_id int(11) NOT NULL DEFAULT '0' COMMENT '', 
claim_identifier varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci, 
revision int(11) NOT NULL DEFAULT '0' COMMENT '', 
status enum('new','pending','sent','archive') NOT NULL DEFAULT 'new' COMMENT '' COLLATE latin1_swedish_ci, 
`timestamp` timestamp NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT '', 
date_sent datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '', 
format varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci, 
PRIMARY KEY (claim_id) 
) TYPE=MyISAM; 

CREATE TABLE `fbclaimline` ( 
`claimline_id` int(11) NOT NULL default '0', 
`claim_id` int(11) NOT NULL default '0', 
`procedure` varchar(10) NOT NULL default '', 
`modifier` varchar(4) NOT NULL default '', 
`amount` float(11,2) NOT NULL default '0.00', 
`units` float(5,2) NOT NULL default '0.00', 
`comment` varchar(80) NOT NULL default '', 
`comment_type` varchar(10) NOT NULL default '', 
`date_of_treatment` datetime NOT NULL default '0000-00-00 00:00:00', 
`amount_paid` float(11,2) NOT NULL default '0.00', 
PRIMARY KEY (`claimline_id`), 
KEY `claim_id` (`claim_id`) 
) TYPE=MyISAM; 

CREATE TABLE `fbcompany` ( 
`company_id` int(11) NOT NULL default '0', 
`claim_id` int(11) NOT NULL default '0', 
`index` tinyint(4) NOT NULL DEFAULT '0' COMMENT '', 
`identifier` varchar(25) NOT NULL default '', 
`identifier_type` varchar(10) NOT NULL default '', 
`type` varchar(50) NOT NULL default '', 
`name` varchar(100) NOT NULL default '', 
`phone_number` varchar(45) NOT NULL default '', 
PRIMARY KEY (`company_id`) 
) TYPE=MyISAM COMMENT='Base Company record most of the data is in linked tables'; 

CREATE TABLE `fbperson` ( 
`person_id` int(11) NOT NULL default '0', 
`claim_id` int(11) NOT NULL default '0', 
`index` tinyint(4) NOT NULL DEFAULT '0' COMMENT '', 
`type` varchar(50) NOT NULL default '', 
`identifier` varchar(100) NOT NULL default '', 
`identifier_type` varchar(10) NOT NULL default '', 
`record_number` varchar(255) NOT NULL default '', 
`salutation` varchar(20) NOT NULL default '', 
`last_name` varchar(100) NOT NULL default '', 
`first_name` varchar(100) NOT NULL default '', 
`middle_name` varchar(50) NOT NULL default '', 
`gender` enum('M','F','O') default NULL, 
`date_of_birth` date NOT NULL default '0000-00-00', 
`phone_number` varchar(45) NOT NULL default '', 
`comment` varchar(255) NOT NULL default '', 
PRIMARY KEY (`person_id`) 
) TYPE=MyISAM COMMENT='A person in the system'; 

CREATE TABLE `fblatest_revision` ( 
`claim_identifier` varchar(255) NOT NULL default '', 
`revision` int(11) NOT NULL default '0', 
PRIMARY KEY (`claim_identifier`) 
) TYPE=MyISAM; 

Help Out to execute the above mentioned select query successfully...

How to repeat:
execute the selecte query the same error message will appear again and again.

Thank you for a problem report. Please, specify the exact versions of Query Browser and MySQL server used.

I have the same problem. Here are some details:

MySQL server: 4.1.11-standard (Linux)
Query Browser: 1.1.20 (Win XP Pro)

Table definition (from "show create table"):
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(40) default NULL,
  `music` set('Classical','Jazz','Rock','World','Other') default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Query: 
select * from test where find_in_set('Classical', music) = 0;

This query executes ok if run directly on the server (locale is set to en_US.UTF-8) from mysql client program.

However, when entered from the QB remotely it gives me "illegal mix of collations" error.

If I check table details in the QB I see that it implicitely sets collation for this table to latin1_swedish_ci.

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".

I also am experiencing the same problem. It doesn't happen in the MySQL shell, only in the MySQL Tools' Query Browser.

[!-- QUERY --!]
SELECT field_id, IF (field_id=10,FROM_UNIXTIME(value,'%Y'),value), subscriber_id FROM `pommo_subscriber_data` WHERE subscriber_id IN(6,2,1,11,8,10,3,4,9,7,5);

[!-- RESULT --!]
Query Browser: Error executing query. 1267 - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'if'

[!-- WORKAROUNDS --!]

  1) If I change the character set/collation of the pommo_subscriber_data table to utf8 via; "ALTER TABLE pommo_subscriber_data CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" it will *work in the query broswer*

  2) The query executes *fine* in MySQL shell regardless of the character set/collation of pommo_subscriber_data -- leading me to believe this issue is related to the libraries the Query Browser is linked against??

Pertinent Info:

MySQL Query Browser: 1.2.12
MySQL Server: mysql  Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i686) using readline 5.2
libmysqlclient: 5.0.45

System: Linux Slade 2.6.22-iCEBURG #11 Fri Aug 3 21:19:33 EDT 2007 i686 AMD Athlon(tm) 64 Processor 3000+ AuthenticAMD GNU/Linux

Let me know if there's anything else I can provide.

Thanks!

~ Brice