summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/partition_pruning.result114
-rw-r--r--mysql-test/t/partition_pruning.test48
-rw-r--r--sql/sql_partition.cc39
3 files changed, 184 insertions, 17 deletions
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index fde7a3b8104..3d572726507 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -3294,3 +3294,117 @@ explain partitions select * from t1 where a between 10 and 10+33;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 100 Using where
drop table t0, t1;
+#
+# Bug#71095: Wrong results with PARTITION BY LIST COLUMNS()
+#
+CREATE TABLE t1
+(c1 int,
+c2 int,
+c3 int,
+c4 int,
+PRIMARY KEY (c1,c2))
+PARTITION BY LIST COLUMNS (c2)
+(PARTITION p1 VALUES IN (1,2),
+PARTITION p2 VALUES IN (3,4));
+INSERT INTO t1 VALUES (1, 1, 1, 1), (2, 3, 1, 1);
+INSERT INTO t1 VALUES (1, 2, 1, 1), (2, 4, 1, 1);
+SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1;
+c1 c2 c3 c4
+SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1;
+c1 c2 c3 c4
+1 1 1 1
+SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
+c1 c2 c3 c4
+1 1 1 1
+SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1;
+c1 c2 c3 c4
+1 1 1 1
+1 2 1 1
+SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1;
+c1 c2 c3 c4
+1 2 1 1
+SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3;
+c1 c2 c3 c4
+1 1 1 1
+1 2 1 1
+SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3;
+c1 c2 c3 c4
+1 1 1 1
+1 2 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3;
+c1 c2 c3 c4
+2 3 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3;
+c1 c2 c3 c4
+2 3 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3;
+c1 c2 c3 c4
+2 3 1 1
+2 4 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3;
+c1 c2 c3 c4
+2 4 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4;
+c1 c2 c3 c4
+2 3 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4;
+c1 c2 c3 c4
+2 3 1 1
+2 4 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4;
+c1 c2 c3 c4
+2 4 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4;
+c1 c2 c3 c4
+2 4 1 1
+SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4;
+c1 c2 c3 c4
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 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 t1 WHERE c1 = 1 AND c2 <= 1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1 range PRIMARY PRIMARY 8 NULL 1 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1 const PRIMARY PRIMARY 8 const,const 1
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1 range PRIMARY PRIMARY 8 NULL 1 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p2 const PRIMARY PRIMARY 8 const,const 1
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2 range PRIMARY PRIMARY 8 NULL 2 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p2 const PRIMARY PRIMARY 8 const,const 1
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p2 range PRIMARY PRIMARY 8 NULL 1 Using where
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4;
+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;
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index f2ca16b9259..fc09f40ed18 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -1410,3 +1410,51 @@ explain partitions select * from t1 where a between 10 and 13;
explain partitions select * from t1 where a between 10 and 10+33;
drop table t0, t1;
+
+--echo #
+--echo # Bug#71095: Wrong results with PARTITION BY LIST COLUMNS()
+--echo #
+CREATE TABLE t1
+(c1 int,
+ c2 int,
+ c3 int,
+ c4 int,
+ PRIMARY KEY (c1,c2))
+PARTITION BY LIST COLUMNS (c2)
+(PARTITION p1 VALUES IN (1,2),
+ PARTITION p2 VALUES IN (3,4));
+INSERT INTO t1 VALUES (1, 1, 1, 1), (2, 3, 1, 1);
+INSERT INTO t1 VALUES (1, 2, 1, 1), (2, 4, 1, 1);
+SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3;
+SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4;
+SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 >= 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 > 1;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 < 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 1 AND c2 <= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 3;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 < 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 <= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 = 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 >= 4;
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE c1 = 2 AND c2 > 4;
+DROP TABLE t1;
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index e2ff4d07f1a..d7d362dbdfb 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -3304,19 +3304,28 @@ uint32 get_partition_id_cols_list_for_endpoint(partition_info *part_info,
uint num_columns= part_info->part_field_list.elements;
uint list_index;
uint min_list_index= 0;
+ int cmp;
+ /* Notice that max_list_index = last_index + 1 here! */
uint max_list_index= part_info->num_list_values;
DBUG_ENTER("get_partition_id_cols_list_for_endpoint");
/* Find the matching partition (including taking endpoint into account). */
do
{
- /* Midpoint, adjusted down, so it can never be > last index. */
+ /* Midpoint, adjusted down, so it can never be >= max_list_index. */
list_index= (max_list_index + min_list_index) >> 1;
- if (cmp_rec_and_tuple_prune(list_col_array + list_index*num_columns,
- nparts, left_endpoint, include_endpoint) > 0)
+ cmp= cmp_rec_and_tuple_prune(list_col_array + list_index*num_columns,
+ nparts, left_endpoint, include_endpoint);
+ if (cmp > 0)
+ {
min_list_index= list_index + 1;
+ }
else
+ {
max_list_index= list_index;
+ if (cmp == 0)
+ break;
+ }
} while (max_list_index > min_list_index);
list_index= max_list_index;
@@ -3333,12 +3342,10 @@ uint32 get_partition_id_cols_list_for_endpoint(partition_info *part_info,
nparts, left_endpoint,
include_endpoint)));
- if (!left_endpoint)
- {
- /* Set the end after this list tuple if not already after the last. */
- if (list_index < part_info->num_parts)
- list_index++;
- }
+ /* Include the right endpoint if not already passed end of array. */
+ if (!left_endpoint && include_endpoint && cmp == 0 &&
+ list_index < part_info->num_list_values)
+ list_index++;
DBUG_RETURN(list_index);
}
@@ -7493,15 +7500,13 @@ static int cmp_rec_and_tuple_prune(part_column_list_val *val,
field= val->part_info->part_field_array + n_vals_in_rec;
if (!(*field))
{
- /*
- Full match, if right endpoint and not including the endpoint,
- (rec < part) return lesser.
- */
- if (!is_left_endpoint && !include_endpoint)
- return -4;
+ /* Full match. Only equal if including endpoint. */
+ if (include_endpoint)
+ return 0;
- /* Otherwise they are equal! */
- return 0;
+ if (is_left_endpoint)
+ return +4; /* Start of range, part_tuple < rec, return higher. */
+ return -4; /* End of range, rec < part_tupe, return lesser. */
}
/*
The prefix is equal and there are more partition columns to compare.
View Lorry Controller Status