summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/alter_table.result29
-rw-r--r--mysql-test/r/flush.result23
-rw-r--r--mysql-test/r/multi_update.result38
-rw-r--r--mysql-test/r/multi_update_innodb.result40
-rw-r--r--mysql-test/r/mysql_embedded.result5
-rw-r--r--mysql-test/r/partition.result47
-rw-r--r--mysql-test/r/partition_datatype.result1000
-rw-r--r--mysql-test/r/sp_sync.result16
8 files changed, 1195 insertions, 3 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index eba9562a8c3..ab6793235c7 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -1345,6 +1345,35 @@ DROP TABLE t1;
CREATE TABLE t1 (a TEXT, id INT, b INT);
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST;
DROP TABLE t1;
+#
+# Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION
+# FIRST CAN CAUSE DATA TO BE CORRUPTED".
+#
+drop table if exists t1;
+# Use MyISAM engine as the fact that InnoDB doesn't support
+# in-place ALTER TABLE in cases when columns are being renamed
+# hides some bugs.
+create table t1 (i int, j int) engine=myisam;
+insert into t1 value (1, 2);
+# First, test for original problem described in the bug report.
+select * from t1;
+i j
+1 2
+# Change of column order by the below ALTER TABLE statement should
+# affect both column names and column contents.
+alter table t1 modify column j int first;
+select * from t1;
+j i
+2 1
+# Now test for similar problem with the same root.
+# The below ALTER TABLE should change not only the name but
+# also the value for the last column of the table.
+alter table t1 drop column i, add column k int default 0;
+select * from t1;
+j k
+2 0
+# Clean-up.
+drop table t1;
End of 5.1 tests
CREATE TABLE t1(c CHAR(10),
i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result
index bbfea2dade8..e36cd8a478f 100644
--- a/mysql-test/r/flush.result
+++ b/mysql-test/r/flush.result
@@ -466,3 +466,26 @@ ALTER TABLE t1 COMMENT 'test';
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
UNLOCK TABLES;
DROP TABLE t1;
+#
+# Test for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES
+# GRADUALLY IF A TRIGGER EXISTS".
+#
+# One of side-effects of this bug was that a transaction which
+# involved DML statements requiring prelocking blocked concurrent
+# FLUSH TABLES WITH READ LOCK for the whole its duration, while
+# correct behavior in this case is to block FTWRL only for duration
+# of individual DML statements.
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id INT PRIMARY KEY, value INT);
+INSERT INTO t1 VALUES (1, 1);
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW SET @var = "a";
+BEGIN;
+UPDATE t1 SET value= value + 1 WHERE id = 1;
+# Switching to connection 'con1'.
+# The below FLUSH TABLES WITH READ LOCK should succeed and
+# should not be blocked by the transaction in default connection.
+FLUSH TABLES WITH READ LOCK;
+UNLOCK TABLES;
+# Switching to connection 'default'.
+COMMIT;
+DROP TABLE t1;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 5a3e7938121..c6ee170eef7 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -697,4 +697,40 @@ SELECT * FROM t1;
pk a
1 2
DROP TABLE t1;
-end of tests
+#
+# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS
+# UPDATED TWICE
+#
+CREATE TABLE t1 (
+col_int_key int,
+pk int,
+col_int int,
+key(col_int_key),
+primary key (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,2,3);
+
+CREATE TABLE t2 (
+col_int_key int,
+pk_1 int,
+pk_2 int,
+col_int int,
+key(col_int_key),
+primary key (pk_1,pk_2)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+
+SELECT * FROM t1;
+col_int_key pk col_int
+1 7 3
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+
+SELECT * FROM t2;
+col_int_key pk_1 pk_2 col_int
+1 7 11 4
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/multi_update_innodb.result b/mysql-test/r/multi_update_innodb.result
index 12a94accc1f..643287c3a93 100644
--- a/mysql-test/r/multi_update_innodb.result
+++ b/mysql-test/r/multi_update_innodb.result
@@ -27,3 +27,43 @@ pk a b
0 1 2
DROP VIEW v1;
DROP TABLE t1;
+#
+# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS
+# UPDATED TWICE
+#
+CREATE TABLE t1 (
+col_int_key int,
+pk int,
+col_int int,
+key(col_int_key),
+primary key (pk)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2,3);
+
+CREATE TABLE t2 (
+col_int_key int,
+pk_1 int,
+pk_2 int,
+col_int int,
+key(col_int_key),
+primary key (pk_1,pk_2)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,2,3,4);
+
+UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+SELECT * FROM t1;
+col_int_key pk col_int
+1 2 3
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
+ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
+
+SELECT * FROM t2;
+col_int_key pk_1 pk_2 col_int
+1 2 3 4
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/mysql_embedded.result b/mysql-test/r/mysql_embedded.result
new file mode 100644
index 00000000000..3ba79a01e44
--- /dev/null
+++ b/mysql-test/r/mysql_embedded.result
@@ -0,0 +1,5 @@
+#
+# Bug#12561297 : LIBMYSQLD/EXAMPLE/MYSQL_EMBEDDED IS ABORTING.
+#
+1
+1
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 49e7f4a6ec9..0febdbc38d0 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -1,5 +1,34 @@
drop table if exists t1, t2;
#
+# Bug#11765667: bug#58655: ASSERTION FAILED,
+# SERVER CRASHES WITH MYSQLD GOT SIGNAL 6
+#
+CREATE TABLE t1 (
+id MEDIUMINT NOT NULL AUTO_INCREMENT,
+dt DATE, st VARCHAR(255), uid INT,
+id2nd LONGBLOB, filler VARCHAR(255), PRIMARY KEY(id, dt)
+);
+INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES
+('1991-03-14', 'Initial Insert', 200, 1234567, 'No Data'),
+('1991-02-26', 'Initial Insert', 201, 1234567, 'No Data'),
+('1992-03-16', 'Initial Insert', 234, 1234567, 'No Data'),
+('1992-07-02', 'Initial Insert', 287, 1234567, 'No Data'),
+('1991-05-26', 'Initial Insert', 256, 1234567, 'No Data'),
+('1991-04-25', 'Initial Insert', 222, 1234567, 'No Data'),
+('1993-03-12', 'Initial Insert', 267, 1234567, 'No Data'),
+('1993-03-14', 'Initial Insert', 291, 1234567, 'No Data'),
+('1991-12-20', 'Initial Insert', 298, 1234567, 'No Data'),
+('1994-10-31', 'Initial Insert', 220, 1234567, 'No Data');
+ALTER TABLE t1 PARTITION BY LIST (YEAR(dt)) (
+PARTITION d1 VALUES IN (1991, 1994),
+PARTITION d2 VALUES IN (1993),
+PARTITION d3 VALUES IN (1992, 1995, 1996)
+);
+INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES
+('1991-07-14', 'After Partitioning Insert', 299, 1234567, 'Insert row');
+UPDATE t1 SET filler='Updating the row' WHERE uid=298;
+DROP TABLE t1;
+#
# Bug#59297: Can't find record in 'tablename' on update inner join
#
CREATE TABLE t1 (
@@ -189,6 +218,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
+2007-07-30 17: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);
@@ -197,6 +235,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
+2007-07-30 17: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` (
diff --git a/mysql-test/r/partition_datatype.result b/mysql-test/r/partition_datatype.result
index 47ea799f497..651d924d7e5 100644
--- a/mysql-test/r/partition_datatype.result
+++ b/mysql-test/r/partition_datatype.result
@@ -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
+1970-01-01 00:00:01 Moscow
+1970-01-01 00:00:01 UTC
+1974-02-05 18:28:16 Moscow
+1974-02-05 21:28:16 UTC
+2011-03-26 22:59:59 Moscow
+2011-03-26 22:59:59 UTC
+2011-03-26 23:00:00 Moscow
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 UTC
+2011-03-26 23:00:01 Moscow
+2011-03-26 23:00:01 UTC
+2011-10-29 21:59:59 Moscow
+2011-10-29 21:59:59 UTC
+2011-10-29 22:00:00 MoscowD
+2011-10-29 22:00:00 UTC
+2011-10-29 22:00:01 MoscowD
+2011-10-29 22:00:01 UTC
+2011-10-29 22:59:59 MoscowD
+2011-10-29 22:59:59 UTC
+2011-10-29 23:00:00 UTC
+2011-10-29 23:00:01 UTC
+2011-10-29 23:59:59 UTC
+2011-10-30 00:00:00 Moscow
+2011-10-30 00:00:00 UTC
+2011-10-30 00:00:01 Moscow
+2011-10-30 00:00:01 UTC
+2038-01-19 03:14:06 Moscow
+2038-01-19 03:14:06 UTC
+2038-01-19 03:14:07 Moscow
+2038-01-19 03:14:07 UTC
+SELECT * FROM t2 ORDER BY a, tz;
+a tz
+NULL Moscow
+NULL UTC
+0000-00-00 00:00:00 Moscow
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 UTC
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+1970-01-01 00:00:01 Moscow
+1970-01-01 00:00:01 UTC
+1974-02-05 18:28:16 Moscow
+1974-02-05 21:28:16 UTC
+2011-03-26 22:59:59 Moscow
+2011-03-26 22:59:59 UTC
+2011-03-26 23:00:00 Moscow
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 UTC
+2011-03-26 23:00:01 Moscow
+2011-03-26 23:00:01 UTC
+2011-10-29 21:59:59 Moscow
+2011-10-29 21:59:59 UTC
+2011-10-29 22:00:00 MoscowD
+2011-10-29 22:00:00 UTC
+2011-10-29 22:00:01 MoscowD
+2011-10-29 22:00:01 UTC
+2011-10-29 22:59:59 MoscowD
+2011-10-29 22:59:59 UTC
+2011-10-29 23:00:00 UTC
+2011-10-29 23:00:01 UTC
+2011-10-29 23:59:59 UTC
+2011-10-30 00:00:00 Moscow
+2011-10-30 00:00:00 UTC
+2011-10-30 00:00:01 Moscow
+2011-10-30 00:00:01 UTC
+2038-01-19 03:14:06 Moscow
+2038-01-19 03:14:06 UTC
+2038-01-19 03:14:07 Moscow
+2038-01-19 03:14:07 UTC
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 23:00:00' ORDER BY a, tz;
+a tz
+2011-03-26 22:59:59 Moscow
+2011-03-26 22:59:59 UTC
+2011-03-26 23:00:00 Moscow
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 23:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 22:59:59' ORDER BY a, tz;
+a tz
+2011-03-26 22:59:59 Moscow
+2011-03-26 22:59:59 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 22:59:59' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSK ALL NULL NULL NULL NULL 2 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-26 22:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz;
+a tz
+2011-03-26 22:59:59 Moscow
+2011-03-26 22:59:59 UTC
+2011-03-26 23:00:00 Moscow
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 UTC
+2011-03-26 23:00:01 Moscow
+2011-03-26 23:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-26 22:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-26 23:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz;
+a tz
+2011-03-26 23:00:00 Moscow
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 UTC
+2011-03-26 23:00:01 Moscow
+2011-03-26 23:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-26 23:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 23:00:00' ORDER BY a, tz;
+a tz
+2011-10-29 21:59:59 Moscow
+2011-10-29 21:59:59 UTC
+2011-10-29 22:00:00 MoscowD
+2011-10-29 22:00:00 UTC
+2011-10-29 22:00:01 MoscowD
+2011-10-29 22:00:01 UTC
+2011-10-29 22:59:59 MoscowD
+2011-10-29 22:59:59 UTC
+2011-10-29 23:00:00 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 23:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1 ALL NULL NULL NULL NULL 18 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 22:59:59' ORDER BY a, tz;
+a tz
+2011-10-29 21:59:59 Moscow
+2011-10-29 21:59:59 UTC
+2011-10-29 22:00:00 MoscowD
+2011-10-29 22:00:00 UTC
+2011-10-29 22:00:01 MoscowD
+2011-10-29 22:00:01 UTC
+2011-10-29 22:59:59 MoscowD
+2011-10-29 22:59:59 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 22:59:59' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2 ALL NULL NULL NULL NULL 15 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 22:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz;
+a tz
+2011-10-29 22:59:59 MoscowD
+2011-10-29 22:59:59 UTC
+2011-10-29 23:00:00 UTC
+2011-10-29 23:00:01 UTC
+2011-10-29 23:59:59 UTC
+2011-10-30 00:00:00 Moscow
+2011-10-30 00:00:00 UTC
+2011-10-30 00:00:01 Moscow
+2011-10-30 00:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 22:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 13 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 23:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz;
+a tz
+2011-10-29 23:00:00 UTC
+2011-10-29 23:00:01 UTC
+2011-10-29 23:59:59 UTC
+2011-10-30 00:00:00 Moscow
+2011-10-30 00:00:00 UTC
+2011-10-30 00:00:01 Moscow
+2011-10-30 00:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 23:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 7 Using where; Using filesort
+# Test end range changes
+DELETE FROM t2 WHERE a = 0;
+INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC');
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+35
+SELECT COUNT(*) FROM t2 WHERE a = 0;
+COUNT(*)
+1
+SELECT * FROM t2 ORDER BY a, tz LIMIT 3;
+a tz
+NULL Moscow
+NULL UTC
+0000-00-00 00:00:00 UTC
+SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3;
+a tz
+2038-01-19 03:14:07 Moscow
+2038-01-19 03:14:07 UTC
+2038-01-19 03:14:06 Moscow
+UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a);
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 34
+Warning 1264 Out of range value for column 'a' at row 35
+SELECT MIN(a), MAX(a) FROM t2;
+MIN(a) MAX(a)
+0000-00-00 00:00:00 2038-01-19 03:14:07
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+35
+SELECT COUNT(*) FROM t2 WHERE a = 0;
+COUNT(*)
+2
+SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
+WHERE TABLE_NAME = 't2';
+PARTITION_NAME TABLE_ROWS
+p0 3
+p-2000 6
+p-2011-MSK 0
+p-2011-MSD-1 9
+p-2011-MSD-2 6
+p-2012-MSK-1 4
+p-2012-MSK-2 5
+pEnd 0
+pMax 2
+SELECT * FROM t2 ORDER BY a, tz;
+a tz
+NULL Moscow
+NULL UTC
+NULL UTC
+0000-00-00 00:00:00 Moscow
+0000-00-00 00:00:00 UTC
+1970-01-01 00:00:02 Moscow
+1970-01-01 00:00:02 UTC
+1974-02-05 18:28:17 Moscow
+1974-02-05 21:28:17 UTC
+2011-03-26 23:00:00 Moscow
+2011-03-26 23:00:00 UTC
+2011-03-26 23:00:01 Moscow
+2011-03-26 23:00:01 MoscowI
+2011-03-26 23:00:01 MoscowI
+2011-03-26 23:00:01 MoscowI
+2011-03-26 23:00:01 UTC
+2011-03-26 23:00:02 Moscow
+2011-03-26 23:00:02 UTC
+2011-10-29 22:00:00 Moscow
+2011-10-29 22:00:00 UTC
+2011-10-29 22:00:01 MoscowD
+2011-10-29 22:00:01 UTC
+2011-10-29 22:00:02 MoscowD
+2011-10-29 22:00:02 UTC
+2011-10-29 23:00:00 MoscowD
+2011-10-29 23:00:00 UTC
+2011-10-29 23:00:01 UTC
+2011-10-29 23:00:02 UTC
+2011-10-30 00:00:00 UTC
+2011-10-30 00:00:01 Moscow
+2011-10-30 00:00:01 UTC
+2011-10-30 00:00:02 Moscow
+2011-10-30 00:00:02 UTC
+2038-01-19 03:14:07 Moscow
+2038-01-19 03:14:07 UTC
+# Test start range changes
+INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC');
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+36
+SELECT COUNT(*) FROM t2 WHERE a = 0;
+COUNT(*)
+3
+SELECT * FROM t2 ORDER BY a, tz LIMIT 3;
+a tz
+NULL Moscow
+NULL UTC
+NULL UTC
+SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3;
+a tz
+2038-01-19 03:14:07 Moscow
+2038-01-19 03:14:07 UTC
+2011-10-30 00:00:02 Moscow
+UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
+SELECT MIN(a), MAX(a) FROM t2;
+MIN(a) MAX(a)
+1970-01-01 00:00:01 2038-01-19 03:14:06
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+36
+SELECT COUNT(*) FROM t2 WHERE a = 0;
+COUNT(*)
+0
+SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
+WHERE TABLE_NAME = 't2';
+PARTITION_NAME TABLE_ROWS
+p0 6
+p-2000 4
+p-2011-MSK 2
+p-2011-MSD-1 9
+p-2011-MSD-2 6
+p-2012-MSK-1 3
+p-2012-MSK-2 4
+pEnd 2
+pMax 0
+SELECT * FROM t2 ORDER BY a, tz;
+a tz
+NULL Moscow
+NULL Moscow
+NULL UTC
+NULL UTC
+NULL UTC
+NULL UTC
+1970-01-01 00:00:01 Moscow
+1970-01-01 00:00:01 UTC
+1974-02-05 18:28:16 Moscow
+1974-02-05 21:28:16 UTC
+2011-03-26 22:59:59 Moscow
+2011-03-26 22:59:59 UTC
+2011-03-26 23:00:00 Moscow
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 MoscowI
+2011-03-26 23:00:00 UTC
+2011-03-26 23:00:01 Moscow
+2011-03-26 23:00:01 UTC
+2011-10-29 21:59:59 Moscow
+2011-10-29 21:59:59 UTC
+2011-10-29 22:00:00 MoscowD
+2011-10-29 22:00:00 UTC
+2011-10-29 22:00:01 MoscowD
+2011-10-29 22:00:01 UTC
+2011-10-29 22:59:59 MoscowD
+2011-10-29 22:59:59 UTC
+2011-10-29 23:00:00 UTC
+2011-10-29 23:00:01 UTC
+2011-10-29 23:59:59 UTC
+2011-10-30 00:00:00 Moscow
+2011-10-30 00:00:00 UTC
+2011-10-30 00:00:01 Moscow
+2011-10-30 00:00:01 UTC
+2038-01-19 03:14:06 Moscow
+2038-01-19 03:14:06 UTC
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` timestamp NULL DEFAULT NULL,
+ `tz` varchar(16) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(a))
+(PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM,
+ PARTITION `p-2000` VALUES LESS THAN (946684800) ENGINE = MyISAM,
+ PARTITION `p-2011-MSK` VALUES LESS THAN (1301180400) ENGINE = MyISAM,
+ PARTITION `p-2011-MSD-1` VALUES LESS THAN (1319925600) ENGINE = MyISAM,
+ PARTITION `p-2011-MSD-2` VALUES LESS THAN (1319929200) ENGINE = MyISAM,
+ PARTITION `p-2012-MSK-1` VALUES LESS THAN (1319932800) ENGINE = MyISAM,
+ PARTITION `p-2012-MSK-2` VALUES LESS THAN (1332630000) ENGINE = MyISAM,
+ PARTITION pEnd VALUES LESS THAN (2147483647) ENGINE = MyISAM,
+ PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+TRUNCATE TABLE t2;
+SET @@session.time_zone = 'Europe/Moscow';
+INSERT INTO t2 SELECT * FROM t1;
+SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
+WHERE TABLE_NAME = 't2';
+PARTITION_NAME TABLE_ROWS
+p0 2
+p-2000 16
+p-2011-MSK 2
+p-2011-MSD-1 9
+p-2011-MSD-2 6
+p-2012-MSK-1 3
+p-2012-MSK-2 4
+pEnd 2
+pMax 2
+SELECT * FROM t1 ORDER BY a, tz;
+a tz
+NULL Moscow
+NULL UTC
+0000-00-00 00:00:00 Moscow
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 UTC
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+1970-01-01 03:00:01 Moscow
+1970-01-01 03:00:01 UTC
+1974-02-05 21:28:16 Moscow
+1974-02-06 00:28:16 UTC
+2011-03-27 01:59:59 Moscow
+2011-03-27 01:59:59 UTC
+2011-03-27 03:00:00 Moscow
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 UTC
+2011-03-27 03:00:01 Moscow
+2011-03-27 03:00:01 UTC
+2011-10-30 01:59:59 Moscow
+2011-10-30 01:59:59 UTC
+2011-10-30 02:00:00 MoscowD
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 MoscowD
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 MoscowD
+2011-10-30 02:59:59 UTC
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 UTC
+2011-10-30 03:00:00 Moscow
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:01 Moscow
+2011-10-30 03:00:01 UTC
+2038-01-19 06:14:06 Moscow
+2038-01-19 06:14:06 UTC
+2038-01-19 06:14:07 Moscow
+2038-01-19 06:14:07 UTC
+SELECT * FROM t2 ORDER BY a, tz;
+a tz
+NULL Moscow
+NULL UTC
+0000-00-00 00:00:00 Moscow
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 MoscowI
+0000-00-00 00:00:00 UTC
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+0000-00-00 00:00:00 UTCI
+1970-01-01 03:00:01 Moscow
+1970-01-01 03:00:01 UTC
+1974-02-05 21:28:16 Moscow
+1974-02-06 00:28:16 UTC
+2011-03-27 01:59:59 Moscow
+2011-03-27 01:59:59 UTC
+2011-03-27 03:00:00 Moscow
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 UTC
+2011-03-27 03:00:01 Moscow
+2011-03-27 03:00:01 UTC
+2011-10-30 01:59:59 Moscow
+2011-10-30 01:59:59 UTC
+2011-10-30 02:00:00 MoscowD
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 MoscowD
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 MoscowD
+2011-10-30 02:59:59 UTC
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 UTC
+2011-10-30 03:00:00 Moscow
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:01 Moscow
+2011-10-30 03:00:01 UTC
+2038-01-19 06:14:06 Moscow
+2038-01-19 06:14:06 UTC
+2038-01-19 06:14:07 Moscow
+2038-01-19 06:14:07 UTC
+# Testing the leap from 01:59:59 to 03:00:00
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 03:00:00' ORDER BY a, tz;
+a tz
+2011-03-27 01:59:59 Moscow
+2011-03-27 01:59:59 UTC
+2011-03-27 03:00:00 Moscow
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 03:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 01:59:59' ORDER BY a, tz;
+a tz
+2011-03-27 01:59:59 Moscow
+2011-03-27 01:59:59 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 01:59:59' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSK ALL NULL NULL NULL NULL 2 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-26 01:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz;
+a tz
+2011-03-27 01:59:59 Moscow
+2011-03-27 01:59:59 UTC
+2011-03-27 03:00:00 Moscow
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 UTC
+2011-03-27 03:00:01 Moscow
+2011-03-27 03:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-26 01:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-26 03:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz;
+a tz
+2011-03-27 01:59:59 Moscow
+2011-03-27 01:59:59 UTC
+2011-03-27 03:00:00 Moscow
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 UTC
+2011-03-27 03:00:01 Moscow
+2011-03-27 03:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-03-26 03:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort
+# Testing the leap from 02:59:59 to 02:00:00
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:00:00' ORDER BY a, tz;
+a tz
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:59:59' ORDER BY a, tz;
+a tz
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:59:59' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 03:00:00' ORDER BY a, tz;
+a tz
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 03:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 01:59:59' ORDER BY a, tz;
+a tz
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 01:59:59' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 02:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz;
+a tz
+2011-10-30 01:59:59 Moscow
+2011-10-30 01:59:59 UTC
+2011-10-30 02:00:00 MoscowD
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 MoscowD
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 MoscowD
+2011-10-30 02:59:59 UTC
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 UTC
+2011-10-30 03:00:00 Moscow
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:01 Moscow
+2011-10-30 03:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 02:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 22 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 02:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz;
+a tz
+2011-10-30 01:59:59 Moscow
+2011-10-30 01:59:59 UTC
+2011-10-30 02:00:00 MoscowD
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 MoscowD
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 MoscowD
+2011-10-30 02:59:59 UTC
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 UTC
+2011-10-30 03:00:00 Moscow
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:01 Moscow
+2011-10-30 03:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 02:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 22 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 03:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz;
+a tz
+2011-10-30 01:59:59 Moscow
+2011-10-30 01:59:59 UTC
+2011-10-30 02:00:00 MoscowD
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 MoscowD
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 MoscowD
+2011-10-30 02:59:59 UTC
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 UTC
+2011-10-30 03:00:00 Moscow
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:01 Moscow
+2011-10-30 03:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 03:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 22 Using where; Using filesort
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 01:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz;
+a tz
+2011-10-30 01:59:59 Moscow
+2011-10-30 01:59:59 UTC
+2011-10-30 02:00:00 MoscowD
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 MoscowD
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 MoscowD
+2011-10-30 02:59:59 UTC
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 UTC
+2011-10-30 03:00:00 Moscow
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:01 Moscow
+2011-10-30 03:00:01 UTC
+EXPLAIN PARTITIONS
+SELECT * FROM t2
+WHERE a BETWEEN '2011-10-29 01:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 22 Using where; Using filesort
+# Test end range changes
+DELETE FROM t2 WHERE a = 0;
+INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow');
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+35
+SELECT COUNT(*) FROM t2 WHERE a = 0;
+COUNT(*)
+1
+SELECT * FROM t2 ORDER BY a, tz LIMIT 3;
+a tz
+NULL Moscow
+NULL UTC
+0000-00-00 00:00:00 Moscow
+SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3;
+a tz
+2038-01-19 06:14:07 Moscow
+2038-01-19 06:14:07 UTC
+2038-01-19 06:14:06 Moscow
+UPDATE t2 SET a = TIMESTAMPADD(SECOND, 1, a);
+Warnings:
+Warning 1299 Invalid TIMESTAMP value in column 'a' at row 8
+Warning 1299 Invalid TIMESTAMP value in column 'a' at row 9
+Warning 1264 Out of range value for column 'a' at row 34
+Warning 1264 Out of range value for column 'a' at row 35
+SELECT MIN(a), MAX(a) FROM t2;
+MIN(a) MAX(a)
+0000-00-00 00:00:00 2038-01-19 06:14:07
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+35
+SELECT COUNT(*) FROM t2 WHERE a = 0;
+COUNT(*)
+2
+SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
+WHERE TABLE_NAME = 't2';
+PARTITION_NAME TABLE_ROWS
+p0 3
+p-2000 6
+p-2011-MSK 0
+p-2011-MSD-1 9
+p-2011-MSD-2 8
+p-2012-MSK-1 0
+p-2012-MSK-2 7
+pEnd 0
+pMax 2
+SELECT * FROM t2 ORDER BY a, tz;
+a tz
+NULL Moscow
+NULL Moscow
+NULL UTC
+0000-00-00 00:00:00 Moscow
+0000-00-00 00:00:00 UTC
+1970-01-01 03:00:02 Moscow
+1970-01-01 03:00:02 UTC
+1974-02-05 21:28:17 Moscow
+1974-02-06 00:28:17 UTC
+2011-03-27 03:00:00 Moscow
+2011-03-27 03:00:00 UTC
+2011-03-27 03:00:01 Moscow
+2011-03-27 03:00:01 MoscowI
+2011-03-27 03:00:01 MoscowI
+2011-03-27 03:00:01 MoscowI
+2011-03-27 03:00:01 UTC
+2011-03-27 03:00:02 Moscow
+2011-03-27 03:00:02 UTC
+2011-10-30 02:00:00 Moscow
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 MoscowD
+2011-10-30 02:00:01 UTC
+2011-10-30 02:00:01 UTC
+2011-10-30 02:00:02 MoscowD
+2011-10-30 02:00:02 UTC
+2011-10-30 02:00:02 UTC
+2011-10-30 03:00:00 MoscowD
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:01 Moscow
+2011-10-30 03:00:01 UTC
+2011-10-30 03:00:02 Moscow
+2011-10-30 03:00:02 UTC
+2038-01-19 06:14:07 Moscow
+2038-01-19 06:14:07 UTC
+# Test start range changes
+INSERT INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow');
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+36
+SELECT COUNT(*) FROM t2 WHERE a = 0;
+COUNT(*)
+3
+SELECT * FROM t2 ORDER BY a, tz LIMIT 3;
+a tz
+NULL Moscow
+NULL Moscow
+NULL UTC
+SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3;
+a tz
+2038-01-19 06:14:07 Moscow
+2038-01-19 06:14:07 UTC
+2011-10-30 03:00:02 Moscow
+UPDATE t2 SET a = TIMESTAMPADD(SECOND, -1, a);
+Warnings:
+Warning 1299 Invalid TIMESTAMP value in column 'a' at row 18
+Warning 1299 Invalid TIMESTAMP value in column 'a' at row 19
+SELECT MIN(a), MAX(a) FROM t2;
+MIN(a) MAX(a)
+1970-01-01 03:00:01 2038-01-19 06:14:06
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+36
+SELECT COUNT(*) FROM t2 WHERE a = 0;
+COUNT(*)
+0
+SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
+WHERE TABLE_NAME = 't2';
+PARTITION_NAME TABLE_ROWS
+p0 6
+p-2000 4
+p-2011-MSK 0
+p-2011-MSD-1 11
+p-2011-MSD-2 9
+p-2012-MSK-1 0
+p-2012-MSK-2 4
+pEnd 2
+pMax 0
+SELECT * FROM t2 ORDER BY a, tz;
+a tz
+NULL Moscow
+NULL Moscow
+NULL Moscow
+NULL Moscow
+NULL UTC
+NULL UTC
+1970-01-01 03:00:01 Moscow
+1970-01-01 03:00:01 UTC
+1974-02-05 21:28:16 Moscow
+1974-02-06 00:28:16 UTC
+2011-03-27 03:00:00 Moscow
+2011-03-27 03:00:00 Moscow
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 MoscowI
+2011-03-27 03:00:00 UTC
+2011-03-27 03:00:00 UTC
+2011-03-27 03:00:01 Moscow
+2011-03-27 03:00:01 UTC
+2011-10-30 01:59:59 Moscow
+2011-10-30 01:59:59 UTC
+2011-10-30 02:00:00 MoscowD
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:00 UTC
+2011-10-30 02:00:01 MoscowD
+2011-10-30 02:00:01 UTC
+2011-10-30 02:00:01 UTC
+2011-10-30 02:59:59 MoscowD
+2011-10-30 02:59:59 UTC
+2011-10-30 02:59:59 UTC
+2011-10-30 03:00:00 Moscow
+2011-10-30 03:00:00 UTC
+2011-10-30 03:00:01 Moscow
+2011-10-30 03:00:01 UTC
+2038-01-19 06:14:06 Moscow
+2038-01-19 06:14:06 UTC
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` timestamp NULL DEFAULT NULL,
+ `tz` varchar(16) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(a))
+(PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM,
+ PARTITION `p-2000` VALUES LESS THAN (946684800) ENGINE = MyISAM,
+ PARTITION `p-2011-MSK` VALUES LESS THAN (1301180400) ENGINE = MyISAM,
+ PARTITION `p-2011-MSD-1` VALUES LESS THAN (1319925600) ENGINE = MyISAM,
+ PARTITION `p-2011-MSD-2` VALUES LESS THAN (1319929200) ENGINE = MyISAM,
+ PARTITION `p-2012-MSK-1` VALUES LESS THAN (1319932800) ENGINE = MyISAM,
+ PARTITION `p-2012-MSK-2` VALUES LESS THAN (1332630000) ENGINE = MyISAM,
+ PARTITION pEnd VALUES LESS THAN (2147483647) ENGINE = MyISAM,
+ PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+TRUNCATE TABLE t2;
+DROP TABLE t1, t2;
+SET @@session.time_zone= @old_time_zone;
diff --git a/mysql-test/r/sp_sync.result b/mysql-test/r/sp_sync.result
index 174b3c60745..ba90b009ce4 100644
--- a/mysql-test/r/sp_sync.result
+++ b/mysql-test/r/sp_sync.result
@@ -1,4 +1,4 @@
-Tests of syncronization of stored procedure execution.
+Tests of synchronization of stored procedure execution.
SET DEBUG_SYNC= 'RESET';
#
# Bug #30977 Concurrent statement using stored function and
@@ -92,4 +92,16 @@ COUNT(f1(a))
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP TABLES t0, t1;
-SET DEBUG_SYNC= 'RESET';
+#
+# test for bug#11756013
+#
+DROP SCHEMA IF EXISTS s1;
+CREATE SCHEMA s1;
+CREATE PROCEDURE s1.p1() BEGIN END;
+SET DEBUG_SYNC='before_db_dir_check SIGNAL check_db WAIT_FOR dropped_schema';
+CALL s1.p1;
+SET DEBUG_SYNC='now WAIT_FOR check_db';
+DROP SCHEMA s1;
+SET DEBUG_SYNC='now SIGNAL dropped_schema';
+ERROR 42000: Unknown database 's1'
+SET DEBUG_SYNC = 'RESET';
View Lorry Controller Status