From: Ole John Aske
Date: December 1 2010 10:21am
Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
 (ole.john.aske:3379 to 3380) Bug#58626
List-Archive: http://lists.mysql.com/commits/125633
X-Bug: 58626
Message-Id: <20101201102140.3BB92222@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3380 Ole John Aske	2010-12-01
      SPJ-scan-scan: Cherry picked proposed fix for bug#58626 into SPJ branch
      
            NOTE: This fix is backported from 5.6.99 which already seems to have fixed this problem.
      
            The fix ensures that if 'Full scan on NULL key' access method may be used
            for a table, we can't assume that any part of the predicate is covered by
            the REF-key. (The join_tab is known to have 'Full scan on NULL key' if
            any cond_guards[] has been defined for 'join_tab->ref')
      
            part_of_refkey() will therefore return '0' if a potential'Full scan on NULL key'
            is detected - Which will force make_cond_for_table() to include all part of a
            predicate covering the specified 'tables' and 'used_table' mask.

    modified:
      mysql-test/r/join_outer.result
      mysql-test/t/join_outer.test
      sql/sql_select.cc
 3379 Ole John Aske	2010-11-25 [merge]
      Merge from telco-7.0 mainline

    modified:
      mysql-test/suite/ndb/r/ndb_index.result
      mysql-test/suite/ndb/r/ndb_read_multi_range.result
      mysql-test/suite/ndb/t/ndb_index.test
      mysql-test/suite/ndb/t/ndb_read_multi_range.test
      mysql-test/suite/rpl/t/disabled.def
      sql/ha_ndbcluster.cc
      sql/ha_ndbcluster.h
      sql/rpl_utility.cc
      storage/ndb/src/kernel/blocks/ERROR_codes.txt
      storage/ndb/src/kernel/blocks/dbdih/DbdihMain.cpp
      storage/ndb/src/kernel/blocks/dblqh/DblqhMain.cpp
      storage/ndb/src/kernel/vm/GlobalData.hpp
      storage/ndb/src/kernel/vm/mt.cpp
      storage/ndb/src/mgmsrv/ConfigInfo.cpp
      storage/ndb/test/ndbapi/testNdbApi.cpp
      storage/ndb/test/ndbapi/testNodeRestart.cpp
      storage/ndb/test/run-test/daily-basic-tests.txt
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2010-11-25 14:13:23 +0000
+++ b/mysql-test/r/join_outer.result	2010-12-01 10:21:04 +0000
@@ -1441,4 +1441,44 @@ i	i	i	i
 3	3	NULL	NULL
 4	4	4	NULL
 DROP TABLE t1,t2,t3,t4;
+#
+# Bug#58626 Incorrect result for WHERE <column> IN (<subquery>) IS UNKNOWN 
+#
+CREATE TABLE t1 (I INT NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (I INT NOT NULL);
+INSERT INTO t2 VALUES (3);
+CREATE TABLE t3 (PK1 INT, PK2 INT, PRIMARY KEY(PK1,PK2));
+INSERT INTO t3 VALUES (1,1),(2,2),(3,3);
+SELECT * FROM
+t1 LEFT JOIN t2 ON t2.i = t1.i
+WHERE t2.i IN 
+(
+SELECT STRAIGHT_JOIN t3.pk1 FROM t3 JOIN t3 as t4
+ON t4.pk1=t3.pk1
+WHERE t3.pk2 = t2.i
+)
+IS UNKNOWN;
+I	I
+SELECT * FROM
+t1 LEFT JOIN t2 ON t2.i = t1.i
+WHERE t2.i IN 
+(
+SELECT t3.pk1 FROM t3 LEFT JOIN t3 as t4
+ON t4.pk1=t3.pk1
+WHERE t3.pk2 = t2.i
+)
+IS UNKNOWN;
+I	I
+SELECT * FROM
+t1 LEFT JOIN t2 ON t2.i = t1.i
+WHERE t2.i IN 
+(
+SELECT t3.pk1 FROM t3 JOIN t3 as t4
+ON t4.pk1=t3.pk1
+WHERE t3.pk2 = t2.i
+)
+IS UNKNOWN;
+I	I
+DROP TABLE t1,t2,t3;
 End of 5.1 tests

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2010-11-25 14:13:23 +0000
+++ b/mysql-test/t/join_outer.test	2010-12-01 10:21:04 +0000
@@ -1022,4 +1022,48 @@ SELECT * FROM
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # Bug#58626 Incorrect result for WHERE <column> IN (<subquery>) IS UNKNOWN 
+--echo #
+
+CREATE TABLE t1 (I INT NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (I INT NOT NULL);
+INSERT INTO t2 VALUES (3);
+CREATE TABLE t3 (PK1 INT, PK2 INT, PRIMARY KEY(PK1,PK2));
+INSERT INTO t3 VALUES (1,1),(2,2),(3,3);
+
+##'IS UNKNOWN' should not return any rows as subquery returns an empty set
+SELECT * FROM
+ t1 LEFT JOIN t2 ON t2.i = t1.i
+ WHERE t2.i IN 
+ (
+   SELECT STRAIGHT_JOIN t3.pk1 FROM t3 JOIN t3 as t4
+    ON t4.pk1=t3.pk1
+    WHERE t3.pk2 = t2.i
+ )
+ IS UNKNOWN; 
+
+SELECT * FROM
+ t1 LEFT JOIN t2 ON t2.i = t1.i
+ WHERE t2.i IN 
+ (
+   SELECT t3.pk1 FROM t3 LEFT JOIN t3 as t4
+    ON t4.pk1=t3.pk1
+    WHERE t3.pk2 = t2.i
+ )
+ IS UNKNOWN; 
+
+SELECT * FROM
+ t1 LEFT JOIN t2 ON t2.i = t1.i
+ WHERE t2.i IN 
+ (
+   SELECT t3.pk1 FROM t3 JOIN t3 as t4
+    ON t4.pk1=t3.pk1
+    WHERE t3.pk2 = t2.i
+ )
+ IS UNKNOWN; 
+
+DROP TABLE t1,t2,t3;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-25 14:13:23 +0000
+++ b/sql/sql_select.cc	2010-12-01 10:21:04 +0000
@@ -13128,10 +13128,23 @@ part_of_refkey(TABLE *table,Field *field
     KEY_PART_INFO *key_part=
       table->key_info[table->reginfo.join_tab->ref.key].key_part;
 
-    for (uint part=0 ; part < ref_parts ; part++,key_part++)
+    uint part;
+
+    /* If execution plan may use 'Full scan on NULL key', There might
+     * not by any 'REF' access and entire predicate should be preserved.
+     */
+    for (part=0 ; part < ref_parts ; part++)
+    {
+      if (table->reginfo.join_tab->ref.cond_guards[part])
+        return (Item*) 0;
+    }
+
+    for (part=0 ; part < ref_parts ; part++,key_part++)
+    {
       if (field->eq(key_part->field) &&
 	  !(key_part->key_part_flag & (HA_PART_KEY_SEG | HA_NULL_PART)))
 	return table->reginfo.join_tab->ref.items[part];
+    }
   }
   return (Item*) 0;
 }

No bundle (reason: useless for push emails).