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;
set sql_mode='';
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(65532) not null) 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 HY000: A BLOB field is not allowed in partition function
create table t1 (a varchar(65534) not null) partition by key (a);
ERROR HY000: A BLOB field is not allowed in partition function
create table t1 (a varchar(65535)) partition by key (a);
ERROR HY000: A BLOB field is not allowed in partition function
set sql_mode=default;
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,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 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(20000101)),
PARTITION `p-2011-MSK` VALUES LESS THAN (UNIX_TIMESTAMP(20110326230000)),
PARTITION `p-2011-MSD-1` VALUES LESS THAN (UNIX_TIMESTAMP(20111029220000)),
PARTITION `p-2011-MSD-2` VALUES LESS THAN (UNIX_TIMESTAMP(20111029230000)),
PARTITION `p-2012-MSK-1` VALUES LESS THAN (UNIX_TIMESTAMP(20111030000000)),
PARTITION `p-2012-MSK-2` VALUES LESS THAN (UNIX_TIMESTAMP(20120324230000)),
PARTITION `pEnd` VALUES LESS THAN (UNIX_TIMESTAMP(20380119031407)),
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 IGNORE INTO t1 VALUES ('1901-01-01 00:00:00', 'UTCI');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT IGNORE INTO t1 VALUES ('1969-12-31 23:59:59', 'UTCI');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT IGNORE INTO t1 VALUES ('2038-01-19 03:14:08', 'UTCI');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT IGNORE 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 IGNORE INTO t1 VALUES ('0000-00-00 03:00:00', 'MoscowI');
Warnings:
Warning	1265	Data truncated for column 'a' at row 1
INSERT IGNORE INTO t1 VALUES ('1901-01-01 00:00:00', 'MoscowI');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT IGNORE INTO t1 VALUES ('1969-12-31 23:59:59', 'MoscowI');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT IGNORE INTO t1 VALUES ('1970-01-01 02:29:29', 'MoscowI');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT IGNORE INTO t1 VALUES ('2038-01-19 06:14:08', 'MoscowI');
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
INSERT IGNORE 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 IGNORE INTO t1 VALUES ('2011-03-27 02:00:00', 'MoscowI');
Warnings:
Warning	1299	Invalid TIMESTAMP value in column 'a' at row 1
INSERT IGNORE INTO t1 VALUES ('2011-03-27 02:00:01', 'MoscowI');
Warnings:
Warning	1299	Invalid TIMESTAMP value in column 'a' at row 1
INSERT IGNORE 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 IGNORE 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 IGNORE t2 SET a = TIMESTAMPADD(SECOND, 1, a);
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
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 IGNORE 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 IGNORE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
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
 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 IGNORE 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 IGNORE t2 SET a = TIMESTAMPADD(SECOND, 1, a);
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
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 IGNORE 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 IGNORE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
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
 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;