diff options
Diffstat (limited to 'mysql-test/r/partition_pruning.result')
| -rw-r--r-- | mysql-test/r/partition_pruning.result | 288 |
1 files changed, 144 insertions, 144 deletions
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 0fb24b74218..69a43ae7238 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -640,29 +640,33 @@ PARTITION BY RANGE (TO_DAYS(a)) PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1), PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1), PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1)); -INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), +INSERT IGNORE INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 4 +Warning 1265 Data truncated for column 'a' at row 6 SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a @@ -670,45 +674,44 @@ a SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a -1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 @@ -720,55 +723,53 @@ a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 -1001-00-00 1001-01-01 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 index a a 4 NULL 6 Using where; Using index +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 index a a 4 NULL 6 Using where; Using index +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 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 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 +1 SIMPLE t1 pNULL,p2001-01-01 index 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 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 index a a 4 NULL 6 Using where; Using index +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 index a a 4 NULL 6 Using where; Using index +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 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 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 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 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 index @@ -793,7 +794,7 @@ 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 4 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 index a a 4 NULL 6 Using where; Using index +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 index a a 4 NULL 5 Using where; Using index @@ -801,28 +802,30 @@ 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 index a a 4 NULL 6 Using where; Using index # test without index +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; ALTER TABLE t1 DROP KEY a; +SET sql_mode = DEFAULT; SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a @@ -830,45 +833,44 @@ a SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a -1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 @@ -880,27 +882,25 @@ a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 -1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra @@ -969,29 +969,33 @@ PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')), PARTITION `pNULL` VALUES IN (NULL), PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')), PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01'))); -INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), +INSERT IGNORE INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 4 +Warning 1265 Data truncated for column 'a' at row 6 SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a @@ -999,45 +1003,44 @@ a SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a -1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 @@ -1049,55 +1052,53 @@ a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 -1001-00-00 1001-01-01 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 +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 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 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index +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 +1 SIMPLE t1 p2001-01-01,pNULL,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 p2001-01-01,pNULL range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 p2001-01-01,pNULL index 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 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 +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 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 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 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 +1 SIMPLE t1 p2001-01-01,pNULL,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 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p2001-01-01,pNULL,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 ref a a 4 const 1 Using index @@ -1122,7 +1123,7 @@ 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 '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 3 Using where; Using index +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 index a a 4 NULL 4 Using where; Using index @@ -1130,28 +1131,30 @@ 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 +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; ALTER TABLE t1 DROP KEY a; +SET sql_mode = DEFAULT; SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a @@ -1159,45 +1162,44 @@ a SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a -1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 @@ -1209,27 +1211,25 @@ a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 -1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra @@ -1298,29 +1298,33 @@ PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')), PARTITION `pNULL` VALUES IN (NULL), PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')), PARTITION `p1001-01-01` VALUES IN (TO_SECONDS('1001-01-01'))); -INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), +INSERT IGNORE INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 4 +Warning 1265 Data truncated for column 'a' at row 6 SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a @@ -1328,45 +1332,44 @@ a SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a -1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 @@ -1378,55 +1381,53 @@ a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 -1001-00-00 1001-01-01 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 +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 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 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index +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 +1 SIMPLE t1 p2001-01-01,pNULL,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 p2001-01-01,pNULL range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 p2001-01-01,pNULL index 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 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 +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 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 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 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 +1 SIMPLE t1 p2001-01-01,pNULL,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 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p2001-01-01,pNULL,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 ref a a 4 const 1 Using index @@ -1451,7 +1452,7 @@ 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 '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 3 Using where; Using index +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 index a a 4 NULL 4 Using where; Using index @@ -1459,28 +1460,30 @@ 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 +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; ALTER TABLE t1 DROP KEY a; +SET sql_mode = DEFAULT; SELECT * FROM t1 WHERE a < '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a <= '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a >= '1001-01-01'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-01-01'; a -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-01-01'; a @@ -1488,45 +1491,44 @@ a SELECT * FROM t1 WHERE a < '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 SELECT * FROM t1 WHERE a <= '1001-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 SELECT * FROM t1 WHERE a >= '1001-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a > '1001-00-00'; a 1001-01-01 -1002-00-00 2001-01-01 SELECT * FROM t1 WHERE a = '1001-00-00'; a -1001-00-00 # Disabling warnings for the invalid date SELECT * FROM t1 WHERE a < '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a <= '1999-02-31'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a >= '1999-02-31'; a 2001-01-01 @@ -1538,27 +1540,25 @@ a SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; a 0000-00-00 +0000-00-00 +0000-00-00 0000-01-02 0001-01-01 -1001-00-00 1001-01-01 SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; a -1001-00-00 1001-01-01 -1002-00-00 SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; a 0001-01-01 -1001-00-00 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra |