Thanks for filing the JIRA, James. This is actually working as designed. See my comments here: https://issues.apache.org/jira/browse/PHOENIX-2223?focusedCommentId=14727548&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14727548

On Wed, Sep 2, 2015 at 7:20 AM, James Heather <james.heather@mendeley.com> wrote:
https://issues.apache.org/jira/browse/PHOENIX-2223

James


On 02/09/15 15:09, Jean-Marc Spaggiari wrote:
Yep, now I can only totally agree with you.

I think you should open a JIRA.

2015-09-02 10:05 GMT-04:00 James Heather <james.heather@mendeley.com>:
I think this is enough to demonstrate that there's an issue. Deleting without the 'where' clause returns (correctly) no rows affected when there's nothing there. Using a '<=' operator in the 'where' clause seems to report the right values (2 when it deletes 2 rows, 0 when it deletes 0 rows). Going back to '=' reports 1 row regardless.

This is 4.3 on CDH5, by the way.

0: jdbc:phoenix:172.31.30.216> select * from names;
+------------------------------------------+----------------------+
|                    ID                    |         NAME         |
+------------------------------------------+----------------------+
+------------------------------------------+----------------------+
No rows selected (0.538 seconds)
0: jdbc:phoenix:172.31.30.216> delete from names;
No rows affected (0.1 seconds)
0: jdbc:phoenix:172.31.30.216> upsert into names (id, name) values (1, 'james');
1 row affected (0.064 seconds)
0: jdbc:phoenix:172.31.30.216> upsert into names (id, name) values (2, 'helen');
1 row affected (0.05 seconds)
0: jdbc:phoenix:172.31.30.216> upsert into names (id, name) values (3, 'naomi');
1 row affected (0.052 seconds)
0: jdbc:phoenix:172.31.30.216> upsert into names (id, name) values (4, 'reuben');
1 row affected (0.053 seconds)
0: jdbc:phoenix:172.31.30.216> delete from names where id<=2;
2 rows affected (0.116 seconds)
0: jdbc:phoenix:172.31.30.216> delete from names where id<=2;
No rows affected (0.098 seconds)
0: jdbc:phoenix:172.31.30.216> delete from names where id=2;
1 row affected (0.071 seconds)
0: jdbc:phoenix:172.31.30.216> 

James


On 02/09/15 14:57, Jean-Marc Spaggiari wrote:
Is not the output the number of lines of the delete command, which is one line (the command itself) and not the number of deleted lines?

Can you try to put some rows into the table and do the delete again? Or try without the where close too?

2015-09-02 9:54 GMT-04:00 James Heather <james.heather@mendeley.com>:
Any idea why sqlline would report 1 row affected when I delete no rows?

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>

James