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#50237 | Tor Didriksen | 14 May |
