[jira] [Commented] (PHOENIX-2223) Support DELETE that strictly returns number of rows deleted

["James Taylor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-2223?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14727548#comment-14727548 ] 

James Taylor commented on PHOENIX-2223:
---------------------------------------

Our DELETE command returns the number of delete markers that were added, not how many rows were deleted. The reason is performance. To get an exact count, we'd need to do CheckAndDelete calls which would be *much* slower as it'd force a read under lock for every row.

For the particular DELETE you're discussing, we can execute it in a single RPC since on the client side we can form the complete row key. That's why we always return a row count of 1. In cases where this is not possible, we essentially issue the equivalent of a SELECT query, execute it on the server side, and return the number of rows read. This is more accurate, but not guaranteed either, as another client could be issuing the same or an overlapping DELETE command. In this case some rows could be deleted by each command depending on the order of execution.

FWIW, we use the same technique for an UPERT command. See PHOENIX-526 for example.

> Support DELETE that strictly returns number of rows deleted
> -----------------------------------------------------------
>
>                 Key: PHOENIX-2223
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2223
>             Project: Phoenix
>          Issue Type: Improvement
>    Affects Versions: 4.3.0
>         Environment: CDH5
>            Reporter: James Heather
>
> If you create a table with no rows in, and then issue a delete statement with a {{where id=1}} clause, sqlline reports 1 row affected, even though no rows were actually deleted. It doesn't seem to happen without a {{where}} clause, or with something like {{where id<=2}}.
> Some experiments:
> {code}
> 0: jdbc:phoenix:172.xx.xx.xxx> create table names (id bigint(20) primary key, name varchar(20));
> No rows affected (1.158 seconds)
> 0: jdbc:phoenix:172.xx.xx.xxx> delete from names where id=1;
> 1 row affected (0.204 seconds)
> 0: jdbc:phoenix:172.xx.xx.xxx> 
> {code}
> and
> {code}
> 0: jdbc:phoenix:172.31.30.216> select * from names;
> +------------------------------------------+----------------------+
> |                    ID                    |         NAME         |
> +------------------------------------------+----------------------+
> +------------------------------------------+----------------------+
> No rows selected (0.538 seconds)
> 0: jdbc:phoenix:172...> delete from names;
> No rows affected (0.1 seconds)
> 0: jdbc:phoenix:172...> upsert into names (id, name) values (1, 'james');
> 1 row affected (0.064 seconds)
> 0: jdbc:phoenix:172...> upsert into names (id, name) values (2, 'helen');
> 1 row affected (0.05 seconds)
> 0: jdbc:phoenix:172...> upsert into names (id, name) values (3, 'naomi');
> 1 row affected (0.052 seconds)
> 0: jdbc:phoenix:172...> upsert into names (id, name) values (4, 'reuben');
> 1 row affected (0.053 seconds)
> 0: jdbc:phoenix:172...> delete from names where id<=2;
> 2 rows affected (0.116 seconds)
> 0: jdbc:phoenix:172...> delete from names where id<=2;
> No rows affected (0.098 seconds)
> 0: jdbc:phoenix:172...> delete from names where id=2;
> 1 row affected (0.071 seconds)
> 0: jdbc:phoenix:172...> 
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)