1drop table if exists t1; 2# test with not null 3create table t1 (a bit not null) partition by key (a); 4insert into t1 values (b'1'); 5select hex(a) from t1 where a = b'1'; 6hex(a) 71 8drop table t1; 9create table t1 (a tinyint not null) partition by key (a); 10insert into t1 values (2); 11select * from t1 where a = 2; 12a 132 14drop table t1; 15create table t1 (a smallint not null) partition by key (a); 16insert into t1 values (2); 17select * from t1 where a = 2; 18a 192 20drop table t1; 21create table t1 (a mediumint not null) partition by key (a); 22insert into t1 values (2); 23select * from t1 where a = 2; 24a 252 26drop table t1; 27create table t1 (a int not null) partition by key (a); 28insert into t1 values (2); 29select * from t1 where a = 2; 30a 312 32drop table t1; 33create table t1 (a bigint not null) partition by key (a); 34insert into t1 values (2); 35select * from t1 where a = 2; 36a 372 38drop table t1; 39create table t1 (a float not null) partition by key (a); 40insert into t1 values (0.5); 41select * from t1 where a = 0.5; 42a 430.5 44drop table t1; 45create table t1 (a double not null) partition by key (a); 46insert into t1 values (0.5); 47select * from t1 where a = 0.5; 48a 490.5 50drop table t1; 51create table t1 (a decimal(4,2) not null) partition by key (a); 52insert into t1 values (2.1); 53select * from t1 where a = 2.1; 54a 552.10 56drop table t1; 57create table t1 (a date not null) partition by key (a); 58insert into t1 values ('2001-01-01'); 59select * from t1 where a = '2001-01-01'; 60a 612001-01-01 62drop table t1; 63create table t1 (a datetime not null) partition by key (a); 64insert into t1 values ('2001-01-01 01:02:03'); 65select * from t1 where a = '2001-01-01 01:02:03'; 66a 672001-01-01 01:02:03 68drop table t1; 69create table t1 (a timestamp not null) partition by key (a); 70insert into t1 values ('2001-01-01 01:02:03'); 71select * from t1 where a = '2001-01-01 01:02:03'; 72a 732001-01-01 01:02:03 74drop table t1; 75create table t1 (a time not null) partition by key (a); 76insert into t1 values ('01:02:03'); 77select * from t1 where a = '01:02:03'; 78a 7901:02:03 80drop table t1; 81create table t1 (a year not null) partition by key (a); 82insert into t1 values ('2001'); 83select * from t1 where a = '2001'; 84a 852001 86drop table t1; 87create table t1 (a varchar(10) character set utf8 not null) partition by key (a); 88insert into t1 values ('abc'); 89select * from t1 where a = 'abc'; 90a 91abc 92drop table t1; 93create table t1 (a varchar(300) character set utf8 not null) partition by key (a); 94insert into t1 values ('abc'); 95select * from t1 where a = 'abc'; 96a 97abc 98drop table t1; 99create table t1 (a varchar(10) character set latin1 not null) partition by key (a); 100insert into t1 values ('abc'); 101select * from t1 where a = 'abc'; 102a 103abc 104drop table t1; 105create table t1 (a varchar(300) character set latin1 not null) partition by key (a); 106insert into t1 values ('abc'); 107select * from t1 where a = 'abc'; 108a 109abc 110drop table t1; 111create table t1 (a char(10) character set utf8 not null) partition by key (a); 112insert into t1 values ('abc'); 113select * from t1 where a = 'abc'; 114a 115abc 116drop table t1; 117create table t1 (a char(10) character set latin1 not null) partition by key (a); 118insert into t1 values ('abc'); 119select * from t1 where a = 'abc'; 120a 121abc 122drop table t1; 123create table t1 (a enum('y','n') not null) partition by key (a); 124insert into t1 values ('y'); 125select * from t1 where a = 'y'; 126a 127y 128drop table t1; 129create table t1 (a set('y','n') not null) partition by key (a); 130insert into t1 values ('y'); 131select * from t1 where a = 'y'; 132a 133y 134drop table t1; 135# test with null allowed 136create table t1 (a bit) partition by key (a); 137insert into t1 values (b'1'); 138insert into t1 values (NULL); 139select hex(a) from t1 where a = b'1'; 140hex(a) 1411 142select hex(a) from t1 where a is NULL; 143hex(a) 144NULL 145select hex(a) from t1 order by a; 146hex(a) 147NULL 1481 149drop table t1; 150create table t1 (a tinyint) partition by key (a); 151insert into t1 values (2); 152select * from t1 where a = 2; 153a 1542 155drop table t1; 156create table t1 (a smallint) partition by key (a); 157insert into t1 values (2); 158select * from t1 where a = 2; 159a 1602 161drop table t1; 162create table t1 (a mediumint) partition by key (a); 163insert into t1 values (2); 164select * from t1 where a = 2; 165a 1662 167drop table t1; 168create table t1 (a int) partition by key (a); 169insert into t1 values (2); 170select * from t1 where a = 2; 171a 1722 173drop table t1; 174create table t1 (a bigint) partition by key (a); 175insert into t1 values (2); 176select * from t1 where a = 2; 177a 1782 179drop table t1; 180create table t1 (a float) partition by key (a); 181insert into t1 values (0.5); 182select * from t1 where a = 0.5; 183a 1840.5 185drop table t1; 186create table t1 (a double) partition by key (a); 187insert into t1 values (0.5); 188select * from t1 where a = 0.5; 189a 1900.5 191drop table t1; 192create table t1 (a decimal(4,2)) partition by key (a); 193insert into t1 values (2.1); 194select * from t1 where a = 2.1; 195a 1962.10 197drop table t1; 198create table t1 (a date) partition by key (a); 199insert into t1 values ('2001-01-01'); 200select * from t1 where a = '2001-01-01'; 201a 2022001-01-01 203drop table t1; 204create table t1 (a datetime) partition by key (a); 205insert into t1 values ('2001-01-01 01:02:03'); 206select * from t1 where a = '2001-01-01 01:02:03'; 207a 2082001-01-01 01:02:03 209drop table t1; 210create table t1 (a timestamp null) partition by key (a); 211insert into t1 values ('2001-01-01 01:02:03'); 212select * from t1 where a = '2001-01-01 01:02:03'; 213a 2142001-01-01 01:02:03 215drop table t1; 216create table t1 (a time) partition by key (a); 217insert into t1 values ('01:02:03'); 218select * from t1 where a = '01:02:03'; 219a 22001:02:03 221drop table t1; 222create table t1 (a year) partition by key (a); 223insert into t1 values ('2001'); 224select * from t1 where a = '2001'; 225a 2262001 227drop table t1; 228create table t1 (a varchar(10) character set utf8) partition by key (a); 229insert into t1 values ('abc'); 230select * from t1 where a = 'abc'; 231a 232abc 233drop table t1; 234create table t1 (a varchar(300) character set utf8) partition by key (a); 235insert into t1 values ('abc'); 236select * from t1 where a = 'abc'; 237a 238abc 239drop table t1; 240create table t1 (a varchar(10) character set latin1) partition by key (a); 241insert into t1 values ('abc'); 242select * from t1 where a = 'abc'; 243a 244abc 245drop table t1; 246create table t1 (a varchar(300) character set latin1) partition by key (a); 247insert into t1 values ('abc'); 248select * from t1 where a = 'abc'; 249a 250abc 251drop table t1; 252create table t1 (a char(10) character set utf8) partition by key (a); 253insert into t1 values ('abc'); 254select * from t1 where a = 'abc'; 255a 256abc 257drop table t1; 258create table t1 (a char(10) character set latin1) partition by key (a); 259insert into t1 values ('abc'); 260select * from t1 where a = 'abc'; 261a 262abc 263drop table t1; 264create table t1 (a enum('y','n')) partition by key (a); 265insert into t1 values ('y'); 266select * from t1 where a = 'y'; 267a 268y 269drop table t1; 270create table t1 (a set('y','n')) partition by key (a); 271insert into t1 values ('y'); 272select * from t1 where a = 'y'; 273a 274y 275drop table t1; 276create table t1 (a varchar(3068)) partition by key (a); 277insert into t1 values ('bbbb'); 278insert into t1 values ('aaaa'); 279select * from t1 where a = 'aaaa'; 280a 281aaaa 282select * from t1 where a like 'aaa%'; 283a 284aaaa 285select * from t1 where a = 'bbbb'; 286a 287bbbb 288drop table t1; 289create table t1 (a varchar(3069)) partition by key (a); 290insert into t1 values ('bbbb'); 291insert into t1 values ('aaaa'); 292select * from t1 where a = 'aaaa'; 293a 294aaaa 295select * from t1 where a like 'aaa%'; 296a 297aaaa 298select * from t1 where a = 'bbbb'; 299a 300bbbb 301drop table t1; 302create table t1 (a varchar(3070) not null) partition by key (a); 303insert into t1 values ('bbbb'); 304insert into t1 values ('aaaa'); 305select * from t1 where a = 'aaaa'; 306a 307aaaa 308select * from t1 where a like 'aaa%'; 309a 310aaaa 311select * from t1 where a = 'bbbb'; 312a 313bbbb 314drop table t1; 315set sql_mode=''; 316create table t1 (a varchar(3070)) partition by key (a); 317ERROR HY000: The total length of the partitioning fields is too large 318create table t1 (a varchar(65532) not null) partition by key (a); 319ERROR HY000: The total length of the partitioning fields is too large 320create table t1 (a varchar(65533)) partition by key (a); 321ERROR HY000: A BLOB field is not allowed in partition function 322create table t1 (a varchar(65534) not null) partition by key (a); 323ERROR HY000: A BLOB field is not allowed in partition function 324create table t1 (a varchar(65535)) partition by key (a); 325ERROR HY000: A BLOB field is not allowed in partition function 326set sql_mode=default; 327create table t1 (a bit(27), primary key (a)) engine=myisam 328partition by hash (a) 329(partition p0, partition p1, partition p2); 330show create table t1; 331Table Create Table 332t1 CREATE TABLE `t1` ( 333 `a` bit(27) NOT NULL, 334 PRIMARY KEY (`a`) 335) ENGINE=MyISAM DEFAULT CHARSET=latin1 336 PARTITION BY HASH (`a`) 337(PARTITION `p0` ENGINE = MyISAM, 338 PARTITION `p1` ENGINE = MyISAM, 339 PARTITION `p2` ENGINE = MyISAM) 340insert into t1 values (1),(4),(7),(10),(13),(16),(19),(22),(25),(28),(31),(34); 341select hex(a) from t1 where a = 7; 342hex(a) 3437 344drop table t1; 345# 346# Bug#28928: UNIX_TIMESTAMP() should be considered unary monotonic 347# by partition pruning 348SET @old_time_zone= @@session.time_zone; 349SET @@session.time_zone = 'UTC'; 350# Using MyISAM to get stable values on TABLE_ROWS in I_S.PARTITIONS 351CREATE TABLE t1 352(a TIMESTAMP NULL, 353tz varchar(16)) 354ENGINE = MyISAM; 355CREATE TABLE t2 LIKE t1; 356ALTER TABLE t2 PARTITION BY RANGE (UNIX_TIMESTAMP(a)) 357(PARTITION `p0` VALUES LESS THAN (0), 358PARTITION `p-2000` VALUES LESS THAN (UNIX_TIMESTAMP(20000101)), 359PARTITION `p-2011-MSK` VALUES LESS THAN (UNIX_TIMESTAMP(20110326230000)), 360PARTITION `p-2011-MSD-1` VALUES LESS THAN (UNIX_TIMESTAMP(20111029220000)), 361PARTITION `p-2011-MSD-2` VALUES LESS THAN (UNIX_TIMESTAMP(20111029230000)), 362PARTITION `p-2012-MSK-1` VALUES LESS THAN (UNIX_TIMESTAMP(20111030000000)), 363PARTITION `p-2012-MSK-2` VALUES LESS THAN (UNIX_TIMESTAMP(20120324230000)), 364PARTITION `pEnd` VALUES LESS THAN (UNIX_TIMESTAMP(20380119031407)), 365PARTITION `pMax` VALUES LESS THAN MAXVALUE); 366# Test 'odd' values 367INSERT INTO t1 VALUES (NULL, 'UTC'); 368INSERT INTO t1 VALUES ('0000-00-00 00:00:00', 'UTC'); 369# Test invalid values 370INSERT IGNORE INTO t1 VALUES ('1901-01-01 00:00:00', 'UTCI'); 371Warnings: 372Warning 1264 Out of range value for column 'a' at row 1 373INSERT IGNORE INTO t1 VALUES ('1969-12-31 23:59:59', 'UTCI'); 374Warnings: 375Warning 1264 Out of range value for column 'a' at row 1 376INSERT IGNORE INTO t1 VALUES ('2038-01-19 03:14:08', 'UTCI'); 377Warnings: 378Warning 1264 Out of range value for column 'a' at row 1 379INSERT IGNORE INTO t1 VALUES ('1970-01-01 00:00:00', 'UTCI'); 380Warnings: 381Warning 1264 Out of range value for column 'a' at row 1 382# Test start range 383INSERT INTO t1 VALUES ('1970-01-01 00:00:01', 'UTC'); 384INSERT INTO t1 VALUES ('1974-02-05 21:28:16', 'UTC'); 385# Test end range 386INSERT INTO t1 VALUES ('2038-01-19 03:14:06', 'UTC'); 387INSERT INTO t1 VALUES ('2038-01-19 03:14:07', 'UTC'); 388# Test Daylight saving shift 389INSERT INTO t1 VALUES ('2011-03-26 22:59:59', 'UTC'); 390INSERT INTO t1 VALUES ('2011-03-26 23:00:00', 'UTC'); 391INSERT INTO t1 VALUES ('2011-03-26 23:00:01', 'UTC'); 392INSERT INTO t1 VALUES ('2011-10-29 21:59:59', 'UTC'); 393INSERT INTO t1 VALUES ('2011-10-29 22:00:00', 'UTC'); 394INSERT INTO t1 VALUES ('2011-10-29 22:00:01', 'UTC'); 395INSERT INTO t1 VALUES ('2011-10-29 22:59:59', 'UTC'); 396INSERT INTO t1 VALUES ('2011-10-29 23:00:00', 'UTC'); 397INSERT INTO t1 VALUES ('2011-10-29 23:00:01', 'UTC'); 398INSERT INTO t1 VALUES ('2011-10-29 23:59:59', 'UTC'); 399INSERT INTO t1 VALUES ('2011-10-30 00:00:00', 'UTC'); 400INSERT INTO t1 VALUES ('2011-10-30 00:00:01', 'UTC'); 401SET @@session.time_zone = 'Europe/Moscow'; 402# Test 'odd' values 403INSERT INTO t1 VALUES (NULL, 'Moscow'); 404INSERT INTO t1 VALUES ('0000-00-00 00:00:00', 'Moscow'); 405# Test invalid values 406INSERT IGNORE INTO t1 VALUES ('0000-00-00 03:00:00', 'MoscowI'); 407Warnings: 408Warning 1265 Data truncated for column 'a' at row 1 409INSERT IGNORE INTO t1 VALUES ('1901-01-01 00:00:00', 'MoscowI'); 410Warnings: 411Warning 1264 Out of range value for column 'a' at row 1 412INSERT IGNORE INTO t1 VALUES ('1969-12-31 23:59:59', 'MoscowI'); 413Warnings: 414Warning 1264 Out of range value for column 'a' at row 1 415INSERT IGNORE INTO t1 VALUES ('1970-01-01 02:29:29', 'MoscowI'); 416Warnings: 417Warning 1264 Out of range value for column 'a' at row 1 418INSERT IGNORE INTO t1 VALUES ('2038-01-19 06:14:08', 'MoscowI'); 419Warnings: 420Warning 1264 Out of range value for column 'a' at row 1 421INSERT IGNORE INTO t1 VALUES ('1970-01-01 03:00:00', 'MoscowI'); 422Warnings: 423Warning 1264 Out of range value for column 'a' at row 1 424# values truncated to 03:00:00 due to daylight saving shift 425INSERT IGNORE INTO t1 VALUES ('2011-03-27 02:00:00', 'MoscowI'); 426Warnings: 427Warning 1299 Invalid TIMESTAMP value in column 'a' at row 1 428INSERT IGNORE INTO t1 VALUES ('2011-03-27 02:00:01', 'MoscowI'); 429Warnings: 430Warning 1299 Invalid TIMESTAMP value in column 'a' at row 1 431INSERT IGNORE INTO t1 VALUES ('2011-03-27 02:59:59', 'MoscowI'); 432Warnings: 433Warning 1299 Invalid TIMESTAMP value in column 'a' at row 1 434# Test start range 435INSERT INTO t1 VALUES ('1970-01-01 03:00:01', 'Moscow'); 436INSERT INTO t1 VALUES ('1974-02-05 21:28:16', 'Moscow'); 437# Test end range 438INSERT INTO t1 VALUES ('2038-01-19 06:14:06', 'Moscow'); 439INSERT INTO t1 VALUES ('2038-01-19 06:14:07', 'Moscow'); 440# Test Daylight saving shift 441INSERT INTO t1 VALUES ('2011-03-27 01:59:59', 'Moscow'); 442INSERT INTO t1 VALUES ('2011-03-27 03:00:00', 'Moscow'); 443INSERT INTO t1 VALUES ('2011-03-27 03:00:01', 'Moscow'); 444INSERT INTO t1 VALUES ('2011-10-30 01:59:59', 'Moscow'); 445# All values between 02:00 and 02:59:59 will be interpretated as DST 446INSERT INTO t1 VALUES ('2011-10-30 02:00:00', 'MoscowD'); 447INSERT INTO t1 VALUES ('2011-10-30 02:00:01', 'MoscowD'); 448INSERT INTO t1 VALUES ('2011-10-30 02:59:59', 'MoscowD'); 449INSERT INTO t1 VALUES ('2011-10-30 03:00:00', 'Moscow'); 450INSERT INTO t1 VALUES ('2011-10-30 03:00:01', 'Moscow'); 451SET @@session.time_zone = 'UTC'; 452INSERT INTO t2 SELECT * FROM t1; 453SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS 454WHERE TABLE_NAME = 't2'; 455PARTITION_NAME TABLE_ROWS 456p0 2 457p-2000 16 458p-2011-MSK 2 459p-2011-MSD-1 9 460p-2011-MSD-2 6 461p-2012-MSK-1 3 462p-2012-MSK-2 4 463pEnd 2 464pMax 2 465SELECT * FROM t1 ORDER BY a, tz; 466a tz 467NULL Moscow 468NULL UTC 4690000-00-00 00:00:00 Moscow 4700000-00-00 00:00:00 MoscowI 4710000-00-00 00:00:00 MoscowI 4720000-00-00 00:00:00 MoscowI 4730000-00-00 00:00:00 MoscowI 4740000-00-00 00:00:00 MoscowI 4750000-00-00 00:00:00 MoscowI 4760000-00-00 00:00:00 UTC 4770000-00-00 00:00:00 UTCI 4780000-00-00 00:00:00 UTCI 4790000-00-00 00:00:00 UTCI 4800000-00-00 00:00:00 UTCI 4811970-01-01 00:00:01 Moscow 4821970-01-01 00:00:01 UTC 4831974-02-05 18:28:16 Moscow 4841974-02-05 21:28:16 UTC 4852011-03-26 22:59:59 Moscow 4862011-03-26 22:59:59 UTC 4872011-03-26 23:00:00 Moscow 4882011-03-26 23:00:00 MoscowI 4892011-03-26 23:00:00 MoscowI 4902011-03-26 23:00:00 MoscowI 4912011-03-26 23:00:00 UTC 4922011-03-26 23:00:01 Moscow 4932011-03-26 23:00:01 UTC 4942011-10-29 21:59:59 Moscow 4952011-10-29 21:59:59 UTC 4962011-10-29 22:00:00 MoscowD 4972011-10-29 22:00:00 UTC 4982011-10-29 22:00:01 MoscowD 4992011-10-29 22:00:01 UTC 5002011-10-29 22:59:59 MoscowD 5012011-10-29 22:59:59 UTC 5022011-10-29 23:00:00 UTC 5032011-10-29 23:00:01 UTC 5042011-10-29 23:59:59 UTC 5052011-10-30 00:00:00 Moscow 5062011-10-30 00:00:00 UTC 5072011-10-30 00:00:01 Moscow 5082011-10-30 00:00:01 UTC 5092038-01-19 03:14:06 Moscow 5102038-01-19 03:14:06 UTC 5112038-01-19 03:14:07 Moscow 5122038-01-19 03:14:07 UTC 513SELECT * FROM t2 ORDER BY a, tz; 514a tz 515NULL Moscow 516NULL UTC 5170000-00-00 00:00:00 Moscow 5180000-00-00 00:00:00 MoscowI 5190000-00-00 00:00:00 MoscowI 5200000-00-00 00:00:00 MoscowI 5210000-00-00 00:00:00 MoscowI 5220000-00-00 00:00:00 MoscowI 5230000-00-00 00:00:00 MoscowI 5240000-00-00 00:00:00 UTC 5250000-00-00 00:00:00 UTCI 5260000-00-00 00:00:00 UTCI 5270000-00-00 00:00:00 UTCI 5280000-00-00 00:00:00 UTCI 5291970-01-01 00:00:01 Moscow 5301970-01-01 00:00:01 UTC 5311974-02-05 18:28:16 Moscow 5321974-02-05 21:28:16 UTC 5332011-03-26 22:59:59 Moscow 5342011-03-26 22:59:59 UTC 5352011-03-26 23:00:00 Moscow 5362011-03-26 23:00:00 MoscowI 5372011-03-26 23:00:00 MoscowI 5382011-03-26 23:00:00 MoscowI 5392011-03-26 23:00:00 UTC 5402011-03-26 23:00:01 Moscow 5412011-03-26 23:00:01 UTC 5422011-10-29 21:59:59 Moscow 5432011-10-29 21:59:59 UTC 5442011-10-29 22:00:00 MoscowD 5452011-10-29 22:00:00 UTC 5462011-10-29 22:00:01 MoscowD 5472011-10-29 22:00:01 UTC 5482011-10-29 22:59:59 MoscowD 5492011-10-29 22:59:59 UTC 5502011-10-29 23:00:00 UTC 5512011-10-29 23:00:01 UTC 5522011-10-29 23:59:59 UTC 5532011-10-30 00:00:00 Moscow 5542011-10-30 00:00:00 UTC 5552011-10-30 00:00:01 Moscow 5562011-10-30 00:00:01 UTC 5572038-01-19 03:14:06 Moscow 5582038-01-19 03:14:06 UTC 5592038-01-19 03:14:07 Moscow 5602038-01-19 03:14:07 UTC 561SELECT * FROM t2 562WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 23:00:00' ORDER BY a, tz; 563a tz 5642011-03-26 22:59:59 Moscow 5652011-03-26 22:59:59 UTC 5662011-03-26 23:00:00 Moscow 5672011-03-26 23:00:00 MoscowI 5682011-03-26 23:00:00 MoscowI 5692011-03-26 23:00:00 MoscowI 5702011-03-26 23:00:00 UTC 571EXPLAIN PARTITIONS 572SELECT * FROM t2 573WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 23:00:00' ORDER BY a, tz; 574id select_type table partitions type possible_keys key key_len ref rows Extra 5751 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort 576SELECT * FROM t2 577WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 22:59:59' ORDER BY a, tz; 578a tz 5792011-03-26 22:59:59 Moscow 5802011-03-26 22:59:59 UTC 581EXPLAIN PARTITIONS 582SELECT * FROM t2 583WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-26 22:59:59' ORDER BY a, tz; 584id select_type table partitions type possible_keys key key_len ref rows Extra 5851 SIMPLE t2 p-2011-MSK ALL NULL NULL NULL NULL 2 Using where; Using filesort 586SELECT * FROM t2 587WHERE a BETWEEN '2011-03-26 22:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; 588a tz 5892011-03-26 22:59:59 Moscow 5902011-03-26 22:59:59 UTC 5912011-03-26 23:00:00 Moscow 5922011-03-26 23:00:00 MoscowI 5932011-03-26 23:00:00 MoscowI 5942011-03-26 23:00:00 MoscowI 5952011-03-26 23:00:00 UTC 5962011-03-26 23:00:01 Moscow 5972011-03-26 23:00:01 UTC 598EXPLAIN PARTITIONS 599SELECT * FROM t2 600WHERE a BETWEEN '2011-03-26 22:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; 601id select_type table partitions type possible_keys key key_len ref rows Extra 6021 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort 603SELECT * FROM t2 604WHERE a BETWEEN '2011-03-26 23:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; 605a tz 6062011-03-26 23:00:00 Moscow 6072011-03-26 23:00:00 MoscowI 6082011-03-26 23:00:00 MoscowI 6092011-03-26 23:00:00 MoscowI 6102011-03-26 23:00:00 UTC 6112011-03-26 23:00:01 Moscow 6122011-03-26 23:00:01 UTC 613EXPLAIN PARTITIONS 614SELECT * FROM t2 615WHERE a BETWEEN '2011-03-26 23:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; 616id select_type table partitions type possible_keys key key_len ref rows Extra 6171 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort 618SELECT * FROM t2 619WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 23:00:00' ORDER BY a, tz; 620a tz 6212011-10-29 21:59:59 Moscow 6222011-10-29 21:59:59 UTC 6232011-10-29 22:00:00 MoscowD 6242011-10-29 22:00:00 UTC 6252011-10-29 22:00:01 MoscowD 6262011-10-29 22:00:01 UTC 6272011-10-29 22:59:59 MoscowD 6282011-10-29 22:59:59 UTC 6292011-10-29 23:00:00 UTC 630EXPLAIN PARTITIONS 631SELECT * FROM t2 632WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 23:00:00' ORDER BY a, tz; 633id select_type table partitions type possible_keys key key_len ref rows Extra 6341 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2,p-2012-MSK-1 ALL NULL NULL NULL NULL 18 Using where; Using filesort 635SELECT * FROM t2 636WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 22:59:59' ORDER BY a, tz; 637a tz 6382011-10-29 21:59:59 Moscow 6392011-10-29 21:59:59 UTC 6402011-10-29 22:00:00 MoscowD 6412011-10-29 22:00:00 UTC 6422011-10-29 22:00:01 MoscowD 6432011-10-29 22:00:01 UTC 6442011-10-29 22:59:59 MoscowD 6452011-10-29 22:59:59 UTC 646EXPLAIN PARTITIONS 647SELECT * FROM t2 648WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 22:59:59' ORDER BY a, tz; 649id select_type table partitions type possible_keys key key_len ref rows Extra 6501 SIMPLE t2 p-2011-MSD-1,p-2011-MSD-2 ALL NULL NULL NULL NULL 15 Using where; Using filesort 651SELECT * FROM t2 652WHERE a BETWEEN '2011-10-29 22:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; 653a tz 6542011-10-29 22:59:59 MoscowD 6552011-10-29 22:59:59 UTC 6562011-10-29 23:00:00 UTC 6572011-10-29 23:00:01 UTC 6582011-10-29 23:59:59 UTC 6592011-10-30 00:00:00 Moscow 6602011-10-30 00:00:00 UTC 6612011-10-30 00:00:01 Moscow 6622011-10-30 00:00:01 UTC 663EXPLAIN PARTITIONS 664SELECT * FROM t2 665WHERE a BETWEEN '2011-10-29 22:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; 666id select_type table partitions type possible_keys key key_len ref rows Extra 6671 SIMPLE t2 p-2011-MSD-2,p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 13 Using where; Using filesort 668SELECT * FROM t2 669WHERE a BETWEEN '2011-10-29 23:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; 670a tz 6712011-10-29 23:00:00 UTC 6722011-10-29 23:00:01 UTC 6732011-10-29 23:59:59 UTC 6742011-10-30 00:00:00 Moscow 6752011-10-30 00:00:00 UTC 6762011-10-30 00:00:01 Moscow 6772011-10-30 00:00:01 UTC 678EXPLAIN PARTITIONS 679SELECT * FROM t2 680WHERE a BETWEEN '2011-10-29 23:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; 681id select_type table partitions type possible_keys key key_len ref rows Extra 6821 SIMPLE t2 p-2012-MSK-1,p-2012-MSK-2 ALL NULL NULL NULL NULL 7 Using where; Using filesort 683# Test end range changes 684DELETE FROM t2 WHERE a = 0; 685INSERT IGNORE INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC'); 686Warnings: 687Warning 1264 Out of range value for column 'a' at row 1 688SELECT COUNT(*) FROM t2; 689COUNT(*) 69035 691SELECT COUNT(*) FROM t2 WHERE a = 0; 692COUNT(*) 6931 694SELECT * FROM t2 ORDER BY a, tz LIMIT 3; 695a tz 696NULL Moscow 697NULL UTC 6980000-00-00 00:00:00 UTC 699SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; 700a tz 7012038-01-19 03:14:07 Moscow 7022038-01-19 03:14:07 UTC 7032038-01-19 03:14:06 Moscow 704UPDATE IGNORE t2 SET a = TIMESTAMPADD(SECOND, 1, a); 705Warnings: 706Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 707Warning 1264 Out of range value for column 'a' at row 34 708Warning 1264 Out of range value for column 'a' at row 35 709SELECT MIN(a), MAX(a) FROM t2; 710MIN(a) MAX(a) 7110000-00-00 00:00:00 2038-01-19 03:14:07 712SELECT COUNT(*) FROM t2; 713COUNT(*) 71435 715SELECT COUNT(*) FROM t2 WHERE a = 0; 716COUNT(*) 7172 718SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS 719WHERE TABLE_NAME = 't2'; 720PARTITION_NAME TABLE_ROWS 721p0 3 722p-2000 6 723p-2011-MSK 0 724p-2011-MSD-1 9 725p-2011-MSD-2 6 726p-2012-MSK-1 4 727p-2012-MSK-2 5 728pEnd 0 729pMax 2 730SELECT * FROM t2 ORDER BY a, tz; 731a tz 732NULL Moscow 733NULL UTC 734NULL UTC 7350000-00-00 00:00:00 Moscow 7360000-00-00 00:00:00 UTC 7371970-01-01 00:00:02 Moscow 7381970-01-01 00:00:02 UTC 7391974-02-05 18:28:17 Moscow 7401974-02-05 21:28:17 UTC 7412011-03-26 23:00:00 Moscow 7422011-03-26 23:00:00 UTC 7432011-03-26 23:00:01 Moscow 7442011-03-26 23:00:01 MoscowI 7452011-03-26 23:00:01 MoscowI 7462011-03-26 23:00:01 MoscowI 7472011-03-26 23:00:01 UTC 7482011-03-26 23:00:02 Moscow 7492011-03-26 23:00:02 UTC 7502011-10-29 22:00:00 Moscow 7512011-10-29 22:00:00 UTC 7522011-10-29 22:00:01 MoscowD 7532011-10-29 22:00:01 UTC 7542011-10-29 22:00:02 MoscowD 7552011-10-29 22:00:02 UTC 7562011-10-29 23:00:00 MoscowD 7572011-10-29 23:00:00 UTC 7582011-10-29 23:00:01 UTC 7592011-10-29 23:00:02 UTC 7602011-10-30 00:00:00 UTC 7612011-10-30 00:00:01 Moscow 7622011-10-30 00:00:01 UTC 7632011-10-30 00:00:02 Moscow 7642011-10-30 00:00:02 UTC 7652038-01-19 03:14:07 Moscow 7662038-01-19 03:14:07 UTC 767# Test start range changes 768INSERT IGNORE INTO t2 VALUES ('1970-01-01 00:00:00', 'UTC'); 769Warnings: 770Warning 1264 Out of range value for column 'a' at row 1 771SELECT COUNT(*) FROM t2; 772COUNT(*) 77336 774SELECT COUNT(*) FROM t2 WHERE a = 0; 775COUNT(*) 7763 777SELECT * FROM t2 ORDER BY a, tz LIMIT 3; 778a tz 779NULL Moscow 780NULL UTC 781NULL UTC 782SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; 783a tz 7842038-01-19 03:14:07 Moscow 7852038-01-19 03:14:07 UTC 7862011-10-30 00:00:02 Moscow 787UPDATE IGNORE t2 SET a = TIMESTAMPADD(SECOND, -1, a); 788Warnings: 789Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 790Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 791Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 792SELECT MIN(a), MAX(a) FROM t2; 793MIN(a) MAX(a) 7941970-01-01 00:00:01 2038-01-19 03:14:06 795SELECT COUNT(*) FROM t2; 796COUNT(*) 79736 798SELECT COUNT(*) FROM t2 WHERE a = 0; 799COUNT(*) 8000 801SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS 802WHERE TABLE_NAME = 't2'; 803PARTITION_NAME TABLE_ROWS 804p0 6 805p-2000 4 806p-2011-MSK 2 807p-2011-MSD-1 9 808p-2011-MSD-2 6 809p-2012-MSK-1 3 810p-2012-MSK-2 4 811pEnd 2 812pMax 0 813SELECT * FROM t2 ORDER BY a, tz; 814a tz 815NULL Moscow 816NULL Moscow 817NULL UTC 818NULL UTC 819NULL UTC 820NULL UTC 8211970-01-01 00:00:01 Moscow 8221970-01-01 00:00:01 UTC 8231974-02-05 18:28:16 Moscow 8241974-02-05 21:28:16 UTC 8252011-03-26 22:59:59 Moscow 8262011-03-26 22:59:59 UTC 8272011-03-26 23:00:00 Moscow 8282011-03-26 23:00:00 MoscowI 8292011-03-26 23:00:00 MoscowI 8302011-03-26 23:00:00 MoscowI 8312011-03-26 23:00:00 UTC 8322011-03-26 23:00:01 Moscow 8332011-03-26 23:00:01 UTC 8342011-10-29 21:59:59 Moscow 8352011-10-29 21:59:59 UTC 8362011-10-29 22:00:00 MoscowD 8372011-10-29 22:00:00 UTC 8382011-10-29 22:00:01 MoscowD 8392011-10-29 22:00:01 UTC 8402011-10-29 22:59:59 MoscowD 8412011-10-29 22:59:59 UTC 8422011-10-29 23:00:00 UTC 8432011-10-29 23:00:01 UTC 8442011-10-29 23:59:59 UTC 8452011-10-30 00:00:00 Moscow 8462011-10-30 00:00:00 UTC 8472011-10-30 00:00:01 Moscow 8482011-10-30 00:00:01 UTC 8492038-01-19 03:14:06 Moscow 8502038-01-19 03:14:06 UTC 851SHOW CREATE TABLE t2; 852Table Create Table 853t2 CREATE TABLE `t2` ( 854 `a` timestamp NULL DEFAULT NULL, 855 `tz` varchar(16) DEFAULT NULL 856) ENGINE=MyISAM DEFAULT CHARSET=latin1 857 PARTITION BY RANGE (unix_timestamp(`a`)) 858(PARTITION `p0` VALUES LESS THAN (0) ENGINE = MyISAM, 859 PARTITION `p-2000` VALUES LESS THAN (946684800) ENGINE = MyISAM, 860 PARTITION `p-2011-MSK` VALUES LESS THAN (1301180400) ENGINE = MyISAM, 861 PARTITION `p-2011-MSD-1` VALUES LESS THAN (1319925600) ENGINE = MyISAM, 862 PARTITION `p-2011-MSD-2` VALUES LESS THAN (1319929200) ENGINE = MyISAM, 863 PARTITION `p-2012-MSK-1` VALUES LESS THAN (1319932800) ENGINE = MyISAM, 864 PARTITION `p-2012-MSK-2` VALUES LESS THAN (1332630000) ENGINE = MyISAM, 865 PARTITION `pEnd` VALUES LESS THAN (2147483647) ENGINE = MyISAM, 866 PARTITION `pMax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) 867TRUNCATE TABLE t2; 868SET @@session.time_zone = 'Europe/Moscow'; 869INSERT INTO t2 SELECT * FROM t1; 870SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS 871WHERE TABLE_NAME = 't2'; 872PARTITION_NAME TABLE_ROWS 873p0 2 874p-2000 16 875p-2011-MSK 2 876p-2011-MSD-1 9 877p-2011-MSD-2 6 878p-2012-MSK-1 3 879p-2012-MSK-2 4 880pEnd 2 881pMax 2 882SELECT * FROM t1 ORDER BY a, tz; 883a tz 884NULL Moscow 885NULL UTC 8860000-00-00 00:00:00 Moscow 8870000-00-00 00:00:00 MoscowI 8880000-00-00 00:00:00 MoscowI 8890000-00-00 00:00:00 MoscowI 8900000-00-00 00:00:00 MoscowI 8910000-00-00 00:00:00 MoscowI 8920000-00-00 00:00:00 MoscowI 8930000-00-00 00:00:00 UTC 8940000-00-00 00:00:00 UTCI 8950000-00-00 00:00:00 UTCI 8960000-00-00 00:00:00 UTCI 8970000-00-00 00:00:00 UTCI 8981970-01-01 03:00:01 Moscow 8991970-01-01 03:00:01 UTC 9001974-02-05 21:28:16 Moscow 9011974-02-06 00:28:16 UTC 9022011-03-27 01:59:59 Moscow 9032011-03-27 01:59:59 UTC 9042011-03-27 03:00:00 Moscow 9052011-03-27 03:00:00 MoscowI 9062011-03-27 03:00:00 MoscowI 9072011-03-27 03:00:00 MoscowI 9082011-03-27 03:00:00 UTC 9092011-03-27 03:00:01 Moscow 9102011-03-27 03:00:01 UTC 9112011-10-30 01:59:59 Moscow 9122011-10-30 01:59:59 UTC 9132011-10-30 02:00:00 MoscowD 9142011-10-30 02:00:00 UTC 9152011-10-30 02:00:01 MoscowD 9162011-10-30 02:00:01 UTC 9172011-10-30 02:59:59 MoscowD 9182011-10-30 02:59:59 UTC 9192011-10-30 02:00:00 UTC 9202011-10-30 02:00:01 UTC 9212011-10-30 02:59:59 UTC 9222011-10-30 03:00:00 Moscow 9232011-10-30 03:00:00 UTC 9242011-10-30 03:00:01 Moscow 9252011-10-30 03:00:01 UTC 9262038-01-19 06:14:06 Moscow 9272038-01-19 06:14:06 UTC 9282038-01-19 06:14:07 Moscow 9292038-01-19 06:14:07 UTC 930SELECT * FROM t2 ORDER BY a, tz; 931a tz 932NULL Moscow 933NULL UTC 9340000-00-00 00:00:00 Moscow 9350000-00-00 00:00:00 MoscowI 9360000-00-00 00:00:00 MoscowI 9370000-00-00 00:00:00 MoscowI 9380000-00-00 00:00:00 MoscowI 9390000-00-00 00:00:00 MoscowI 9400000-00-00 00:00:00 MoscowI 9410000-00-00 00:00:00 UTC 9420000-00-00 00:00:00 UTCI 9430000-00-00 00:00:00 UTCI 9440000-00-00 00:00:00 UTCI 9450000-00-00 00:00:00 UTCI 9461970-01-01 03:00:01 Moscow 9471970-01-01 03:00:01 UTC 9481974-02-05 21:28:16 Moscow 9491974-02-06 00:28:16 UTC 9502011-03-27 01:59:59 Moscow 9512011-03-27 01:59:59 UTC 9522011-03-27 03:00:00 Moscow 9532011-03-27 03:00:00 MoscowI 9542011-03-27 03:00:00 MoscowI 9552011-03-27 03:00:00 MoscowI 9562011-03-27 03:00:00 UTC 9572011-03-27 03:00:01 Moscow 9582011-03-27 03:00:01 UTC 9592011-10-30 01:59:59 Moscow 9602011-10-30 01:59:59 UTC 9612011-10-30 02:00:00 MoscowD 9622011-10-30 02:00:00 UTC 9632011-10-30 02:00:01 MoscowD 9642011-10-30 02:00:01 UTC 9652011-10-30 02:59:59 MoscowD 9662011-10-30 02:59:59 UTC 9672011-10-30 02:00:00 UTC 9682011-10-30 02:00:01 UTC 9692011-10-30 02:59:59 UTC 9702011-10-30 03:00:00 Moscow 9712011-10-30 03:00:00 UTC 9722011-10-30 03:00:01 Moscow 9732011-10-30 03:00:01 UTC 9742038-01-19 06:14:06 Moscow 9752038-01-19 06:14:06 UTC 9762038-01-19 06:14:07 Moscow 9772038-01-19 06:14:07 UTC 978# Testing the leap from 01:59:59 to 03:00:00 979SELECT * FROM t2 980WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 03:00:00' ORDER BY a, tz; 981a tz 9822011-03-27 01:59:59 Moscow 9832011-03-27 01:59:59 UTC 9842011-03-27 03:00:00 Moscow 9852011-03-27 03:00:00 MoscowI 9862011-03-27 03:00:00 MoscowI 9872011-03-27 03:00:00 MoscowI 9882011-03-27 03:00:00 UTC 989EXPLAIN PARTITIONS 990SELECT * FROM t2 991WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 03:00:00' ORDER BY a, tz; 992id select_type table partitions type possible_keys key key_len ref rows Extra 9931 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort 994SELECT * FROM t2 995WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 01:59:59' ORDER BY a, tz; 996a tz 9972011-03-27 01:59:59 Moscow 9982011-03-27 01:59:59 UTC 999EXPLAIN PARTITIONS 1000SELECT * FROM t2 1001WHERE a BETWEEN '2011-03-01 00:00:00' and '2011-03-27 01:59:59' ORDER BY a, tz; 1002id select_type table partitions type possible_keys key key_len ref rows Extra 10031 SIMPLE t2 p-2011-MSK ALL NULL NULL NULL NULL 2 Using where; Using filesort 1004SELECT * FROM t2 1005WHERE a BETWEEN '2011-03-26 01:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; 1006a tz 10072011-03-27 01:59:59 Moscow 10082011-03-27 01:59:59 UTC 10092011-03-27 03:00:00 Moscow 10102011-03-27 03:00:00 MoscowI 10112011-03-27 03:00:00 MoscowI 10122011-03-27 03:00:00 MoscowI 10132011-03-27 03:00:00 UTC 10142011-03-27 03:00:01 Moscow 10152011-03-27 03:00:01 UTC 1016EXPLAIN PARTITIONS 1017SELECT * FROM t2 1018WHERE a BETWEEN '2011-03-26 01:59:59' and '2011-03-28 00:00:00' ORDER BY a, tz; 1019id select_type table partitions type possible_keys key key_len ref rows Extra 10201 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort 1021SELECT * FROM t2 1022WHERE a BETWEEN '2011-03-26 03:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; 1023a tz 10242011-03-27 01:59:59 Moscow 10252011-03-27 01:59:59 UTC 10262011-03-27 03:00:00 Moscow 10272011-03-27 03:00:00 MoscowI 10282011-03-27 03:00:00 MoscowI 10292011-03-27 03:00:00 MoscowI 10302011-03-27 03:00:00 UTC 10312011-03-27 03:00:01 Moscow 10322011-03-27 03:00:01 UTC 1033EXPLAIN PARTITIONS 1034SELECT * FROM t2 1035WHERE a BETWEEN '2011-03-26 03:00:00' and '2011-03-28 00:00:00' ORDER BY a, tz; 1036id select_type table partitions type possible_keys key key_len ref rows Extra 10371 SIMPLE t2 p-2011-MSK,p-2011-MSD-1 ALL NULL NULL NULL NULL 11 Using where; Using filesort 1038# Testing the leap from 02:59:59 to 02:00:00 1039SELECT * FROM t2 1040WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:00:00' ORDER BY a, tz; 1041a tz 1042EXPLAIN PARTITIONS 1043SELECT * FROM t2 1044WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:00:00' ORDER BY a, tz; 1045id select_type table partitions type possible_keys key key_len ref rows Extra 10461 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort 1047SELECT * FROM t2 1048WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:59:59' ORDER BY a, tz; 1049a tz 1050EXPLAIN PARTITIONS 1051SELECT * FROM t2 1052WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 02:59:59' ORDER BY a, tz; 1053id select_type table partitions type possible_keys key key_len ref rows Extra 10541 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort 1055SELECT * FROM t2 1056WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 03:00:00' ORDER BY a, tz; 1057a tz 1058EXPLAIN PARTITIONS 1059SELECT * FROM t2 1060WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 03:00:00' ORDER BY a, tz; 1061id select_type table partitions type possible_keys key key_len ref rows Extra 10621 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort 1063SELECT * FROM t2 1064WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 01:59:59' ORDER BY a, tz; 1065a tz 1066EXPLAIN PARTITIONS 1067SELECT * FROM t2 1068WHERE a BETWEEN '2011-10-01 00:00:00' and '2011-10-29 01:59:59' ORDER BY a, tz; 1069id select_type table partitions type possible_keys key key_len ref rows Extra 10701 SIMPLE t2 p-2011-MSD-1 ALL NULL NULL NULL NULL 9 Using where; Using filesort 1071SELECT * FROM t2 1072WHERE a BETWEEN '2011-10-29 02:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; 1073a tz 10742011-10-30 01:59:59 Moscow 10752011-10-30 01:59:59 UTC 10762011-10-30 02:00:00 MoscowD 10772011-10-30 02:00:00 UTC 10782011-10-30 02:00:01 MoscowD 10792011-10-30 02:00:01 UTC 10802011-10-30 02:59:59 MoscowD 10812011-10-30 02:59:59 UTC 10822011-10-30 02:00:00 UTC 10832011-10-30 02:00:01 UTC 10842011-10-30 02:59:59 UTC 10852011-10-30 03:00:00 Moscow 10862011-10-30 03:00:00 UTC 10872011-10-30 03:00:01 Moscow 10882011-10-30 03:00:01 UTC 1089EXPLAIN PARTITIONS 1090SELECT * FROM t2 1091WHERE a BETWEEN '2011-10-29 02:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; 1092id select_type table partitions type possible_keys key key_len ref rows Extra 10931 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 1094SELECT * FROM t2 1095WHERE a BETWEEN '2011-10-29 02:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; 1096a tz 10972011-10-30 01:59:59 Moscow 10982011-10-30 01:59:59 UTC 10992011-10-30 02:00:00 MoscowD 11002011-10-30 02:00:00 UTC 11012011-10-30 02:00:01 MoscowD 11022011-10-30 02:00:01 UTC 11032011-10-30 02:59:59 MoscowD 11042011-10-30 02:59:59 UTC 11052011-10-30 02:00:00 UTC 11062011-10-30 02:00:01 UTC 11072011-10-30 02:59:59 UTC 11082011-10-30 03:00:00 Moscow 11092011-10-30 03:00:00 UTC 11102011-10-30 03:00:01 Moscow 11112011-10-30 03:00:01 UTC 1112EXPLAIN PARTITIONS 1113SELECT * FROM t2 1114WHERE a BETWEEN '2011-10-29 02:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; 1115id select_type table partitions type possible_keys key key_len ref rows Extra 11161 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 1117SELECT * FROM t2 1118WHERE a BETWEEN '2011-10-29 03:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; 1119a tz 11202011-10-30 01:59:59 Moscow 11212011-10-30 01:59:59 UTC 11222011-10-30 02:00:00 MoscowD 11232011-10-30 02:00:00 UTC 11242011-10-30 02:00:01 MoscowD 11252011-10-30 02:00:01 UTC 11262011-10-30 02:59:59 MoscowD 11272011-10-30 02:59:59 UTC 11282011-10-30 02:00:00 UTC 11292011-10-30 02:00:01 UTC 11302011-10-30 02:59:59 UTC 11312011-10-30 03:00:00 Moscow 11322011-10-30 03:00:00 UTC 11332011-10-30 03:00:01 Moscow 11342011-10-30 03:00:01 UTC 1135EXPLAIN PARTITIONS 1136SELECT * FROM t2 1137WHERE a BETWEEN '2011-10-29 03:00:00' and '2011-10-31 00:00:00' ORDER BY a, tz; 1138id select_type table partitions type possible_keys key key_len ref rows Extra 11391 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 1140SELECT * FROM t2 1141WHERE a BETWEEN '2011-10-29 01:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; 1142a tz 11432011-10-30 01:59:59 Moscow 11442011-10-30 01:59:59 UTC 11452011-10-30 02:00:00 MoscowD 11462011-10-30 02:00:00 UTC 11472011-10-30 02:00:01 MoscowD 11482011-10-30 02:00:01 UTC 11492011-10-30 02:59:59 MoscowD 11502011-10-30 02:59:59 UTC 11512011-10-30 02:00:00 UTC 11522011-10-30 02:00:01 UTC 11532011-10-30 02:59:59 UTC 11542011-10-30 03:00:00 Moscow 11552011-10-30 03:00:00 UTC 11562011-10-30 03:00:01 Moscow 11572011-10-30 03:00:01 UTC 1158EXPLAIN PARTITIONS 1159SELECT * FROM t2 1160WHERE a BETWEEN '2011-10-29 01:59:59' and '2011-10-31 00:00:00' ORDER BY a, tz; 1161id select_type table partitions type possible_keys key key_len ref rows Extra 11621 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 1163# Test end range changes 1164DELETE FROM t2 WHERE a = 0; 1165INSERT IGNORE INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow'); 1166Warnings: 1167Warning 1264 Out of range value for column 'a' at row 1 1168SELECT COUNT(*) FROM t2; 1169COUNT(*) 117035 1171SELECT COUNT(*) FROM t2 WHERE a = 0; 1172COUNT(*) 11731 1174SELECT * FROM t2 ORDER BY a, tz LIMIT 3; 1175a tz 1176NULL Moscow 1177NULL UTC 11780000-00-00 00:00:00 Moscow 1179SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; 1180a tz 11812038-01-19 06:14:07 Moscow 11822038-01-19 06:14:07 UTC 11832038-01-19 06:14:06 Moscow 1184UPDATE IGNORE t2 SET a = TIMESTAMPADD(SECOND, 1, a); 1185Warnings: 1186Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 1187Warning 1299 Invalid TIMESTAMP value in column 'a' at row 8 1188Warning 1299 Invalid TIMESTAMP value in column 'a' at row 9 1189Warning 1264 Out of range value for column 'a' at row 34 1190Warning 1264 Out of range value for column 'a' at row 35 1191SELECT MIN(a), MAX(a) FROM t2; 1192MIN(a) MAX(a) 11930000-00-00 00:00:00 2038-01-19 06:14:07 1194SELECT COUNT(*) FROM t2; 1195COUNT(*) 119635 1197SELECT COUNT(*) FROM t2 WHERE a = 0; 1198COUNT(*) 11992 1200SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS 1201WHERE TABLE_NAME = 't2'; 1202PARTITION_NAME TABLE_ROWS 1203p0 3 1204p-2000 6 1205p-2011-MSK 0 1206p-2011-MSD-1 9 1207p-2011-MSD-2 8 1208p-2012-MSK-1 0 1209p-2012-MSK-2 7 1210pEnd 0 1211pMax 2 1212SELECT * FROM t2 ORDER BY a, tz; 1213a tz 1214NULL Moscow 1215NULL Moscow 1216NULL UTC 12170000-00-00 00:00:00 Moscow 12180000-00-00 00:00:00 UTC 12191970-01-01 03:00:02 Moscow 12201970-01-01 03:00:02 UTC 12211974-02-05 21:28:17 Moscow 12221974-02-06 00:28:17 UTC 12232011-03-27 03:00:00 Moscow 12242011-03-27 03:00:00 UTC 12252011-03-27 03:00:01 Moscow 12262011-03-27 03:00:01 MoscowI 12272011-03-27 03:00:01 MoscowI 12282011-03-27 03:00:01 MoscowI 12292011-03-27 03:00:01 UTC 12302011-03-27 03:00:02 Moscow 12312011-03-27 03:00:02 UTC 12322011-10-30 02:00:00 Moscow 12332011-10-30 02:00:00 UTC 12342011-10-30 02:00:01 MoscowD 12352011-10-30 02:00:01 UTC 12362011-10-30 02:00:01 UTC 12372011-10-30 02:00:02 MoscowD 12382011-10-30 02:00:02 UTC 12392011-10-30 02:00:02 UTC 12402011-10-30 03:00:00 MoscowD 12412011-10-30 03:00:00 UTC 12422011-10-30 03:00:00 UTC 12432011-10-30 03:00:01 Moscow 12442011-10-30 03:00:01 UTC 12452011-10-30 03:00:02 Moscow 12462011-10-30 03:00:02 UTC 12472038-01-19 06:14:07 Moscow 12482038-01-19 06:14:07 UTC 1249# Test start range changes 1250INSERT IGNORE INTO t2 VALUES ('1970-01-01 00:00:00', 'Moscow'); 1251Warnings: 1252Warning 1264 Out of range value for column 'a' at row 1 1253SELECT COUNT(*) FROM t2; 1254COUNT(*) 125536 1256SELECT COUNT(*) FROM t2 WHERE a = 0; 1257COUNT(*) 12583 1259SELECT * FROM t2 ORDER BY a, tz LIMIT 3; 1260a tz 1261NULL Moscow 1262NULL Moscow 1263NULL UTC 1264SELECT * FROM t2 ORDER BY a DESC, tz LIMIT 3; 1265a tz 12662038-01-19 06:14:07 Moscow 12672038-01-19 06:14:07 UTC 12682011-10-30 03:00:02 Moscow 1269UPDATE IGNORE t2 SET a = TIMESTAMPADD(SECOND, -1, a); 1270Warnings: 1271Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 1272Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 1273Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' 1274Warning 1299 Invalid TIMESTAMP value in column 'a' at row 18 1275Warning 1299 Invalid TIMESTAMP value in column 'a' at row 19 1276SELECT MIN(a), MAX(a) FROM t2; 1277MIN(a) MAX(a) 12781970-01-01 03:00:01 2038-01-19 06:14:06 1279SELECT COUNT(*) FROM t2; 1280COUNT(*) 128136 1282SELECT COUNT(*) FROM t2 WHERE a = 0; 1283COUNT(*) 12840 1285SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS 1286WHERE TABLE_NAME = 't2'; 1287PARTITION_NAME TABLE_ROWS 1288p0 6 1289p-2000 4 1290p-2011-MSK 0 1291p-2011-MSD-1 11 1292p-2011-MSD-2 9 1293p-2012-MSK-1 0 1294p-2012-MSK-2 4 1295pEnd 2 1296pMax 0 1297SELECT * FROM t2 ORDER BY a, tz; 1298a tz 1299NULL Moscow 1300NULL Moscow 1301NULL Moscow 1302NULL Moscow 1303NULL UTC 1304NULL UTC 13051970-01-01 03:00:01 Moscow 13061970-01-01 03:00:01 UTC 13071974-02-05 21:28:16 Moscow 13081974-02-06 00:28:16 UTC 13092011-03-27 03:00:00 Moscow 13102011-03-27 03:00:00 Moscow 13112011-03-27 03:00:00 MoscowI 13122011-03-27 03:00:00 MoscowI 13132011-03-27 03:00:00 MoscowI 13142011-03-27 03:00:00 UTC 13152011-03-27 03:00:00 UTC 13162011-03-27 03:00:01 Moscow 13172011-03-27 03:00:01 UTC 13182011-10-30 01:59:59 Moscow 13192011-10-30 01:59:59 UTC 13202011-10-30 02:00:00 MoscowD 13212011-10-30 02:00:00 UTC 13222011-10-30 02:00:00 UTC 13232011-10-30 02:00:01 MoscowD 13242011-10-30 02:00:01 UTC 13252011-10-30 02:00:01 UTC 13262011-10-30 02:59:59 MoscowD 13272011-10-30 02:59:59 UTC 13282011-10-30 02:59:59 UTC 13292011-10-30 03:00:00 Moscow 13302011-10-30 03:00:00 UTC 13312011-10-30 03:00:01 Moscow 13322011-10-30 03:00:01 UTC 13332038-01-19 06:14:06 Moscow 13342038-01-19 06:14:06 UTC 1335SHOW CREATE TABLE t2; 1336Table Create Table 1337t2 CREATE TABLE `t2` ( 1338 `a` timestamp NULL DEFAULT NULL, 1339 `tz` varchar(16) DEFAULT NULL 1340) ENGINE=MyISAM DEFAULT CHARSET=latin1 1341 PARTITION BY RANGE (unix_timestamp(`a`)) 1342(PARTITION `p0` VALUES LESS THAN (0) ENGINE = MyISAM, 1343 PARTITION `p-2000` VALUES LESS THAN (946684800) ENGINE = MyISAM, 1344 PARTITION `p-2011-MSK` VALUES LESS THAN (1301180400) ENGINE = MyISAM, 1345 PARTITION `p-2011-MSD-1` VALUES LESS THAN (1319925600) ENGINE = MyISAM, 1346 PARTITION `p-2011-MSD-2` VALUES LESS THAN (1319929200) ENGINE = MyISAM, 1347 PARTITION `p-2012-MSK-1` VALUES LESS THAN (1319932800) ENGINE = MyISAM, 1348 PARTITION `p-2012-MSK-2` VALUES LESS THAN (1332630000) ENGINE = MyISAM, 1349 PARTITION `pEnd` VALUES LESS THAN (2147483647) ENGINE = MyISAM, 1350 PARTITION `pMax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM) 1351TRUNCATE TABLE t2; 1352DROP TABLE t1, t2; 1353SET @@session.time_zone= @old_time_zone; 1354