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.2079 06/01/27 14:59:46 patg@stripped +5 -0
WL# 2986
Final review changes prior to single patch
sql/ha_partition.cc
1.32 06/01/27 14:59:43 patg@stripped +9 -13
WL# 2986, Final review changes prior to last patch
mysql-test/t/partition_pruning.test
1.6 06/01/27 14:59:43 patg@stripped +132 -0
WL# 2986 Added tests to end of partition pruning test per final review
mysql-test/r/partition_pruning.result
1.8 06/01/27 14:59:43 patg@stripped +206 -0
WL# 2986, added tests to end of partition_pruning test, per final review
BitKeeper/deleted/.del-partition_wl2986.result~35b76a63f89ca8b0
1.2 06/01/27 14:34:37 patg@stripped +0 -0
Delete: mysql-test/r/partition_wl2986.result
BitKeeper/deleted/.del-partition_wl2986.test~be629395e49a58a0
1.2 06/01/27 14:34:30 patg@stripped +1 -7
Delete: mysql-test/t/partition_wl2986.test
# 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-wl2986
--- 1.31/sql/ha_partition.cc 2006-01-26 18:48:09 -08:00
+++ 1.32/sql/ha_partition.cc 2006-01-27 14:59:43 -08:00
@@ -2252,7 +2252,7 @@
/* Initialise the bitmap we use to determine what partitions are used */
if (bitmap_init(&(m_part_info->used_partitions), NULL, m_tot_parts, TRUE))
- goto err;
+ DBUG_RETURN(1);
bitmap_set_all(&(m_part_info->used_partitions));
file= m_file;
@@ -2907,7 +2907,7 @@
/* 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));
+ 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));
@@ -3826,11 +3826,11 @@
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);
+ 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;
case partition_index_first:
DBUG_PRINT("info", ("index_first on partition %d", i));
@@ -3845,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;
}
@@ -4983,7 +4983,7 @@
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 || tmp_in_range == HA_OFFSET_ERROR)
+ if (tmp_in_range == HA_POS_ERROR)
DBUG_RETURN(tmp_in_range);
in_range+= tmp_in_range;
}
@@ -5404,10 +5404,6 @@
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.1/mysql-test/t/partition_wl2986.test 2006-01-26 18:48:09 -08:00
+++ 1.2/BitKeeper/deleted/.del-partition_wl2986.test~be629395e49a58a0 2006-01-27 14:34:30 -08:00
@@ -1,13 +1,7 @@
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`a` 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 t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
--- 1.7/mysql-test/r/partition_pruning.result 2006-01-26 18:48:08 -08:00
+++ 1.8/mysql-test/r/partition_pruning.result 2006-01-27 14:59:43 -08:00
@@ -304,3 +304,209 @@
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 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-27 14:59:43 -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.2079) | Patrick Galbraith | 27 Jan |
