#At file:///Users/mattiasj/clones/bzrroot/b46362-51-bugteam/ based on revid:li-bing.song@stripped
3068 Mattias Jonsson 2009-08-14
Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
There were multiple problems since pruning uses the field
for comparison (while evaluate_join_record uses longlong)
Fix was to take better care when comparing DATE vs DATETIME.
And adding optimization for comparing with 23:59:59.
@ mysql-test/r/partition_pruning.result
Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
Updated result-file
@ mysql-test/t/partition_pruning.test
Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
Added testcases.
@ sql-common/my_time.c
Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
removed duplicate assignment.
@ sql/item.cc
Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
Changed field_is_equal_to_item into field_cmp_to_item, to
better handling DATE vs DATETIME comparision.
@ sql/item.h
Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
Updated comment
@ sql/item_timefunc.cc
Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
Added optimization (pruning) of DATETIME where time-part is
23:59:59
@ sql/opt_range.cc
Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
Using the new field_cmp_to_item for better pruning.
modified:
mysql-test/r/partition_pruning.result
mysql-test/t/partition_pruning.test
sql-common/my_time.c
sql/item.cc
sql/item.h
sql/item_timefunc.cc
sql/opt_range.cc
=== modified file 'mysql-test/r/partition_pruning.result'
--- a/mysql-test/r/partition_pruning.result 2008-12-28 11:33:49 +0000
+++ b/mysql-test/r/partition_pruning.result 2009-08-13 23:00:41 +0000
@@ -1,4 +1,536 @@
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+# 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 /* 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 /* 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+DROP TABLE t1;
CREATE TABLE t1
(a INT NOT NULL AUTO_INCREMENT,
b DATETIME,
=== modified file 'mysql-test/t/partition_pruning.test'
--- a/mysql-test/t/partition_pruning.test 2008-12-28 11:33:49 +0000
+++ b/mysql-test/t/partition_pruning.test 2009-08-13 23:00:41 +0000
@@ -9,6 +9,226 @@ drop table if exists t1,t2,t3,t4,t5,t6,t
--enable_warnings
#
+# 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 /* 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 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;
+
+#
# Bug#40972: some sql execution lead the whole database crashing
#
# Setup so the start is at partition pX and end is at p1
=== modified file 'sql-common/my_time.c'
--- a/sql-common/my_time.c 2009-06-11 16:21:32 +0000
+++ b/sql-common/my_time.c 2009-08-13 23:00:41 +0000
@@ -450,9 +450,7 @@ str_to_datetime(const char *str, uint le
}
}
- 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));
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2009-07-19 12:49:40 +0000
+++ b/sql/item.cc 2009-08-13 23:00:41 +0000
@@ -6846,14 +6846,14 @@ void resolve_const_item(THD *thd, Item *
}
/**
- Return true if the value stored in the field is equal to the const
- item.
+ 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 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.
*/
-bool field_is_equal_to_item(Field *field,Item *item)
+int field_cmp_to_item(Field *field,Item *item)
{
Item_result res_type=item_cmp_type(field->result_type(),
@@ -6864,28 +6864,44 @@ bool field_is_equal_to_item(Field *field
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; // This must be true
field->val_str(&field_tmp);
- return !stringcmp(&field_tmp,item_result);
+
+ /* Only compare the date part 'YYYY-MM-DD' */
+ 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 where 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; // This must be true
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)
=== modified file 'sql/item.h'
--- a/sql/item.h 2009-05-25 08:00:40 +0000
+++ b/sql/item.h 2009-08-13 23:00:41 +0000
@@ -576,8 +576,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
@@ -3117,4 +3117,4 @@ void mark_select_range_as_dependent(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 field_cmp_to_item(Field *field,Item *item);
=== modified file 'sql/item_timefunc.cc'
--- a/sql/item_timefunc.cc 2009-06-09 16:44:26 +0000
+++ b/sql/item_timefunc.cc 2009-08-13 23:00:41 +0000
@@ -991,15 +991,19 @@ longlong Item_func_to_days::val_int_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;
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2009-07-16 12:43:17 +0000
+++ b/sql/opt_range.cc 2009-08-13 23:00:41 +0000
@@ -5855,7 +5855,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
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 field_cmp_to_item)
*/
}
else
@@ -5930,7 +5930,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
switch (type) {
case Item_func::LT_FUNC:
- if (field_is_equal_to_item(field,value))
+ if (field_cmp_to_item(field,value) == 0)
tree->max_flag=NEAR_MAX;
/* fall through */
case Item_func::LE_FUNC:
@@ -5944,11 +5944,12 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
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))
tree->min_flag=NEAR_MIN;
/* fall through */
case Item_func::GE_FUNC:
+ if (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:
Attachment: [text/bzr-bundle]
