Delete

The delete statement’s syntax is as follows：

example 1：

example 2:

DELETE FROM my_table PARTITION p1 WHERE k1 < 3 AND k2 = "abc";

The following describes the parameters used in the delete statement:

• PARTITION

  The target partition of the delete statement. If not specified, the table must be a single partition table, otherwise it cannot be deleted

• WHERE

  The conditiona of the delete statement. All delete statements must specify a where condition.

说明：

1. The type of OP in the WHERE condition can only include =, >, <, > =, < =,!=. Currently, where key in (value1, Value2, value3) mode is not supported yet, may be added this support later.
2. The column in the WHERE condition can only be the key column.
3. Cannot delete when the key column does not exist in any rollup table.
4. If the specified table is a range partitioned table, PARTITION must be specified unless the table is a single partition table,.
5. Unlike the insert into command, delete statement cannot specify label manually. You can view the concept of label in [Insert Into] (./insert-into-manual.md)

The delete command is an SQL command, and the returned results are synchronous. It can be divided into the following types:

1. Successful visible

   If delete completes successfully and is visible, the following results will be returned, query OK indicates success.

2. Submitted successfully, but not visible

   mysql> delete from test_tbl PARTITION p1 where k1 = 1;
   Query OK, 0 rows affected (0.04 sec)
   

   The result will return a JSON string at the same time:

   affected rows: Indicates the row affected by this deletion. Since the deletion of Doris is currently a logical deletion, the value is always 0.

   label: The label generated automatically to be the signature of the delete jobs. Each job has a unique label within a single database.

   status: Indicates whether the data deletion is visible. If it is visible, visible will be displayed. If it is not visible, committed will be displayed.

   txnId: The transaction ID corresponding to the delete job

   err: Field will display some details of this deletion

3. Commit failed, transaction cancelled

   If the delete statement is not submitted successfully, it will be automatically aborted by Doris and the following results will be returned

   mysql> delete from test_tbl partition p1 where k1 > 80;
   ERROR 1064 (HY000): errCode = 2, detailMessage = {错误原因}

   example：

   A timeout deletion will return the timeout and unfinished replicas displayed as (tablet = replica)

   The correct processing logic for the returned results of the delete operation is as follows:

   1. If Error 1064 (HY000) is returned, deletion fails

   2. If the returned result is Query OK, the deletion is successful

      1. If status is visible, the data have been deleted successfully.

In general, Doris’s deletion timeout is limited from 30 seconds to 5 minutes. The specific time can be adjusted through the following configuration items

• tablet_delete_timeout_second

  The timeout of delete itself can be elastically changed by the number of tablets in the specified partition. This configuration represents the average timeout contributed by a tablet. The default value is 2.

  Assuming that there are 5 tablets under the specified partition for this deletion, the timeout time available for the deletion is 10 seconds. Because the minimum timeout is 30 seconds which is higher than former timeout time, the final timeout is 30 seconds.

• load_straggler_wait_second

  If the user estimates a large amount of data, so that the upper limit of 5 minutes is insufficient, the user can adjust the upper limit of timeout through this item, and the default value is 300.

  The specific calculation rule of timeout(seconds)

  TIMEOUT = MIN(load_straggler_wait_second, MAX(30, tablet_delete_timeout_second * tablet_num))

• query_timeout

  Because delete itself is an SQL command, the deletion statement is also limited by the session variables, and the timeout is also affected by the session value query'timeout. You can increase the value by set query'timeout = xxx.

1. The user can view the deletion completed in history through the show delete statement.

   Syntax

   SHOW DELETE [FROM db_name]

   example

   mysql> show delete from test_db;
   +-----------+---------------+---------------------+-----------------+----------+
   | TableName | PartitionName | CreateTime          | DeleteCondition | State    |
   +-----------+---------------+---------------------+-----------------+----------+
   | empty_tbl | p3            | 2020-04-15 23:09:35 | k1 EQ "1"       | FINISHED |
   | test_tbl  | p4            | 2020-04-15 23:09:53 | k1 GT "80"      | FINISHED |
   2 rows in set (0.00 sec)