[MCOL-1156] Incorrect 0 row(s) affected on delete Created: 2018-01-11  Updated: 2021-10-01  Resolved: 2018-03-21

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.1.2
Fix Version/s: 1.1.4

Type: Bug Priority: Minor
Reporter: MIke Thibodeau Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

– version=10.2.10-MariaDB-log
– version_comment=Columnstore 1.1.2-1


Issue Links:
Blocks
is blocked by MCOL-1527 Incorrect 0 row(s) affected on delete... Closed
Relates
relates to MCOL-4536 update returns wrong number of rows u... Closed
Sprint: 2018-03, 2018-04, 2018-05, 2018-06

 Description   

when I was doing some tests I observed that when I delete a row (via a join to another column store table) the server reports 0 rows affected even when row is deleted.

Here's a sample...

– version=10.2.10-MariaDB-log
– version_comment=Columnstore 1.1.2-1

CREATE TABLE cs1 (
id INT(10) UNSIGNED NOT NULL,
`timestamp_` DATETIME,
numeric_val DOUBLE DEFAULT NULL
) ENGINE=columnstore;
INSERT INTO cs1 VALUES (1, '2018-01-09 21:59:02', 11.11)

CREATE TABLE cs2 (
id INT(10) UNSIGNED NOT NULL,
`timestamp_` DATETIME,
numeric_val DOUBLE DEFAULT NULL
) ENGINE=columnstore;

INSERT INTO cs2 VALUES (1, '2018-01-09 21:59:02', 22.22);

SELECT * FROM cs1;
DELETE FROM cs1 WHERE id = 1;
– correctly reporting rows affected
– Query: delete from cs1 where id = 1
– 1 row(s) affected
SELECT * FROM cs1;
– Query: select * from cs1 LIMIT 0, 5000
– 0 row(s) affected

INSERT INTO cs1 VALUES (1, '2018-01-09 21:59:02', 11.11);

SELECT * FROM cs1;
SELECT * FROM cs2;
DELETE cs1 FROM cs1
JOIN cs2 USING(id,timestamp_);
– reporting 0 rows affected when 1 row was affected
– Query: DELETE cs1 from cs1 join cs2 using(id,timestamp_)
– 0 row(s) affected

SELECT * FROM cs1;
– Query: SELECT * FROM cs1 LIMIT 0, 5000
– 0 row(s) affected

SELECT * FROM cs2;
– Query: SELECT * FROM cs2 LIMIT 0, 5000
– 1 row(s) affected

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-01-11 ]

This is a duplicate of MCOL-1082 which is fixed and will be released as part of versoin 1.1.3.

Comment by MIke Thibodeau [ 2018-01-11 ]

Sorry about the dup... I saw MCOL-1082 but I thought this was different because I wasn't using the function and the server returns the correct number of rows deleted when not joining to another column store table. In other words I only observe this behavior when I delete with a join.

Comment by Andrew Hutchings (Inactive) [ 2018-01-11 ]

You are right, this is slightly different and isn't fixed yet. Reopening.

Comment by MIke Thibodeau [ 2018-01-12 ]

UPDATE reports all the rows as affected not just those with a change. Is that a duplicate of this issue?

Comment by Andrew Hutchings (Inactive) [ 2018-01-12 ]

Could be, I'm not sure on the root cause yet. I'll investigate it at the same time.

Comment by Roman [ 2018-02-11 ]

Here is a quick hack to get over with the issue and here are the results.
The CS code uses THD.m_row_count_func attribute to send a number of affected rows back to the server code ATM, but it is an improper way to do that, since it causes a CS specific hack in the sever code. I will take a closer look at the issue, fixing the mentioned schema.

Comment by Roman [ 2018-02-12 ]

Current CS implementation bypasses the section, which produces affected rows number. Since that I made the PR for a develop-1.1 branch of the server to fix the issue.

Comment by Roman [ 2018-02-12 ]

Please the review the fix for MCOL-1156.

Comment by Andrew Hutchings (Inactive) [ 2018-02-14 ]

Thanks for the commit. I'll review it after 1.1.3 has been released.

Comment by Daniel Lee (Inactive) [ 2018-03-21 ]

Build verified: 1.1.4-1 source

/root/columnstore/mariadb-columnstore-server
commit 3b5242143b394423dbdf96d888410a3c33f9ff97
Merge: b7d93b7 23900e6
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Wed Mar 7 10:39:40 2018 -0600

Merge pull request #104 from mariadb-corporation/davidhilldallas-patch-3

update version

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 4e167285c6665c9aa0e204eac2de0add61709be4
Merge: 819c69c b800fc7
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Wed Mar 14 13:25:21 2018 -0500

Merge pull request #427 from mariadb-corporation/MCOL-1225

Mcol 1225 - fix mysql/lib library path

Reproduced the issue in 1.1.2-1

It was fixed in 1.1.3-2

1.1.4-1

MariaDB [mytest]> SELECT * FROM cs1;
----------------------------------

id timestamp_ numeric_val

----------------------------------

1 2018-01-09 21:59:02 11.11

----------------------------------
1 row in set (0.11 sec)

MariaDB [mytest]> SELECT * FROM cs2;
----------------------------------

id timestamp_ numeric_val

----------------------------------

1 2018-01-09 21:59:02 22.22

----------------------------------
1 row in set (0.04 sec)

MariaDB [mytest]>
MariaDB [mytest]> DELETE cs1 FROM cs1
-> JOIN cs2 USING(id,timestamp_);
Query OK, 1 row affected (0.54 sec)

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