diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/partition.test | 6 | ||||
| -rw-r--r-- | mysql-test/t/partition_datatype.test | 310 |
2 files changed, 316 insertions, 0 deletions
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 7d7b7a2cc54..2a8ba15d3f1 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -227,10 +227,16 @@ INSERT INTO t1 VALUES ('2009-07-14 17:35:55', 'pmax'); INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax'); SELECT * FROM t1; +SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; ALTER TABLE t1 REORGANIZE PARTITION pmax INTO ( PARTITION p3 VALUES LESS THAN (1247688000), PARTITION pmax VALUES LESS THAN MAXVALUE); SELECT * FROM t1; +SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; SHOW CREATE TABLE t1; DROP TABLE t1; diff --git a/mysql-test/t/partition_datatype.test b/mysql-test/t/partition_datatype.test index 0a9fae15354..967e1cddb4e 100644 --- a/mysql-test/t/partition_datatype.test +++ b/mysql-test/t/partition_datatype.test @@ -7,6 +7,7 @@ # BUG#48164 limited size to 3072 bytes # -- source include/have_partition.inc +-- source include/have_innodb.inc --disable_warnings drop table if exists t1; @@ -233,3 +234,312 @@ show create table t1; insert into t1 values (1),(4),(7),(10),(13),(16),(19),(22),(25),(28),(31),(34); select hex(a) from t1 where a = 7; drop table t1; + +--echo # +--echo # Bug#28928: UNIX_TIMESTAMP() should be considered unary monotonic +--echo # by partition pruning +SET @old_time_zone= @@session.time_zone; +SET @@session.time_zone = 'UTC'; +--echo # Using MyISAM to get stable values on TABLE_ROWS in I_S.PARTITIONS +CREATE TABLE t1 +(a TIMESTAMP NULL, + tz varchar(16)) +ENGINE = MyISAM; +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 PARTITION BY RANGE (UNIX_TIMESTAMP(a)) +(PARTITION `p0` VALUES LESS THAN (0), + PARTITION `p-2000` VALUES LESS THAN (UNIX_TIMESTAMP('2000-01-01')), + PARTITION `p-2011-MSK` VALUES LESS THAN (UNIX_TIMESTAMP('2011-03-26 23:00:00')), + PARTITION `p-2011-MSD-1` VALUES LESS THAN (UNIX_TIMESTAMP('2011-10-29 22:00:00')), + PARTITION `p-2011-MSD-2` VALUES LESS THAN (UNIX_TIMESTAMP('2011-10-29 23:00:00')), + PARTITION `p-2012-MSK-1` VALUES LESS THAN (UNIX_TIMESTAMP('2011-10-30 00:00:00')), + PARTITION `p-2012-MSK-2` VALUES LESS THAN (UNIX_TIMESTAMP('2012-03-24 23:00:00')), + PARTITION `pEnd` VALUES LESS THAN (UNIX_TIMESTAMP('2038-01-19 03:14:07')), + PARTITION `pMax` VALUES LESS THAN MAXVALUE); + + +--echo # Test 'odd' values +INSERT INTO t1 VALUES (NULL, 'UTC'); +INSERT INTO t1 VALUES ('0000-00-00 00:00:00', 'UTC'); +--echo # Test invalid values +INSERT INTO t1 VALUES ('1901-01-01 00:00:00', 'UTCI'); +INSERT INTO t1 VALUES ('1969-12-31 23:59:59', 'UTCI'); +INSERT INTO t1 VALUES ('2038-01-19 03:14:08', 'UTCI'); +INSERT INTO t1 VALUES ('1970-01-01 00:00:00', 'UTCI'); +--echo # Test start range +INSERT INTO t1 VALUES ('1970-01-01 00:00:01', 'UTC'); +INSERT INTO t1 VALUES ('1974-02-05 21:28:16', 'UTC'); +--echo # Test end range +INSERT INTO t1 VALUES ('2038-01-19 03:14:06', 'UTC'); +INSERT INTO t1 VALUES ('2038-01-19 03:14:07', 'UTC'); +--echo # Test Daylight saving shift +INSERT INTO t1 VALUES ('2011-03-26 22:59:59', 'UTC'); +INSERT INTO t1 VALUES ('2011-03-26 23:00:00', 'UTC'); +INSERT INTO t1 VALUES ('2011-03-26 23:00:01', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-29 21:59:59', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-29 22:00:00', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-29 22:00:01', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-29 22:59:59', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-29 23:00:00', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-29 23:00:01', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-29 23:59:59', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-30 00:00:00', 'UTC'); +INSERT INTO t1 VALUES ('2011-10-30 00:00:01', 'UTC'); + +SET @@session.time_zone = 'Europe/Moscow'; + +--echo # Test 'odd' values +INSERT INTO t1 VALUES (NULL, 'Moscow'); +INSERT INTO t1 VALUES ('0000-00-00 00:00:00', 'Moscow'); +--echo # Test invalid values +INSERT INTO t1 VALUES ('0000-00-00 03:00:00', 'MoscowI'); +INSERT INTO t1 VALUES ('1901-01-01 00:00:00', 'MoscowI'); +INSERT INTO t1 VALUES ('1969-12-31 23:59:59', 'MoscowI'); +INSERT INTO t1 VALUES ('1970-01-01 02:29:29', 'MoscowI'); +INSERT INTO t1 VALUES ('2038-01-19 06:14:08', 'MoscowI'); +INSERT INTO t1 VALUES ('1970-01-01 03:00:00', 'MoscowI'); +--echo # values truncated to 03:00:00 due to daylight saving shift +INSERT INTO t1 VALUES ('2011-03-27 02:00:00', 'MoscowI'); +INSERT INTO t1 VALUES ('2011-03-27 02:00:01', 'MoscowI'); +INSERT INTO t1 VALUES ('2011-03-27 02:59:59', 'MoscowI'); +--echo # Test start range +INSERT INTO t1 VALUES ('1970-01-01 03:00:01', 'Moscow'); +INSERT INTO t1 VALUES ('1974-02-05 21:28:16', 'Moscow'); +--echo # Test end range +INSERT INTO t1 VALUES ('2038-01-19 06:14:06', 'Moscow'); +INSERT INTO t1 VALUES ('2038-01-19 06:14:07', 'Moscow'); +--echo # Test Daylight saving shift +INSERT INTO t1 VALUES ('2011-03-27 01:59:59', 'Moscow'); +INSERT INTO t1 VALUES ('2011-03-27 03:00:00', 'Moscow'); +INSERT INTO t1 VALUES ('2011-03-27 03:00:01', 'Moscow'); +INSERT INTO t1 VALUES ('2011-10-30 01:59:59', 'Moscow'); +--echo # All values between 02:00 and 02:59:59 will be interpretated as DST +INSERT INTO t1 VALUES ('2011-10-30 02:00:00', 'MoscowD'); +INSERT INTO t1 VALUES ('2011-10-30 02:00:01', 'MoscowD'); +INSERT INTO t1 VALUES ('2011-10-30 02:59:59', 'MoscowD'); +INSERT INTO t1 VALUES ('2011-10-30 03:00:00', 'Moscow'); +INSERT INTO t1 VALUES ('2011-10-30 03:00:01', 'Moscow'); + + +SET @@session.time_zone = 'UTC'; + +INSERT INTO t2 SELECT * FROM t1; + +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 't2'; + +SELECT * FROM t1 ORDER BY a, tz; +SELECT * FROM t2 ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 23:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 23:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 22:59:59' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 22:59:59' ORDER BY a, tz; + + +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-26 22:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-26 22:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-26 23:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-26 23:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; + + + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 23:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 23:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 22:59:59' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 22:59:59' ORDER BY a, tz; + + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 22:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 22:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 23:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 23:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; + + +--echo # Test end range changes +DELETE FROM t2 WHERE a = 0; +INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC'); +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t2 WHERE a = 0; +SELECT * FROM t2 ORDER BY a, tz LIMIT 3; +SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; +UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a); +SELECT MIN(a), MAX(a) FROM t2; +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t2 WHERE a = 0; +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 't2'; +SELECT * FROM t2 ORDER BY a, tz; + +--echo # Test start range changes +INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC'); +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t2 WHERE a = 0; +SELECT * FROM t2 ORDER BY a, tz LIMIT 3; +SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; +UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a); +SELECT MIN(a), MAX(a) FROM t2; +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t2 WHERE a = 0; +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 't2'; +SELECT * FROM t2 ORDER BY a, tz; + +SHOW CREATE TABLE t2; +TRUNCATE TABLE t2; + +SET @@session.time_zone = 'Europe/Moscow'; + +INSERT INTO t2 SELECT * FROM t1; + +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 't2'; + +SELECT * FROM t1 ORDER BY a, tz; +SELECT * FROM t2 ORDER BY a, tz; + +--echo # Testing the leap from 01:59:59 to 03:00:00 +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 03:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 03:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 01:59:59' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 01:59:59' ORDER BY a, tz; + + +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-26 01:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-26 01:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-26 03:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-03-26 03:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; + + + +--echo # Testing the leap from 02:59:59 to 02:00:00 +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:59:59' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:59:59' ORDER BY a, tz; + + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 03:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 03:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 01:59:59' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 01:59:59' ORDER BY a, tz; + + + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 02:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 02:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 02:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 02:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; + + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 03:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 03:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; + +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 01:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; +EXPLAIN PARTITIONS +SELECT * FROM t2 +WHERE a BETWEEN '2011-10-29 01:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; + + + +--echo # Test end range changes +DELETE FROM t2 WHERE a = 0; +INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow'); +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t2 WHERE a = 0; +SELECT * FROM t2 ORDER BY a, tz LIMIT 3; +SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; +UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a); +SELECT MIN(a), MAX(a) FROM t2; +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t2 WHERE a = 0; +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 't2'; +SELECT * FROM t2 ORDER BY a, tz; + +--echo # Test start range changes +INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow'); +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t2 WHERE a = 0; +SELECT * FROM t2 ORDER BY a, tz LIMIT 3; +SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; +UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a); +SELECT MIN(a), MAX(a) FROM t2; +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t2 WHERE a = 0; +SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS +WHERE TABLE_NAME = 't2'; +SELECT * FROM t2 ORDER BY a, tz; + +SHOW CREATE TABLE t2; +TRUNCATE TABLE t2; + +DROP TABLE t1, t2; +SET @@session.time_zone= @old_time_zone; |