[MCOL-5281] INSERT IGNORE is incorrectly supported Created: 2022-10-27  Updated: 2023-10-27  Resolved: 2023-10-27

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 22.08.2
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Sergey Zefirov Assignee: Leonid Fedorov
Resolution: Won't Fix Votes: 0
Labels: beginner-friendly


 Description   

INSERT IGNORE performs insertion attempt for single row insert and results in "INSERT IGNORE is unsupported" for several rows insertion.

Here's session that demonstrate problematiic behavior:

MariaDB [(none)]> use test;
 
Database changed
 
MariaDB [test]> create table t(a int) engine =columnstore;
 
Query OK, 0 rows affected (0.352 sec)
 
 
 
MariaDB [test]> insert into t values (1),(2),(3);
 
Query OK, 3 rows affected (0.201 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> insert ignore into t values (1);
 
Query OK, 1 row affected (1.112 sec)
 
 
 
MariaDB [test]> insert ignore into t values (1),(2);
 
ERROR 1178 (42000): The storage engine for the table doesn't support IGNORE option in insert statement is not supported in Columnstore.
 
MariaDB [test]>



 Comments   
Comment by jiawei liang [ 2023-04-17 ]

Hello , I have found where the problem is and in my version of code it works like this.Is that ok? Or should I make "INSERT IGNORE is unsupported" for single row insert ?

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table t(a int) engine =columnstore;
Query OK, 0 rows affected (0.902 sec)

MariaDB [test]> insert into t values (1),(2),(3);
Query OK, 3 rows affected (0.237 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [test]> insert ignore into t values (1);
Query OK, 1 row affected (1.105 sec)

MariaDB [test]> insert ignore into t values (1),(2);
Query OK, 2 rows affected (0.158 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from t;
------

a

------

1
2
3
1
1
2

------
6 rows in set (0.048 sec)

Comment by Roman [ 2023-04-18 ]

Hey jiawei liang.
Thx for noticing this issue. The issue itself has multiple facets, namely there are multiple cases to test with IGNORE keyword. Here is the actual list. Those related to index errors are irrelevant for MCS b/c there is no index in MCS per-se but others must be tested. Especially those related to default values or saturation when the bigger than a range value i inserted.

Generated at Thu Feb 08 02:56:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.