List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:June 12 2011 2:01pm
Subject:bzr commit into mysql-trunk branch (guilhem.bichot:3383) Bug#12616344
View as plain text  
#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#12616344Guilhem Bichot13 Jun