Since I don't have control on this server(cannot upgrade), so I cannot tell if same happen in 4.1.3. However, I would like to know the result in 4.0 as well. Anyone test this script in 4.0/3.X server? I suspect this could be a unicode problem in 4.1? If 4.1.3 could fix the problem I will suggest my admin to do an upgrade. Luckly this is not a production server...
EXPLAIN SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id In Extra show: "Impossible WHERE noticed after reading const tables" I also found out that the trim should actually go to the 'matching' id: /* Previously I suggest trim on the left column: */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id /* However, this works as well, and it seems more logical? */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE TRIM(tb_test1.id)='abcde' AND tb_test1.id = tb_test2.id Anybody could explain this? Thank you. Francis -----Original Message----- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 8:19 PM To: 'Francis Mak '; '[EMAIL PROTECTED] ' Subject: RE: Cannot do join on varchar Have you tried this with the latest MySQL 4.1.x build? Does it not work in this version? What does the explain plan show? -----Original Message----- From: Francis Mak To: [EMAIL PROTECTED] Sent: 7/13/04 4:01 AM Subject: Cannot do join on varchar Hi, I found out that when joining varchar column, you have to trim down the left column in order for MYSQL to match the row, is it a known issue? Or join only works fine in number column? CREATE TABLE `tb_test1` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; CREATE TABLE `tb_test2` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; INSERT INTO tb_test1(id, name) VALUES('abcde', 'NAME1') INSERT INTO tb_test2(id, name) VALUES('abcde', 'NAME2') /* The following display zero row */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id /* The following will display correct result */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id Redhat 9, with MYSQL 4.1.1, using UTF8 as default charset. Any idea?? Thank you. Francis Mak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
