[MCOL-4959] Delete IN (select id from columnstoreTable) not working Created: 2022-01-11  Updated: 2023-02-06  Resolved: 2022-11-30

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 5.5.2
Fix Version/s: 6.4.6

Type: Bug Priority: Critical
Reporter: andreas eschbacher Assignee: Roman
Resolution: Fixed Votes: 0
Labels: crash, delete
Environment:

MariaDB 10.5.10
NAME="Red Hat Enterprise Linux"
VERSION="8.2 (Ootpa)"


 Description   

drop table if exists deleteTestInnoDB;
create table if not exists deleteTestInnoDB (
report_id int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into deleteTestInnoDB values (0),(1),(2),(3);
select * from deleteTestInnoDB;

drop table if exists deleteTestInnoDB2;
create table if not exists deleteTestInnoDB2 (
report_id int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into deleteTestInnoDB2 values (1),(2),(3),(4);
select * from deleteTestInnoDB2;

drop table if exists deleteTestColumnstore;
create table if not exists deleteTestColumnstore (
report_id int
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;
insert into deleteTestColumnstore values (1),(2),(3),(4);
select * from deleteTestColumnstore;

delete from deleteTestInnoDB where report_id in (select report_id from deleteTestColumnstore); – nothing is deleted!!! (In ETL: connection to ExeMgr was lost.)

delete from deleteTestInnoDB where report_id in (select report_id from deleteTestInnoDB2); – works
delete from deleteTestInnoDB where report_id in (1,2,3,4); – works
delete from deleteTestColumnstore where report_id in (select report_id from deleteTestInnoDB); – works

 Comments   
Comment by Roman [ 2022-02-15 ]

Greetings aeae81
Could I ask you to try MCS that comes with MDB 10.6 instead b/c we have some JOIN-related fixes in that version that should fix this issue?

Comment by andreas eschbacher [ 2022-02-15 ]

Hi Roman,

I've tried on a Machine were we are running MariadbCS 10.6.3-MariaDB on, but there is the same behavior.
br Andreas

Comment by Roman [ 2022-02-16 ]

Hm, I just retested the query and it works for me in the current develop-6.

MariaDB [test]> delete from deleteTestInnoDB where report_id in (select report_id from deleteTestColumnstore);
 
Query OK, 3 rows affected (0.011 sec)
 
 
 
MariaDB [test]> select * from deleteTestInnoDB;
 
+-----------+
 
| report_id |
 
+-----------+
 
|         0 |
 
+-----------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> show status like 'columnstore%';
 
+-------------------------+-----------+
 
| Variable_name           | Value     |
 
+-------------------------+-----------+
 
| Columnstore_commit_hash | 3647d607b |
 
| Columnstore_version     | 6.2.1     |
 
+-------------------------+-----------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [test]>

Could you share the output from show status like 'columnstore%'; from 10.6 system?

Comment by Roman [ 2022-02-16 ]

However DML queries with such pattern, namely delete from InnoDB filtering with MCS data might be a bit slow b/c MDB can query full scan only from MCS.

Comment by andreas eschbacher [ 2022-02-21 ]

MariaDB [(none)]> show status like 'columnstore%';
-------------------------------+

Variable_name Value

-------------------------------+

Columnstore_commit_hash source
Columnstore_version 5.5.2

-------------------------------+

Comment by Roman [ 2022-03-04 ]

aeae81 The next community MDB 10.6 release will contain MCS 6 where the issue is fixed. I would suggest you to upgrade to that version if it is acceptable.

Comment by Sergey Zefirov [ 2022-03-04 ]

The following works alright on server 10.6 and columnstore's current develop:

use test;
 
 
 
drop table if exists deleteTestInnoDB;
 
create table if not exists deleteTestInnoDB (
 
report_id int
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
insert into deleteTestInnoDB values (0),(1),(2),(3);
 
select * from deleteTestInnoDB;
 
 
 
drop table if exists deleteTestInnoDB2;
 
create table if not exists deleteTestInnoDB2 (
 
report_id int
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
insert into deleteTestInnoDB2 values (1),(2),(3),(4);
 
select * from deleteTestInnoDB2;
 
 
 
drop table if exists deleteTestColumnstore;
 
create table if not exists deleteTestColumnstore (
 
report_id int
 
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;
 
insert into deleteTestColumnstore values (1),(2),(3),(4);
 
select * from deleteTestColumnstore;
 
 
 
select 'innodb from mcs';
 
delete from deleteTestInnoDB where report_id in (select report_id from deleteTestColumnstore); -- nothing is deleted!!! (In ETL: connection to ExeMgr was lost.)
 
 
 
select * from deleteTestInnoDB;

The last SELECT returns only one row with zero in it, as it should.

Comment by andreas eschbacher [ 2023-02-06 ]

@roman.nozdrin
delete from deleteTestInnoDB where report_id in (select report_id from deleteTestColumnstore); – nothing is deleted!!! (In ETL: connection to ExeMgr was lost.)

is still not possible in Columnstore_version | 5.6.8 |
and it
WORKS with
Columnstore_version | 6.4.6
br andreas

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