mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
393 lines
14 KiB
Text
393 lines
14 KiB
Text
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`.`t1`
|
|
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 COLLATE=latin1_swedish_ci
|
|
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;
|
|
db.opt
|
|
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`.`t1`
|
|
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 COLLATE=latin1_swedish_ci
|
|
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;
|
|
db.opt
|
|
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 COLLATE=latin1_swedish_ci DELAY_KEY_WRITE=1
|
|
PARTITION BY HASH (`id_2`)
|
|
(PARTITION `p01` ENGINE = MyISAM,
|
|
PARTITION `p02` ENGINE = MyISAM,
|
|
PARTITION `p03` ENGINE = MyISAM)
|
|
drop table t1;
|
|
#
|
|
# MDEV-19751 Wrong partitioning by KEY() after key dropped
|
|
#
|
|
create or replace table t1 (pk int, x timestamp(6), primary key (pk, x)) engine innodb
|
|
partition by key() partitions 2;
|
|
insert into t1 (pk, x) values (1, '2000-01-01 00:00'), (2, '2000-01-01 00:01');
|
|
# Inplace for DROP PRIMARY KEY when partitioned by default field list is denied
|
|
alter table t1 drop primary key, drop column x, add primary key (pk), algorithm=inplace;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
|
|
alter table t1 drop primary key, drop column x, add primary key (pk);
|
|
select * from t1 partition (p0);
|
|
pk
|
|
1
|
|
drop table t1;
|
|
create or replace table t1 (pk int not null, x timestamp(6) not null, unique u(pk, x)) engine innodb
|
|
partition by key() partitions 2;
|
|
insert into t1 (pk, x) values (1, '2000-01-01 00:00'), (2, '2000-01-01 00:01');
|
|
# Same for NOT NULL UNIQUE KEY as this is actually primary key
|
|
alter table t1 drop key u, drop column x, add unique (pk), algorithm=inplace;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
|
|
alter table t1 drop key u, drop column x, add unique (pk);
|
|
select * from t1 partition (p0);
|
|
pk
|
|
1
|
|
drop table t1;
|
|
create or replace table t1 (pk int, x timestamp(6) not null, primary key (pk)) engine innodb
|
|
partition by key(pk) partitions 2;
|
|
insert into t1 (pk, x) values (1, '2000-01-01 00:00'), (2, '2000-01-01 00:01');
|
|
# Inplace for DROP PRIMARY KEY when partitioned by explicit field list is allowed
|
|
alter table t1 drop primary key, add primary key (pk, x), algorithm=inplace;
|
|
select * from t1 partition (p0);
|
|
pk x
|
|
1 2000-01-01 00:00:00.000000
|
|
drop table t1;
|
|
create or replace table t1 (k int, x timestamp(6), unique key u (x, k)) engine innodb
|
|
partition by key(k) partitions 2;
|
|
insert into t1 (k, x) values (1, '2000-01-01 00:00'), (2, '2000-01-01 00:01');
|
|
# Inplace for DROP KEY is allowed
|
|
alter table t1 drop key u, algorithm=inplace;
|
|
select * from t1 partition (p0);
|
|
k x
|
|
1 2000-01-01 00:00:00.000000
|
|
drop table t1;
|
|
# End of 10.2 tests
|
|
#
|
|
# MDEV-14817 Server crashes in prep_alter_part_table()
|
|
# after table lock and multiple add partition
|
|
#
|
|
create 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;
|
|
#
|
|
# MDEV-17091 Assertion `old_part_id == m_last_part' failed in
|
|
# ha_partition::update_row or `part_id == m_last_part' in
|
|
# ha_partition::delete_row upon UPDATE/DELETE after dropping versioning
|
|
#
|
|
create table t1 (pk int, f int, primary key(pk, f)) engine=innodb
|
|
partition by key() partitions 2;
|
|
insert into t1 values (1,10),(2,11);
|
|
# expected to hit same partition
|
|
select * from t1 partition (p0);
|
|
pk f
|
|
1 10
|
|
2 11
|
|
alter table t1 drop primary key, drop f, add primary key(pk);
|
|
# 1 and 2 are expected to be in different partitions
|
|
select * from t1 partition(p0);
|
|
pk
|
|
1
|
|
select * from t1 partition(p1);
|
|
pk
|
|
2
|
|
delete from t1;
|
|
drop table t1;
|
|
#
|
|
# MDEV-28576 RENAME COLUMN with NOCOPY algorithm leads to corrupt partitioned table
|
|
#
|
|
create table t (a int, b int) partition by list (b) (partition p1 values in (1, 2));
|
|
insert into t values (0, 1), (2, 2);
|
|
alter table t change b f int, change a b int, algorithm=nocopy;
|
|
check table t;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
delete from t order by b limit 1;
|
|
drop table t;
|
|
#
|
|
# MDEV-30112 ASAN errors in Item_ident::print / generate_partition_syntax
|
|
#
|
|
create table t (a int) partition by hash(a);
|
|
alter table t change a b int, drop a;
|
|
ERROR 42S22: Unknown column 'a' in 't'
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY HASH (`a`)
|
|
drop table t;
|
|
# End of 10.3 tests
|
|
#
|
|
# Start of 10.4 tests
|
|
#
|
|
#
|
|
# MDEV-28545 MyISAM reorganize partition corrupt older table format
|
|
#
|
|
SET GLOBAL mysql56_temporal_format=OFF;
|
|
CREATE TABLE t (ts timestamp, KEY (ts)) ENGINE=MyISAM
|
|
PARTITION BY RANGE (unix_timestamp(ts)) (
|
|
PARTITION p1 VALUES LESS THAN (1645398000),
|
|
PARTITION pn VALUES LESS THAN MAXVALUE
|
|
);
|
|
SET GLOBAL mysql56_temporal_format=ON;
|
|
FLUSH TABLES;
|
|
ALTER TABLE t DROP PARTITION p1;
|
|
CHECK TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
DROP TABLE t;
|
|
SET GLOBAL mysql56_temporal_format=DEFAULT;
|
|
#
|
|
# End of 10.4 tests
|
|
#
|
|
#
|
|
# MDEV-28576 RENAME COLUMN with NOCOPY algorithm leads to corrupt partitioned table
|
|
#
|
|
create table t (a int)
|
|
partition by list (a)
|
|
subpartition by hash(a) subpartitions 2
|
|
(partition p0 values in (1));
|
|
alter table t rename column a to b, algorithm=nocopy;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`b` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY LIST (`b`)
|
|
SUBPARTITION BY HASH (`b`)
|
|
SUBPARTITIONS 2
|
|
(PARTITION `p0` VALUES IN (1) ENGINE = MyISAM)
|
|
alter table t rename column b to c, algorithm=copy;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`c` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY LIST (`c`)
|
|
SUBPARTITION BY HASH (`c`)
|
|
SUBPARTITIONS 2
|
|
(PARTITION `p0` VALUES IN (1) ENGINE = MyISAM)
|
|
drop table t;
|
|
create table t (d int, e int)
|
|
partition by list columns (d, e)
|
|
subpartition by key (d, e)
|
|
(partition p0 values in ((2, 3)));
|
|
alter table t rename column d to f, rename column e to g, algorithm=nocopy;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`f` int(11) DEFAULT NULL,
|
|
`g` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY LIST COLUMNS(`f`,`g`)
|
|
SUBPARTITION BY KEY (`f`,`g`)
|
|
(PARTITION `p0` VALUES IN ((2,3)) ENGINE = MyISAM)
|
|
alter table t rename column f to h, rename column g to i, algorithm=copy;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`h` int(11) DEFAULT NULL,
|
|
`i` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY LIST COLUMNS(`h`,`i`)
|
|
SUBPARTITION BY KEY (`h`,`i`)
|
|
(PARTITION `p0` VALUES IN ((2,3)) ENGINE = MyISAM)
|
|
drop table t;
|
|
create table t (k int, l int)
|
|
partition by range (k)
|
|
subpartition by hash(l) subpartitions 4
|
|
(partition p0 values less than (5));
|
|
alter table t rename column k to l, rename column l to k;
|
|
show create table t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`l` int(11) DEFAULT NULL,
|
|
`k` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY RANGE (`l`)
|
|
SUBPARTITION BY HASH (`k`)
|
|
SUBPARTITIONS 4
|
|
(PARTITION `p0` VALUES LESS THAN (5) ENGINE = MyISAM)
|
|
drop table t;
|
|
create table t (a int, b int) partition by list (b) (partition p1 values in (1, 2));
|
|
insert into t values (0, 1), (2, 2);
|
|
alter table t rename column b to f, rename column a to b, algorithm=nocopy;
|
|
check table t;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
delete from t order by b limit 1;
|
|
drop table t;
|
|
# End of 10.5 tests
|
|
create or replace table t1 (x int primary key)
|
|
partition by range(x) (
|
|
p1 values less than (10),
|
|
partition p2 values less than (20),
|
|
p3 values less than (30),
|
|
partition p4 values less than (40),
|
|
p5 values less than (50),
|
|
pn values less than maxvalue);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`x` int(11) NOT NULL,
|
|
PRIMARY KEY (`x`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY RANGE (`x`)
|
|
(PARTITION `p1` VALUES LESS THAN (10) ENGINE = MyISAM,
|
|
PARTITION `p2` VALUES LESS THAN (20) ENGINE = MyISAM,
|
|
PARTITION `p3` VALUES LESS THAN (30) ENGINE = MyISAM,
|
|
PARTITION `p4` VALUES LESS THAN (40) ENGINE = MyISAM,
|
|
PARTITION `p5` VALUES LESS THAN (50) ENGINE = MyISAM,
|
|
PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
|
|
create or replace table t1 (x int)
|
|
partition by list(x) (
|
|
partition p1 values in (2, 3, 4),
|
|
partition p2 values in (12, 13, 14),
|
|
partition p3 values in (22, 23, 24),
|
|
p4 values in (32, 33, 34),
|
|
p5 values in (42, 43, 44),
|
|
pn values in (52, 53, 54));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`x` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
PARTITION BY LIST (`x`)
|
|
(PARTITION `p1` VALUES IN (2,3,4) ENGINE = MyISAM,
|
|
PARTITION `p2` VALUES IN (12,13,14) ENGINE = MyISAM,
|
|
PARTITION `p3` VALUES IN (22,23,24) ENGINE = MyISAM,
|
|
PARTITION `p4` VALUES IN (32,33,34) ENGINE = MyISAM,
|
|
PARTITION `p5` VALUES IN (42,43,44) ENGINE = MyISAM,
|
|
PARTITION `pn` VALUES IN (52,53,54) ENGINE = MyISAM)
|
|
create or replace table t1 (x int)
|
|
partition by list(x) (
|
|
partition partition p1 values in (2, 3, 4),
|
|
pn values in (52, 53, 54));
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'partition p1 values in (2, 3, 4),
|
|
pn values in (52, 53, 54))' at line 3
|
|
create or replace table t1 (x int)
|
|
partition by list(x) (
|
|
partition partition values in (2, 3, 4),
|
|
pn values in (52, 53, 54));
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'partition values in (2, 3, 4),
|
|
pn values in (52, 53, 54))' at line 3
|
|
create or replace table t1 (x int)
|
|
partition by list(x) (
|
|
partition values in (2, 3, 4),
|
|
pn values in (52, 53, 54));
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'in (2, 3, 4),
|
|
pn values in (52, 53, 54))' at line 3
|
|
create or replace table t1 (x int)
|
|
partition by list(x) (
|
|
partitio values in (2, 3, 4),
|
|
pn values in (52, 53, 54));
|
|
drop table t1;
|
|
# End of 10.7 tests
|