bzr push into mysql-trunk branch (mattias.jonsson:3194 to 3195)

[Mattias Jonsson]

 3195 Mattias Jonsson	2011-06-13 [merge]
      merge

    modified:
      mysql-test/r/partition.result
      mysql-test/r/partition_datatype.result
      mysql-test/t/partition.test
      mysql-test/t/partition_datatype.test
      sql/item_timefunc.cc
      sql/item_timefunc.h
 3194 Mattias Jonsson	2011-06-13 [merge]
      merge

    modified:
      mysql-test/r/partition.result
      mysql-test/t/partition.test
      sql/ha_partition.cc
=== modified file 'mysql-test/r/partition.result'
--- a/mysql-test/r/partition.result	2011-06-13 09:33:42 +0000
+++ b/mysql-test/r/partition.result	2011-06-13 10:55:58 +0000
@@ -231,6 +231,15 @@ a	b
 2007-07-30 17:35:48	p1
 2009-07-14 17:35:55	pmax
 2009-09-21 17:31:42	pmax
+SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
+a	b
[phone]:35:48	p1
+EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1	system	PRIMARY	NULL	NULL	NULL	1	
+EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1	system	PRIMARY	NULL	NULL	NULL	1	
 ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (
 PARTITION p3 VALUES LESS THAN (1247688000),
 PARTITION pmax VALUES LESS THAN MAXVALUE);
@@ -239,6 +248,15 @@ a	b
 2007-07-30 17:35:48	p1
 2009-07-14 17:35:55	pmax
 2009-09-21 17:31:42	pmax
+SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
+a	b
[phone]:35:48	p1
+EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1	system	PRIMARY	NULL	NULL	NULL	1	
+EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48';
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1	system	PRIMARY	NULL	NULL	NULL	1	
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (

=== modified file 'mysql-test/r/partition_datatype.result'
--- a/mysql-test/r/partition_datatype.result	2009-10-28 00:11:17 +0000
+++ b/mysql-test/r/partition_datatype.result	2011-06-13 09:57:47 +0000
@@ -338,3 +338,1003 @@ 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
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:28:16	Moscow
[phone]:28:16	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:14:06	Moscow
[phone]:14:06	UTC
[phone]:14:07	Moscow
[phone]: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
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:28:16	Moscow
[phone]:28:16	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:14:06	Moscow
[phone]:14:06	UTC
[phone]:14:07	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]: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
[phone]:59:59	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]: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
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:14:07	Moscow
[phone]:14:07	UTC
[phone]: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
[phone]:00:02	Moscow
[phone]:00:02	UTC
[phone]:28:17	Moscow
[phone]:28:17	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	MoscowI
[phone]:00:01	MoscowI
[phone]:00:01	MoscowI
[phone]:00:01	UTC
[phone]:00:02	Moscow
[phone]:00:02	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:00:02	MoscowD
[phone]:00:02	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:00:02	UTC
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:00:02	Moscow
[phone]:00:02	UTC
[phone]:14:07	Moscow
[phone]: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
[phone]:14:07	Moscow
[phone]:14:07	UTC
[phone]:00:02	Moscow
+UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
+SELECT MIN(a), MAX(a) FROM t2;
+MIN(a)	MAX(a)
[phone]: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
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:28:16	Moscow
[phone]:28:16	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:14:06	Moscow
[phone]: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
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:28:16	Moscow
[phone]:28:16	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:14:06	Moscow
[phone]:14:06	UTC
[phone]:14:07	Moscow
[phone]: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
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:28:16	Moscow
[phone]:28:16	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:14:06	Moscow
[phone]:14:06	UTC
[phone]:14:07	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]: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
[phone]:59:59	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:00:00	UTC
[phone]:00:01	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]: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
[phone]:14:07	Moscow
[phone]:14:07	UTC
[phone]: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
[phone]:00:02	Moscow
[phone]:00:02	UTC
[phone]:28:17	Moscow
[phone]:28:17	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	MoscowI
[phone]:00:01	MoscowI
[phone]:00:01	MoscowI
[phone]:00:01	UTC
[phone]:00:02	Moscow
[phone]:00:02	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:00:01	UTC
[phone]:00:02	MoscowD
[phone]:00:02	UTC
[phone]:00:02	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:00:02	Moscow
[phone]:00:02	UTC
[phone]:14:07	Moscow
[phone]: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
[phone]:14:07	Moscow
[phone]:14:07	UTC
[phone]: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)
[phone]: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
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:28:16	Moscow
[phone]:28:16	UTC
[phone]:00:00	Moscow
[phone]:00:00	Moscow
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	MoscowI
[phone]:00:00	UTC
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:59:59	Moscow
[phone]:59:59	UTC
[phone]:00:00	MoscowD
[phone]:00:00	UTC
[phone]:00:00	UTC
[phone]:00:01	MoscowD
[phone]:00:01	UTC
[phone]:00:01	UTC
[phone]:59:59	MoscowD
[phone]:59:59	UTC
[phone]:59:59	UTC
[phone]:00:00	Moscow
[phone]:00:00	UTC
[phone]:00:01	Moscow
[phone]:00:01	UTC
[phone]:14:06	Moscow
[phone]: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;

=== modified file 'mysql-test/t/partition.test'
--- a/mysql-test/t/partition.test	2011-06-13 09:33:42 +0000
+++ b/mysql-test/t/partition.test	2011-06-13 10:55:58 +0000
@@ -236,10 +236,16 @@ INSERT INTO t1 VALUES ('2009-07-14 17:35
 INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax');
 
 SELECT * FROM t1;
+SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48';
 ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (
  PARTITION p3 VALUES LESS THAN (1247688000),
  PARTITION pmax VALUES LESS THAN MAXVALUE);
 SELECT * FROM t1;
+SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
+EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48';
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 

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

=== modified file 'sql/item_timefunc.cc'
--- a/sql/item_timefunc.cc	2011-05-26 15:20:09 +0000
+++ b/sql/item_timefunc.cc	2011-06-13 10:55:58 +0000
@@ -1385,6 +1385,26 @@ longlong Item_func_unix_timestamp::val_i
   return (longlong) TIME_to_timestamp(current_thd, &ltime, &not_used);
 }
 
+enum_monotonicity_info Item_func_unix_timestamp::get_monotonicity_info() const
+{
+  if (args[0]->type() == Item::FIELD_ITEM &&
+      (args[0]->field_type() == MYSQL_TYPE_TIMESTAMP))
+    return MONOTONIC_INCREASING;
+  return NON_MONOTONIC;
+}
+
+
+longlong Item_func_unix_timestamp::val_int_endpoint(bool left_endp, bool *incl_endp)
+{
+  DBUG_ASSERT(fixed == 1);
+  DBUG_ASSERT(arg_count == 1 &&
+              args[0]->type() == Item::FIELD_ITEM &&
+              args[0]->field_type() == MYSQL_TYPE_TIMESTAMP);
+  Field *field=((Item_field*) args[0])->field;
+  /* Leave the incl_endp intact */
+  return ((Field_timestamp*) field)->get_timestamp(&null_value);
+}
+
 
 longlong Item_func_time_to_sec::val_int()
 {

=== modified file 'sql/item_timefunc.h'
--- a/sql/item_timefunc.h	2011-03-30 07:42:03 +0000
+++ b/sql/item_timefunc.h	2011-06-13 10:55:58 +0000
@@ -387,6 +387,8 @@ public:
   Item_func_unix_timestamp(Item *a) :Item_int_func(a) {}
   longlong val_int();
   const char *func_name() const { return "unix_timestamp"; }
+  enum_monotonicity_info get_monotonicity_info() const;
+  longlong val_int_endpoint(bool left_endp, bool *incl_endp);
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
   /*
     UNIX_TIMESTAMP() depends on the current timezone

No bundle (reason: useless for push emails).