[Commits] 3fc69b0: Partition pruning added.

Oleksandr Byelkin sanja at mariadb.com
Fri Sep 2 15:38:28 EEST 2016


revision-id: 3fc69b08a685145c19e58b9cd1cd4a093a273a88 (mariadb-10.2.1-58-g3fc69b0)
parent(s): 3e7384efc410d266470bb902f77422491d6c53e3
committer: Oleksandr Byelkin
timestamp: 2016-09-02 14:38:27 +0200
message:

Partition pruning added.

---
 mysql-test/r/partition_default.result | 126 +++++++++++++++++++++++++++++++++-
 mysql-test/t/partition_default.test   |  53 ++++++++++++++
 sql/partition_info.h                  |   6 ++
 sql/sql_partition.cc                  |  45 +++++++++++-
 sql/sql_partition.h                   |   4 ++
 5 files changed, 230 insertions(+), 4 deletions(-)

diff --git a/mysql-test/r/partition_default.result b/mysql-test/r/partition_default.result
index 3590d74..e4ce9b6 100644
--- a/mysql-test/r/partition_default.result
+++ b/mysql-test/r/partition_default.result
@@ -130,9 +130,131 @@ PARTITION p3 DEFAULT
 )
 ;
 ERROR HY000: Only one DEFAULT partition allowed
+create table t1 (a int, b int)
+PARTITION BY LIST (a)
+(
+PARTITION p2 VALUES IN (4,5,6),
+PARTITION p1 VALUES IN (1,20),
+PARTITION p0 default
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (a)
+(PARTITION p2 VALUES IN (4,5,6) ENGINE = MyISAM,
+ PARTITION p1 VALUES IN (1,20) ENGINE = MyISAM,
+ PARTITION p0 DEFAULT ENGINE = MyISAM) */
+insert into t1 values (10,10);
+select * from t1 where a=10;
+a	b
+10	10
+select * from t1 where a<=10;
+a	b
+10	10
+select * from t1 where a<=20;
+a	b
+10	10
+select * from t1 where a>=10;
+a	b
+10	10
+select * from t1 where a>=5;
+a	b
+10	10
+insert into t1 values (20,20),(5,5);
+select * from t1 where a=10;
+a	b
+10	10
+select * from t1 where a<=10;
+a	b
+5	5
+10	10
+select * from t1 where a<=20;
+a	b
+5	5
+20	20
+10	10
+select * from t1 where a>=10;
+a	b
+20	20
+10	10
+select * from t1 where a>=5;
+a	b
+5	5
+20	20
+10	10
+explain partitions select * from t1 where a=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where a=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+select * from t1 where a=10 or a=5;
+a	b
+5	5
+10	10
+explain partitions select * from t1 where a=10 or a=5;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2,p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+drop table t1;
+create table t1 (a int, b int)
+PARTITION BY LIST COLUMNS(a,b)
+(
+PARTITION p2 VALUES IN ((1,4),(2,5),(3,6),(5,5)),
+PARTITION p1 VALUES IN ((1,1),(20,20)),
+PARTITION p0 DEFAULT
+)
+;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50500 PARTITION BY LIST  COLUMNS(a,b)
+(PARTITION p2 VALUES IN ((1,4),(2,5),(3,6),(5,5)) ENGINE = MyISAM,
+ PARTITION p1 VALUES IN ((1,1),(20,20)) ENGINE = MyISAM,
+ PARTITION p0 DEFAULT ENGINE = MyISAM) */
+insert into t1 values (10,10);
+select * from t1 where a=10 and b=10;
+a	b
+10	10
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+select * from t1 where a=10;
+a	b
+10	10
+explain partitions select * from t1 where a=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+select * from t1 where a<=10;
+a	b
+10	10
+select * from t1 where a>=10;
+a	b
+10	10
+insert into t1 values (20,20),(5,5);
+select * from t1 where a=10 and b=10;
+a	b
+10	10
+explain partitions select * from t1 where a=10 and b=10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+select * from t1 where a=10 and b=10 or a=20 and b=20;
+a	b
+20	20
+10	10
+explain partitions select * from t1 where a=10 and b=10 or a=20 and b=20;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1,p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+drop table t1;
 CREATE TABLE t1 (a DATE, KEY(a))
 PARTITION BY LIST (TO_DAYS(a))
-(PARTITION `p0001-01-01` VALUES IN (TO_DAYS('0001-01-01')),
+(PARTITION `pDEF` DEFAULT,
 PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')),
 PARTITION `pNULL` VALUES IN (NULL),
 PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')),
@@ -345,7 +467,7 @@ DROP TABLE t1;
 # TO_SECONDS, test of LIST and index
 CREATE TABLE t1 (a DATE, KEY(a))
 PARTITION BY LIST (TO_SECONDS(a))
-(PARTITION `p0001-01-01` VALUES IN (TO_SECONDS('0001-01-01')),
+(PARTITION `pDEF` DEFAULT,
 PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')),
 PARTITION `pNULL` VALUES IN (NULL),
 PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')),
diff --git a/mysql-test/t/partition_default.test b/mysql-test/t/partition_default.test
index e99dce7..dd25fea 100644
--- a/mysql-test/t/partition_default.test
+++ b/mysql-test/t/partition_default.test
@@ -108,6 +108,59 @@ create table t1 (a int, b int)
 #
 # partititon prunning test
 #
+
+create table t1 (a int, b int)
+  PARTITION BY LIST (a)
+  (
+    PARTITION p2 VALUES IN (4,5,6),
+    PARTITION p1 VALUES IN (1,20),
+    PARTITION p0 default
+  )
+;
+show create table t1;
+insert into t1 values (10,10);
+select * from t1 where a=10;
+select * from t1 where a<=10;
+select * from t1 where a<=20;
+select * from t1 where a>=10;
+select * from t1 where a>=5;
+insert into t1 values (20,20),(5,5);
+select * from t1 where a=10;
+select * from t1 where a<=10;
+select * from t1 where a<=20;
+select * from t1 where a>=10;
+select * from t1 where a>=5;
+explain partitions select * from t1 where a=10;
+explain partitions select * from t1 where a=5;
+select * from t1 where a=10 or a=5;
+explain partitions select * from t1 where a=10 or a=5;
+
+drop table t1;
+
+create table t1 (a int, b int)
+  PARTITION BY LIST COLUMNS(a,b)
+  (
+    PARTITION p2 VALUES IN ((1,4),(2,5),(3,6),(5,5)),
+    PARTITION p1 VALUES IN ((1,1),(20,20)),
+    PARTITION p0 DEFAULT
+  )
+;
+show create table t1;
+insert into t1 values (10,10);
+select * from t1 where a=10 and b=10;
+explain partitions select * from t1 where a=10 and b=10;
+select * from t1 where a=10;
+explain partitions select * from t1 where a=10;
+select * from t1 where a<=10;
+select * from t1 where a>=10;
+insert into t1 values (20,20),(5,5);
+select * from t1 where a=10 and b=10;
+explain partitions select * from t1 where a=10 and b=10;
+select * from t1 where a=10 and b=10 or a=20 and b=20;
+explain partitions select * from t1 where a=10 and b=10 or a=20 and b=20;
+drop table t1;
+
+
 CREATE TABLE t1 (a DATE, KEY(a))
 PARTITION BY LIST (TO_DAYS(a))
 (PARTITION `pDEF` DEFAULT,
diff --git a/sql/partition_info.h b/sql/partition_info.h
index 0c7448f..f70a68b 100644
--- a/sql/partition_info.h
+++ b/sql/partition_info.h
@@ -231,6 +231,10 @@ class partition_info : public Sql_alloc
   bool use_default_num_subpartitions;
   bool default_partitions_setup;
   bool defined_max_value;
+  inline bool has_default_partititon()
+  {
+    return (part_type == LIST_PARTITION && defined_max_value);
+  }
   bool list_of_part_fields;                  // KEY or COLUMNS PARTITIONING
   bool list_of_subpart_fields;               // KEY SUBPARTITIONING
   bool linear_hash_ind;                      // LINEAR HASH/KEY
@@ -400,6 +404,7 @@ static inline void init_single_partition_iterator(uint32 part_id,
   part_iter->part_nums.start= part_iter->part_nums.cur= part_id;
   part_iter->part_nums.end= part_id+1;
   part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE;
+  part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE;
   part_iter->get_next= get_next_partition_id_range;
 }
 
@@ -411,6 +416,7 @@ void init_all_partitions_iterator(partition_info *part_info,
   part_iter->part_nums.start= part_iter->part_nums.cur= 0;
   part_iter->part_nums.end= part_info->num_parts;
   part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE;
+  part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE;
   part_iter->get_next= get_next_partition_id_range;
 }
 
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index 7e99721..9cef2b1 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -7695,6 +7695,7 @@ int get_part_iter_for_interval_cols_via_map(partition_info *part_info,
                                             uint flags,
                                             PARTITION_ITERATOR *part_iter)
 {
+  bool can_match_multiple_values;
   uint32 nparts;
   get_col_endpoint_func  UNINIT_VAR(get_col_endpoint);
   DBUG_ENTER("get_part_iter_for_interval_cols_via_map");
@@ -7714,6 +7715,12 @@ int get_part_iter_for_interval_cols_via_map(partition_info *part_info,
   else
     assert(0);
 
+  can_match_multiple_values= (flags || !min_value || !max_value ||
+                              (min_len != max_len) ||
+                              memcmp(min_value, max_value, min_len));
+  if (can_match_multiple_values && part_info->has_default_partititon())
+    part_iter->ret_default_part= part_iter->ret_default_part_orig= TRUE;
+
   if (flags & NO_MIN_RANGE)
     part_iter->part_nums.start= part_iter->part_nums.cur= 0;
   else
@@ -7749,7 +7756,14 @@ int get_part_iter_for_interval_cols_via_map(partition_info *part_info,
                                                nparts);
   }
   if (part_iter->part_nums.start == part_iter->part_nums.end)
+  {
+    if (part_info->has_default_partititon())
+    {
+      part_iter->ret_default_part= part_iter->ret_default_part_orig= TRUE;
+      DBUG_RETURN(1);
+    }
     DBUG_RETURN(0);
+  }
   DBUG_RETURN(1);
 }
 
@@ -7810,6 +7824,7 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info,
   (void)min_len;
   (void)max_len;
   part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE;
+  part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE;
 
   if (part_info->part_type == RANGE_PARTITION)
   {
@@ -7848,6 +7863,8 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info,
 
   can_match_multiple_values= (flags || !min_value || !max_value ||
                               memcmp(min_value, max_value, field_len));
+  if (can_match_multiple_values && part_info->has_default_partititon())
+    part_iter->ret_default_part= part_iter->ret_default_part_orig= TRUE;
   if (can_match_multiple_values &&
       (part_info->part_type == RANGE_PARTITION ||
        part_info->has_null_value))
@@ -7877,6 +7894,12 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info,
     {
       /* The right bound is X <= NULL, i.e. it is a "X IS NULL" interval */
       part_iter->part_nums.end= 0;
+      /*
+        It is something like select * from tbl where col IS NULL
+        and we have partition with NULL to catch it, so we do not need
+        DEFAULT partition
+      */
+      part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE;
       DBUG_RETURN(1);
     }
   }
@@ -7918,7 +7941,7 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info,
         }
       }
       if (part_iter->part_nums.start == max_endpoint_val)
-        DBUG_RETURN(0); /* No partitions */
+        goto not_found;
     }
   }
 
@@ -7955,9 +7978,17 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info,
     }
     if (part_iter->part_nums.start >= part_iter->part_nums.end &&
         !part_iter->ret_null_part)
-      DBUG_RETURN(0); /* No partitions */
+      goto not_found;
   }
   DBUG_RETURN(1); /* Ok, iterator initialized */
+
+not_found:
+  if (part_info->has_default_partititon())
+  {
+    part_iter->ret_default_part= part_iter->ret_default_part_orig= TRUE;
+    DBUG_RETURN(1);
+  }
+  DBUG_RETURN(0); /* No partitions */
 }
 
 
@@ -8021,6 +8052,8 @@ int get_part_iter_for_interval_via_walking(partition_info *part_info,
   (void)max_len;
 
   part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE;
+  part_iter->ret_default_part= part_iter->ret_default_part_orig= FALSE;
+
   if (is_subpart)
   {
     field= part_info->subpart_field_array[0];
@@ -8152,6 +8185,8 @@ uint32 get_next_partition_id_range(PARTITION_ITERATOR* part_iter)
       part_iter->ret_null_part= FALSE;
       return 0;                    /* NULL always in first range partition */
     }
+    DBUG_ASSERT(!part_iter->ret_default_part);
+
     part_iter->part_nums.cur= part_iter->part_nums.start;
     part_iter->ret_null_part= part_iter->ret_null_part_orig;
     return NOT_A_PARTITION_ID;
@@ -8189,8 +8224,14 @@ uint32 get_next_partition_id_list(PARTITION_ITERATOR *part_iter)
       part_iter->ret_null_part= FALSE;
       return part_iter->part_info->has_null_part_id;
     }
+    if (part_iter->ret_default_part)
+    {
+      part_iter->ret_default_part= FALSE;
+      return part_iter->part_info->default_partition_id;
+    }
     part_iter->part_nums.cur= part_iter->part_nums.start;
     part_iter->ret_null_part= part_iter->ret_null_part_orig;
+    part_iter->ret_default_part= part_iter->ret_default_part_orig;
     return NOT_A_PARTITION_ID;
   }
   else
diff --git a/sql/sql_partition.h b/sql/sql_partition.h
index dd352b6..b225c14 100644
--- a/sql/sql_partition.h
+++ b/sql/sql_partition.h
@@ -177,6 +177,10 @@ typedef struct st_partition_iter
     iterator also produce id of the partition that contains NULL value.
   */
   bool ret_null_part, ret_null_part_orig;
+  /*
+    We should return DEFAULT partition.
+  */
+  bool ret_default_part, ret_default_part_orig;
   struct st_part_num_range
   {
     uint32 start;


More information about the commits mailing list