[Commits] Rev 3165: Fix bug lp:825095 in file:///home/tsk/mprog/src/5.3/

timour at askmonty.org timour at askmonty.org
Tue Aug 23 00:00:29 EEST 2011


At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------
revno: 3165
revision-id: timour at askmonty.org-20110822210013-egubev0wgyi00wjt
parent: igor at askmonty.org-20110820040205-1suikiokqfn8s81j
fixes bug(s): https://launchpad.net/bugs/825095
committer: timour at askmonty.org
branch nick: 5.3
timestamp: Tue 2011-08-23 00:00:13 +0300
message:
  Fix bug lp:825095
  
  Analysis:
  Partial matching is used even when there are no NULLs in
  a materialized subquery, as long as the left NOT IN operand
  may contain NULL values.
  
  This case was not handled correctly in two different places.
  First, the implementation of parital matching did not clear
  the set of matching columns when the merge process advanced
  to the next row.
  
  Second, there is no need to perform partial matching at all
  when the left operand has no NULLs.
  
  Solution:
  First fix subselect_rowid_merge_engine::partial_match() to
  properly cleanup the bitmap of matching keys when advancing
  to the next row.
  
  Second, change subselect_partial_match_engine::exec() so
  that when the materialized subquery doesn't contain any
  NULLs, and the left operand of [NOT] IN doesn't contain
  NULLs either, the method returns without doing any
  unnecessary partial matching. The correct result in this
  case is in Item::in_value.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2011-08-09 15:34:26 +0000
+++ b/mysql-test/r/subselect_mat.result	2011-08-22 21:00:13 +0000
@@ -1812,3 +1812,35 @@ id	select_type	table	type	possible_keys
 Warnings:
 Note    1003    select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where <in_optimizer>(9,<exists>(select `test`.`t2`.`a` from `test`.`t2` where (9 = `test`.`t2`.`a`)))
 DROP TABLE t1,t2;
+#
+# LPBUG#825095: Wrong result with materialization and NOT IN with 2 expressions
+#
+CREATE TABLE t1 (a int,b int);
+INSERT INTO t1 VALUES (4,4),(4,2);
+CREATE TABLE t2 (b int, a int);
+INSERT INTO t2 VALUES (4,3),(8,4);
+set @@optimizer_switch='semijoin=off,in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';
+EXPLAIN SELECT *
+FROM t1
+WHERE (a, b) NOT IN (SELECT a, b FROM t2);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       Using where
+2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       
+SELECT *
+FROM t1
+WHERE (a, b) NOT IN (SELECT a, b FROM t2);
+a       b
+4       4
+4       2
+EXPLAIN
+SELECT a, b, (a, b) NOT IN (SELECT a, b FROM t2) as sq
+FROM t1;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    2       
+2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       
+SELECT a, b, (a, b) NOT IN (SELECT a, b FROM t2) as sq
+FROM t1;
+a       b       sq
+4       4       1
+4       2       1
+drop table t1, t2;

=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test	2011-07-20 23:09:28 +0000
+++ b/mysql-test/t/subselect_mat.test	2011-08-22 21:00:13 +0000
@@ -226,3 +226,31 @@ SELECT MAX(t1.b) AS max_res FROM t1 WHER
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # LPBUG#825095: Wrong result with materialization and NOT IN with 2 expressions
+--echo #
+
+CREATE TABLE t1 (a int,b int);
+INSERT INTO t1 VALUES (4,4),(4,2);
+
+CREATE TABLE t2 (b int, a int);
+INSERT INTO t2 VALUES (4,3),(8,4);
+
+set @@optimizer_switch='semijoin=off,in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off';
+
+EXPLAIN SELECT *
+FROM t1
+WHERE (a, b) NOT IN (SELECT a, b FROM t2);
+
+SELECT *
+FROM t1
+WHERE (a, b) NOT IN (SELECT a, b FROM t2);
+
+EXPLAIN
+SELECT a, b, (a, b) NOT IN (SELECT a, b FROM t2) as sq
+FROM t1;
+
+SELECT a, b, (a, b) NOT IN (SELECT a, b FROM t2) as sq
+FROM t1;
+
+drop table t1, t2;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-08-17 11:10:32 +0000
+++ b/sql/item_subselect.cc	2011-08-22 21:00:13 +0000
@@ -3898,6 +3898,8 @@ subselect_hash_sj_engine::get_strategy_u
     }
     if (result_sink->get_null_count_of_col(i) == tmp_table->file->stats.records)
       ++count_null_only_columns;
+    if (result_sink->get_null_count_of_col(i))
+      ++count_columns_with_nulls;
   }
 
   /* If no column contains NULLs use regular hash index lookups. */
@@ -4659,6 +4661,7 @@ int subselect_hash_sj_engine::exec()
                                          count_pm_keys,
                                          has_covering_null_row,
                                          has_covering_null_columns,
+                                         count_columns_with_nulls,
                                          item, result,
                                          semi_join_conds->argument_list());
       if (!pm_engine ||
@@ -4684,7 +4687,8 @@ int subselect_hash_sj_engine::exec()
                                             item, result,
                                             semi_join_conds->argument_list(),
                                             has_covering_null_row,
-                                            has_covering_null_columns)))
+                                            has_covering_null_columns,
+                                            count_columns_with_nulls)))
       {
         /* This is an irrecoverable error. */
         res= 1;
@@ -5121,43 +5125,48 @@ subselect_partial_match_engine::subselec
   select_result_interceptor *result_arg,
   List<Item> *equi_join_conds_arg,
   bool has_covering_null_row_arg,
-  bool has_covering_null_columns_arg)
+  bool has_covering_null_columns_arg,
+  uint count_columns_with_nulls_arg)
   :subselect_engine(thd_arg, item_arg, result_arg),
    tmp_table(tmp_table_arg), lookup_engine(engine_arg),
    equi_join_conds(equi_join_conds_arg),
    has_covering_null_row(has_covering_null_row_arg),
-   has_covering_null_columns(has_covering_null_columns_arg)
+   has_covering_null_columns(has_covering_null_columns_arg),
+   count_columns_with_nulls(count_columns_with_nulls_arg)
 {}
 
 
 int subselect_partial_match_engine::exec()
 {
   Item_in_subselect *item_in= (Item_in_subselect *) item;
-  int res;
+  int copy_res, lookup_res;
 
   /* Try to find a matching row by index lookup. */
-  res= lookup_engine->copy_ref_key_simple();
-  if (res == -1)
+  copy_res= lookup_engine->copy_ref_key_simple();
+  if (copy_res == -1)
   {
     /* The result is FALSE based on the outer reference. */
     item_in->value= 0;
     item_in->null_value= 0;
     return 0;
   }
-  else if (res == 0)
+  else if (copy_res == 0)
   {
     /* Search for a complete match. */
-    if ((res= lookup_engine->index_lookup()))
+    if ((lookup_res= lookup_engine->index_lookup()))
     {
       /* An error occured during lookup(). */
       item_in->value= 0;
       item_in->null_value= 0;
-      return res;
+      return lookup_res;
     }
-    else if (item_in->value)
+    else if (item_in->value || !count_columns_with_nulls)
     {
       /*
         A complete match was found, the result of IN is TRUE.
+        If no match was found, and there are no NULLs in the materialized
+        subquery, then the result is guaranteed to be false because this
+        branch is executed when the outer reference has no NULLs as well.
         Notice: (this->item == lookup_engine->item)
       */
       return 0;
@@ -5608,6 +5617,7 @@ bool subselect_rowid_merge_engine::parti
       {
         min_key= cur_key;
         min_row_num= cur_row_num;
+        bitmap_clear_all(&matching_keys);
         bitmap_set_bit(&matching_keys, min_key->get_keyid());
         bitmap_union(&matching_keys, &matching_outer_cols);
       }
@@ -5641,11 +5651,13 @@ subselect_table_scan_engine::subselect_t
   select_result_interceptor *result_arg,
   List<Item> *equi_join_conds_arg,
   bool has_covering_null_row_arg,
-  bool has_covering_null_columns_arg)
+  bool has_covering_null_columns_arg,
+  uint count_columns_with_nulls_arg)
   :subselect_partial_match_engine(thd_arg, engine_arg, tmp_table_arg, item_arg,
                                   result_arg, equi_join_conds_arg,
                                   has_covering_null_row_arg,
-                                  has_covering_null_columns_arg)
+                                  has_covering_null_columns_arg,
+                                  count_columns_with_nulls_arg)
 {}
 
 

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2011-07-19 20:19:10 +0000
+++ b/sql/item_subselect.h	2011-08-22 21:00:13 +0000
@@ -870,7 +870,7 @@ class subselect_hash_sj_engine : public
       tmp_table(NULL), is_materialized(FALSE), materialize_engine(old_engine),
       materialize_join(NULL),  semi_join_conds(NULL), lookup_engine(NULL),
       count_partial_match_columns(0), count_null_only_columns(0),
-      strategy(UNDEFINED)
+      count_columns_with_nulls(0), strategy(UNDEFINED)
   {}
   ~subselect_hash_sj_engine();
 
@@ -908,6 +908,7 @@ class subselect_hash_sj_engine : public
   MY_BITMAP partial_match_key_parts;
   uint count_partial_match_columns;
   uint count_null_only_columns;
+  uint count_columns_with_nulls;
   /* Possible execution strategies that can be used to compute hash semi-join.*/
   enum exec_strategy {
     UNDEFINED,
@@ -1145,6 +1146,7 @@ class subselect_partial_match_engine : p
     guaranteed partial match.
   */
   bool has_covering_null_columns;
+  uint count_columns_with_nulls;
 
 protected:
   virtual bool partial_match()= 0;
@@ -1155,7 +1157,8 @@ class subselect_partial_match_engine : p
                                  select_result_interceptor *result_arg,
                                  List<Item> *equi_join_conds_arg,
                                  bool has_covering_null_row_arg,
-                                 bool has_covering_null_columns_arg);
+                                 bool has_covering_null_columns_arg,
+                                 uint count_columns_with_nulls_arg);
   int prepare() { return 0; }
   int exec();
   void fix_length_and_dec(Item_cache**) {}
@@ -1245,13 +1248,15 @@ class subselect_rowid_merge_engine: publ
                                TABLE *tmp_table_arg, uint merge_keys_count_arg,
                                bool has_covering_null_row_arg,
                                bool has_covering_null_columns_arg,
+                               uint count_columns_with_nulls_arg,
                                Item_subselect *item_arg,
                                select_result_interceptor *result_arg,
                                List<Item> *equi_join_conds_arg)
     :subselect_partial_match_engine(thd_arg, engine_arg, tmp_table_arg,
                                     item_arg, result_arg, equi_join_conds_arg,
                                     has_covering_null_row_arg,
-                                    has_covering_null_columns_arg),
+                                    has_covering_null_columns_arg,
+                                    count_columns_with_nulls_arg),
     merge_keys_count(merge_keys_count_arg), non_null_key(NULL)
   {}
   ~subselect_rowid_merge_engine();
@@ -1272,7 +1277,8 @@ class subselect_table_scan_engine: publi
                               select_result_interceptor *result_arg,
                               List<Item> *equi_join_conds_arg,
                               bool has_covering_null_row_arg,
-                              bool has_covering_null_columns_arg);
+                              bool has_covering_null_columns_arg,
+                              uint count_columns_with_nulls_arg);
   void cleanup();
   virtual enum_engine_type engine_type() { return TABLE_SCAN_ENGINE; }
 };



More information about the commits mailing list