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]
