diff options
| -rw-r--r-- | mysql-test/r/partition.result | 101 | ||||
| -rw-r--r-- | mysql-test/t/partition.test | 75 | ||||
| -rw-r--r-- | sql/opt_range.cc | 1 | ||||
| -rw-r--r-- | sql/sql_partition.cc | 23 | ||||
| -rw-r--r-- | sql/sql_partition.h | 2 |
5 files changed, 198 insertions, 4 deletions
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 7244dd6ccbf..f5d8cc8f349 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -718,4 +718,105 @@ CALL test.p1(13); Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back drop table t1; +create table t1 (f1 integer) partition by range(f1) +(partition p1 values less than (0), partition p2 values less than (10)); +insert into t1 set f1 = null; +select * from t1 where f1 is null; +f1 +NULL +explain partitions select * from t1 where f1 is null; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 system NULL NULL NULL NULL 1 +drop table t1; +create table t1 (f1 integer) partition by list(f1) +(partition p1 values in (1), partition p2 values in (null)); +insert into t1 set f1 = null; +insert into t1 set f1 = 1; +select * from t1 where f1 is null or f1 = 1; +f1 +1 +NULL +drop table t1; +create table t1 (f1 smallint) +partition by list (f1) (partition p0 values in (null)); +insert into t1 values (null); +select * from t1 where f1 is null; +f1 +NULL +drop table t1; +create table t1 (f1 smallint) +partition by range (f1) (partition p0 values less than (0)); +insert into t1 values (null); +select * from t1 where f1 is null; +f1 +NULL +drop table t1; +create table t1 (f1 integer) partition by list(f1) +( +partition p1 values in (1), +partition p2 values in (NULL), +partition p3 values in (2), +partition p4 values in (3), +partition p5 values in (4) +); +insert into t1 values (1),(2),(3),(4),(null); +select * from t1 where f1 < 3; +f1 +1 +2 +explain partitions select * from t1 where f1 < 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p3 ALL NULL NULL NULL NULL 2 Using where +select * from t1 where f1 is null; +f1 +NULL +explain partitions select * from t1 where f1 is null; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 system NULL NULL NULL NULL 1 +drop table t1; +create table t1 (f1 int) partition by list(f1 div 2) +( +partition p1 values in (1), +partition p2 values in (NULL), +partition p3 values in (2), +partition p4 values in (3), +partition p5 values in (4) +); +insert into t1 values (2),(4),(6),(8),(null); +select * from t1 where f1 < 3; +f1 +2 +explain partitions select * from t1 where f1 < 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,p2,p3,p4,p5 ALL NULL NULL NULL NULL 5 Using where +select * from t1 where f1 is null; +f1 +NULL +explain partitions select * from t1 where f1 is null; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2 system NULL NULL NULL NULL 1 +drop table t1; +create table t1 (a int) partition by LIST(a) ( +partition pn values in (NULL), +partition p0 values in (0), +partition p1 values in (1), +partition p2 values in (2) +); +insert into t1 values (NULL),(0),(1),(2); +select * from t1 where a is null or a < 2; +a +NULL +0 +1 +explain partitions select * from t1 where a is null or a < 2; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pn,p0,p1 ALL NULL NULL NULL NULL 3 Using where +select * from t1 where a is null or a < 0 or a > 1; +a +NULL +2 +explain partitions select * from t1 where a is null or a < 0 or a > 1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pn,p2 ALL NULL NULL NULL NULL 2 Using where +drop table t1; End of 5.1 tests diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index ad01977a458..c3e32e832bf 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -849,4 +849,79 @@ CALL test.p1(12); CALL test.p1(13); drop table t1; +# +# Bug#18053 Partitions: crash if null +# Bug#18070 Partitions: wrong result on WHERE ... IS NULL +# +create table t1 (f1 integer) partition by range(f1) +(partition p1 values less than (0), partition p2 values less than (10)); +insert into t1 set f1 = null; +select * from t1 where f1 is null; +explain partitions select * from t1 where f1 is null; +drop table t1; + +create table t1 (f1 integer) partition by list(f1) +(partition p1 values in (1), partition p2 values in (null)); +insert into t1 set f1 = null; +insert into t1 set f1 = 1; +select * from t1 where f1 is null or f1 = 1; +drop table t1; + +create table t1 (f1 smallint) +partition by list (f1) (partition p0 values in (null)); +insert into t1 values (null); +select * from t1 where f1 is null; +drop table t1; + +create table t1 (f1 smallint) +partition by range (f1) (partition p0 values less than (0)); +insert into t1 values (null); +select * from t1 where f1 is null; +drop table t1; + +create table t1 (f1 integer) partition by list(f1) +( + partition p1 values in (1), + partition p2 values in (NULL), + partition p3 values in (2), + partition p4 values in (3), + partition p5 values in (4) +); + +insert into t1 values (1),(2),(3),(4),(null); +select * from t1 where f1 < 3; +explain partitions select * from t1 where f1 < 3; +select * from t1 where f1 is null; +explain partitions select * from t1 where f1 is null; +drop table t1; + +create table t1 (f1 int) partition by list(f1 div 2) +( + partition p1 values in (1), + partition p2 values in (NULL), + partition p3 values in (2), + partition p4 values in (3), + partition p5 values in (4) +); + +insert into t1 values (2),(4),(6),(8),(null); +select * from t1 where f1 < 3; +explain partitions select * from t1 where f1 < 3; +select * from t1 where f1 is null; +explain partitions select * from t1 where f1 is null; +drop table t1; + +create table t1 (a int) partition by LIST(a) ( + partition pn values in (NULL), + partition p0 values in (0), + partition p1 values in (1), + partition p2 values in (2) +); +insert into t1 values (NULL),(0),(1),(2); +select * from t1 where a is null or a < 2; +explain partitions select * from t1 where a is null or a < 2; +select * from t1 where a is null or a < 0 or a > 1; +explain partitions select * from t1 where a is null or a < 0 or a > 1; +drop table t1; + --echo End of 5.1 tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e9ebd9cbccb..36de27ef3e3 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2296,6 +2296,7 @@ bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond) RANGE_OPT_PARAM *range_par= &prune_param.range_param; prune_param.part_info= part_info; + prune_param.part_iter.has_null_value= FALSE; init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0); range_par->mem_root= &alloc; diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index 682c74dc31e..d8a886d2227 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -2868,9 +2868,6 @@ uint32 get_partition_id_range_for_endpoint(partition_info *part_info, /* Get the partitioning function value for the endpoint */ longlong part_func_value= part_val_int(part_info->part_expr); - if (part_info->part_expr->null_value) - DBUG_RETURN(0); - while (max_part_id > min_part_id) { loc_part_id= (max_part_id + min_part_id + 1) >> 1; @@ -5745,6 +5742,19 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info, else DBUG_ASSERT(0); + if (field->real_maybe_null() && part_info->has_null_value) + { + if (*min_value) + { + if (*max_value && !(flags & (NO_MIN_RANGE | NO_MAX_RANGE))) + { + init_single_partition_iterator(part_info->has_null_part_id, part_iter); + return 1; + } + if (!(flags & NEAR_MIN)) + part_iter->has_null_value= TRUE; + } + } /* Find minimum */ if (flags & NO_MIN_RANGE) part_iter->part_nums.start= 0; @@ -5956,7 +5966,14 @@ uint32 get_next_partition_id_range(PARTITION_ITERATOR* part_iter) uint32 get_next_partition_id_list(PARTITION_ITERATOR *part_iter) { if (part_iter->part_nums.start == part_iter->part_nums.end) + { + if (part_iter->has_null_value) + { + part_iter->has_null_value= FALSE; + return part_iter->part_info->has_null_part_id; + } return NOT_A_PARTITION_ID; + } else return part_iter->part_info->list_array[part_iter-> part_nums.start++].partition_id; diff --git a/sql/sql_partition.h b/sql/sql_partition.h index 4982b1fcf1f..c214be81ae4 100644 --- a/sql/sql_partition.h +++ b/sql/sql_partition.h @@ -124,7 +124,7 @@ typedef uint32 (*partition_iter_func)(st_partition_iter* part_iter); typedef struct st_partition_iter { partition_iter_func get_next; - + bool has_null_value; struct st_part_num_range { uint32 start; |