List:Commits« Previous MessageNext Message »
From:Patrick Galbraith Date:January 10 2006 7:29am
Subject:bk commit into 5.1 tree (patg:1.2017)
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of patg. When patg 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
  1.2017 06/01/09 23:29:29 patg@stripped +14 -0
  WL# 2682 Consolidated patch.
  
  Implement 
  
  SELECT [columns] FROM [tablename] [PARTITION|SUBPARTITION] (partition name)
  
  Please see https://intranet.mysql.com/worklog/Server-Sprint/?tid=2682
  for Worklog description. See comments for each file as to what was changed.

  sql/opt_range.cc
    1.193 06/01/09 23:29:22 patg@stripped +8 -4
    WL# 2682 - Do not review these 

  mysql-test/t/partition_select.test
    1.1 06/01/09 23:28:33 patg@stripped +144 -0
    WL# 2682
    
    New partition select tests

  mysql-test/r/partition_select.result
    1.1 06/01/09 23:28:33 patg@stripped +322 -0
    WL# 2682
    
    Test results

  sql/table.h
    1.123 06/01/09 23:28:33 patg@stripped +3 -1
    WL# 2682
    
    Added partition_name to st_table struct.

  sql/sql_yacc.yy
    1.436 06/01/09 23:28:33 patg@stripped +13 -4
    WL# 2682
    
    Added opt_use_partition declaration and definition. Added partition_name to 
    add_table_to_list (modified args as well)

  sql/sql_parse.cc
    1.506 06/01/09 23:28:33 patg@stripped +3 -1
    WL# 2682
    
    Added partition_name to add_table_to_list, set partition name if defined.

  mysql-test/t/partition_select.test
    1.0 06/01/09 23:28:33 patg@stripped +0 -0
    BitKeeper file /home/patg/mysql-build/mysql-5.1-stock/mysql-test/t/partition_select.test

  mysql-test/r/partition_select.result
    1.0 06/01/09 23:28:33 patg@stripped +0 -0
    BitKeeper file /home/patg/mysql-build/mysql-5.1-stock/mysql-test/r/partition_select.result

  sql/sql_lex.h
    1.214 06/01/09 23:28:32 patg@stripped +4 -2
    WL# 2682
    
    Added partition_name to add_table_to_list

  sql/sql_lex.cc
    1.170 06/01/09 23:28:32 patg@stripped +2 -1
    WL# 2682
    
    Added partition_name to add table to list

  sql/sql_base.cc
    1.295 06/01/09 23:28:32 patg@stripped +3 -0
    WL# 2682
    
    Added setting of table->partition_name to table_list->partition_name

  sql/share/errmsg.txt
    1.66 06/01/09 23:28:32 patg@stripped +23 -0
    WL# 2682 
    
    Added "ER_NO_SUCH_PARTITION"

  sql/handler.h
    1.176 06/01/09 23:28:32 patg@stripped +9 -3
    WL #2682
    
    1. typdef of Partition_Element_Iterator
    2. Added selected_partition string and partition_names HASH 

  sql/ha_partition.h
    1.8 06/01/09 23:28:32 patg@stripped +14 -0
    WL# 2682
    
    1. New struct to hold partition name, and params
    2. Class var current_partition_index
    3. Declaration of set_specific_partition(), populate_partition_name_hash(), 
    get_partition_index()

  sql/ha_partition.cc
    1.19 06/01/09 23:28:32 patg@stripped +350 -152
    WL# 2682 Consolidated patch
    
    1. Added HASH to store partition names
    2. Added method to return partition number given partition name
    3. Added method to populate partition names hash with partitions and subpartitions
    4. Added code to throw error if partition name doesn't exist within table
    5. Added method to set the m_part_info->used_partitions bitmap to the partition
    number that was specified in the query
    6. modified rnd_next to iterate over only the partition (if specified)
    using current_partition_index. (rnd_end as well)
    7. Modified rnd_init to init only specified partition
    8. Modified rnd_end to only ha_rnd_end only 
    9. Modified all do {} while loops that iterate over partitions to only iterate
    over a specified partition (when specified)

  include/my_sys.h
    1.185 06/01/09 23:28:32 patg@stripped +1 -0
    WL #2682
    
    Map def

# 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:	patg
# Host:	govinda.site
# Root:	/home/patg/mysql-build/mysql-5.1-stock

--- 1.184/include/my_sys.h	2006-01-04 01:36:34 -08:00
+++ 1.185/include/my_sys.h	2006-01-09 23:28:32 -08:00
@@ -846,6 +846,7 @@
 #define PROT_WRITE       2
 #define MAP_NORESERVE    0
 #define MAP_SHARED       0x0001
+#define MAP_NORESERVE    0x0002
 #define MAP_NOSYNC       0x0800
 #define MAP_FAILED       ((void *)-1)
 #define MS_SYNC          0x0000

--- 1.175/sql/handler.h	2006-01-02 06:40:59 -08:00
+++ 1.176/sql/handler.h	2006-01-09 23:28:32 -08:00
@@ -512,6 +512,7 @@
   handlerton *engine_type;
   enum partition_state part_state;
   uint16 nodegroup_id;
+  int index;
   
   partition_element()
   : part_max_rows(0), part_min_rows(0), partition_name(NULL),
@@ -526,6 +527,8 @@
   ~partition_element() {}
 };
 
+typedef List_iterator<partition_element> Partition_Element_Iterator;
+
 typedef struct {
   longlong list_value;
   uint partition_id;
@@ -547,8 +550,10 @@
 
   List<char> part_field_list;
   List<char> subpart_field_list;
-  
-  /* 
+  char *selected_partition;
+  HASH partition_names;
+
+  /*
     If there is no subpartitioning, use only this func to get partition ids.
     If there is subpartitioning, use the this func to get partition id when
     you have both partition and subpartition fields.
@@ -598,7 +603,8 @@
     longlong *range_int_array;
     LIST_PART_ENTRY *list_array;
   };
-  char* part_info_string;
+
+  char *part_info_string;
 
   char *part_func_string;
   char *subpart_func_string;

--- 1.192/sql/opt_range.cc	2006-01-06 10:34:56 -08:00
+++ 1.193/sql/opt_range.cc	2006-01-09 23:29:22 -08:00
@@ -2280,13 +2280,13 @@
 
   if (!part_info)
     DBUG_RETURN(FALSE); /* not a partitioned table */
-  
+
   if (!pprune_cond)
   {
     mark_all_partitions_as_used(part_info);
     DBUG_RETURN(FALSE);
   }
-  
+
   PART_PRUNE_PARAM prune_param;
   MEM_ROOT alloc;
   RANGE_OPT_PARAM  *range_par= &prune_param.range_param;
@@ -2303,7 +2303,7 @@
     free_root(&alloc,MYF(0));		// Return memory & allocator
     DBUG_RETURN(FALSE);
   }
-  
+
   range_par->thd= thd;
   range_par->table= table;
   /* range_par->cond doesn't need initialization */
@@ -2806,7 +2806,11 @@
 
 static void mark_all_partitions_as_used(partition_info *part_info)
 {
-  bitmap_set_all(&part_info->used_partitions);
+  DBUG_ENTER("mark_all_partitions_as_used");
+  DBUG_PRINT("info", ("used_partitions %lx", part_info->used_partitions));
+  // REMOVE AFTER PSERGEY FIX!!!!
+  //bitmap_set_all(&part_info->used_partitions);
+  DBUG_VOID_RETURN;
 }
 
 

--- 1.294/sql/sql_base.cc	2006-01-06 10:34:57 -08:00
+++ 1.295/sql/sql_base.cc	2006-01-09 23:28:32 -08:00
@@ -25,6 +25,7 @@
 #include <m_ctype.h>
 #include <my_dir.h>
 #include <hash.h>
+#include <ha_partition.h>
 #ifdef	__WIN__
 #include <io.h>
 #endif
@@ -1975,6 +1976,7 @@
   if (table->timestamp_field)
     table->timestamp_field_type= table->timestamp_field->get_auto_set_type();
   table_list->updatable= 1; // It is not derived table nor non-updatable VIEW
+  table->partition_name= table_list->partition_name;
   DBUG_ASSERT(table->key_read == 0);
   DBUG_RETURN(table);
 }
@@ -5131,6 +5133,7 @@
                   Item **conds, TABLE_LIST **leaves, bool select_insert)
 {
   uint tablenr= 0;
+  int partition_set=0;
   DBUG_ENTER("setup_tables");
 
   context->table_list= context->first_name_resolution_table= tables;

--- 1.169/sql/sql_lex.cc	2005-12-12 11:55:13 -08:00
+++ 1.170/sql/sql_lex.cc	2006-01-09 23:28:32 -08:00
@@ -1389,7 +1389,8 @@
 						  thr_lock_type flags,
 						  List<String> *use_index,
 						  List<String> *ignore_index,
-                                                  LEX_STRING *option)
+              LEX_STRING *option,
+              LEX_STRING *partition_name)
 {
   return 0;
 }

--- 1.213/sql/sql_lex.h	2005-12-26 03:53:40 -08:00
+++ 1.214/sql/sql_lex.h	2006-01-09 23:28:32 -08:00
@@ -366,7 +366,8 @@
 					thr_lock_type flags= TL_UNLOCK,
 					List<String> *use_index= 0,
 					List<String> *ignore_index= 0,
-                                        LEX_STRING *option= 0);
+          LEX_STRING *option= 0,
+          LEX_STRING *partition_name= 0);
   virtual void set_lock_for_tables(thr_lock_type lock_type) {}
 
   friend class st_select_lex_unit;
@@ -607,7 +608,8 @@
 				thr_lock_type flags= TL_UNLOCK,
 				List<String> *use_index= 0,
 				List<String> *ignore_index= 0,
-                                LEX_STRING *option= 0);
+        LEX_STRING *option= 0,
+        LEX_STRING *partition_name= 0);
   TABLE_LIST* get_table_list();
   bool init_nested_join(THD *thd);
   TABLE_LIST *end_nested_join(THD *thd);

--- 1.505/sql/sql_parse.cc	2006-01-07 05:49:41 -08:00
+++ 1.506/sql/sql_parse.cc	2006-01-09 23:28:33 -08:00
@@ -5889,7 +5889,8 @@
 					     thr_lock_type lock_type,
 					     List<String> *use_index_arg,
 					     List<String> *ignore_index_arg,
-                                             LEX_STRING *option)
+               LEX_STRING *option,
+               LEX_STRING *partition_name)
 {
   register TABLE_LIST *ptr;
   TABLE_LIST *previous_table_ref; /* The table preceding the current one. */
@@ -5945,6 +5946,7 @@
     ptr->db= thd->strdup(ptr->db);
 
   ptr->alias= alias_str;
+  ptr->partition_name= partition_name ? partition_name->str : NULL;
   if (lower_case_table_names && table->table.length)
     my_casedn_str(files_charset_info, table->table.str);
   ptr->table_name=table->table.str;

--- 1.435/sql/sql_yacc.yy	2005-12-26 01:39:52 -08:00
+++ 1.436/sql/sql_yacc.yy	2006-01-09 23:28:33 -08:00
@@ -702,7 +702,7 @@
         sp_opt_label BIN_NUM label_ident
 
 %type <lex_str_ptr>
-	opt_table_alias opt_fulltext_parser
+	opt_table_alias opt_fulltext_parser opt_use_partition
 
 %type <table>
 	table_ident table_ident_nodb references xid
@@ -5951,6 +5951,13 @@
 	| CROSS JOIN_SYM	{}
 	;
 
+opt_use_partition:
+	/* empty */	{ $$=0; }
+	| PARTITION_SYM '(' ident ')' 
+		{ $$= &$3; }
+	| SUBPARTITION_SYM '(' ident ')' 
+		{ $$= &$3; };
+		
 /* Warning - may return NULL in case of incomplete SELECT */
 table_factor:
 	{
@@ -5958,15 +5965,17 @@
 	  sel->use_index_ptr=sel->ignore_index_ptr=0;
 	  sel->table_join_options= 0;
 	}
-        table_ident opt_table_alias opt_key_definition
+        table_ident opt_use_partition opt_table_alias opt_key_definition
 	{
 	  LEX *lex= Lex;
+
 	  SELECT_LEX *sel= lex->current_select;
-	  if (!($$= sel->add_table_to_list(lex->thd, $2, $3,
+	  if (!($$= sel->add_table_to_list(lex->thd, $2, $4,
 					   sel->get_table_join_options(),
 					   lex->lock_option,
 					   sel->get_use_index(),
-					   sel->get_ignore_index())))
+					   sel->get_ignore_index(),
+					   0, $3)))
 	    YYABORT;
           sel->add_joined_table($$);
 	}

--- 1.122/sql/table.h	2005-12-26 03:53:41 -08:00
+++ 1.123/sql/table.h	2006-01-09 23:28:33 -08:00
@@ -300,7 +300,8 @@
   FILESORT_INFO sort;
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   partition_info *part_info;            /* Partition related information */
-  bool no_partitions_used; /* If true, all partitions have been pruned away */
+  const char *partition_name;           /* name of the specific partition on the query */
+  bool no_partitions_used;              /* If true, all partitions have been pruned away */
 #endif
 
   bool fill_item_list(List<Item> *item_list) const;
@@ -487,6 +488,7 @@
   /* link in a global list of all queries tables */
   struct st_table_list *next_global, **prev_global;
   char		*db, *alias, *table_name, *schema_table_name;
+  const char *partition_name;
   char          *option;                /* Used by cache index  */
   Item		*on_expr;		/* Used with outer join */
   /*

--- 1.65/sql/share/errmsg.txt	2005-12-24 09:06:18 -08:00
+++ 1.66/sql/share/errmsg.txt	2006-01-09 23:28:32 -08:00
@@ -5727,6 +5727,29 @@
 	eng "Incorrect %-.32s value: '%-.128s'"
 ER_NO_PARTITION_FOR_GIVEN_VALUE
 	eng "Table has no partition for value %ld"
+ER_NO_SUCH_PARTITION 
+	cze "partion '%-.64s.%s' neexistuje"
+	dan "partition '%-.64s' eksisterer ikke"
+	nla "partition '%-.64s' bestaat niet"
+	eng "partition '%-.64s' doesn't exist"
+	est "partition '%-.64s' ei eksisteeri"
+	fre "La partition '%-.64s' n'existe pas"
+	ger "Die partition '%-.64s' existiert nicht"
+	hun "A '%-.64s' partition nem letezik"
+	ita "La tabella particione '%-.64s' non esiste"
+	jpn "Partition '%-.64s' doesn't exist"
+	nor "Partition '%-.64s' doesn't exist"
+	norwegian-ny "Partition '%-.64s' doesn't exist"
+	pol "Partition '%-.64s' doesn't exist"
+	rum "Partition '%-.64s' nu exista"
+	serbian "Partition '%-.64s' ne postoji"
+	slo "Partition '%-.64s' doesn't exist"
+	spa "Particion '%-.64s' no existe"
+	swe "Det finns ingen partition som heter '%-.64s'"
 ER_BINLOG_ROW_LOGGING_FAILED
 	eng "Writing one row to the row-based binary log failed"
 ER_BINLOG_ROW_WRONG_TABLE_DEF

--- 1.18/sql/ha_partition.cc	2005-12-30 20:53:54 -08:00
+++ 1.19/sql/ha_partition.cc	2006-01-09 23:28:32 -08:00
@@ -62,6 +62,25 @@
 static PARTITION_SHARE *get_share(const char *table_name, TABLE * table);
 #endif
 
+static byte *partition_names_get_key(partition_element *partition_element, uint *length,
+			       my_bool not_used __attribute__ ((unused)))
+{
+  *length= strlen(partition_element->partition_name);
+  return (byte *) partition_element->partition_name;
+}
+
+int ha_partition::get_partition_index(const char* partition_name)
+{
+  partition_element *el= (partition_element*)hash_search(
+	                                 &(m_part_info->partition_names), 
+	                                 (byte*)partition_name, 
+									 strlen(partition_name));
+  if (el == NULL)
+    return -1;
+  return el->index;
+}
+
+
 /****************************************************************************
                 MODULE create/delete handler object
 ****************************************************************************/
@@ -157,6 +176,7 @@
   m_top_entry= NO_CURRENT_PART_ID;
   m_rec_length= 0;
   m_last_part= 0;
+  current_partition_index= MY_BIT_NONE;
   m_rec0= 0;
   m_curr_key_info= 0;
   /*
@@ -399,7 +419,6 @@
   DBUG_ENTER("ha_partition::create");
 
   strmov(t_name, name);
-//  *fn_ext(t_name)= 0;
   DBUG_ASSERT(*fn_rext((char*)name) == '\0');
   if (del_ren_cre_table(t_name, NULL, table_arg, create_info))
   {
@@ -720,7 +739,9 @@
     DBUG_PRINT("info", ("engine_type: %u", m_engine_array[i]));
   }
   m_file[m_tot_parts]= 0;
-  /* For the moment we only support partition over the same table engine */
+  /*
+    For the moment we only support partition over the same table engine
+  */
   if (m_engine_array[0] == &myisam_hton)
   {
     DBUG_PRINT("info", ("MyISAM"));
@@ -853,6 +874,46 @@
   DBUG_RETURN(TRUE);
 }
 
+/*
+ * populate_partition_name_hash
+ * This function will iterate over the list of partition elements and insert them
+ * into a hash based on partition name.  This hash sits on the partition info object
+ * but is used by the handler for determining if a partition specified in a query
+ * actually exists.
+ * RETURNS:  1 on error, 0 if successful
+*/
+int ha_partition::populate_partition_name_hash()
+{
+  List_iterator_fast <partition_element> part_it(m_part_info->partitions);
+
+  (void) hash_init(&(m_part_info->partition_names), system_charset_info, 32, 0, 0,
+                   (hash_get_key) partition_names_get_key, 0, 0);
+
+  int index= 0;
+  partition_element *el;
+  while ((el= part_it++) != NULL)
+  {
+    bool has_sub_parts= is_sub_partitioned(m_part_info);
+
+    if (! has_sub_parts)
+	    el->index= index++;
+    if (my_hash_insert(&(m_part_info->partition_names), (byte*)el))
+      return 1;
+    if (!has_sub_parts)
+      continue;
+    List_iterator_fast <partition_element> sub_part_it(el->subpartitions);
+    partition_element *subel;
+    while ((subel= sub_part_it++) != NULL)
+    {
+      subel->index= index++;
+      if (my_hash_insert(&(m_part_info->partition_names), (byte*)subel))
+        return 1;
+    }
+  }
+  return 0;
+}
+
+
 /****************************************************************************
                 MODULE open/close object
 ****************************************************************************/
@@ -868,11 +929,11 @@
 
 int ha_partition::open(const char *name, int mode, uint test_if_locked)
 {
-  int error;
   char name_buff[FN_REFLEN];
   char *name_buffer_ptr= m_name_buffer_ptr;
+  int error;
+  uint alloc_len,i, j, part_num_counter= 0;
   handler **file;
-  uint alloc_len;
   DBUG_ENTER("ha_partition::open");
 
   ref_length= 0;
@@ -908,6 +969,15 @@
       m_start_key.key= (const byte*)ptr;
     }
   }
+
+  /* Initialze the bitmap we use to determine what partitions are used */
+  bitmap_init(&(m_part_info->used_partitions), NULL, m_tot_parts, TRUE);
+  bitmap_set_all(&(m_part_info->used_partitions));
+
+  /* Here we stick all of our partition element objects in a hash by name */
+  if (populate_partition_name_hash())
+    goto err_handler;
+
   file= m_file;
   do
   {
@@ -919,6 +989,7 @@
     name_buffer_ptr+= strlen(name_buffer_ptr) + 1;
     set_if_bigger(ref_length, ((*file)->ref_length));
   } while (*(++file));
+
   /*
     Add 2 bytes for partition id in position ref length.
     ref_length=max_in_all_partitions(ref_length) + PARTITION_BYTES_IN_POS
@@ -936,6 +1007,9 @@
   if ((error= init_queue(&queue, m_tot_parts, (uint) PARTITION_BYTES_IN_POS,
                          0, key_rec_cmp, (void*)this)))
     goto err_handler;
+
+
+
   /*
     Some handlers update statistics as part of the open call. This will in
     some cases corrupt the statistics of the partition handler and thus
@@ -964,19 +1038,21 @@
 
 int ha_partition::close(void)
 {
+  int i;
   handler **file;
+  partition_element *part_elem;
+  List_iterator_fast <partition_element> part_it(m_part_info->partitions);
   DBUG_ENTER("ha_partition::close");
 
   delete_queue(&queue);
+  bitmap_free(&(m_part_info->used_partitions));
   file= m_file;
-  do
-  {
-    (*file)->close();
-  } while (*(++file));
+  do { (*file)->close(); } while (*(++file));
+  hash_free(&(m_part_info->partition_names));
+
   DBUG_RETURN(0);
 }
 
-
 /****************************************************************************
                 MODULE start/end statement
 ****************************************************************************/
@@ -1008,18 +1084,32 @@
 int ha_partition::external_lock(THD *thd, int lock_type)
 {
   uint error;
-  handler **file;
   DBUG_ENTER("ha_partition::external_lock");
-  file= m_file;
+
+  /* if this query specifies a partition, then set the bitmap accordingly */
+  if (table->partition_name != NULL)
+  {
+    if ((error= set_specific_partition(table->partition_name)))
+    {
+      DBUG_RETURN(error);
+    }
+  }
+  /* REMOVE when psergey pushes his fix */
+  else
+  {
+    bitmap_set_all(&(m_part_info->used_partitions));
+  }
+
+  handler **file= m_file;
   do
   {
     if ((error= (*file)->external_lock(thd, lock_type)))
     {
-      if (lock_type != F_UNLCK)
-	goto err_handler;
+      if (F_UNLCK != lock_type)
+        goto err_handler;
     }
   } while (*(++file));
-  m_lock_type= lock_type;                       // For the future (2009?)
+  m_lock_type= lock_type;             // for the future (2009?)
   DBUG_RETURN(0);
 
 err_handler:
@@ -1076,13 +1166,66 @@
   DBUG_RETURN(to);
 }
 
+/*
+  set_specific_partition
+  This function takes the given partition name and checks it against the list
+  of partitions and subpartitions, setting the current_partition_index and
+  start_part equal to the chosen 
+*/
+int ha_partition::set_specific_partition(const char *partition_name)
+{
+  int error;
+  DBUG_ENTER("ha_partition::set_specific_partition");
+  partition_element *el= (partition_element*)hash_search(
+	                                 &(m_part_info->partition_names), 
+	                                 (byte*)partition_name, 
+									                  strlen(partition_name));
+  if (el == NULL)
+  {
+    error= my_error(ER_NO_SUCH_PARTITION, MYF(0), partition_name);
+    DBUG_RETURN(error);
+  }
+
+  DBUG_PRINT("info", ("selected partition %s is in table", partition_name));
+  
+  if (is_sub_partitioned(m_part_info) && (el->subpartitions.elements > 0))
+  {
+    List_iterator<partition_element> sub_part_it(el->subpartitions);
+    partition_element *sub_el;
+    while ((sub_el= sub_part_it++) != NULL)
+    {
+      bitmap_set_bit(&(m_part_info->used_partitions), sub_el->index);
+    }
+  }
+  else
+    bitmap_set_bit(&(m_part_info->used_partitions), el->index);
+    
+  DBUG_RETURN(0);
+}
+
 
 int ha_partition::start_stmt(THD *thd, thr_lock_type lock_type)
 {
+  int i= 0;
   int error= 0;
-  handler **file;
   DBUG_ENTER("ha_partition::start_stmt");
-  file= m_file;
+
+  /* if this query specifies a partition, then set the bitmap accordingly */
+  if (table->partition_name != NULL)
+  {
+    if ((error= set_specific_partition(table->partition_name)))
+    {
+      DBUG_RETURN(error);
+    }
+  }
+  /* REMOVE when psergey pushes his fix */
+  else
+  {
+    bitmap_set_all(&(m_part_info->used_partitions));
+  }
+
+
+  handler **file= m_file;
   do
   {
     if ((error= (*file)->start_stmt(thd, lock_type)))
@@ -1300,8 +1443,9 @@
   file= m_file;
   do
   {
-    if ((error= (*file)->delete_all_rows()))
-      DBUG_RETURN(error);
+    if (_bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
+      if ((error= (*file)->delete_all_rows()))
+        DBUG_RETURN(error);
   } while (*(++file));
   DBUG_RETURN(0);
 }
@@ -1323,7 +1467,8 @@
   file= m_file;
   do
   {
-    (*file)->start_bulk_insert(rows);
+    if (_bitmap_is_set(&(m_part_info->used_partitions),(file - m_file)))
+      (*file)->start_bulk_insert(rows);
   } while (*(++file));
   DBUG_VOID_RETURN;
 }
@@ -1339,9 +1484,10 @@
   do
   {
     int tmp;
-    /* We want to execute end_bulk_insert() on all handlers */
-    if ((tmp= (*file)->end_bulk_insert()))
-      error= tmp;
+    int i= file - m_file;
+    if (_bitmap_is_set(&(m_part_info->used_partitions), i))
+      if ((tmp= (*file)->end_bulk_insert()))
+        error= tmp;
   } while (*(++file));
   DBUG_RETURN(error);
 }
@@ -1374,82 +1520,95 @@
 int ha_partition::rnd_init(bool scan)
 {
   int error;
+  uint i= 0;
   handler **file;
   DBUG_ENTER("ha_partition::rnd_init");
 
   include_partition_fields_in_used_fields();
+  
+  /*
+    we are going to be scanning through rnd_next so we should eliminate as many
+    of the partitions as possible.
+  */
   if (scan)
   {
     /*
       rnd_end() is needed for partitioning to reset internal data if scan
       is already in use
     */
-
     rnd_end();
-    if (partition_scan_set_up(rec_buf(0), FALSE))
-    {
-      /*
-        The set of partitions to scan is empty. We return success and return
-        end of file on first rnd_next.
-      */
-      DBUG_RETURN(0);
-    }
+    partition_scan_set_up(rec_buf(0), FALSE);
+  }
+
+  /* now we see what the index of our first important partition is */
+  current_partition_index = bitmap_get_first_set(&(m_part_info->used_partitions));
+  if (MY_BIT_NONE == current_partition_index)
+  {
     /*
-      We will use the partition set in our scan, using the start and stop
-      partition and checking each scan before start dependent on bittfields.
+      The set of partitions to scan is empty. We return success and return
+      end of file on first rnd_next.
     */
-    late_extra_cache(m_part_spec.start_part);
-    DBUG_PRINT("info", ("rnd_init on partition %d",m_part_spec.start_part));
-    error= m_file[m_part_spec.start_part]->ha_rnd_init(1);
-    m_scan_value= 1;                            // Scan active
-    if (error)
-      m_scan_value= 2;                          // No scan active
-    DBUG_RETURN(error);
+    DBUG_RETURN(0);
   }
-  file= m_file;
-  do
+
+  /* we have a partition and we are scanning with rnd_next so we bump our cache */
+  if (scan)
+    late_extra_cache(current_partition_index);
+
+  DBUG_PRINT("info", ("rnd_init on partition %d", current_partition_index));
+
+  for (i= current_partition_index; i < m_tot_parts; i++)
   {
-    if ((error= (*file)->ha_rnd_init(0)))
-      goto err;
-  } while (*(++file));
-  m_scan_value= 0;
+    if (_bitmap_is_set(&(m_part_info->used_partitions), i))
+      if ((error= m_file[i]->ha_rnd_init(scan)))
+        goto err;
+    if (scan) break;
+  }
+  m_scan_value= scan ? 1 : 0;
+  DBUG_PRINT("info", ("m_scan_value=%d", m_scan_value));
   DBUG_RETURN(0);
 
 err:
-  while (file--)
-    (*file)->ha_rnd_end();
+  while (i >= current_partition_index)
+  {
+    if (_bitmap_is_set(&(m_part_info->used_partitions), i))
+	  m_file[i--]->ha_rnd_end();
+  }
+  m_scan_value= scan ? 2 : 0;
   DBUG_RETURN(error);
 }
 
 
 int ha_partition::rnd_end()
 {
+  int i= 0;
   handler **file;
   DBUG_ENTER("ha_partition::rnd_end");
-  switch (m_scan_value) {
-  case 2:                                       // Error
-    break;
-  case 1:                                       // Table scan
-    if (m_part_spec.start_part != NO_CURRENT_PART_ID)
-    {
-      late_extra_no_cache(m_part_spec.start_part);
-      m_file[m_part_spec.start_part]->ha_rnd_end();
-    }
-    break;
-  case 0:
-    file= m_file;
-    do
-    {
-      (*file)->ha_rnd_end();
-    } while (*(++file));
-    break;
+  switch (m_scan_value) 
+  {
+    case 2:                                       // Error
+      break;
+    case 1:
+      if (MY_BIT_NONE != current_partition_index)         // Table scan
+      {
+        late_extra_no_cache(current_partition_index);
+        m_file[current_partition_index]->ha_rnd_end();
+      }
+      break;
+    case 0:
+      int i=0;
+      file= m_file;
+      do
+      {
+        if (_bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
+          (*file)->ha_rnd_end();
+      } while (*(++file));
+      break;
   }
-  m_part_spec.start_part= NO_CURRENT_PART_ID;
   m_scan_value= 2;
   DBUG_RETURN(0);
 }
 
-
 /*
   read next row during full table scan (scan in random row order)
 
@@ -1469,14 +1628,14 @@
 int ha_partition::rnd_next(byte *buf)
 {
   DBUG_ASSERT(m_scan_value);
-  uint part_id= m_part_spec.start_part;         // Cache of this variable
-  handler *file= m_file[part_id];
+  handler *file;
   int result= HA_ERR_END_OF_FILE;
   DBUG_ENTER("ha_partition::rnd_next");
+  DBUG_PRINT("info", ("m_scan_value %d", m_scan_value));
 
   DBUG_ASSERT(m_scan_value == 1);
 
-  if (part_id > m_part_spec.end_part)
+  if (MY_BIT_NONE == current_partition_index)
   {
     /*
       The original set of partitions to scan was empty and thus we report
@@ -1484,44 +1643,53 @@
     */
     goto end;
   }
+  
+  file= m_file[current_partition_index];
+  
   while (TRUE)
   {
-    if ((result= file->rnd_next(buf)))
+    int result= file->rnd_next(buf);
+    if (!result)
     {
-      if (result == HA_ERR_RECORD_DELETED)
-        continue;                               // Probably MyISAM
+      table->status= 0;
+      DBUG_RETURN(0);
+    }
 
-      if (result != HA_ERR_END_OF_FILE)
-        break;                                  // Return error
+    /*
+      if we get here, then the current partition rnd_next returned failure
+    */
+    if (result == HA_ERR_RECORD_DELETED)
+      continue;                               // Probably MyISAM
 
-      /* End current partition */
-      late_extra_no_cache(part_id);
-      DBUG_PRINT("info", ("rnd_end on partition %d", part_id));
-      if ((result= file->ha_rnd_end()))
-        break;
-      /* Shift to next partition */
-      if (++part_id > m_part_spec.end_part)
-      {
-        result= HA_ERR_END_OF_FILE;
-        break;
-      }
-      file= m_file[part_id];
-      DBUG_PRINT("info", ("rnd_init on partition %d", part_id));
-      if ((result= file->ha_rnd_init(1)))
-        break;
-      late_extra_cache(part_id);
+    if (result != HA_ERR_END_OF_FILE)
+      break;                                  // Return error
+
+    /* End current partition */
+    late_extra_no_cache(current_partition_index);
+    DBUG_PRINT("info", ("rnd_end on partition %d", current_partition_index));
+    if ((result= file->ha_rnd_end()))
+      break;
+
+    /* Shift to next partition */
+    while (++current_partition_index < m_tot_parts &&
+           !bitmap_is_set(&(m_part_info->used_partitions), current_partition_index))
+    {
     }
-    else
+    if (current_partition_index >= m_tot_parts)
     {
-      m_part_spec.start_part= part_id;
-      m_last_part= part_id;
-      table->status= 0;
-      DBUG_RETURN(0);
+      result= HA_ERR_END_OF_FILE;
+      break;
     }
+    file= m_file[current_partition_index];
+    DBUG_PRINT("info", ("rnd_init on partition %d", current_partition_index));
+    if ((result= file->ha_rnd_init(1)))
+      break;
+    late_extra_cache(current_partition_index);
   }
 
 end:
-  m_part_spec.start_part= NO_CURRENT_PART_ID;
+  current_partition_index= MY_BIT_NONE;
+  m_part_spec.start_part= NO_CURRENT_PART_ID;  //TODO: should be removed
   table->status= STATUS_NOT_FOUND;
   DBUG_RETURN(result);
 }
@@ -1554,10 +1722,10 @@
 
 void ha_partition::position(const byte *record)
 {
-  handler *file= m_file[m_last_part];
+  handler *file= m_file[current_partition_index];
   DBUG_ENTER("ha_partition::position");
   file->position(record);
-  store_part_id_in_pos(ref, m_last_part);
+  store_part_id_in_pos(ref, current_partition_index);
   memcpy((ref + PARTITION_BYTES_IN_POS), file->ref,
 	 (ref_length - PARTITION_BYTES_IN_POS));
 
@@ -1587,7 +1755,8 @@
   part_id= get_part_id_from_pos((const byte *) pos);
   DBUG_ASSERT(part_id < m_tot_parts);
   file= m_file[part_id];
-  m_last_part= part_id;
+  current_partition_index= part_id;
+  m_last_part= part_id;  // TODO: remove
   DBUG_RETURN(file->rnd_pos(buf, (pos + PARTITION_BYTES_IN_POS)));
 }
 
@@ -1619,6 +1788,7 @@
 int ha_partition::index_init(uint inx, bool sorted)
 {
   int error= 0;
+  uint i= 0;
   handler **file;
   DBUG_ENTER("ha_partition::index_init");
 
@@ -1628,16 +1798,16 @@
   m_ordered= sorted;
   m_curr_key_info= table->key_info+inx;
   include_partition_fields_in_used_fields();
-
   file= m_file;
   do
   {
     /* TODO RONM: Change to index_init() when code is stable */
-    if ((error= (*file)->ha_index_init(inx, sorted)))
-    {
-      DBUG_ASSERT(0);                           // Should never happen
-      break;
-    }
+    if (_bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
+      if ((error= (*file)->ha_index_init(inx, sorted)))
+      {
+        DBUG_ASSERT(0);                           // Should never happen
+        break;
+      }
   } while (*(++file));
   DBUG_RETURN(error);
 }
@@ -1651,6 +1821,7 @@
 int ha_partition::index_end()
 {
   int error= 0;
+  uint i= 0;
   handler **file;
   DBUG_ENTER("ha_partition::index_end");
 
@@ -1660,10 +1831,10 @@
   do
   {
     int tmp;
-    /* We want to execute index_end() on all handlers */
-    /* TODO RONM: Change to index_end() when code is stable */
-    if ((tmp= (*file)->ha_index_end()))
-      error= tmp;
+    /* TODO RONM: Change to index_init() when code is stable */
+    if (_bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
+      if ((tmp= (*file)->ha_index_end()))
+        error= tmp;
   } while (*(++file));
   DBUG_RETURN(error);
 }
@@ -2079,43 +2250,61 @@
 
   m_top_entry= NO_CURRENT_PART_ID;
   queue_remove_all(&queue);
-  for (i= m_part_spec.start_part; i <= m_part_spec.end_part; i++)
+
+  /* now we see what the index of our first important partition is */
+  current_partition_index = bitmap_get_first_set(&(m_part_info->used_partitions));
+  if (MY_BIT_NONE == current_partition_index)
   {
-    int error;
-    byte *rec_buf_ptr= rec_buf(i);
-    handler *file= m_file[i];
+    /*
+      The set of partitions to scan is empty. We return success and return
+      end of file on first rnd_next.
+    */
+    DBUG_RETURN(0);
+  }
 
-    switch (m_index_scan_type) {
-    case partition_index_read:
-      error= file->index_read(rec_buf_ptr,
-			      m_start_key.key,
-			      m_start_key.length,
-			      m_start_key.flag);
-      reverse_order= FALSE;
-      break;
-    case partition_index_first:
-      error= file->index_first(rec_buf_ptr);
-      reverse_order= FALSE;
-      break;
-    case partition_index_last:
-      error= file->index_last(rec_buf_ptr);
-      reverse_order= TRUE;
-      break;
-    default:
-      DBUG_ASSERT(FALSE);
-      DBUG_RETURN(HA_ERR_END_OF_FILE);
-    }
-    if (!error)
-    {
-      found= TRUE;
-      /*
-        Initialise queue without order first, simply insert
-      */
-      queue_element(&queue, j++)= (byte*)queue_buf(i);
-    }
-    else if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
+  DBUG_PRINT("info", ("current_partition_index %d", current_partition_index));
+  for (i= current_partition_index; i <= m_part_spec.end_part; i++)
+  {
+    DBUG_PRINT("info", ("i %d", i));
+    if (_bitmap_is_set(&(m_part_info->used_partitions), i))
     {
-      DBUG_RETURN(error);
+      byte *rec_buf_ptr= rec_buf(i);
+      int error;
+      handler *file= m_file[i];
+      DBUG_PRINT("info", ("i %d is set", i));
+
+      switch (m_index_scan_type) {
+      case partition_index_read:
+        error= file->index_read(rec_buf_ptr,
+                                m_start_key.key,
+                                m_start_key.length,
+                                m_start_key.flag);
+        reverse_order= FALSE;
+        break;
+      case partition_index_first:
+        error= file->index_first(rec_buf_ptr);
+        reverse_order= FALSE;
+        break;
+      case partition_index_last:
+        error= file->index_last(rec_buf_ptr);
+        reverse_order= TRUE;
+        break;
+      default:
+        DBUG_ASSERT(FALSE);
+        DBUG_RETURN(HA_ERR_END_OF_FILE);
+      }
+      if (!error)
+      {
+        found= TRUE;
+        /*
+          Initialise queue without order first, simply insert
+        */
+        queue_element(&queue, j++)= (byte*)queue_buf(i);
+      }
+      else if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
+      {
+        DBUG_RETURN(error);
+      }
     }
   }
   if (found)
@@ -2799,18 +2988,18 @@
   int result= 0, tmp;
   handler **file;
   DBUG_ENTER("ha_partition::reset");
-  file= m_file;
   if (m_part_info)
     bitmap_clear_all(&m_part_info->used_partitions);
+  file= m_file;
   do
   {
+    DBUG_PRINT("info", ("file iterator %d", (file - m_file) ));
     if ((tmp= (*file)->reset()))
       result= tmp;
   } while (*(++file));
   DBUG_RETURN(result);
 }
 
-
 int ha_partition::extra_opt(enum ha_extra_function operation, ulong cachesize)
 {
   DBUG_ENTER("ha_partition::extra_opt()");
@@ -2926,14 +3115,16 @@
 ha_rows ha_partition::records_in_range(uint inx, key_range *min_key,
 				       key_range *max_key)
 {
-  ha_rows in_range= 0;
   handler **file;
+  ha_rows in_range= 0;
   DBUG_ENTER("ha_partition::records_in_range");
 
   file= m_file;
   do
   {
-    in_range+= (*file)->records_in_range(inx, min_key, max_key);
+    int i= file - m_file;
+    if (_bitmap_is_set(&(m_part_info->used_partitions), i))
+      in_range+= (*file)->records_in_range(inx, min_key, max_key);
   } while (*(++file));
   DBUG_RETURN(in_range);
 }
@@ -2948,10 +3139,13 @@
   file= m_file;
   do
   {
-    rows= (*file)->estimate_rows_upper_bound();
-    if (rows == HA_POS_ERROR)
-      DBUG_RETURN(HA_POS_ERROR);
-    tot_rows+= rows;
+    if (_bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
+    {
+      rows= (*file)->estimate_rows_upper_bound();
+      if (rows == HA_POS_ERROR)
+        DBUG_RETURN(HA_POS_ERROR);
+      tot_rows+= rows;
+    }
   } while (*(++file));
   DBUG_RETURN(tot_rows);
 }
@@ -3272,6 +3466,10 @@
     hash_delete(&partition_open_tables, (byte *) share);
     thr_lock_delete(&share->lock);
     pthread_mutex_destroy(&share->mutex);
+    /*
+      this doesn't seem to free m_part_info->partition_names,
+      need to know how to make sure this happens
+    */
     my_free((gptr) share, MYF(0));
   }
   pthread_mutex_unlock(&partition_mutex);

--- 1.7/sql/ha_partition.h	2005-12-21 10:18:27 -08:00
+++ 1.8/sql/ha_partition.h	2006-01-09 23:28:32 -08:00
@@ -28,9 +28,18 @@
   char *table_name;
   uint table_name_length, use_count;
   pthread_mutex_t mutex;
+  HASH partition_names;
   THR_LOCK lock;
 } PARTITION_SHARE;
 
+typedef struct st_partition_name
+{
+  char *partition_name;
+  bool is_sub_partition;
+  uint partition_name_length;
+  uint partition_number;
+} PARTITION_NAME;
+
 
 #define PARTITION_BYTES_IN_POS 2
 class ha_partition :public handler
@@ -69,6 +78,7 @@
   uint m_tot_parts;                      // Total number of partitions;
   uint m_no_locks;                        // For engines like ha_blackhole, which needs no locks
   uint m_last_part;                      // Last file that we update,write
+  uint current_partition_index;          // Index of current partition in bitmap
   int m_lock_type;                       // Remembers type of last
                                          // external_lock
   part_id_range m_part_spec;             // Which parts to scan
@@ -121,6 +131,8 @@
   THR_LOCK_DATA lock;                   /* MySQL lock */
   PARTITION_SHARE *share;               /* Shared lock info */
 
+  int set_specific_partition(const char *partition_name);
+  
 public:
   virtual void set_part_info(partition_info *part_info)
   {
@@ -195,6 +207,8 @@
   void set_up_table_before_create(TABLE * table_arg, HA_CREATE_INFO * info,
 				  uint part_id);
   partition_element *find_partition_element(uint part_id);
+  int populate_partition_name_hash();
+  int get_partition_index(const char* partition_name);
 public:
 
   /*
--- New file ---
+++ mysql-test/r/partition_select.result	06/01/09 23:28:33
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
CREATE TABLE `t1` (
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM);
INSERT INTO `t1` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
SELECT * FROM t1;
id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT * FROM t1 PARTITION (p0);
id
1
2
3
4
5
SELECT * FROM t1 PARTITION (p1);
id
6
7
8
9
10
11
SELECT * FROM t1 PARTITION (p2);
id
12
13
14
15
16
SELECT * FROM t1 PARTITION (p3);
id
17
18
19
20
SELECT * FROM t1 PARTITION (p3) WHERE id = 2;
id
2
SELECT * FROM t1 PARTITION (foo);
ERROR HY000: partition 'foo' doesn't exist
CREATE TABLE `t2` (
`id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM);
INSERT INTO `t2` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
SELECT * FROM t2;
id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT * FROM t2 PARTITION (p0);
id
1
2
3
4
5
SELECT * FROM t2 PARTITION (p1);
id
6
7
8
9
10
11
SELECT * FROM t2 PARTITION (p2);
id
12
13
14
15
16
SELECT * FROM t2 PARTITION (p3);
id
17
18
19
20
SELECT * FROM t2 PARTITION (p3) ORDER BY id;
id
17
18
19
20
SELECT * FROM t2 PARTITION (p3) WHERE id = 2;
id
2
SELECT * FROM t2 PARTITION (foo);
ERROR HY000: partition 'foo' doesn't exist
DROP TABLE IF EXISTS `t3`;
Warnings:
Note	1051	Unknown table 't3'
CREATE TABLE `t3` (
`id` int(32) default NULL,
`name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY LIST (id) (
PARTITION p0 VALUES IN (1,3,5,7),
PARTITION p1 VALUES IN (0,2,4,6,8),
PARTITION p2 VALUES IN (9,10,11,12,13)
);
INSERT INTO `t3` VALUES (1,'first'), (3,'third'),(5,'fifth'),(7,'seventh'),(0,'zilch'),(2,'second'),(4,'fourth'),(6,'sixth'),(8,'eighth'),(9,'ninth'),(10,'tenth'),(11,'eleventh'),(12,'twelfth'),(13,'thirteenth');
SELECT * FROM `t3`;
id	name
1	first
3	third
5	fifth
7	seventh
0	zilch
2	second
4	fourth
6	sixth
8	eighth
9	ninth
10	tenth
11	eleventh
12	twelfth
13	thirteenth
SELECT * FROM `t3` PARTITION (p0);
id	name
1	first
3	third
5	fifth
7	seventh
SELECT * FROM `t3` PARTITION (p1);
id	name
0	zilch
2	second
4	fourth
6	sixth
8	eighth
SELECT * FROM `t3` PARTITION (p2);
id	name
9	ninth
10	tenth
11	eleventh
12	twelfth
13	thirteenth
SELECT * FROM `t3` PARTITION (p2) ORDER BY id;
id	name
9	ninth
10	tenth
11	eleventh
12	twelfth
13	thirteenth
DROP TABLE IF EXISTS `t4`;
Warnings:
Note	1051	Unknown table 't4'
CREATE TABLE `t4` (
`id` int(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ;
INSERT INTO `t4` SELECT * FROM `t2`;
INSERT INTO `t4` SELECT * FROM `t2` ORDER BY id;
CREATE TABLE `t5` (
id int(32),
name varchar(64),
purchased date)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
INSERT INTO `t5` VALUES (1, 'aaaaaaa', '2006-01-05 00:00:00');
INSERT INTO `t5` VALUES (2, 'bbbbbbb', '2005-08-05 00:00:00');
INSERT INTO `t5` VALUES (3, 'ccccccc', '1985-08-07 00:00:00');
INSERT INTO `t5` VALUES (4, 'ddddddd', '2000-01-01 00:00:00');
INSERT INTO `t5` VALUES (5, 'eeeeeee', '1999-12-01 00:00:00');
INSERT INTO `t5` VALUES (6, 'fffffff', '2003-11-12 00:00:00');
INSERT INTO `t5` VALUES (7, 'ggggggg', '1990-01-05 00:00:00');
INSERT INTO `t5` VALUES (8, 'hhhhhhh', '1978-01-05 00:00:00');
INSERT INTO `t5` VALUES (9, 'iiiiiii', '1979-01-05 00:00:00');
INSERT INTO `t5` VALUES (10, 'jjjjjjj', '1992-01-05 00:00:00');
INSERT INTO `t5` VALUES (11, 'kkkkkkk', '1993-01-05 00:00:00');
INSERT INTO `t5` VALUES (12, 'mmmmmmm', '1994-01-05 00:00:00');
INSERT INTO `t5` VALUES (13, 'nnnnnnn', '1989-01-05 00:00:00');
INSERT INTO `t5` VALUES (14, 'ooooooo', '1983-12-05 00:00:00');
INSERT INTO `t5` VALUES (15, 'ppppppp', '1986-06-05 00:00:00');
INSERT INTO `t5` VALUES (16, 'qqqqqqq', '1974-04-11 00:00:00');
INSERT INTO `t5` VALUES (17, 'qqqqqqq', '1960-03-15 00:00:00');
INSERT INTO `t5` VALUES (18, 'sssssss', '1950-09-23 00:00:00');
INSERT INTO `t5` VALUES (19, 'ttttttt', '1999-08-02 00:00:00');
INSERT INTO `t5` VALUES (20, 'uuuuuuu', '1994-05-28 00:00:00');
SELECT * FROM `t5`;
id	name	purchased
8	hhhhhhh	1978-01-05
13	nnnnnnn	1989-01-05
14	ooooooo	1983-12-05
18	sssssss	1950-09-23
3	ccccccc	1985-08-07
9	iiiiiii	1979-01-05
15	ppppppp	1986-06-05
16	qqqqqqq	1974-04-11
17	qqqqqqq	1960-03-15
5	eeeeeee	1999-12-01
12	mmmmmmm	1994-01-05
4	ddddddd	2000-01-01
7	ggggggg	1990-01-05
10	jjjjjjj	1992-01-05
11	kkkkkkk	1993-01-05
19	ttttttt	1999-08-02
20	uuuuuuu	1994-05-28
2	bbbbbbb	2005-08-05
6	fffffff	2003-11-12
1	aaaaaaa	2006-01-05
SELECT * FROM `t5` PARTITION(p0) ORDER BY id;
id	name	purchased
3	ccccccc	1985-08-07
8	hhhhhhh	1978-01-05
9	iiiiiii	1979-01-05
13	nnnnnnn	1989-01-05
14	ooooooo	1983-12-05
15	ppppppp	1986-06-05
16	qqqqqqq	1974-04-11
17	qqqqqqq	1960-03-15
18	sssssss	1950-09-23
SELECT * FROM `t5` PARTITION(s0) ORDER BY id;
id	name	purchased
8	hhhhhhh	1978-01-05
13	nnnnnnn	1989-01-05
14	ooooooo	1983-12-05
18	sssssss	1950-09-23
SELECT * FROM `t5` PARTITION(s1) ORDER BY id;
id	name	purchased
3	ccccccc	1985-08-07
9	iiiiiii	1979-01-05
15	ppppppp	1986-06-05
16	qqqqqqq	1974-04-11
17	qqqqqqq	1960-03-15
SELECT * FROM `t5` PARTITION(p1) ORDER BY id;
id	name	purchased
4	ddddddd	2000-01-01
5	eeeeeee	1999-12-01
7	ggggggg	1990-01-05
10	jjjjjjj	1992-01-05
11	kkkkkkk	1993-01-05
12	mmmmmmm	1994-01-05
19	ttttttt	1999-08-02
20	uuuuuuu	1994-05-28
SELECT * FROM `t5` PARTITION(s2) ORDER BY id;
id	name	purchased
5	eeeeeee	1999-12-01
12	mmmmmmm	1994-01-05
SELECT * FROM `t5` PARTITION(s3) ORDER BY id;
id	name	purchased
4	ddddddd	2000-01-01
7	ggggggg	1990-01-05
10	jjjjjjj	1992-01-05
11	kkkkkkk	1993-01-05
19	ttttttt	1999-08-02
20	uuuuuuu	1994-05-28
SELECT * FROM `t5` PARTITION(p2) ORDER BY id;
id	name	purchased
1	aaaaaaa	2006-01-05
2	bbbbbbb	2005-08-05
6	fffffff	2003-11-12
SELECT * FROM `t5` PARTITION(s4) ORDER BY id;
id	name	purchased
2	bbbbbbb	2005-08-05
6	fffffff	2003-11-12
SELECT * FROM `t5` PARTITION(s5) ORDER BY id;
id	name	purchased
1	aaaaaaa	2006-01-05
drop table t1,t2,t3,t4,t5;

--- New file ---
+++ mysql-test/t/partition_select.test	06/01/09 23:28:33
#
# WL# 2682
# Partition select tests.
#
-- source include/have_partition.inc

--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
--enable_warnings

CREATE TABLE `t1` (
`id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM);

INSERT INTO `t1` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);

SELECT * FROM t1;
SELECT * FROM t1 PARTITION (p0);
SELECT * FROM t1 PARTITION (p1);
SELECT * FROM t1 PARTITION (p2);
SELECT * FROM t1 PARTITION (p3);
SELECT * FROM t1 PARTITION (p3) WHERE id = 2;
--error 1501
SELECT * FROM t1 PARTITION (foo);

# now try indexes
CREATE TABLE `t2` (
`id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM);

INSERT INTO `t2` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);

SELECT * FROM t2;
SELECT * FROM t2 PARTITION (p0);
SELECT * FROM t2 PARTITION (p1);
SELECT * FROM t2 PARTITION (p2);
SELECT * FROM t2 PARTITION (p3);
SELECT * FROM t2 PARTITION (p3) ORDER BY id;
SELECT * FROM t2 PARTITION (p3) WHERE id = 2;
--error 1501
SELECT * FROM t2 PARTITION (foo);


DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `id` int(32) default NULL,
  `name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY LIST (id) (
  PARTITION p0 VALUES IN (1,3,5,7),
  PARTITION p1 VALUES IN (0,2,4,6,8),
  PARTITION p2 VALUES IN (9,10,11,12,13)
);

INSERT INTO `t3` VALUES (1,'first'), (3,'third'),(5,'fifth'),(7,'seventh'),(0,'zilch'),(2,'second'),(4,'fourth'),(6,'sixth'),(8,'eighth'),(9,'ninth'),(10,'tenth'),(11,'eleventh'),(12,'twelfth'),(13,'thirteenth');

SELECT * FROM `t3`;
SELECT * FROM `t3` PARTITION (p0);
SELECT * FROM `t3` PARTITION (p1);
SELECT * FROM `t3` PARTITION (p2);
SELECT * FROM `t3` PARTITION (p2) ORDER BY id;

DROP TABLE IF EXISTS `t4`;
CREATE TABLE `t4` (
  `id` int(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ;

INSERT INTO `t4` SELECT * FROM `t2`;
INSERT INTO `t4` SELECT * FROM `t2` ORDER BY id;
# not sure how to do this, since names could be anything
#SELECT * FROM `t4` PARTITION (p0);
#SELECT * FROM `t4` PARTITION (p1);
#SELECT * FROM `t4` PARTITION (p2);
#SELECT * FROM `t4` PARTITION (p3);
#SELECT * FROM `t4` PARTITION (p3) ORDER BY id;

CREATE TABLE `t5` (
  id int(32),
  name varchar(64),
  purchased date)
PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

INSERT INTO `t5` VALUES (1, 'aaaaaaa', '2006-01-05 00:00:00');
INSERT INTO `t5` VALUES (2, 'bbbbbbb', '2005-08-05 00:00:00');
INSERT INTO `t5` VALUES (3, 'ccccccc', '1985-08-07 00:00:00');
INSERT INTO `t5` VALUES (4, 'ddddddd', '2000-01-01 00:00:00');
INSERT INTO `t5` VALUES (5, 'eeeeeee', '1999-12-01 00:00:00');
INSERT INTO `t5` VALUES (6, 'fffffff', '2003-11-12 00:00:00');
INSERT INTO `t5` VALUES (7, 'ggggggg', '1990-01-05 00:00:00');
INSERT INTO `t5` VALUES (8, 'hhhhhhh', '1978-01-05 00:00:00');
INSERT INTO `t5` VALUES (9, 'iiiiiii', '1979-01-05 00:00:00');
INSERT INTO `t5` VALUES (10, 'jjjjjjj', '1992-01-05 00:00:00');
INSERT INTO `t5` VALUES (11, 'kkkkkkk', '1993-01-05 00:00:00');
INSERT INTO `t5` VALUES (12, 'mmmmmmm', '1994-01-05 00:00:00');
INSERT INTO `t5` VALUES (13, 'nnnnnnn', '1989-01-05 00:00:00');
INSERT INTO `t5` VALUES (14, 'ooooooo', '1983-12-05 00:00:00');
INSERT INTO `t5` VALUES (15, 'ppppppp', '1986-06-05 00:00:00');
INSERT INTO `t5` VALUES (16, 'qqqqqqq', '1974-04-11 00:00:00');
INSERT INTO `t5` VALUES (17, 'qqqqqqq', '1960-03-15 00:00:00');
INSERT INTO `t5` VALUES (18, 'sssssss', '1950-09-23 00:00:00');
INSERT INTO `t5` VALUES (19, 'ttttttt', '1999-08-02 00:00:00');
INSERT INTO `t5` VALUES (20, 'uuuuuuu', '1994-05-28 00:00:00');

SELECT * FROM `t5`;
SELECT * FROM `t5` PARTITION(p0) ORDER BY id;
SELECT * FROM `t5` PARTITION(s0) ORDER BY id;
SELECT * FROM `t5` PARTITION(s1) ORDER BY id;
SELECT * FROM `t5` PARTITION(p1) ORDER BY id;
SELECT * FROM `t5` PARTITION(s2) ORDER BY id;
SELECT * FROM `t5` PARTITION(s3) ORDER BY id;
SELECT * FROM `t5` PARTITION(p2) ORDER BY id;
SELECT * FROM `t5` PARTITION(s4) ORDER BY id;
SELECT * FROM `t5` PARTITION(s5) ORDER BY id;

--disable_warnings
drop table t1,t2,t3,t4,t5;
--enable_warnings

Thread
bk commit into 5.1 tree (patg:1.2017)Patrick Galbraith10 Jan