[Commits] Rev 3166: Automatic merge. in file:///home/tsk/mprog/src/5.3-tmp/

timour at askmonty.org timour at askmonty.org
Tue Aug 23 15:51:56 EEST 2011


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

------------------------------------------------------------
revno: 3166 [merge]
revision-id: timour at askmonty.org-20110823125147-65w156utjsku0dj7
parent: sergii at pisem.net-20110822113832-aun1iwedg7b124i2
parent: timour at askmonty.org-20110823123915-jxf9hvxq2hy03rr7
committer: timour at askmonty.org
branch nick: 5.3-tmp
timestamp: Tue 2011-08-23 15:51:47 +0300
message:
  Automatic merge.
modified:
  mysql-test/r/subselect4.result subselect4.result-20090903150316-1sul3u8k29ooxm3r-2
  mysql-test/r/subselect_mat.result subselect_mat.result-20100117143924-r0jv32dj80dg3b5h-1
  mysql-test/t/subselect4.test   subselect4.test-20090903150316-1sul3u8k29ooxm3r-1
  mysql-test/t/subselect_mat.test subselect_mat.test-20100117143929-iif102ysgna1tyj0-1
  sql/item_cmpfunc.cc            sp1f-item_cmpfunc.cc-19700101030959-hrk7pi2n6qpwxauufnkizirsoucdcx2e
  sql/item_subselect.cc          sp1f-item_subselect.cc-20020512204640-qep43aqhsfrwkqmrobni6czc3fqj36oo
  sql/item_subselect.h           sp1f-item_subselect.h-20020512204640-qdg77wil56cxyhtc2bjjdrppxq3wqgh3
-------------- next part --------------
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2011-07-21 12:50:25 +0000
+++ b/mysql-test/r/subselect4.result	2011-08-23 12:39:15 +0000
@@ -2090,4 +2090,42 @@ EXECUTE st2;
 f2
 2
 drop table t1, t2;
+#
+# LP BUG#825018: Crash in check_and_do_in_subquery_rewrites() with corrlated subquery in select list
+#
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (10,1),(11,7);
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (1,1);
+CREATE PROCEDURE sp1 () LANGUAGE SQL
+SELECT (SELECT t1.a
+FROM t1
+WHERE t1.b = t3.b
+AND t1.b IN ( SELECT a FROM t2 )) sq
+FROM t3
+GROUP BY 1;
+CALL sp1();
+sq
+NULL
+CALL sp1();
+sq
+NULL
+drop procedure sp1;
+prepare st1 from "
+SELECT (SELECT t1.a
+        FROM t1
+        WHERE t1.b = t3.b
+        AND t1.b IN ( SELECT a FROM t2 )) sq
+FROM t3
+GROUP BY 1";
+execute st1;
+sq
+NULL
+execute st1;
+sq
+NULL
+deallocate prepare st1;
+drop table t1, t2, t3;
 set optimizer_switch=@subselect4_tmp;

=== 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/subselect4.test'
--- a/mysql-test/t/subselect4.test	2011-07-18 20:45:38 +0000
+++ b/mysql-test/t/subselect4.test	2011-08-23 12:39:15 +0000
@@ -1726,5 +1726,41 @@ EXECUTE st2;
 
 drop table t1, t2;
 
+--echo #
+--echo # LP BUG#825018: Crash in check_and_do_in_subquery_rewrites() with corrlated subquery in select list
+--echo #
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (10,1),(11,7);
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (2),(3);
+
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (1,1);
+
+CREATE PROCEDURE sp1 () LANGUAGE SQL
+SELECT (SELECT t1.a
+        FROM t1
+        WHERE t1.b = t3.b
+        AND t1.b IN ( SELECT a FROM t2 )) sq
+FROM t3
+GROUP BY 1;
+CALL sp1();
+CALL sp1();
+drop procedure sp1;
+
+prepare st1 from "
+SELECT (SELECT t1.a
+        FROM t1
+        WHERE t1.b = t3.b
+        AND t1.b IN ( SELECT a FROM t2 )) sq
+FROM t3
+GROUP BY 1";
+execute st1;
+execute st1;
+deallocate prepare st1;
+
+drop table t1, t2, t3;
 
 set optimizer_switch=@subselect4_tmp;

=== 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_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2011-08-17 11:10:32 +0000
+++ b/sql/item_cmpfunc.cc	2011-08-23 12:39:15 +0000
@@ -1804,7 +1804,7 @@ Item *Item_in_optimizer::transform(Item_
                  Item_subselect::ANY_SUBS));
 
     Item_in_subselect *in_arg= (Item_in_subselect*)args[1];
-    in_arg->left_expr= args[0];
+    current_thd->change_item_tree(&in_arg->left_expr, args[0]);
   }
   return (this->*transformer)(argument);
 }

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