614 lines
21 KiB
Plaintext
614 lines
21 KiB
Plaintext
--echo # TestWriteListPartitionTable2
|
|
--echo # test for write list partition when the partition expression is complicated and contain generated column.
|
|
drop table if exists t;
|
|
create table t (id int, name varchar(10),b int generated always as (length(name)+1) virtual)
|
|
partition by list (id*2 + b*b + b*b - b*b*2 - abs(id)) (
|
|
partition p0 values in (3,5,6,9,17),
|
|
partition p1 values in (1,2,10,11,19,20),
|
|
partition p2 values in (4,12,13,14,18),
|
|
partition p3 values in (7,8,15,16,27,null)
|
|
);
|
|
|
|
analyze table t;
|
|
|
|
--echo ## Test add unique index failed.
|
|
insert into t (id,name) values (1, 'a'),(1,'b');
|
|
--error 1062
|
|
alter table t add unique index idx (id,b);
|
|
--echo ## Test add unique index success.
|
|
delete from t where name='b';
|
|
alter table t add unique index idx (id,b);
|
|
|
|
--echo ## --------------------------Test insert---------------------------
|
|
--echo ## Test insert 1 partition.
|
|
delete from t;
|
|
insert into t (id,name) values (1, 'a'),(2,'b'),(10,'c');
|
|
select id,name from t partition(p1) order by id;
|
|
--echo ## Test insert multi-partitions.
|
|
delete from t;
|
|
insert into t (id,name) values (1, 'a'),(3,'c'),(4,'e');
|
|
select id,name from t partition(p0) order by id;
|
|
select id,name from t partition(p1) order by id;
|
|
select id,name from t partition(p2) order by id;
|
|
select id,name from t partition(p3) order by id;
|
|
--echo ## Test insert on duplicate.
|
|
insert into t (id,name) values (1, 'd'), (3,'f'),(5,'g') on duplicate key update name='x';
|
|
select id,name from t partition(p0) order by id;
|
|
select id,name from t partition(p1) order by id;
|
|
select id,name from t partition(p2) order by id;
|
|
select id,name from t partition(p3) order by id;
|
|
--echo ## Test insert on duplicate error
|
|
--error 1062
|
|
insert into t (id,name) values (3, 'a'), (11,'x') on duplicate key update id=id+1;
|
|
select id,name from t order by id;
|
|
--echo ## Test insert ignore with duplicate
|
|
insert ignore into t (id,name) values (1, 'b'), (5,'a'),(null,'y');
|
|
show warnings;
|
|
select id,name from t partition(p0) order by id;
|
|
select id,name from t partition(p1) order by id;
|
|
select id,name from t partition(p2) order by id;
|
|
select id,name from t partition(p3) order by id;
|
|
--echo ## Test insert ignore without duplicate
|
|
insert ignore into t (id,name) values (15, 'a'),(17,'a');
|
|
select id,name from t partition(p0,p1,p2) order by id;
|
|
select id,name from t partition(p3) order by id;
|
|
--echo ## Test insert meet no partition error.
|
|
--error 1526
|
|
insert into t (id,name) values (100, 'd');
|
|
|
|
--echo ## --------------------------Test update---------------------------
|
|
--echo ## Test update 1 partition.
|
|
delete from t;
|
|
insert into t (id,name) values (1, 'a'),(2,'b'),(3,'c');
|
|
update t set name='b' where id=2;;
|
|
select id,name from t partition(p1);
|
|
update t set name='x' where id in (1,2);
|
|
select id,name from t partition(p1);
|
|
update t set name='y' where id < 3;
|
|
select id,name from t order by id;
|
|
--echo ## Test update meet duplicate error.
|
|
--error 1062
|
|
update t set id=2 where id = 1;
|
|
select id,name from t order by id;
|
|
|
|
--echo ## Test update multi-partitions
|
|
update t set name='z' where id in (1,2,3);;
|
|
select id,name from t order by id;
|
|
update t set name='a' limit 3;
|
|
select id,name from t order by id;
|
|
update t set id=id*10 where id in (1,2);
|
|
select id,name from t order by id;
|
|
--echo ## Test update meet duplicate error.
|
|
--error 1062
|
|
update t set id=id+17 where id in (3,10);
|
|
select id,name from t order by id;
|
|
--echo ## Test update meet no partition error.
|
|
--error 1526
|
|
update t set id=id*2 where id in (3,20);
|
|
select id,name from t order by id;
|
|
|
|
--echo ## --------------------------Test replace---------------------------
|
|
--echo ## Test replace 1 partition.
|
|
delete from t;
|
|
replace into t (id,name) values (1, 'a'),(2,'b');
|
|
select id,name from t order by id;
|
|
--echo ## Test replace multi-partitions.
|
|
replace into t (id,name) values (3, 'c'),(4,'d'),(7,'f');
|
|
select id,name from t partition(p0) order by id;
|
|
select id,name from t partition(p1) order by id;
|
|
select id,name from t partition(p2) order by id;
|
|
select id,name from t partition(p3) order by id;
|
|
--echo ## Test replace on duplicate.
|
|
replace into t (id,name) values (1, 'x'),(7,'x');
|
|
select id,name from t order by id;
|
|
--echo ## Test replace meet no partition error.
|
|
--error 1526
|
|
replace into t (id,name) values (10,'x'),(50,'x');
|
|
select id,name from t order by id;
|
|
|
|
--echo ## --------------------------Test delete---------------------------
|
|
--echo ## Test delete 1 partition.
|
|
delete from t where id = 3;
|
|
select id,name from t partition(p0) order by id;
|
|
delete from t where id in (1,2);
|
|
select id,name from t partition(p1) order by id;
|
|
--echo ## Test delete multi-partitions.
|
|
delete from t where id in (4,7,10,11);
|
|
select id,name from t;
|
|
insert into t (id,name) values (3, 'c'),(4,'d'),(7,'f');
|
|
delete from t where id < 10;
|
|
select id,name from t;
|
|
insert into t (id,name) values (3, 'c'),(4,'d'),(7,'f');
|
|
delete from t limit 3;
|
|
select id,name from t;
|
|
|
|
--echo # TestWriteListColumnsPartitionTable1
|
|
|
|
drop table if exists t;
|
|
create table t (id int, name varchar(10)) partition by list columns (id) (
|
|
partition p0 values in (3,5,6,9,17),
|
|
partition p1 values in (1,2,10,11,19,20),
|
|
partition p2 values in (4,12,13,14,18),
|
|
partition p3 values in (7,8,15,16,null)
|
|
);
|
|
|
|
analyze table t;
|
|
|
|
--echo ## Test add unique index failed.
|
|
insert into t values (1, 'a'),(1,'b');
|
|
--error 1062
|
|
alter table t add unique index idx (id);
|
|
--echo ## Test add unique index success.
|
|
delete from t where name='b';
|
|
alter table t add unique index idx (id);
|
|
|
|
--echo ## --------------------------Test insert---------------------------
|
|
--echo ## Test insert 1 partition.
|
|
delete from t;
|
|
insert into t values (1, 'a'),(2,'b'),(10,'c');
|
|
select * from t partition(p1) order by id;
|
|
--echo ## Test insert multi-partitions.
|
|
delete from t;
|
|
insert into t values (1, 'a'),(3,'c'),(4,'e');
|
|
select * from t partition(p0) order by id;
|
|
select * from t partition(p1) order by id;
|
|
select * from t partition(p2) order by id;
|
|
select * from t partition(p3) order by id;
|
|
--echo ## Test insert on duplicate.
|
|
insert into t values (1, 'd'), (3,'f'),(5,'g') on duplicate key update name='x';
|
|
select * from t partition(p0) order by id;
|
|
select * from t partition(p1) order by id;
|
|
select * from t partition(p2) order by id;
|
|
select * from t partition(p3) order by id;
|
|
--echo ## Test insert on duplicate error
|
|
--error 1062
|
|
insert into t values (3, 'a'), (11,'x') on duplicate key update id=id+1;
|
|
select * from t order by id;
|
|
--echo ## Test insert ignore with duplicate
|
|
insert ignore into t values (1, 'b'), (5,'a'),(null,'y');
|
|
show warnings;
|
|
select * from t partition(p0) order by id;
|
|
select * from t partition(p1) order by id;
|
|
select * from t partition(p2) order by id;
|
|
select * from t partition(p3) order by id;
|
|
--echo ## Test insert ignore without duplicate
|
|
insert ignore into t values (15, 'a'),(17,'a');
|
|
select * from t partition(p0,p1,p2) order by id;
|
|
select * from t partition(p3) order by id;
|
|
--echo ## Test insert meet no partition error.
|
|
--error 1526
|
|
insert into t values (100, 'd');
|
|
|
|
--echo ## --------------------------Test update---------------------------
|
|
--echo ## Test update 1 partition.
|
|
delete from t;
|
|
insert into t values (1, 'a'),(2,'b'),(3,'c');
|
|
update t set name='b' where id=2;;
|
|
select * from t partition(p1);
|
|
update t set name='x' where id in (1,2);
|
|
select * from t partition(p1);
|
|
update t set name='y' where id < 3;
|
|
select * from t order by id;
|
|
--echo ## Test update meet duplicate error.
|
|
--error 1062
|
|
update t set id=2 where id = 1;
|
|
select * from t order by id;
|
|
|
|
--echo ## Test update multi-partitions
|
|
update t set name='z' where id in (1,2,3);;
|
|
select * from t order by id;
|
|
update t set name='a' limit 3;
|
|
select * from t order by id;
|
|
update t set id=id*10 where id in (1,2);
|
|
select * from t order by id;
|
|
--echo ## Test update meet duplicate error.
|
|
--error 1062
|
|
update t set id=id+17 where id in (3,10);
|
|
select * from t order by id;
|
|
--echo ## Test update meet no partition error.
|
|
--error 1526
|
|
update t set id=id*2 where id in (3,20);
|
|
select * from t order by id;
|
|
|
|
--echo ## --------------------------Test replace---------------------------
|
|
--echo ## Test replace 1 partition.
|
|
delete from t;
|
|
replace into t values (1, 'a'),(2,'b');
|
|
select * from t order by id;
|
|
--echo ## Test replace multi-partitions.
|
|
replace into t values (3, 'c'),(4,'d'),(7,'f');
|
|
select * from t partition(p0) order by id;
|
|
select * from t partition(p1) order by id;
|
|
select * from t partition(p2) order by id;
|
|
select * from t partition(p3) order by id;
|
|
--echo ## Test replace on duplicate.
|
|
replace into t values (1, 'x'),(7,'x');
|
|
select * from t order by id;
|
|
--echo ## Test replace meet no partition error.
|
|
--error 1526
|
|
replace into t values (10,'x'),(100,'x');
|
|
select * from t order by id;
|
|
|
|
--echo ## --------------------------Test delete---------------------------
|
|
--echo ## Test delete 1 partition.
|
|
delete from t where id = 3;
|
|
select * from t partition(p0) order by id;
|
|
delete from t where id in (1,2);
|
|
select * from t partition(p1) order by id;
|
|
--echo ## Test delete multi-partitions.
|
|
delete from t where id in (4,7,10,11);
|
|
select * from t;
|
|
insert into t values (3, 'c'),(4,'d'),(7,'f');
|
|
delete from t where id < 10;
|
|
select * from t;
|
|
insert into t values (3, 'c'),(4,'d'),(7,'f');
|
|
delete from t limit 3;
|
|
select * from t;
|
|
|
|
|
|
# TestPartitionedTableReplace
|
|
set tidb_opt_fix_control = "44262:ON";
|
|
drop table if exists replace_test;
|
|
create table replace_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1)
|
|
partition by range (id) (
|
|
PARTITION p0 VALUES LESS THAN (3),
|
|
PARTITION p1 VALUES LESS THAN (5),
|
|
PARTITION p2 VALUES LESS THAN (7),
|
|
PARTITION p3 VALUES LESS THAN (9));
|
|
--enable_info
|
|
replace replace_test (c1) values (1),(2),(NULL);
|
|
--disable_info
|
|
begin;
|
|
-- error 1136
|
|
replace replace_test (c1) values ();
|
|
rollback;
|
|
begin;
|
|
-- error 1136
|
|
replace replace_test (c1, c2) values (1,2),(1);
|
|
rollback;
|
|
begin;
|
|
-- error 1054
|
|
replace replace_test (xxx) values (3);
|
|
rollback;
|
|
begin;
|
|
-- error 1146
|
|
replace replace_test_xxx (c1) values ();
|
|
rollback;
|
|
--enable_info
|
|
replace replace_test set c1 = 3;
|
|
--disable_info
|
|
begin;
|
|
-- error 1110
|
|
replace replace_test set c1 = 4, c1 = 5;
|
|
rollback;
|
|
begin;
|
|
-- error 1054
|
|
replace replace_test set xxx = 6;
|
|
rollback;
|
|
|
|
drop table if exists replace_test_1;
|
|
create table replace_test_1 (id int, c1 int) partition by range (id) (
|
|
PARTITION p0 VALUES LESS THAN (4),
|
|
PARTITION p1 VALUES LESS THAN (6),
|
|
PARTITION p2 VALUES LESS THAN (8),
|
|
PARTITION p3 VALUES LESS THAN (10),
|
|
PARTITION p4 VALUES LESS THAN (100));
|
|
--enable_info
|
|
replace replace_test_1 select id, c1 from replace_test;
|
|
--disable_info
|
|
|
|
drop table if exists replace_test_2;
|
|
create table replace_test_2 (id int, c1 int) partition by range (id) (
|
|
PARTITION p0 VALUES LESS THAN (10),
|
|
PARTITION p1 VALUES LESS THAN (50),
|
|
PARTITION p2 VALUES LESS THAN (100),
|
|
PARTITION p3 VALUES LESS THAN (300));
|
|
--enable_info
|
|
replace replace_test_2 select id, c1 from replace_test union select id * 10, c1 * 10 from replace_test;
|
|
--disable_info
|
|
begin;
|
|
-- error 1136
|
|
replace replace_test_2 select c1 from replace_test;
|
|
rollback;
|
|
|
|
drop table if exists replace_test_3;
|
|
create table replace_test_3 (c1 int, c2 int, UNIQUE INDEX (c2)) partition by range (c2) (
|
|
PARTITION p0 VALUES LESS THAN (4),
|
|
PARTITION p1 VALUES LESS THAN (7),
|
|
PARTITION p2 VALUES LESS THAN (11));
|
|
--enable_info
|
|
replace into replace_test_3 set c2=8;
|
|
replace into replace_test_3 set c2=8;
|
|
replace into replace_test_3 set c1=8, c2=8;
|
|
replace into replace_test_3 set c2=NULL;
|
|
replace into replace_test_3 set c2=NULL;
|
|
--disable_info
|
|
|
|
drop table if exists replace_test_4;
|
|
create table replace_test_4 (c1 int, c2 int, c3 int, UNIQUE INDEX (c1, c2)) partition by range (c1) (
|
|
PARTITION p0 VALUES LESS THAN (4),
|
|
PARTITION p1 VALUES LESS THAN (7),
|
|
PARTITION p2 VALUES LESS THAN (11));
|
|
--enable_info
|
|
replace into replace_test_4 set c2=NULL;
|
|
replace into replace_test_4 set c2=NULL;
|
|
--disable_info
|
|
|
|
drop table if exists replace_test_5;
|
|
create table replace_test_5 (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2)) partition by range (c2) (
|
|
PARTITION p0 VALUES LESS THAN (4),
|
|
PARTITION p1 VALUES LESS THAN (7),
|
|
PARTITION p2 VALUES LESS THAN (11));
|
|
--enable_info
|
|
replace into replace_test_5 set c1=1, c2=2;
|
|
replace into replace_test_5 set c1=1, c2=2;
|
|
--disable_info
|
|
|
|
drop table if exists tIssue989;
|
|
CREATE TABLE tIssue989 (a int, b int, KEY(a), UNIQUE KEY(b)) partition by range (b) (
|
|
PARTITION p1 VALUES LESS THAN (100),
|
|
PARTITION p2 VALUES LESS THAN (200));
|
|
--enable_info
|
|
insert into tIssue989 (a, b) values (1, 2);
|
|
replace into tIssue989(a, b) values (111, 2);
|
|
--disable_info
|
|
select * from tIssue989;
|
|
|
|
set tidb_opt_fix_control = default;
|
|
|
|
|
|
# TestPartitionedTableUpdate
|
|
set tidb_opt_fix_control = "44262:ON";
|
|
drop table if exists t;
|
|
create table t (id int not null default 1, name varchar(255))
|
|
PARTITION BY RANGE ( id ) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11),
|
|
PARTITION p2 VALUES LESS THAN (16),
|
|
PARTITION p3 VALUES LESS THAN (21));
|
|
insert INTO t VALUES (1, "hello");
|
|
insert INTO t VALUES (7, "hello");
|
|
|
|
--echo ## update non partition column
|
|
--enable_info
|
|
UPDATE t SET name = "abc" where id > 0;
|
|
--disable_info
|
|
SELECT * from t order by id limit 2;
|
|
|
|
--echo ## update partition column
|
|
--enable_info
|
|
update t set id = id + 1;
|
|
--disable_info
|
|
SELECT * from t order by id limit 2;
|
|
|
|
--echo ## update partition column, old and new record locates on different partitions
|
|
--enable_info
|
|
update t set id = 20 where id = 8;
|
|
--disable_info
|
|
SELECT * from t order by id limit 2;
|
|
|
|
--echo ## table option is auto-increment
|
|
drop table if exists t;
|
|
create table t (id int not null auto_increment, name varchar(255), primary key(id))
|
|
PARTITION BY RANGE ( id ) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11),
|
|
PARTITION p2 VALUES LESS THAN (16),
|
|
PARTITION p3 VALUES LESS THAN (21));
|
|
insert into t(name) values ('aa');
|
|
--enable_info
|
|
update t set id = 8 where name = 'aa';
|
|
--disable_info
|
|
insert into t(name) values ('bb');
|
|
select * from t;
|
|
-- error 1048
|
|
update t set id = null where name = 'aa';
|
|
|
|
--echo ## Test that in a transaction, when a constraint failed in an update statement, the record is not inserted.
|
|
drop table if exists t;
|
|
create table t (id int, name int unique)
|
|
PARTITION BY RANGE ( name ) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11),
|
|
PARTITION p2 VALUES LESS THAN (16),
|
|
PARTITION p3 VALUES LESS THAN (21));
|
|
insert t values (1, 1), (2, 2);
|
|
-- error 1062
|
|
update t set name = 1 where id = 2;
|
|
select * from t;
|
|
|
|
--echo ## test update ignore for pimary key
|
|
drop table if exists t;
|
|
create table t(a bigint, primary key (a))
|
|
PARTITION BY RANGE (a) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11));
|
|
insert into t values (5);
|
|
insert into t values (7);
|
|
update ignore t set a = 5 where a = 7;
|
|
SHOW WARNINGS;
|
|
select * from t order by a;
|
|
|
|
--echo ## test update ignore for truncate as warning
|
|
update ignore t set a = 1 where a = (select '2a');
|
|
SHOW WARNINGS;
|
|
|
|
--echo ## test update ignore for unique key
|
|
drop table if exists t;
|
|
create table t(a bigint, unique key I_uniq (a))
|
|
PARTITION BY RANGE (a) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11));
|
|
insert into t values (5);
|
|
insert into t values (7);
|
|
--enable_info
|
|
update ignore t set a = 5 where a = 7;
|
|
--disable_info
|
|
SHOW WARNINGS;
|
|
select * from t order by a;
|
|
set tidb_opt_fix_control = default;
|
|
|
|
|
|
# TestPartitionedTableDelete
|
|
drop table if exists t;
|
|
set tidb_opt_fix_control = "44262:ON";
|
|
CREATE TABLE t (id int not null default 1, name varchar(255), index(id))
|
|
PARTITION BY RANGE ( id ) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11),
|
|
PARTITION p2 VALUES LESS THAN (16),
|
|
PARTITION p3 VALUES LESS THAN (21));
|
|
insert into t values (1, "hello"),(2, "hello"),(3, "hello"),(4, "hello"),(5, "hello"),(6, "hello"),(7, "hello"),(8, "hello"),(9, "hello"),(10, "hello"),(11, "hello"),(12, "hello"),(13, "hello"),(14, "hello"),(15, "hello"),(16, "hello"),(17, "hello"),(18, "hello"),(19, "hello"),(20, "hello");
|
|
--enable_info
|
|
delete from t where id = 2 limit 1;
|
|
|
|
--echo ## Test delete with false condition
|
|
delete from t where 0;
|
|
--disable_info
|
|
|
|
insert into t values (2, 'abc');
|
|
--enable_info
|
|
delete from t where t.id = 2 limit 1;
|
|
--disable_info
|
|
|
|
--echo ## Test delete ignore
|
|
insert into t values (2, 'abc');
|
|
## TODO: https://github.com/pingcap/tidb/issues/48120
|
|
--replace_regex /INTEGER/DOUBLE/
|
|
-- error 1292
|
|
delete from t where id = (select '2a');
|
|
--enable_info
|
|
delete ignore from t where id = (select '2a');
|
|
--disable_info
|
|
SHOW WARNINGS;
|
|
|
|
--echo ## Test delete without using index, involve multiple partitions.
|
|
--enable_info
|
|
delete from t ignore index(id) where id >= 13 and id <= 17;
|
|
--disable_info
|
|
|
|
admin check table t;
|
|
--enable_info
|
|
delete from t;
|
|
--disable_info
|
|
|
|
--echo ## Fix that partitioned table should not use PointGetPlan.
|
|
drop table if exists t1;
|
|
create table t1 (c1 bigint, c2 bigint, c3 bigint, primary key(c1)) partition by range (c1) (partition p0 values less than (3440));
|
|
insert into t1 values (379, 379, 379);
|
|
--enable_info
|
|
delete from t1 where c1 = 379;
|
|
--disable_info
|
|
drop table t1;
|
|
|
|
set tidb_opt_fix_control=default;
|
|
|
|
|
|
# TestHashPartitionedTableReplace
|
|
drop table if exists replace_test;
|
|
create table replace_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1)
|
|
partition by hash(id) partitions 4;
|
|
replace replace_test (c1) values (1),(2),(NULL);
|
|
begin;
|
|
-- error 1136
|
|
replace replace_test (c1) values ();
|
|
rollback;
|
|
begin;
|
|
-- error 1136
|
|
replace replace_test (c1, c2) values (1,2),(1);
|
|
rollback;
|
|
begin;
|
|
-- error 1054
|
|
replace replace_test (xxx) values (3);
|
|
rollback;
|
|
begin;
|
|
-- error 1146
|
|
replace replace_test_xxx (c1) values ();
|
|
rollback;
|
|
begin;
|
|
-- error 1110
|
|
replace replace_test set c1 = 4, c1 = 5;
|
|
rollback;
|
|
begin;
|
|
-- error 1054
|
|
replace replace_test set xxx = 6;
|
|
rollback;
|
|
replace replace_test set c1 = 3;
|
|
replace replace_test set c1 = 4;
|
|
replace replace_test set c1 = 5;
|
|
replace replace_test set c1 = 6;
|
|
replace replace_test set c1 = 7;
|
|
|
|
drop table if exists replace_test_1;
|
|
create table replace_test_1 (id int, c1 int) partition by hash(id) partitions 5;
|
|
replace replace_test_1 select id, c1 from replace_test;
|
|
|
|
drop table if exists replace_test_2;
|
|
create table replace_test_2 (id int, c1 int) partition by hash(id) partitions 6;
|
|
replace replace_test_1 select id, c1 from replace_test union select id * 10, c1 * 10 from replace_test;
|
|
begin;
|
|
-- error 1136
|
|
replace replace_test_1 select c1 from replace_test;
|
|
rollback;
|
|
|
|
drop table if exists replace_test_3;
|
|
create table replace_test_3 (c1 int, c2 int, UNIQUE INDEX (c2)) partition by hash(c2) partitions 7;
|
|
replace into replace_test_3 set c2=8;
|
|
--enable_info
|
|
replace into replace_test_3 set c2=8;
|
|
replace into replace_test_3 set c1=8, c2=8;
|
|
--disable_info
|
|
replace into replace_test_3 set c2=NULL;
|
|
--enable_info
|
|
replace into replace_test_3 set c2=NULL;
|
|
--disable_info
|
|
|
|
replace into replace_test_3 set c2=0;
|
|
replace into replace_test_3 set c2=1;
|
|
replace into replace_test_3 set c2=2;
|
|
replace into replace_test_3 set c2=3;
|
|
replace into replace_test_3 set c2=4;
|
|
replace into replace_test_3 set c2=5;
|
|
replace into replace_test_3 set c2=6;
|
|
replace into replace_test_3 set c2=7;
|
|
replace into replace_test_3 set c2=8;
|
|
replace into replace_test_3 set c2=9;
|
|
select count(*) from replace_test_3;
|
|
|
|
drop table if exists replace_test_4;
|
|
create table replace_test_4 (c1 int, c2 int, c3 int, UNIQUE INDEX (c1, c2)) partition by hash(c1) partitions 8;
|
|
replace into replace_test_4 set c2=NULL;
|
|
--enable_info
|
|
replace into replace_test_4 set c2=NULL;
|
|
--disable_info
|
|
|
|
drop table if exists replace_test_5;
|
|
create table replace_test_5 (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2)) partition by hash (c2) partitions 9;
|
|
replace into replace_test_5 set c1=1, c2=2;
|
|
--enable_info
|
|
replace into replace_test_5 set c1=1, c2=2;
|
|
--disable_info
|
|
|
|
drop table if exists tIssue989;
|
|
CREATE TABLE tIssue989 (a int, b int, KEY(a), UNIQUE KEY(b)) partition by hash (b) partitions 10;
|
|
insert into tIssue989 (a, b) values (1, 2);
|
|
replace into tIssue989(a, b) values (111, 2);
|
|
select * from tIssue989;
|
|
|
|
## test partition insert/update ignore to invalid partition
|
|
drop table if exists insert_update_ignore_test;
|
|
create table insert_update_ignore_test (a int) partition by range (a) (partition p0 values less than (100), partition p1 values less than (200));
|
|
insert ignore into insert_update_ignore_test values(1000);
|
|
show warnings where Message not like '%disable dynamic pruning%';
|
|
insert ignore into insert_update_ignore_test partition(p0) values(101);
|
|
show warnings where Message not like '%disable dynamic pruning%';
|
|
select * from insert_update_ignore_test;
|
|
insert into insert_update_ignore_test values(1);
|
|
update ignore insert_update_ignore_test set a=1000;
|
|
show warnings where Message not like '%disable dynamic pruning%';
|
|
select * from insert_update_ignore_test;
|
|
update ignore insert_update_ignore_test partition(p0) set a=101;
|
|
show warnings where Message not like '%disable dynamic pruning%';
|
|
select * from insert_update_ignore_test;
|
|
drop table insert_update_ignore_test;
|