Re: Incorrect number of rows affected from DELETE query

[Jins George]

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 
<sergeysolda...@gmail.com <mailto:sergeysolda...@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.geo...@aeris.net <mailto:jins.geo...@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