summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/include/partition_date_range.inc63
-rw-r--r--mysql-test/r/partition_pruning.result425
-rw-r--r--mysql-test/r/partition_range.result4
-rw-r--r--mysql-test/t/partition_pruning.test44
-rw-r--r--sql/item.h9
-rw-r--r--sql/item_timefunc.cc21
-rw-r--r--sql/opt_range.cc4
-rw-r--r--sql/partition_info.h1
-rw-r--r--sql/sql_partition.cc65
9 files changed, 624 insertions, 12 deletions
diff --git a/mysql-test/include/partition_date_range.inc b/mysql-test/include/partition_date_range.inc
new file mode 100644
index 00000000000..c54396af9cb
--- /dev/null
+++ b/mysql-test/include/partition_date_range.inc
@@ -0,0 +1,63 @@
+# Created for verifying bug#20577.
+# expects TABLE t1 (... , a DATE, ...)
+
+--sorted_result
+SELECT * FROM t1 WHERE a < '1001-01-01';
+--sorted_result
+SELECT * FROM t1 WHERE a <= '1001-01-01';
+--sorted_result
+SELECT * FROM t1 WHERE a >= '1001-01-01';
+--sorted_result
+SELECT * FROM t1 WHERE a > '1001-01-01';
+--sorted_result
+SELECT * FROM t1 WHERE a = '1001-01-01';
+--sorted_result
+SELECT * FROM t1 WHERE a < '1001-00-00';
+--sorted_result
+SELECT * FROM t1 WHERE a <= '1001-00-00';
+--sorted_result
+SELECT * FROM t1 WHERE a >= '1001-00-00';
+--sorted_result
+SELECT * FROM t1 WHERE a > '1001-00-00';
+--sorted_result
+SELECT * FROM t1 WHERE a = '1001-00-00';
+--sorted_result
+SELECT * FROM t1 WHERE a < '1999-02-31';
+--sorted_result
+SELECT * FROM t1 WHERE a <= '1999-02-31';
+--sorted_result
+SELECT * FROM t1 WHERE a >= '1999-02-31';
+--sorted_result
+SELECT * FROM t1 WHERE a > '1999-02-31';
+--sorted_result
+SELECT * FROM t1 WHERE a = '1999-02-31';
+--sorted_result
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
+--sorted_result
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
+--sorted_result
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
+--sorted_result
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
+if ($explain_partitions)
+{
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
+}
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index 26ddc92e97b..7ac1e24cfab 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -1,4 +1,429 @@
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+# test of RANGE and index
+CREATE TABLE t1 (a DATE, KEY(a))
+PARTITION BY RANGE (TO_DAYS(a))
+(PARTITION `pNULL` VALUES LESS THAN (0),
+PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1),
+PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1),
+PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1));
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+SELECT * FROM t1 WHERE a < '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+SELECT * FROM t1 WHERE a <= '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+SELECT * FROM t1 WHERE a >= '1001-01-01';
+a
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a > '1001-01-01';
+a
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a = '1001-01-01';
+a
+1001-01-01
+SELECT * FROM t1 WHERE a < '1001-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+SELECT * FROM t1 WHERE a <= '1001-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+SELECT * FROM t1 WHERE a >= '1001-00-00';
+a
+1001-00-00
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a > '1001-00-00';
+a
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a = '1001-00-00';
+a
+1001-00-00
+SELECT * FROM t1 WHERE a < '1999-02-31';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a <= '1999-02-31';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a >= '1999-02-31';
+a
+2001-01-01
+SELECT * FROM t1 WHERE a > '1999-02-31';
+a
+2001-01-01
+SELECT * FROM t1 WHERE a = '1999-02-31';
+a
+Warning 1292 Incorrect date value: '1999-02-31' for column 'a' at row 1
+Warnings:
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
+a
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
+a
+0001-01-01
+1001-00-00
+1001-01-01
+# test without index
+ALTER TABLE t1 DROP KEY a;
+SELECT * FROM t1 WHERE a < '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+SELECT * FROM t1 WHERE a <= '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+SELECT * FROM t1 WHERE a >= '1001-01-01';
+a
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a > '1001-01-01';
+a
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a = '1001-01-01';
+a
+1001-01-01
+SELECT * FROM t1 WHERE a < '1001-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+SELECT * FROM t1 WHERE a <= '1001-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+SELECT * FROM t1 WHERE a >= '1001-00-00';
+a
+1001-00-00
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a > '1001-00-00';
+a
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a = '1001-00-00';
+a
+1001-00-00
+SELECT * FROM t1 WHERE a < '1999-02-31';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a <= '1999-02-31';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a >= '1999-02-31';
+a
+2001-01-01
+SELECT * FROM t1 WHERE a > '1999-02-31';
+a
+2001-01-01
+SELECT * FROM t1 WHERE a = '1999-02-31';
+a
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
+a
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
+a
+0001-01-01
+1001-00-00
+1001-01-01
+DROP TABLE t1;
+# test of LIST and index
+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 `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')),
+PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01')));
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+SELECT * FROM t1 WHERE a < '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+SELECT * FROM t1 WHERE a <= '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+SELECT * FROM t1 WHERE a >= '1001-01-01';
+a
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a > '1001-01-01';
+a
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a = '1001-01-01';
+a
+1001-01-01
+SELECT * FROM t1 WHERE a < '1001-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+SELECT * FROM t1 WHERE a <= '1001-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+SELECT * FROM t1 WHERE a >= '1001-00-00';
+a
+1001-00-00
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a > '1001-00-00';
+a
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a = '1001-00-00';
+a
+1001-00-00
+SELECT * FROM t1 WHERE a < '1999-02-31';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a <= '1999-02-31';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a >= '1999-02-31';
+a
+2001-01-01
+SELECT * FROM t1 WHERE a > '1999-02-31';
+a
+2001-01-01
+SELECT * FROM t1 WHERE a = '1999-02-31';
+a
+Warning 1292 Incorrect date value: '1999-02-31' for column 'a' at row 1
+Warnings:
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
+a
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
+a
+0001-01-01
+1001-00-00
+1001-01-01
+# test without index
+ALTER TABLE t1 DROP KEY a;
+SELECT * FROM t1 WHERE a < '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+SELECT * FROM t1 WHERE a <= '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+SELECT * FROM t1 WHERE a >= '1001-01-01';
+a
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a > '1001-01-01';
+a
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a = '1001-01-01';
+a
+1001-01-01
+SELECT * FROM t1 WHERE a < '1001-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+SELECT * FROM t1 WHERE a <= '1001-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+SELECT * FROM t1 WHERE a >= '1001-00-00';
+a
+1001-00-00
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a > '1001-00-00';
+a
+1001-01-01
+1002-00-00
+2001-01-01
+SELECT * FROM t1 WHERE a = '1001-00-00';
+a
+1001-00-00
+SELECT * FROM t1 WHERE a < '1999-02-31';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a <= '1999-02-31';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a >= '1999-02-31';
+a
+2001-01-01
+SELECT * FROM t1 WHERE a > '1999-02-31';
+a
+2001-01-01
+SELECT * FROM t1 WHERE a = '1999-02-31';
+a
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
+a
+0000-00-00
+0000-01-02
+0001-01-01
+1001-00-00
+1001-01-01
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
+a
+1001-00-00
+1001-01-01
+1002-00-00
+SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
+a
+0001-01-01
+1001-00-00
+1001-01-01
+DROP TABLE t1;
CREATE TABLE t1
(a INT NOT NULL AUTO_INCREMENT,
b DATETIME,
diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result
index e8fc55b759b..02d2f6359c5 100644
--- a/mysql-test/r/partition_range.result
+++ b/mysql-test/r/partition_range.result
@@ -745,7 +745,7 @@ a
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE a >= '2004-07-01' AND a <= '2004-09-30';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p407,p408,p409 ALL NULL NULL NULL NULL 9 Using where
+1 SIMPLE t1 p3xx,p407,p408,p409 ALL NULL NULL NULL NULL 18 Using where
SELECT * from t1
WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
(a >= '2005-07-01' AND a <= '2005-09-30');
@@ -772,7 +772,7 @@ EXPLAIN PARTITIONS SELECT * from t1
WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
(a >= '2005-07-01' AND a <= '2005-09-30');
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p407,p408,p409,p507,p508,p509 ALL NULL NULL NULL NULL 18 Using where
+1 SIMPLE t1 p3xx,p407,p408,p409,p507,p508,p509 ALL NULL NULL NULL NULL 27 Using where
DROP TABLE t1;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index ad102062ef8..511cfa595a4 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -9,6 +9,50 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
--enable_warnings
#
+# Bug#20577: Partitions: use of to_days() function leads to selection failures
+#
+--let $explain_partitions= 1;
+--let $verify_without_partitions= 0;
+--echo # test of RANGE and index
+CREATE TABLE t1 (a DATE, KEY(a))
+PARTITION BY RANGE (TO_DAYS(a))
+(PARTITION `pNULL` VALUES LESS THAN (0),
+ PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1),
+ PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1),
+ PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1));
+if ($verify_without_partitions)
+{
+ALTER TABLE t1 REMOVE PARTITIONING;
+}
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+ ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+--source include/partition_date_range.inc
+--echo # test without index
+ALTER TABLE t1 DROP KEY a;
+--source include/partition_date_range.inc
+DROP TABLE t1;
+
+--echo # test of LIST and index
+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 `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')),
+ PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01')));
+if ($verify_without_partitions)
+{
+ALTER TABLE t1 REMOVE PARTITIONING;
+}
+INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
+ ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
+--source include/partition_date_range.inc
+--echo # test without index
+ALTER TABLE t1 DROP KEY a;
+--source include/partition_date_range.inc
+DROP TABLE t1;
+
+#
# Bug#40972: some sql execution lead the whole database crashing
#
# Setup so the start is at partition pX and end is at p1
diff --git a/sql/item.h b/sql/item.h
index 464085cb101..808e2370b1e 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -397,13 +397,20 @@ public:
from INT_RESULT, may be NULL, or are unsigned.
It will be possible to address this issue once the related partitioning bugs
(BUG#16002, BUG#15447, BUG#13436) are fixed.
+
+ The NOT_NULL enums are used in TO_DAYS, since TO_DAYS('2001-00-00') returns
+ NULL which puts those rows into the NULL partition, but
+ '2000-12-31' < '2001-00-00' < '2001-01-01'. So special handling is needed
+ for this (see Bug#20577).
*/
typedef enum monotonicity_info
{
NON_MONOTONIC, /* none of the below holds */
MONOTONIC_INCREASING, /* F() is unary and (x < y) => (F(x) <= F(y)) */
- MONOTONIC_STRICT_INCREASING /* F() is unary and (x < y) => (F(x) < F(y)) */
+ MONOTONIC_INCREASING_NOT_NULL, /* But only for valid/real x and y */
+ MONOTONIC_STRICT_INCREASING,/* F() is unary and (x < y) => (F(x) < F(y)) */
+ MONOTONIC_STRICT_INCREASING_NOT_NULL /* But only for valid/real x and y */
} enum_monotonicity_info;
/*************************************************************************/
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index d79b0b02998..eefe47232ae 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -960,9 +960,9 @@ enum_monotonicity_info Item_func_to_days::get_monotonicity_info() const
if (args[0]->type() == Item::FIELD_ITEM)
{
if (args[0]->field_type() == MYSQL_TYPE_DATE)
- return MONOTONIC_STRICT_INCREASING;
+ return MONOTONIC_STRICT_INCREASING_NOT_NULL;
if (args[0]->field_type() == MYSQL_TYPE_DATETIME)
- return MONOTONIC_INCREASING;
+ return MONOTONIC_INCREASING_NOT_NULL;
}
return NON_MONOTONIC;
}
@@ -973,12 +973,27 @@ longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp)
DBUG_ASSERT(fixed == 1);
MYSQL_TIME ltime;
longlong res;
- if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE))
+ int dummy; /* unused */
+ if (get_arg0_date(&ltime, TIME_FUZZY_DATE))
{
/* got NULL, leave the incl_endp intact */
return LONGLONG_MIN;
}
res=(longlong) calc_daynr(ltime.year,ltime.month,ltime.day);
+ /* Set to NULL if invalid date, but keep the value */
+ null_value= check_date(&ltime,
+ (ltime.year || ltime.month || ltime.day),
+ (TIME_NO_ZERO_IN_DATE | TIME_NO_ZERO_DATE),
+ &dummy);
+ if (null_value)
+ {
+ /*
+ Even if the evaluation return NULL, the calc_daynr is useful for pruning
+ */
+ if (args[0]->field_type() != MYSQL_TYPE_DATE)
+ *incl_endp= TRUE;
+ return res;
+ }
if (args[0]->field_type() == MYSQL_TYPE_DATE)
{
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 47067c03a85..8dc2f4683dd 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -5826,6 +5826,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
{
tree= new (alloc) SEL_ARG(field, 0, 0);
tree->type= SEL_ARG::IMPOSSIBLE;
+ field->table->in_use->variables.sql_mode= orig_sql_mode;
goto end;
}
else
@@ -5859,7 +5860,10 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
*/
}
else
+ {
+ field->table->in_use->variables.sql_mode= orig_sql_mode;
goto end;
+ }
}
}
diff --git a/sql/partition_info.h b/sql/partition_info.h
index 415f955d5d4..8832b6ee409 100644
--- a/sql/partition_info.h
+++ b/sql/partition_info.h
@@ -300,6 +300,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->get_next= get_next_partition_id_range;
}
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index 61766e5c509..ad24af12087 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -2766,8 +2766,24 @@ uint32 get_list_array_idx_for_endpoint(partition_info *part_info,
if (part_info->part_expr->null_value)
{
- DBUG_RETURN(0);
+ /*
+ Special handling for MONOTONIC functions that can return NULL for
+ values that are comparable. I.e.
+ '2000-00-00' can be compared to '2000-01-01' but TO_DAYS('2000-00-00')
+ returns NULL which cannot be compared used <, >, <=, >= etc.
+
+ Otherwise, just return the the first index (lowest value).
+ */
+ enum_monotonicity_info monotonic;
+ monotonic= part_info->part_expr->get_monotonicity_info();
+ if (monotonic != MONOTONIC_INCREASING_NOT_NULL &&
+ monotonic != MONOTONIC_STRICT_INCREASING_NOT_NULL)
+ {
+ /* F(col) can not return NULL, return index with lowest value */
+ DBUG_RETURN(0);
+ }
}
+
if (unsigned_flag)
part_func_value-= 0x8000000000000000ULL;
DBUG_ASSERT(part_info->no_list_values);
@@ -2916,11 +2932,29 @@ uint32 get_partition_id_range_for_endpoint(partition_info *part_info,
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);
+ /*
+ Special handling for MONOTONIC functions that can return NULL for
+ values that are comparable. I.e.
+ '2000-00-00' can be compared to '2000-01-01' but TO_DAYS('2000-00-00')
+ returns NULL which cannot be compared used <, >, <=, >= etc.
+
+ Otherwise, just return the first partition
+ (may be included if not left endpoint)
+ */
+ enum_monotonicity_info monotonic;
+ monotonic= part_info->part_expr->get_monotonicity_info();
+ if (monotonic != MONOTONIC_INCREASING_NOT_NULL &&
+ monotonic != MONOTONIC_STRICT_INCREASING_NOT_NULL)
+ {
+ /* F(col) can not return NULL, return partition with lowest value */
+ if (!left_endpoint && include_endpoint)
+ DBUG_RETURN(1);
+ DBUG_RETURN(0);
+
+ }
}
+
+
if (unsigned_flag)
part_func_value-= 0x8000000000000000ULL;
if (left_endpoint && !include_endpoint)
@@ -6733,6 +6767,19 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info,
}
else
assert(0);
+
+ if (part_info->part_type == RANGE_PARTITION ||
+ part_info->has_null_value)
+ {
+ enum_monotonicity_info monotonic;
+ monotonic= part_info->part_expr->get_monotonicity_info();
+ if (monotonic == MONOTONIC_INCREASING_NOT_NULL ||
+ monotonic == MONOTONIC_STRICT_INCREASING_NOT_NULL)
+ {
+ /* col is NOT NULL, but F(col) can return NULL, add NULL partition */
+ part_iter->ret_null_part= part_iter->ret_null_part_orig= TRUE;
+ }
+ }
/*
Find minimum: Do special handling if the interval has left bound in form
@@ -6959,7 +7006,13 @@ uint32 get_next_partition_id_range(PARTITION_ITERATOR* part_iter)
{
if (part_iter->part_nums.cur >= part_iter->part_nums.end)
{
+ if (part_iter->ret_null_part)
+ {
+ part_iter->ret_null_part= FALSE;
+ return 0; /* NULL always in first range partition */
+ }
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;
}
else
@@ -6987,7 +7040,7 @@ 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.cur == part_iter->part_nums.end)
+ if (part_iter->part_nums.cur >= part_iter->part_nums.end)
{
if (part_iter->ret_null_part)
{
View Lorry Controller Status