github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/auto_increment/auto_increment_columns.result (about) 1 drop table if exists t1; 2 create table t1( 3 a bigint primary key auto_increment, 4 b varchar(10) 5 ); 6 show create table t1; 7 Table Create Table 8 t1 CREATE TABLE `t1` (\n`a` BIGINT NOT NULL AUTO_INCREMENT,\n`b` VARCHAR(10) DEFAULT NULL,\nPRIMARY KEY (`a`)\n) 9 insert into t1(b) values ('bbb'); 10 insert into t1 values (1, 'ccc'); 11 Duplicate entry '1' for key 'a' 12 insert into t1 values (3, 'ccc'); 13 insert into t1(b) values ('bbb1111'); 14 select * from t1 order by a; 15 a b 16 1 bbb 17 3 ccc 18 4 bbb1111 19 insert into t1 values (2, 'aaaa1111'); 20 select * from t1 order by a; 21 a b 22 1 bbb 23 2 aaaa1111 24 3 ccc 25 4 bbb1111 26 insert into t1(b) values ('aaaa1111'); 27 select * from t1 order by a; 28 a b 29 1 bbb 30 2 aaaa1111 31 3 ccc 32 4 bbb1111 33 5 aaaa1111 34 insert into t1 values (100, 'xxxx'); 35 insert into t1(b) values ('xxxx'); 36 select * from t1 order by a; 37 a b 38 1 bbb 39 2 aaaa1111 40 3 ccc 41 4 bbb1111 42 5 aaaa1111 43 100 xxxx 44 101 xxxx 45 insert into t1 values (0, 'xxxx'); 46 insert into t1(b) values ('xxxx'); 47 insert into t1 values (-1000, 'yyy'); 48 select * from t1 order by a; 49 a b 50 -1000 yyy 51 0 xxxx 52 1 bbb 53 2 aaaa1111 54 3 ccc 55 4 bbb1111 56 5 aaaa1111 57 100 xxxx 58 101 xxxx 59 102 xxxx 60 insert into t1 values ('-2000', 'yyy'); 61 insert into t1 values ('200', 'yyy'); 62 insert into t1 values ('0', 'yyy'); 63 Duplicate entry '0' for key 'a' 64 select * from t1; 65 a b 66 1 bbb 67 3 ccc 68 4 bbb1111 69 2 aaaa1111 70 5 aaaa1111 71 100 xxxx 72 101 xxxx 73 0 xxxx 74 102 xxxx 75 -1000 yyy 76 -2000 yyy 77 200 yyy 78 insert into t1 values (NULL, 'yyy'); 79 select * from t1 order by a; 80 a b 81 -2000 yyy 82 -1000 yyy 83 0 xxxx 84 1 bbb 85 2 aaaa1111 86 3 ccc 87 4 bbb1111 88 5 aaaa1111 89 100 xxxx 90 101 xxxx 91 102 xxxx 92 200 yyy 93 201 yyy 94 update t1 set a=0 where b='ccc'; 95 Duplicate entry '0' for key 'a' 96 update t1 set a='200' where b='ccc'; 97 Duplicate entry '200' for key 'a' 98 insert into t1(b) values ('xefrsdfgds'); 99 select * from t1 order by a; 100 a b 101 -2000 yyy 102 -1000 yyy 103 0 xxxx 104 1 bbb 105 2 aaaa1111 106 3 ccc 107 4 bbb1111 108 5 aaaa1111 109 100 xxxx 110 101 xxxx 111 102 xxxx 112 200 yyy 113 201 yyy 114 202 xefrsdfgds 115 insert into t1 values (-9223372036854775808,'xefrsdfgds'); 116 insert into t1 values (-9223372036854775809,'xefrsdfgds'); 117 Data truncation: data out of range: data type int64, value '-9223372036854775809' 118 insert into t1 values (9223372036854775807,'xefrsdfgds'); 119 insert into t1 values (9223372036854775808,'xefrsdfgds'); 120 Data truncation: data out of range: data type int64, value '9223372036854775808' 121 insert into t1(b) values ('eeeee'); 122 Data truncation: data out of range: data type bigint, value 9223372036854775808 123 drop table t1; 124 drop table if exists t2; 125 create table t2 ( 126 c int primary key auto_increment, 127 d varchar(10) 128 ); 129 insert into t2 values (-2147483648, 'aaa'); 130 select * from t2 order by c; 131 c d 132 -2147483648 aaa 133 insert into t2 values (-2147483649, 'aaa'); 134 Data truncation: data out of range: data type int32, value '-2147483649' 135 insert into t2(d) values ('1111'); 136 select * from t2 order by c; 137 c d 138 -2147483648 aaa 139 1 1111 140 insert into t2 values(2147483647, 'bbb'); 141 insert into t2 values(2147483648, 'bbb'); 142 Data truncation: data out of range: data type int32, value '2147483648' 143 insert into t2(d) values ('22222'); 144 Data truncation: data out of range: data type int, value 2147483648 145 select * from t2 order by c; 146 c d 147 -2147483648 aaa 148 1 1111 149 2147483647 bbb 150 drop table t2; 151 drop table if exists t3; 152 create table t3( 153 a int primary key auto_increment, 154 b varchar(10) 155 ); 156 insert into t3 values (-19, 'aaa'); 157 insert into t3(b) values ('bbb'); 158 select * from t3 order by a; 159 a b 160 -19 aaa 161 1 bbb 162 delete from t3 where b='bbb'; 163 insert into t3(b) values ('bbb'); 164 select * from t3 order by a; 165 a b 166 -19 aaa 167 2 bbb 168 insert into t3 values (1, 'aaa'); 169 update t3 set a=10 where b='aaa'; 170 Duplicate entry '10' for key 'a' 171 update t3 set a=10 where b='bbb'; 172 select * from t3 order by a; 173 a b 174 -19 aaa 175 1 aaa 176 10 bbb 177 insert into t3 values (2,'ccc'); 178 select * from t3 order by a; 179 a b 180 -19 aaa 181 1 aaa 182 2 ccc 183 10 bbb 184 delete from t3; 185 insert into t3(b) values ('bbb'); 186 select * from t3 order by a; 187 a b 188 11 bbb 189 drop table t3; 190 drop table if exists t4; 191 create table t4( 192 a bigint auto_increment, 193 b varchar(10) 194 ); 195 insert into t4(b) values ('bbb'); 196 insert into t4 values (1, 'ccc'); 197 insert into t4 values (3, 'ccc'); 198 insert into t4(b) values ('bbb1111'); 199 select * from t4 order by a; 200 a b 201 1 bbb 202 1 ccc 203 3 ccc 204 4 bbb1111 205 insert into t4 values (2, 'aaaa1111'); 206 select * from t4 order by a; 207 a b 208 1 bbb 209 1 ccc 210 2 aaaa1111 211 3 ccc 212 4 bbb1111 213 insert into t4(b) values ('aaaa1111'); 214 select * from t4 order by a; 215 a b 216 1 bbb 217 1 ccc 218 2 aaaa1111 219 3 ccc 220 4 bbb1111 221 5 aaaa1111 222 insert into t4 values (100, 'xxxx'); 223 insert into t4(b) values ('xxxx'); 224 select * from t4 order by a; 225 a b 226 1 bbb 227 1 ccc 228 2 aaaa1111 229 3 ccc 230 4 bbb1111 231 5 aaaa1111 232 100 xxxx 233 101 xxxx 234 insert into t4 values (0, 'xxxx'); 235 insert into t4(b) values ('xxxx'); 236 insert into t4 values (-1000, 'yyy'); 237 select * from t4 order by a; 238 a b 239 -1000 yyy 240 0 xxxx 241 1 bbb 242 1 ccc 243 2 aaaa1111 244 3 ccc 245 4 bbb1111 246 5 aaaa1111 247 100 xxxx 248 101 xxxx 249 102 xxxx 250 insert into t4 values ('-2000', 'yyy'); 251 insert into t4 values ('200', 'yyy'); 252 insert into t4 values ('0', 'yyy'); 253 select * from t4 order by a; 254 a b 255 -2000 yyy 256 -1000 yyy 257 0 yyy 258 0 xxxx 259 1 ccc 260 1 bbb 261 2 aaaa1111 262 3 ccc 263 4 bbb1111 264 5 aaaa1111 265 100 xxxx 266 101 xxxx 267 102 xxxx 268 200 yyy 269 insert into t4 values (NULL, 'yyy'); 270 select * from t4 order by a; 271 a b 272 -2000 yyy 273 -1000 yyy 274 0 yyy 275 0 xxxx 276 1 ccc 277 1 bbb 278 2 aaaa1111 279 3 ccc 280 4 bbb1111 281 5 aaaa1111 282 100 xxxx 283 101 xxxx 284 102 xxxx 285 200 yyy 286 201 yyy 287 update t4 set a=0 where b='ccc'; 288 update t4 set a='200' where b='ccc'; 289 insert into t4(b) values ('xefrsdfgds'); 290 select * from t4 order by a; 291 a b 292 -2000 yyy 293 -1000 yyy 294 0 yyy 295 0 xxxx 296 1 bbb 297 2 aaaa1111 298 4 bbb1111 299 5 aaaa1111 300 100 xxxx 301 101 xxxx 302 102 xxxx 303 200 yyy 304 200 ccc 305 200 ccc 306 201 yyy 307 202 xefrsdfgds 308 insert into t4 values (-9223372036854775808,'xefrsdfgds'); 309 insert into t4 values (-9223372036854775809,'xefrsdfgds'); 310 Data truncation: data out of range: data type int64, value '-9223372036854775809' 311 insert into t4 values (9223372036854775807,'xefrsdfgds'); 312 insert into t4 values (9223372036854775808,'xefrsdfgds'); 313 Data truncation: data out of range: data type int64, value '9223372036854775808' 314 insert into t4(b) values ('eeeee'); 315 Data truncation: data out of range: data type bigint, value 9223372036854775808 316 drop table t4; 317 drop table if exists t5; 318 create table t5 ( 319 c int auto_increment, 320 d varchar(10) 321 ); 322 insert into t5 values (-2147483648, 'aaa'); 323 select * from t5 order by c; 324 c d 325 -2147483648 aaa 326 insert into t5 values (-2147483649, 'aaa'); 327 Data truncation: data out of range: data type int32, value '-2147483649' 328 insert into t5(d) values ('1111'); 329 select * from t5 order by c; 330 c d 331 -2147483648 aaa 332 1 1111 333 insert into t5 values(2147483647, 'bbb'); 334 insert into t5 values(2147483648, 'bbb'); 335 Data truncation: data out of range: data type int32, value '2147483648' 336 select * from t5 order by c; 337 c d 338 -2147483648 aaa 339 1 1111 340 2147483647 bbb 341 insert into t5(d) values ('22222'); 342 Data truncation: data out of range: data type int, value 2147483648 343 select * from t5 order by c; 344 c d 345 -2147483648 aaa 346 1 1111 347 2147483647 bbb 348 drop table t5; 349 drop table if exists t6; 350 create table t6( 351 a int primary key auto_increment, 352 b bigint auto_increment, 353 c int auto_increment, 354 d int auto_increment, 355 e bigint auto_increment 356 ); 357 show create table t6; 358 Table Create Table 359 t6 CREATE TABLE `t6` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` BIGINT NOT NULL AUTO_INCREMENT,\n`c` INT NOT NULL AUTO_INCREMENT,\n`d` INT NOT NULL AUTO_INCREMENT,\n`e` BIGINT NOT NULL AUTO_INCREMENT,\nPRIMARY KEY (`a`)\n) 360 insert into t6 values (),(),(),(); 361 select * from t6 order by a; 362 a b c d e 363 1 1 1 1 1 364 2 2 2 2 2 365 3 3 3 3 3 366 4 4 4 4 4 367 insert into t6 values (NULL, NULL, NULL, NULL, NULL); 368 select * from t6 order by a; 369 a b c d e 370 1 1 1 1 1 371 2 2 2 2 2 372 3 3 3 3 3 373 4 4 4 4 4 374 5 5 5 5 5 375 insert into t6(b,c,d) values (NULL,NULL,NULL); 376 select * from t6 order by a; 377 a b c d e 378 1 1 1 1 1 379 2 2 2 2 2 380 3 3 3 3 3 381 4 4 4 4 4 382 5 5 5 5 5 383 6 6 6 6 6 384 insert into t6(a,b) values (100, 400); 385 select * from t6 order by a; 386 a b c d e 387 1 1 1 1 1 388 2 2 2 2 2 389 3 3 3 3 3 390 4 4 4 4 4 391 5 5 5 5 5 392 6 6 6 6 6 393 100 400 7 7 7 394 insert into t6(c,d,e) values (200, 200, 200); 395 select * from t6; 396 a b c d e 397 1 1 1 1 1 398 2 2 2 2 2 399 3 3 3 3 3 400 4 4 4 4 4 401 5 5 5 5 5 402 6 6 6 6 6 403 100 400 7 7 7 404 101 401 200 200 200 405 insert into t6(c,d,e) values (200, 400, 600); 406 select * from t6; 407 a b c d e 408 1 1 1 1 1 409 2 2 2 2 2 410 3 3 3 3 3 411 4 4 4 4 4 412 5 5 5 5 5 413 6 6 6 6 6 414 100 400 7 7 7 415 101 401 200 200 200 416 102 402 200 400 600 417 insert into t6(a,b) values (100, 400); 418 Duplicate entry '100' for key 'a' 419 select * from t6 order by a; 420 a b c d e 421 1 1 1 1 1 422 2 2 2 2 2 423 3 3 3 3 3 424 4 4 4 4 4 425 5 5 5 5 5 426 6 6 6 6 6 427 100 400 7 7 7 428 101 401 200 200 200 429 102 402 200 400 600 430 insert into t6 values ('0','0','0','0','0'); 431 select * from t6 order by a; 432 a b c d e 433 0 0 0 0 0 434 1 1 1 1 1 435 2 2 2 2 2 436 3 3 3 3 3 437 4 4 4 4 4 438 5 5 5 5 5 439 6 6 6 6 6 440 100 400 7 7 7 441 101 401 200 200 200 442 102 402 200 400 600 443 insert into t6 values ('a','a','a','a','a'); 444 invalid argument cast to int, bad value a 445 select * from t6 order by a; 446 a b c d e 447 0 0 0 0 0 448 1 1 1 1 1 449 2 2 2 2 2 450 3 3 3 3 3 451 4 4 4 4 4 452 5 5 5 5 5 453 6 6 6 6 6 454 100 400 7 7 7 455 101 401 200 200 200 456 102 402 200 400 600 457 insert into t6 values ('-1',0,0,0,0); 458 select * from t6 order by a; 459 a b c d e 460 -1 0 0 0 0 461 0 0 0 0 0 462 1 1 1 1 1 463 2 2 2 2 2 464 3 3 3 3 3 465 4 4 4 4 4 466 5 5 5 5 5 467 6 6 6 6 6 468 100 400 7 7 7 469 101 401 200 200 200 470 102 402 200 400 600 471 drop table t6; 472 drop table if exists t8; 473 create table t8( 474 a int auto_increment primary key, 475 b int auto_increment 476 ); 477 drop table if exists t9; 478 create table t9( 479 c int auto_increment primary key, 480 d int auto_increment 481 ); 482 insert into t8 values (),(); 483 select * from t8 order by a; 484 a b 485 1 1 486 2 2 487 insert into t9 values (),(); 488 select * from t9 order by c; 489 c d 490 1 1 491 2 2 492 insert into t8(a) values (19); 493 select * from t8 order by a; 494 a b 495 1 1 496 2 2 497 19 3 498 insert into t9 (c) values (19); 499 select * from t9 order by c; 500 c d 501 1 1 502 2 2 503 19 3 504 insert into t8 values (),(); 505 select * from t8 order by a; 506 a b 507 1 1 508 2 2 509 19 3 510 20 4 511 21 5 512 insert into t9 values (),(); 513 select * from t9 order by c; 514 c d 515 1 1 516 2 2 517 19 3 518 20 4 519 21 5 520 insert into t8(b) values (1); 521 select * from t8 order by a; 522 a b 523 1 1 524 2 2 525 19 3 526 20 4 527 21 5 528 22 1 529 insert into t9 (d) values (1); 530 select * from t9 order by c; 531 c d 532 1 1 533 2 2 534 19 3 535 20 4 536 21 5 537 22 1 538 insert into t8(a) values (1); 539 Duplicate entry '1' for key 'a' 540 select * from t8 order by a; 541 a b 542 1 1 543 2 2 544 19 3 545 20 4 546 21 5 547 22 1 548 insert into t9 (c) values (1); 549 Duplicate entry '1' for key 'c' 550 select * from t9 order by c; 551 c d 552 1 1 553 2 2 554 19 3 555 20 4 556 21 5 557 22 1 558 drop table t8; 559 drop table t9; 560 drop table if exists t10; 561 create table t10( 562 a int auto_increment primary key, 563 b int auto_increment 564 ); 565 insert into t10 values (10, 10); 566 insert into t10 values (),(),(); 567 select * from t10 order by a; 568 a b 569 10 10 570 11 11 571 12 12 572 13 13 573 truncate table t10; 574 insert into t10 values (); 575 select * from t10 order by a; 576 a b 577 1 1 578 drop table t10; 579 drop table if exists t11; 580 create table t11( 581 a int primary key auto_increment, 582 b bigint auto_increment, 583 c varchar(25) 584 ); 585 load data infile '$resources/auto_increment_columns/auto_increment_1.csv' into table t11 fields terminated by ','; 586 select * from t11 order by a; 587 a b c 588 1 1 '你好' 589 2 3 'hello' 590 4 4 'world' 591 5 5 ' 592 6 6 'N' 593 7 7 ' 594 8 8 '###' 595 9 9 596 10 10 597 100 100 '100' 598 101 101 599 drop table t11; 600 drop table if exists t12; 601 create table t12( 602 a int primary key auto_increment, 603 b bigint auto_increment, 604 c varchar(25) 605 ); 606 load data infile '$resources/auto_increment_columns/auto_increment_2.csv' into table t12 fields terminated by ','; 607 select * from t12 order by a; 608 a b c 609 0 0 '0' 610 1 1 '你好' 611 2 3 'hello' 612 4 4 'world' 613 5 5 ' 614 6 100 'aa' 615 10 101 'bb' 616 11 102 'cc' 617 1000 103 '1000' 618 1001 104 619 1002 105 620 drop table t12; 621 drop table if exists t13; 622 create table t13( 623 a int primary key auto_increment, 624 b bigint auto_increment, 625 c varchar(25) 626 ); 627 load data infile '$resources/auto_increment_columns/auto_increment_3.csv' into table t13 fields terminated by ','; 628 select * from t13 order by a; 629 a b c 630 1 1 '你好' 631 2 3 'hello' 632 4 4 'world' 633 5 5 'ccc' 634 6 6 '%%' 635 drop table t13; 636 create table t1(a int default(-1) auto_increment); 637 invalid input: invalid default value for 'a' 638 create table t1(a int primary key default(-1) auto_increment); 639 invalid input: invalid default value for 'a' 640 create table t1(a bigint default(-1) auto_increment); 641 invalid input: invalid default value for 'a' 642 create table t1(a bigint primary key default(-1) auto_increment); 643 invalid input: invalid default value for 'a' 644 create table t1(a int, b int default(10), c int auto_increment); 645 show create table t1; 646 Table Create Table 647 t1 CREATE TABLE `t1` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT (10),\n`c` INT NOT NULL AUTO_INCREMENT\n) 648 drop table t1; 649 650 create table t1(a tinyint auto_increment); 651 insert into t1 values(null), (3), (null), (6), (null), (127); 652 select * from t1; 653 a 654 1 655 3 656 4 657 6 658 7 659 127 660 insert into t1 values(); 661 Data truncation: data out of range: data type tinyint, value 128 662 663 drop table t1; 664 create table t1(a smallint auto_increment); 665 insert into t1 values(null), (3), (null), (6), (null), (32767); 666 select * from t1; 667 a 668 1 669 3 670 4 671 6 672 7 673 32767 674 insert into t1 values(); 675 Data truncation: data out of range: data type smallint, value 32768 676 677 drop table t1; 678 create table t1(a int auto_increment); 679 insert into t1 values(null), (3), (null), (6), (null), (2147483647); 680 select * from t1; 681 a 682 1 683 3 684 4 685 6 686 7 687 2147483647 688 insert into t1 values(); 689 Data truncation: data out of range: data type int, value 2147483648 690 691 drop table t1; 692 create table t1(a bigint auto_increment); 693 insert into t1 values(null), (3), (null), (6), (null), (9223372036854775807); 694 select * from t1; 695 a 696 1 697 3 698 4 699 6 700 7 701 9223372036854775807 702 insert into t1 values(); 703 Data truncation: data out of range: data type bigint, value 9223372036854775808 704 705 drop table t1; 706 create table t1(a tinyint unsigned auto_increment); 707 insert into t1 values(null), (3), (null), (6), (null), (255); 708 select * from t1; 709 a 710 1 711 3 712 4 713 6 714 7 715 255 716 insert into t1 values(); 717 Data truncation: data out of range: data type tinyint unsigned, value 256 718 719 drop table t1; 720 create table t1(a smallint unsigned auto_increment); 721 insert into t1 values(null), (3), (null), (6), (null), (65535); 722 select * from t1; 723 a 724 1 725 3 726 4 727 6 728 7 729 65535 730 insert into t1 values(); 731 Data truncation: data out of range: data type smallint unsigned, value 65536 732 733 drop table t1; 734 create table t1(a int unsigned auto_increment); 735 insert into t1 values(null), (3), (null), (6), (null), (4294967295); 736 select * from t1; 737 a 738 1 739 3 740 4 741 6 742 7 743 4294967295 744 insert into t1 values(); 745 Data truncation: data out of range: data type int unsigned, value 4294967296 746 747 drop table t1; 748 create table t1(a bigint unsigned auto_increment); 749 insert into t1 values(null), (3), (null), (6), (null), (18446744073709551615); 750 select * from t1; 751 a 752 1 753 3 754 4 755 6 756 7 757 18446744073709551615 758 insert into t1 values(); 759 Data truncation: data out of range: data type bigint unsigned, auto_incrment column constant value overflows bigint unsigned 760 drop table t1; 761 drop table if exists t1; 762 create table t1 (a int not null auto_increment, b int); 763 insert into t1(b) values (1); 764 select * from t1; 765 a b 766 1 1 767 drop table t1; 768 create table t1(a int auto_increment primary key); 769 insert into t1 values(); 770 select last_insert_id(); 771 last_insert_id() 772 1 773 insert into t1 values(11); 774 insert into t1 values(); 775 select last_insert_id(); 776 last_insert_id() 777 12 778 create table t2(a int auto_increment primary key); 779 insert into t2 values(); 780 select last_insert_id(); 781 last_insert_id() 782 1 783 insert into t2 values(100); 784 insert into t2 values(); 785 select last_insert_id(); 786 last_insert_id() 787 101 788 insert into t1 values(); 789 select last_insert_id(); 790 last_insert_id() 791 13 792 insert into t2 values(); 793 select last_insert_id(); 794 last_insert_id() 795 102 796 drop table if exists t1; 797 create table t1(a int auto_increment primary key) auto_increment = 0; 798 insert into t1 values(); 799 select last_insert_id(); 800 last_insert_id() 801 1 802 insert into t1 values(11); 803 insert into t1 values(); 804 select last_insert_id(); 805 last_insert_id() 806 12 807 drop table if exists t1; 808 create table t1(a int auto_increment primary key) auto_increment = 10; 809 insert into t1 values(); 810 select last_insert_id(); 811 last_insert_id() 812 10 813 insert into t1 values(101); 814 insert into t1 values(); 815 select last_insert_id(); 816 last_insert_id() 817 102 818 drop table if exists t1; 819 create table t1(a int auto_increment primary key) auto_increment = 100; 820 insert into t1 values(); 821 select last_insert_id(); 822 last_insert_id() 823 100 824 insert into t1 values(7); 825 insert into t1 values(); 826 select last_insert_id(); 827 last_insert_id() 828 101