MySQL Lists: commits: bzr commit into mysql-6.0-opt branch (sergefp:2809) Bug#37893

List:	Commits	« Previous Message Next Message »
From:	Sergey Petrunia	Date:	January 25 2009 5:52pm
Subject:	bzr commit into mysql-6.0-opt branch (sergefp:2809) Bug#37893
View as plain text
#At file:///home/spetrunia/dev/mysql-6.0-bug41842/ based on revid:sergefp@stripped

 2809 Sergey Petrunia	2009-01-25
      BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
      - Disable subquery-to-semi-join conversion when there is an outer join in the child
        or in the parent select
      - Testcases
added:
  mysql-test/r/subselect_sj_falcon.result
  mysql-test/t/subselect_sj_falcon.test
modified:
  mysql-test/r/subselect2.result
  mysql-test/r/subselect_sj.result
  mysql-test/r/subselect_sj2.result
  mysql-test/r/subselect_sj2_jcl6.result
  mysql-test/r/subselect_sj_jcl6.result
  mysql-test/t/subselect_sj.test
  mysql-test/t/subselect_sj2.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/subselect2.result
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  mysql-test/r/subselect_sj.result
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  mysql-test/r/subselect_sj2.result
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  mysql-test/r/subselect_sj2_jcl6.result
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  mysql-test/r/subselect_sj_falcon.result
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  mysql-test/r/subselect_sj_jcl6.result
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  mysql-test/t/subselect_sj.test
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  mysql-test/t/subselect_sj2.test
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  mysql-test/t/subselect_sj_falcon.test
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Testcases
  sql/sql_select.cc
    BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken
    - Disable subquery-to-semi-join conversion when there is an outer join in the child
      or in the parent select
=== modified file 'mysql-test/r/subselect2.result'
--- a/mysql-test/r/subselect2.result	2008-07-27 19:17:41 +0000
+++ b/mysql-test/r/subselect2.result	2009-01-25 17:51:54 +0000
@@ -126,13 +126,13 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t2	ALL	DDOCTYPEID_IDX	NULL	NULL	NULL	9	100.00	Using where
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCID	1	100.00	
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCTYPEID	1	100.00	
-2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	func	1	100.00	Using where
-2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
-2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
-2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
-2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
+2	SUBQUERY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	const	6	100.00	Using index condition; Using where
+2	SUBQUERY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	test.t3.FOLDERID	1	100.00	Using where
+2	SUBQUERY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	test.t3.FOLDERID	1	100.00	Using where
+2	SUBQUERY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	test.t3.FOLDERID	1	100.00	Using where
+2	SUBQUERY	t3	ref	CMFLDRPARNT_IDX	CMFLDRPARNT_IDX	35	test.t3.FOLDERID	1	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTY
 PEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,<exists>(select 1 AS `Not_used` from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` where ((`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and (<cache>(`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`)))))
+Note	1003	select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTY
 PEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,`test`.`t2`.`FOLDERID` in ( <materialize> (select `test`.`t3`.`FOLDERID` AS `FOLDERID` from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` where ((`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews')) ), <primary_index_lookup>(`test`.`t2`.`FOLDERID` in <temporary table> on distinct_key where ((`test`.`t2`.`FOLDERID` = `materialized subselect`.`FOLDERID`))))))
 drop table t1, t2, t3, t4;
 CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
 INSERT INTO t1 VALUES (1),(2);

=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2009-01-25 15:06:37 +0000
+++ b/mysql-test/r/subselect_sj.result	2009-01-25 17:51:54 +0000
@@ -1,4 +1,5 @@
 drop table if exists t0, t1, t2, t10, t11, t12;
+drop view if exists v1;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1(a int, b int);
@@ -73,18 +74,18 @@ id	select_type	tABle	type	possiBle_keys	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	t10	unique_suBquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
+2	SUBQUERY	t10	index	NULL	PRIMARY	4	NULL	10	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`B`.`A`) in t10 on PRIMARY))))) where 1
+Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,`test`.`B`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` AS `pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`B`.`A` in <temporAry tABle> on distinct_key where ((`test`.`B`.`A` = `mAteriAlized suBselect`.`pk`))))))) where 1
 t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
 explAin extended
 select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
 id	select_type	tABle	type	possiBle_keys	key	key_len	ref	rows	filtered	ExtrA
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-2	DEPENDENT SUBQUERY	t10	unique_suBquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
+2	SUBQUERY	t10	index	NULL	PRIMARY	4	NULL	10	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t2`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`t2`.`A`) in t10 on PRIMARY))))) where 1
+Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t2`.`A`,`test`.`t2`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` AS `pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t2`.`A` in <temporAry tABle> on distinct_key where ((`test`.`t2`.`A` = `mAteriAlized suBselect`.`pk`))))))) where 1
 we shouldn't flatten if we're going to get a join of > MAX_TABLES.
 explain select * from 
 t1 s00, t1 s01,  t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
@@ -339,3 +340,167 @@ X
 Warnings:
 Warning	1292	Incorrect datetime value: 'r' for column 'X' at row 1
 drop table t1, t2;
+#
+# BUG#37893: Crash in select_describe line 19251 on a FirstMatch 
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) DEFAULT NULL,
+int_key int(11) DEFAULT NULL,
+date_key date DEFAULT NULL,
+date_nokey date DEFAULT NULL,
+time_key time DEFAULT NULL,
+time_nokey time DEFAULT NULL,
+datetime_key datetime DEFAULT NULL,
+datetime_nokey datetime DEFAULT NULL,
+varchar_key varchar(5) DEFAULT NULL,
+varchar_nokey varchar(5) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t1 VALUES
+(1,NULL,NULL,NULL,NULL,'00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','dtrp','dtrp'),
+(2,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
+(3,NULL,NULL,'2009-09-14','2009-09-14',NULL,NULL,'2000-01-30 16:39:40','2000-01-30 16:39:40','qj','qj'),
+(4,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
+(5,7,7,NULL,NULL,NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00','{bq','{bq'),
+(6,8,8,'2000-01-14','2000-01-14','23:07:21','23:07:21',NULL,NULL,'xhn','xhn'),
+(7,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
+(8,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
+(9,NULL,NULL,'0000-00-00','0000-00-00','23:41:06','23:41:06','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(10,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) DEFAULT NULL,
+int_key int(11) DEFAULT NULL,
+date_key date DEFAULT NULL,
+date_nokey date DEFAULT NULL,
+time_key time DEFAULT NULL,
+time_nokey time DEFAULT NULL,
+datetime_key datetime DEFAULT NULL,
+datetime_nokey datetime DEFAULT NULL,
+varchar_key varchar(5) DEFAULT NULL,
+varchar_nokey varchar(5) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+EXPLAIN
+SELECT *
+FROM t1 AS OUTR
+WHERE '2006-2-22 1:52:21' IN (
+SELECT INNR . time_nokey AS Y
+FROM t1 AS INNR2
+LEFT JOIN t2 AS INNR
+ON ( INNR2 . pk <= INNR . int_key )
+)
+AND OUTR . int_key > 218 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+drop table t1, t2;
+#
+# BUG#42259: SELECT WHERE IN performs poorly (slow) with views and functions
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, b int);
+insert into t1 values (0,0),(1,1),(2,2);
+create table t2 as select * from t1;
+create view v1 as select t0.a as x from t0 join t2 on t0.a = t2.a where t2.b<5;
+This must be flattened:
+explain select * from v1 where x in (select a from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Materialize; Scan
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer
+This must be flattened:
+explain select * from t2 where a in (select x from v1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Start materialize
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize; Using join buffer
+drop table t0, t1, t2;
+drop view v1;
+#
+# BUG#38075: Wrong result: rows matching a subquery with outer join not returned
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t1 VALUES 
+(1,5,5,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00','',''),
+(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26','',''),
+(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),
+(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44','',''),
+(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
+(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t2 VALUES 
+(1,5,5,'0000-00-00','0000-00-00','22:51:38','22:51:38','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'),
+(2,1,1,'2007-08-24','2007-08-24','07:26:48','07:26:48','2000-11-04 00:00:00','2000-11-04 00:00:00','{','{'),
+(3,1,1,'2009-04-12','2009-04-12','08:19:28','08:19:28','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),
+(4,8,8,'2009-12-04','2009-12-04','00:00:00','00:00:00','2004-02-24 17:40:14','2004-02-24 17:40:14','x','x'),
+(5,7,7,'2000-09-17','2000-09-17','15:23:34','15:23:34','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),
+(6,3,3,'0000-00-00','0000-00-00','08:52:49','08:52:49','2008-06-29 15:03:33','2008-06-29 15:03:33','p','p'),
+(7,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2005-04-06 00:00:00','2005-04-06 00:00:00','c','c'),
+(8,0,0,'0000-00-00','0000-00-00','15:10:28','15:10:28','2006-05-01 16:00:17','2006-05-01 16:00:17','k','k'),
+(9,6,6,'0000-00-00','0000-00-00','08:05:45','08:05:45','2002-09-17 00:00:00','2002-09-17 00:00:00','t','t'),
+(10,0,0,'0000-00-00','0000-00-00','19:46:39','19:46:39','0000-00-00 00:00:00','0000-00-00 00:00:00','c','c');
+SELECT OUTR . pk AS X, OUTR . varchar_nokey
+FROM t1 AS OUTR
+WHERE OUTR . varchar_nokey IN (
+SELECT  INNR . varchar_nokey AS X 
+FROM t2 AS INNR2 LEFT JOIN
+t2 AS INNR ON ( INNR2 . int_nokey = INNR . pk )
+);
+X	varchar_nokey
+2	p
+7	p
+10	z
+drop table t1, t2;

=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result	2008-12-22 19:03:25 +0000
+++ b/mysql-test/r/subselect_sj2.result	2009-01-25 17:51:54 +0000
@@ -261,10 +261,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer
-1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	t1	index	NULL	a	5	NULL	10	Using index
+2	SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
+2	SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 drop table t0, t1,t2,t3;
 CREATE TABLE t1 (
 ID int(11) NOT NULL auto_increment,
@@ -559,7 +559,7 @@ select * from t1 left join t2 on (t2.a= 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+2	SUBQUERY	t3	index	NULL	PRIMARY	4	NULL	10	Using index
 drop table t0, t1, t2, t3;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -677,3 +677,119 @@ select count(a) from t2 where a in ( SEL
 count(a)
 1000
 drop table t0,t1,t2,t3;
+#
+#  BUG38010: Wrong result returned when outer join, semijoin and "use index"
+# 
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) DEFAULT NULL,
+varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,5,5,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-04-12 00:00:00','2009-04-12 00:00:00','o','o');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) DEFAULT NULL,
+varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,6,6,'0000-00-00','0000-00-00','11:01:28','11:01:28','2007-08-09 06:23:37','2007-08-09 06:23:37','c','c');
+CREATE TABLE C (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) DEFAULT NULL,
+varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO C VALUES 
+(1,5,5,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26',NULL,NULL),
+(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),
+(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44',NULL,NULL),
+(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
+(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z');
+CREATE TABLE t3 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) DEFAULT NULL,
+varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES 
+(1,3,3,'2005-07-18','2005-07-18','15:58:39','15:58:39','2004-03-27 03:22:05','2004-03-27 03:22:05',NULL,NULL),
+(2,7,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2008-02-28 20:02:09','2008-02-28 20:02:09','x','x'),
+(3,0,0,'2004-07-05','2004-07-05','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','u','u'),
+(4,9,9,'2007-09-06','2007-09-06','12:29:03','12:29:03','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(5,7,7,'2005-01-24','2005-01-24','12:37:03','12:37:03','2006-07-26 00:00:00','2006-07-26 00:00:00','{','{'),
+(6,5,5,'2003-03-07','2003-03-07','04:13:56','04:13:56','2007-06-15 23:13:40','2007-06-15 23:13:40','i','i'),
+(7,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(8,2,2,'2004-08-28','2004-08-28','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','i','i'),
+(9,4,4,'0000-00-00','0000-00-00','22:27:41','22:27:41','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),
+(10,2,2,'2001-06-22','2001-06-22','15:27:12','15:27:12','2001-08-25 20:44:07','2001-08-25 20:44:07','t','t');
+SELECT  OUTR . int_key
+FROM t1 AS OUTR2
+LEFT JOIN C AS OUTR
+ON ( OUTR2 . varchar_nokey > OUTR . varchar_key )
+WHERE OUTR . pk IN (
+SELECT  INNR . int_key AS Y
+FROM t2 AS INNR2
+LEFT JOIN t3 AS INNR
+ON ( INNR2 . date_nokey >= INNR . date_key )
+);
+int_key
+6
+drop table t1,t2,t3;

=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result	2009-01-14 10:29:36 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result	2009-01-25 17:51:54 +0000
@@ -265,10 +265,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer
-1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	t1	index	NULL	a	5	NULL	10	Using index
+2	SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
+2	SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 drop table t0, t1,t2,t3;
 CREATE TABLE t1 (
 ID int(11) NOT NULL auto_increment,
@@ -563,7 +563,7 @@ select * from t1 left join t2 on (t2.a= 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer
-2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+2	SUBQUERY	t3	index	NULL	PRIMARY	4	NULL	10	Using index
 drop table t0, t1, t2, t3;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

=== added file 'mysql-test/r/subselect_sj_falcon.result'
--- a/mysql-test/r/subselect_sj_falcon.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/subselect_sj_falcon.result	2009-01-25 17:51:54 +0000
@@ -0,0 +1,132 @@
+drop table if exists t1, t2;
+#
+# BUG#38201: Wrong result when left join + subquery + semijoin
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=Falcon;
+INSERT INTO t1 VALUES 
+(1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(2,0,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','d','d');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=Falcon;
+INSERT INTO t2 VALUES 
+(1,1,6,'2005-12-23','2005-12-23','02:24:28','02:24:28','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'),
+(2,0,3,'2009-09-14','2009-09-14','00:00:00','00:00:00','2000-01-30 16:39:40','2000-01-30 16:39:40','q','q'),
+(3,0,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','c','c'),
+(4,1,6,'2007-03-29','2007-03-29','15:49:00','15:49:00','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),
+(5,4,0,'2002-12-04','2002-12-04','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),
+(6,9,0,'2005-01-28','2005-01-28','00:00:00','00:00:00','2001-05-18 00:00:00','2001-05-18 00:00:00','w','w'),
+(7,6,0,'0000-00-00','0000-00-00','06:57:25','06:57:25','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),
+(8,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),
+(9,4,6,'2006-08-15','2006-08-15','00:00:00','00:00:00','2002-04-12 14:44:25','2002-04-12 14:44:25','j','j'),
+(10,0,5,'2006-12-20','2006-12-20','10:13:53','10:13:53','2008-07-22 00:00:00','2008-07-22 00:00:00','y','y'),
+(11,9,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2004-07-05 00:00:00','2004-07-05 00:00:00','{','{'),
+(12,4,3,'2007-01-26','2007-01-26','23:00:51','23:00:51','2001-05-16 00:00:00','2001-05-16 00:00:00','f','f'),
+(13,7,0,'2004-03-27','2004-03-27','00:00:00','00:00:00','2005-01-24 03:30:37','2005-01-24 03:30:37','',''),
+(14,6,0,'2006-07-26','2006-07-26','18:43:57','18:43:57','0000-00-00 00:00:00','0000-00-00 00:00:00','{','{'),
+(15,0,6,'2000-01-14','2000-01-14','00:00:00','00:00:00','2000-09-21 00:00:00','2000-09-21 00:00:00','o','o'),
+(16,9,8,'0000-00-00','0000-00-00','21:15:08','21:15:08','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),
+(17,2,0,'2004-10-27','2004-10-27','00:00:00','00:00:00','2004-03-24 22:13:43','2004-03-24 22:13:43','',''),
+(18,7,4,'0000-00-00','0000-00-00','08:38:27','08:38:27','2002-03-18 19:51:44','2002-03-18 19:51:44','t','t'),
+(19,5,3,'2008-03-07','2008-03-07','03:29:07','03:29:07','2007-12-01 18:44:44','2007-12-01 18:44:44','t','t'),
+(20,0,0,'2002-04-09','2002-04-09','16:06:03','16:06:03','2009-04-22 00:00:00','2009-04-22 00:00:00','n','n');
+CREATE TABLE t3 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+date_key date NOT NULL,
+date_nokey date NOT NULL,
+time_key time NOT NULL,
+time_nokey time NOT NULL,
+datetime_key datetime NOT NULL,
+datetime_nokey datetime NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+) ENGINE=Falcon;
+INSERT INTO t3 VALUES
+(10,4,2,'0000-00-00','0000-00-00','00:47:47','00:47:47','2006-03-18 00:00:00','2006-03-18 00:00:00','',''),
+(11,1,0,'2004-04-22','2004-04-22','00:00:00','00:00:00','2009-09-20 18:52:05','2009-09-20 18:52:05','y','y'),
+(12,7,0,'0000-00-00','0000-00-00','22:24:06','22:24:06','0000-00-00 00:00:00','0000-00-00 00:00:00','y','y'),
+(13,4,0,'2003-03-15','2003-03-15','16:38:08','16:38:08','2001-02-15 10:17:29','2001-02-15 10:17:29','w','w'),
+(14,9,8,'2003-11-26','2003-11-26','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','n','n'),
+(15,4,0,'2005-06-11','2005-06-11','00:00:00','00:00:00','2003-08-14 18:35:30','2003-08-14 18:35:30','a','a'),
+(16,0,0,'0000-00-00','0000-00-00','15:47:46','15:47:46','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(17,9,0,'0000-00-00','0000-00-00','15:49:08','15:49:08','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(18,0,0,'2003-05-06','2003-05-06','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','t','t'),
+(19,4,1,'2003-05-18','2003-05-18','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','q','q'),
+(20,8,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2003-04-18 21:56:02','2003-04-18 21:56:02','o','o'),
+(21,9,4,'0000-00-00','0000-00-00','04:02:24','04:02:24','2009-12-24 00:00:00','2009-12-24 00:00:00','v','v'),
+(22,2,0,'2009-02-20','2009-02-20','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','',''),
+(23,6,3,'2005-11-13','2005-11-13','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'),
+(24,9,0,'2009-10-25','2009-10-25','14:35:13','14:35:13','2000-01-13 09:40:25','2000-01-13 09:40:25','g','g'),
+(25,1,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
+(26,4,1,'2007-07-28','2007-07-28','00:00:00','00:00:00','2000-03-10 00:30:49','2000-03-10 00:30:49','z','z'),
+(27,3,0,'2003-08-01','2003-08-01','20:07:09','20:07:09','2002-11-08 12:53:36','2002-11-08 12:53:36','',''),
+(28,1,9,'0000-00-00','0000-00-00','21:11:55','21:11:55','2002-03-02 04:50:28','2002-03-02 04:50:28','i','i'),
+(29,3,4,'2000-07-15','2000-07-15','19:35:49','19:35:49','2000-12-19 15:50:00','2000-12-19 15:50:00','','');
+SELECT OUTR . varchar_nokey
+FROM t2 AS OUTR2
+LEFT JOIN t1 AS OUTR
+ON ( OUTR2 . varchar_nokey < OUTR . varchar_nokey )
+WHERE OUTR . varchar_nokey IN (
+SELECT INNR . varchar_key AS Y FROM t3 AS INNR
+WHERE INNR . pk >= 6
+);
+varchar_nokey
+p
+p
+d
+p
+p
+p
+p
+p
+p
+d
+p
+p
+d
+p
+d
+p
+drop table t1,t2,t3;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2009-01-25 15:06:37 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2009-01-25 17:51:54 +0000
@@ -3,6 +3,7 @@ show variables like 'join_cache_level';
 Variable_name	Value
 join_cache_level	6
 drop table if exists t0, t1, t2, t10, t11, t12;
+drop view if exists v1;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1(a int, b int);
@@ -77,18 +78,18 @@ id	select_type	tABle	type	possiBle_keys	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join Buffer
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join Buffer
-2	DEPENDENT SUBQUERY	t10	unique_suBquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
+2	SUBQUERY	t10	index	NULL	PRIMARY	4	NULL	10	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`B`.`A`) in t10 on PRIMARY))))) where 1
+Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,`test`.`B`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` AS `pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`B`.`A` in <temporAry tABle> on distinct_key where ((`test`.`B`.`A` = `mAteriAlized suBselect`.`pk`))))))) where 1
 t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
 explAin extended
 select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
 id	select_type	tABle	type	possiBle_keys	key	key_len	ref	rows	filtered	ExtrA
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join Buffer
-2	DEPENDENT SUBQUERY	t10	unique_suBquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index
+2	SUBQUERY	t10	index	NULL	PRIMARY	4	NULL	10	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t2`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`t2`.`A`) in t10 on PRIMARY))))) where 1
+Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t2`.`A`,`test`.`t2`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` AS `pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t2`.`A` in <temporAry tABle> on distinct_key where ((`test`.`t2`.`A` = `mAteriAlized suBselect`.`pk`))))))) where 1
 we shouldn't flatten if we're going to get a join of > MAX_TABLES.
 explain select * from 
 t1 s00, t1 s01,  t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
@@ -343,6 +344,95 @@ X
 Warnings:
 Warning	1292	Incorrect datetime value: 'r' for column 'X' at row 1
 drop table t1, t2;
+#
+# BUG#37893: Crash in select_describe line 19251 on a FirstMatch 
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) DEFAULT NULL,
+int_key int(11) DEFAULT NULL,
+date_key date DEFAULT NULL,
+date_nokey date DEFAULT NULL,
+time_key time DEFAULT NULL,
+time_nokey time DEFAULT NULL,
+datetime_key datetime DEFAULT NULL,
+datetime_nokey datetime DEFAULT NULL,
+varchar_key varchar(5) DEFAULT NULL,
+varchar_nokey varchar(5) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t1 VALUES
+(1,NULL,NULL,NULL,NULL,'00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','dtrp','dtrp'),
+(2,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
+(3,NULL,NULL,'2009-09-14','2009-09-14',NULL,NULL,'2000-01-30 16:39:40','2000-01-30 16:39:40','qj','qj'),
+(4,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
+(5,7,7,NULL,NULL,NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00','{bq','{bq'),
+(6,8,8,'2000-01-14','2000-01-14','23:07:21','23:07:21',NULL,NULL,'xhn','xhn'),
+(7,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
+(8,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
+(9,NULL,NULL,'0000-00-00','0000-00-00','23:41:06','23:41:06','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(10,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) DEFAULT NULL,
+int_key int(11) DEFAULT NULL,
+date_key date DEFAULT NULL,
+date_nokey date DEFAULT NULL,
+time_key time DEFAULT NULL,
+time_nokey time DEFAULT NULL,
+datetime_key datetime DEFAULT NULL,
+datetime_nokey datetime DEFAULT NULL,
+varchar_key varchar(5) DEFAULT NULL,
+varchar_nokey varchar(5) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY date_key (date_key),
+KEY time_key (time_key),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+EXPLAIN
+SELECT *
+FROM t1 AS OUTR
+WHERE '2006-2-22 1:52:21' IN (
+SELECT INNR . time_nokey AS Y
+FROM t1 AS INNR2
+LEFT JOIN t2 AS INNR
+ON ( INNR2 . pk <= INNR . int_key )
+)
+AND OUTR . int_key > 218 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+drop table t1, t2;
+#
+# BUG#42259: SELECT WHERE IN performs poorly (slow) with views and functions
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, b int);
+insert into t1 values (0,0),(1,1),(2,2);
+create table t2 as select * from t1;
+create view v1 as select t0.a as x from t0 join t2 on t0.a = t2.a where t2.b<5;
+This must be flattened:
+explain select * from v1 where x in (select a from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Materialize; Scan
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer
+This must be flattened:
+explain select * from t2 where a in (select x from v1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Start materialize
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize; Using join buffer
+drop table t0, t1, t2;
+drop view v1;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2009-01-25 15:06:37 +0000
+++ b/mysql-test/t/subselect_sj.test	2009-01-25 17:51:54 +0000
@@ -3,6 +3,7 @@
 #
 --disable_warnings
 drop table if exists t0, t1, t2, t10, t11, t12;
+drop view if exists v1;
 --enable_warnings
 
 #
@@ -228,3 +229,164 @@ HAVING X > 'r';
 drop table t1, t2;
 
 
+--echo #
+--echo # BUG#37893: Crash in select_describe line 19251 on a FirstMatch 
+--echo #
+
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) DEFAULT NULL,
+  int_key int(11) DEFAULT NULL,
+  date_key date DEFAULT NULL,
+  date_nokey date DEFAULT NULL,
+  time_key time DEFAULT NULL,
+  time_nokey time DEFAULT NULL,
+  datetime_key datetime DEFAULT NULL,
+  datetime_nokey datetime DEFAULT NULL,
+  varchar_key varchar(5) DEFAULT NULL,
+  varchar_nokey varchar(5) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+);
+
+INSERT INTO t1 VALUES
+(1,NULL,NULL,NULL,NULL,'00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','dtrp','dtrp'),
+(2,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
+(3,NULL,NULL,'2009-09-14','2009-09-14',NULL,NULL,'2000-01-30 16:39:40','2000-01-30 16:39:40','qj','qj'),
+(4,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
+(5,7,7,NULL,NULL,NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00','{bq','{bq'),
+(6,8,8,'2000-01-14','2000-01-14','23:07:21','23:07:21',NULL,NULL,'xhn','xhn'),
+(7,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
+(8,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
+(9,NULL,NULL,'0000-00-00','0000-00-00','23:41:06','23:41:06','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(10,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');
+
+CREATE TABLE t2 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) DEFAULT NULL,
+  int_key int(11) DEFAULT NULL,
+  date_key date DEFAULT NULL,
+  date_nokey date DEFAULT NULL,
+  time_key time DEFAULT NULL,
+  time_nokey time DEFAULT NULL,
+  datetime_key datetime DEFAULT NULL,
+  datetime_nokey datetime DEFAULT NULL,
+  varchar_key varchar(5) DEFAULT NULL,
+  varchar_nokey varchar(5) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+);
+
+EXPLAIN
+SELECT *
+FROM t1 AS OUTR
+WHERE '2006-2-22 1:52:21' IN (
+ SELECT INNR . time_nokey AS Y
+ FROM t1 AS INNR2
+ LEFT JOIN t2 AS INNR
+ ON ( INNR2 . pk <= INNR . int_key )
+)
+AND OUTR . int_key > 218 ;
+drop table t1, t2;
+
+--echo #
+--echo # BUG#42259: SELECT WHERE IN performs poorly (slow) with views and functions
+--echo #
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, b int);
+insert into t1 values (0,0),(1,1),(2,2);
+create table t2 as select * from t1;
+
+create view v1 as select t0.a as x from t0 join t2 on t0.a = t2.a where t2.b<5;
+--echo This must be flattened:
+explain select * from v1 where x in (select a from t2);
+--echo This must be flattened:
+explain select * from t2 where a in (select x from v1);
+drop table t0, t1, t2;
+drop view v1;
+
+
+--echo #
+--echo # BUG#38075: Wrong result: rows matching a subquery with outer join not returned
+--echo #
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) NOT NULL,
+  varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+);
+
+INSERT INTO t1 VALUES 
+(1,5,5,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00','',''),
+(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26','',''),
+(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),
+(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44','',''),
+(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
+(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z');
+
+CREATE TABLE t2 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) NOT NULL,
+  varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+);
+
+INSERT INTO t2 VALUES 
+(1,5,5,'0000-00-00','0000-00-00','22:51:38','22:51:38','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'),
+(2,1,1,'2007-08-24','2007-08-24','07:26:48','07:26:48','2000-11-04 00:00:00','2000-11-04 00:00:00','{','{'),
+(3,1,1,'2009-04-12','2009-04-12','08:19:28','08:19:28','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),
+(4,8,8,'2009-12-04','2009-12-04','00:00:00','00:00:00','2004-02-24 17:40:14','2004-02-24 17:40:14','x','x'),
+(5,7,7,'2000-09-17','2000-09-17','15:23:34','15:23:34','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),
+(6,3,3,'0000-00-00','0000-00-00','08:52:49','08:52:49','2008-06-29 15:03:33','2008-06-29 15:03:33','p','p'),
+(7,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2005-04-06 00:00:00','2005-04-06 00:00:00','c','c'),
+(8,0,0,'0000-00-00','0000-00-00','15:10:28','15:10:28','2006-05-01 16:00:17','2006-05-01 16:00:17','k','k'),
+(9,6,6,'0000-00-00','0000-00-00','08:05:45','08:05:45','2002-09-17 00:00:00','2002-09-17 00:00:00','t','t'),
+(10,0,0,'0000-00-00','0000-00-00','19:46:39','19:46:39','0000-00-00 00:00:00','0000-00-00 00:00:00','c','c');
+
+SELECT OUTR . pk AS X, OUTR . varchar_nokey
+FROM t1 AS OUTR
+WHERE OUTR . varchar_nokey IN (
+ SELECT  INNR . varchar_nokey AS X 
+ FROM t2 AS INNR2 LEFT JOIN
+ t2 AS INNR ON ( INNR2 . int_nokey = INNR . pk )
+);
+drop table t1, t2;

=== modified file 'mysql-test/t/subselect_sj2.test'
--- a/mysql-test/t/subselect_sj2.test	2008-07-10 16:02:38 +0000
+++ b/mysql-test/t/subselect_sj2.test	2009-01-25 17:51:54 +0000
@@ -859,3 +859,129 @@ explain select count(a) from t2 where a 
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 
 drop table t0,t1,t2,t3;
+
+--echo #
+--echo #  BUG38010: Wrong result returned when outer join, semijoin and "use index"
+--echo # 
+
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) DEFAULT NULL,
+  varchar_nokey varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1,5,5,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-04-12 00:00:00','2009-04-12 00:00:00','o','o');
+
+CREATE TABLE t2 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) DEFAULT NULL,
+  varchar_nokey varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (1,6,6,'0000-00-00','0000-00-00','11:01:28','11:01:28','2007-08-09 06:23:37','2007-08-09 06:23:37','c','c');
+
+CREATE TABLE C (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) DEFAULT NULL,
+  varchar_nokey varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO C VALUES 
+(1,5,5,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26',NULL,NULL),
+(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),
+(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44',NULL,NULL),
+(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
+(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z');
+
+CREATE TABLE t3 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) DEFAULT NULL,
+  varchar_nokey varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t3 VALUES 
+(1,3,3,'2005-07-18','2005-07-18','15:58:39','15:58:39','2004-03-27 03:22:05','2004-03-27 03:22:05',NULL,NULL),
+(2,7,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2008-02-28 20:02:09','2008-02-28 20:02:09','x','x'),
+(3,0,0,'2004-07-05','2004-07-05','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','u','u'),
+(4,9,9,'2007-09-06','2007-09-06','12:29:03','12:29:03','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(5,7,7,'2005-01-24','2005-01-24','12:37:03','12:37:03','2006-07-26 00:00:00','2006-07-26 00:00:00','{','{'),
+(6,5,5,'2003-03-07','2003-03-07','04:13:56','04:13:56','2007-06-15 23:13:40','2007-06-15 23:13:40','i','i'),
+(7,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
+(8,2,2,'2004-08-28','2004-08-28','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','i','i'),
+(9,4,4,'0000-00-00','0000-00-00','22:27:41','22:27:41','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),
+(10,2,2,'2001-06-22','2001-06-22','15:27:12','15:27:12','2001-08-25 20:44:07','2001-08-25 20:44:07','t','t');
+
+SELECT  OUTR . int_key
+FROM t1 AS OUTR2
+LEFT JOIN C AS OUTR
+ON ( OUTR2 . varchar_nokey > OUTR . varchar_key )
+WHERE OUTR . pk IN (
+ SELECT  INNR . int_key AS Y
+ FROM t2 AS INNR2
+ LEFT JOIN t3 AS INNR
+ ON ( INNR2 . date_nokey >= INNR . date_key )
+);
+
+drop table t1,t2,t3;
+

=== added file 'mysql-test/t/subselect_sj_falcon.test'
--- a/mysql-test/t/subselect_sj_falcon.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/subselect_sj_falcon.test	2009-01-25 17:51:54 +0000
@@ -0,0 +1,129 @@
+--source include/have_falcon.inc
+#
+# Semi-join subquery tests only repeatable with Falcon
+#
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+
+--echo #
+--echo # BUG#38201: Wrong result when left join + subquery + semijoin
+--echo #
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) NOT NULL,
+  varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+) ENGINE=Falcon;
+INSERT INTO t1 VALUES 
+(1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(2,0,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','d','d');
+
+CREATE TABLE t2 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) NOT NULL,
+  varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+) ENGINE=Falcon;
+
+INSERT INTO t2 VALUES 
+(1,1,6,'2005-12-23','2005-12-23','02:24:28','02:24:28','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'),
+(2,0,3,'2009-09-14','2009-09-14','00:00:00','00:00:00','2000-01-30 16:39:40','2000-01-30 16:39:40','q','q'),
+(3,0,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','c','c'),
+(4,1,6,'2007-03-29','2007-03-29','15:49:00','15:49:00','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),
+(5,4,0,'2002-12-04','2002-12-04','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),
+(6,9,0,'2005-01-28','2005-01-28','00:00:00','00:00:00','2001-05-18 00:00:00','2001-05-18 00:00:00','w','w'),
+(7,6,0,'0000-00-00','0000-00-00','06:57:25','06:57:25','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),
+(8,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),
+(9,4,6,'2006-08-15','2006-08-15','00:00:00','00:00:00','2002-04-12 14:44:25','2002-04-12 14:44:25','j','j'),
+(10,0,5,'2006-12-20','2006-12-20','10:13:53','10:13:53','2008-07-22 00:00:00','2008-07-22 00:00:00','y','y'),
+(11,9,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2004-07-05 00:00:00','2004-07-05 00:00:00','{','{'),
+(12,4,3,'2007-01-26','2007-01-26','23:00:51','23:00:51','2001-05-16 00:00:00','2001-05-16 00:00:00','f','f'),
+(13,7,0,'2004-03-27','2004-03-27','00:00:00','00:00:00','2005-01-24 03:30:37','2005-01-24 03:30:37','',''),
+(14,6,0,'2006-07-26','2006-07-26','18:43:57','18:43:57','0000-00-00 00:00:00','0000-00-00 00:00:00','{','{'),
+(15,0,6,'2000-01-14','2000-01-14','00:00:00','00:00:00','2000-09-21 00:00:00','2000-09-21 00:00:00','o','o'),
+(16,9,8,'0000-00-00','0000-00-00','21:15:08','21:15:08','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),
+(17,2,0,'2004-10-27','2004-10-27','00:00:00','00:00:00','2004-03-24 22:13:43','2004-03-24 22:13:43','',''),
+(18,7,4,'0000-00-00','0000-00-00','08:38:27','08:38:27','2002-03-18 19:51:44','2002-03-18 19:51:44','t','t'),
+(19,5,3,'2008-03-07','2008-03-07','03:29:07','03:29:07','2007-12-01 18:44:44','2007-12-01 18:44:44','t','t'),
+(20,0,0,'2002-04-09','2002-04-09','16:06:03','16:06:03','2009-04-22 00:00:00','2009-04-22 00:00:00','n','n');
+
+CREATE TABLE t3 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  date_key date NOT NULL,
+  date_nokey date NOT NULL,
+  time_key time NOT NULL,
+  time_nokey time NOT NULL,
+  datetime_key datetime NOT NULL,
+  datetime_nokey datetime NOT NULL,
+  varchar_key varchar(1) NOT NULL,
+  varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY date_key (date_key),
+  KEY time_key (time_key),
+  KEY datetime_key (datetime_key),
+  KEY varchar_key (varchar_key)
+) ENGINE=Falcon;
+
+INSERT INTO t3 VALUES
+(10,4,2,'0000-00-00','0000-00-00','00:47:47','00:47:47','2006-03-18 00:00:00','2006-03-18 00:00:00','',''),
+(11,1,0,'2004-04-22','2004-04-22','00:00:00','00:00:00','2009-09-20 18:52:05','2009-09-20 18:52:05','y','y'),
+(12,7,0,'0000-00-00','0000-00-00','22:24:06','22:24:06','0000-00-00 00:00:00','0000-00-00 00:00:00','y','y'),
+(13,4,0,'2003-03-15','2003-03-15','16:38:08','16:38:08','2001-02-15 10:17:29','2001-02-15 10:17:29','w','w'),
+(14,9,8,'2003-11-26','2003-11-26','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','n','n'),
+(15,4,0,'2005-06-11','2005-06-11','00:00:00','00:00:00','2003-08-14 18:35:30','2003-08-14 18:35:30','a','a'),
+(16,0,0,'0000-00-00','0000-00-00','15:47:46','15:47:46','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
+(17,9,0,'0000-00-00','0000-00-00','15:49:08','15:49:08','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
+(18,0,0,'2003-05-06','2003-05-06','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','t','t'),
+(19,4,1,'2003-05-18','2003-05-18','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','q','q'),
+(20,8,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2003-04-18 21:56:02','2003-04-18 21:56:02','o','o'),
+(21,9,4,'0000-00-00','0000-00-00','04:02:24','04:02:24','2009-12-24 00:00:00','2009-12-24 00:00:00','v','v'),
+(22,2,0,'2009-02-20','2009-02-20','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','',''),
+(23,6,3,'2005-11-13','2005-11-13','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'),
+(24,9,0,'2009-10-25','2009-10-25','14:35:13','14:35:13','2000-01-13 09:40:25','2000-01-13 09:40:25','g','g'),
+(25,1,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
+(26,4,1,'2007-07-28','2007-07-28','00:00:00','00:00:00','2000-03-10 00:30:49','2000-03-10 00:30:49','z','z'),
+(27,3,0,'2003-08-01','2003-08-01','20:07:09','20:07:09','2002-11-08 12:53:36','2002-11-08 12:53:36','',''),
+(28,1,9,'0000-00-00','0000-00-00','21:11:55','21:11:55','2002-03-02 04:50:28','2002-03-02 04:50:28','i','i'),
+(29,3,4,'2000-07-15','2000-07-15','19:35:49','19:35:49','2000-12-19 15:50:00','2000-12-19 15:50:00','','');
+
+SELECT OUTR . varchar_nokey
+FROM t2 AS OUTR2
+LEFT JOIN t1 AS OUTR
+ON ( OUTR2 . varchar_nokey < OUTR . varchar_nokey )
+WHERE OUTR . varchar_nokey IN (
+ SELECT INNR . varchar_key AS Y FROM t3 AS INNR
+ WHERE INNR . pk >= 6
+);
+
+drop table t1,t2,t3;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-01-25 15:06:37 +0000
+++ b/sql/sql_select.cc	2009-01-25 17:51:54 +0000
@@ -476,6 +476,26 @@ inline int setup_without_group(THD *thd,
   DBUG_RETURN(res);
 }
 
+
+/*
+  Check if select has outer joins. This is expected to be run before
+  simplify_joins() was called.
+*/
+bool select_has_outer_joins(SELECT_LEX *sel)
+{
+  for (TABLE_LIST *leaf= sel->leaf_tables; leaf; leaf= leaf->next_leaf)
+  {
+    if (leaf->outer_join & (JOIN_TYPE_LEFT | JOIN_TYPE_RIGHT))
+      return TRUE;
+    for (TABLE_LIST *emb= leaf->embedding; emb; emb= emb->embedding)
+    {
+      if (emb->outer_join & (JOIN_TYPE_LEFT | JOIN_TYPE_RIGHT))
+        return TRUE;
+    }
+  }
+  return FALSE;
+}
+
 /*****************************************************************************
   Check fields, find best join, do the select and output fields.
   mysql_select assumes that all tables are already opened
@@ -618,6 +638,8 @@ JOIN::prepare(Item ***rref_pointer_array
         select_lex->master_unit()->first_select()->leaf_tables &&     // 7
         in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED && // 8
         select_lex->outer_select()->leaf_tables &&                    // 9
+        !select_has_outer_joins(select_lex) && 
+        !select_has_outer_joins(select_lex->outer_select()) && 
         !((select_options | select_lex->outer_select()->join->select_options)
           & SELECT_STRAIGHT_JOIN))                                    // 10
     {
@@ -3494,20 +3516,6 @@ bool JOIN::flatten_subqueries()
       (*in_subq)->is_correlated * MAX_TABLES + child_join->outer_tables;
   }
   
-  // Temporary measure: disable semi-joins when they are together with outer
-  // joins.
-  for (TABLE_LIST *tbl= select_lex->leaf_tables; tbl; tbl=tbl->next_leaf)
-  {
-    TABLE_LIST *embedding= tbl->embedding;
-    if (tbl->on_expr || (tbl->embedding && !(embedding->sj_on_expr && 
-                                            !embedding->embedding)))
-    {
-      in_subq= sj_subselects.front();
-      arena= thd->activate_stmt_arena_if_needed(&backup);
-      goto skip_conversion;
-    }
-  }
-
   //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables);
   /* 
     2. Pick which subqueries to convert:
@@ -3539,7 +3547,7 @@ bool JOIN::flatten_subqueries()
     if (convert_subq_to_sj(this, *in_subq))
       DBUG_RETURN(TRUE);
   }
-skip_conversion:
+
   /* 3. Finalize those we didn't convert */
   for (; in_subq!= in_subq_end; in_subq++)
   {
@@ -9691,7 +9699,6 @@ bool setup_sj_materialization(JOIN_TAB *
       bool dummy;
       Item_equal *item_eq;
       Field *copy_to=((Item_field*)it++)->field; 
-      Item *head;
       /*
         Tricks with Item_equal are due to the following: suppose we have a
         query:
Thread
• bzr commit into mysql-6.0-opt branch (sergefp:2809) Bug#37893	Sergey Petrunia	25 Jan