From user-return-3642-apmail-phoenix-user-archive=phoenix.apache.org@phoenix.apache.org Wed Sep 2 14:20:38 2015 Return-Path: X-Original-To: apmail-phoenix-user-archive@minotaur.apache.org Delivered-To: apmail-phoenix-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 758BF17647 for ; Wed, 2 Sep 2015 14:20:38 +0000 (UTC) Received: (qmail 42504 invoked by uid 500); 2 Sep 2015 14:20:38 -0000 Delivered-To: apmail-phoenix-user-archive@phoenix.apache.org Received: (qmail 42457 invoked by uid 500); 2 Sep 2015 14:20:38 -0000 Mailing-List: contact user-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@phoenix.apache.org Delivered-To: mailing list user@phoenix.apache.org Received: (qmail 42447 invoked by uid 99); 2 Sep 2015 14:20:38 -0000 Received: from Unknown (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 02 Sep 2015 14:20:38 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id AF2DCC6B48 for ; Wed, 2 Sep 2015 14:20:37 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 3 X-Spam-Level: *** X-Spam-Status: No, score=3 tagged_above=-999 required=6.31 tests=[HTML_MESSAGE=3, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-eu-west.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id OlLw7wAdkFO1 for ; Wed, 2 Sep 2015 14:20:28 +0000 (UTC) Received: from mail-wi0-f180.google.com (mail-wi0-f180.google.com [209.85.212.180]) by mx1-eu-west.apache.org (ASF Mail Server at mx1-eu-west.apache.org) with ESMTPS id AAFA224C0E for ; Wed, 2 Sep 2015 14:20:27 +0000 (UTC) Received: by wicfx3 with SMTP id fx3so20930877wic.0 for ; Wed, 02 Sep 2015 07:20:27 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-type; bh=OdKMgXri9KuXT7rnuol9B+sjIk8MMXvNk6qTpRwXnN4=; b=E5EaRrQomjaBcdOl9PA2KAPOSQF9lpc0c3z+nXbptpB28Co52i6PJJ1smJX4DnD2m1 nGWntN4OSK85Gt+5/XydSz88gsAN8CUPKE3AYSACBf6tKUXMP/WafSOA0A2iZKXd7qn5 bF1tpj0tAOf6UuAkN+Jl3JlfYZ445ocism25OtoUki09vUm587WRMKKZ1aP9fD0VT2Ra nYZD4mk0dA9vrMm6ZHfQJmoGGvCeitcsspEAyrM8pBD6d3N8O25fV2hNeTFH3Fb7byNb bt/Z4kHsBHQXF5fhBQSJrk8Cifm6hbvi3snvdEUM8ljMymtEhASDehZp2tSi86alr7/N HbTQ== X-Gm-Message-State: ALoCoQmJKhfMlGiL7jx9LSePZ3WHO5UTJnOJwu/wjGRJPp7oZxS0ozwKQp1jB3nH5dXsXup2E8EC X-Received: by 10.180.37.33 with SMTP id v1mr4576852wij.88.1441203627392; Wed, 02 Sep 2015 07:20:27 -0700 (PDT) Received: from [10.154.190.229] (ec2-52-18-179-73.eu-west-1.compute.amazonaws.com. [52.18.179.73]) by smtp.googlemail.com with ESMTPSA id lh3sm15395290wjc.44.2015.09.02.07.20.26 for (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 02 Sep 2015 07:20:26 -0700 (PDT) Subject: Re: sqlline reporting 1 row affected when it isn't To: user@phoenix.apache.org References: <55E6FF98.8050208@mendeley.com> <55E70239.6050906@mendeley.com> From: James Heather Message-ID: <55E705AA.5070104@mendeley.com> Date: Wed, 2 Sep 2015 15:20:26 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.2.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------070607030309000909010201" This is a multi-part message in MIME format. --------------070607030309000909010201 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 >> >: >> >> 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 >> >> > > --------------070607030309000909010201 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit 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




--------------070607030309000909010201--