[
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)
