diff options
| -rw-r--r-- | mysql-test/r/partition_pruning.result | 849 | ||||
| -rw-r--r-- | mysql-test/t/partition_pruning.test | 307 | ||||
| -rw-r--r-- | sql-common/my_time.c | 4 | ||||
| -rw-r--r-- | sql/item.cc | 50 | ||||
| -rw-r--r-- | sql/item.h | 6 | ||||
| -rw-r--r-- | sql/item_timefunc.cc | 10 | ||||
| -rw-r--r-- | sql/opt_range.cc | 22 | ||||
| -rw-r--r-- | sql/sql_partition.cc | 17 |
8 files changed, 1231 insertions, 34 deletions
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 7ac1e24cfab..86aa89be8a7 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -109,6 +109,63 @@ a 0001-01-01 1001-00-00 1001-01-01 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 2 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 2 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; @@ -210,6 +267,63 @@ a 0001-01-01 1001-00-00 1001-01-01 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where DROP TABLE t1; # test of LIST and index CREATE TABLE t1 (a DATE, KEY(a)) @@ -322,6 +436,63 @@ a 0001-01-01 1001-00-00 1001-01-01 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 2 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 2 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01 range a a 4 NULL 2 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 range a a 4 NULL 3 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; @@ -423,6 +594,684 @@ a 0001-01-01 1001-00-00 1001-01-01 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1 +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where +DROP TABLE t1; +# Test with DATETIME column NOT NULL +CREATE TABLE t1 ( +a int(10) unsigned NOT NULL, +b DATETIME NOT NULL, +PRIMARY KEY (a, b) +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), +PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), +PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), +PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), +PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), +(1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), +(1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), +(1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index +DROP TABLE t1; +# Test with DATE column NOT NULL +CREATE TABLE t1 ( +a int(10) unsigned NOT NULL, +b DATE NOT NULL, +PRIMARY KEY (a, b) +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), +PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), +PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), +PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), +PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), +(1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), +(1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), +(1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +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 b >= CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +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 b >= '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +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 b >= CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +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 b >= CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index +DROP TABLE t1; +# Test with DATETIME column NULL +CREATE TABLE t1 ( +a int(10) unsigned NOT NULL, +b DATETIME NULL +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), +PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), +PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), +PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), +PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), +(1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), +(1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), +(1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 8 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where +DROP TABLE t1; +# Test with DATE column NULL +CREATE TABLE t1 ( +a int(10) unsigned NOT NULL, +b DATE NULL +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), +PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), +PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), +PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), +PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), +(1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), +(1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), +(1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 7 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +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 b >= CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +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 b >= '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +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 b >= CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +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 b >= CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +EXPLAIN PARTITIONS SELECT * FROM t1 +WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where +DROP TABLE t1; +# For better code coverage of the patch +CREATE TABLE t1 ( +a int(10) unsigned NOT NULL, +b DATE +) PARTITION BY RANGE ( TO_DAYS(b) ) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), +PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), +PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), +PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), +PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL); +# test with an invalid date, which lead to item->null_value is set. +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p20090401 ALL NULL NULL NULL NULL 2 Using where +Warnings: +Warning 1292 Incorrect datetime value: '2009-04-99' +Warning 1292 Incorrect datetime value: '2009-04-99' DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index 511cfa595a4..11e65be45fc 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -53,6 +53,313 @@ ALTER TABLE t1 DROP KEY a; DROP TABLE t1; # +# Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) +# There is a problem when comparing DATE with DATETIME. +# In pruning it is converted into the field type +# and in row evaluation it is converted to longlong +# (like a DATETIME). +--echo # Test with DATETIME column NOT NULL +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATETIME NOT NULL, + PRIMARY KEY (a, b) +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), + (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), + (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), + (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +DROP TABLE t1; + +--echo # Test with DATE column NOT NULL +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATE NOT NULL, + PRIMARY KEY (a, b) +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), + (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), + (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), + (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +DROP TABLE t1; + +--echo # Test with DATETIME column NULL +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATETIME NULL +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), + (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'), + (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'), + (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +DROP TABLE t1; + +--echo # Test with DATE column NULL +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATE NULL +) PARTITION BY RANGE (TO_DAYS(b)) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'), + (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'), + (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'), + (1, '2009-04-07'); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03'; +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME); +EXPLAIN PARTITIONS SELECT * FROM t1 + WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME); +DROP TABLE t1; + +--echo # For better code coverage of the patch +CREATE TABLE t1 ( + a int(10) unsigned NOT NULL, + b DATE +) PARTITION BY RANGE ( TO_DAYS(b) ) +(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), + PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), + PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), + PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), + PARTITION p20090405 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL); +--echo # test with an invalid date, which lead to item->null_value is set. +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME); +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-common/my_time.c b/sql-common/my_time.c index 747c5797ed4..a1b85049934 100644 --- a/sql-common/my_time.c +++ b/sql-common/my_time.c @@ -450,9 +450,7 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time, } } - DBUG_RETURN(l_time->time_type= - (number_of_fields <= 3 ? MYSQL_TIMESTAMP_DATE : - MYSQL_TIMESTAMP_DATETIME)); + DBUG_RETURN(l_time->time_type); err: bzero((char*) l_time, sizeof(*l_time)); diff --git a/sql/item.cc b/sql/item.cc index 9551c5feaff..0ebb1d61a28 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6853,14 +6853,21 @@ void resolve_const_item(THD *thd, Item **ref, Item *comp_item) } /** - Return true if the value stored in the field is equal to the const - item. + Compare the value stored in field, with the original item. - We need to use this on the range optimizer because in some cases - we can't store the value in the field without some precision/character loss. + @param field field which the item is converted and stored in + @param item original item + + @return Return an integer greater than, equal to, or less than 0 if + the value stored in the field is greater than, equal to, + or less than the original item + + @note We only use this on the range optimizer/partition pruning, + because in some cases we can't store the value in the field + without some precision/character loss. */ -bool field_is_equal_to_item(Field *field,Item *item) +int stored_field_cmp_to_item(Field *field, Item *item) { Item_result res_type=item_cmp_type(field->result_type(), @@ -6871,28 +6878,49 @@ bool field_is_equal_to_item(Field *field,Item *item) char field_buff[MAX_FIELD_WIDTH]; String item_tmp(item_buff,sizeof(item_buff),&my_charset_bin),*item_result; String field_tmp(field_buff,sizeof(field_buff),&my_charset_bin); + enum_field_types field_type; item_result=item->val_str(&item_tmp); if (item->null_value) - return 1; // This must be true + return 0; field->val_str(&field_tmp); - return !stringcmp(&field_tmp,item_result); + + /* + If comparing DATE with DATETIME, append the time-part to the DATE. + So that the strings are equally formatted. + A DATE converted to string is 10 characters, and a DATETIME converted + to string is 19 characters. + */ + field_type= field->type(); + if (field_type == MYSQL_TYPE_DATE && + item_result->length() == 19) + field_tmp.append(" 00:00:00"); + else if (field_type == MYSQL_TYPE_DATETIME && + item_result->length() == 10) + item_result->append(" 00:00:00"); + + return stringcmp(&field_tmp,item_result); } if (res_type == INT_RESULT) - return 1; // Both where of type int + return 0; // Both are of type int if (res_type == DECIMAL_RESULT) { my_decimal item_buf, *item_val, field_buf, *field_val; item_val= item->val_decimal(&item_buf); if (item->null_value) - return 1; // This must be true + return 0; field_val= field->val_decimal(&field_buf); - return !my_decimal_cmp(item_val, field_val); + return my_decimal_cmp(item_val, field_val); } double result= item->val_real(); if (item->null_value) + return 0; + double field_result= field->val_real(); + if (field_result < result) + return -1; + else if (field_result > result) return 1; - return result == field->val_real(); + return 0; } Item_cache* Item_cache::get_cache(const Item *item) diff --git a/sql/item.h b/sql/item.h index 808e2370b1e..a2cff3ab3a9 100644 --- a/sql/item.h +++ b/sql/item.h @@ -583,8 +583,8 @@ public: left_endp FALSE <=> The interval is "x < const" or "x <= const" TRUE <=> The interval is "x > const" or "x >= const" - incl_endp IN TRUE <=> the comparison is '<' or '>' - FALSE <=> the comparison is '<=' or '>=' + incl_endp IN FALSE <=> the comparison is '<' or '>' + TRUE <=> the comparison is '<=' or '>=' OUT The same but for the "F(x) $CMP$ F(const)" comparison DESCRIPTION @@ -3125,4 +3125,4 @@ void mark_select_range_as_dependent(THD *thd, extern Cached_item *new_Cached_item(THD *thd, Item *item); extern Item_result item_cmp_type(Item_result a,Item_result b); extern void resolve_const_item(THD *thd, Item **ref, Item *cmp_item); -extern bool field_is_equal_to_item(Field *field,Item *item); +extern int stored_field_cmp_to_item(Field *field, Item *item); diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index eefe47232ae..a9e5c71ab56 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -1006,15 +1006,19 @@ longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp) point to day bound ("strictly less" comparison stays intact): col < '2007-09-15 00:00:00' -> TO_DAYS(col) < TO_DAYS('2007-09-15') + col > '2007-09-15 23:59:59' -> TO_DAYS(col) > TO_DAYS('2007-09-15') which is different from the general case ("strictly less" changes to "less or equal"): col < '2007-09-15 12:34:56' -> TO_DAYS(col) <= TO_DAYS('2007-09-15') */ - if (!left_endp && !(ltime.hour || ltime.minute || ltime.second || - ltime.second_part)) - ; /* do nothing */ + if ((!left_endp && !(ltime.hour || ltime.minute || ltime.second || + ltime.second_part)) || + (left_endp && ltime.hour == 23 && ltime.minute == 59 && + ltime.second == 59)) + /* do nothing */ + ; else *incl_endp= TRUE; return res; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 8dc2f4683dd..23126069a5d 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -5856,7 +5856,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field, but we'll need to convert '>' to '>=' and '<' to '<='. This will be done together with other types at the end of this function - (grep for field_is_equal_to_item) + (grep for stored_field_cmp_to_item) */ } else @@ -5934,7 +5934,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field, switch (type) { case Item_func::LT_FUNC: - if (field_is_equal_to_item(field,value)) + if (stored_field_cmp_to_item(field,value) == 0) tree->max_flag=NEAR_MAX; /* fall through */ case Item_func::LE_FUNC: @@ -5948,11 +5948,16 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field, break; case Item_func::GT_FUNC: /* Don't use open ranges for partial key_segments */ - if (field_is_equal_to_item(field,value) && - !(key_part->flag & HA_PART_KEY_SEG)) + if ((!(key_part->flag & HA_PART_KEY_SEG)) && + (stored_field_cmp_to_item(field, value) <= 0)) tree->min_flag=NEAR_MIN; - /* fall through */ + tree->max_flag= NO_MAX_RANGE; + break; case Item_func::GE_FUNC: + /* Don't use open ranges for partial key_segments */ + if ((!(key_part->flag & HA_PART_KEY_SEG)) && + (stored_field_cmp_to_item(field,value) < 0)) + tree->min_flag= NEAR_MIN; tree->max_flag=NO_MAX_RANGE; break; case Item_func::SP_EQUALS_FUNC: @@ -6443,13 +6448,6 @@ key_and(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, uint clone_flag) return 0; // Can't optimize this } - if ((key1->min_flag | key2->min_flag) & GEOM_FLAG) - { - key1->free_tree(); - key2->free_tree(); - return 0; // Can't optimize this - } - key1->use_count--; key2->use_count--; SEL_ARG *e1=key1->first(), *e2=key2->first(), *new_tree=0; diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index ad24af12087..d433ba0b0cc 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -6730,6 +6730,7 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info, Field *field= part_info->part_field_array[0]; uint32 max_endpoint_val; get_endpoint_func get_endpoint; + bool can_match_multiple_values; /* is not '=' */ uint field_len= field->pack_length_in_rec(); part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE; @@ -6768,9 +6769,13 @@ 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) + can_match_multiple_values= (flags || !min_value || !max_value || + memcmp(min_value, max_value, field_len)); + if (can_match_multiple_values && + (part_info->part_type == RANGE_PARTITION || + part_info->has_null_value)) { + /* Range scan on RANGE or LIST partitioned table */ enum_monotonicity_info monotonic; monotonic= part_info->part_expr->get_monotonicity_info(); if (monotonic == MONOTONIC_INCREASING_NOT_NULL || @@ -6812,6 +6817,14 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info, store_key_image_to_rec(field, min_value, field_len); bool include_endp= !test(flags & NEAR_MIN); part_iter->part_nums.start= get_endpoint(part_info, 1, include_endp); + if (!can_match_multiple_values && part_info->part_expr->null_value) + { + /* 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; + return 1; + } part_iter->part_nums.cur= part_iter->part_nums.start; if (part_iter->part_nums.start == max_endpoint_val) return 0; /* No partitions */ |