merge

1 files changed, 310 insertions, 0 deletions

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;