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. This includes storage overhead, check the manual. 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. This includes storage overhead, check the manual. 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. This includes storage overhead, check the manual. 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);
Warnings:
Warning	1287	The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bit(27) NOT NULL,
  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) */
Warnings:
Warning	1287	The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
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);
Warnings:
Warning	1287	The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead.
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
# 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	1264	Out of range value 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;
Warnings:
Warning	1287	The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead.
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSK,p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	11	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-03-01 00:00:00' and '2011-03-26 23:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSK	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-03-01 00:00:00' and '2011-03-26 22:59:59') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSK,p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	11	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-03-26 22:59:59' and '2011-03-28 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	9	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-03-26 23:00:00' and '2011-03-28 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1	ALL	NULL	NULL	NULL	NULL	18	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-01 00:00:00' and '2011-10-29 23:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSD-1,p-2011-MSD-2	ALL	NULL	NULL	NULL	NULL	15	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-01 00:00:00' and '2011-10-29 22:59:59') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2	ALL	NULL	NULL	NULL	NULL	13	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-29 22:59:59' and '2011-10-31 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2012-MSK-1,p-2012-MSK-2	ALL	NULL	NULL	NULL	NULL	7	14.29	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-29 23:00:00' and '2011-10-31 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
# 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) */
Warnings:
Warning	1287	The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead.
TRUNCATE TABLE t2;
Warnings:
Warning	1287	The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead.
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSK,p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	11	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-03-01 00:00:00' and '2011-03-27 03:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSK	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-03-01 00:00:00' and '2011-03-27 01:59:59') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSK,p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	11	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-03-26 01:59:59' and '2011-03-28 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSK,p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	11	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-03-26 03:00:00' and '2011-03-28 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
# 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	filtered	Extra
1	SIMPLE	t2	p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	9	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-01 00:00:00' and '2011-10-29 02:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	9	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-01 00:00:00' and '2011-10-29 02:59:59') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	9	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-01 00:00:00' and '2011-10-29 03:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	Extra
1	SIMPLE	t2	p-2011-MSD-1	ALL	NULL	NULL	NULL	NULL	9	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-01 00:00:00' and '2011-10-29 01:59:59') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	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	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-29 02:00:00' and '2011-10-31 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	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	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-29 02:59:59' and '2011-10-31 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	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	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-29 03:00:00' and '2011-10-31 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
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	filtered	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	11.11	Using where; Using filesort
Warnings:
Warning	1681	'PARTITIONS' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`tz` AS `tz` from `test`.`t2` where (`test`.`t2`.`a` between '2011-10-29 01:59:59' and '2011-10-31 00:00:00') order by `test`.`t2`.`a`,`test`.`t2`.`tz`
# 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) */
Warnings:
Warning	1287	The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead.
TRUNCATE TABLE t2;
Warnings:
Warning	1287	The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead.
SET sql_mode = default;
DROP TABLE t1, t2;
SET @@session.time_zone= @old_time_zone;