diff options
Diffstat (limited to 'mysql-test/main/partition_alter.result')
-rw-r--r-- | mysql-test/main/partition_alter.result | 129 |
1 files changed, 129 insertions, 0 deletions
diff --git a/mysql-test/main/partition_alter.result b/mysql-test/main/partition_alter.result new file mode 100644 index 00000000000..e42604ba056 --- /dev/null +++ b/mysql-test/main/partition_alter.result @@ -0,0 +1,129 @@ +CREATE TABLE `test_data` ( +`hid` bigint(20) unsigned NOT NULL, +`itid` bigint(20) unsigned NOT NULL, +`clocktime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, +`values` double(16,4) NOT NULL, +PRIMARY KEY (`hid`,`itid`,`clocktime`) +) ; +INSERT INTO `test_data` (`hid`, `itid`, `clocktime`, `values`) VALUES +(1, 1, '2015-03-10 06:25:16', 0.0000), +(1, 1, '2015-03-10 06:26:24', 0.0000), +(1, 1, '2015-03-10 06:27:32', 0.0000), +(1, 1, '2015-03-10 06:28:40', 0.0000), +(1, 1, '2015-03-10 06:29:49', 0.0000), +(1, 1, '2015-03-10 06:30:57', 0.0000), +(1, 1, '2015-03-10 06:32:05', 0.0000), +(1, 1, '2015-03-10 06:33:14', 0.0000), +(1, 1, '2015-03-10 06:34:22', 0.0000), +(1, 1, '2015-03-10 06:35:30', 0.0000), +(1, 1, '2015-03-10 06:36:39', 0.0000), +(1, 1, '2015-03-10 06:37:47', 0.0000), +(1, 1, '2015-03-10 06:38:55', 0.0000), +(1, 1, '2015-03-10 06:40:03', 0.0000), +(1, 1, '2015-03-10 06:41:09', 0.0000), +(1, 1, '2015-03-10 06:42:21', 0.0000), +(1, 1, '2015-03-10 06:43:29', 0.0000), +(1, 1, '2015-03-10 06:44:37', 0.0000), +(1, 1, '2015-03-10 06:45:46', 0.0000), +(1, 1, '2015-03-10 06:47:05', 0.0000), +(1, 1, '2015-03-10 06:48:21', 0.0000), +(1, 1, '2015-03-10 06:49:41', 0.0000), +(1, 1, '2015-03-10 06:50:58', 0.0000), +(1, 1, '2015-03-10 06:52:08', 0.0000), +(1, 1, '2015-03-10 06:53:17', 0.0000), +(1, 1, '2015-03-10 06:54:25', 0.0000), +(563, 1, '2015-03-17 14:28:28', 0.3125), +(563, 1, '2015-03-17 14:29:39', 0.2775), +(563, 1, '2015-03-17 14:30:49', 0.2675); +CREATE PROCEDURE `create_part_max`() +alter table `test_data` + partition by range(unix_timestamp(clocktime)) ( +partition partMAX values less than MAXVALUE +); +call create_part_max(); +call create_part_max(); +drop procedure create_part_max; +prepare stmt from "alter table `test_data` + partition by range(unix_timestamp(clocktime)) ( + partition partMAX values less than MAXVALUE + )"; +execute stmt; +execute stmt; +deallocate prepare stmt; +drop table test_data; +create table t1(id int, d date not null, b bool not null default 0, primary key(id,d)) +engine=innodb +partition by range columns (d) ( +partition p1 values less than ('2016-10-18'), +partition p2 values less than ('2020-10-19')); +insert t1 values (0, '2000-01-02', 0); +insert t1 values (1, '2020-01-02', 10); +alter table t1 add check (b in (0, 1)); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`#sql-temporary` +alter table t1 add check (b in (0, 10)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `d` date NOT NULL, + `b` tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`id`,`d`), + CONSTRAINT `CONSTRAINT_1` CHECK (`b` in (0,10)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY RANGE COLUMNS(`d`) +(PARTITION `p1` VALUES LESS THAN ('2016-10-18') ENGINE = InnoDB, + PARTITION `p2` VALUES LESS THAN ('2020-10-19') ENGINE = InnoDB) +insert t1 values (2, '2020-01-03', 20); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` +drop table t1; +create table t1(id int, d date not null, b bool not null default 0, primary key(id,d)) +partition by range columns (d) ( +partition p1 values less than ('2016-10-18'), +partition p2 values less than ('2020-10-19')); +insert t1 values (0, '2000-01-02', 0); +insert t1 values (1, '2020-01-02', 10); +alter table t1 add check (b in (0, 1)); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`#sql-temporary` +alter table t1 add check (b in (0, 10)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `d` date NOT NULL, + `b` tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`id`,`d`), + CONSTRAINT `CONSTRAINT_1` CHECK (`b` in (0,10)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY RANGE COLUMNS(`d`) +(PARTITION `p1` VALUES LESS THAN ('2016-10-18') ENGINE = MyISAM, + PARTITION `p2` VALUES LESS THAN ('2020-10-19') ENGINE = MyISAM) +insert t1 values (2, '2020-01-03', 20); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` +drop table t1; +create table t1 (id_1 int auto_increment, id_2 int, id_3 int, d1 date, dt1 datetime default current_timestamp, dt2 datetime default current_timestamp on update current_timestamp, primary key (id_2, id_3), key(id_1)) partition by hash(id_2) partitions 3 (partition p01, partition p02, partition p03); +insert into t1 values(0, 1, 1, NULL, now(), now()); +alter online table t1 delay_key_write=1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id_1` int(11) NOT NULL AUTO_INCREMENT, + `id_2` int(11) NOT NULL, + `id_3` int(11) NOT NULL, + `d1` date DEFAULT NULL, + `dt1` datetime DEFAULT current_timestamp(), + `dt2` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY (`id_2`,`id_3`), + KEY `id_1` (`id_1`) +) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 + PARTITION BY HASH (`id_2`) +(PARTITION `p01` ENGINE = MyISAM, + PARTITION `p02` ENGINE = MyISAM, + PARTITION `p03` ENGINE = MyISAM) +drop table t1; +create or replace table t1 (x int) partition by hash (x) (partition p1, partition p2); +lock table t1 write; +alter table t1 add partition (partition p1); +ERROR HY000: Duplicate partition name p1 +alter table t1 add partition (partition p1); +ERROR HY000: Duplicate partition name p1 +drop table t1; |