List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:March 15 2011 11:28am
Subject:bzr commit into mysql-trunk branch (mattias.jonsson:3693)
View as plain text  
#At file:///Users/mattiasj/mysql-bzr/wl4443-trunk/ based on revid:mattias.jonsson@stripped

 3693 Mattias Jonsson	2011-03-15
      Minor updates from Mikaels preliminary code review

    modified:
      mysql-test/r/wl4443.result
      mysql-test/t/wl4443.test
      sql/ha_partition.cc
      sql/sql_base.cc
      sql/sql_base.h
      sql/sql_delete.cc
      sql/sql_derived.cc
      sql/sql_insert.cc
      sql/sql_parse.cc
      sql/sql_select.cc
      sql/sql_union.cc
      sql/sql_update.cc
=== modified file 'mysql-test/r/wl4443.result'
--- a/mysql-test/r/wl4443.result	2011-02-21 16:27:53 +0000
+++ b/mysql-test/r/wl4443.result	2011-03-15 11:27:59 +0000
@@ -804,6 +804,164 @@ a	b
 4	First row, p4
 52	Fifth row, p0, updated 1
 # Test CREATE SELECT
+# Test Stored procedures
+DROP PROCEDURE IF EXISTS sp_insert;
+DROP PROCEDURE IF EXISTS sp_insert_partition;
+DROP PROCEDURE IF EXISTS sp_select_all;
+DROP PROCEDURE IF EXISTS sp_select_exact;
+DROP PROCEDURE IF EXISTS sp_select_partition;
+DROP PROCEDURE IF EXISTS sp_select_range;
+CREATE PROCEDURE sp_insert(a INT, b CHAR(16))
+INSERT INTO test.t1 VALUES (a, b);
+CREATE PROCEDURE sp_insert_partition(p CHAR(16), a INT, b CHAR(16))
+BEGIN
+SET @str = concat("INSERT INTO test.t1 PARTITION(", p, ") VALUES (?, ?)");
+set @x = a, @y = b;
+PREPARE stmt FROM @str;
+EXECUTE stmt USING @x, @y;
+END|
+CREATE PROCEDURE sp_select_all()
+SELECT * FROM test.t1;
+CREATE PROCEDURE sp_select_exact(x INT)
+SELECT * FROM test.t1 WHERE a = x;
+CREATE PROCEDURE sp_select_partition(p CHAR(16))
+BEGIN
+SET @str = concat("SELECT * FROM test.t1 PARTITION(", p, ")");
+PREPARE stmt FROM @str;
+EXECUTE stmt;
+END|
+CREATE PROCEDURE sp_select_range(x INT, y INT)
+SELECT * FROM test.t1 WHERE a between x and y;
+FLUSH STATUS;
+CALL sp_insert(313,"Test313");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	30
+HANDLER_READ_KEY	1
+HANDLER_WRITE	18
+FLUSH STATUS;
+CALL sp_insert_partition("p7", 98, "Test98");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	6
+HANDLER_READ_KEY	1
+HANDLER_WRITE	18
+FLUSH STATUS;
+CALL sp_insert_partition("p8", 111, "Test111");
+ERROR HY000: Found a row not matching the given partition set
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_EXTERNAL_LOCK	4
+HANDLER_ROLLBACK	1
+HANDLER_WRITE	17
+FLUSH STATUS;
+CALL sp_insert_partition("p7,p8", 111, "Test111");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	6
+HANDLER_WRITE	18
+FLUSH STATUS;
+CALL sp_select_all();
+a	b
+0	First row, p0
+1	First row, p1
+111	Test111
+113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
+2	First row, p2
+26	Third row, p0, updated 1
+3	First row, p3
+313	Test313
+39	Fourth row, p0, updated 1
+4	First row, p4
+52	Fifth row, p0, updated 1
+98	Test98
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	30
+HANDLER_READ_FIRST	13
+HANDLER_READ_KEY	27
+HANDLER_READ_NEXT	12
+HANDLER_WRITE	17
+FLUSH STATUS;
+CALL sp_select_exact(98);
+a	b
+98	Test98
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	6
+HANDLER_READ_KEY	3
+HANDLER_WRITE	17
+FLUSH STATUS;
+CALL sp_select_partition("p7");
+a	b
+111	Test111
+98	Test98
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	6
+HANDLER_READ_FIRST	1
+HANDLER_READ_KEY	3
+HANDLER_READ_NEXT	2
+HANDLER_WRITE	17
+FLUSH STATUS;
+CALL sp_select_partition("p8");
+a	b
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	4
+HANDLER_READ_FIRST	1
+HANDLER_READ_KEY	2
+HANDLER_WRITE	17
+FLUSH STATUS;
+CALL sp_select_partition("p7,p8");
+a	b
+111	Test111
+98	Test98
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	6
+HANDLER_READ_FIRST	2
+HANDLER_READ_KEY	4
+HANDLER_READ_NEXT	2
+HANDLER_WRITE	17
+FLUSH STATUS;
+CALL sp_select_range(1,5);
+a	b
+1	First row, p1
+2	First row, p2
+3	First row, p3
+4	First row, p4
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	14
+HANDLER_READ_KEY	16
+HANDLER_READ_NEXT	4
+HANDLER_WRITE	17
+DROP PROCEDURE sp_insert;
+DROP PROCEDURE sp_insert_partition;
+DROP PROCEDURE sp_select_all;
+DROP PROCEDURE sp_select_partition;
+DROP PROCEDURE sp_select_range;
+DROP PROCEDURE sp_select_exact;
 # TODO: Add variants for LOCK TABLES
 # TODO: add delayed locking/pruning for multi-table update
 # TODO: Document that functions in WHERE clause can now be evaluated

=== modified file 'mysql-test/t/wl4443.test'
--- a/mysql-test/t/wl4443.test	2011-02-21 16:27:53 +0000
+++ b/mysql-test/t/wl4443.test	2011-03-15 11:27:59 +0000
@@ -257,6 +257,93 @@ eval $get_handler_status_counts;
 --sorted_result
 SELECT * FROM t1;
 --echo # Test CREATE SELECT
+
+
+--echo # Test Stored procedures
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp_insert;
+DROP PROCEDURE IF EXISTS sp_insert_partition;
+DROP PROCEDURE IF EXISTS sp_select_all;
+DROP PROCEDURE IF EXISTS sp_select_exact;
+DROP PROCEDURE IF EXISTS sp_select_partition;
+DROP PROCEDURE IF EXISTS sp_select_range;
+--enable_warnings
+CREATE PROCEDURE sp_insert(a INT, b CHAR(16))
+  INSERT INTO test.t1 VALUES (a, b);
+
+delimiter |;
+CREATE PROCEDURE sp_insert_partition(p CHAR(16), a INT, b CHAR(16))
+BEGIN
+  SET @str = concat("INSERT INTO test.t1 PARTITION(", p, ") VALUES (?, ?)");
+  set @x = a, @y = b;
+  PREPARE stmt FROM @str;
+  EXECUTE stmt USING @x, @y;
+END|
+delimiter ;|
+
+CREATE PROCEDURE sp_select_all()
+  SELECT * FROM test.t1;
+
+CREATE PROCEDURE sp_select_exact(x INT)
+  SELECT * FROM test.t1 WHERE a = x;
+
+delimiter |;
+CREATE PROCEDURE sp_select_partition(p CHAR(16))
+BEGIN
+  SET @str = concat("SELECT * FROM test.t1 PARTITION(", p, ")");
+  PREPARE stmt FROM @str;
+  EXECUTE stmt;
+END|
+delimiter ;|
+
+
+CREATE PROCEDURE sp_select_range(x INT, y INT)
+  SELECT * FROM test.t1 WHERE a between x and y;
+
+FLUSH STATUS;
+CALL sp_insert(313,"Test313");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+CALL sp_insert_partition("p7", 98, "Test98");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
+CALL sp_insert_partition("p8", 111, "Test111");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+CALL sp_insert_partition("p7,p8", 111, "Test111");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+CALL sp_select_all();
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+CALL sp_select_exact(98);
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+CALL sp_select_partition("p7");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+CALL sp_select_partition("p8");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+CALL sp_select_partition("p7,p8");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+--sorted_result
+CALL sp_select_range(1,5);
+eval $get_handler_status_counts;
+DROP PROCEDURE sp_insert;
+DROP PROCEDURE sp_insert_partition;
+DROP PROCEDURE sp_select_all;
+DROP PROCEDURE sp_select_partition;
+DROP PROCEDURE sp_select_range;
+DROP PROCEDURE sp_select_exact;
+
 --echo # TODO: Add variants for LOCK TABLES
 --echo # TODO: add delayed locking/pruning for multi-table update
 --echo # TODO: Document that functions in WHERE clause can now be evaluated

=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc	2011-02-21 16:27:53 +0000
+++ b/sql/ha_partition.cc	2011-03-15 11:27:59 +0000
@@ -2943,6 +2943,7 @@ int ha_partition::external_lock(THD *thd
     used_partitions= &(m_part_info->lock_partitions);
   first_used_partition= bitmap_get_first_set(used_partitions);
 
+  DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
   if (first_used_partition == MY_BIT_NONE)
     DBUG_RETURN(0);
 
@@ -3044,6 +3045,7 @@ THR_LOCK_DATA **ha_partition::store_lock
 
   first_used_partition= bitmap_get_first_set(&(m_part_info->lock_partitions));
 
+  DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
   if (first_used_partition == MY_BIT_NONE)
     DBUG_RETURN(to);
 
@@ -3083,6 +3085,7 @@ int ha_partition::start_stmt(THD *thd, t
 
   first_used_partition= bitmap_get_first_set(&(m_part_info->lock_partitions));
 
+  DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
   if (first_used_partition == MY_BIT_NONE)
     DBUG_RETURN(0);
 
@@ -3198,6 +3201,8 @@ void ha_partition::try_semi_consistent_r
   
   first_used_partition= bitmap_get_first_set(&(m_part_info->read_partitions));
 
+  DBUG_ASSERT(first_used_partition != MY_BIT_NONE);
+
   for (i= first_used_partition; i < m_tot_parts; i++)
   {
     if (bitmap_is_set(&(m_part_info->read_partitions), i))
@@ -6081,22 +6086,18 @@ int ha_partition::reset(void)
     int tmp;
     uint first_used_partition;
     first_used_partition= bitmap_get_first_set(&m_part_info->lock_partitions);
-    if (first_used_partition == MY_BIT_NONE)
-    {
-      /* Be sure lock_partitions are set if no pruning in the next statement */
-      m_part_info->set_partition_bitmaps(NULL);
-      DBUG_RETURN(result);
-    }
-
-    file= m_file + first_used_partition;
-    do
+    if (first_used_partition != MY_BIT_NONE)
     {
-      if (bitmap_is_set(&(m_part_info->lock_partitions), file - m_file))
+      file= m_file + first_used_partition;
+      do
       {
-        if ((tmp= (*file)->ha_reset()))
-          result= tmp;
-      }
-    } while (*(++file));
+        if (bitmap_is_set(&(m_part_info->lock_partitions), file - m_file))
+        {
+          if ((tmp= (*file)->ha_reset()))
+            result= tmp;
+        }
+      } while (*(++file));
+    }
     /* Be sure lock_partitions are set if no pruning in the next statement */
     m_part_info->set_partition_bitmaps(NULL);
   }
@@ -6318,6 +6319,7 @@ void ha_partition::partitions_optimizer_
                                                           uint *check_min_num)
 {
   *first= bitmap_get_first_set(&(m_part_info->read_partitions));
+  DBUG_ASSERT(*first != MY_BIT_NONE);
   *num_used_parts= bitmap_bits_set(&(m_part_info->read_partitions));
   *check_min_num= min(MAX_PARTS_FOR_OPTIMIZER_CALLS, *num_used_parts);
 }

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2011-02-21 16:27:53 +0000
+++ b/sql/sql_base.cc	2011-03-15 11:27:59 +0000
@@ -4974,6 +4974,7 @@ restart:
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   /* TODO: move this to prune_partitions() when implementing WL#4443. */
+  /* TODO: MOVE THIS TO ha_partition::store_lock/start_stmt ? */
   /* Prune partitions to avoid unneccesary locks */
   if (prune_partition_locks(*start))
   {
@@ -5448,6 +5449,68 @@ void open_or_lock_cleanup(THD *thd, cons
 
 
 /**
+  Open tables and open derived tables and prepares them.
+
+  @param         thd            Thread context.
+  @param[in,out] tables         List of tables to open/was opened.
+  @param[out]    table_counter  Number of tables opened.
+
+  @return Operation status
+    @retval false  OK
+    @retval true   Error
+*/
+
+bool open_tables_and_prepare_derived(THD *thd, TABLE_LIST **tables,
+                                     uint *table_counter)
+{
+  DBUG_ENTER("open_tables_and_prepare_derived");
+
+  if (open_tables(thd, tables, table_counter, 0))
+    DBUG_RETURN(true);
+
+  if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+      (thd->fill_derived_tables() &&
+       mysql_handle_derived(thd->lex, &mysql_derived_prepare_filling)))
+    DBUG_RETURN(true);
+
+  DBUG_RETURN(false);
+}
+
+
+/**
+  Lock tables and execute derived tables.
+
+  @param thd                 Thread context.
+  @param tables              List of tables to lock.
+  @param num_tables_to_lock  Number of tables to lock.
+
+  @return Operation status
+    @retval false  OK
+    @retval true   Error
+*/
+
+bool lock_tables_and_execute_derived(THD *thd, TABLE_LIST *tables,
+                                     uint num_tables_to_lock)
+{
+  DBUG_ENTER("lock_tables_and_execute_derived");
+
+  if (lock_tables(thd, tables, num_tables_to_lock, 0))
+    DBUG_RETURN(true);
+
+  if (thd->fill_derived_tables() &&
+      mysql_handle_derived(thd->lex, &mysql_derived_execute_filling))
+  {
+    mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+    DBUG_RETURN(true);
+  }
+  if (!thd->lex->describe)
+    mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+
+  DBUG_RETURN(false);
+}
+
+
+/**
   Open all tables in list, locks them and optionally process derived tables.
 
   @param thd		      Thread context.

=== modified file 'sql/sql_base.h'
--- a/sql/sql_base.h	2011-02-21 16:27:53 +0000
+++ b/sql/sql_base.h	2011-03-15 11:27:59 +0000
@@ -247,6 +247,10 @@ bool lock_table_names(THD *thd, TABLE_LI
 bool open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags,
                  Prelocking_strategy *prelocking_strategy);
 void open_or_lock_cleanup(THD *thd, const MDL_savepoint &mdl_savepoint);
+bool open_tables_and_prepare_derived(THD *thd, TABLE_LIST **tables,
+                                     uint *table_counter);
+bool lock_tables_and_execute_derived(THD *thd, TABLE_LIST *tables,
+                                     uint num_tables_to_lock);
 /* open_and_lock_tables with optional derived handling */
 bool open_and_lock_tables(THD *thd, TABLE_LIST *tables,
                           bool derived, uint flags,

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2011-02-02 10:32:21 +0000
+++ b/sql/sql_delete.cc	2011-03-15 11:27:59 +0000
@@ -68,6 +68,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   THD::enum_binlog_query_type query_type= THD::ROW_QUERY_TYPE;
   DBUG_ENTER("mysql_delete");
 
+  /* TODO: delay locking to be able to prune partitioning locks */
   if (open_and_lock_tables(thd, table_list, TRUE, 0))
     DBUG_RETURN(TRUE);
   if (!(table= table_list->table))
@@ -141,6 +142,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
        (!thd->is_current_stmt_binlog_format_row() &&
         !(table->triggers && table->triggers->has_delete_triggers())))
   {
+    /* TODO: add locking here since no pruning will be possible anyway */
     /* Update the table->file->stats.records number */
     table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
     ha_rows const maybe_deleted= table->file->stats.records;
@@ -181,6 +183,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     DBUG_RETURN(0);
   }
 #endif
+  /* TODO: add locking here */
   /* Update the table->file->stats.records number */
   table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
 

=== modified file 'sql/sql_derived.cc'
--- a/sql/sql_derived.cc	2011-02-21 16:27:53 +0000
+++ b/sql/sql_derived.cc	2011-03-15 11:27:59 +0000
@@ -32,14 +32,12 @@
 /*
   Call given derived table processor (preparing or filling tables)
 
-  SYNOPSIS
-    mysql_handle_derived()
-    lex                 LEX for this thread
-    processor           procedure of derived table processing
-
-  RETURN
-    FALSE  OK
-    TRUE   Error
+  @param lex        LEX for this thread
+  @param processor  procedure of derived table processing
+
+  @return Operation status
+    @retval FALSE  OK
+    @retval TRUE   Error
 */
 
 bool
@@ -242,24 +240,23 @@ exit:
 /*
   prepare fill derived table
 
-  SYNOPSIS
-    mysql_derived_prepare_filling()
-    thd			Thread handle
-    lex                 LEX for this thread
-    unit                node that contains all SELECT's for derived tables
-    orig_table_list     TABLE_LIST for the upper SELECT
-
-  IMPLEMENTATION
-    Derived table is resolved with temporary table. It is created based on the
-    queries defined. After temporary table is filled, if this is not EXPLAIN,
-    then the entire unit / node is deleted. unit is deleted if UNION is used
-    for derived table and node is deleted is it is a  simple SELECT.
-    If you use this function, make sure it's not called at prepare.
-    Due to evaluation of LIMIT clause it can not be used at prepared stage.
-
-  RETURN
-    FALSE  OK
-    TRUE   Error
+  @param thd              Thread handle
+  @param lex              LEX for this thread
+  @param unit             node that contains all SELECT's for derived tables
+  @param orig_table_list  TABLE_LIST for the upper SELECT
+
+  @details
+
+  Derived table is resolved with temporary table. It is created based on the
+  queries defined. This is the prepare part of filling the derived table.
+  It calls prepare and optimize_before_locking on the unit or node
+  (UNION or JOIN/SELECT). This call must only use SE API calls where the table
+  is only opened, not yet locked! This to ensure that prune_partitions can be
+  called and also prune away locks not needed for the derived tables.
+
+  @return Operation status
+    @retval FALSE  OK
+    @retval TRUE   Error
 */
 
 bool mysql_derived_prepare_filling(THD *thd, LEX *lex,
@@ -294,7 +291,7 @@ bool mysql_derived_prepare_filling(THD *
     {
       unit->set_limit(first_select);
       if (unit->select_limit_cnt == HA_POS_ERROR)
-	first_select->options&= ~OPTION_FOUND_ROWS;
+        first_select->options&= ~OPTION_FOUND_ROWS;
 
       lex->current_select= first_select;
       res= mysql_prepare_select(thd, &first_select->ref_pointer_array,
@@ -332,24 +329,24 @@ bool mysql_derived_prepare_filling(THD *
 /*
   execute fill derived table
 
-  SYNOPSIS
-    mysql_derived_execute_filling()
-    thd			Thread handle
-    lex                 LEX for this thread
-    unit                node that contains all SELECT's for derived tables
-    orig_table_list     TABLE_LIST for the upper SELECT
-
-  IMPLEMENTATION
-    Derived table is resolved with temporary table. It is created based on the
-    queries defined. After temporary table is filled, if this is not EXPLAIN,
-    then the entire unit / node is deleted. unit is deleted if UNION is used
-    for derived table and node is deleted is it is a  simple SELECT.
-    If you use this function, make sure it's not called at prepare.
-    Due to evaluation of LIMIT clause it can not be used at prepared stage.
-
-  RETURN
-    FALSE  OK
-    TRUE   Error
+  @param thd              Thread handle
+  @param lex              LEX for this thread
+  @param unit             node that contains all SELECT's for derived tables
+  @param orig_table_list  TABLE_LIST for the upper SELECT
+
+  @details
+
+  Derived table is resolved with temporary table. It is created based on the
+  queries defined. This step executes the filling of the prepared derived
+  table. After temporary table is filled, if this is not EXPLAIN,
+  then the entire unit / node is deleted. unit is deleted if UNION is used
+  for derived table and node is deleted is it is a  simple SELECT.
+  If you use this function, make sure it's not called at prepare.
+  Due to evaluation of LIMIT clause it can not be used at prepared stage.
+
+  @return Operation status
+    @retval FALSE  OK
+    @retval TRUE   Error
 */
 
 bool mysql_derived_execute_filling(THD *thd, LEX *lex,
@@ -376,7 +373,7 @@ bool mysql_derived_execute_filling(THD *
     {
       unit->set_limit(first_select);
       if (unit->select_limit_cnt == HA_POS_ERROR)
-	first_select->options&= ~OPTION_FOUND_ROWS;
+        first_select->options&= ~OPTION_FOUND_ROWS;
 
       lex->current_select= first_select;
       res= mysql_execute_select(thd, first_select,
@@ -403,77 +400,34 @@ bool mysql_derived_execute_filling(THD *
 /*
   fill derived table
 
-  SYNOPSIS
-    mysql_derived_filling()
-    thd			Thread handle
-    lex                 LEX for this thread
-    unit                node that contains all SELECT's for derived tables
-    orig_table_list     TABLE_LIST for the upper SELECT
-
-  IMPLEMENTATION
-    Derived table is resolved with temporary table. It is created based on the
-    queries defined. After temporary table is filled, if this is not EXPLAIN,
-    then the entire unit / node is deleted. unit is deleted if UNION is used
-    for derived table and node is deleted is it is a  simple SELECT.
-    If you use this function, make sure it's not called at prepare.
-    Due to evaluation of LIMIT clause it can not be used at prepared stage.
-
-  RETURN
-    FALSE  OK
-    TRUE   Error
+  @param thd                 Thread handle
+  @param lex                 LEX for this thread
+  @param unit                node that contains all SELECT's for derived tables
+  @param orig_table_list     TABLE_LIST for the upper SELECT
+
+  @details
+
+  Derived table is resolved with temporary table. It is created based on the
+  queries defined. After temporary table is filled, if this is not EXPLAIN,
+  then the entire unit / node is deleted. unit is deleted if UNION is used
+  for derived table and node is deleted is it is a  simple SELECT.
+  If you use this function, make sure it's not called at prepare.
+  Due to evaluation of LIMIT clause it can not be used at prepared stage.
+
+  @return Operation status
+    @retval FALSE  OK
+    @retval TRUE   Error
 */
 
 bool mysql_derived_filling(THD *thd, LEX *lex, TABLE_LIST *orig_table_list)
 {
-  TABLE *table= orig_table_list->table;
-  SELECT_LEX_UNIT *unit= orig_table_list->derived;
   bool res= FALSE;
 
   DBUG_ENTER("mysql_derived_filling");
-  /*check that table creation pass without problem and it is derived table */
-  if (table && unit)
-  {
-    SELECT_LEX *first_select= unit->first_select();
-    select_union *derived_result= orig_table_list->derived_result;
-    SELECT_LEX *save_current_select= lex->current_select;
-    if (unit->is_union())
-    {
-      // execute union without clean up
-      res= unit->exec();
-    }
-    else
-    {
-      unit->set_limit(first_select);
-      if (unit->select_limit_cnt == HA_POS_ERROR)
-	first_select->options&= ~OPTION_FOUND_ROWS;
-
-      lex->current_select= first_select;
-      res= mysql_select(thd, &first_select->ref_pointer_array,
-			first_select->table_list.first,
-			first_select->with_wild,
-			first_select->item_list, first_select->where,
-			(first_select->order_list.elements+
-			 first_select->group_list.elements),
-			first_select->order_list.first,
-			first_select->group_list.first,
-			first_select->having, (ORDER*) NULL,
-			(first_select->options | thd->variables.option_bits |
-			 SELECT_NO_UNLOCK),
-			derived_result, unit, first_select, NULL, true, NULL,
-                        NULL);
-    }
 
-    if (!res)
-    {
-      /*
-        Here we entirely fix both TABLE_LIST and list of SELECT's as
-        there were no derived tables
-      */
-      if (derived_result->flush())
-        res= TRUE;
-    }
-    lex->current_select= save_current_select;
-  }
+  res= mysql_derived_prepare_filling(thd, lex, orig_table_list);
+  if (!res)
+    res= mysql_derived_execute_filling(thd, lex, orig_table_list);
   DBUG_RETURN(res);
 }
 

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2011-02-21 16:27:53 +0000
+++ b/sql/sql_insert.cc	2011-03-15 11:27:59 +0000
@@ -716,18 +716,11 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
   {
     used_table_counter= &table_counter;
     mdl_savepoint= thd->mdl_context.mdl_savepoint();
-    if (open_tables(thd, &table_list, used_table_counter, 0))
+    if (open_tables_and_prepare_derived(thd, &table_list, used_table_counter))
     {
       open_or_lock_cleanup(thd, mdl_savepoint);
       DBUG_RETURN(TRUE);
     }
-    if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
-        (thd->fill_derived_tables() &&
-         mysql_handle_derived(thd->lex, &mysql_derived_prepare_filling)))
-    {
-      open_or_lock_cleanup(thd, mdl_savepoint);
-      DBUG_RETURN(true);
-    }
   }
   lock_type= table_list->lock_type;
 
@@ -816,20 +809,11 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
   if (used_table_counter)
   {
     /* lock the tables and handle derived tables */
-    if (lock_tables(thd, table_list, *used_table_counter, 0))
+    if (lock_tables_and_execute_derived(thd, table_list, *used_table_counter))
     {
       open_or_lock_cleanup(thd, mdl_savepoint);
       DBUG_RETURN(true);
     }
-    /* Handle derived tables */
-    if (thd->fill_derived_tables() &&
-        mysql_handle_derived(thd->lex, &mysql_derived_execute_filling))
-    {
-      mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
-      DBUG_RETURN(true);
-    }
-    if (!thd->lex->describe)
-      mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
   }
 
   /*

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2011-02-21 16:27:53 +0000
+++ b/sql/sql_parse.cc	2011-03-15 11:27:59 +0000
@@ -2419,11 +2419,8 @@ case SQLCOM_PREPARE:
       }
 
       mdl_savepoint= thd->mdl_context.mdl_savepoint();
-      if (!((res= open_tables(thd, &lex->query_tables, &counter, 0)) ||
-            (res= mysql_handle_derived(lex, &mysql_derived_prepare)) ||
-            (thd->fill_derived_tables() &&
-             (res= mysql_handle_derived(lex,
-                                        &mysql_derived_prepare_filling)))))
+      if (!(res= open_tables_and_prepare_derived(thd, &lex->query_tables,
+                                                 &counter)))
       {
         /* The table already exists */
         if (create_table->table || create_table->view)
@@ -2898,11 +2895,7 @@ end_with_restore_list:
     unit->set_limit(select_lex);
 
     mdl_savepoint= thd->mdl_context.mdl_savepoint();
-    if (!((res= open_tables(thd, &all_tables, &counter, 0)) ||
-          (res= mysql_handle_derived(lex, &mysql_derived_prepare)) ||
-          (thd->fill_derived_tables() &&
-           (res= mysql_handle_derived(lex,
-                                      &mysql_derived_prepare_filling)))))
+    if (!(res= open_tables_and_prepare_derived(thd, &all_tables, &counter)))
     {
       MYSQL_INSERT_SELECT_START(thd->query());
       /* Skip first table, which is the table we are inserting in */
@@ -2991,10 +2984,7 @@ end_with_restore_list:
 
     thd_proc_info(thd, "init");
     mdl_savepoint= thd->mdl_context.mdl_savepoint();
-    if ((res= open_tables(thd, &all_tables, &counter, 0)) ||
-        (res= mysql_handle_derived(lex, &mysql_derived_prepare)) ||
-        (thd->fill_derived_tables() &&
-         (res= mysql_handle_derived(lex, &mysql_derived_prepare_filling))))
+    if ((res= open_tables_and_prepare_derived(thd, &all_tables, &counter)))
     {
       open_or_lock_cleanup(thd, mdl_savepoint);
       break;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-02-21 16:27:53 +0000
+++ b/sql/sql_select.cc	2011-03-15 11:27:59 +0000
@@ -325,10 +325,10 @@ bool handle_select(THD *thd, select_resu
 		      select_lex->order_list.first,
 		      select_lex->group_list.first,
 		      select_lex->having,
-		      thd->lex->proc_list.first,
-		      select_lex->options | thd->variables.option_bits |
-                      setup_tables_done_option,
-		      result, unit, select_lex, tables_to_open_and_lock,
+                      thd->lex->proc_list.first,
+                      (select_lex->options | thd->variables.option_bits |
+                       setup_tables_done_option),
+                      result, unit, select_lex, tables_to_open_and_lock,
                       open_tables_is_done, tables_to_lock, open_mdl_savepoint);
   }
   DBUG_PRINT("info",("res: %d  report_error: %d", res,
@@ -3752,11 +3752,11 @@ err:
 
 bool
 mysql_select(THD *thd, Item ***rref_pointer_array,
-	     TABLE_LIST *tables, uint wild_num, List<Item> &fields,
-	     Item *conds, uint og_num,  ORDER *order, ORDER *group,
-	     Item *having, ORDER *proc_param, ulonglong select_options,
-	     select_result *result, SELECT_LEX_UNIT *unit,
-	     SELECT_LEX *select_lex, TABLE_LIST *tables_to_open_and_lock,
+             TABLE_LIST *tables, uint wild_num, List<Item> &fields,
+             Item *conds, uint og_num,  ORDER *order, ORDER *group,
+             Item *having, ORDER *proc_param, ulonglong select_options,
+             select_result *result, SELECT_LEX_UNIT *unit,
+             SELECT_LEX *select_lex, TABLE_LIST *tables_to_open_and_lock,
              bool open_tables_is_done, uint *tables_to_lock,
              MDL_savepoint *open_mdl_savepoint)
 {
@@ -3770,20 +3770,13 @@ mysql_select(THD *thd, Item ***rref_poin
   {
     DBUG_ASSERT(!tables_to_lock && !open_mdl_savepoint);
     mdl_savepoint= thd->mdl_context.mdl_savepoint();
-    /* Open the tables */
-    if (open_tables(thd, &tables_to_open_and_lock, &table_counter, 0))
+    if (open_tables_and_prepare_derived(thd, &tables_to_open_and_lock,
+                                        &table_counter))
     {
       open_or_lock_cleanup(thd, mdl_savepoint);
       DBUG_RETURN(true);
     }
     query_cache_store_query(thd, tables_to_open_and_lock);
-    if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
-        (thd->fill_derived_tables() &&
-         mysql_handle_derived(thd->lex, &mysql_derived_prepare_filling)))
-    {
-      open_or_lock_cleanup(thd, mdl_savepoint);
-      DBUG_RETURN(true);
-    }
   }
   else
   {
@@ -3810,29 +3803,15 @@ mysql_select(THD *thd, Item ***rref_poin
     DBUG_RETURN(true);
   }
 
-
   /* Lock all tables */
   if (!open_tables_is_done || tables_to_lock)
   {
     if (open_tables_is_done)
       table_counter= *tables_to_lock;
 
-    /* Lock the tables */
-    if (lock_tables(thd, tables_to_open_and_lock, table_counter, 0))
-    {
-      if (free_join)
-      {
-        thd_proc_info(thd, "end");
-        (void) select_lex->cleanup();
-      }
-      open_or_lock_cleanup(thd, mdl_savepoint);
-      DBUG_RETURN(true);
-    }
-    /* Handle derived tables */
-    if (thd->fill_derived_tables() &&
-        mysql_handle_derived(thd->lex, &mysql_derived_execute_filling))
+    if (lock_tables_and_execute_derived(thd, tables_to_open_and_lock,
+                                        table_counter))
     {
-      mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
       if (free_join)
       {
         thd_proc_info(thd, "end");
@@ -3841,8 +3820,6 @@ mysql_select(THD *thd, Item ***rref_poin
       open_or_lock_cleanup(thd, mdl_savepoint);
       DBUG_RETURN(true);
     }
-    if (!thd->lex->describe)
-      mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
   }
 
   if (mysql_execute_select(thd, select_lex, free_join, join))

=== modified file 'sql/sql_union.cc'
--- a/sql/sql_union.cc	2011-02-21 16:27:53 +0000
+++ b/sql/sql_union.cc	2011-03-15 11:27:59 +0000
@@ -44,19 +44,13 @@ bool mysql_union(THD *thd, select_result
     DBUG_ASSERT(!tables_to_lock && !open_mdl_savepoint);
     mdl_savepoint= thd->mdl_context.mdl_savepoint();
     /* Open the tables */
-    if (open_tables(thd, &tables_to_open_and_lock, &table_counter, 0))
+    if (open_tables_and_prepare_derived(thd, &tables_to_open_and_lock,
+                                        &table_counter))
     {
       open_or_lock_cleanup(thd, mdl_savepoint);
       DBUG_RETURN(true);
     }
     query_cache_store_query(thd, tables_to_open_and_lock);
-    if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
-        (thd->fill_derived_tables() &&
-         mysql_handle_derived(thd->lex, &mysql_derived_prepare_filling)))
-    {
-      open_or_lock_cleanup(thd, mdl_savepoint);
-      DBUG_RETURN(true);
-    }
   }
   else
   {
@@ -82,19 +76,9 @@ bool mysql_union(THD *thd, select_result
     if (open_tables_is_done)
       table_counter= *tables_to_lock;
 
-    /* Lock the tables */
-    if (lock_tables(thd, tables_to_open_and_lock, table_counter, 0))
+    if (lock_tables_and_execute_derived(thd, tables_to_open_and_lock,
+                                        table_counter))
       goto err;
-
-    /* Handle derived tables */
-    if (thd->fill_derived_tables() &&
-        mysql_handle_derived(thd->lex, &mysql_derived_execute_filling))
-    {
-      mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
-      goto err;
-    }
-    if (!thd->lex->describe)
-      mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
   }
 
   res= unit->exec();
@@ -586,7 +570,7 @@ bool st_select_lex_unit::exec()
           (select_limit_cnt == HA_POS_ERROR || sl->braces) ?
           sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
 
-	saved_error= sl->join->optimize_after_locking();
+          saved_error= sl->join->optimize_after_locking();
       }
       if (!saved_error)
       {

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2011-02-21 16:27:53 +0000
+++ b/sql/sql_update.cc	2011-03-15 11:27:59 +0000
@@ -293,6 +293,7 @@ int mysql_update(THD *thd,
     /* convert to multiupdate */
     DBUG_RETURN(2);
   }
+  /* TODO: Check if possible to use open_tables_and_prepare_derived */
   if (mysql_handle_derived(thd->lex, &mysql_derived_prepare))
     DBUG_RETURN(1);
 
@@ -388,17 +389,9 @@ int mysql_update(THD *thd,
   }
 #endif
 
-  if (lock_tables(thd, table_list, table_count, 0))
+  if (lock_tables_and_execute_derived(thd, table_list, table_count))
     DBUG_RETURN(1);
 
-  if (thd->fill_derived_tables() &&
-      mysql_handle_derived(thd->lex, &mysql_derived_filling))
-  {
-    mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
-    DBUG_RETURN(1);
-  }
-  mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
-
   /* Update the table->file->stats.records number */
   table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
 
@@ -1041,6 +1034,7 @@ int mysql_multi_update_prepare(THD *thd)
     keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE
     and global read lock.
   */
+  /* TODO: Check if possible to use open_tables_and_prepare_derived */
   if ((original_multiupdate &&
        open_tables(thd, &table_list, &table_count,
                    (thd->stmt_arena->is_stmt_prepare() ?
@@ -1158,6 +1152,7 @@ int mysql_multi_update_prepare(THD *thd)
   }
 
   /* now lock and fill tables */
+  /* TOOD: check if possible to use lock_tables_and_execute_derived */
   if (!thd->stmt_arena->is_stmt_prepare() &&
       lock_tables(thd, table_list, table_count, 0))
   {
@@ -1200,6 +1195,7 @@ int mysql_multi_update_prepare(THD *thd)
   */
   lex->select_lex.exclude_from_table_unique_test= FALSE;
  
+  /* TOOD: check if possible to use lock_tables_and_execute_derived */
   if (thd->fill_derived_tables() &&
       mysql_handle_derived(lex, &mysql_derived_filling))
   {


Attachment: [text/bzr-bundle] bzr/mattias.jonsson@oracle.com-20110315112759-8xm6tym77f8qclhm.bundle
Thread
bzr commit into mysql-trunk branch (mattias.jonsson:3693) Mattias Jonsson15 Mar