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.2073 06/01/20 17:27:38 patg@stripped +4 -0
WL# 2986
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.25 06/01/20 17:27:34 patg@stripped +2 -2
WL# 2986
<mikael> patg: yep, it seems to be a bug in get_partition_id_int_range
<mikael> patg: the normal bug it seems, should be <= and was < makes edge cases
go awry
<mikael> patg: most likely not found since it uses the same algorithm for
reading as it does for writing
sql/opt_range.cc
1.194 06/01/20 17:27:34 patg@stripped +15 -1
WL# 2986
Added bitmap_clear_all prior to setting specific bits in partition pruning.
sql/ha_partition.cc
1.24 06/01/20 17:27:34 patg@stripped +187 -120
WL# 2986
- Added bitmap check to see if partition should be used in all iterations over partitions
(except locking)
mysql-test/r/partition_pruning.result
1.4 06/01/20 17:27:34 patg@stripped +31 -31
WL #2986
New results due to new functionality of using partition pruning.
# 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-clean3
--- 1.193/sql/opt_range.cc 2006-01-17 00:24:50 -08:00
+++ 1.194/sql/opt_range.cc 2006-01-20 17:27:34 -08:00
@@ -2317,7 +2317,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;
@@ -2435,7 +2437,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;
}
@@ -2445,8 +2450,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;
}
/* See comment in PART_PRUNE_PARAM::part_num_to_part_id about what this is */
@@ -2738,6 +2749,9 @@
for (uint32 num= ppar->start_part_num; num != ppar->end_part_num;
num++)
{
+ uint part_id= (ppar->part_num_to_part_id(ppar, num) *
+ part_info->no_subparts) + subpart_id;
+ DBUG_PRINT("info", ("2:Mark subpartition %u as used", part_id));
bitmap_set_bit(&part_info->used_partitions,
ppar->part_num_to_part_id(ppar, num) *
part_info->no_subparts + subpart_id);
--- 1.23/sql/ha_partition.cc 2006-01-18 07:25:48 -08:00
+++ 1.24/sql/ha_partition.cc 2006-01-20 17:27:34 -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))
{
@@ -2144,7 +2143,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;
@@ -2198,11 +2197,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;
@@ -2214,7 +2213,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)
{
@@ -2240,6 +2239,11 @@
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));
+
file= m_file;
do
{
@@ -2252,6 +2256,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
@@ -2269,6 +2274,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
@@ -2305,11 +2311,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:
@@ -2317,16 +2324,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
****************************************************************************/
@@ -2369,9 +2377,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;
@@ -2380,12 +2388,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);
@@ -2397,7 +2408,9 @@
err_handler:
while (file-- != m_file)
+ {
(*file)->external_lock(thd, F_UNLCK);
+ }
DBUG_RETURN(error);
}
@@ -2454,10 +2467,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);
@@ -2515,7 +2528,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);
}
@@ -2834,7 +2847,6 @@
do
{
int tmp;
- /* We want to execute end_bulk_insert() on all handlers */
if ((tmp= (*file)->end_bulk_insert()))
error= tmp;
} while (*(++file));
@@ -2874,50 +2886,64 @@
int ha_partition::rnd_init(bool scan)
{
int error;
+ uint i= 0;
+ uint 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",
+ *(uint32*)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);
}
@@ -2937,12 +2963,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();
@@ -2952,16 +2977,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)
@@ -2985,14 +3010,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
@@ -3000,40 +3023,49 @@
*/
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:
@@ -3080,7 +3112,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;
@@ -3169,16 +3202,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);
}
@@ -3211,10 +3244,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);
}
@@ -3636,7 +3669,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)
{
/*
@@ -3660,7 +3696,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 &&
@@ -3758,13 +3806,16 @@
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,
+ 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);
break;
@@ -3832,18 +3883,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:
@@ -4149,14 +4204,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)
@@ -4664,10 +4722,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()))
@@ -4676,7 +4733,6 @@
DBUG_RETURN(result);
}
-
/*
Special extra method for HA_EXTRA_CACHE with cachesize as extra parameter
@@ -4719,8 +4775,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;
}
@@ -4743,7 +4798,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)))
@@ -4843,7 +4901,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);
}
@@ -4901,14 +4960,15 @@
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)))
+ in_range+= (*file)->records_in_range(inx, min_key, max_key);
} while (*(++file));
DBUG_RETURN(in_range);
}
@@ -4933,10 +4993,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);
}
@@ -5323,6 +5386,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.24/sql/sql_partition.cc 2006-01-18 13:09:20 -08:00
+++ 1.25/sql/sql_partition.cc 2006-01-20 17:27:34 -08:00
@@ -2980,7 +2980,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;
@@ -3053,7 +3053,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;
--- 1.3/mysql-test/r/partition_pruning.result 2005-12-25 23:16:29 -08:00
+++ 1.4/mysql-test/r/partition_pruning.result 2006-01-20 17:27:34 -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
| Thread |
|---|
| • bk commit into 5.1 tree (patg:1.2073) | Patrick Galbraith | 21 Jan |
