[MCOL-1527] Incorrect 0 row(s) affected on delete with cross engine join Created: 2018-07-04  Updated: 2021-10-01  Resolved: 2018-07-26

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.1.5
Fix Version/s: 1.1.6

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MCOL-1156 Incorrect 0 row(s) affected on delete Closed
Relates
relates to MCOL-1600 Improve multi-UPDATE performance when... Closed
relates to MCOL-2239 Rows matched: and Changed: are 0 even... Closed
relates to MCOL-4536 update returns wrong number of rows u... Closed
Sprint: 2018-14, 2018-15

 Description   

similar to MCOL-1156, which is fxied in 1.1.4 but still exist, if a join with innodb come in play.

Testcase:

MariaDB [dtest]> create table t_mcs (id int(11)) engine=Columnstore;
 
Query OK, 0 rows affected (0.53 sec)
 
 
 
MariaDB [dtest]> create table t_innodb (id int(11)) engine=InnoDB;
 
Query OK, 0 rows affected (0.02 sec)
 
 
 
MariaDB [dtest]> insert into t_mcs (id) values(1),(2),(3);
 
Query OK, 3 rows affected, 1 warning (0.19 sec)
 
Records: 3  Duplicates: 0  Warnings: 1
 
 
 
MariaDB [dtest]> insert into t_innodb (id) values(1),(2),(3);
 
Query OK, 3 rows affected (0.01 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [dtest]> delete t_mcs from t_mcs inner join t_innodb using(id);
 
Query OK, 0 rows affected, 3 warnings (0.29 sec)
 
 
 
MariaDB [dtest]> select * from t_mcs;
 
Empty set, 1 warning (0.02 sec)



 Comments   
Comment by Roman [ 2018-07-17 ]

Please review.

Comment by Daniel Lee (Inactive) [ 2018-07-25 ]

Build tested: 1.1.6-1 source

/root/columnstore/mariadb-columnstore-server
commit 513775738f72ec990d055a5d47e2511e3c0e34dd
Merge: 3c37210 9236098
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Wed Jul 18 09:37:17 2018 +0100

Merge pull request #123 from drrtuy/MCOL-970

MCOL-970 Slow query log now contains original query even in vtable mode

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit f9f6dc43dd15ad3f2ca2d9e515b1e44028a16183
Merge: ced7eb4 1170b4e
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Tue Jul 24 18:06:24 2018 +0100

Merge pull request #526 from mariadb-corporation/MCOL-1535

Mcol 1535

First case, DELETE, is now returning correct row count

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.15-MariaDB-log Columnstore 1.1.6-1

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mytest]> create table t_mcs (id int(11)) engine=Columnstore;
Query OK, 0 rows affected (0.14 sec)

MariaDB [mytest]> create table t_innodb (id int(11)) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

MariaDB [mytest]> insert into t_mcs (id) values(1),(2),(3);
Query OK, 3 rows affected (0.20 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [mytest]> insert into t_innodb (id) values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [mytest]> delete t_mcs from t_mcs inner join t_innodb using(id);
Query OK, 3 rows affected (0.45 sec)

MariaDB [mytest]> select * from t_mcs;
Empty set (0.00 sec)

MariaDB [mytest]>

The 2nd case, UPDATE, is still returning incorrect row count

MariaDB [mytest]> create table cs1 (t char(1),i int) engine=columnstore;
Query OK, 0 rows affected (0.20 sec)

MariaDB [mytest]> create table i1 (t char(1),i int) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

MariaDB [mytest]> insert into cs1 values ('',1);
Query OK, 1 row affected (0.23 sec)

MariaDB [mytest]> insert into cs1 values ('',7);
Query OK, 1 row affected (0.07 sec)

MariaDB [mytest]> insert into cs1 values ('',7);
Query OK, 1 row affected (0.07 sec)

MariaDB [mytest]> insert into i1 values ('',1);
Query OK, 1 row affected (0.02 sec)

MariaDB [mytest]> insert into i1 values ('',2);
Query OK, 1 row affected (0.08 sec)

MariaDB [mytest]> insert into i1 values ('',3);
Query OK, 1 row affected (0.00 sec)

MariaDB [mytest]> insert into i1 values ('',5);
Query OK, 1 row affected (0.00 sec)

MariaDB [mytest]> insert into i1 values ('',2);
Query OK, 1 row affected (0.05 sec)

MariaDB [mytest]> insert into i1 values ('',3);
Query OK, 1 row affected (0.00 sec)

MariaDB [mytest]> insert into i1 values ('',5);
Query OK, 1 row affected (0.00 sec)

MariaDB [mytest]> insert into i1 values ('',2);
Query OK, 1 row affected (0.00 sec)

MariaDB [mytest]> insert into i1 values ('',5);
Query OK, 1 row affected (0.04 sec)

MariaDB [mytest]> select * from cs1;
----------+

t i

----------+

NULL 1
NULL 7
NULL 7

----------+
3 rows in set (0.09 sec)

MariaDB [mytest]> select * from i1;
----------+

t i

----------+

  1
  2
  3
  5
  2
  3
  5
  2
  5

----------+
9 rows in set (0.00 sec)

MariaDB [mytest]> update cs1 join i1 on i1.i=cs1.i set cs1.t = 's' ;
Query OK, 9 rows affected (1.17 sec)
Rows matched: 0 Changed: 0 Warnings: 0

MariaDB [mytest]> select * from cs1;
----------+

t i

----------+

s 1
NULL 7
NULL 7

----------+
3 rows in set (0.39 sec)

MariaDB [mytest]> select * from i1;
----------+

t i

----------+

  1
  2
  3
  5
  2
  3
  5
  2
  5

----------+
9 rows in set (0.00 sec)

Comment by Daniel Lee (Inactive) [ 2018-07-26 ]

Thanks remaining issue is being tracked by MCOL-1600. Closing this one.

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