bk commit into 5.2 tree (timour:1.2549)

[timour]

Below is the list of changes that have just been committed into a local
5.2 repository of tkatchaounov. When tkatchaounov does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-07-09 18:50:36+03:00, timour@stripped +4 -0
  WL#1110: Subquery optimization: materialization
  
  Changed the way we setup IN predicates for execution via hash semi-join so
  that it works correctly with UNION queries.
  
  Enabled the corresponding regression test case.

  mysql-test/r/information_schema.result@stripped, 2007-07-09 18:50:25+03:00, timour@stripped +8 -0
    Re-enabled test case that now passes.

  mysql-test/t/information_schema.test@stripped, 2007-07-09 18:50:25+03:00, timour@stripped +7 -8
    Re-enabled test case that now passes.

  sql/sql_select.cc@stripped, 2007-07-09 18:50:25+03:00, timour@stripped +46 -23
    Changed the way IN predicates are set-up for execution.
    Before the approach was to identify the top-most query, and then go through
    the list of all subqueries and invoke the setup method for each IN predicate
    for which the subquery is an argument. This approach doesn't work with UNION
    queries that contain subqueries, because the hash semi-join setup is called
    too late.
    
    This patch performs the setup one level higher, where we walk over all
    subqueries of a query, test if they are under IN predicate, and if the
    the optimizer chose to execute it via hash semi-join, we invoke the
    corresponding setup method for the IN predicate. 

  sql/sql_select.h@stripped, 2007-07-09 18:50:26+03:00, timour@stripped +1 -0
    Encapsulated all setup of IN predicates that should be executed via hash semi-join
    in one method.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	timour
# Host:	zmeiux.home
# Root:	/home/tkatchaounov/mysql/src/5.2-1110

--- 1.549/sql/sql_select.cc	2007-07-03 07:02:35 +03:00
+++ 1.550/sql/sql_select.cc	2007-07-09 18:50:25 +03:00
@@ -1742,29 +1742,9 @@
   if (!(select_options & SELECT_DESCRIBE))
     init_ftfuncs(thd, select_lex, test(order));
 
-  /*
-    Create all structures needed for materialized subquery execution,
-    assuming this is the outer-most query. This phase must be after
-    substitute_for_best_equal_field() because that function may replace
-    items with other items from a multiple equality, and we need to reference
-    the correct items in the index access method of the IN predicate.
-  */
-  if (is_top_level_join())
-     /* Alternatively check: (&lex.select_lex == join->select) */
-  {
-    for (SELECT_LEX *sl= thd->lex->all_selects_list; sl;
-         sl= sl->next_select_in_list())
-    {
-      Item_subselect *subquery_predicate= sl->master_unit()->item;
-      if (subquery_predicate &&
-          subquery_predicate->substype() == Item_subselect::IN_SUBS)
-      {
-        Item_in_subselect *in_subs= (Item_in_subselect*) subquery_predicate;
-        if (in_subs->use_hash_sj && in_subs->setup_hash_sj_engine())
-          DBUG_RETURN(1);
-      }
-    }
-  }
+  /* Create all structures needed for materialized subquery execution. */
+  if (setup_subquery_materialization())
+    DBUG_RETURN(1);
 
   /*
     is this simple IN subquery?
@@ -3330,6 +3310,49 @@
   }
   sj_subselects.clear();
   DBUG_RETURN(FALSE);
+}
+
+
+/**
+  Setup for execution all subqueries of a query, for which the optimizer
+  chose hash semi-join.
+
+  @detail Iterate over all subqueries of the query, and if they are under an
+  IN predicate, and the optimizer chose to compute it via hash semi-join,
+  initialize all data structures needed for the execution of the IN
+  predicate.
+
+  This method is part of the "code generation" query processing phase.
+
+  This phase must be called after substitute_for_best_equal_field() because
+  that function may replace items with other items from a multiple equality,
+  and we need to reference the correct items in the index access method of the
+  IN predicate.
+
+  @return Operation status
+  @retval FALSE     success.
+  @retval TRUE      error occurred.
+*/
+
+bool JOIN::setup_subquery_materialization()
+{
+  for (SELECT_LEX_UNIT *un= select_lex->first_inner_unit(); un;
+       un= un->next_unit())
+  {
+    for (SELECT_LEX *sl= un->first_select(); sl;
+         sl= sl->next_select())
+    {
+      Item_subselect *subquery_predicate= sl->master_unit()->item;
+      if (subquery_predicate &&
+          subquery_predicate->substype() == Item_subselect::IN_SUBS)
+      {
+        Item_in_subselect *in_subs= (Item_in_subselect*) subquery_predicate;
+        if (in_subs->use_hash_sj && in_subs->setup_hash_sj_engine())
+          return TRUE;
+      }
+    }
+  }
+  return FALSE;
 }
 
 

--- 1.127/sql/sql_select.h	2007-06-30 01:19:08 +03:00
+++ 1.128/sql/sql_select.h	2007-07-09 18:50:26 +03:00
@@ -586,6 +586,7 @@
   void restore_tmp();
   bool alloc_func_list();
   bool flatten_subqueries();
+  bool setup_subquery_materialization();
   bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields,
 			  bool before_group_by, bool recompute= FALSE);
 

--- 1.158/mysql-test/r/information_schema.result	2007-06-29 01:47:29 +03:00
+++ 1.159/mysql-test/r/information_schema.result	2007-07-09 18:50:25 +03:00
@@ -506,6 +506,14 @@
 AUTO_INCREMENT
 4
 drop table t1;
+create table t1 (s1 int);
+insert into t1 values (0),(9),(0);
+select s1 from t1 where s1 in (select version from
+information_schema.tables) union select version from
+information_schema.tables;
+s1
+10
+drop table t1;
 SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
 Table	Create Table
 CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (

--- 1.100/mysql-test/t/information_schema.test	2007-06-29 01:47:29 +03:00
+++ 1.101/mysql-test/t/information_schema.test	2007-07-09 18:50:25 +03:00
@@ -292,14 +292,13 @@
 insert into t1 values (1,1),(NULL,3),(NULL,4);
 select AUTO_INCREMENT from information_schema.tables where table_name = 't1';
 drop table t1;
-# WL#1110 - disabled test - wrong result
-# the problem is not IS specific.
-#create table t1 (s1 int);
-#insert into t1 values (0),(9),(0);
-#select s1 from t1 where s1 in (select version from
-#information_schema.tables) union select version from
-#information_schema.tables;
-#drop table t1;
+
+create table t1 (s1 int);
+insert into t1 values (0),(9),(0);
+select s1 from t1 where s1 in (select version from
+information_schema.tables) union select version from
+information_schema.tables;
+drop table t1;
 
 SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
 set names latin2;