MySQL Bugs: #28385: where in does not return what I expected

Bug #28385	where in does not return what I expected
Submitted:	12 May 2007 6:53	Modified:	23 Dec 2011 19:50
Reporter:	Steven	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.0.41	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	IN, SELECT, where

Description:
I have a collections table that has a partids column.  The ids stored in this column are id numbers for another table.  They are comma seperated like so.  546,685,759  If I do a query like this.  Select * from mainsubjects where id in (select partids from collections where id = 546)  I will only get the first record from mainsubjects.  Meaning it will only give me the first number before the comma.  But if I split it into 2 seperate queries and get the ids then do a select using those I can get it right.  I would like to know if you could add this feature.  I think it would make a lot of things easier for more than just me.

How to repeat:
create 2 tables.  both of them having a primary auto key.  One having a partid column that has ids from the other in it seperated with commas.

Suggested fix:
If you could make it so that the in function would see the whole result for the column it would work.

Thank you for the report.

You can currently do what if use proper functions. We are not allowed to write solutions here, but I recommend to convert subquery into JOIN and use appropriate WHERE clause for collections.partids and mainsubjects.id