MySQL Bugs: #39639: unexpected results in subquery using comparison function in across databases

Bug #39639	unexpected results in subquery using comparison function in across databases
Submitted:	25 Sep 2008 1:07	Modified:	25 Sep 2008 8:35
Reporter:	Mark Nipper	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.51a-12	OS:	Linux (Debian x86-64 2.6.26)
Assigned to:		CPU Architecture:	Any

Description:
This query contains values for the session_id column in the result set which are not found in the subquery:
---
select user_session_id, session_id from bug2.user_session where session_id in (select session_id from bug.quote_session) order by session_id asc;

The example data set can be found at http://nipsy.bitgnome.net/dbs.sql.gz if I can't find a different way to attach that data to this report.

Maybe my understanding of things is wrong, but I would think that the results should only contain session_id's which were found in both tables instead of including session_id's which cannot be found at all in the bug.quote_session table.

But maybe this is the way partially indexed strings behave since it appears to be matching any session_id up to five places and including those in the results.

How to repeat:
Import the above databases and run the query above.  Compare to the output from:
---
select * from bug.quote_session order by session_id asc;

and you'll see columns appearing in the result set where the session_id does not appear in the referenced bug.quote_session table.

sample data set illustrating potential subquery bug

Attachment: dbs.sql.bz2 (application/x-bzip, text), 471.01 KiB.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67, and inform about the results.

Okay, I just compiled the latest Ubuntu version of MySQL under Debian, so I'm now running 5.0.67-0ubuntu6.  Strangely enough, problem query returns no results at all.  So now I'm really confused.  Is the query somehow malformed?  Why would it return results, albeit seemingly incorrectly, under 5.0.51 but nothing at all under 5.0.67?

Thank you for the feedback.

If look into result below:

mysql> select session_id from bug2.user_session join bug.quote_session using (session_id);
Empty set (0.10 sec)

there is no records in the table bug2.user_session which satisfy condition. So results of 5.0.67 seems to be correct. So I close the report as "Can't repeat". If you can show they are not feel free to reopen the report.