Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-2223

Support DELETE that strictly returns number of rows deleted

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.3.0
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      CDH5

      Description

      If you create a table with no rows in, and then issue a delete statement with a where id=1 clause, sqlline reports 1 row affected, even though no rows were actually deleted. It doesn't seem to happen without a where clause, or with something like where id<=2.

      Some experiments:

      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> 
      

      and

      0: jdbc:phoenix:172.31.30.216> select * from names;
      +------------------------------------------+----------------------+
      |                    ID                    |         NAME         |
      +------------------------------------------+----------------------+
      +------------------------------------------+----------------------+
      No rows selected (0.538 seconds)
      0: jdbc:phoenix:172...> delete from names;
      No rows affected (0.1 seconds)
      0: jdbc:phoenix:172...> upsert into names (id, name) values (1, 'james');
      1 row affected (0.064 seconds)
      0: jdbc:phoenix:172...> upsert into names (id, name) values (2, 'helen');
      1 row affected (0.05 seconds)
      0: jdbc:phoenix:172...> upsert into names (id, name) values (3, 'naomi');
      1 row affected (0.052 seconds)
      0: jdbc:phoenix:172...> upsert into names (id, name) values (4, 'reuben');
      1 row affected (0.053 seconds)
      0: jdbc:phoenix:172...> delete from names where id<=2;
      2 rows affected (0.116 seconds)
      0: jdbc:phoenix:172...> delete from names where id<=2;
      No rows affected (0.098 seconds)
      0: jdbc:phoenix:172...> delete from names where id=2;
      1 row affected (0.071 seconds)
      0: jdbc:phoenix:172...> 
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              chiastic-security James Heather
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: