# This is a BitKeeper generated diff -Nru style patch.
#
# ChangeSet
# 2006/07/20 05:28:16-04:00 mikael@dator5.(none)
# BUG20733: Bug in partition pruning with zerofill field
# Problem was with handling NULL values in ranges
#
# mysql-test/r/partition_hash.result
# 2006/07/20 05:28:13-04:00 mikael@dator5.(none) +84 -0
# New partition pruning test cases
#
# mysql-test/r/partition_list.result
# 2006/07/20 05:28:13-04:00 mikael@dator5.(none) +94 -0
# New partition pruning test cases
#
# mysql-test/r/partition_pruning.result
# 2006/07/20 05:28:13-04:00 mikael@dator5.(none) +78 -0
# New partition pruning test cases
#
# mysql-test/r/partition_range.result
# 2006/07/20 05:28:13-04:00 mikael@dator5.(none) +78 -0
# New partition pruning test cases
#
# mysql-test/t/partition_hash.test
# 2006/07/20 05:28:13-04:00 mikael@dator5.(none) +30 -0
# New partition pruning test cases
#
# mysql-test/t/partition_list.test
# 2006/07/20 05:28:13-04:00 mikael@dator5.(none) +44 -0
# New partition pruning test cases
#
# mysql-test/t/partition_pruning.test
# 2006/07/20 05:28:14-04:00 mikael@dator5.(none) +52 -0
# New partition pruning test cases
#
# mysql-test/t/partition_range.test
# 2006/07/20 05:28:14-04:00 mikael@dator5.(none) +44 -0
# New partition pruning test cases
#
# sql/opt_range.cc
# 2006/07/20 05:28:14-04:00 mikael@dator5.(none) +6 -1
# Added comment
#
# sql/sql_partition.cc
# 2006/07/20 05:28:14-04:00 mikael@dator5.(none) +11 -0
# Partition pruning didn't handle ranges with NULL values in a proper
manner
#
diff -Nru a/mysql-test/r/partition_hash.result
b/mysql-test/r/partition_hash.result
--- a/mysql-test/r/partition_hash.result 2006-07-20 05:36:21 -04:00
+++ b/mysql-test/r/partition_hash.result 2006-07-20 05:36:21 -04:00
@@ -1,4 +1,88 @@
drop table if exists t1;
+create table t1 (a int unsigned)
+partition by hash(a div 2)
+partitions 4;
+insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7);
+select * from t1 where a < 0;
+a
+select * from t1 where a is null or (a >= 5 and a <= 7);
+a
+NULL
+5
+6
+7
+select * from t1 where a is null;
+a
+NULL
+select * from t1 where a is not null;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+select * from t1 where a >= 1 and a < 3;
+a
+1
+2
+select * from t1 where a >= 3 and a <= 5;
+a
+3
+4
+5
+select * from t1 where a > 2 and a < 4;
+a
+3
+select * from t1 where a > 3 and a <= 6;
+a
+4
+5
+6
+select * from t1 where a > 5;
+a
+6
+7
+select * from t1 where a >= 1 and a <= 5;
+a
+1
+2
+3
+4
+5
+explain partitions select * from t1 where a < 0;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
+explain partitions select * from t1 where a is null or (a >= 5 and a
<= 7);
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 p0,p2,p3 ALL NULL NULL NULL NULL 7 Using where
+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 3 Using where
+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,p2,p3 ALL NULL NULL NULL NULL 9 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 p0,p1 ALL NULL NULL NULL NULL 5 Using where
+explain partitions select * from t1 where a >= 3 and a <= 5;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 4 Using where
+explain partitions select * from t1 where a > 2 and a < 4;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
+explain partitions select * from t1 where a > 3 and a <= 6;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 4 Using where
+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 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
+explain partitions select * from t1 where a >= 1 and a <= 5;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
+drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
diff -Nru a/mysql-test/r/partition_list.result
b/mysql-test/r/partition_list.result
--- a/mysql-test/r/partition_list.result 2006-07-20 05:36:21 -04:00
+++ b/mysql-test/r/partition_list.result 2006-07-20 05:36:21 -04:00
@@ -1,4 +1,98 @@
drop table if exists t1;
+create table t1 (a int unsigned)
+partition by list (a)
+(partition p0 values in (0),
+partition p1 values in (1),
+partition pnull values in (null),
+partition p2 values in (2));
+insert into t1 values (null),(0),(1),(2);
+select * from t1 where a < 2;
+a
+0
+1
+select * from t1 where a <= 0;
+a
+0
+select * from t1 where a < 1;
+a
+0
+select * from t1 where a > 0;
+a
+1
+2
+select * from t1 where a > 1;
+a
+2
+select * from t1 where a >= 0;
+a
+0
+1
+2
+select * from t1 where a >= 1;
+a
+1
+2
+select * from t1 where a is null;
+a
+NULL
+select * from t1 where a is not null;
+a
+0
+1
+2
+select * from t1 where a is null or a > 0;
+a
+1
+NULL
+2
+drop table t1;
+create table t1 (a int unsigned, b int)
+partition by list (a)
+subpartition by hash (b)
+subpartitions 2
+(partition p0 values in (0),
+partition p1 values in (1),
+partition pnull values in (null, 2),
+partition p3 values in (3));
+insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1);
+insert into t1 values (2,0),(2,1),(3,0),(3,1);
+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 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 4
Using where
+select * from t1 where a is null;
+a b
+NULL 0
+NULL 1
+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 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 4
Using where
+select * from t1 where a = 2;
+a b
+2 0
+2 1
+select * from t1 where a <= 0;
+a b
+0 0
+0 1
+select * from t1 where a < 3;
+a b
+0 0
+0 1
+1 0
+1 1
+2 0
+2 1
+select * from t1 where a >= 1 or a is null;
+a b
+1 0
+1 1
+NULL 0
+2 0
+NULL 1
+2 1
+3 0
+3 1
+drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
diff -Nru a/mysql-test/r/partition_pruning.result
b/mysql-test/r/partition_pruning.result
--- a/mysql-test/r/partition_pruning.result 2006-07-20 05:36:21 -04:00
+++ b/mysql-test/r/partition_pruning.result 2006-07-20 05:36:21 -04:00
@@ -149,7 +149,85 @@
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
+drop table t6;
+create table t6 (a int unsigned not null) partition by LIST(a) (
+partition p1 values in (1),
+partition p3 values in (3),
+partition p5 values in (5),
+partition p7 values in (7),
+partition p9 values in (9)
+);
+insert into t6 values (1),(3),(5);
+explain partitions select * from t6 where a < 1;
+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 <= 1;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+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 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 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 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 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 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 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
create table t7 (a int not null) partition by RANGE(a) (
+partition p10 values less than (10),
+partition p30 values less than (30),
+partition p50 values less than (50),
+partition p70 values less than (70),
+partition p90 values less than (90)
+);
+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 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 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 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 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
+explain partitions select * from t7 where a = 90;
+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 t7 where a > 90;
+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 t7 where a >= 90;
+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 t7 where a > 11 and a < 29;
+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
+drop table t7;
+create table t7 (a int unsigned not null) partition by RANGE(a) (
partition p10 values less than (10),
partition p30 values less than (30),
partition p50 values less than (50),
diff -Nru a/mysql-test/r/partition_range.result
b/mysql-test/r/partition_range.result
--- a/mysql-test/r/partition_range.result 2006-07-20 05:36:21 -04:00
+++ b/mysql-test/r/partition_range.result 2006-07-20 05:36:21 -04:00
@@ -1,4 +1,82 @@
drop table if exists t1;
+create table t1 (a int unsigned)
+partition by range (a)
+(partition pnull values less than (0),
+partition p0 values less than (1),
+partition p1 values less than(2));
+insert into t1 values (null),(0),(1);
+select * from t1 where a is null;
+a
+NULL
+select * from t1 where a >= 0;
+a
+0
+1
+select * from t1 where a < 0;
+a
+select * from t1 where a <= 0;
+a
+0
+select * from t1 where a > 1;
+a
+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 pnull system NULL NULL NULL NULL 1
+explain partitions select * from t1 where a >= 0;
+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
+explain partitions select * from t1 where a < 0;
+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 t1 where a <= 0;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 2 Using where
+explain partitions select * from t1 where a > 1;
+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
+drop table t1;
+create table t1 (a int unsigned, b int unsigned)
+partition by range (a)
+subpartition by hash (b)
+subpartitions 2
+(partition pnull values less than (0),
+partition p0 values less than (1),
+partition p1 values less than(2));
+insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1);
+select * from t1 where a is null;
+a b
+NULL 0
+NULL 1
+select * from t1 where a >= 0;
+a b
+0 0
+0 1
+1 0
+1 1
+select * from t1 where a < 0;
+a b
+select * from t1 where a <= 0;
+a b
+0 0
+0 1
+select * from t1 where a > 1;
+a b
+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 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2
Using where
+explain partitions select * from t1 where a >= 0;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL
NULL 4 Using where
+explain partitions select * from t1 where a < 0;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2
Using where
+explain partitions select * from t1 where a <= 0;
+id select_type table partitions type possible_keys key key_len ref
rows Extra
+1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL
NULL NULL NULL 4 Using where
+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_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 2 Using where
+drop table t1;
CREATE TABLE t1 (
a int not null,
b int not null,
diff -Nru a/mysql-test/t/partition_hash.test
b/mysql-test/t/partition_hash.test
--- a/mysql-test/t/partition_hash.test 2006-07-20 05:36:21 -04:00
+++ b/mysql-test/t/partition_hash.test 2006-07-20 05:36:21 -04:00
@@ -10,6 +10,36 @@
--enable_warnings
#
+# More partition pruning tests, especially on interval walking
+#
+create table t1 (a int unsigned)
+partition by hash(a div 2)
+partitions 4;
+insert into t1 values (null),(0),(1),(2),(3),(4),(5),(6),(7);
+select * from t1 where a < 0;
+select * from t1 where a is null or (a >= 5 and a <= 7);
+select * from t1 where a is null;
+select * from t1 where a is not null;
+select * from t1 where a >= 1 and a < 3;
+select * from t1 where a >= 3 and a <= 5;
+select * from t1 where a > 2 and a < 4;
+select * from t1 where a > 3 and a <= 6;
+select * from t1 where a > 5;
+select * from t1 where a >= 1 and a <= 5;
+explain partitions select * from t1 where a < 0;
+explain partitions select * from t1 where a is null or (a >= 5 and a
<= 7);
+explain partitions select * from t1 where a is null;
+explain partitions select * from t1 where a is not null;
+explain partitions select * from t1 where a >= 1 and a < 3;
+explain partitions select * from t1 where a >= 3 and a <= 5;
+explain partitions select * from t1 where a > 2 and a < 4;
+explain partitions select * from t1 where a > 3 and a <= 6;
+explain partitions select * from t1 where a > 5;
+explain partitions select * from t1 where a >= 1 and a <= 5;
+
+drop table t1;
+
+#
# Partition by hash, basic
#
CREATE TABLE t1 (
diff -Nru a/mysql-test/t/partition_list.test
b/mysql-test/t/partition_list.test
--- a/mysql-test/t/partition_list.test 2006-07-20 05:36:21 -04:00
+++ b/mysql-test/t/partition_list.test 2006-07-20 05:36:21 -04:00
@@ -10,6 +10,49 @@
--enable_warnings
#
+# Bug 20733: Zerofill columns gives wrong result with partitioned
tables
+#
+create table t1 (a int unsigned)
+partition by list (a)
+(partition p0 values in (0),
+ partition p1 values in (1),
+ partition pnull values in (null),
+ partition p2 values in (2));
+
+insert into t1 values (null),(0),(1),(2);
+select * from t1 where a < 2;
+select * from t1 where a <= 0;
+select * from t1 where a < 1;
+select * from t1 where a > 0;
+select * from t1 where a > 1;
+select * from t1 where a >= 0;
+select * from t1 where a >= 1;
+select * from t1 where a is null;
+select * from t1 where a is not null;
+select * from t1 where a is null or a > 0;
+drop table t1;
+
+create table t1 (a int unsigned, b int)
+partition by list (a)
+subpartition by hash (b)
+subpartitions 2
+(partition p0 values in (0),
+ partition p1 values in (1),
+ partition pnull values in (null, 2),
+ partition p3 values in (3));
+insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1);
+insert into t1 values (2,0),(2,1),(3,0),(3,1);
+
+explain partitions select * from t1 where a is null;
+select * from t1 where a is null;
+explain partitions select * from t1 where a = 2;
+select * from t1 where a = 2;
+select * from t1 where a <= 0;
+select * from t1 where a < 3;
+select * from t1 where a >= 1 or a is null;
+drop table t1;
+
+#
# Test ordinary list partitioning that it works ok
#
CREATE TABLE t1 (
@@ -135,4 +178,5 @@
insert into t1 values (null);
select * from t1;
drop table t1;
+
diff -Nru a/mysql-test/t/partition_pruning.test
b/mysql-test/t/partition_pruning.test
--- a/mysql-test/t/partition_pruning.test 2006-07-20 05:36:21 -04:00
+++ b/mysql-test/t/partition_pruning.test 2006-07-20 05:36:21 -04:00
@@ -137,8 +137,60 @@
explain partitions select * from t6 where a > 3 and a < 5;
+drop table t6;
+
+create table t6 (a int unsigned not null) partition by LIST(a) (
+ partition p1 values in (1),
+ partition p3 values in (3),
+ partition p5 values in (5),
+ partition p7 values in (7),
+ partition p9 values in (9)
+);
+insert into t6 values (1),(3),(5);
+
+explain partitions select * from t6 where a < 1;
+explain partitions select * from t6 where a <= 1;
+explain partitions select * from t6 where a > 9;
+explain partitions select * from t6 where a >= 9;
+
+explain partitions select * from t6 where a > 0 and a < 5;
+explain partitions select * from t6 where a > 5 and a < 12;
+explain partitions select * from t6 where a > 3 and a < 8 ;
+
+explain partitions select * from t6 where a >= 0 and a <= 5;
+explain partitions select * from t6 where a >= 5 and a <= 12;
+explain partitions select * from t6 where a >= 3 and a <= 8;
+
+explain partitions select * from t6 where a > 3 and a < 5;
+
# RANGE(field) partitioning, interval analysis.
create table t7 (a int not null) partition by RANGE(a) (
+ partition p10 values less than (10),
+ partition p30 values less than (30),
+ partition p50 values less than (50),
+ partition p70 values less than (70),
+ partition p90 values less than (90)
+);
+insert into t7 values (10),(30),(50);
+
+# leftmost intervals
+explain partitions select * from t7 where a < 5;
+explain partitions select * from t7 where a < 10;
+explain partitions select * from t7 where a <= 10;
+explain partitions select * from t7 where a = 10;
+
+#rightmost intervals
+explain partitions select * from t7 where a < 90;
+explain partitions select * from t7 where a = 90;
+explain partitions select * from t7 where a > 90;
+explain partitions select * from t7 where a >= 90;
+
+# misc intervals
+explain partitions select * from t7 where a > 11 and a < 29;
+
+drop table t7;
+
+create table t7 (a int unsigned not null) partition by RANGE(a) (
partition p10 values less than (10),
partition p30 values less than (30),
partition p50 values less than (50),
diff -Nru a/mysql-test/t/partition_range.test
b/mysql-test/t/partition_range.test
--- a/mysql-test/t/partition_range.test 2006-07-20 05:36:21 -04:00
+++ b/mysql-test/t/partition_range.test 2006-07-20 05:36:21 -04:00
@@ -10,6 +10,50 @@
--enable_warnings
#
+# More checks for partition pruning
+#
+create table t1 (a int unsigned)
+partition by range (a)
+(partition pnull values less than (0),
+ partition p0 values less than (1),
+ partition p1 values less than(2));
+insert into t1 values (null),(0),(1);
+
+select * from t1 where a is null;
+select * from t1 where a >= 0;
+select * from t1 where a < 0;
+select * from t1 where a <= 0;
+select * from t1 where a > 1;
+explain partitions select * from t1 where a is null;
+explain partitions select * from t1 where a >= 0;
+explain partitions select * from t1 where a < 0;
+explain partitions select * from t1 where a <= 0;
+explain partitions select * from t1 where a > 1;
+drop table t1;
+
+create table t1 (a int unsigned, b int unsigned)
+partition by range (a)
+subpartition by hash (b)
+subpartitions 2
+(partition pnull values less than (0),
+ partition p0 values less than (1),
+ partition p1 values less than(2));
+insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1);
+
+select * from t1 where a is null;
+select * from t1 where a >= 0;
+select * from t1 where a < 0;
+select * from t1 where a <= 0;
+select * from t1 where a > 1;
+explain partitions select * from t1 where a is null;
+explain partitions select * from t1 where a >= 0;
+explain partitions select * from t1 where a < 0;
+explain partitions select * from t1 where a <= 0;
+explain partitions select * from t1 where a > 1;
+
+drop table t1;
+
+#
# Partition by range, basic
#
CREATE TABLE t1 (
diff -Nru a/sql/opt_range.cc b/sql/opt_range.cc
--- a/sql/opt_range.cc 2006-07-20 05:36:21 -04:00
+++ b/sql/opt_range.cc 2006-07-20 05:36:21 -04:00
@@ -3203,7 +3203,12 @@
ppar->is_part_keypart[part]= !in_subpart_fields;
ppar->is_subpart_keypart[part]= in_subpart_fields;
-
+
+ /*
+ Check if this was last field in this array, in this case we
+ switch to subpartitioning fields. (This will only happens if
+ there are subpartitioning fields to cater for).
+ */
if (!*(++field))
{
field= part_info->subpart_field_array;
diff -Nru a/sql/sql_partition.cc b/sql/sql_partition.cc
--- a/sql/sql_partition.cc 2006-07-20 05:36:21 -04:00
+++ b/sql/sql_partition.cc 2006-07-20 05:36:21 -04:00
@@ -2415,6 +2415,10 @@
bool unsigned_flag= part_info->part_expr->unsigned_flag;
DBUG_ENTER("get_list_array_idx_for_endpoint");
+ if (part_info->part_expr->null_value)
+ {
+ DBUG_RETURN(0);
+ }
if (unsigned_flag)
part_func_value-= 0x8000000000000000ULL;
DBUG_ASSERT(part_info->no_list_values);
@@ -2539,6 +2543,13 @@
bool unsigned_flag= part_info->part_expr->unsigned_flag;
DBUG_ENTER("get_partition_id_range_for_endpoint");
+ if (part_info->part_expr->null_value)
+ {
+ uint32 ret_part_id= 0;
+ if (!left_endpoint && include_endpoint)
+ ret_part_id= 1;
+ DBUG_RETURN(ret_part_id);
+ }
if (unsigned_flag)
part_func_value-= 0x8000000000000000ULL;
while (max_part_id > min_part_id)
Mikael Ronstrom, Senior Software Architect
MySQL AB, www.mysql.com
Jumpstart your cluster:
http://www.mysql.com/consulting/packaged/cluster.html
My blog:
http://mikaelronstrom.blogspot.com
| Thread |
|---|
| • bk commit - 5.1 tree (mikael:1.2259) BUG#20733 | Mikael Ronström | 20 Jul |
