Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.2
-
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
Attachments
Activity
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
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?
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.
MariaDB [(none)]> show status like 'columnstore%';
-------------------------------+
| Variable_name | Value |
-------------------------------+
| Columnstore_commit_hash | source |
| Columnstore_version | 5.5.2 |
-------------------------------+
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.
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.
@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
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?