[Commits] e358b19: MDEV-10765: Wrong result - query does not retrieve values from default partition on a table partitioned by list columns

Oleksandr Byelkin sanja at mariadb.com
Thu Sep 8 17:36:23 EEST 2016


revision-id: e358b194f007eda20e544cd7998d46fdbab6515c (mariadb-10.1.8-242-ge358b19)
parent(s): effb65bc863da0f1115e16ef5f11d11a13cdc7a0
committer: Oleksandr Byelkin
timestamp: 2016-09-08 16:36:23 +0200
message:

MDEV-10765: Wrong result - query does not retrieve values from default partition on a table partitioned by list columns

Fixed partition pruning for NULL value.

---
 mysql-test/r/partition_default.result | 29 +++++++++++++++++++++++++++++
 mysql-test/t/partition_default.test   | 21 +++++++++++++++++++++
 sql/sql_partition.cc                  | 15 +++++++++++++--
 3 files changed, 63 insertions(+), 2 deletions(-)

diff --git a/mysql-test/r/partition_default.result b/mysql-test/r/partition_default.result
index ab9fa58..321a060 100644
--- a/mysql-test/r/partition_default.result
+++ b/mysql-test/r/partition_default.result
@@ -1149,3 +1149,32 @@ t1	CREATE TABLE `t1` (
  PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)) ENGINE = MyISAM,
  PARTITION p1 VALUES IN ((1,1),(0,0)) ENGINE = MyISAM) */
 drop table t1;
+#
+# MDEV-10765: Wrong result - query does not retrieve values from
+# default partition on a table partitioned by list columns
+#
+create table t1 (i int) partition by list (i) ( partition p1 default);
+insert into t1 values (null);
+select * from t1 where i is null;
+i
+NULL
+alter table t1 partition by list (i) ( partition p1 values in (1), partition p2 default);
+select * from t1 where i is null;
+i
+NULL
+explain partitions 
+select * from t1 where i 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	
+alter table t1 partition by list (i) (
+partition p0 values in (NULL),
+partition p1 values in (1),
+partition p2 default);
+select * from t1 where i is null;
+i
+NULL
+explain partitions 
+select * from t1 where i is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
diff --git a/mysql-test/t/partition_default.test b/mysql-test/t/partition_default.test
index 8f7fe58..71eb15a 100644
--- a/mysql-test/t/partition_default.test
+++ b/mysql-test/t/partition_default.test
@@ -453,3 +453,24 @@ create table t1 (a int, b int)
 show create table t1;
 
 drop table t1;
+
+--echo #
+--echo # MDEV-10765: Wrong result - query does not retrieve values from
+--echo # default partition on a table partitioned by list columns
+--echo #
+create table t1 (i int) partition by list (i) ( partition p1 default);
+insert into t1 values (null);
+select * from t1 where i is null;
+alter table t1 partition by list (i) ( partition p1 values in (1), partition p2 default);
+select * from t1 where i is null;
+explain partitions 
+select * from t1 where i is null;
+alter table t1 partition by list (i) (
+  partition p0 values in (NULL),
+  partition p1 values in (1),
+  partition p2 default);
+select * from t1 where i is null;
+explain partitions 
+select * from t1 where i is null;
+
+drop table t1;
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index 54396b9..caf40c0 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -7954,8 +7954,19 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info,
         /* col = x and F(x) = NULL -> only search NULL partition */
         part_iter->part_nums.cur= part_iter->part_nums.start= 0;
         part_iter->part_nums.end= 0;
-        part_iter->ret_null_part= part_iter->ret_null_part_orig= TRUE;
-        DBUG_RETURN(1);
+        /*
+          if NULL partition exists:
+            for RANGE it is the first partition (always exists);
+            for LIST should be indicator that it is present
+        */
+        if (part_info->part_type == RANGE_PARTITION ||
+            part_info->has_null_value)
+        {
+          part_iter->ret_null_part= part_iter->ret_null_part_orig= TRUE;
+          DBUG_RETURN(1);
+        }
+        // If no NULL partition look up in DEFAULT or there is no such value
+        goto not_found;
       }
       part_iter->part_nums.cur= part_iter->part_nums.start;
       if (check_zero_dates && !part_info->part_expr->null_value)


More information about the commits mailing list