bk commit into 5.2 tree (timour:1.2555)

[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-08-09 10:24:07+03:00, timour@stripped +5 -0
  WL#1110: Subquery optimization: materialization
  
  Fixed problems with subquery materialization when the subquery is executed
  within a stored procedure.

  mysql-test/r/sp.result@stripped, 2007-08-09 10:24:01+03:00, timour@stripped +14 -0
    Enabled a test case that was previously failing.

  mysql-test/t/sp.test@stripped, 2007-08-09 10:24:01+03:00, timour@stripped +5 -5
    Enabled a test case that was previously failing.

  sql/item_subselect.cc@stripped, 2007-08-09 10:24:01+03:00, timour@stripped +6 -4
    * Fixed a problem with detecting empty subqueries when the subquery itself
      required a temporary table for its execution. Instead of testing for the
      number of sent records, we check whether the temporary table for the materialized
      query is empty as this is reliable in all cases.
    * Added comments.

  sql/sql_class.h@stripped, 2007-08-09 10:24:02+03:00, timour@stripped +1 -1
    Added select_union::cleanup() method.

  sql/sql_union.cc@stripped, 2007-08-09 10:24:02+03:00, timour@stripped +16 -0
    Added select_union::cleanup() method that overrides the default noop method,
    so that between subquery re-executions temporary tables are emptied and their
    state is reset.

# 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.371/sql/sql_class.h	2007-07-01 00:52:28 +03:00
+++ 1.372/sql/sql_class.h	2007-08-09 10:24:02 +03:00
@@ -2106,7 +2106,7 @@
   bool send_data(List<Item> &items);
   bool send_eof();
   bool flush();
-
+  void cleanup();
   bool create_result_table(THD *thd, List<Item> *column_types,
                            bool is_distinct, ulonglong options,
                            const char *alias);

--- 1.154/sql/sql_union.cc	2007-07-01 01:53:26 +03:00
+++ 1.155/sql/sql_union.cc	2007-08-09 10:24:02 +03:00
@@ -130,6 +130,22 @@
 }
 
 
+/**
+  Reset and empty the temporary table that stores the materialized query result.
+
+  @note The cleanup performed here is exactly the same as for the two temp
+  tables of JOIN - exec_tmp_table_[1 | 2].
+*/
+
+void select_union::cleanup()
+{
+  table->file->extra(HA_EXTRA_RESET_STATE);
+  table->file->delete_all_rows();
+  free_io_cache(table);
+  filesort_free_buffers(table,0);
+}
+
+
 /*
   initialization procedures before fake_select_lex preparation()
 

--- 1.163/sql/item_subselect.cc	2007-07-13 15:20:00 +03:00
+++ 1.164/sql/item_subselect.cc	2007-08-09 10:24:01 +03:00
@@ -2937,7 +2937,7 @@
 void subselect_hash_sj_engine::cleanup()
 {
   is_materialized= FALSE;
-  result->cleanup();
+  result->cleanup(); /* Resets the temp table as well. */
   materialize_engine->cleanup();
   subselect_uniquesubquery_engine::cleanup();
 }
@@ -2978,10 +2978,12 @@
         immediately after materialization (yet i's done together with unlocking).
      */
     /*
-      If the subquery returned no rows, there is no need to perform
-      lookups for empty subqueries.
+      If the subquery returned no rows, the temporary table is empty, so we know
+      directly that the result of IN is FALSE. We first update the table statistics,
+      then we test if the temporary table for the query result is empty.
     */
-    if (!materialize_join->send_records)
+    tab->table->file->info(HA_STATUS_VARIABLE);
+    if (!tab->table->file->stats.records)
     {
       empty_result_set= TRUE;
       item_in->value= FALSE;

--- 1.276/mysql-test/r/sp.result	2007-07-11 16:40:19 +03:00
+++ 1.277/mysql-test/r/sp.result	2007-08-09 10:24:01 +03:00
@@ -5435,6 +5435,20 @@
 WHERE Member_ID=paramMember GROUP BY Track);
 RETURN tracks;
 END|
+SELECT bug21493('111111')|
+bug21493('111111')
+NULL
+SELECT bug21493('222222')|
+bug21493('222222')
+CAD
+SELECT bug21493(Member_ID) FROM t3|
+bug21493(Member_ID)
+NULL
+CAD
+CAD
+CAD
+CAD
+CHF
 DROP FUNCTION bug21493|
 DROP TABLE t3,t4|
 drop function if exists func_20028_a|

--- 1.242/mysql-test/t/sp.test	2007-07-11 16:40:20 +03:00
+++ 1.243/mysql-test/t/sp.test	2007-08-09 10:24:01 +03:00
@@ -6391,11 +6391,11 @@
                                   WHERE Member_ID=paramMember GROUP BY Track);
 RETURN tracks;
 END|
-# WL#1110 - disabled queries because of wrong result in PS mode
-#SELECT bug21493('111111')|
-#SELECT bug21493('222222')|
-#
-#SELECT bug21493(Member_ID) FROM t3|
+
+SELECT bug21493('111111')|
+SELECT bug21493('222222')|
+
+SELECT bug21493(Member_ID) FROM t3|
 
 DROP FUNCTION bug21493|
 DROP TABLE t3,t4|