Join condition based on varchar fails

[joydeepu]

I'm working on a mysql server 5.0.45 hosted on linux RHEL 5.0.

1. I've 2-tables as below:

table1:
acct_no VARCHAR(15)
column1 varchar(10)

table2:

acct_no VARCHAR(12)
column2 varchar(10)

In both the tables, there are many rows with identical A/c nos.

2. Of the below 2-queries, only the second one works.

SELECT a.acct_no,a.column1,b.column2
FROM table1 a, table2 b
WHERE trim(a.acct_no) = trim(b.acct_no)

SELECT a.acct_no,a.column1,b.column2
FROM table1 a, table2 b
WHERE a.acct_no*1 = b.acct_no*1

Why does varchar matching fail whereas numeric operation works?

[smantscheff]

I cannot confirm your observation. Here my test code with results:

drop table table1;
drop table table2;
create table table1 
(acct_no VARCHAR(15)
,column1 varchar(10)
);
create table table2
(acct_no VARCHAR(12)
,column2 varchar(10)
);
insert into table1 values ('1',1), ('2',2);
insert into table2 values ('1',1), ('2',2);

SELECT a.acct_no,a.column1,b.column2
FROM table1 a, table2 b
WHERE trim(a.acct_no) = trim(b.acct_no);

+---------+---------+---------+
| acct_no | column1 | column2 |
+---------+---------+---------+
| 1       | 1       | 1       |
| 2       | 2       | 2       |
+---------+---------+---------+

2 rows in set

SELECT a.acct_no,a.column1,b.column2
FROM table1 a, table2 b
WHERE a.acct_no*1 = b.acct_no*1;

+---------+---------+---------+
| acct_no | column1 | column2 |
+---------+---------+---------+
| 1       | 1       | 1       |
| 2       | 2       | 2       |
+---------+---------+---------+

[varmadba]

if you say there are identical acct_no in both tables then why are using trim function

To isolate your problem try selecting from both the tables by passing acct_no