Re: Collation problems or messed joins?

Tue, 03 May 2005 08:15:48 -0700

The tables are now entirely in utf8, and that is also the instance's default.
I'm using the .net dll to connect and the connection string has charset = utf8.
I'm testing everything with the query browser, and i get the exact same behaviour.
Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly.

Thanks
Andrés Villanueva

Kevin Cowley wrote:

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]

Reply via email to