MySQL Bugs: #53229: Query results missing known results with no generated error messages

Bug #53229	Query results missing known results with no generated error messages
Submitted:	27 Apr 2010 22:51	Modified:	2 May 2010 17:57
Reporter:	Marshall DenHartog	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.16, 5.0.22 and 5.1.43	OS:	Any (Linux and Windows tested)
Assigned to:		CPU Architecture:	Any
Tags:	data missing, large tables, query, wrong result returned

Description:
The following query builds either a 611 row temporary tmp2 table if the smaller WHERE m_row_id is used or a 4898 row tmp2 if the larger WHERE m_row_id is used.  In both cases, tmp2 has two entries where m_row_id = 1993266.  The subsequent query from tmp2 into global_session_link will fail everytime from the larger tmp2 returning no entries where m_row_id = 1993266. Likewise, it will succeed every time from the smaller tmp2 returning the expected two entries where m_row_id = 1993266.  No error of any kind is generated and extensive review of temporary table space and other system variables has been conducted to try and identify cause.  Both source tables are MyISAM tables.  member_list_link has 967,952 rows and global_session_link has 1,958,645 rows.  I have dumps of both files for anyone who would like to recreate this puzzler.  Obviously, any assumptions regarding the reliability of simple queries into large MyISAM tables seem to be seriously questionable given this anomaly.  The work-around, if it can be considered reasonable, is to first of all guess which queries might be impaired by this issue and convert them to stored procedures using a looping construct that builds small tmp2 tables and assembles tmp3 one piece at a time.

DROP TEMPORARY TABLE IF EXISTS tmp2;

CREATE TEMPORARY TABLE tmp2
SELECT
	mll.request_session_id,
	mll.m_row_id
FROM member_list_link mll
# WHERE m_row_id BETWEEN 1993000 AND 1993999; # not_ok
WHERE m_row_id BETWEEN 1993200 AND 1993299; # ok

SELECT * FROM tmp2 WHERE m_row_id = 1993266;

DROP TEMPORARY TABLE IF EXISTS tmp3;

CREATE TEMPORARY TABLE tmp3
SELECT
	gsl.global_session_id,
	mll.m_row_id
FROM tmp2 mll
INNER JOIN global_session_link gsl ON mll.request_session_id = gsl.request_session_id
GROUP BY global_session_id;

SELECT * FROM tmp3 WHERE m_row_id = 1993266;

How to repeat:
Request the sql dumps for the two tables from me using the email included in the private comment.

Build the tables on your MySQL server and execute the above query with the two variations as discussed.

Suggested fix:
Not sure what is wrong as no error message is generated.  I might suggest that someone needs to climb down there to the end of that tunnel and fix it... :)

Thank you for the bug report. You can upload the dump files (compressed file) at:

ftp://ftp.mysql.com/pub/mysql/upload/

use a file name which identify this bug i.e: bug53229.zip(tar.gz).

and comment here when done. Thanks in advance.

I believe the zip file containing the two table dumps as well as the test sql script were uploaded to bug53229(2).zip.  If bug53229.zip is also on the server, it should be ignored as the upload was interrupted very late in progress.

Thank you for the report.

You use GROUP BY without ORDER BY, so this is for MySQL server to decide which m_row_id is corresponded to particular global_session_id. It just chooses not 1993266, but other one. So this is not a bug.