Bug #1523 incorrect execution of subqueries with filesort
Submitted: 10 Oct 2003 11:47 Modified: 1 Nov 2003 10:14
Reporter: Oleksandr Byelkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:Any (any)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[10 Oct 2003 11:47] Oleksandr Byelkin 
Description:
incorrect execution of subqueries with filesort 

How to repeat:
CREATE TABLE t1 (number char(11) NOT NULL default '') TYPE=MyISAM 
CHARSET=latin1; 
INSERT INTO t1 VALUES 
('69294728265'),('18621828126'),('89356874041'),('95895001874'); 
CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) 
TYPE=MyISAM CHARSET=latin1; 
INSERT INTO t2 VALUES 
('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6'); 
select c.number as phone,(select p.code from t2 p where c.number like 
concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c; 
drop table t1, t2; 
 

Suggested fix:
store/restore join_tab
[10 Oct 2003 11:54] Oleksandr Byelkin 
ChangeSet 
  1.1590 03/10/10 21:49:48 bell@laptop.sanja.is.com.ua +4 -0 
  samall optimisation (any independent query will not save data for 
rexecution) 
  saving/restoring join_tab array (to save it of chnging by 
create_sort_index() before reexecuting) 
  (BUG#1523)
[1 Nov 2003 10:14] Oleksandr Byelkin 
bugfix is pushed