List:Commits« Previous MessageNext Message »
From:Patrick Galbraith Date:January 29 2006 12:22am
Subject:bk commit into 5.1 tree (patg:1.2089)
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.2089 06/01/28 16:22:32 patg@stripped +7 -0
  WL# 2986
  
  Final patch
  -----------
  
  This WL is about using this bitmap in all parts of the partition handler.
  Thus for:
  rnd_init/rnd_next
  index_init/index_next and all other variants of index scans
  read_range_... the various range scans implemented in the partition handler.
  
  Also use those bitmaps in the various other calls that currently loop over all
  partitions.
  
  

  sql/sql_partition.cc
    1.28 06/01/28 16:22:28 patg@stripped +2 -4
    WL# 2986
    
    Changed < to <=, which fixes the problem with edge cases going awry.

  sql/opt_range.cc
    1.197 06/01/28 16:22:28 patg@stripped +15 -4
    WL# 2986
    
    Added bitmap_clear_all to clear bitmap prior to pruning
    DBUG code for testing

  sql/handler.h
    1.182 06/01/28 16:22:28 patg@stripped +0 -1
    WL# 2986
    
    Removed unused member

  sql/ha_partition.h
    1.11 06/01/28 16:22:28 patg@stripped +0 -1
    WL# 2986
    
    Removed unused member

  sql/ha_partition.cc
    1.26 06/01/28 16:22:28 patg@stripped +191 -120
    WL# 2986
    
    Used work from 2682 and removed the partition select code.
        
    Added do {} while loop to any iteration over partitions to now utilise 
    m_part_info->used_partitions bitmap to determine if a partition should be
    used.

  mysql-test/t/partition_pruning.test
    1.6 06/01/28 16:22:28 patg@stripped +132 -0
    WL# 2986
    
    New tests to show bitmap being used in ha_partition

  mysql-test/r/partition_pruning.result
    1.6 06/01/28 16:22:27 patg@stripped +244 -38
    WL# 2986
    
    New results reflecting bitmap being used to determine if a partition is to be
    included

# 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.patg.net
# Root:	/home/patg/mysql-build/mysql-5.1-new-wl2986

--- 1.181/sql/handler.h	2006-01-24 19:01:48 -08:00
+++ 1.182/sql/handler.h	2006-01-28 16:22:28 -08:00
@@ -618,7 +618,6 @@
 typedef struct {
   uint32 start_part;
   uint32 end_part;
-  bool use_bit_array;
 } part_id_range;
 
 

--- 1.196/sql/opt_range.cc	2006-01-18 03:09:00 -08:00
+++ 1.197/sql/opt_range.cc	2006-01-28 16:22:28 -08:00
@@ -2309,7 +2309,9 @@
 
   thd->no_errors=1;				// Don't warn about NULL
   thd->mem_root=&alloc;
-  
+
+  bitmap_clear_all(&part_info->used_partitions);
+
   prune_param.key= prune_param.range_param.key_parts;
   SEL_TREE *tree;
   SEL_ARG *arg;
@@ -2453,7 +2455,10 @@
 static void mark_full_partition_used_no_parts(partition_info* part_info,
                                               uint32 part_id)
 {
+  DBUG_ENTER("mark_full_partition_used_no_parts");
+  DBUG_PRINT("enter", ("Mark partition %u as used", part_id));
   bitmap_set_bit(&part_info->used_partitions, part_id);
+  DBUG_VOID_RETURN;
 }
 
 
@@ -2463,8 +2468,14 @@
 {
   uint32 start= part_id * part_info->no_subparts;
   uint32 end=   start + part_info->no_subparts; 
+  DBUG_ENTER("mark_full_partition_used_with_parts");
+
   for (; start != end; start++)
+  {
+    DBUG_PRINT("info", ("1:Mark subpartition %u as used", start));
     bitmap_set_bit(&part_info->used_partitions, start);
+  }
+  DBUG_VOID_RETURN;
 }
 
 /*
@@ -2495,7 +2506,7 @@
   if (!(bitmap_buf= (uint32*)alloc_root(ppar->range_param.mem_root,
                                         bitmap_bytes)))
   {
-    /* 
+    /*
       Fallback, process just the first SEL_IMERGE. This can leave us with more
       partitions marked as used then actually needed.
     */
@@ -2503,7 +2514,7 @@
   }
   bitmap_init(&all_merges, bitmap_buf, n_bits, FALSE);
   bitmap_set_prefix(&all_merges, n_bits);
-  
+
   List_iterator<SEL_IMERGE> it(merges);
   SEL_IMERGE *imerge;
   while ((imerge=it++))
@@ -2514,7 +2525,7 @@
       /* no used partitions on one ANDed imerge => no used partitions at all */
       return 0;
     }
-    
+
     if (res != -1)
       bitmap_intersect(&all_merges, &ppar->part_info->used_partitions);
 

--- 1.25/sql/ha_partition.cc	2006-01-26 04:37:19 -08:00
+++ 1.26/sql/ha_partition.cc	2006-01-28 16:22:28 -08:00
@@ -611,7 +611,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))
   {
@@ -2154,7 +2153,7 @@
   if (!(engine_array= (handlerton **) my_malloc(m_tot_parts * sizeof(handlerton*),MYF(0))))
     goto err2;
   for (i= 0; i < m_tot_parts; i++)
-    engine_array[i]= ha_resolve_by_legacy_type(current_thd, 
+    engine_array[i]= ha_resolve_by_legacy_type(current_thd,
                 (enum legacy_db_type) *(uchar *) ((file_buffer) + 12 + i));
   address_tot_name_len= file_buffer + 12 + 4 * tot_partition_words;
   tot_name_words= (uint4korr(address_tot_name_len) + 3) / 4;
@@ -2208,11 +2207,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;
-  handler **file;
+  int error;
   uint alloc_len;
+  handler **file;
+  char name_buff[FN_REFLEN];
   DBUG_ENTER("ha_partition::open");
 
   ref_length= 0;
@@ -2224,7 +2223,7 @@
   m_start_key.length= 0;
   m_rec0= table->record[0];
   m_rec_length= table->s->reclength;
-  alloc_len= m_tot_parts * (m_rec_length + PARTITION_BYTES_IN_POS); 
+  alloc_len= m_tot_parts * (m_rec_length + PARTITION_BYTES_IN_POS);
   alloc_len+= table->s->max_key_length;
   if (!m_ordered_rec_buffer)
   {
@@ -2250,6 +2249,12 @@
       m_start_key.key= (const byte*)ptr;
     }
   }
+
+  /* Initialise the bitmap we use to determine what partitions are used */
+  if (bitmap_init(&(m_part_info->used_partitions), NULL, m_tot_parts, TRUE))
+    DBUG_RETURN(1);
+  bitmap_set_all(&(m_part_info->used_partitions));
+
   file= m_file;
   do
   {
@@ -2262,6 +2267,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
@@ -2279,6 +2285,7 @@
   if ((error= init_queue(&m_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
@@ -2291,6 +2298,7 @@
 err_handler:
   while (file-- != m_file)
     (*file)->close();
+err:
   DBUG_RETURN(error);
 }
 
@@ -2315,11 +2323,12 @@
 
 int ha_partition::close(void)
 {
-  handler **file;
   bool first= TRUE;
+  handler **file;
   DBUG_ENTER("ha_partition::close");
 
   delete_queue(&m_queue);
+  bitmap_free(&(m_part_info->used_partitions));
   file= m_file;
 
 repeat:
@@ -2327,16 +2336,17 @@
   {
     (*file)->close();
   } while (*(++file));
+
   if (first && m_added_file && m_added_file[0])
   {
     file= m_added_file;
     first= FALSE;
     goto repeat;
   }
+
   DBUG_RETURN(0);
 }
 
-
 /****************************************************************************
                 MODULE start/end statement
 ****************************************************************************/
@@ -2379,9 +2389,9 @@
 
 int ha_partition::external_lock(THD *thd, int lock_type)
 {
+  bool first= TRUE;
   uint error;
   handler **file;
-  bool first= TRUE;
   DBUG_ENTER("ha_partition::external_lock");
 
   file= m_file;
@@ -2390,12 +2400,15 @@
 repeat:
   do
   {
+    DBUG_PRINT("info", ("external_lock(thd, %d) iteration %d",
+                        lock_type, (file - m_file)));
     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));
+
   if (first && m_added_file && m_added_file[0])
   {
     DBUG_ASSERT(lock_type == F_UNLCK);
@@ -2407,7 +2420,9 @@
 
 err_handler:
   while (file-- != m_file)
+  {
     (*file)->external_lock(thd, F_UNLCK);
+  }
   DBUG_RETURN(error);
 }
 
@@ -2464,10 +2479,10 @@
 {
   handler **file;
   DBUG_ENTER("ha_partition::store_lock");
-
   file= m_file;
   do
   {
+    DBUG_PRINT("info", ("store lock %d iteration", (file - m_file)));
     to= (*file)->store_lock(thd, to, lock_type);
   } while (*(++file));
   DBUG_RETURN(to);
@@ -2525,7 +2540,7 @@
 uint ha_partition::lock_count() const
 {
   DBUG_ENTER("ha_partition::lock_count");
-
+  DBUG_PRINT("info", ("m_no_locks %d", m_no_locks));
   DBUG_RETURN(m_no_locks);
 }
 
@@ -2844,7 +2859,6 @@
   do
   {
     int tmp;
-    /* We want to execute end_bulk_insert() on all handlers */
     if ((tmp= (*file)->end_bulk_insert()))
       error= tmp;
   } while (*(++file));
@@ -2884,50 +2898,64 @@
 int ha_partition::rnd_init(bool scan)
 {
   int error;
+  uint i= 0;
+  uint32 part_id;
   handler **file;
   DBUG_ENTER("ha_partition::rnd_init");
 
   include_partition_fields_in_used_fields();
+  
+  /* Now we see what the index of our first important partition is */
+  DBUG_PRINT("info", ("m_part_info->used_partitions 0x%x",
+             m_part_info->used_partitions.bitmap));
+  part_id= bitmap_get_first_set(&(m_part_info->used_partitions));
+  DBUG_PRINT("info", ("m_part_spec.start_part %d", part_id));
+
+  if (MY_BIT_NONE == part_id)
+    goto err1;
+
+  /*
+    We have a partition and we are scanning with rnd_next
+    so we bump our cache
+  */
+  DBUG_PRINT("info", ("rnd_init on partition %d", part_id));
   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))
+    late_extra_cache(part_id);
+    if ((error= m_file[part_id]->ha_rnd_init(scan)))
+      goto err;
+  }
+  else
+  {
+    for (i= part_id; i < m_tot_parts; i++)
     {
-      /*
-        The set of partitions to scan is empty. We return success and return
-        end of file on first rnd_next.
-      */
-      DBUG_RETURN(0);
+      if (bitmap_is_set(&(m_part_info->used_partitions), i))
+      {
+        if ((error= m_file[i]->ha_rnd_init(scan)))
+          goto err;
+      }
     }
-    /*
-      We will use the partition set in our scan, using the start and stop
-      partition and checking each scan before start dependent on bittfields.
-    */
-    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);
   }
-  file= m_file;
-  do
-  {
-    if ((error= (*file)->ha_rnd_init(0)))
-      goto err;
-  } while (*(++file));
-  m_scan_value= 0;
+  m_scan_value= scan;
+  m_part_spec.start_part= part_id;
+  m_part_spec.end_part= m_tot_parts - 1;
+  DBUG_PRINT("info", ("m_scan_value=%d", m_scan_value));
   DBUG_RETURN(0);
 
 err:
-  while (file--)
-    (*file)->ha_rnd_end();
+  while ((int)--i >= (int)part_id)
+  {
+    if (bitmap_is_set(&(m_part_info->used_partitions), i))
+	  m_file[i]->ha_rnd_end();
+  }
+err1:
+  m_scan_value= 2;
+  m_part_spec.start_part= NO_CURRENT_PART_ID;
   DBUG_RETURN(error);
 }
 
@@ -2947,12 +2975,11 @@
 {
   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)
+  case 1:
+    if (NO_CURRENT_PART_ID != m_part_spec.start_part)         // Table scan
     {
       late_extra_no_cache(m_part_spec.start_part);
       m_file[m_part_spec.start_part]->ha_rnd_end();
@@ -2962,16 +2989,16 @@
     file= m_file;
     do
     {
-      (*file)->ha_rnd_end();
+      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;
+  m_part_spec.start_part= NO_CURRENT_PART_ID;
   DBUG_RETURN(0);
 }
 
-
 /*
   read next row during full table scan (scan in random row order)
 
@@ -2995,14 +3022,12 @@
 
 int ha_partition::rnd_next(byte *buf)
 {
-  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;
+  uint part_id= m_part_spec.start_part;
   DBUG_ENTER("ha_partition::rnd_next");
 
-  DBUG_ASSERT(m_scan_value == 1);
-
-  if (part_id > m_part_spec.end_part)
+  if (NO_CURRENT_PART_ID == part_id)
   {
     /*
       The original set of partitions to scan was empty and thus we report
@@ -3010,40 +3035,50 @@
     */
     goto end;
   }
+  
+  DBUG_ASSERT(m_scan_value == 1);
+  file= m_file[part_id];
+  
   while (TRUE)
   {
-    if ((result= file->rnd_next(buf)))
-    {
-      if (result == HA_ERR_RECORD_DELETED)
-        continue;                               // Probably MyISAM
-
-      if (result != HA_ERR_END_OF_FILE)
-        break;                                  // Return error
-
-      /* 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);
-    }
-    else
+    int result= file->rnd_next(buf);
+    if (!result)
     {
-      m_part_spec.start_part= part_id;
       m_last_part= part_id;
+      m_part_spec.start_part= part_id;
       table->status= 0;
       DBUG_RETURN(0);
     }
+
+    /*
+      if we get here, then the current partition rnd_next returned failure
+    */
+    if (result == HA_ERR_RECORD_DELETED)
+      continue;                               // Probably MyISAM
+
+    if (result != HA_ERR_END_OF_FILE)
+      break;                                  // Return error
+
+    /* 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 */
+    while (++part_id < m_tot_parts &&
+           !bitmap_is_set(&(m_part_info->used_partitions), part_id))
+      ;
+    if (part_id >= m_tot_parts)
+    {
+      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);
   }
 
 end:
@@ -3090,7 +3125,8 @@
 
 #ifdef SUPPORTING_PARTITION_OVER_DIFFERENT_ENGINES
 #ifdef HAVE_purify
-  bzero(ref + PARTITION_BYTES_IN_POS + ref_length, max_ref_length-ref_length);
+  bzero(ref + PARTITION_BYTES_IN_POS + ref_length,
+        max_ref_length-ref_length);
 #endif /* HAVE_purify */
 #endif
   DBUG_VOID_RETURN;
@@ -3179,16 +3215,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);
 }
@@ -3221,10 +3257,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;
+    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);
 }
@@ -3646,7 +3682,10 @@
   if (idx_read_flag)
     get_partition_set(table,buf,active_index,&m_start_key,&m_part_spec);
   else
-    get_partition_set(table, buf, MAX_KEY, 0, &m_part_spec);
+  {
+    m_part_spec.start_part= 0;
+    m_part_spec.end_part= m_tot_parts - 1;
+  }
   if (m_part_spec.start_part > m_part_spec.end_part)
   {
     /*
@@ -3670,7 +3709,19 @@
   {
     /*
       Set m_ordered_scan_ongoing according how the scan should be done
+      Only exact partitions are discovered atm by get_partition_set.
+      Verify this, also bitmap must have at least one bit set otherwise
+      the result from this table is the empty set.
     */
+    uint start_part= bitmap_get_first_set(&(m_part_info->used_partitions));
+    if (start_part == MY_BIT_NONE)
+    {
+      DBUG_PRINT("info", ("scan with no partition to scan"));
+      DBUG_RETURN(HA_ERR_END_OF_FILE);
+    }
+    if (start_part > m_part_spec.start_part)
+      m_part_spec.start_part= start_part;
+    DBUG_ASSERT(m_part_spec.start_part < m_tot_parts);
     m_ordered_scan_ongoing= m_ordered;
   }
   DBUG_ASSERT(m_part_spec.start_part < m_tot_parts &&
@@ -3768,15 +3819,18 @@
   for (i= m_part_spec.start_part; i <= m_part_spec.end_part; i++)
   {
     int error;
-    handler *file= m_file[i];
+    handler *file;
 
+    if (!(bitmap_is_set(&(m_part_info->used_partitions), i)))
+      continue;
+    file= m_file[i];
     m_part_spec.start_part= i;
     switch (m_index_scan_type) {
     case partition_index_read:
       DBUG_PRINT("info", ("index_read on partition %d", i));
       error= file->index_read(buf, m_start_key.key,
-			      m_start_key.length,
-			      m_start_key.flag);
+                              m_start_key.length,
+                              m_start_key.flag);
       break;
     case partition_index_first:
       DBUG_PRINT("info", ("index_first on partition %d", i));
@@ -3791,7 +3845,7 @@
       if (compare_key(end_range) <= 0)
       {
         m_last_part= i;
-	DBUG_RETURN(0);
+        DBUG_RETURN(0);
       }
       error= HA_ERR_END_OF_FILE;
     }
@@ -3842,18 +3896,22 @@
 
   m_top_entry= NO_CURRENT_PART_ID;
   queue_remove_all(&m_queue);
+
+  DBUG_PRINT("info", ("m_part_spec.start_part %d", m_part_spec.start_part));
   for (i= m_part_spec.start_part; i <= m_part_spec.end_part; i++)
   {
-    int error;
+    if (!(bitmap_is_set(&(m_part_info->used_partitions), i)))
+      continue;
     byte *rec_buf_ptr= rec_buf(i);
+    int error;
     handler *file= m_file[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);
+                              m_start_key.key,
+                              m_start_key.length,
+                              m_start_key.flag);
       reverse_order= FALSE;
       break;
     case partition_index_first:
@@ -4159,14 +4217,17 @@
     file_array= m_file;
     do
     {
-      file= *file_array;
-      file->info(HA_STATUS_VARIABLE);
-      records+= file->records;
-      deleted+= file->deleted;
-      data_file_length+= file->data_file_length;
-      index_file_length+= file->index_file_length;
-      if (file->check_time > check_time)
-	check_time= file->check_time;
+      if (bitmap_is_set(&(m_part_info->used_partitions), (file_array - m_file)))
+      {
+        file= *file_array;
+        file->info(HA_STATUS_VARIABLE);
+        records+= file->records;
+        deleted+= file->deleted;
+        data_file_length+= file->data_file_length;
+        index_file_length+= file->index_file_length;
+        if (file->check_time > check_time)
+          check_time= file->check_time;
+      }
     } while (*(++file_array));
     if (records < 2 &&
         m_table_flags & HA_NOT_EXACT_COUNT)
@@ -4674,10 +4735,9 @@
   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);
+    bitmap_set_all(&m_part_info->used_partitions);
+  file= m_file;
   do
   {
     if ((tmp= (*file)->reset()))
@@ -4686,7 +4746,6 @@
   DBUG_RETURN(result);
 }
 
-
 /*
   Special extra method for HA_EXTRA_CACHE with cachesize as extra parameter
 
@@ -4729,8 +4788,7 @@
   m_extra_cache_size= cachesize;
   if (m_part_spec.start_part != NO_CURRENT_PART_ID)
   {
-    DBUG_ASSERT(m_part_spec.start_part == 0);
-    late_extra_cache(0);
+    late_extra_cache(m_part_spec.start_part);
   }
   DBUG_VOID_RETURN;
 }
@@ -4753,7 +4811,10 @@
   int result= 0, tmp;
   handler **file;
   DBUG_ENTER("ha_partition::loop_extra()");
-
+  /* 
+    TODO, 5.2: this is where you could possibly add optimisations to add the bitmap
+    _if_ a SELECT.
+  */
   for (file= m_file; *file; file++)
   {
     if ((tmp= (*file)->extra(operation)))
@@ -4853,7 +4914,8 @@
   DBUG_ENTER("ha_partition::scan_time");
 
   for (file= m_file; *file; file++)
-    scan_time+= (*file)->scan_time();
+    if (bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
+      scan_time+= (*file)->scan_time();
   DBUG_RETURN(scan_time);
 }
 
@@ -4911,14 +4973,20 @@
 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);
+    if (bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
+    {
+      ha_rows tmp_in_range= (*file)->records_in_range(inx, min_key, max_key);
+      if (tmp_in_range == HA_POS_ERROR)
+        DBUG_RETURN(tmp_in_range);
+      in_range+= tmp_in_range;
+    }
   } while (*(++file));
   DBUG_RETURN(in_range);
 }
@@ -4943,10 +5011,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);
 }

--- 1.10/sql/ha_partition.h	2006-01-25 11:06:53 -08:00
+++ 1.11/sql/ha_partition.h	2006-01-28 16:22:28 -08:00
@@ -100,7 +100,6 @@
   bool m_create_handler;                 // Handler used to create table
   bool m_is_sub_partitioned;             // Is subpartitioned
   bool m_ordered_scan_ongoing;
-  bool m_use_bit_array;
 
   /*
     We keep track if all underlying handlers are MyISAM since MyISAM has a

--- 1.27/sql/sql_partition.cc	2006-01-25 11:06:53 -08:00
+++ 1.28/sql/sql_partition.cc	2006-01-28 16:22:28 -08:00
@@ -3004,7 +3004,7 @@
   while (max_part_id > min_part_id)
   {
     loc_part_id= (max_part_id + min_part_id + 1) >> 1;
-    if (range_array[loc_part_id] < part_func_value)
+    if (range_array[loc_part_id] <= part_func_value)
       min_part_id= loc_part_id + 1;
     else
       max_part_id= loc_part_id - 1;
@@ -3077,7 +3077,7 @@
   while (max_part_id > min_part_id)
   {
     loc_part_id= (max_part_id + min_part_id + 1) >> 1;
-    if (range_array[loc_part_id] < part_func_value)
+    if (range_array[loc_part_id] <= part_func_value)
       min_part_id= loc_part_id + 1;
     else
       max_part_id= loc_part_id - 1;
@@ -3639,7 +3639,6 @@
   bool found_part_field= FALSE;
   DBUG_ENTER("get_partition_set");
 
-  part_spec->use_bit_array= FALSE;
   part_spec->start_part= 0;
   part_spec->end_part= no_parts - 1;
   if ((index < MAX_KEY) && 
@@ -3757,7 +3756,6 @@
     else
     {
       DBUG_ASSERT(sub_part != no_parts);
-      part_spec->use_bit_array= TRUE;
       part_spec->start_part= sub_part;
       part_spec->end_part=sub_part+
                            (part_info->no_subparts*(part_info->no_parts-1));

--- 1.5/mysql-test/r/partition_pruning.result	2006-01-04 00:08:49 -08:00
+++ 1.6/mysql-test/r/partition_pruning.result	2006-01-28 16:22:27 -08:00
@@ -11,10 +11,10 @@
 insert into t1 values (1),(2),(3);
 explain partitions select * from t1 where a=1;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t1 where a=2;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t1 where a=1 or a=2;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p0,p1	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -42,10 +42,10 @@
 insert into t3 values (5),(15);
 explain partitions select * from t3 where a=11;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t3 where a=10;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t3 where a=20;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
@@ -59,11 +59,11 @@
 insert into t4 values (10,2), (10,4);
 explain partitions select * from t4 where (a=10 and b=1) or (a=10 and b=2);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t4	p0	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t4 
 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t4	p0	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t4 where (a=10 and b=2) or (a=10 and b=3)
 or (a=10 and b = 4);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
@@ -89,25 +89,25 @@
 explain partitions select * from t5
 where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp0,p0_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp0,p0_sp1	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3)
 or (a=10 and b = 4);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp0,p1_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
 explain partitions select * from t5 where (c=1 and d=1);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp0,p1_sp0	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp0,p1_sp0	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t5 where (c=2 and d=1);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or 
 (c=2 and d=1);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	3	Using where
 explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or 
 (b=2 and c=2 and d=1);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t5	p0_sp0,p0_sp1,p1_sp1	ALL	NULL	NULL	NULL	NULL	3	Using where
 create table t6 (a int not null) partition by LIST(a) (
 partition p1 values in (1),
 partition p3 values in (3),
@@ -121,31 +121,31 @@
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t6 where a <= 1;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t6	p1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t6	p1	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t6 where a >  9;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t6 where a >= 9;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t6	p9	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t6 where a > 0 and a < 5;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t6	p1,p3	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t6	p1,p3	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t6 where a > 5 and a < 12;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t6	p7,p9	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t6 where a > 3 and a < 8 ;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t6	p5,p7	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t6	p5,p7	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t6 where a >= 0 and a <= 5;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t6	p1,p3,p5	ALL	NULL	NULL	NULL	NULL	3	Using where
 explain partitions select * from t6 where a >= 5 and a <= 12;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t6	p5,p7,p9	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t6	p5,p7,p9	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t6 where a >= 3 and a <= 8;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t6	p3,p5,p7	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t6	p3,p5,p7	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t6 where a > 3 and a < 5;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
@@ -159,16 +159,16 @@
 insert into t7 values (10),(30),(50);
 explain partitions select * from t7 where a < 5;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t7	p10	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t7 where a < 10;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t7	p10	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t7 where a <= 10;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t7	p10,p30	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t7	p10,p30	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t7 where a = 10;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t7	p30	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t7	p30	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t7 where a < 90;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t7	p10,p30,p50,p70,p90	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -183,7 +183,7 @@
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t7 where a > 11 and a < 29;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t7	p30	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 create table t8 (a date not null) partition by RANGE(YEAR(a)) (
 partition p0 values less than (1980),
 partition p1 values less than (1990),
@@ -192,7 +192,7 @@
 insert into t8 values ('1985-05-05'),('1995-05-05');
 explain partitions select * from t8 where a < '1980-02-02';
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t8	p0,p1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 create table t9 (a date not null) partition by RANGE(TO_DAYS(a)) (
 partition p0 values less than (732299), -- 2004-12-19
 partition p1 values less than (732468), -- 2005-06-06
@@ -201,7 +201,7 @@
 insert into t9 values ('2005-05-05'), ('2005-04-04');
 explain partitions select * from t9 where a <  '2004-12-19';
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t9	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t9 where a <= '2004-12-19';
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t9	p0,p1	ALL	NULL	NULL	NULL	NULL	2	Using where
@@ -224,10 +224,10 @@
 insert into t1 values (1),(2),(3);
 explain partitions select * from t1 where a1 > 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t1 where a1 >= 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p1,p2	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t1 where a1 < 3 and a1 > 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
@@ -242,19 +242,19 @@
 insert into t3 values (1,1),(2,2),(3,3);
 explain partitions select * from t3 where a=2 or b=1;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p0_sp1,p1_sp0,p1_sp1,p1_sp2,p1_sp3,p2_sp1,p3_sp1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t3	p0_sp1,p1_sp0,p1_sp1,p1_sp2,p1_sp3,p2_sp1,p3_sp1	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t3 where a=4 or b=2;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p0_sp2,p1_sp2,p2_sp2,p3_sp0,p3_sp1,p3_sp2,p3_sp3	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t3	p0_sp2,p1_sp2,p2_sp2,p3_sp0,p3_sp1,p3_sp2,p3_sp3	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t3 where (a=2 or b=1) and (a=4 or b=2) ;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	p1_sp2,p3_sp1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t3	p1_sp2,p3_sp1	system	NULL	NULL	NULL	NULL	1	
 drop table t3;
 create table t1 (a int) partition by hash(a) partitions 2;
 insert into t1 values (1),(2);
 explain partitions select * from t1 where a is null;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 explain partitions select * from t1 where a is not null;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p0,p1	ALL	NULL	NULL	NULL	NULL	2	Using where
@@ -266,25 +266,25 @@
 select * from t1 X, t1 Y 
 where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	X	p1,p2	ALL	a,b	NULL	NULL	NULL	4	Using where
+1	SIMPLE	X	p1,p2	ALL	a,b	NULL	NULL	NULL	2	Using where
 1	SIMPLE	Y	p2,p3	ref	a,b	b	4	test.X.b	2	Using where
 explain partitions
 select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	X	p1,p2	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	X	p1,p2	ALL	a	NULL	NULL	NULL	2	Using where
 1	SIMPLE	Y	p1,p2	ref	a	a	4	test.X.a	2	
 drop table t1;
 create table t1 (a int) partition by hash(a) partitions 20;
 insert into t1 values (1),(2),(3);
 explain partitions select * from t1 where a >  1 and a < 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t1 where a >= 1 and a < 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t1 where a >  1 and a <= 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p2,p3	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p2,p3	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t1 where a >= 1 and a <= 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p1,p2,p3	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -300,7 +300,213 @@
 insert into t1 values (1,1),(2,2),(3,3);
 explain partitions select * from t1 where b >  1 and b < 3;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p0_sp2,p1_sp2,p2_sp2,p3_sp2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p0_sp2,p1_sp2,p2_sp2,p3_sp2	system	NULL	NULL	NULL	NULL	1	
 explain partitions select * from t1 where b >  1 and b < 3 and (a =1 or a =2);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p1_sp2,p2_sp2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	p1_sp2,p2_sp2	system	NULL	NULL	NULL	NULL	1	
+DROP TABLE IF EXISTS `t1`;
+CREATE TABLE `t1` (
+`a` int(11) default NULL
+);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+DROP TABLE IF EXISTS `t2`;
+Warnings:
+Note	1051	Unknown table 't2'
+CREATE TABLE `t2` (
+`a` int(11) default NULL,
+KEY `a` (`a`)
+) ;
+insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
+insert into t1 select a from t2;
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+`a` int(11) default NULL,
+`b` int(11) default NULL
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+insert into t2 select a,1 from t1 where a < 200;
+insert into t2 select a,2 from t1 where a >= 200 and a < 400;
+insert into t2 select a,3 from t1 where a >= 400 and a < 600;
+insert into t2 select a,4 from t1 where a >= 600 and a < 800;
+insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
+explain partitions select * from t2;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	1010	
+explain partitions select * from t2 where a < 801 and a > 200;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	800	Using where
+explain partitions select * from t2 where a < 801 and a > 800;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p4	ALL	NULL	NULL	NULL	NULL	200	Using where
+explain partitions select * from t2 where a > 600;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p3,p4	ALL	NULL	NULL	NULL	NULL	400	Using where
+explain partitions select * from t2 where a > 600 and b = 1;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p3,p4	ALL	NULL	NULL	NULL	NULL	400	Using where
+explain partitions select * from t2 where a > 600 and b = 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p3,p4	ALL	NULL	NULL	NULL	NULL	400	Using where
+explain partitions select * from t2 where a > 600 and b = 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p3,p4	ALL	NULL	NULL	NULL	NULL	400	Using where
+explain partitions select * from t2 where b = 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	1010	Using where
+flush status;
+update t2 set b = 100 where b = 6;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+flush status;
+update t2 set a = 1002 where a = 1001;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+flush status;
+update t2 set b = 6 where a = 600;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+flush status;
+update t2 set b = 6 where a > 600 and a < 800;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+flush status;
+delete from t2 where a > 600;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1015
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+`a` int(11) default NULL,
+`b` int(11) default NULL,
+index (b)
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+insert into t2 select a,1 from t1 where a < 100;
+insert into t2 select a,2 from t1 where a >= 200 and a < 300;
+insert into t2 select a,3 from t1 where a >= 300 and a < 400;
+insert into t2 select a,4 from t1 where a >= 400 and a < 500;
+insert into t2 select a,5 from t1 where a >= 500 and a < 600;
+insert into t2 select a,6 from t1 where a >= 600 and a < 700;
+insert into t2 select a,7 from t1 where a >= 700 and a < 800;
+insert into t2 select a,8 from t1 where a >= 800 and a < 900;
+insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
+explain partitions select * from t2;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	910	
+explain partitions select * from t2 where a = 101;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0	ALL	NULL	NULL	NULL	NULL	110	Using where
+explain partitions select * from t2 where a = 550;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p2	ALL	NULL	NULL	NULL	NULL	200	Using where
+explain partitions select * from t2 where a = 833;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p4	ALL	NULL	NULL	NULL	NULL	200	Using where
+explain partitions select * from t2 where (a = 100 OR a = 900);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p4	ALL	NULL	NULL	NULL	NULL	310	Using where
+explain partitions select * from t2 where (a > 100 AND a < 600);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	710	Using where
+explain partitions select * from t2 where b = 4;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ref	b	b	5	const	76	Using where
+explain partitions select * from t2 where b = 6;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ref	b	b	5	const	76	Using where
+explain partitions select * from t2 where b in (1,3,5);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b in (2,4,6);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b in (7,8,9);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b > 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b > 5 and b < 8;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+explain partitions select * from t2 where b > 5 and b < 7;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	range	b	b	5	NULL	76	Using where
+explain partitions select * from t2 where b > 0 and b < 5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	p0,p1,p2,p3,p4	ALL	b	NULL	NULL	NULL	910	Using where
+flush status;
+update t2 set a = 111 where b = 10;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	5
+flush status;
+update t2 set a = 111 where b in (5,6);
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	915
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	0
+flush status;
+update t2 set a = 222 where b = 7;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	5
+flush status;
+delete from t2 where b = 7;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	0
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	5
+flush status;
+delete from t2 where b > 5;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1215
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	0
+show status like 'Handler_read_prev';
+Variable_name	Value
+Handler_read_prev	0
+show status like 'Handler_read_next';
+Variable_name	Value
+Handler_read_next	0
+flush status;
+delete from t2 where b < 5 or b > 3;
+show status like 'Handler_read_rnd_next';
+Variable_name	Value
+Handler_read_rnd_next	1215
+show status like 'Handler_read_key';
+Variable_name	Value
+Handler_read_key	0
+show status like 'Handler_read_prev';
+Variable_name	Value
+Handler_read_prev	0
+show status like 'Handler_read_next';
+Variable_name	Value
+Handler_read_next	0
+drop table t1, t2;

--- 1.5/mysql-test/t/partition_pruning.test	2006-01-04 00:08:49 -08:00
+++ 1.6/mysql-test/t/partition_pruning.test	2006-01-28 16:22:28 -08:00
@@ -270,5 +270,137 @@
 explain partitions select * from t1 where b >  1 and b < 3;
 explain partitions select * from t1 where b >  1 and b < 3 and (a =1 or a =2);
 
+# WL# 2986
+DROP TABLE IF EXISTS `t1`;
+CREATE TABLE `t1` (
+  `a` int(11) default NULL
+);
+
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  KEY `a` (`a`)
+) ;
+
+insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
+
+insert into t1 select a from t2;
+
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  `b` int(11) default NULL
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+
+insert into t2 select a,1 from t1 where a < 200;
+insert into t2 select a,2 from t1 where a >= 200 and a < 400;
+insert into t2 select a,3 from t1 where a >= 400 and a < 600;
+insert into t2 select a,4 from t1 where a >= 600 and a < 800;
+insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
+
+explain partitions select * from t2;
+explain partitions select * from t2 where a < 801 and a > 200;
+explain partitions select * from t2 where a < 801 and a > 800;
+explain partitions select * from t2 where a > 600;
+explain partitions select * from t2 where a > 600 and b = 1;
+explain partitions select * from t2 where a > 600 and b = 4;
+explain partitions select * from t2 where a > 600 and b = 5;
+explain partitions select * from t2 where b = 5;
+
+flush status;
+update t2 set b = 100 where b = 6;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set a = 1002 where a = 1001;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set b = 6 where a = 600;
+show status like 'Handler_read_rnd_next';
+flush status;
+update t2 set b = 6 where a > 600 and a < 800;
+show status like 'Handler_read_rnd_next';
+flush status;
+delete from t2 where a > 600;
+show status like 'Handler_read_rnd_next';
+
+
+DROP TABLE IF EXISTS `t2`;
+CREATE TABLE `t2` (
+  `a` int(11) default NULL,
+  `b` int(11) default NULL,
+  index (b)
+) 
+PARTITION BY RANGE (a) (
+PARTITION p0 VALUES LESS THAN (200),
+PARTITION p1 VALUES LESS THAN (400),
+PARTITION p2 VALUES LESS THAN (600),
+PARTITION p3 VALUES LESS THAN (800),
+PARTITION p4 VALUES LESS THAN (1001));
+
+insert into t2 select a,1 from t1 where a < 100;
+insert into t2 select a,2 from t1 where a >= 200 and a < 300;
+insert into t2 select a,3 from t1 where a >= 300 and a < 400;
+insert into t2 select a,4 from t1 where a >= 400 and a < 500;
+insert into t2 select a,5 from t1 where a >= 500 and a < 600;
+insert into t2 select a,6 from t1 where a >= 600 and a < 700;
+insert into t2 select a,7 from t1 where a >= 700 and a < 800;
+insert into t2 select a,8 from t1 where a >= 800 and a < 900;
+insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
+
+explain partitions select * from t2;
+# not using indexes
+explain partitions select * from t2 where a = 101;
+explain partitions select * from t2 where a = 550;
+explain partitions select * from t2 where a = 833;
+explain partitions select * from t2 where (a = 100 OR a = 900);
+explain partitions select * from t2 where (a > 100 AND a < 600);
+explain partitions select * from t2 where b = 4;
+explain partitions select * from t2 where b = 6;
+explain partitions select * from t2 where b in (1,3,5);
+explain partitions select * from t2 where b in (2,4,6);
+explain partitions select * from t2 where b in (7,8,9);
+explain partitions select * from t2 where b > 5;
+explain partitions select * from t2 where b > 5 and b < 8;
+explain partitions select * from t2 where b > 5 and b < 7;
+explain partitions select * from t2 where b > 0 and b < 5;
+
+flush status;
+update t2 set a = 111 where b = 10;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+update t2 set a = 111 where b in (5,6);
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+update t2 set a = 222 where b = 7;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+delete from t2 where b = 7;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+flush status;
+delete from t2 where b > 5;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+show status like 'Handler_read_prev';
+show status like 'Handler_read_next';
+flush status;
+delete from t2 where b < 5 or b > 3;
+show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_key';
+show status like 'Handler_read_prev';
+show status like 'Handler_read_next';
+
+drop table t1, t2;
 # No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447
 # being fixed.
Thread
bk commit into 5.1 tree (patg:1.2089)Patrick Galbraith29 Jan