You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Jins George <ji...@aeris.net> on 2018/02/20 00:30:37 UTC
Incorrect number of rows affected from DELETE query
Hi,
I am facing an issue in which the number of rows affected by a DELETE
query returns an incorrect value. The record I am trying to delete
does not exists in the table, as evident from the first query but on
deletion, it reports 1 row is affected. Is this a known issue?
I have tried this in Phoenix 4.7 & Phoenix 4.13 and both behaves the
same way.
0: jdbc:phoenix:localhost> select accountId, subid from test.mytable
where accountid = '1' and subid = '1';
+------------+--------+
| ACCOUNTID | SUBID |
+------------+--------+
+------------+--------+
*No rows selected (0.017 seconds)*
0: jdbc:phoenix:localhost> delete from test.mytable where accountid =
'1' and subid = '1';
*1 row affected (0.005 seconds)*
0: jdbc:phoenix:localhost>
Thanks,
Jins George
Re: Incorrect number of rows affected from DELETE query
Posted by Jins George <ji...@aeris.net>.
Thanks James & Sergey. On further googling, I found
https://issues.apache.org/jira/browse/PHOENIX-2223 which is describing
the same issue. It would be great if this can be made configurable (
this will help especially for CRUD use cases)
The workaround to include a non pk which is always true worked fine and
returns the correct number of rows affected.
Thanks,
Jins George
On 02/22/2018 07:03 PM, James Taylor wrote:
> Phoenix returns the number of delete markers that were placed if it’s
> a point delete, not the actual number of rows deleted. Otherwise you’d
> need to do a read before the delete (which would be costly). It’s
> possible that this could be made configurable - please file a JIRA.
> You could work around this by adding a nullable column that’s never
> populated to your primary key or by adding another ANDed expression to
> your WHERE clause for a non pk column which you know is always true.
>
> On Thu, Feb 22, 2018 at 4:05 PM Sergey Soldatov
> <sergeysoldatov@gmail.com <ma...@gmail.com>> wrote:
>
> Hi Jins,
> If you provide steps to reproduce it would be much easier to
> understand where the problem is. If nothing was deleted the report
> should be 'No rows affected'.
>
> Thanks,
> Sergey
>
> On Mon, Feb 19, 2018 at 4:30 PM, Jins George
> <jins.george@aeris.net <ma...@aeris.net>> wrote:
>
> Hi,
>
> I am facing an issue in which the number of rows affected by a
> DELETE query returns an incorrect value. The record I am
> trying to delete does not exists in the table, as evident from
> the first query but on deletion, it reports 1 row is
> affected. Is this a known issue?
>
> I have tried this in Phoenix 4.7 & Phoenix 4.13 and both
> behaves the same way.
>
>
> 0: jdbc:phoenix:localhost> select accountId, subid from
> test.mytable where accountid = '1' and subid = '1';
> +------------+--------+
> | ACCOUNTID | SUBID |
> +------------+--------+
> +------------+--------+
> *No rows selected (0.017 seconds)*
> 0: jdbc:phoenix:localhost> delete from test.mytable where
> accountid = '1' and subid = '1';
> *1 row affected (0.005 seconds)*
> 0: jdbc:phoenix:localhost>
>
>
> Thanks,
> Jins George
>
>
Re: Incorrect number of rows affected from DELETE query
Posted by James Taylor <ja...@apache.org>.
Phoenix returns the number of delete markers that were placed if it’s a
point delete, not the actual number of rows deleted. Otherwise you’d need
to do a read before the delete (which would be costly). It’s possible that
this could be made configurable - please file a JIRA. You could work around
this by adding a nullable column that’s never populated to your primary key
or by adding another ANDed expression to your WHERE clause for a non pk
column which you know is always true.
On Thu, Feb 22, 2018 at 4:05 PM Sergey Soldatov <se...@gmail.com>
wrote:
> Hi Jins,
> If you provide steps to reproduce it would be much easier to understand
> where the problem is. If nothing was deleted the report should be 'No
> rows affected'.
>
> Thanks,
> Sergey
>
> On Mon, Feb 19, 2018 at 4:30 PM, Jins George <ji...@aeris.net>
> wrote:
>
>> Hi,
>>
>> I am facing an issue in which the number of rows affected by a DELETE
>> query returns an incorrect value. The record I am trying to delete does
>> not exists in the table, as evident from the first query but on deletion,
>> it reports 1 row is affected. Is this a known issue?
>>
>> I have tried this in Phoenix 4.7 & Phoenix 4.13 and both behaves the same
>> way.
>>
>>
>> 0: jdbc:phoenix:localhost> select accountId, subid from test.mytable
>> where accountid = '1' and subid = '1';
>> +------------+--------+
>> | ACCOUNTID | SUBID |
>> +------------+--------+
>> +------------+--------+
>> *No rows selected (0.017 seconds)*
>> 0: jdbc:phoenix:localhost> delete from test.mytable where accountid = '1'
>> and subid = '1';
>> *1 row affected (0.005 seconds)*
>> 0: jdbc:phoenix:localhost>
>>
>>
>> Thanks,
>> Jins George
>>
>
>
Re: Incorrect number of rows affected from DELETE query
Posted by Sergey Soldatov <se...@gmail.com>.
Hi Jins,
If you provide steps to reproduce it would be much easier to understand
where the problem is. If nothing was deleted the report should be 'No
rows affected'.
Thanks,
Sergey
On Mon, Feb 19, 2018 at 4:30 PM, Jins George <ji...@aeris.net> wrote:
> Hi,
>
> I am facing an issue in which the number of rows affected by a DELETE
> query returns an incorrect value. The record I am trying to delete does
> not exists in the table, as evident from the first query but on deletion,
> it reports 1 row is affected. Is this a known issue?
>
> I have tried this in Phoenix 4.7 & Phoenix 4.13 and both behaves the same
> way.
>
>
> 0: jdbc:phoenix:localhost> select accountId, subid from test.mytable
> where accountid = '1' and subid = '1';
> +------------+--------+
> | ACCOUNTID | SUBID |
> +------------+--------+
> +------------+--------+
> *No rows selected (0.017 seconds)*
> 0: jdbc:phoenix:localhost> delete from test.mytable where accountid = '1'
> and subid = '1';
> *1 row affected (0.005 seconds)*
> 0: jdbc:phoenix:localhost>
>
>
> Thanks,
> Jins George
>