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

[Ole John Aske]

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0/ based on revid:magnus.blaudd@stripped

 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
=== 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;
 }


Attachment: [text/bzr-bundle] bzr/[e-mail]