MySQL Bugs: #31464: Test subselect2 Fails in 5.2-opt tree

Bug #31464	Test subselect2 Fails in 5.2-opt tree
Submitted:	8 Oct 2007 19:30	Modified:	22 Nov 2010 1:12
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.2-opt bk	OS:	Any
Assigned to:	Sergey Petrunya	CPU Architecture:	Any
Tags:	semi-join, subquery

Description:
Test subselect2 fails on many, but not all machines in pushbuild. The failure is manifestation of some bug in subquery optimization.

How to repeat:
Look into pushbuild and see lots of failures like this: 

/dev/shm/var-ps_row-111/log/subselect2.reject	2007-10-04 18:14:28
@@ -123,14 +123,14 @@
 c373e9f5ad07993f3859444553544200	Last Discussion	c373e9f5ad079174ff17444553544200	c373e9f5ad0796c0eca4444553544200	Goldilocks	2003-06-09 11:21:06	Title: Last Discussion	NULL	Setting new abstract and keeping doc checked out	2003-06-09 10:51:26	2003-06-09 10:51:26	NULL	NULL	NULL	03eea05112b845949f3fd03278b5fe43	2003-06-09 11:21:06	admin	0	NULL	Discussion	NULL	NULL
 EXPLAIN SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t4	ALL	PRIMARY	NULL	NULL	NULL	10	
-1	PRIMARY	t2	ALL	DDOCTYPEID_IDX,DFOLDERID_IDX	NULL	NULL	NULL	9	Using where; Using join buffer
+1	PRIMARY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	const	2	Using index condition; Using where; Start temporary
+1	PRIMARY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	test.t3.FOLDERID	1	Using where
+1	PRIMARY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	test.t3.FOLDERID	1	Using where
+1	PRIMARY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	test.t3.FOLDERID	1	Using where
+1	PRIMARY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	test.t3.FOLDERID	1	Using where
+1	PRIMARY	t2	ALL	DDOCTYPEID_IDX,DFOLDERID_IDX	NULL	NULL	NULL	9	Using where; End temporary; Using join buffer
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCID	1	
-1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t2.FOLDERID	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where; FirstMatch(t3)
-1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where; FirstMatch(t3)
-1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where; FirstMatch(t3)
-1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where; FirstMatch(t3)
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCTYPEID	1	
 drop table t1, t2, t3, t4;
 CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
 INSERT INTO t1 VALUES (1),(2);

Suggested fix:
Find out why the plans could be different and either fix the bug or fix the testcase.

Investigation results: 
1. All of the subquery tables should have been pulled out with table pull-out strategy. However, this doesn't happen, neither on machines that pass the test, nor on the machines that fail it. Non-working table pull-out will be addressed separately as BUG#31563. 

2. However, even if table pull-out didn't work at all, the query's EXPLAIN should have been the same on all platforms. So far I can only tell the difference is in the cost calculations. Will continue to work to find out what exactly causes the difference.

Ok, now pullout works and correctly flattens all subqueries into inner joins.

We've also fixed several bugs dealing with uninitialized memory reads so I think the case can be closed for now.

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/commits/46277

ChangeSet@1.2633, 2008-05-02 02:41:35+04:00, sergefp@mysql.com +3 -0
  BUG#31464: Test subselect2 Fails in 5.2-opt tree
  - Update the test result with new QEP after BUG#31563 is fixed

Pushed into 6.0.6-alpha

Test case change. No changelog entry needed.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)