Re: Incorrect number of rows affected from DELETE query
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 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 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
Re: Incorrect number of rows affected from DELETE query
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 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 > 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
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 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 >
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
