drop table if exists t,t0;
create table t0 (i int);
insert into t0 values (1), (1);
create table t (i int unique key);
set @@tidb_dml_batch_size = 1;
insert ignore into t select * from t0;
admin check table t;
drop table if exists t,t0;
set @@tidb_dml_batch_size = default;
drop table if exists t1, t2;
create table t1 (a int default 10, b int default 20);
insert into t1 set a=default;
select * from t1;
a	b
10	20
delete from t1;
insert into t1 set b=default;
select * from t1;
a	b
10	20
delete from t1;
insert into t1 set b=default, a=1;
select * from t1;
a	b
1	20
delete from t1;
insert into t1 set a=default(a);
select * from t1;
a	b
10	20
delete from t1;
insert into t1 set a=default(b), b=default(a);
select * from t1;
a	b
20	10
delete from t1;
insert into t1 set a=default(b)+default(a);
select * from t1;
a	b
30	20
create table t2 (a int default 10 primary key, b int generated always as (-a) virtual, c int generated always as (-a) stored);
insert into t2 set a=default;
select * from t2;
a	b	c
10	-10	-10
delete from t2;
insert into t2 set a=2, b=default;
select * from t2;
a	b	c
2	-2	-2
delete from t2;
insert into t2 set c=default, a=3;
select * from t2;
a	b	c
3	-3	-3
delete from t2;
insert into t2 set a=default, b=default, c=default;
select * from t2;
a	b	c
10	-10	-10
delete from t2;
insert into t2 set a=default(a), b=default, c=default;
select * from t2;
a	b	c
10	-10	-10
delete from t2;
insert into t2 set b=default(a);
Error 3105 (HY000): The value specified for generated column 'b' in table 't2' is not allowed.
insert into t2 set a=default(b), b=default(b);
Error 3105 (HY000): The value specified for generated column 'a' in table 't2' is not allowed.
insert into t2 set a=default(a), c=default(c);
insert into t2 set a=default(a), c=default(a);
Error 3105 (HY000): The value specified for generated column 'c' in table 't2' is not allowed.
insert into t2 set a=3, b=default, c=default(c) ON DUPLICATE KEY UPDATE b = default(b);
insert into t2 set a=3, b=default, c=default(c) ON DUPLICATE KEY UPDATE b = default(b);
Error 1062 (23000): Duplicate entry '3' for key 't2.PRIMARY'
insert into t2 set a=3, b=default, c=default(c) ON DUPLICATE KEY UPDATE b = default(a);
Error 3105 (HY000): The value specified for generated column 'b' in table 't2' is not allowed.
select * from t2;
a	b	c
10	-10	-10
3	-3	-3
drop table t1, t2;
create table t1 (a int not null auto_increment,primary key(a), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
set @@timestamp = 1637541064;
insert into t1 set a=default,t=default;
set @@timestamp = 1637541082;
insert into t1 VALUES (default,default);
select * from t1;
a	t
1	2021-11-22 08:31:04
2	2021-11-22 08:31:22
set @@timestamp = 1637541332;
insert into t1 set a=1,t='2001-02-03 04:05:06' ON DUPLICATE KEY UPDATE t = default;
insert into t1 set a=2,t='2001-02-03 04:05:06' ON DUPLICATE KEY UPDATE t = default(t);
select * from t1;
a	t
1	2021-11-22 08:35:32
2	2021-11-22 08:35:32
DROP TABLE t1;
CREATE TABLE t1 (a int default 1 PRIMARY KEY, b int default 2);
INSERT INTO t1 VALUES (2,2), (3,3);
INSERT INTO t1 VALUES (3,2) ON DUPLICATE KEY UPDATE b = DEFAULT(a);
INSERT INTO t1 SET a = 2, b = 3 ON DUPLICATE KEY UPDATE b = DEFAULT(a);
select * from t1;
a	b
2	1
3	1
drop table if exists t1;
set @@timestamp = DEFAULT;
drop table if exists t1, t2;
create table t1 (a int unique, b int default 20, c int default 30);
insert into t1 values (1,default,default);
insert into t1 values (1,default,default) on duplicate key update b=default;
select * from t1;
a	b	c
1	20	30
insert into t1 values (1,default,default) on duplicate key update c=default, b=default;
select * from t1;
a	b	c
1	20	30
insert into t1 values (1,default,default) on duplicate key update c=default, a=2;
select * from t1;
a	b	c
2	20	30
insert into t1 values (2,default,default) on duplicate key update c=default(b);
select * from t1;
a	b	c
2	20	20
insert into t1 values (2,default,default) on duplicate key update a=default(b)+default(c);
select * from t1;
a	b	c
50	20	20
create table t2 (a int unique, b int generated always as (-a) virtual, c int generated always as (-a) stored);
insert into t2 values (1,default,default);
insert into t2 values (1,default,default) on duplicate key update a=2, b=default;
select * from t2;
a	b	c
2	-2	-2
insert into t2 values (2,default,default) on duplicate key update a=3, c=default;
select * from t2;
a	b	c
3	-3	-3
insert into t2 values (3,default,default) on duplicate key update c=default, b=default, a=4;
select * from t2;
a	b	c
4	-4	-4
insert into t2 values (4,default,default) on duplicate key update b=default, a=5, c=default;
select * from t2;
a	b	c
5	-5	-5
insert into t2 values (5,default,default) on duplicate key update b=default(a);
Error 3105 (HY000): The value specified for generated column 'b' in table 't2' is not allowed.
insert into t2 values (5,default,default) on duplicate key update a=default(a), c=default(c);
select * from t2;
a	b	c
NULL	NULL	NULL
delete from t2;
insert into t2 (a) values (1);
insert into t2 values (1,default,default) on duplicate key update a=default(b), b=default(b);
select * from t2;
a	b	c
NULL	NULL	NULL
delete from t2;
insert into t2 (a) values (1);
insert into t2 values (1,default,default) on duplicate key update a=default(a), c=default(a);
Error 3105 (HY000): The value specified for generated column 'c' in table 't2' is not allowed.
drop table t1, t2;
set @@tidb_txn_mode = 'pessimistic';
create table t ( c_int int, c_string varchar(40) collate utf8mb4_bin , primary key (c_string), unique key (c_int));
insert into t values (22, 'gold witch'), (24, 'gray singer'), (21, 'silver sight');
begin;
insert into t values (21,'black warlock'), (22, 'dark sloth'), (21,  'cyan song') on duplicate key update c_int = c_int + 1, c_string = concat(c_int, ':', c_string);
Error 1062 (23000): Duplicate entry '22' for key 't.c_int'
commit;
select * from t order by c_int;
c_int	c_string
21	silver sight
22	gold witch
24	gray singer
drop table t;
set @@tidb_txn_mode = default;
drop table if exists t;
create table t (a varchar(20) charset utf8mb4 collate utf8mb4_general_ci primary key);
replace into t(a) values (_binary'A '),(_binary'A');
select a from t use index(primary);
a
A
select a from t ignore index(primary);
a
A
drop table if exists t;
drop table if exists t1;
create table t1(id int, id_gen int as(id + 42), b int, unique key id_gen(id_gen));
insert into t1 (id, b) values(1,1),(2,2),(3,3),(4,4),(5,5);
replace into t1 (id, b) values(1,1);
replace into t1 (id, b) values(1,1),(2,2);
replace into t1 (id, b) values(6,16),(7,17),(8,18);
select * from t1;
id	id_gen	b
1	43	1
2	44	2
3	45	3
4	46	4
5	47	5
6	48	16
7	49	17
8	50	18
insert into t1 (id, b) values (6,18) on duplicate key update id = -id;
insert into t1 (id, b) values (7,28) on duplicate key update b = -values(b);
select * from t1;
id	id_gen	b
1	43	1
2	44	2
3	45	3
4	46	4
5	47	5
-6	36	16
7	49	-28
8	50	18
drop table if exists t;
create table t (i int as(k+1) stored, j int as(k+2) virtual, k int, unique key idx_i(i), unique key idx_j(j));
insert into t (k) values (1), (2);
replace into t (k) values (1), (2);
select * from t;
i	j	k
2	3	1
3	4	2
drop table if exists t;
create table t (i int as(k+1) stored, j int as(k+2) virtual, k int, unique key idx_j(j));
insert into t (k) values (1), (2);
replace into t (k) values (1), (2);
select * from t;
i	j	k
2	3	1
3	4	2
drop table if exists t;
create table t (i int as(k+1) stored, j int as(k+2) virtual, k int, unique key idx_i(i));
insert into t (k) values (1), (2);
replace into t (k) values (1), (2);
select * from t;
i	j	k
2	3	1
3	4	2
drop table if exists t1;
create table t1(f1 json, f2 real as (cast(f1 as decimal(2,1))));
INSERT INTO t1 (f1) VALUES (CAST(1000 AS JSON));
Error 1690 (22003): DECIMAL value is out of range in '(2, 1)'
set @@sql_mode = '';
INSERT INTO t1 (f1) VALUES (CAST(1000 AS JSON));
select * from t1;
f1	f2
1000	9.9
set @@sql_mode = default;
drop table if exists t1;
drop table if exists test_null_default;
set timestamp = 1234;
set time_zone = '+08:00';
create table test_null_default (ts timestamp null default current_timestamp);
insert into test_null_default values (null);
select * from test_null_default;
ts
NULL
insert into test_null_default values ();
select * from test_null_default;
ts
NULL
1970-01-01 08:20:34
set timestamp = default;
drop table if exists test_null_default;
drop table if exists t1,t2;
create table t1 (a int not null default null default 1);
create table t2 (a int);
alter table  t2 change column a a int not null default null default 1;
drop table t1,t2;
drop table if exists t1, t2;
create table t1(id int);
create table t2(id int);
insert into t1 values(123);
insert into t2 values(123);
delete from t1 where id not in (select id from t2);
select * from t1;
id
123
delete from t1 where id in (select id from t2);
select * from t1;
id
drop table if exists t1, t2;
drop table if exists t;
create table t(a int, b int);
insert into t set a=1, b=a+1;
select a, b from t;
a	b
1	2
drop table if exists t;
create table t(a int default 100, b int);
insert into t set b=a+1, a=1;
select a, b from t;
a	b
1	101
insert into t (b) value (a);
select * from t where b = 100;
a	b
100	100
insert into t set a=2, b=a+1;
select * from t where a = 2;
a	b
2	3
drop table if exists t;
create table t (c int);
insert into t set t.c = '1';
select * from t;
c
1
drop table if exists t;
create table t(a int default 1);
insert into t values (a);
select * from t;
a
1
drop table if exists t;
create table t (a int, b int, c int, d int);
insert into t value (1, 2, a+1, b+1);
select * from t;
a	b	c	d
1	2	2	3
drop table if exists t;
create table t (a int not null);
insert into t values (a+2);
insert into t values (a);
select * from t order by a;
a
0
2
drop table if exists t;
create table t (a bigint not null, b bigint not null);
insert into t value(b + 1, a);
insert into t set a = b + a, b = a + 1;
insert into t value(1000, a);
insert t set b = sqrt(a + 4), a = 10;
select * from t order by a;
a	b
0	1
1	1
10	2
1000	1000
drop table if exists t;
create table t(a int);
insert into t values(a);
select * from t;
a
NULL
drop table if exists t;
create table t(a enum('a', 'b'));
insert into t values(a);
select * from t;
a
NULL
drop table if exists t;
create table t(a enum('a', 'b') default 'a');
insert into t values(a);
insert into t values(a+1);
select * from t order by a;
a
a
b
drop table if exists t;
create table t(a blob);
insert into t values(a);
select * from t;
a
NULL
drop table if exists t;
create table t(a varchar(20) default 'a');
insert into t values(a);
insert into t values(upper(a));
select * from t order by a;
a
A
a
drop table if exists t;
create table t(a varchar(20) not null, b varchar(20));
insert into t value (a, b);
select * from t;
a	b
	NULL
drop table if exists t;
create table t(a int, b int);
insert into t values(a*b, b*b);
select * from t;
a	b
NULL	NULL
drop table if exists t;
create table t (a json not null, b int);
insert into t value (a,a->'$');
Error 1366 (HY000): Incorrect int value: 'null' for column 'b' at row 1
drop table if exists t;
create table t (a json not null, b varchar(10));
insert into t value (a,a->'$');
select * from t;
a	b
null	null
drop table if exists t;
create table t(a json, b int, c int as (a->'$.a'));
insert into t (a, b) value (a, a->'$.a'+1);
insert into t (b) value (a->'$.a'+1);
select * from t;
a	b	c
NULL	NULL	NULL
NULL	NULL	NULL
insert into t (a, b) value ('{"a": 1}', a->'$.a'+1);
select * from t where c = 1;
a	b	c
{"a": 1}	2	1
truncate table t;
insert t set b = c + 1;
select * from t;
a	b	c
NULL	NULL	NULL
truncate table t;
insert t set a = '{"a": 1}', b = c;
select * from t;
a	b	c
{"a": 1}	NULL	1
drop table if exists t;
create table t(a int auto_increment key, b int);
insert into t (b) value (a);
insert into t value (a, a+1);
set SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
insert into t (b) value (a+1);
select * from t order by a;
a	b
1	0
2	1
3	1
set SQL_MODE=STRICT_ALL_TABLES;
drop table if exists t;
create table t(a int not null, b int, c int as (sqrt(a)));
insert t set b = a, a = 4;
select * from t;
a	b	c
4	0	2
set SQL_MODE=default;
drop table t;
drop table if exists t;
create table t(a varchar(2));
insert into t values('123');
Error 1406 (22001): Data too long for column 'a' at row 1
insert into t values('12');
update t set a = '123' where a = '12';
Error 1406 (22001): Data too long for column 'a' at row 1
drop table t;
set tidb_constraint_check_in_place = 0;
set @@tidb_txn_mode = 'optimistic';
drop table if exists t1, t2, t3, t4, t5;
create table t1(i int primary key, j int);
insert into t1 values(1, 2);
begin;
insert into t1 values(1, 3);
delete from t1 where j = 3;
commit;
Error 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'
rollback;
create table t2(i int, j int, unique index idx(i));
insert into t2 values(1, 2);
begin;
insert into t2 values(1, 3);
delete from t2 where j = 3;
commit;
Error 1062 (23000): Duplicate entry '1' for key 't2.idx'
admin check table t2;
create table t3(i int, j int, primary key(i));
begin;
insert into t3 values(1, 3);
delete from t3 where j = 3;
commit;
create table t4(i int, j int, primary key(i));
begin;
insert into t4 values(1, 3);
delete from t4 where j = 3;
insert into t4 values(2, 3);
commit;
admin check table t4;
select * from t4;
i	j
2	3
create table t5(i int, j int, primary key(i));
begin;
insert into t5 values(1, 3);
delete from t5 where j = 3;
insert into t5 values(1, 4);
commit;
admin check table t5;
select * from t5;
i	j
1	4
set tidb_constraint_check_in_place = default;
set @@tidb_txn_mode = default;
drop table if exists test;
create table test (id int, prescription_type enum('a','b','c','d','e','f') NOT NULL, primary key(id));
insert into test (id)  values (1);
select prescription_type from test;
prescription_type
a
drop table if exists test;
drop table if exists t;
create table t (pk int primary key, uk int unique, v int);
insert into t values (2, 11, 215);
insert into t values (3, 7, 2111);
update t set pk = 2 where uk = 7;
Error 1062 (23000): Duplicate entry '2' for key 't.PRIMARY'
drop table if exists t;
drop table if exists t, te, tn, t1, t2, t3;
create table t (i int, j int as (i+1) not null);
insert into t set i = j + 1;
select * from t;
i	j
1	2
insert into t set i = j + 100;
select * from t;
i	j
1	2
100	101
create table te (i int);
insert into te set i = i + 10;
select * from te;
i
NULL
insert into te set i = i;
select * from te;
i
NULL
NULL
create table tn (i int not null);
insert into tn set i = i;
select * from tn;
i
0
insert into tn set i = i + 10;
select * from tn;
i
0
10
create table t1 (j int(11) GENERATED ALWAYS AS (i + 1) stored, i int(11) DEFAULT '10');
insert into t1 values();
select * from t1;
j	i
11	10
insert into t1 values();
select * from t1;
j	i
11	10
11	10
create table t2 (j int(11) GENERATED ALWAYS AS (i + 1) stored not null, i int(11) DEFAULT '5');
insert into t2 set i = j + 9;
select * from t2;
j	i
10	9
insert into t2 set j = i + 1;
Error 3105 (HY000): The value specified for generated column 'j' in table 't2' is not allowed.
insert into t2 set i = j + 100;
select * from t2;
j	i
10	9
101	100
create table t3(j int(11) GENERATED ALWAYS AS (i + 1) stored, i int(11) DEFAULT '5');
insert into t3 set i = j + 100;
select * from t3;
j	i
NULL	NULL
insert into t3 set j = i + 1;
Error 3105 (HY000): The value specified for generated column 'j' in table 't3' is not allowed.
drop table if exists t, te, t1, t2, t3;
drop table if exists t1;
create table t1(c1 timestamp default current_timestamp, c2 int, c3 timestamp default current_timestamp);
insert into t1 set c1 = current_timestamp, c2 = sleep(2);
select c1 = c3 from t1;
c1 = c3
1
insert into t1 set c1 = current_timestamp, c2 = sleep(1);
select c1 = c3 from t1;
c1 = c3
1
1
drop table if exists t1;
drop table if exists t;
create table t ( c_int int, c_str varchar(40),c_datetime datetime, c_timestamp timestamp,
c_double double, c_decimal decimal(12, 6) , primary key(c_int, c_str) , unique key(c_int) , unique key(c_str) ,
unique key(c_decimal) , unique key(c_datetime) , key(c_timestamp) );
insert into t values (1, 'zen ardinghelli', '2020-02-03 18:15:17', '2020-03-11 05:47:11', 36.226534, 3.763),
(2, 'suspicious joliot', '2020-01-01 22:56:37', '2020-04-07 06:19:07', 62.756537, 5.567),
(3, 'keen zhukovsky', '2020-01-21 04:09:20', '2020-06-06 08:32:14', 33.115065, 1.381),
(4, 'crazy newton', '2020-02-14 21:37:56', '2020-04-28 08:33:48', 44.146318, 4.249),
(5, 'happy black', '2020-03-12 16:04:14', '2020-01-18 09:17:37', 41.962653, 5.959);
insert into t values (6, 'vigilant swartz', '2020-06-01 07:37:44', '2020-05-25 01:26:43', 56.352233, 2.202),
(7, 'suspicious germain', '2020-04-16 23:25:23', '2020-03-17 05:06:57', 55.897698, 3.460),
(8, 'festive chandrasekhar', '2020-02-11 23:40:29', '2020-04-08 10:13:04', 77.565691, 0.540),
(9, 'vigorous meninsky', '2020-02-17 10:03:17', '2020-01-02 15:02:02', 6.484815, 6.292),
(10, 'heuristic moser', '2020-04-20 12:18:49', '2020-06-20 20:20:18', 28.023822, 2.765);
insert into t values (11, 'sharp carver', '2020-03-01 11:23:41', '2020-03-23 17:59:05', 40.842442, 6.345),
(12, 'trusting noether', '2020-03-28 06:42:34', '2020-01-27 15:33:40', 49.544658, 4.811),
(13, 'objective ishizaka', '2020-01-28 17:30:55', '2020-04-02 17:45:39', 59.523930, 5.015),
(14, 'sad rhodes', '2020-03-30 21:43:37', '2020-06-09 06:53:53', 87.295753, 2.413),
(15, 'wonderful shockley', '2020-04-29 09:17:11', '2020-03-14 04:36:51', 6.778588, 8.497);
begin pessimistic;
insert into t values (13, 'vibrant yalow', '2020-05-15 06:59:05', '2020-05-03 05:58:45', 43.721929, 8.066),
(14, 'xenodochial spence', '2020-02-13 17:28:07', '2020-04-01 12:18:30', 19.981331, 5.774),
(22, 'eloquent neumann', '2020-02-10 16:00:20', '2020-03-28 00:24:42', 10.702532, 7.618)
on duplicate key update c_int=values(c_int), c_str=values(c_str), c_double=values(c_double), c_timestamp=values(c_timestamp);
select sum((select t1.c_str from t t1 where t1.c_int = 11 and t1.c_str > t.c_str order by t1.c_decimal limit 1) is null) nulls
from t order by c_str;
nulls
10
select sum((select t1.c_str from t t1 where t1.c_int in (11, 10086) and t1.c_str > t.c_str order by t1.c_decimal limit 1) is null) nulls
from t order by c_str;
nulls
10
commit;
select sum((select t1.c_str from t t1 where t1.c_int = 11 and t1.c_str > t.c_str order by t1.c_decimal limit 1) is null) nulls
from t order by c_str;
nulls
10
select sum((select t1.c_str from t t1 where t1.c_int in (11, 10086) and t1.c_str > t.c_str order by t1.c_decimal limit 1) is null) nulls
from t order by c_str;
nulls
10
drop table if exists t;
drop table if exists t;
create table t (id int, name varchar(10), unique index idx (id)) partition by list  (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)
);
insert into t values  (1, 'a');
update t set name='b' where id=2;
select * from t;
id	name
1	a
update t set name='b' where id=1;
select * from t;
id	name
1	b
replace into t values  (1, 'c');
select * from t;
id	name
1	c
insert into t values (1, 'd') on duplicate key update name='e';
select * from t;
id	name
1	e
delete from t where id=1;
select * from t;
id	name
insert into t values  (2, 'f');
delete from t where name='f';
select * from t;
id	name
insert into t values  (1, 'a');
insert into t values (1, 'd');
Error 1062 (23000): Duplicate entry '1' for key 't.idx'
insert into t values (100, 'd');
Error 1526 (HY000): Table has no partition for value 100
admin check table t;
insert into t values  (2,'b'),(3,'c'),(4,'d'),(7,'f'), (null,null);
select * from t partition (p0) order by id;
id	name
3	c
select * from t partition (p1,p3) order by id;
id	name
NULL	NULL
1	a
2	b
7	f
select * from t partition (p1,p3,p0,p2) order by id;
id	name
NULL	NULL
1	a
2	b
3	c
4	d
7	f
select * from t order by id;
id	name
NULL	NULL
1	a
2	b
3	c
4	d
7	f
delete from t partition (p0);
select * from t order by id;
id	name
NULL	NULL
1	a
2	b
4	d
7	f
delete from t partition (p3,p2);
select * from t order by id;
id	name
1	a
2	b
drop table if exists t;
drop table if exists t;
create table t (id int, name varchar(10), unique index idx (id)) 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)
);
insert into t values  (1, 'a');
update t set name='b' where id=2;
select * from t;
id	name
1	a
update t set name='b' where id=1;
select * from t;
id	name
1	b
replace into t values  (1, 'c');
select * from t;
id	name
1	c
insert into t values (1, 'd') on duplicate key update name='e';
select * from t;
id	name
1	e
delete from t where id=1;
select * from t;
id	name
insert into t values  (2, 'f');
delete from t where name='f';
select * from t;
id	name
insert into t values  (1, 'a');
insert into t values (1, 'd');
Error 1062 (23000): Duplicate entry '1' for key 't.idx'
insert into t values (100, 'd');
Error 1526 (HY000): Table has no partition for value from column_list
admin check table t;
insert into t values  (2,'b'),(3,'c'),(4,'d'),(7,'f'), (null,null);
select * from t partition (p0) order by id;
id	name
3	c
select * from t partition (p1,p3) order by id;
id	name
NULL	NULL
1	a
2	b
7	f
select * from t partition (p1,p3,p0,p2) order by id;
id	name
NULL	NULL
1	a
2	b
3	c
4	d
7	f
select * from t order by id;
id	name
NULL	NULL
1	a
2	b
3	c
4	d
7	f
delete from t partition (p0);
select * from t order by id;
id	name
NULL	NULL
1	a
2	b
4	d
7	f
delete from t partition (p3,p2);
select * from t order by id;
id	name
1	a
2	b
drop table if exists t;
drop table if exists t;
create table t (id int, name varchar(10)) partition by list  (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)
);
insert into t values  (1, 'a'),(1,'b');
alter table t add unique index idx (id);
Error 1062 (23000): Duplicate entry '1' for key 't.idx'
delete from t where name='b';
alter table t add unique index idx (id);
delete from t;
insert into t values  (1, 'a'),(2,'b'),(10,'c');
select * from t partition(p1) order by id;
id	name
1	a
2	b
10	c
delete from t;
insert into t values  (1, 'a'),(3,'c'),(4,'e');
select * from t partition(p0) order by id;
id	name
3	c
select * from t partition(p1) order by id;
id	name
1	a
select * from t partition(p2) order by id;
id	name
4	e
select * from t partition(p3) order by id;
id	name
insert into t values (1, 'd'), (3,'f'),(5,'g') on duplicate key update name='x';
select * from t partition(p0) order by id;
id	name
3	x
5	g
select * from t partition(p1) order by id;
id	name
1	x
select * from t partition(p2) order by id;
id	name
4	e
select * from t partition(p3) order by id;
id	name
insert into t values (3, 'a'), (11,'x') on duplicate key update id=id+1;
Error 1062 (23000): Duplicate entry '4' for key 't.idx'
select * from t order by id;
id	name
1	x
3	x
4	e
5	g
insert ignore into t values  (1, 'b'), (5,'a'),(null,'y');
Level	Code	Message
Warning	1062	Duplicate entry '1' for key 't.idx'
Warning	1062	Duplicate entry '5' for key 't.idx'
select * from t partition(p0) order by id;
id	name
3	x
5	g
select * from t partition(p1) order by id;
id	name
1	x
select * from t partition(p2) order by id;
id	name
4	e
select * from t partition(p3) order by id;
id	name
NULL	y
insert ignore into t values  (15, 'a'),(17,'a');
select * from t partition(p0,p1,p2) order by id;
id	name
1	x
3	x
4	e
5	g
17	a
select * from t partition(p3) order by id;
id	name
NULL	y
15	a
insert into t values (100, 'd');
Error 1526 (HY000): Table has no partition for value 100
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);
id	name
1	a
2	b
update t set name='x' where id in (1,2);
select * from t partition(p1);
id	name
1	x
2	x
update t set name='y' where id < 3;
select * from t order by id;
id	name
1	y
2	y
3	c
update t set id=2 where id = 1;
Error 1062 (23000): Duplicate entry '2' for key 't.idx'
select * from t order by id;
id	name
1	y
2	y
3	c
update t set name='z' where id in (1,2,3);
select * from t order by id;
id	name
1	z
2	z
3	z
update t set name='a' limit 3;
select * from t order by id;
id	name
1	a
2	a
3	a
update t set id=id*10 where id in (1,2);
select * from t order by id;
id	name
3	a
10	a
20	a
update t set id=id+17 where id in (3,10);
Error 1062 (23000): Duplicate entry '20' for key 't.idx'
select * from t order by id;
id	name
3	a
10	a
20	a
update t set id=id*2 where id in (3,20);
Error 1526 (HY000): Table has no partition for value 40
select * from t order by id;
id	name
3	a
10	a
20	a
delete from t;
replace into t values  (1, 'a'),(2,'b');
select * from t order by id;
id	name
1	a
2	b
replace into t values  (3, 'c'),(4,'d'),(7,'f');
select * from t partition(p0) order by id;
id	name
3	c
select * from t partition(p1) order by id;
id	name
1	a
2	b
select * from t partition(p2) order by id;
id	name
4	d
select * from t partition(p3) order by id;
id	name
7	f
replace into t values  (1, 'x'),(7,'x');
select * from t order by id;
id	name
1	x
2	b
3	c
4	d
7	x
replace into t values  (10,'x'),(50,'x');
Error 1526 (HY000): Table has no partition for value 50
select * from t order by id;
id	name
1	x
2	b
3	c
4	d
7	x
delete from t where id = 3;
select * from t partition(p0) order by id;
id	name
delete from t where id in (1,2);
select * from t partition(p1) order by id;
id	name
delete from t where id in (4,7,10,11);
select * from t;
id	name
insert into t values  (3, 'c'),(4,'d'),(7,'f');
delete from t where id < 10;
select * from t;
id	name
insert into t values  (3, 'c'),(4,'d'),(7,'f');
delete from t limit 3;
select * from t;
id	name
drop table if exists t;
drop table if exists t;
create table t (location varchar(10), id int, a int) partition by list columns (location,id) (
partition p_west  values in (('w', 1),('w', 2),('w', 3),('w', 4)),
partition p_east  values in (('e', 5),('e', 6),('e', 7),('e', 8)),
partition p_north values in (('n', 9),('n',10),('n',11),('n',12)),
partition p_south values in (('s',13),('s',14),('s',15),('s',16))
);
insert into t values  ('w', 1, 1),('w', 1, 2);
alter table t add unique index idx (location,id);
Error 1062 (23000): Duplicate entry 'w-1' for key 't.idx'
delete from t where a=2;
alter table t add unique index idx (location,id);
delete from t;
insert into t values  ('w', 1, 1),('w', 2, 2),('w', 3, 3);
select * from t partition(p_west) order by id;
location	id	a
w	1	1
w	2	2
w	3	3
delete from t;
insert into t values  ('w', 1, 1),('e', 5, 5),('n', 9, 9);
select * from t partition(p_west) order by id;
location	id	a
w	1	1
select * from t partition(p_east) order by id;
location	id	a
e	5	5
select * from t partition(p_north) order by id;
location	id	a
n	9	9
select * from t partition(p_south) order by id;
location	id	a
insert into t values  ('w', 1, 1) on duplicate key update a=a+1;
select * from t partition(p_west) order by id;
location	id	a
w	1	2
insert into t values  ('w', 1, 1) on duplicate key update location='s', id=13;
select * from t partition(p_south) order by id;
location	id	a
s	13	2
select * from t partition(p_west) order by id;
location	id	a
insert into t values  ('w', 2, 2), ('w', 1, 1);
insert into t values  ('w', 2, 3) on duplicate key update id=1;
Error 1062 (23000): Duplicate entry 'w-1' for key 't.idx'
select * from t partition(p_west) order by id;
location	id	a
w	1	1
w	2	2
insert ignore into t values  ('w', 2, 2), ('w', 3, 3), ('n', 10, 10);
Level	Code	Message
Warning	1062	Duplicate entry 'w-2' for key 't.idx'
select * from t partition(p_west) order by id;
location	id	a
w	1	1
w	2	2
w	3	3
select * from t partition(p_north) order by id;
location	id	a
n	9	9
n	10	10
insert ignore into t values  ('w', 4, 4), ('s', 14, 14);
select * from t partition(p_west) order by id;
location	id	a
w	1	1
w	2	2
w	3	3
w	4	4
select * from t partition(p_south) order by id;
location	id	a
s	13	2
s	14	14
insert into t values  ('w', 5, 5);
Error 1526 (HY000): Table has no partition for value from column_list
insert into t values  ('s', 5, 5);
Error 1526 (HY000): Table has no partition for value from column_list
insert into t values  ('s', 100, 5);
Error 1526 (HY000): Table has no partition for value from column_list
insert into t values  ('x', 1, 5);
Error 1526 (HY000): Table has no partition for value from column_list
delete from t;
insert into t values  ('w', 1, 1),('w', 2, 2),('w', 3, 3);
update t set a=2 where a=1;
select * from t partition(p_west) order by id;
location	id	a
w	1	2
w	2	2
w	3	3
update t set a=3 where location='w';
select * from t partition(p_west) order by id;
location	id	a
w	1	3
w	2	3
w	3	3
update t set a=4 where location='w' and id=1;
select * from t partition(p_west) order by id;
location	id	a
w	1	4
w	2	3
w	3	3
update t set a=5 where id=1;
select * from t partition(p_west) order by id;
location	id	a
w	1	5
w	2	3
w	3	3
update t set a=a+id where id>1;
select * from t partition(p_west) order by id,a;
location	id	a
w	1	5
w	2	5
w	3	6
update t set id=id+1 where location='w' and id<2;
Error 1062 (23000): Duplicate entry 'w-2' for key 't.idx'
select * from t partition(p_west) order by id,a;
location	id	a
w	1	5
w	2	5
w	3	6
delete from t;
insert into t values  ('w', 1, 1), ('w', 2, 2), ('e', 8, 8),('n', 11, 11);
update t set a=a+1 where id < 20;
select * from t order by id;
location	id	a
w	1	2
w	2	3
e	8	9
n	11	12
update t set a=a+1 where location in ('w','s','n');
select * from t order by id;
location	id	a
w	1	3
w	2	4
e	8	9
n	11	13
update t set a=a+1 where location in ('w','s','n') and id in (1,3,5,7,8,9,11);
select * from t order by id;
location	id	a
w	1	4
w	2	4
e	8	9
n	11	14
update t set a=a+1 where location='n' and id=12;
select * from t order by id;
location	id	a
w	1	4
w	2	4
e	8	9
n	11	14
update t set a=a+1 where location='n' and id=11;
select * from t order by id;
location	id	a
w	1	4
w	2	4
e	8	9
n	11	15
update t set id=id+1 where location='w' and id in (1,2);
Error 1062 (23000): Duplicate entry 'w-2' for key 't.idx'
select * from t order by id;
location	id	a
w	1	4
w	2	4
e	8	9
n	11	15
update t set id=id+3 where location='w' and id in (1,2);
Error 1526 (HY000): Table has no partition for value from column_list
select * from t order by id;
location	id	a
w	1	4
w	2	4
e	8	9
n	11	15
update t set location='s', id=14 where location='e' and id=8;
select * from t order by id;
location	id	a
w	1	4
w	2	4
n	11	15
s	14	9
delete from t;
replace into t values  ('w', 1, 1),('w', 2, 2),('w', 3, 3);
select * from t partition(p_west) order by id;
location	id	a
w	1	1
w	2	2
w	3	3
delete from t;
replace into t values  ('w', 1, 1),('e', 5, 5),('n', 9, 9);
select * from t order by id;
location	id	a
w	1	1
e	5	5
n	9	9
replace into t values  ('w', 1, 2),('n', 10, 10);
select * from t order by id;
location	id	a
w	1	2
e	5	5
n	9	9
n	10	10
replace into t values  ('w', 5, 5);
Error 1526 (HY000): Table has no partition for value from column_list
replace into t values  ('s', 5, 5);
Error 1526 (HY000): Table has no partition for value from column_list
replace into t values  ('s', 100, 5);
Error 1526 (HY000): Table has no partition for value from column_list
replace into t values  ('x', 1, 5);
Error 1526 (HY000): Table has no partition for value from column_list
delete from t where location='w' and id=2;
select * from t order by id;
location	id	a
w	1	2
e	5	5
n	9	9
n	10	10
delete from t where location='w' and id=1;
select * from t order by id;
location	id	a
e	5	5
n	9	9
n	10	10
delete from t where location in ('w','e','n') and id in (1,2,3,4,5,8,9);
select * from t order by id;
location	id	a
n	10	10
delete from t where a=10;
select * from t order by id;
location	id	a
replace into t values  ('w', 1, 1),('e', 5, 5),('n', 11, 11);
delete from t where id < 10;
select * from t order by id;
location	id	a
n	11	11
delete from t limit 1;
select * from t order by id;
location	id	a
drop table if exists t;
drop table if exists t;
create table t (a int) partition by list (a%10) (partition p0 values in (0,1));
replace into t values  (null);
Error 1526 (HY000): Table has no partition for value NULL
drop table if exists t;
drop table if exists t;
create table t (a bigint, b bigint GENERATED ALWAYS AS (3*a - 2*a) VIRTUAL, index idx(a)) partition by list (5*b - 4*b) (partition p0 values in (1,2,3,4,5), partition p1 values in (6,7,8,9,10));
insert into t (a) values (1),(3),(5),(7),(9);
select a from t partition (p0) order by a;
a
1
3
5
select a from t partition (p1) order by a;
a
7
9
select * from t where a = 1;
a	b
1	1
update t set a=a+1 where a = 1;
select a from t partition (p0) order by a;
a
2
3
5
select a from t partition (p1) order by a;
a
7
9
select * from t where a = 1;
a	b
select * from t where a = 2;
a	b
2	2
delete from t where a>10;
select count(1) from t;
count(1)
5
delete from t where a=9;
select a from t partition (p1) order by a;
a
7
select count(1) from t;
count(1)
4
insert into t (a) values (11);
Error 1526 (HY000): Table has no partition for value 11
update t set a=a+10 where a = 2;
Error 1526 (HY000): Table has no partition for value 12
drop table if exists t;
create table t (a bigint, b bigint GENERATED ALWAYS AS (3*a - 2*a) STORED, index idx(a)) partition by list (5*b - 4*b) (partition p0 values in (1,2,3,4,5), partition p1 values in (6,7,8,9,10));
insert into t (a) values (1),(3),(5),(7),(9);
select a from t partition (p0) order by a;
a
1
3
5
select a from t partition (p1) order by a;
a
7
9
select * from t where a = 1;
a	b
1	1
update t set a=a+1 where a = 1;
select a from t partition (p0) order by a;
a
2
3
5
select a from t partition (p1) order by a;
a
7
9
select * from t where a = 1;
a	b
select * from t where a = 2;
a	b
2	2
delete from t where a>10;
select count(1) from t;
count(1)
5
delete from t where a=9;
select a from t partition (p1) order by a;
a
7
select count(1) from t;
count(1)
4
insert into t (a) values (11);
Error 1526 (HY000): Table has no partition for value 11
update t set a=a+10 where a = 2;
Error 1526 (HY000): Table has no partition for value 12
drop table if exists t;
create table t (a bigint, b bigint GENERATED ALWAYS AS (3*a - 2*a) VIRTUAL, index idx(a)) partition by list columns(b) (partition p0 values in (1,2,3,4,5), partition p1 values in (6,7,8,9,10));
insert into t (a) values (1),(3),(5),(7),(9);
select a from t partition (p0) order by a;
a
1
3
5
select a from t partition (p1) order by a;
a
7
9
select * from t where a = 1;
a	b
1	1
update t set a=a+1 where a = 1;
select a from t partition (p0) order by a;
a
2
3
5
select a from t partition (p1) order by a;
a
7
9
select * from t where a = 1;
a	b
select * from t where a = 2;
a	b
2	2
delete from t where a>10;
select count(1) from t;
count(1)
5
delete from t where a=9;
select a from t partition (p1) order by a;
a
7
select count(1) from t;
count(1)
4
insert into t (a) values (11);
Error 1526 (HY000): Table has no partition for value from column_list
update t set a=a+10 where a = 2;
Error 1526 (HY000): Table has no partition for value from column_list
drop table if exists t;
create table t (a bigint, b bigint GENERATED ALWAYS AS (3*a - 2*a) STORED, index idx(a)) partition by list columns(b) (partition p0 values in (1,2,3,4,5), partition p1 values in (6,7,8,9,10));
insert into t (a) values (1),(3),(5),(7),(9);
select a from t partition (p0) order by a;
a
1
3
5
select a from t partition (p1) order by a;
a
7
9
select * from t where a = 1;
a	b
1	1
update t set a=a+1 where a = 1;
select a from t partition (p0) order by a;
a
2
3
5
select a from t partition (p1) order by a;
a
7
9
select * from t where a = 1;
a	b
select * from t where a = 2;
a	b
2	2
delete from t where a>10;
select count(1) from t;
count(1)
5
delete from t where a=9;
select a from t partition (p1) order by a;
a
7
select count(1) from t;
count(1)
4
insert into t (a) values (11);
Error 1526 (HY000): Table has no partition for value from column_list
update t set a=a+10 where a = 2;
Error 1526 (HY000): Table has no partition for value from column_list
drop table if exists t;
drop table if exists t;
create table t (a year, b year GENERATED ALWAYS AS (3*a - 2*a) VIRTUAL, index idx(a)) partition by list (1 + b - 1) (partition p0 values in (2001,2002,2003,2004,2005), partition p1 values in (2006,2007,2008,2009));
insert into t (a) values (1),(3),(5),(7),(9);
select a from t partition (p0) order by a;
a
2001
2003
2005
select a from t partition (p1) order by a;
a
2007
2009
select * from t where a = 1;
a	b
2001	2001
update t set a=a+1 where a = 1;
select a from t partition (p0) order by a;
a
2002
2003
2005
select a from t partition (p1) order by a;
a
2007
2009
select * from t where a = 1;
a	b
select * from t where a = 2;
a	b
2002	2002
delete from t where a>10;
select count(1) from t;
count(1)
5
delete from t where a=9;
select a from t partition (p1) order by a;
a
2007
select count(1) from t;
count(1)
4
insert into t (a) values (11);
Error 1526 (HY000): Table has no partition for value 2011
update t set a=a+10 where a = 2;
Error 1526 (HY000): Table has no partition for value 2012
delete from t;
insert into t (a) values (2001),(2003),(2005),(2007),(2009);
select a from t partition (p0) order by a;
a
2001
2003
2005
select a from t partition (p1) order by a;
a
2007
2009
select * from t where a = 2001;
a	b
2001	2001
update t set a=a+1 where a = 2001;
select a from t partition (p0) order by a;
a
2002
2003
2005
select a from t partition (p1) order by a;
a
2007
2009
select * from t where a = 2001;
a	b
select * from t where a = 2002;
a	b
2002	2002
delete from t where a>2010;
select count(1) from t;
count(1)
5
delete from t where a=2009;
select a from t partition (p1) order by a;
a
2007
select count(1) from t;
count(1)
4
insert into t (a) values (2011);
Error 1526 (HY000): Table has no partition for value 2011
update t set a=a+10 where a = 2002;
Error 1526 (HY000): Table has no partition for value 2012
drop table if exists t;
create table t (a year, b year GENERATED ALWAYS AS (3*a - 2*a) STORED, index idx(a)) partition by list (1 + b - 1) (partition p0 values in (2001,2002,2003,2004,2005), partition p1 values in (2006,2007,2008,2009));
insert into t (a) values (1),(3),(5),(7),(9);
select a from t partition (p0) order by a;
a
2001
2003
2005
select a from t partition (p1) order by a;
a
2007
2009
select * from t where a = 1;
a	b
2001	2001
update t set a=a+1 where a = 1;
select a from t partition (p0) order by a;
a
2002
2003
2005
select a from t partition (p1) order by a;
a
2007
2009
select * from t where a = 1;
a	b
select * from t where a = 2;
a	b
2002	2002
delete from t where a>10;
select count(1) from t;
count(1)
5
delete from t where a=9;
select a from t partition (p1) order by a;
a
2007
select count(1) from t;
count(1)
4
insert into t (a) values (11);
Error 1526 (HY000): Table has no partition for value 2011
update t set a=a+10 where a = 2;
Error 1526 (HY000): Table has no partition for value 2012
delete from t;
insert into t (a) values (2001),(2003),(2005),(2007),(2009);
select a from t partition (p0) order by a;
a
2001
2003
2005
select a from t partition (p1) order by a;
a
2007
2009
select * from t where a = 2001;
a	b
2001	2001
update t set a=a+1 where a = 2001;
select a from t partition (p0) order by a;
a
2002
2003
2005
select a from t partition (p1) order by a;
a
2007
2009
select * from t where a = 2001;
a	b
select * from t where a = 2002;
a	b
2002	2002
delete from t where a>2010;
select count(1) from t;
count(1)
5
delete from t where a=2009;
select a from t partition (p1) order by a;
a
2007
select count(1) from t;
count(1)
4
insert into t (a) values (2011);
Error 1526 (HY000): Table has no partition for value 2011
update t set a=a+10 where a = 2002;
Error 1526 (HY000): Table has no partition for value 2012
drop table if exists t;
drop table if exists t;
create table t (a datetime, b bigint GENERATED ALWAYS AS (to_seconds(a)) VIRTUAL, index idx(a)) partition by list (1 + b - 1) (
partition p0 values in (to_seconds('2020-09-28 17:03:38'),to_seconds('2020-09-28 17:03:39')),
partition p1 values in (to_seconds('2020-09-28 17:03:40'),to_seconds('2020-09-28 17:03:41')));
insert into t (a) values  ('2020-09-28 17:03:38'),('2020-09-28 17:03:40');
select a from t partition (p0);
a
2020-09-28 17:03:38
select a from t where a = '2020-09-28 17:03:40';
a
2020-09-28 17:03:40
update t set a='2020-09-28 17:03:41' where a = '2020-09-28 17:03:38';
select a from t partition (p0);
a
select a from t partition (p1) order by a;
a
2020-09-28 17:03:40
2020-09-28 17:03:41
drop table if exists t;
create table t (a datetime, b bigint GENERATED ALWAYS AS (to_seconds(a)) STORED, index idx(a)) partition by list (1 + b - 1) (
partition p0 values in (to_seconds('2020-09-28 17:03:38'),to_seconds('2020-09-28 17:03:39')),
partition p1 values in (to_seconds('2020-09-28 17:03:40'),to_seconds('2020-09-28 17:03:41')));
insert into t (a) values  ('2020-09-28 17:03:38'),('2020-09-28 17:03:40');
select a from t partition (p0);
a
2020-09-28 17:03:38
select a from t where a = '2020-09-28 17:03:40';
a
2020-09-28 17:03:40
update t set a='2020-09-28 17:03:41' where a = '2020-09-28 17:03:38';
select a from t partition (p0);
a
select a from t partition (p1) order by a;
a
2020-09-28 17:03:40
2020-09-28 17:03:41
drop table if exists t;
create table t (a timestamp, b bigint GENERATED ALWAYS AS (to_seconds(a)) VIRTUAL, index idx(a)) partition by list (1 + b - 1) (
partition p0 values in (to_seconds('2020-09-28 17:03:38'),to_seconds('2020-09-28 17:03:39')),
partition p1 values in (to_seconds('2020-09-28 17:03:40'),to_seconds('2020-09-28 17:03:41')));
insert into t (a) values  ('2020-09-28 17:03:38'),('2020-09-28 17:03:40');
select a from t partition (p0);
a
2020-09-28 17:03:38
select a from t where a = '2020-09-28 17:03:40';
a
2020-09-28 17:03:40
update t set a='2020-09-28 17:03:41' where a = '2020-09-28 17:03:38';
select a from t partition (p0);
a
select a from t partition (p1) order by a;
a
2020-09-28 17:03:40
2020-09-28 17:03:41
drop table if exists t;
create table t (a timestamp, b bigint GENERATED ALWAYS AS (to_seconds(a)) STORED, index idx(a)) partition by list (1 + b - 1) (
partition p0 values in (to_seconds('2020-09-28 17:03:38'),to_seconds('2020-09-28 17:03:39')),
partition p1 values in (to_seconds('2020-09-28 17:03:40'),to_seconds('2020-09-28 17:03:41')));
insert into t (a) values  ('2020-09-28 17:03:38'),('2020-09-28 17:03:40');
select a from t partition (p0);
a
2020-09-28 17:03:38
select a from t where a = '2020-09-28 17:03:40';
a
2020-09-28 17:03:40
update t set a='2020-09-28 17:03:41' where a = '2020-09-28 17:03:38';
select a from t partition (p0);
a
select a from t partition (p1) order by a;
a
2020-09-28 17:03:40
2020-09-28 17:03:41
drop table if exists t;
create table t (a timestamp, b bigint GENERATED ALWAYS AS (to_seconds(a)) VIRTUAL, index idx(a)) partition by list columns(b) (
partition p0 values in (to_seconds('2020-09-28 17:03:38'),to_seconds('2020-09-28 17:03:39')),
partition p1 values in (to_seconds('2020-09-28 17:03:40'),to_seconds('2020-09-28 17:03:41')));
insert into t (a) values  ('2020-09-28 17:03:38'),('2020-09-28 17:03:40');
select a from t partition (p0);
a
2020-09-28 17:03:38
select a from t where a = '2020-09-28 17:03:40';
a
2020-09-28 17:03:40
update t set a='2020-09-28 17:03:41' where a = '2020-09-28 17:03:38';
select a from t partition (p0);
a
select a from t partition (p1) order by a;
a
2020-09-28 17:03:40
2020-09-28 17:03:41
drop table if exists t;
create table t (a timestamp, b bigint GENERATED ALWAYS AS (to_seconds(a)) STORED, index idx(a)) partition by list columns(b) (
partition p0 values in (to_seconds('2020-09-28 17:03:38'),to_seconds('2020-09-28 17:03:39')),
partition p1 values in (to_seconds('2020-09-28 17:03:40'),to_seconds('2020-09-28 17:03:41')));
insert into t (a) values  ('2020-09-28 17:03:38'),('2020-09-28 17:03:40');
select a from t partition (p0);
a
2020-09-28 17:03:38
select a from t where a = '2020-09-28 17:03:40';
a
2020-09-28 17:03:40
update t set a='2020-09-28 17:03:41' where a = '2020-09-28 17:03:38';
select a from t partition (p0);
a
select a from t partition (p1) order by a;
a
2020-09-28 17:03:40
2020-09-28 17:03:41
drop table if exists t;
drop table if exists t;
create table t (a varchar(10), b varchar(1) GENERATED ALWAYS AS (substr(a,1,1)) VIRTUAL, index idx(a)) partition by list columns(b) (partition p0 values in ('a','c'), partition p1 values in ('b','d'));
insert into t (a) values  ('aaa'),('abc'),('acd');
select a from t partition (p0) order by a;
a
aaa
abc
acd
select * from t where a = 'abc' order by a;
a	b
abc	a
update t set a='bbb' where a = 'aaa';
select a from t partition (p0) order by a;
a
abc
acd
select a from t partition (p1) order by a;
a
bbb
select * from t where a = 'bbb' order by a;
a	b
bbb	b
drop table if exists t;
create table t (a varchar(10), b varchar(1) GENERATED ALWAYS AS (substr(a,1,1)) STORED, index idx(a)) partition by list columns(b) (partition p0 values in ('a','c'), partition p1 values in ('b','d'));
insert into t (a) values  ('aaa'),('abc'),('acd');
select a from t partition (p0) order by a;
a
aaa
abc
acd
select * from t where a = 'abc' order by a;
a	b
abc	a
update t set a='bbb' where a = 'aaa';
select a from t partition (p0) order by a;
a
abc
acd
select a from t partition (p1) order by a;
a
bbb
select * from t where a = 'bbb' order by a;
a	b
bbb	b
drop table if exists t;
drop table if exists t12;
create table t12(d decimal(15,2));
insert into t12 values('1,9999.00');
Error 1366 (HY000): Incorrect decimal value: '1,9999.00' for column 'd' at row 1
set sql_mode='';
insert into t12 values('1,999.00');
SELECT * FROM t12;
d
1.00
drop table t12;
set sql_mode=default;
drop table if exists t, t1;
create table t(a varchar(1), index idx(a));
create table t1(a varchar(5), index idx(a));
insert into t values('a'), ('b');
insert into t1 values('a'), ('bbbbb');
update /*+ INL_JOIN(t) */ t, t1 set t.a='a' where t.a=t1.a;
select * from t;
a
a
b
update /*+ INL_HASH_JOIN(t) */ t, t1 set t.a='a' where t.a=t1.a;
select * from t;
a
a
b
update /*+ INL_MERGE_JOIN(t) */ t, t1 set t.a='a' where t.a=t1.a;
Level	Code	Message
Warning	1815	The INDEX MERGE JOIN hint is deprecated for usage, try other hints.
select * from t;
a
a
b
drop table if exists t, t1;
drop table if exists t1;
create table t1(a varchar(10) collate utf8mb4_general_ci);
insert into t1 values ('a');
update t1 set a = 'A';
select * from t1;
a
A
drop table t1;
drop table if exists t1;
set @@tidb_enable_clustered_index = 'int_only';
create table t1 (i varchar(20) unique key) collate=utf8mb4_general_ci;
insert into t1 values ('a');
replace into t1 values ('A');
select * from t1;
i
A
drop table t1;
drop table if exists t1, t2, t3;
create table t1 ( c_int int, c_str varchar(40) character set ascii collate ascii_bin, primary key(c_int, c_str(8)) clustered , unique key(c_str));
create table t2 ( c_int int, c_str varchar(40) character set latin1 collate latin1_bin, primary key(c_int, c_str(8)) clustered , unique key(c_str));
insert into t1 values (3, 'fervent brattain');
insert into t2 values (3, 'fervent brattain');
admin check table t1;
admin check table t2;
create table t3 (x varchar(40) CHARACTER SET ascii COLLATE ascii_bin, UNIQUE KEY uk(x(4)));
insert into t3 select 'abc ';
insert into t3 select 'abc d';
Error 1062 (23000): Duplicate entry 'abc ' for key 't3.uk'
drop table if exists t1, t2, t3;
drop table if exists t_int;
create table t_int(column1 int, column2 int unsigned generated always as(column1-100));
set @@sql_mode = DEFAULT;
insert into t_int(column1) values (99);
Error 1264 (22003): Out of range value for column 'column2' at row 1
set @@sql_mode = '';
insert into t_int(column1) values (99);
Level	Code	Message
Warning	1264	Out of range value for column 'column2' at row 1
select * from t_int;
column1	column2
99	0
drop table if exists t_float;
create table t_float(column1 float, column2 int unsigned generated always as(column1-100));
set @@sql_mode = DEFAULT;
insert into t_float(column1) values (12.95);
Error 1264 (22003): Out of range value for column 'column2' at row 1
set @@sql_mode = '';
insert into t_float(column1) values (12.95);
Level	Code	Message
Warning	1264	Out of range value for column 'column2' at row 1
select * from t_float;
column1	column2
12.95	0
drop table if exists t_decimal;
create table t_decimal(column1 decimal(20,10), column2 int unsigned generated always as(column1-100));
set @@sql_mode = DEFAULT;
insert into t_decimal(column1) values (123.456e-2);
Error 1264 (22003): Out of range value for column 'column2' at row 1
set @@sql_mode = '';
insert into t_decimal(column1) values (123.456e-2);
Level	Code	Message
Warning	1264	Out of range value for column 'column2' at row 1
select * from t_decimal;
column1	column2
1.2345600000	0
drop table if exists t_varchar;
create table t_varchar(column1 varchar(10), column2 int unsigned generated always as(column1-100));
set @@sql_mode = DEFAULT;
insert into t_varchar(column1) values ('87.12');
Error 1264 (22003): Out of range value for column 'column2' at row 1
set @@sql_mode = '';
insert into t_varchar(column1) values ('87.12');
Level	Code	Message
Warning	1264	Out of range value for column 'column2' at row 1
select * from t_varchar;
column1	column2
87.12	0
drop table if exists t_union;
create table t_union(column1 float, column2 int unsigned generated always as(column1-100), column3 float unsigned generated always as(column1-100));
set @@sql_mode = DEFAULT;
insert into t_union(column1) values (12.95);
Error 1264 (22003): Out of range value for column 'column2' at row 1
set @@sql_mode = '';
insert into t_union(column1) values (12.95);
Level	Code	Message
Warning	1264	Out of range value for column 'column2' at row 1
Warning	1264	Out of range value for column 'column3' at row 1
select * from t_union;
column1	column2	column3
12.95	0	0
set @@sql_mode = default;
drop table t_int, t_float, t_decimal, t_varchar, t_union;
drop table if exists t;
create table t (a timestamp on update current_timestamp(0), b int, primary key (a) clustered, key idx (a));
insert into t values ('2023-06-11 10:00:00', 1);
update t force index(primary) set b = 10 where a = '2023-06-11 10:00:00';
admin check table t;
drop table if exists t;
drop table if exists t_securities;
create table t_securities(id bigint not null auto_increment primary key, security_id varchar(8), market_id smallint, security_type int, unique key uu(security_id, market_id));
insert into t_securities (security_id, market_id, security_type) values ("1", 2, 7), ("7", 1, 7) ON DUPLICATE KEY UPDATE security_type = VALUES(security_type);
replace into t_securities (security_id, market_id, security_type) select security_id+1, 1, security_type from t_securities  where security_id="7";
INSERT INTO t_securities (security_id, market_id, security_type) values ("1", 2, 7), ("7", 1, 7) ON DUPLICATE KEY UPDATE security_type = VALUES(security_type);
select * from t_securities;
id	security_id	market_id	security_type
1	1	2	7
2	7	1	7
3	8	1	7
insert into t_securities (security_id, market_id, security_type) values ("1", 2, 7), ("7", 1, 7) ON DUPLICATE KEY UPDATE security_type = VALUES(security_type);
insert into t_securities (security_id, market_id, security_type) select security_id+2, 1, security_type from t_securities  where security_id="7";
INSERT INTO t_securities (security_id, market_id, security_type) values ("1", 2, 7), ("7", 1, 7) ON DUPLICATE KEY UPDATE security_type = VALUES(security_type);
select * from t_securities;
id	security_id	market_id	security_type
1	1	2	7
2	7	1	7
3	8	1	7
8	9	1	7
drop table if exists t;
create table t (a bigint key auto_random (3), b int) partition by list (a%5) (partition p0 values in (0,1,2), partition p1 values in (3,4));
set @@allow_auto_random_explicit_insert = true;
replace into t values  (1,1);
insert into t (b) values (2);
insert into t (b) values (3);
insert into t (b) values (4);
insert into t (b) values (5);
insert into t (b) values (6);
insert into t (b) values (7);
insert into t (b) values (8);
insert into t (b) values (9);
select b from t order by b;
b
1
2
3
4
5
6
7
8
9
update t set b=b+1 where a=1;
select b from t where a=1;
b
2
update t set b=b+1 where a<2;
select b from t where a<2;
b
3
insert into t values (1, 1) on duplicate key update b=b+1;
select b from t where a=1;
b
4
set @@allow_auto_random_explicit_insert = default;
drop table if exists t;
create table t (a bigint key auto_increment, b int) partition by list (a%5) (partition p0 values in (0,1,2), partition p1 values in (3,4));
set @@allow_auto_random_explicit_insert = true;
replace into t values  (1,1);
insert into t (b) values (2);
insert into t (b) values (3);
insert into t (b) values (4);
insert into t (b) values (5);
insert into t (b) values (6);
insert into t (b) values (7);
insert into t (b) values (8);
insert into t (b) values (9);
select b from t order by b;
b
1
2
3
4
5
6
7
8
9
update t set b=b+1 where a=1;
select b from t where a=1;
b
2
update t set b=b+1 where a<2;
select b from t where a<2;
b
3
insert into t values (1, 1) on duplicate key update b=b+1;
select b from t where a=1;
b
4
set @@allow_auto_random_explicit_insert = default;
drop table if exists replace_test;
create table replace_test (id int PRIMARY KEY AUTO_INCREMENT, c1 int, c2 int, c3 int default 1);
replace replace_test (c1) values (1),(2),(NULL);
affected rows: 3
info: Records: 3  Duplicates: 0  Warnings: 0
begin;
replace replace_test (c1) values ();
Error 1136 (21S01): Column count doesn't match value count at row 1
rollback;
begin;
replace replace_test (c1, c2) values (1,2),(1);
Error 1136 (21S01): Column count doesn't match value count at row 2
rollback;
begin;
replace replace_test (xxx) values (3);
Error 1054 (42S22): Unknown column 'xxx' in 'field list'
rollback;
begin;
replace replace_test_xxx (c1) values ();
Error 1146 (42S02): Table 'executor__write.replace_test_xxx' doesn't exist
rollback;
replace replace_test set c1 = 3;
affected rows: 1
info: 
begin;
replace replace_test set c1 = 4, c1 = 5;
Error 1110 (42000): Column 'c1' specified twice
rollback;
begin;
replace replace_test set xxx = 6;
Error 1054 (42S22): Unknown column 'xxx' in 'field list'
rollback;
drop table if exists replace_test_1;
create table replace_test_1 (id int, c1 int);
replace replace_test_1 select id, c1 from replace_test;
affected rows: 4
info: Records: 4  Duplicates: 0  Warnings: 0
begin;
replace replace_test_1 select c1 from replace_test;
Error 1136 (21S01): Column count doesn't match value count at row 1
rollback;
create table replace_test_2 (id int, c1 int);
replace replace_test_1 select id, c1 from replace_test union select id * 10, c1 * 10 from replace_test;
affected rows: 8
info: Records: 8  Duplicates: 0  Warnings: 0
drop table if exists replace_test_3;
create table replace_test_3 (c1 int, c2 int, UNIQUE INDEX (c2));
replace into replace_test_3 set c2=1;
affected rows: 1
info: 
replace into replace_test_3 set c2=1;
affected rows: 1
info: 
replace into replace_test_3 set c1=1, c2=1;
affected rows: 2
info: 
replace into replace_test_3 set c2=NULL;
affected rows: 1
info: 
replace into replace_test_3 set c2=NULL;
affected rows: 1
info: 
drop table if exists replace_test_4;
create table replace_test_4 (c1 int, c2 int, c3 int, UNIQUE INDEX (c1, c2));
replace into replace_test_4 set c2=NULL;
affected rows: 1
info: 
replace into replace_test_4 set c2=NULL;
affected rows: 1
info: 
drop table if exists replace_test_5;
create table replace_test_5 (c1 int, c2 int, c3 int, PRIMARY KEY (c1, c2));
replace into replace_test_5 set c1=1, c2=2;
affected rows: 1
info: 
replace into replace_test_5 set c1=1, c2=2;
affected rows: 1
info: 
drop table if exists tIssue989;
CREATE TABLE tIssue989 (a int, b int, PRIMARY KEY(a), UNIQUE KEY(b));
insert into tIssue989 (a, b) values (1, 2);
affected rows: 1
info: 
replace into tIssue989(a, b) values (111, 2);
affected rows: 2
info: 
select * from tIssue989;
a	b
111	2
drop table if exists tIssue1012;
CREATE TABLE tIssue1012 (a int, b int, PRIMARY KEY(a), UNIQUE KEY(b));
insert into tIssue1012 (a, b) values (1, 2);
insert into tIssue1012 (a, b) values (2, 1);
replace into tIssue1012(a, b) values (1, 1);
affected rows: 3
info: 
select * from tIssue1012;
a	b
1	1
drop table if exists t1;
create table t1(a int primary key, b int);
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
replace into t1 values(1,1);
affected rows: 1
info: 
replace into t1 values(1,1),(2,2);
affected rows: 2
info: Records: 2  Duplicates: 0  Warnings: 0
replace into t1 values(4,14),(5,15),(6,16),(7,17),(8,18);
affected rows: 7
info: Records: 5  Duplicates: 2  Warnings: 0
replace into t1 select * from (select 1, 2) as tmp;
affected rows: 2
info: Records: 1  Duplicates: 1  Warnings: 0
drop table if exists t1, t2;
create table t1 (a int primary key, b int default 20, c int default 30);
insert into t1 value (1, 2, 3);
replace t1 set a=1, b=default;
select * from t1;
a	b	c
1	20	30
replace t1 set a=2, b=default, c=default;
select * from t1;
a	b	c
1	20	30
2	20	30
replace t1 set a=2, b=default(c), c=default(b);
select * from t1;
a	b	c
1	20	30
2	30	20
replace t1 set a=default(b)+default(c);
select * from t1;
a	b	c
1	20	30
2	30	20
50	20	30
create table t2 (pk int primary key, a int default 1, b int generated always as (-a) virtual, c int generated always as (-a) stored);
replace t2 set pk=1, b=default;
select * from t2;
pk	a	b	c
1	1	-1	-1
replace t2 set pk=2, a=10, b=default;
select * from t2;
pk	a	b	c
1	1	-1	-1
2	10	-10	-10
replace t2 set pk=2, c=default, a=20;
select * from t2;
pk	a	b	c
1	1	-1	-1
2	20	-20	-20
replace t2 set pk=2, a=default, b=default, c=default;
select * from t2;
pk	a	b	c
1	1	-1	-1
2	1	-1	-1
replace t2 set pk=3, a=default(a), b=default, c=default;
select * from t2;
pk	a	b	c
1	1	-1	-1
2	1	-1	-1
3	1	-1	-1
replace t2 set b=default(a);
Error 3105 (HY000): The value specified for generated column 'b' in table 't2' is not allowed.
replace t2 set a=default(b), b=default(b);
Error 3105 (HY000): The value specified for generated column 'a' in table 't2' is not allowed.
replace t2 set a=default(a), c=default(c);
Error 1364 (HY000): Field 'pk' doesn't have a default value
replace t2 set c=default(a);
Error 3105 (HY000): The value specified for generated column 'c' in table 't2' is not allowed.
drop table t1, t2;
drop table if exists t;
create table t (a date default current_date);
insert into t values();
select count(1) from t where a = date(a);
count(1)
1