Bug #36958 left join on small size index
Submitted: 26 May 2008 12:19 Modified: 2 Jul 2008 20:04
Reporter: Surya Prabha Vadlamani Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0.22 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: left join, small size index

[26 May 2008 12:19] Surya Prabha Vadlamani
Description:
When a varchar column has an index of some specific size, a left join with equal to condition on this column fails for the rows which have text longer than the index size. 

How to repeat:
create table test_table_one (`id` int(10) not null AUTO_INCREMENT,
`name` varchar(50) not null, 
primary key (`id`),
key `index_name`(`name`(10))
)
ENGINE=InnoDB DEFAULT CHARSET=utf8; 

create table test_table_two(`name` varchar(50) not null, 
`club_member` boolean not null default false
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

With some test data, run the following query

select two.name, one.id from test_table_two two 
left join 
test_table_one one 
on 
two.name = one.name 
where club_member is true;

This query will result in successful equal to only for the rows whose name is less than 10 characters long. This is the case only when we use the equal to in the join condition. Regular query like, 

select * from test_table_one where name = 'hello world'; 

works fine.
[26 May 2008 12:33] Miguel Solorzano
Thank you for the bug report. Could you please provide a sample data insert
and also provide the exactly server version that you are using ( I could not
repeat with source server and my own data). Thanks in advance.
[27 May 2008 10:39] Surya Prabha Vadlamani
The server version is 5.0.22 installed on Windows 2003 server. And tested on Windows XP also. 

Please change the length of the varchar column in both the tables to 100. Then it will fail. It is observed when the index length is 10, the left join works fine when the varchar columns are 85 long. It started failing from 86. That is

create table test_table_one (`id` int(10) not null AUTO_INCREMENT,
`name` varchar(86) not null, 
primary key (`id`),
key `index_name`(`name`(10))
)
ENGINE=InnoDB DEFAULT CHARSET=utf8; 

create table test_table_two(`name` varchar(86) not null, 
`club_member` boolean not null default false
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
[2 Jun 2008 20:04] Sveta Smirnova
Thank you for the feedback.

But version 5.0.22 is too old and many bugs fixed since. Please upgrade to current version 5.0.51b, try with it and inform us about results.
[2 Jul 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".