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