Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8).
Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] > -----Original Message----- > From: Andrés Villanueva [mailto:[EMAIL PROTECTED] > Sent: 03 May 2005 15:33 > To: mysql@lists.mysql.com > Subject: Collation problems or messed joins? > > Hi everyone! I'm havng the weirdest problem with mysql. > I had v1.1.07 and everything worked like a charm. But it turns out that > we had the need to move everything to UTF8 (until then, only the columns > in russian where set to utf8, and the rest was latin1). > Once we moved everything to utf8, this (the query is at the end of the > email) didn't work anymore, apparently because of a collation problem > with the literals. After doing some research i found that this wasn't a > problem anymore in 4.1.11, so i decided to upgrade... > After upgrading the query doesn't throw an error, but it doesn't return > all the data it should... > The funny thing is that i thought that the problem was in the 'case's > that i use in the query, so i remove them from the query and the query > runs fine. But later, i tried something else, and insetead of removing > the cases y switched the inners for left joins, and the query worked > fine, just like before, and it turns out it doesn't even matter wether I > switch both joins or just one of them (either one or the other). > If anybody can test this, and needs more data, or needs a script with > the tables and data, I'll be glad to send it. > > Thanks!! > Andrés Villanueva > > This is the problematic query: > > SELECT paper_id, Number, Revision, b.type, c.type_en as type2, a.name_en > as name > , case name_en when '' then 'N' else 'Y' end as name_en > , case name_fr when '' then 'N' else 'Y' end as name_fr > , case name_ru when '' then 'N' else 'Y' end as name_ru > , case name_sp when '' then 'N' else 'Y' end as name_sp > FROM `tpatcm28_paper` a > inner join `tpgen_fmt_type` b using (fmt_type_id) > inner join `tpatcm_pap_type` c on (a.pap_type_id = c.pap_type_id) > order by Number, Revision > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] ************************************************************************************** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000 Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ************************************************************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
