MySQL Bugs: #30620: Wrong result set

Bug #30620	Wrong result set
Submitted:	24 Aug 2007 11:15	Modified:	24 Sep 2007 11:44
Reporter:	Vladimir Nikolic	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Windows (Server 2000)
Assigned to:		CPU Architecture:	Any
Tags:	IN, subquery

Description:
When i am using this query:
select * from tbl_product  where  
cat_id IN
( SELECT cat_id from tbl_category where cat_parent_id = '13' order by cat_parent_id )

I dont get results back. If i run only SUBQUERY- SELECT cat_id from tbl_category where cat_parent_id = '13' order by cat_parent_id, i got 4 rows.

cat_id field in tbl_product looks like 16,17,26,27.

I noticed that if i run some other subquery with e.g. cat_parent_id='12' i get results back.
So, if cat_id in tbl_product STARTS with some of subquery results, i got results back, and if not starts with it, i got empty rows.

hm. confusing.
Lets try like this.
i have a field in tbl_product with value 14,15,16,17 or 20,21,27,28,45 ...
if i use select * from from tbl_product where cat_id IN (21,27,35) or i use select * from tbl_product where cat_id IN(15,16,21) i got empty rows. 
But if i use select * from tbl_product where cat_id IN (20,25,15) or i use select * from tbl_product where cat_id IN(14,16,21) i will get rows which starts with 20 or 14.
I think that this query should return result if any of IN(value1,value2...) is in the cat_id field?

How to repeat:
Re run the query

Thank you for the bug report. The reported version is quite older, could you
please upgrade to latest released version and if you will get the same issue
please provide a dump file with create table and insert command. Thanks in
advance.

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