Edit File: partition_datatype.result
drop table if exists t1; # test with not null create table t1 (a bit not null) partition by key (a); insert into t1 values (b'1'); select hex(a) from t1 where a = b'1'; hex(a) 1 drop table t1; create table t1 (a tinyint not null) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a smallint not null) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a mediumint not null) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a int not null) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a bigint not null) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a float not null) partition by key (a); insert into t1 values (0.5); select * from t1 where a = 0.5; a 0.5 drop table t1; create table t1 (a double not null) partition by key (a); insert into t1 values (0.5); select * from t1 where a = 0.5; a 0.5 drop table t1; create table t1 (a decimal(4,2) not null) partition by key (a); insert into t1 values (2.1); select * from t1 where a = 2.1; a 2.10 drop table t1; create table t1 (a date not null) partition by key (a); insert into t1 values ('2001-01-01'); select * from t1 where a = '2001-01-01'; a 2001-01-01 drop table t1; create table t1 (a datetime not null) partition by key (a); insert into t1 values ('2001-01-01 01:02:03'); select * from t1 where a = '2001-01-01 01:02:03'; a 2001-01-01 01:02:03 drop table t1; create table t1 (a timestamp not null) partition by key (a); insert into t1 values ('2001-01-01 01:02:03'); select * from t1 where a = '2001-01-01 01:02:03'; a 2001-01-01 01:02:03 drop table t1; create table t1 (a time not null) partition by key (a); insert into t1 values ('01:02:03'); select * from t1 where a = '01:02:03'; a 01:02:03 drop table t1; create table t1 (a year not null) partition by key (a); insert into t1 values ('2001'); select * from t1 where a = '2001'; a 2001 drop table t1; create table t1 (a varchar(10) character set utf8 not null) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a varchar(300) character set utf8 not null) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a varchar(10) character set latin1 not null) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a varchar(300) character set latin1 not null) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a char(10) character set utf8 not null) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a char(10) character set latin1 not null) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a enum('y','n') not null) partition by key (a); insert into t1 values ('y'); select * from t1 where a = 'y'; a y drop table t1; create table t1 (a set('y','n') not null) partition by key (a); insert into t1 values ('y'); select * from t1 where a = 'y'; a y drop table t1; # test with null allowed create table t1 (a bit) partition by key (a); insert into t1 values (b'1'); insert into t1 values (NULL); select hex(a) from t1 where a = b'1'; hex(a) 1 select hex(a) from t1 where a is NULL; hex(a) NULL select hex(a) from t1 order by a; hex(a) NULL 1 drop table t1; create table t1 (a tinyint) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a smallint) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a mediumint) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a int) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a bigint) partition by key (a); insert into t1 values (2); select * from t1 where a = 2; a 2 drop table t1; create table t1 (a float) partition by key (a); insert into t1 values (0.5); select * from t1 where a = 0.5; a 0.5 drop table t1; create table t1 (a double) partition by key (a); insert into t1 values (0.5); select * from t1 where a = 0.5; a 0.5 drop table t1; create table t1 (a decimal(4,2)) partition by key (a); insert into t1 values (2.1); select * from t1 where a = 2.1; a 2.10 drop table t1; create table t1 (a date) partition by key (a); insert into t1 values ('2001-01-01'); select * from t1 where a = '2001-01-01'; a 2001-01-01 drop table t1; create table t1 (a datetime) partition by key (a); insert into t1 values ('2001-01-01 01:02:03'); select * from t1 where a = '2001-01-01 01:02:03'; a 2001-01-01 01:02:03 drop table t1; create table t1 (a timestamp null) partition by key (a); insert into t1 values ('2001-01-01 01:02:03'); select * from t1 where a = '2001-01-01 01:02:03'; a 2001-01-01 01:02:03 drop table t1; create table t1 (a time) partition by key (a); insert into t1 values ('01:02:03'); select * from t1 where a = '01:02:03'; a 01:02:03 drop table t1; create table t1 (a year) partition by key (a); insert into t1 values ('2001'); select * from t1 where a = '2001'; a 2001 drop table t1; create table t1 (a varchar(10) character set utf8) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a varchar(300) character set utf8) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a varchar(10) character set latin1) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a varchar(300) character set latin1) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a char(10) character set utf8) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a char(10) character set latin1) partition by key (a); insert into t1 values ('abc'); select * from t1 where a = 'abc'; a abc drop table t1; create table t1 (a enum('y','n')) partition by key (a); insert into t1 values ('y'); select * from t1 where a = 'y'; a y drop table t1; create table t1 (a set('y','n')) partition by key (a); insert into t1 values ('y'); select * from t1 where a = 'y'; a y drop table t1; create table t1 (a varchar(3068)) partition by key (a); insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); select * from t1 where a = 'aaaa'; a aaaa select * from t1 where a like 'aaa%'; a aaaa select * from t1 where a = 'bbbb'; a bbbb drop table t1; create table t1 (a varchar(3069)) partition by key (a); insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); select * from t1 where a = 'aaaa'; a aaaa select * from t1 where a like 'aaa%'; a aaaa select * from t1 where a = 'bbbb'; a bbbb drop table t1; create table t1 (a varchar(3070) not null) partition by key (a); insert into t1 values ('bbbb'); insert into t1 values ('aaaa'); select * from t1 where a = 'aaaa'; a aaaa select * from t1 where a like 'aaa%'; a aaaa select * from t1 where a = 'bbbb'; a bbbb drop table t1; create table t1 (a varchar(3070)) partition by key (a); ERROR HY000: The total length of the partitioning fields is too large create table t1 (a varchar(65533)) partition by key (a); ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs create table t1 (a varchar(65534) not null) partition by key (a); ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs create table t1 (a varchar(65535)) partition by key (a); ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs create table t1 (a bit(27), primary key (a)) engine=myisam partition by hash (a) (partition p0, partition p1, partition p2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bit(27) NOT NULL DEFAULT b'0', PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION p0 ENGINE = MyISAM, PARTITION p1 ENGINE = MyISAM, PARTITION p2 ENGINE = MyISAM) */ insert into t1 values (1),(4),(7),(10),(13),(16),(19),(22),(25),(28),(31),(34); select hex(a) from t1 where a = 7; hex(a) 7 drop table t1; # # Bug#28928: UNIX_TIMESTAMP() should be considered unary monotonic # by partition pruning SET @old_time_zone= @@session.time_zone; SET @@session.time_zone = 'UTC'; # 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); # Test 'odd' values INSERT INTO t1 VALUES (NULL, 'UTC'); INSERT INTO t1 VALUES ('0000-00-00 00:00:00', 'UTC'); # Test invalid values INSERT INTO t1 VALUES ('1901-01-01 00:00:00', 'UTCI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('1969-12-31 23:59:59', 'UTCI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('2038-01-19 03:14:08', 'UTCI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('1970-01-01 00:00:00', 'UTCI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 # 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'); # 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'); # 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'; # Test 'odd' values INSERT INTO t1 VALUES (NULL, 'Moscow'); INSERT INTO t1 VALUES ('0000-00-00 00:00:00', 'Moscow'); # Test invalid values INSERT INTO t1 VALUES ('0000-00-00 03:00:00', 'MoscowI'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES ('1901-01-01 00:00:00', 'MoscowI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('1969-12-31 23:59:59', 'MoscowI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('1970-01-01 02:29:29', 'MoscowI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('2038-01-19 06:14:08', 'MoscowI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('1970-01-01 03:00:00', 'MoscowI'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 # values truncated to 03:00:00 due to daylight saving shift INSERT INTO t1 VALUES ('2011-03-27 02:00:00', 'MoscowI'); Warnings: Warning 1299 Invalid TIMESTAMP value in column 'a' at row 1 INSERT INTO t1 VALUES ('2011-03-27 02:00:01', 'MoscowI'); Warnings: Warning 1299 Invalid TIMESTAMP value in column 'a' at row 1 INSERT INTO t1 VALUES ('2011-03-27 02:59:59', 'MoscowI'); Warnings: Warning 1299 Invalid TIMESTAMP value in column 'a' at row 1 # 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'); # 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'); # 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'); # 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'; PARTITION_NAME TABLE_ROWS p0 2 p-2000 16 p-2011-MSK 2 p-2011-MSD-1 9 p-2011-MSD-2 6 p-2012-MSK-1 3 p-2012-MSK-2 4 pEnd 2 pMax 2 SELECT * FROM t1 ORDER BY a, tz; a tz NULL Moscow NULL UTC 0000-00-00 00:00:00 Moscow 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 UTC 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 1970-01-01 00:00:01 Moscow 1970-01-01 00:00:01 UTC 1974-02-05 18:28:16 Moscow 1974-02-05 21:28:16 UTC 2011-03-26 22:59:59 Moscow 2011-03-26 22:59:59 UTC 2011-03-26 23:00:00 Moscow 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 UTC 2011-03-26 23:00:01 Moscow 2011-03-26 23:00:01 UTC 2011-10-29 21:59:59 Moscow 2011-10-29 21:59:59 UTC 2011-10-29 22:00:00 MoscowD 2011-10-29 22:00:00 UTC 2011-10-29 22:00:01 MoscowD 2011-10-29 22:00:01 UTC 2011-10-29 22:59:59 MoscowD 2011-10-29 22:59:59 UTC 2011-10-29 23:00:00 UTC 2011-10-29 23:00:01 UTC 2011-10-29 23:59:59 UTC 2011-10-30 00:00:00 Moscow 2011-10-30 00:00:00 UTC 2011-10-30 00:00:01 Moscow 2011-10-30 00:00:01 UTC 2038-01-19 03:14:06 Moscow 2038-01-19 03:14:06 UTC 2038-01-19 03:14:07 Moscow 2038-01-19 03:14:07 UTC SELECT * FROM t2 ORDER BY a, tz; a tz NULL Moscow NULL UTC 0000-00-00 00:00:00 Moscow 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 UTC 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 1970-01-01 00:00:01 Moscow 1970-01-01 00:00:01 UTC 1974-02-05 18:28:16 Moscow 1974-02-05 21:28:16 UTC 2011-03-26 22:59:59 Moscow 2011-03-26 22:59:59 UTC 2011-03-26 23:00:00 Moscow 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 UTC 2011-03-26 23:00:01 Moscow 2011-03-26 23:00:01 UTC 2011-10-29 21:59:59 Moscow 2011-10-29 21:59:59 UTC 2011-10-29 22:00:00 MoscowD 2011-10-29 22:00:00 UTC 2011-10-29 22:00:01 MoscowD 2011-10-29 22:00:01 UTC 2011-10-29 22:59:59 MoscowD 2011-10-29 22:59:59 UTC 2011-10-29 23:00:00 UTC 2011-10-29 23:00:01 UTC 2011-10-29 23:59:59 UTC 2011-10-30 00:00:00 Moscow 2011-10-30 00:00:00 UTC 2011-10-30 00:00:01 Moscow 2011-10-30 00:00:01 UTC 2038-01-19 03:14:06 Moscow 2038-01-19 03:14:06 UTC 2038-01-19 03:14:07 Moscow 2038-01-19 03:14:07 UTC SELECT * FROM t2 WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 23:00:00' ORDER BY a, tz; a tz 2011-03-26 22:59:59 Moscow 2011-03-26 22:59:59 UTC 2011-03-26 23:00:00 Moscow 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 22:59:59' ORDER BY a, tz; a tz 2011-03-26 22:59:59 Moscow 2011-03-26 22:59:59 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSK ALL NULL NULL NULL NULL 2 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-03-26 22:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; a tz 2011-03-26 22:59:59 Moscow 2011-03-26 22:59:59 UTC 2011-03-26 23:00:00 Moscow 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 UTC 2011-03-26 23:00:01 Moscow 2011-03-26 23:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-03-26 23:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; a tz 2011-03-26 23:00:00 Moscow 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 UTC 2011-03-26 23:00:01 Moscow 2011-03-26 23:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 23:00:00' ORDER BY a, tz; a tz 2011-10-29 21:59:59 Moscow 2011-10-29 21:59:59 UTC 2011-10-29 22:00:00 MoscowD 2011-10-29 22:00:00 UTC 2011-10-29 22:00:01 MoscowD 2011-10-29 22:00:01 UTC 2011-10-29 22:59:59 MoscowD 2011-10-29 22:59:59 UTC 2011-10-29 23:00:00 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1 ALL NULL NULL NULL NULL 18 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 22:59:59' ORDER BY a, tz; a tz 2011-10-29 21:59:59 Moscow 2011-10-29 21:59:59 UTC 2011-10-29 22:00:00 MoscowD 2011-10-29 22:00:00 UTC 2011-10-29 22:00:01 MoscowD 2011-10-29 22:00:01 UTC 2011-10-29 22:59:59 MoscowD 2011-10-29 22:59:59 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2 ALL NULL NULL NULL NULL 15 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-29 22:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; a tz 2011-10-29 22:59:59 MoscowD 2011-10-29 22:59:59 UTC 2011-10-29 23:00:00 UTC 2011-10-29 23:00:01 UTC 2011-10-29 23:59:59 UTC 2011-10-30 00:00:00 Moscow 2011-10-30 00:00:00 UTC 2011-10-30 00:00:01 Moscow 2011-10-30 00:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 13 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-29 23:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; a tz 2011-10-29 23:00:00 UTC 2011-10-29 23:00:01 UTC 2011-10-29 23:59:59 UTC 2011-10-30 00:00:00 Moscow 2011-10-30 00:00:00 UTC 2011-10-30 00:00:01 Moscow 2011-10-30 00:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 7 Using where; Using filesort # Test end range changes DELETE FROM t2 WHERE a = 0; INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 SELECT COUNT(*) FROM t2; COUNT(*) 35 SELECT COUNT(*) FROM t2 WHERE a = 0; COUNT(*) 1 SELECT * FROM t2 ORDER BY a, tz LIMIT 3; a tz NULL Moscow NULL UTC 0000-00-00 00:00:00 UTC SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; a tz 2038-01-19 03:14:07 Moscow 2038-01-19 03:14:07 UTC 2038-01-19 03:14:06 Moscow UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a); Warnings: Warning 1264 Out of range value for column 'a' at row 34 Warning 1264 Out of range value for column 'a' at row 35 SELECT MIN(a), MAX(a) FROM t2; MIN(a) MAX(a) 0000-00-00 00:00:00 2038-01-19 03:14:07 SELECT COUNT(*) FROM t2; COUNT(*) 35 SELECT COUNT(*) FROM t2 WHERE a = 0; COUNT(*) 2 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't2'; PARTITION_NAME TABLE_ROWS p0 3 p-2000 6 p-2011-MSK 0 p-2011-MSD-1 9 p-2011-MSD-2 6 p-2012-MSK-1 4 p-2012-MSK-2 5 pEnd 0 pMax 2 SELECT * FROM t2 ORDER BY a, tz; a tz NULL Moscow NULL UTC NULL UTC 0000-00-00 00:00:00 Moscow 0000-00-00 00:00:00 UTC 1970-01-01 00:00:02 Moscow 1970-01-01 00:00:02 UTC 1974-02-05 18:28:17 Moscow 1974-02-05 21:28:17 UTC 2011-03-26 23:00:00 Moscow 2011-03-26 23:00:00 UTC 2011-03-26 23:00:01 Moscow 2011-03-26 23:00:01 MoscowI 2011-03-26 23:00:01 MoscowI 2011-03-26 23:00:01 MoscowI 2011-03-26 23:00:01 UTC 2011-03-26 23:00:02 Moscow 2011-03-26 23:00:02 UTC 2011-10-29 22:00:00 Moscow 2011-10-29 22:00:00 UTC 2011-10-29 22:00:01 MoscowD 2011-10-29 22:00:01 UTC 2011-10-29 22:00:02 MoscowD 2011-10-29 22:00:02 UTC 2011-10-29 23:00:00 MoscowD 2011-10-29 23:00:00 UTC 2011-10-29 23:00:01 UTC 2011-10-29 23:00:02 UTC 2011-10-30 00:00:00 UTC 2011-10-30 00:00:01 Moscow 2011-10-30 00:00:01 UTC 2011-10-30 00:00:02 Moscow 2011-10-30 00:00:02 UTC 2038-01-19 03:14:07 Moscow 2038-01-19 03:14:07 UTC # Test start range changes INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 SELECT COUNT(*) FROM t2; COUNT(*) 36 SELECT COUNT(*) FROM t2 WHERE a = 0; COUNT(*) 3 SELECT * FROM t2 ORDER BY a, tz LIMIT 3; a tz NULL Moscow NULL UTC NULL UTC SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; a tz 2038-01-19 03:14:07 Moscow 2038-01-19 03:14:07 UTC 2011-10-30 00:00:02 Moscow UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a); SELECT MIN(a), MAX(a) FROM t2; MIN(a) MAX(a) 1970-01-01 00:00:01 2038-01-19 03:14:06 SELECT COUNT(*) FROM t2; COUNT(*) 36 SELECT COUNT(*) FROM t2 WHERE a = 0; COUNT(*) 0 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't2'; PARTITION_NAME TABLE_ROWS p0 6 p-2000 4 p-2011-MSK 2 p-2011-MSD-1 9 p-2011-MSD-2 6 p-2012-MSK-1 3 p-2012-MSK-2 4 pEnd 2 pMax 0 SELECT * FROM t2 ORDER BY a, tz; a tz NULL Moscow NULL Moscow NULL UTC NULL UTC NULL UTC NULL UTC 1970-01-01 00:00:01 Moscow 1970-01-01 00:00:01 UTC 1974-02-05 18:28:16 Moscow 1974-02-05 21:28:16 UTC 2011-03-26 22:59:59 Moscow 2011-03-26 22:59:59 UTC 2011-03-26 23:00:00 Moscow 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 MoscowI 2011-03-26 23:00:00 UTC 2011-03-26 23:00:01 Moscow 2011-03-26 23:00:01 UTC 2011-10-29 21:59:59 Moscow 2011-10-29 21:59:59 UTC 2011-10-29 22:00:00 MoscowD 2011-10-29 22:00:00 UTC 2011-10-29 22:00:01 MoscowD 2011-10-29 22:00:01 UTC 2011-10-29 22:59:59 MoscowD 2011-10-29 22:59:59 UTC 2011-10-29 23:00:00 UTC 2011-10-29 23:00:01 UTC 2011-10-29 23:59:59 UTC 2011-10-30 00:00:00 Moscow 2011-10-30 00:00:00 UTC 2011-10-30 00:00:01 Moscow 2011-10-30 00:00:01 UTC 2038-01-19 03:14:06 Moscow 2038-01-19 03:14:06 UTC SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` timestamp NULL DEFAULT NULL, `tz` varchar(16) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(a)) (PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM, PARTITION `p-2000` VALUES LESS THAN (946684800) ENGINE = MyISAM, PARTITION `p-2011-MSK` VALUES LESS THAN (1301180400) ENGINE = MyISAM, PARTITION `p-2011-MSD-1` VALUES LESS THAN (1319925600) ENGINE = MyISAM, PARTITION `p-2011-MSD-2` VALUES LESS THAN (1319929200) ENGINE = MyISAM, PARTITION `p-2012-MSK-1` VALUES LESS THAN (1319932800) ENGINE = MyISAM, PARTITION `p-2012-MSK-2` VALUES LESS THAN (1332630000) ENGINE = MyISAM, PARTITION pEnd VALUES LESS THAN (2147483647) ENGINE = MyISAM, PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ 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'; PARTITION_NAME TABLE_ROWS p0 2 p-2000 16 p-2011-MSK 2 p-2011-MSD-1 9 p-2011-MSD-2 6 p-2012-MSK-1 3 p-2012-MSK-2 4 pEnd 2 pMax 2 SELECT * FROM t1 ORDER BY a, tz; a tz NULL Moscow NULL UTC 0000-00-00 00:00:00 Moscow 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 UTC 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 1970-01-01 03:00:01 Moscow 1970-01-01 03:00:01 UTC 1974-02-05 21:28:16 Moscow 1974-02-06 00:28:16 UTC 2011-03-27 01:59:59 Moscow 2011-03-27 01:59:59 UTC 2011-03-27 03:00:00 Moscow 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 UTC 2011-03-27 03:00:01 Moscow 2011-03-27 03:00:01 UTC 2011-10-30 01:59:59 Moscow 2011-10-30 01:59:59 UTC 2011-10-30 02:00:00 MoscowD 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 MoscowD 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 MoscowD 2011-10-30 02:59:59 UTC 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 UTC 2011-10-30 03:00:00 Moscow 2011-10-30 03:00:00 UTC 2011-10-30 03:00:01 Moscow 2011-10-30 03:00:01 UTC 2038-01-19 06:14:06 Moscow 2038-01-19 06:14:06 UTC 2038-01-19 06:14:07 Moscow 2038-01-19 06:14:07 UTC SELECT * FROM t2 ORDER BY a, tz; a tz NULL Moscow NULL UTC 0000-00-00 00:00:00 Moscow 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 MoscowI 0000-00-00 00:00:00 UTC 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 0000-00-00 00:00:00 UTCI 1970-01-01 03:00:01 Moscow 1970-01-01 03:00:01 UTC 1974-02-05 21:28:16 Moscow 1974-02-06 00:28:16 UTC 2011-03-27 01:59:59 Moscow 2011-03-27 01:59:59 UTC 2011-03-27 03:00:00 Moscow 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 UTC 2011-03-27 03:00:01 Moscow 2011-03-27 03:00:01 UTC 2011-10-30 01:59:59 Moscow 2011-10-30 01:59:59 UTC 2011-10-30 02:00:00 MoscowD 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 MoscowD 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 MoscowD 2011-10-30 02:59:59 UTC 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 UTC 2011-10-30 03:00:00 Moscow 2011-10-30 03:00:00 UTC 2011-10-30 03:00:01 Moscow 2011-10-30 03:00:01 UTC 2038-01-19 06:14:06 Moscow 2038-01-19 06:14:06 UTC 2038-01-19 06:14:07 Moscow 2038-01-19 06:14:07 UTC # 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; a tz 2011-03-27 01:59:59 Moscow 2011-03-27 01:59:59 UTC 2011-03-27 03:00:00 Moscow 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 01:59:59' ORDER BY a, tz; a tz 2011-03-27 01:59:59 Moscow 2011-03-27 01:59:59 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSK ALL NULL NULL NULL NULL 2 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-03-26 01:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; a tz 2011-03-27 01:59:59 Moscow 2011-03-27 01:59:59 UTC 2011-03-27 03:00:00 Moscow 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 UTC 2011-03-27 03:00:01 Moscow 2011-03-27 03:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-03-26 03:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; a tz 2011-03-27 01:59:59 Moscow 2011-03-27 01:59:59 UTC 2011-03-27 03:00:00 Moscow 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 UTC 2011-03-27 03:00:01 Moscow 2011-03-27 03:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort # 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; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:59:59' ORDER BY a, tz; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 03:00:00' ORDER BY a, tz; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 01:59:59' ORDER BY a, tz; 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-29 02:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; a tz 2011-10-30 01:59:59 Moscow 2011-10-30 01:59:59 UTC 2011-10-30 02:00:00 MoscowD 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 MoscowD 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 MoscowD 2011-10-30 02:59:59 UTC 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 UTC 2011-10-30 03:00:00 Moscow 2011-10-30 03:00:00 UTC 2011-10-30 03:00:01 Moscow 2011-10-30 03:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 22 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-29 02:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; a tz 2011-10-30 01:59:59 Moscow 2011-10-30 01:59:59 UTC 2011-10-30 02:00:00 MoscowD 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 MoscowD 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 MoscowD 2011-10-30 02:59:59 UTC 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 UTC 2011-10-30 03:00:00 Moscow 2011-10-30 03:00:00 UTC 2011-10-30 03:00:01 Moscow 2011-10-30 03:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 22 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-29 03:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; a tz 2011-10-30 01:59:59 Moscow 2011-10-30 01:59:59 UTC 2011-10-30 02:00:00 MoscowD 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 MoscowD 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 MoscowD 2011-10-30 02:59:59 UTC 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 UTC 2011-10-30 03:00:00 Moscow 2011-10-30 03:00:00 UTC 2011-10-30 03:00:01 Moscow 2011-10-30 03:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 22 Using where; Using filesort SELECT * FROM t2 WHERE a BETWEEN '2011-10-29 01:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; a tz 2011-10-30 01:59:59 Moscow 2011-10-30 01:59:59 UTC 2011-10-30 02:00:00 MoscowD 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 MoscowD 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 MoscowD 2011-10-30 02:59:59 UTC 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 UTC 2011-10-30 03:00:00 Moscow 2011-10-30 03:00:00 UTC 2011-10-30 03:00:01 Moscow 2011-10-30 03:00:01 UTC 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; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 22 Using where; Using filesort # Test end range changes DELETE FROM t2 WHERE a = 0; INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 SELECT COUNT(*) FROM t2; COUNT(*) 35 SELECT COUNT(*) FROM t2 WHERE a = 0; COUNT(*) 1 SELECT * FROM t2 ORDER BY a, tz LIMIT 3; a tz NULL Moscow NULL UTC 0000-00-00 00:00:00 Moscow SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; a tz 2038-01-19 06:14:07 Moscow 2038-01-19 06:14:07 UTC 2038-01-19 06:14:06 Moscow UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a); Warnings: Warning 1299 Invalid TIMESTAMP value in column 'a' at row 8 Warning 1299 Invalid TIMESTAMP value in column 'a' at row 9 Warning 1264 Out of range value for column 'a' at row 34 Warning 1264 Out of range value for column 'a' at row 35 SELECT MIN(a), MAX(a) FROM t2; MIN(a) MAX(a) 0000-00-00 00:00:00 2038-01-19 06:14:07 SELECT COUNT(*) FROM t2; COUNT(*) 35 SELECT COUNT(*) FROM t2 WHERE a = 0; COUNT(*) 2 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't2'; PARTITION_NAME TABLE_ROWS p0 3 p-2000 6 p-2011-MSK 0 p-2011-MSD-1 9 p-2011-MSD-2 8 p-2012-MSK-1 0 p-2012-MSK-2 7 pEnd 0 pMax 2 SELECT * FROM t2 ORDER BY a, tz; a tz NULL Moscow NULL Moscow NULL UTC 0000-00-00 00:00:00 Moscow 0000-00-00 00:00:00 UTC 1970-01-01 03:00:02 Moscow 1970-01-01 03:00:02 UTC 1974-02-05 21:28:17 Moscow 1974-02-06 00:28:17 UTC 2011-03-27 03:00:00 Moscow 2011-03-27 03:00:00 UTC 2011-03-27 03:00:01 Moscow 2011-03-27 03:00:01 MoscowI 2011-03-27 03:00:01 MoscowI 2011-03-27 03:00:01 MoscowI 2011-03-27 03:00:01 UTC 2011-03-27 03:00:02 Moscow 2011-03-27 03:00:02 UTC 2011-10-30 02:00:00 Moscow 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 MoscowD 2011-10-30 02:00:01 UTC 2011-10-30 02:00:01 UTC 2011-10-30 02:00:02 MoscowD 2011-10-30 02:00:02 UTC 2011-10-30 02:00:02 UTC 2011-10-30 03:00:00 MoscowD 2011-10-30 03:00:00 UTC 2011-10-30 03:00:00 UTC 2011-10-30 03:00:01 Moscow 2011-10-30 03:00:01 UTC 2011-10-30 03:00:02 Moscow 2011-10-30 03:00:02 UTC 2038-01-19 06:14:07 Moscow 2038-01-19 06:14:07 UTC # Test start range changes INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 SELECT COUNT(*) FROM t2; COUNT(*) 36 SELECT COUNT(*) FROM t2 WHERE a = 0; COUNT(*) 3 SELECT * FROM t2 ORDER BY a, tz LIMIT 3; a tz NULL Moscow NULL Moscow NULL UTC SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; a tz 2038-01-19 06:14:07 Moscow 2038-01-19 06:14:07 UTC 2011-10-30 03:00:02 Moscow UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a); Warnings: Warning 1299 Invalid TIMESTAMP value in column 'a' at row 18 Warning 1299 Invalid TIMESTAMP value in column 'a' at row 19 SELECT MIN(a), MAX(a) FROM t2; MIN(a) MAX(a) 1970-01-01 03:00:01 2038-01-19 06:14:06 SELECT COUNT(*) FROM t2; COUNT(*) 36 SELECT COUNT(*) FROM t2 WHERE a = 0; COUNT(*) 0 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't2'; PARTITION_NAME TABLE_ROWS p0 6 p-2000 4 p-2011-MSK 0 p-2011-MSD-1 11 p-2011-MSD-2 9 p-2012-MSK-1 0 p-2012-MSK-2 4 pEnd 2 pMax 0 SELECT * FROM t2 ORDER BY a, tz; a tz NULL Moscow NULL Moscow NULL Moscow NULL Moscow NULL UTC NULL UTC 1970-01-01 03:00:01 Moscow 1970-01-01 03:00:01 UTC 1974-02-05 21:28:16 Moscow 1974-02-06 00:28:16 UTC 2011-03-27 03:00:00 Moscow 2011-03-27 03:00:00 Moscow 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 MoscowI 2011-03-27 03:00:00 UTC 2011-03-27 03:00:00 UTC 2011-03-27 03:00:01 Moscow 2011-03-27 03:00:01 UTC 2011-10-30 01:59:59 Moscow 2011-10-30 01:59:59 UTC 2011-10-30 02:00:00 MoscowD 2011-10-30 02:00:00 UTC 2011-10-30 02:00:00 UTC 2011-10-30 02:00:01 MoscowD 2011-10-30 02:00:01 UTC 2011-10-30 02:00:01 UTC 2011-10-30 02:59:59 MoscowD 2011-10-30 02:59:59 UTC 2011-10-30 02:59:59 UTC 2011-10-30 03:00:00 Moscow 2011-10-30 03:00:00 UTC 2011-10-30 03:00:01 Moscow 2011-10-30 03:00:01 UTC 2038-01-19 06:14:06 Moscow 2038-01-19 06:14:06 UTC SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` timestamp NULL DEFAULT NULL, `tz` varchar(16) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(a)) (PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM, PARTITION `p-2000` VALUES LESS THAN (946684800) ENGINE = MyISAM, PARTITION `p-2011-MSK` VALUES LESS THAN (1301180400) ENGINE = MyISAM, PARTITION `p-2011-MSD-1` VALUES LESS THAN (1319925600) ENGINE = MyISAM, PARTITION `p-2011-MSD-2` VALUES LESS THAN (1319929200) ENGINE = MyISAM, PARTITION `p-2012-MSK-1` VALUES LESS THAN (1319932800) ENGINE = MyISAM, PARTITION `p-2012-MSK-2` VALUES LESS THAN (1332630000) ENGINE = MyISAM, PARTITION pEnd VALUES LESS THAN (2147483647) ENGINE = MyISAM, PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ TRUNCATE TABLE t2; DROP TABLE t1, t2; SET @@session.time_zone= @old_time_zone;
Back to File Manager