github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/insert/insert_ignore.result (about) 1 create table insert_ignore_01(c1 int not null auto_increment primary key,c2 varchar(10)); 2 insert into insert_ignore_01(c2) values("a"),("b"),("c"),("d"); 3 insert ignore into insert_ignore_01 values(3,"e"),(6,"f"),(1,"g"); 4 insert ignore into insert_ignore_01(c2) values("h"),("g"),("k"); 5 insert ignore into insert_ignore_01 values(NULL,NULL); 6 select * from insert_ignore_01; 7 c1 c2 8 1 a 9 2 b 10 3 c 11 4 d 12 6 f 13 7 h 14 8 g 15 9 k 16 10 null 17 drop table insert_ignore_01; 18 create table insert_ignore_01 (part_id INT NOT NULL,color VARCHAR(20) NOT NULL,quantity INT,PRIMARY KEY (part_id, color)); 19 insert ignore into insert_ignore_01 (part_id, color, quantity)values(1, 'Red', 10),(1, 'Blue', 20),(2, 'Green', 15),(1, 'Red', 5); 20 select * from insert_ignore_01; 21 part_id color quantity 22 1 Blue 20 23 1 Red 10 24 2 Green 15 25 create table insert_ignore_02(c1 int,c2 decimal(6,2),unique key(c1)); 26 insert into insert_ignore_02 values(100,1234.56),(200,2345.67),(300,3456.78),(400,4567.89),(NULL,33.00); 27 insert ignore into insert_ignore_02 values(100,1234.56),(200,23.7),(500,56.7),(600,6.9); 28 insert ignore into insert_ignore_02 values(700,1.56),(800,3.7); 29 insert ignore into insert_ignore_02 values(NULL,44.56); 30 select * from insert_ignore_02; 31 c1 c2 32 100 1234.56 33 200 2345.67 34 300 3456.78 35 400 4567.89 36 null 33.00 37 500 56.70 38 600 6.90 39 700 1.56 40 800 3.70 41 null 44.56 42 create table insert_ignore_03(c1 int auto_increment primary key,c2 int,key(c2)); 43 insert into insert_ignore_03(c2) values(2),(2),(5),(10),(12),(NULL); 44 insert ignore into insert_ignore_03(c2) values(7),(2),(5),(10),(12),(NULL); 45 select * from insert_ignore_03; 46 c1 c2 47 6 null 48 12 null 49 1 2 50 2 2 51 8 2 52 3 5 53 9 5 54 7 7 55 4 10 56 10 10 57 5 12 58 11 12 59 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)); 60 insert ignore into insert_ignore_04(product_name, price) VALUES('Laptop', 1200.00),('Monitor', 150.00),('Keyboard', NULL),('Mouse', 15.00); 61 insert ignore into insert_ignore_04(product_name, quantity_in_stock,price) VALUES(NULL, 5,1200.00),('board',6, NULL),('phone',NULL,1500.00); 62 select * from insert_ignore_04; 63 product_id product_name quantity_in_stock price 64 1 Laptop 0 1200.00 65 2 Monitor 0 150.00 66 3 Keyboard 0 0.00 67 4 Mouse 0 15.00 68 5 5 1200.00 69 6 board 6 0.00 70 7 phone null 1500.00 71 create table parent_table(parent_id INT AUTO_INCREMENT PRIMARY KEY,parent_name VARCHAR(255) NOT NULL); 72 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) 73 ); 74 insert ignore into parent_table (parent_name) VALUES ('Parent 1'), ('Parent 2'), ('Parent 3'); 75 insert ignore into child_table (child_name, parent_id) VALUES('Child 1', 1),('Child 2', 2),('Child 3', 4),('Child 4', 1); 76 select * from parent_table; 77 parent_id parent_name 78 1 Parent 1 79 2 Parent 2 80 3 Parent 3 81 select * from child_table; 82 child_id child_name parent_id 83 1 Child 1 1 84 2 Child 2 2 85 3 Child 4 1 86 insert ignore into insert_ignore_02 values(1234.56); 87 Column count doesn't match value count at row 1 88 insert ignore into insert_ignore_02 values("abc",1234.56); 89 insert ignore into insert_ignore_02 select "abc",34.22; 90 insert ignore into insert_ignore values("abc",1234.56); 91 no such table insert_ignore.insert_ignore 92 create table insert_ignore_05(id TINYINT,created_at DATETIME); 93 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'); 94 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'); 95 select * from insert_ignore_05; 96 id created_at 97 127 2024-04-03 10:00:00 98 -128 2024-04-03 11:00:00 99 100 2024-04-03 12:00:00 100 50 9999-12-31 23:59:59 101 50 2000-02-29 10:00:00 102 50 2024-04-03 13:00:00 103 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)); 104 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'); 105 invalid input: Table has no partition for value from column_list 106 select * from insert_ignore_06; 107 sale_id product_id sale_amount sale_date 108 create table insert_ignore_07(c1 int primary key auto_increment, c2 int); 109 insert into insert_ignore_07(c2) select result from generate_series(1,100000) g; 110 create table insert_ignore_08(c1 int primary key, c2 int); 111 insert into insert_ignore_08 values(20,45),(21,55),(1,45),(6,22),(5,1),(1000,222),(99999,19); 112 insert ignore into insert_ignore_08 select * from insert_ignore_07; 113 select count(*) from insert_ignore_08; 114 count(*) 115 100000 116 select * from insert_ignore_08 where c2 in (45,55,22,1,222,19); 117 c1 c2 118 1 45 119 5 1 120 6 22 121 20 45 122 21 55 123 1000 222 124 99999 19