#At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting/ based on revid:guilhem.bichot@stripped
3383 Guilhem Bichot 2011-06-12
test for already fixed
Bug#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT OF ROWS WHEN JCL>=3
@ mysql-test/include/subquery_sj.inc
test; the bad result used to be one 6 instead of two 6.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2011-06-11 13:20:10 +0000
+++ b/mysql-test/include/subquery_sj.inc 2011-06-12 14:00:53 +0000
@@ -3567,3 +3567,31 @@ WHERE f2 IN (SELECT a1.col_varchar_1024_
ON a1.col_int_key = a2.col_int_key
WHERE a1.col_int BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+--echo # OF ROWS WHEN JCL>=3
+--echo #
+# duplicate of Bug#12546542 but still tested as query is different
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+ SELECT SUBQUERY2_t2.col_int_nokey
+ FROM t3 AS SUBQUERY2_t1
+ RIGHT JOIN
+ t2 AS SUBQUERY2_t2
+ JOIN t1 AS SUBQUERY2_t3
+ ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+ WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+ OR SUBQUERY2_t2.pk <= table1.pk
+);
+
+DROP TABLE t1,t2,t3;
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-06-12 14:00:53 +0000
@@ -5457,4 +5457,32 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2011-06-12 14:00:53 +0000
@@ -5461,5 +5461,33 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2011-06-12 14:00:53 +0000
@@ -5461,5 +5461,33 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-06-12 14:00:53 +0000
@@ -5456,4 +5456,32 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-06-12 14:00:53 +0000
@@ -5460,5 +5460,33 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-06-12 14:00:53 +0000
@@ -5460,5 +5460,33 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-06-12 14:00:53 +0000
@@ -5458,6 +5458,34 @@ we
v
DROP TABLE t1,t2,t3;
#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-06-12 14:00:53 +0000
@@ -5462,6 +5462,34 @@ we
v
DROP TABLE t1,t2,t3;
#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-06-12 14:00:53 +0000
@@ -5462,6 +5462,34 @@ we
v
DROP TABLE t1,t2,t3;
#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-06-12 14:00:53 +0000
@@ -5457,4 +5457,32 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-06-12 14:00:53 +0000
@@ -5461,5 +5461,33 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-06-12 14:00:53 +0000
@@ -5461,5 +5461,33 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-06-12 14:00:53 +0000
@@ -5457,4 +5457,32 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2011-06-12 14:00:53 +0000
@@ -5461,5 +5461,33 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2011-06-12 14:00:53 +0000
@@ -5461,5 +5461,33 @@ f2
we
v
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-06-12 14:00:53 +0000
@@ -5679,4 +5679,32 @@ f2
v
we
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-06-12 14:00:53 +0000
@@ -5602,4 +5602,32 @@ f2
v
we
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2011-06-12 14:00:53 +0000
@@ -5606,5 +5606,33 @@ f2
v
we
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result 2011-06-11 13:20:10 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2011-06-12 14:00:53 +0000
@@ -5606,5 +5606,33 @@ f2
v
we
DROP TABLE t1,t2,t3;
+#
+# BUG#12616344 - JCL: DIFFERENT RESULT SET AND DIFFERENT AMOUNT
+# OF ROWS WHEN JCL>=3
+#
+CREATE TABLE t1 (col_int_nokey int, col_int_key int, col_varchar_key varchar(1));
+INSERT INTO t1 VALUES (0,4,'c'),(1,6,'u');
+CREATE TABLE t2 (pk int, col_int_nokey int, col_varchar_nokey varchar(1));
+INSERT INTO t2 VALUES (1,4,'b'),(94,6,'u');
+CREATE TABLE t3 (pk int, col_int_nokey int, col_varchar_key varchar(1));
+INSERT INTO t3 VALUES (1,4,'j'),(2,6,'v');
+SELECT table2.col_int_key
+FROM t3 AS table1 JOIN t1 AS table2 ON table2.col_int_nokey
+WHERE table1.col_int_nokey IN
+(
+SELECT SUBQUERY2_t2.col_int_nokey
+FROM t3 AS SUBQUERY2_t1
+RIGHT JOIN
+t2 AS SUBQUERY2_t2
+JOIN t1 AS SUBQUERY2_t3
+ON SUBQUERY2_t3.col_int_key = SUBQUERY2_t2.col_int_nokey
+ON SUBQUERY2_t3.col_varchar_key = SUBQUERY2_t2.col_varchar_nokey
+WHERE SUBQUERY2_t1.col_varchar_key != table1.col_varchar_key
+OR SUBQUERY2_t2.pk <= table1.pk
+);
+col_int_key
+6
+6
+DROP TABLE t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20110612140053-vlmmups3gs11a1fe.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (guilhem.bichot:3383) Bug#12616344 | Guilhem Bichot | 13 Jun |
