List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:December 1 2010 10:12am
Subject:bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58626
View as plain text  
#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:georgi.kodinov@stripped

 3477 Ole John Aske	2010-12-01
      Fix for bug#58626, Incorrect result for WHERE <column> IN (<subquery>) IS UNKNOWN.
      
      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
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2010-10-29 08:23:06 +0000
+++ b/mysql-test/r/join_outer.result	2010-12-01 10:12:05 +0000
@@ -1427,4 +1427,44 @@ WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AN
 GROUP BY t2.f1, t2.f2;
 f1	f1	f2
 DROP TABLE t1,t2;
+#
+# 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-10-29 08:23:06 +0000
+++ b/mysql-test/t/join_outer.test	2010-12-01 10:12:05 +0000
@@ -1010,4 +1010,48 @@ GROUP BY t2.f1, t2.f2;
 
 DROP TABLE t1,t2;
 
+--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-10-29 08:23:06 +0000
+++ b/sql/sql_select.cc	2010-12-01 10:12:05 +0000
@@ -12917,10 +12917,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;
 }


Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20101201101205-esmqasng1tcpa3gr.bundle
Thread
bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58626Ole John Aske1 Dec
  • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58626Roy Lyseng14 Dec