Re: sqlline reporting 1 row affected when it isn't

[James Taylor]

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 
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 :
>
>> 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.heat...@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
>>>
>>
>>
>>
>
>

Re: sqlline reporting 1 row affected when it isn't

[Jean-Marc Spaggiari]

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 :

> 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 :
>
>> 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
>>
>
>
>

Re: sqlline reporting 1 row affected when it isn't

[James Heather]

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 >:


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

Re: sqlline reporting 1 row affected when it isn't

[James Heather]

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 >:


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
mailto:james.heat...@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

Re: sqlline reporting 1 row affected when it isn't

[Jean-Marc Spaggiari]

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 :

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

sqlline reporting 1 row affected when it isn't

[James Heather]

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