MySQL Bugs: #9703: Error 1032 with GROUP BY query and large tables

Bug #9703	Error 1032 with GROUP BY query and large tables
Submitted:	7 Apr 2005 4:26	Modified:	12 May 2005 22:17
Reporter:	Chris Lewis
Status:	Closed
Category:	Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Linux (RedHat EL3)
Assigned to:	Evgeny Potemkin	Target Version:

Description:
Upon upgrade to 4.1.11 today we began seeing this error on our homegrown forum system. 
The error is:

ERROR 1032 (HY000): Can't find record in ''

I will attach a mysqldump of the table structures and data, since this is a query across
several tables with several thousand rows and the problem goes away if I create a test
table with few rows.  The SQL is below, and works correctly in 4.1.10:

select T.ThreadID,count(distinct M.MessageID) as Msgs, 
U1.LoginName,UP1.PrefValue,U2.LoginName,UP2.PrefValue,
max(M.WhenPosted) as LatestPost, count(distinct I.SolvedBy) as Solvers
from Thread T 
inner join Users U1 on (T.StartedBy=U1.UserID) 
inner join Message M on (T.ThreadID=M.ThreadID) 
left join Identified I on (T.ThreadID=I.ThreadID) 
left join UserPrefs UP1 on (U1.UserID=UP1.UserID and UP1.PrefName='ScreenName') 
left join Users U2 on (I.SolvedBy=U2.UserID) 
left join UserPrefs UP2 on (U2.UserID=UP2.UserID and UP2.PrefName='ScreenName') 
group by T.ThreadID 
order by T.WhenStarted desc

The problem goes away if I do *any* of the following:
 - remove ORDER BY clause
 - remove all the group-function (count() etc) columns
 - remove any PrefName or PrefValue column from select list
 - remove a significant portion of rows from Thread table

I suspect it is an index problem, maybe related to bug 9213, however I have checked that
the table indexes do not match the problem at the bottom of bug 9213.  I have also tried
optimizing tables to no avail.

How to repeat:
Create populated tables from attached mysqldump. Run SQL from description above.

I can't attache the mysqldump file since it's greater than 200KB.  Download the file
from:
http://www.myfonts.com/bugtest.gz

Thanks for your excellent bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24487

Noted in 4.1.12 changelog.

When new row inserted to memory buffer and there no room to contain it, error raised, data
dumped to temporary file and server can continue, but error wasn't reset in
end_write_groups() and leads query to fail.

changeset 1.2203