bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4113 to 4114)

[Ole John Aske]

 4114 Ole John Aske	2011-01-14
      Backport to mysql-5.1-telco-7.0:
      
      Fix for bug#58626, Incorrect result for WHERE <column> IN (<subquery>) IS UNKNOWN.
            
      NOTE: This fix is a backport from 5.6.x 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
 4113 Magnus BlXX values in mysql server enums,
         ll the values that can be expected to be handled
         by condition pushdown are hardcoded. Only when asking
         for "is this type supported/expected?" can we expect "any"
         value for the type. This means the 'expecting' functions should return
         false for any type values it does not know about.
       - Since we know the max values for bitmask at compile time, use
          fixed size buffers to remove the need for extra mallocs when creating
         Ndb_expect_stack
       - revert the additiotn of MAX_XXX values on the three enums

    modified:
      include/mysql.h.pp
      include/mysql_com.h
      sql/ha_ndbcluster_cond.h
      sql/item.h
      sql/rpl_utility.cc
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2011-01-14 08:54:47 +0000
+++ b/mysql-test/r/join_outer.result	2011-01-14 14:26:14 +0000
@@ -1467,4 +1467,44 @@ WHERE 7;
 col_datetime_key
 NULL
 DROP TABLE BB;
+#
+# 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	2011-01-14 08:54:47 +0000
+++ b/mysql-test/t/join_outer.test	2011-01-14 14:26:14 +0000
@@ -1063,4 +1063,48 @@ FROM BB table1 RIGHT JOIN BB table2
 
 DROP TABLE BB;
 
+--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	2011-01-14 12:49:25 +0000
+++ b/sql/sql_select.cc	2011-01-14 14:26:14 +0000
@@ -12960,10 +12960,27 @@ part_of_refkey(TABLE *table,Field *field
     KEY_PART_INFO *key_part=
       table->key_info[table->reginfo.join_tab->ref.key].key_part;
 
+#ifndef MCP_BUG58626
+    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++)
+#else
     for (uint part=0 ; part < ref_parts ; part++,key_part++)
+#endif
+    {
       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).