List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:May 14 2010 10:46am
Subject:bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3174 to 3176)
Bug#50237
View as plain text  
 3176 Tor Didriksen	2010-05-14
      Postfix for Bug #50237, isfinite failed to compile on solaris/sunstudio/debug
      
      Backport of tor.didriksen@stripped
     @ sql/handler.h
        isfinite/finite/HAVE_FINITE seems to be broken for solaris

    modified:
      sql/handler.h
 3175 Tor Didriksen	2010-05-14
      Bug #50237 Segfault in fix_semijoin_strategies_for_picked_join_order
      
      Backport of tor.didriksen@stripped
      
      Conflicts:
        subselect_sj.test and result files.
        because fix for Bug#49845 has already been backported.
      
      The problem was wrong cost calculation for semijoins.
      We sometimes ended up with NaN cost for IO, which may lead to
      choice of wrong plan, or (as in this case) no plan at all.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/handler.h
        Don't divide by zero.
        Dbug assert that cost is finite and != NaN
     @ sql/sql_select.cc
        Keep existing cost estimate unchanged if we have zero rows.

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/r/subselect_sj_jcl6.result
      mysql-test/t/subselect_sj.test
      sql/handler.h
      sql/sql_select.cc
 3174 Guilhem Bichot	2010-05-14
      Backporting of guilhem@stripped :
        Testcases for those "can't repeat" bugs:
        BUG#45928 "Differing query results depending on MRR and engine_condition_pushdown settings"
        BUG#46077 "wrong result: HAVING + ORDER BY + MyISAM + ICP returns extra rows"
        both fixed by epotemkin@stripped
        BUG#43578 "MyISAM&Maria gives wrong rows with range access ORDER BY DESC on date index"
        fixed by mattias.jonsson@stripped

    modified:
      mysql-test/r/func_time.result
      mysql-test/r/having.result
      mysql-test/r/subselect4.result
      mysql-test/t/func_time.test
      mysql-test/t/having.test
      mysql-test/t/subselect4.test
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2010-05-13 08:48:37 +0000
+++ b/mysql-test/r/subselect_sj.result	2010-05-14 08:57:28 +0000
@@ -211,6 +211,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	0	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -228,6 +236,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	0	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -245,6 +261,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	0	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -264,6 +288,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	1	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -281,6 +313,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	1	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -298,6 +338,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	1	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -317,6 +365,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	2	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -334,6 +390,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	2	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -351,6 +415,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	2	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -372,6 +444,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	0	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -389,6 +469,15 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	0	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -406,6 +495,15 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	0	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -425,6 +523,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	1	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -442,6 +548,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	1	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -459,6 +576,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	1	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -478,6 +606,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	2	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -495,6 +631,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	2	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -512,6 +659,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	2	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -533,6 +691,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	0	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -550,6 +716,15 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	0	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -567,6 +742,16 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	0	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+1
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -586,6 +771,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	1	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -603,6 +796,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	1	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -620,6 +824,19 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	1	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+1
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
+1
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -639,6 +856,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	2	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -656,6 +881,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	2	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -673,6 +909,20 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	2	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+1
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
+1
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2010-05-13 08:48:37 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2010-05-14 08:57:28 +0000
@@ -215,6 +215,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	0	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -232,6 +240,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	0	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -249,6 +265,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	0	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -268,6 +292,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	1	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -285,6 +317,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	1	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -302,6 +342,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	1	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -321,6 +369,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	2	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -338,6 +394,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	2	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -355,6 +419,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 0	2	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -376,6 +448,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	0	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -393,6 +473,15 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	0	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -410,6 +499,15 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	0	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -429,6 +527,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	1	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -446,6 +552,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	1	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -463,6 +580,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	1	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -482,6 +610,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	2	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -499,6 +635,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	2	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -516,6 +663,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 1	2	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -537,6 +695,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	0	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -554,6 +720,15 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	0	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -571,6 +746,16 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	0	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+1
+1
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -590,6 +775,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	1	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -607,6 +800,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	1	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -624,6 +828,19 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	1	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+1
+1
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
+1
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -643,6 +860,14 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	2	0
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -660,6 +885,17 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	2	1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -677,6 +913,20 @@ SELECT (SELECT COUNT(*) from t1),
 (SELECT COUNT(*) from t3);
 (SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
 2	2	2
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
+2
+1
+SELECT * FROM t1 WHERE (t1.i) IN 
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+2
+1
+SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+2
+1
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2010-05-13 08:48:37 +0000
+++ b/mysql-test/t/subselect_sj.test	2010-05-14 08:57:28 +0000
@@ -114,9 +114,16 @@ while ($i)
          SELECT (SELECT COUNT(*) from t1),
                 (SELECT COUNT(*) from t2),
                 (SELECT COUNT(*) from t3);
-#        TODO: This still crashes when t1 has two rows, t2 and t3 are empty.
-#        SELECT * FROM t1 WHERE (t1.i) IN 
-#          (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+
+#        TODO: The RIGHT join query here generates wrong result for rowcount
+#        (t1 t2 t3) == (2 0 2)   and    (t1 t2 t3) == (2 1 2)
+#        when we execute with join_cache_level=6 
+         SELECT * FROM t1 WHERE (t1.i) IN 
+           (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+         SELECT * FROM t1 WHERE (t1.i) IN 
+           (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+         SELECT * FROM t1 WHERE (t1.i) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+
          SELECT * FROM t1 WHERE (11) IN 
            (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
          SELECT * FROM t1 WHERE (11) IN 

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2010-04-22 14:55:40 +0000
+++ b/sql/handler.h	2010-05-14 09:08:24 +0000
@@ -22,6 +22,7 @@
 #pragma interface			/* gcc class implementation */
 #endif
 
+#include <my_global.h>
 #include <my_handler.h>
 #include <ft_global.h>
 #include <keycache.h>
@@ -1183,7 +1184,7 @@ public:
   enum { MEM_COEFF=1 };
   enum { IMPORT_COEFF=1 };
 
-  COST_VECT() {}                              // keep gcc happy
+  COST_VECT() { zero(); }                              // keep gcc happy
 
   double total_cost() 
   {
@@ -1215,8 +1216,10 @@ public:
   void add_io(double add_io_cnt, double add_avg_cost)
   {
     double io_count_sum= io_count + add_io_cnt;
-    avg_io_cost= (io_count * avg_io_cost + 
-                  add_io_cnt * add_avg_cost) / io_count_sum;
+    if (io_count_sum != 0.0)
+      avg_io_cost= (io_count * avg_io_cost + 
+                    add_io_cnt * add_avg_cost) / io_count_sum;
+    DBUG_ASSERT(!isnan(avg_io_cost));
     io_count= io_count_sum;
   }
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-05-13 16:40:00 +0000
+++ b/sql/sql_select.cc	2010-05-14 08:57:28 +0000
@@ -4713,7 +4713,8 @@ static bool optimize_semijoin_nests(JOIN
           consider doing sjm-scan):
         */ 
         sjm->scan_cost.zero();
-        sjm->scan_cost.add_io(sjm->rows, lookup_cost);
+        if (sjm->rows > 0.0)
+          sjm->scan_cost.add_io(sjm->rows, lookup_cost);
 
         sjm->lookup_cost.convert_from_cost(lookup_cost);
         sj_nest->sj_mat_info= sjm;


Attachment: [text/bzr-bundle] bzr/tor.didriksen@sun.com-20100514090824-d8s00hi2wmie24ta.bundle
Thread
bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3174 to 3176)Bug#50237Tor Didriksen14 May