MySQL Bugs: #36958: left join on small size index

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:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	left join, small size index

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.

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.

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;

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.

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".