Cannot do join on varchar

[Francis Mak]

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