github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/insert/insert_duplicate.result (about) 1 CREATE TABLE IF NOT EXISTS indup_00( 2 `id` INT UNSIGNED, 3 `act_name` VARCHAR(20) NOT NULL, 4 `spu_id` VARCHAR(30) NOT NULL, 5 `uv` BIGINT NOT NULL, 6 `update_time` date default '2020-10-10' COMMENT 'lastest time', 7 unique key idx_act_name_spu_id (act_name,spu_id) 8 ); 9 insert into indup_00 values (1,'beijing','001',1,'2021-01-03'),(2,'shanghai','002',2,'2022-09-23'),(3,'guangzhou','003',3,'2022-09-23'); 10 select * from indup_00; 11 id act_name spu_id uv update_time 12 1 beijing 001 1 2021-01-03 13 3 guangzhou 003 3 2022-09-23 14 2 shanghai 002 2 2022-09-23 15 insert into indup_00 values (4,'shenzheng','004',4,'2021-05-28'),(5,'beijing','010',5,'2022-10-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 16 select * from indup_00; 17 id act_name spu_id uv update_time 18 1 beijing 001 1 2021-01-03 19 5 beijing 010 5 2022-10-23 20 3 guangzhou 003 3 2022-09-23 21 2 shanghai 002 2 2022-09-23 22 4 shenzheng 004 4 2021-05-28 23 insert into indup_00 values (6,'shanghai','002',21,'1999-09-23'),(7,'guangzhou','003',31,'1999-09-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 24 select * from indup_00; 25 id act_name spu_id uv update_time 26 1 beijing 001 1 2021-01-03 27 5 beijing 010 5 2022-10-23 28 3 guangzhou 003 31 2022-09-23 29 2 shanghai 002 21 2022-09-23 30 4 shenzheng 004 4 2021-05-28 31 insert into indup_00 values (8,'shanghai','002',21,'1999-09-23') on duplicate key update `act_name`=NULL; 32 constraint violation: Column 'act_name' cannot be null 33 select * from indup_00; 34 id act_name spu_id uv update_time 35 1 beijing 001 1 2021-01-03 36 5 beijing 010 5 2022-10-23 37 3 guangzhou 003 31 2022-09-23 38 2 shanghai 002 21 2022-09-23 39 4 shenzheng 004 4 2021-05-28 40 insert into indup_00 values (9,'shanxi','005',4,'2022-10-08'),(10,'shandong','006',6,'2022-11-22') on duplicate key update `act_name`='Hongkong'; 41 select * from indup_00; 42 id act_name spu_id uv update_time 43 1 beijing 001 1 2021-01-03 44 5 beijing 010 5 2022-10-23 45 3 guangzhou 003 31 2022-09-23 46 10 shandong 006 6 2022-11-22 47 2 shanghai 002 21 2022-09-23 48 9 shanxi 005 4 2022-10-08 49 4 shenzheng 004 4 2021-05-28 50 insert into indup_00 values (10,'xinjiang','008',7,NULL),(11,'hainan','009',8,NULL) on duplicate key update `act_name`='Hongkong'; 51 select * from indup_00; 52 id act_name spu_id uv update_time 53 1 beijing 001 1 2021-01-03 54 5 beijing 010 5 2022-10-23 55 3 guangzhou 003 31 2022-09-23 56 11 hainan 009 8 null 57 10 shandong 006 6 2022-11-22 58 2 shanghai 002 21 2022-09-23 59 9 shanxi 005 4 2022-10-08 60 4 shenzheng 004 4 2021-05-28 61 10 xinjiang 008 7 null 62 CREATE TABLE IF NOT EXISTS indup_01( 63 `id` INT UNSIGNED, 64 `act_name` VARCHAR(20) NOT NULL, 65 `spu_id` VARCHAR(30) NOT NULL, 66 `uv` BIGINT NOT NULL, 67 `update_time` date default '2020-10-10' COMMENT 'lastest time', 68 PRIMARY KEY ( `id` ), 69 unique key idx_act_name_spu_id (act_name,spu_id) 70 ); 71 insert into indup_01 values (1,'beijing','001',1,'2021-01-03'),(2,'shanghai','002',2,'2022-09-23'),(3,'guangzhou','003',3,'2022-09-23'); 72 select * from indup_01; 73 id act_name spu_id uv update_time 74 1 beijing 001 1 2021-01-03 75 2 shanghai 002 2 2022-09-23 76 3 guangzhou 003 3 2022-09-23 77 insert into indup_01 values (4,'shenzheng','004',4,'2021-05-28'),(5,'beijing','010',5,'2022-10-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 78 select * from indup_01; 79 id act_name spu_id uv update_time 80 1 beijing 001 1 2021-01-03 81 2 shanghai 002 2 2022-09-23 82 3 guangzhou 003 3 2022-09-23 83 4 shenzheng 004 4 2021-05-28 84 5 beijing 010 5 2022-10-23 85 insert into indup_01 values (6,'shanghai','002',21,'1999-09-23'),(7,'guangzhou','003',31,'1999-09-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 86 select * from indup_01; 87 id act_name spu_id uv update_time 88 1 beijing 001 1 2021-01-03 89 2 shanghai 002 21 2022-09-23 90 3 guangzhou 003 31 2022-09-23 91 4 shenzheng 004 4 2021-05-28 92 5 beijing 010 5 2022-10-23 93 insert into indup_01 values (8,'shanghai','002',21,'1999-09-23') on duplicate key update `act_name`=NULL; 94 constraint violation: Column 'act_name' cannot be null 95 select * from indup_01; 96 id act_name spu_id uv update_time 97 1 beijing 001 1 2021-01-03 98 2 shanghai 002 21 2022-09-23 99 3 guangzhou 003 31 2022-09-23 100 4 shenzheng 004 4 2021-05-28 101 5 beijing 010 5 2022-10-23 102 insert into indup_01 values (9,'shanxi','005',4,'2022-10-08'),(10,'shandong','006',6,'2022-11-22') on duplicate key update `act_name`='Hongkong'; 103 select * from indup_01; 104 id act_name spu_id uv update_time 105 1 beijing 001 1 2021-01-03 106 2 shanghai 002 21 2022-09-23 107 3 guangzhou 003 31 2022-09-23 108 4 shenzheng 004 4 2021-05-28 109 5 beijing 010 5 2022-10-23 110 9 shanxi 005 4 2022-10-08 111 10 shandong 006 6 2022-11-22 112 insert into indup_01 values (10,'xinjiang','008',7,NULL),(11,'hainan','009',8,NULL) on duplicate key update `act_name`='Hongkong'; 113 select * from indup_01; 114 id act_name spu_id uv update_time 115 1 beijing 001 1 2021-01-03 116 2 shanghai 002 21 2022-09-23 117 3 guangzhou 003 31 2022-09-23 118 4 shenzheng 004 4 2021-05-28 119 5 beijing 010 5 2022-10-23 120 9 shanxi 005 4 2022-10-08 121 10 Hongkong 006 6 2022-11-22 122 11 hainan 009 8 null 123 CREATE TABLE IF NOT EXISTS indup_02( 124 col1 INT , 125 col2 VARCHAR(20) NOT NULL, 126 col3 VARCHAR(30) NOT NULL, 127 col4 BIGINT default 30, 128 PRIMARY KEY ( col1 ) 129 ); 130 insert into indup_02 values (1,'apple','left',NULL),(2,'bear','right',1000); 131 select * from indup_02; 132 col1 col2 col3 col4 133 1 apple left null 134 2 bear right 1000 135 insert into indup_02 select 1,'banana','lower',NULL on duplicate key update col1=col1*10; 136 select * from indup_02; 137 col1 col2 col3 col4 138 2 bear right 1000 139 10 apple left null 140 insert into indup_02(col1,col2,col3) values(2,'wechat','tower'),(3,'paper','up') on duplicate key update col1=col1+20,col3=values(col3); 141 select * from indup_02; 142 col1 col2 col3 col4 143 3 paper up 30 144 10 apple left null 145 22 bear tower 1000 146 insert into indup_02 values(3,'aaa','bbb',30) on duplicate key update col1=col1+7; 147 Duplicate entry '10' for key 'col1' 148 select * from indup_02; 149 col1 col2 col3 col4 150 3 paper up 30 151 10 apple left null 152 22 bear tower 1000 153 insert into indup_02 values(3,'aaa','bbb',30),(30,'abc','abc',10),(11,'a1','b1',300) on duplicate key update col1=col1*10,col4=0; 154 select * from indup_02; 155 col1 col2 col3 col4 156 10 apple left null 157 11 a1 b1 300 158 22 bear tower 1000 159 300 paper up 0 160 create table indup_tmp(col1 int,col2 varchar(20),col3 varchar(20)); 161 insert into indup_tmp values (1,'apple','left'),(2,'bear','right'),(3,'paper','up'),(10,'wine','down'),(300,'box','high'); 162 insert into indup_02(col1,col2,col3) select col1,col2,col3 from indup_tmp on duplicate key update indup_02.col3=left(indup_02.col3,2),col2='wow'; 163 select * from indup_02; 164 col1 col2 col3 col4 165 22 bear tower 1000 166 11 a1 b1 300 167 1 apple left 30 168 2 bear right 30 169 3 paper up 30 170 10 wow le null 171 300 wow up 0 172 delete from indup_02; 173 select * from indup_02; 174 col1 col2 col3 col4 175 insert into indup_02(col1,col2,col3) values(6,'app','uper'),(7,'light','') on duplicate key update col2=''; 176 select * from indup_02; 177 col1 col2 col3 col4 178 6 app uper 30 179 7 light 30 180 CREATE TABLE IF NOT EXISTS indup_03( 181 col1 varchar(25) , 182 col2 VARCHAR(20) NOT NULL, 183 col3 VARCHAR(30) , 184 col4 BIGINT default 30, 185 PRIMARY KEY (col1) 186 ); 187 insert into indup_03 values ('1','apple','left',NULL),('2','bear','right',1000); 188 insert into indup_03(col1,col2,col3) values(3,'paper','up'),('2','bear','right',1000),('1','sofa','high',NULL) on duplicate key update col2=values(col2),col3=values(col3); 189 Column count doesn't match value count at row 2 190 select * from indup_03; 191 col1 col2 col3 col4 192 1 apple left null 193 2 bear right 1000 194 insert into indup_03(col1,col2,col3) values(4,NULL,NULL) on duplicate key update col2=values(col2),col3=values(col3); 195 constraint violation: Column 'col2' cannot be null 196 insert into indup_03(col1,col2,col3) values(3,NULL,NULL) on duplicate key update col2=values(col2),col3=values(col3); 197 constraint violation: Column 'col2' cannot be null 198 select * from indup_03; 199 col1 col2 col3 col4 200 1 apple left null 201 2 bear right 1000 202 insert into indup_03(col1,col2,col3) values(2,'bear','left') on duplicate key update col1=1; 203 Duplicate entry '1' for key 'col1' 204 insert into indup_03(col1,col2,col3) values(1,'apple','') on duplicate key update col3='constant'; 205 select * from indup_03; 206 col1 col2 col3 col4 207 1 apple constant null 208 2 bear right 1000 209 insert into indup_03(col1,col2,col3) values(1,'apple','uuuu') on duplicate key update col3=NULL; 210 select * from indup_03; 211 col1 col2 col3 col4 212 1 apple null null 213 2 bear right 1000 214 insert into indup_03(col1,col2,col3) values(1,'apple','uuuu') on duplicate key update col3=''; 215 select * from indup_03; 216 col1 col2 col3 col4 217 1 apple null 218 2 bear right 1000 219 insert into indup_03(col1,col2,col3) values(1,'apple','uuuu') on duplicate key update col1=2+3; 220 select * from indup_03; 221 col1 col2 col3 col4 222 2 bear right 1000 223 5 apple null 224 CREATE TABLE IF NOT EXISTS indup_04( 225 `id` INT, 226 `act_name` VARCHAR(20) NOT NULL, 227 `spu_id` VARCHAR(30) NOT NULL, 228 `uv` BIGINT NOT NULL, 229 `update_time` date default '2020-10-10' COMMENT 'lastest time', 230 PRIMARY KEY ( `id`, `act_name`) 231 ); 232 insert into indup_04 values (1,'beijing','001',1,'2021-01-03'),(2,'shanghai','002',2,'2022-09-23'),(3,'guangzhou','003',3,'2022-09-23'); 233 select * from indup_04; 234 id act_name spu_id uv update_time 235 1 beijing 001 1 2021-01-03 236 2 shanghai 002 2 2022-09-23 237 3 guangzhou 003 3 2022-09-23 238 insert into indup_04 values (4,'shenzheng','004',4,'2021-05-28'),(1,'beijing','010',5,'2022-10-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 239 select * from indup_04; 240 id act_name spu_id uv update_time 241 2 shanghai 002 2 2022-09-23 242 3 guangzhou 003 3 2022-09-23 243 4 shenzheng 004 4 2021-05-28 244 1 beijing 010 5 2021-01-03 245 insert into indup_04 values (2,'shanghai','002',21,'1999-09-23'),(3,'guangzhou','003',31,'1999-09-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 246 select * from indup_04; 247 id act_name spu_id uv update_time 248 4 shenzheng 004 4 2021-05-28 249 1 beijing 010 5 2021-01-03 250 2 shanghai 002 21 2022-09-23 251 3 guangzhou 003 31 2022-09-23 252 insert into indup_04 values (2,'shanghai','002',21,'1999-09-23') on duplicate key update `act_name`=NULL; 253 constraint violation: Column 'act_name' cannot be null 254 select * from indup_04; 255 id act_name spu_id uv update_time 256 4 shenzheng 004 4 2021-05-28 257 1 beijing 010 5 2021-01-03 258 2 shanghai 002 21 2022-09-23 259 3 guangzhou 003 31 2022-09-23 260 insert into indup_04 values (5,'shanxi','005',4,'2022-10-08'),(6,'shandong','006',6,'2022-11-22') on duplicate key update `act_name`='Hongkong'; 261 select * from indup_04; 262 id act_name spu_id uv update_time 263 4 shenzheng 004 4 2021-05-28 264 1 beijing 010 5 2021-01-03 265 2 shanghai 002 21 2022-09-23 266 3 guangzhou 003 31 2022-09-23 267 5 shanxi 005 4 2022-10-08 268 6 shandong 006 6 2022-11-22 269 insert into indup_04 values (10,'xinjiang','008',7,NULL),(11,'hainan','009',8,NULL) on duplicate key update `act_name`='Hongkong'; 270 select * from indup_04; 271 id act_name spu_id uv update_time 272 4 shenzheng 004 4 2021-05-28 273 1 beijing 010 5 2021-01-03 274 2 shanghai 002 21 2022-09-23 275 3 guangzhou 003 31 2022-09-23 276 5 shanxi 005 4 2022-10-08 277 6 shandong 006 6 2022-11-22 278 10 xinjiang 008 7 null 279 11 hainan 009 8 null 280 create table indup_fk1(col1 int primary key,col2 varchar(25),col3 tinyint); 281 create table indup_fk2(col1 int,col2 varchar(25),col3 tinyint primary key,constraint ck foreign key(col1) REFERENCES indup_fk1(col1) on delete RESTRICT on update RESTRICT); 282 insert into indup_fk1 values (2,'yellow',20),(10,'apple',50),(11,'opppo',51); 283 insert into indup_fk2 values(2,'score',1),(2,'student',4),(10,'goods',2); 284 insert into indup_fk2 values(10,'food',1)on duplicate key update col1=50; 285 internal error: Cannot add or update a child row: a foreign key constraint fails 286 insert into indup_fk2 values(50,'food',1)on duplicate key update col1=values(col1); 287 internal error: Cannot add or update a child row: a foreign key constraint fails 288 select * from indup_fk1; 289 col1 col2 col3 290 2 yellow 20 291 10 apple 50 292 11 opppo 51 293 select * from indup_fk2; 294 col1 col2 col3 295 2 score 1 296 10 goods 2 297 2 student 4 298 drop table indup_fk2; 299 drop table indup_fk1; 300 CREATE TABLE IF NOT EXISTS indup_05( 301 col1 INT , 302 col2 VARCHAR(20) NOT NULL, 303 col3 VARCHAR(30) NOT NULL, 304 col4 BIGINT default 30 305 ); 306 insert into indup_05 values(22,'11','33',1), (23,'22','55',2),(24,'66','77',1),(25,'99','88',1),(22,'11','33',1) on duplicate key update col1=col1+col2; 307 insert into indup_05 values(22,'78','30',99) on duplicate key update col1=col1/2; 308 select * from indup_05; 309 col1 col2 col3 col4 310 22 11 33 1 311 23 22 55 2 312 24 66 77 1 313 25 99 88 1 314 22 11 33 1 315 22 78 30 99 316 create table indup_06(col1 int primary key,col2 int); 317 insert into indup_06 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17),(18,18),(19,19),(20,20); 318 insert into indup_06 values(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70),(8,80),(9,90),(10,100),(11,110),(12,120),(13,130),(14,140),(15,150),(16,160),(17,170),(18,180),(19,190),(20,200)on duplicate key update col1=col1+1; 319 constraint violation: Duplicate entry for key 'col1' 320 truncate table indup_06; 321 insert into indup_06 values(1,1); 322 insert into indup_06 values(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70),(8,80),(9,90),(10,100),(11,110),(12,120),(13,130),(14,140),(15,150),(16,160),(17,170),(18,180),(19,190),(20,200)on duplicate key update col1=col1+1; 323 insert into indup_06 values(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70),(8,80),(9,90),(10,100),(11,110),(12,120),(13,130),(14,140),(15,150),(16,160),(17,170),(18,180),(19,190),(20,200)on duplicate key update col1=col1+1,col2=col2*10; 324 insert into indup_06 values(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70),(8,80),(9,90),(10,100),(11,110),(12,120),(13,130),(14,140),(15,150),(16,160),(17,170),(18,180),(19,190),(20,200)on duplicate key update col1=col1+1,col2=col2/10; 325 constraint violation: Duplicate entry for key 'col1' 326 CREATE TABLE IF NOT EXISTS indup_07( 327 col1 INT primary key, 328 col2 VARCHAR(20) NOT NULL, 329 col3 VARCHAR(30) NOT NULL, 330 col4 BIGINT default 30 331 ); 332 insert into indup_07 values(22,'11','33',1), (23,'22','55',2),(24,'66','77',1),(25,'99','88',1),(22,'11','33',1) on duplicate key update col1=col1+col2; 333 select * from indup_07; 334 col1 col2 col3 col4 335 23 22 55 2 336 24 66 77 1 337 25 99 88 1 338 33 11 33 1 339 insert into indup_07 values(24,'1','1',100) on duplicate key update col1=2147483649; 340 Data truncation: data out of range: data type int32, value '2147483649' 341 prepare stmt1 from "insert into indup_07 values(?, '11', '33', 1)on duplicate key update col1=col1*10"; 342 set @a_var = 1; 343 execute stmt1 using @a_var; 344 select * from indup_07; 345 col1 col2 col3 col4 346 1 11 33 1 347 23 22 55 2 348 24 66 77 1 349 25 99 88 1 350 33 11 33 1 351 set @a_var = 23; 352 execute stmt1 using @a_var; 353 select * from indup_07; 354 col1 col2 col3 col4 355 1 11 33 1 356 24 66 77 1 357 25 99 88 1 358 33 11 33 1 359 230 22 55 2 360 deallocate prepare stmt1; 361 drop table if exists indup_08; 362 create table indup_08(a varchar, primary key (a)); 363 insert into indup_08 values ('matrixone\''), ('matrixone\''); 364 Duplicate entry 'matrixone'' for key 'a' 365 drop table if exists indup_09; 366 create table indup_09(a varchar, b varchar, primary key (a, b)); 367 insert into indup_09 values ('matrixone\'', 'mo-tester\''), ('matrixone\'', 'mo-tester\''); 368 Duplicate entry '(matrixone',mo-tester')' for key '__mo_cpkey_col' 369 drop table if exists indup_10; 370 create table indup_10(a varchar(256), b int); 371 insert into indup_10 (a, b) select "matrixone " || " some space " || result, 1 from generate_series (1, 500000)g; 372 drop table indup_10;