You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by James Heather <ja...@mendeley.com> on 2015/09/02 15:54:32 UTC
sqlline reporting 1 row affected when it isn't
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
Posted by James Taylor <ja...@apache.org>.
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 <ja...@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 <ja...@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 < <ja...@mendeley.com>
>> 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
>>>
>>
>>
>>
>
>
Re: sqlline reporting 1 row affected when it isn't
Posted by James Heather <ja...@mendeley.com>.
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
> <ma...@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 <ma...@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
Posted by Jean-Marc Spaggiari <je...@spaggiari.org>.
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 <ja...@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 <ja...@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
Posted by James Heather <ja...@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
> <ma...@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
Posted by Jean-Marc Spaggiari <je...@spaggiari.org>.
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 <ja...@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
>