github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/insert/insert_ignore.sql (about)

     1  -- insert ignore autoincrement primary key
     2  -- @bvt:issue#15365
     3  create table insert_ignore_01(c1 int not null auto_increment primary key,c2 varchar(10));
     4  insert into insert_ignore_01(c2) values("a"),("b"),("c"),("d");
     5  insert ignore into insert_ignore_01 values(3,"e"),(6,"f"),(1,"g");
     6  insert ignore into insert_ignore_01(c2) values("h"),("g"),("k");
     7  insert ignore into insert_ignore_01 values(NULL,NULL);
     8  select * from insert_ignore_01;
     9  drop table insert_ignore_01;
    10  -- @bvt:issue
    11  -- insert ignore multi primary key
    12  create table insert_ignore_01 (part_id INT NOT NULL,color VARCHAR(20) NOT NULL,quantity INT,PRIMARY KEY (part_id, color));
    13  insert ignore into insert_ignore_01 (part_id, color, quantity)values(1, 'Red', 10),(1, 'Blue', 20),(2, 'Green', 15),(1, 'Red', 5);
    14  select * from insert_ignore_01;
    15  
    16  -- insert ignore unique key
    17  create table insert_ignore_02(c1 int,c2 decimal(6,2),unique key(c1));
    18  insert into insert_ignore_02 values(100,1234.56),(200,2345.67),(300,3456.78),(400,4567.89),(NULL,33.00);
    19  insert ignore into insert_ignore_02 values(100,1234.56),(200,23.7),(500,56.7),(600,6.9);
    20  insert ignore into insert_ignore_02 values(700,1.56),(800,3.7);
    21  insert ignore into insert_ignore_02 values(NULL,44.56);
    22  select * from insert_ignore_02;
    23  
    24  -- insert ignore secondary key
    25  -- @bvt:issue#15365
    26  create table insert_ignore_03(c1 int auto_increment primary key,c2 int,key(c2));
    27  insert into insert_ignore_03(c2) values(2),(2),(5),(10),(12),(NULL);
    28  insert ignore into insert_ignore_03(c2) values(7),(2),(5),(10),(12),(NULL);
    29  select * from insert_ignore_03;
    30  -- @bvt:issue
    31  -- insert ignore not null and default constraint
    32  -- @bvt:issue#15358
    33  create table insert_ignore_04 (product_id INT NOT NULL AUTO_INCREMENT,product_name VARCHAR(255) NOT NULL,quantity_in_stock INT DEFAULT 0,price DECIMAL(10, 2) NOT NULL,PRIMARY KEY (product_id));
    34  insert ignore into insert_ignore_04(product_name, price) VALUES('Laptop', 1200.00),('Monitor', 150.00),('Keyboard', NULL),('Mouse', 15.00);
    35  -- @bvt:issue
    36  -- @bvt:issue#15345
    37  insert ignore into insert_ignore_04(product_name, quantity_in_stock,price) VALUES(NULL, 5,1200.00),('board',6, NULL),('phone',NULL,1500.00);
    38  select * from insert_ignore_04;
    39  -- @bvt:issue
    40  -- insert ignore foreign key constraint
    41  -- @bvt:issue#15345
    42  create table parent_table(parent_id INT AUTO_INCREMENT PRIMARY KEY,parent_name VARCHAR(255) NOT NULL);
    43  create table child_table(child_id INT AUTO_INCREMENT PRIMARY KEY,child_name VARCHAR(255) NOT NULL,parent_id INT,FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id)
    44  );
    45  insert ignore into parent_table (parent_name) VALUES ('Parent 1'), ('Parent 2'), ('Parent 3');
    46  insert ignore into child_table (child_name, parent_id) VALUES('Child 1', 1),('Child 2', 2),('Child 3', 4),('Child 4', 1);
    47  select * from parent_table;
    48  select * from child_table;
    49  -- @bvt:issue
    50  -- syntax check
    51  insert ignore into insert_ignore_02 values(1234.56);
    52  -- @bvt:issue#15345
    53  insert ignore into insert_ignore_02 values("abc",1234.56);
    54  insert ignore into insert_ignore_02 select "abc",34.22;
    55  -- @bvt:issue
    56  insert ignore into insert_ignore values("abc",1234.56);
    57  
    58  -- insert ignore values out of type range
    59  -- @bvt:issue#15345
    60  create table insert_ignore_05(id TINYINT,created_at DATETIME);
    61  insert ignore INTO insert_ignore_05 (id, created_at) VALUES(130, '2024-04-03 10:00:00'),(-129, '2024-04-03 11:00:00'),(100, '2024-04-03 12:00:00');
    62  insert ignore INTO insert_ignore_05 (id, created_at) VALUES(50, '9999-12-31 23:59:59'), (50, '2000-02-29 10:00:00'),(50, '2024-04-03 13:00:00');
    63  select * from insert_ignore_05;
    64  -- @bvt:issue
    65  
    66  -- insert ignore partition table
    67  create table insert_ignore_06 (sale_id INT AUTO_INCREMENT,product_id INT,sale_amount DECIMAL(10, 2),sale_date DATE,PRIMARY KEY (sale_id, sale_date))PARTITION BY RANGE (year(sale_date)) (PARTITION p0 VALUES LESS THAN (1991),PARTITION p1 VALUES LESS THAN (1992),PARTITION p2 VALUES LESS THAN (1993),PARTITION p3 VALUES LESS THAN (1994));
    68  insert ignore into insert_ignore_06 (product_id, sale_amount, sale_date) VALUES(1, 1000.00, '1990-04-01'),(2, 1500.00, '1992-05-01'),(3, 500.00, '1995-06-01'),(1, 2000.00, '1991-07-01');
    69  select * from insert_ignore_06;
    70  -- insert ignore select from table
    71  -- @bvt:issue#15349
    72  create table insert_ignore_07(c1 int primary key auto_increment, c2 int);
    73  insert into insert_ignore_07(c2) select result from generate_series(1,100000) g;
    74  create table insert_ignore_08(c1 int primary key, c2 int);
    75  insert into insert_ignore_08 values(20,45),(21,55),(1,45),(6,22),(5,1),(1000,222),(99999,19);
    76  insert ignore into insert_ignore_08 select * from insert_ignore_07;
    77  select count(*) from insert_ignore_08;
    78  select * from insert_ignore_08 where c2 in (45,55,22,1,222,19);
    79  -- @bvt:issue