This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 0086fdbbdb [enhancement](planner) support delete from using syntax
(#17787)
0086fdbbdb is described below
commit 0086fdbbdbbb13268388fb344c9ec40ef4544e33
Author: morrySnow <101034200+morrys...@users.noreply.github.com>
AuthorDate: Thu Mar 16 13:12:00 2023 +0800
[enhancement](planner) support delete from using syntax (#17787)
support syntax delete using, this syntax only support UNIQUE KEY model
use the result of `t2` join `t3` to romve rows from `t1`
```sql
-- create t1, t2, t3 tables
CREATE TABLE t1
(id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
UNIQUE KEY (id)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1', "function_column.sequence_col" = "c4");
CREATE TABLE t2
(id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');
CREATE TABLE t3
(id INT)
DISTRIBUTED BY HASH (id)
PROPERTIES('replication_num'='1');
-- insert data
INSERT INTO t1 VALUES
(1, 1, '1', 1.0, '2000-01-01'),
(2, 2, '2', 2.0, '2000-01-02'),
(3, 3, '3', 3.0, '2000-01-03');
INSERT INTO t2 VALUES
(1, 10, '10', 10.0, '2000-01-10'),
(2, 20, '20', 20.0, '2000-01-20'),
(3, 30, '30', 30.0, '2000-01-30'),
(4, 4, '4', 4.0, '2000-01-04'),
(5, 5, '5', 5.0, '2000-01-05');
INSERT INTO t3 VALUES
(1),
(4),
(5);
-- remove rows from t1
DELETE FROM t1
USING t2 INNER JOIN t3 ON t2.id = t3.id
WHERE t1.id = t2.id;
```
the expect result is only remove the row where id = 1 in table t1
```
+----+----+----+--------+------------+
| id | c1 | c2 | c3 | c4 |
+----+----+----+--------+------------+
| 2 | 2 | 2 | 2.0 | 2000-01-02 |
| 3 | 3 | 3 | 3.0 | 2000-01-03 |
+----+----+----+--------+------------+
```
---
.../Manipulation/DELETE.md | 122 ++++++++++++++--
.../Manipulation/UPDATE.md | 24 +---
.../Manipulation/DELETE.md | 120 ++++++++++++++--
.../Manipulation/UPDATE.md | 19 +--
fe/fe-core/src/main/cup/sql_parser.cup | 17 ++-
.../java/org/apache/doris/analysis/DeleteStmt.java | 160 +++++++++++++++++----
.../org/apache/doris/analysis/PartitionNames.java | 2 +-
.../main/java/org/apache/doris/catalog/Column.java | 4 +
.../java/org/apache/doris/qe/StmtExecutor.java | 22 ++-
.../org/apache/doris/analysis/DeleteStmtTest.java | 31 ++--
.../data/delete_p0/test_delete_using.out | 5 +
.../suites/delete_p0/test_delete_using.groovy | 58 ++++++++
12 files changed, 467 insertions(+), 117 deletions(-)
diff --git
a/docs/en/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
b/docs/en/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
index 842fddd17a..fdbf72462d 100644
---
a/docs/en/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
+++
b/docs/en/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
@@ -36,27 +36,60 @@ This statement is used to conditionally delete data in the
specified table (base
This operation will also delete the data of the rollup index related to this
base index.
-grammar:
+#### Syntax
-````SQL
-DELETE FROM table_name [PARTITION partition_name | PARTITIONS (p1, p2)]
+Syntax 1: This syntax can only specify filter predicates
+
+```SQL
+DELETE FROM table_name [PARTITION partition_name | PARTITIONS (partition_name
[, partition_name])]
WHERE
-column_name1 op { value | value_list } [ AND column_name2 op { value |
value_list } ...];
-````
+column_name op { value | value_list } [ AND column_name op { value |
value_list } ...];
+```
+
+<version since="dev">
+
+Syntax 2：This syntax can only used on UNIQUE KEY model
+
+```sql
+DELETE FROM table_name
+ [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
+ [USING additional_tables]
+ WHERE condition
+```
+
+</version>
+
+#### Required Parameters
+
++ table_name: Specifies the table from which rows are removed.
++ column_name: column belong to table_name
++ op: Logical comparison operator, The optional types of op include: =, >, <,
>=, <=, !=, in, not in
++ value | value_list: value or value list used for logial comparison
+
+<version since="dev">
-illustrate:
++ WHERE condition: Specifies a condition to use to select rows for removal
-1. The optional types of op include: =, >, <, >=, <=, !=, in, not in
-2. Only conditions on the key column can be specified when using AGGREGATE
(UNIQUE) model.
-3. When the selected key column does not exist in a rollup, delete cannot be
performed.
-4. Conditions can only have an "and" relationship. If you want to achieve an
"or" relationship, you need to write the conditions in two DELETE statements.
-5. If it is a partitioned table, you can specify a partition. If not
specified, Doris will infer partition from the given conditions. In two cases,
Doris cannot infer the partition from conditions: 1) the conditions do not
contain partition columns; 2) The operator of the partition column is not in.
When a partition table does not specify the partition, or the partition cannot
be inferred from the conditions, the session variable delete_without_partition
needs to be true to make delete s [...]
+</version>
-Notice:
-1. This statement may reduce query efficiency for a period of time after
execution.
-2. The degree of impact depends on the number of delete conditions specified
in the statement.
-3. The more conditions you specify, the greater the impact.
+#### Optional Parameters
+
++ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
Specifies the partition or partitions to select rows for removal
+
+<version since="dev">
+
++ USING additional_tables: If you need to refer to additional tables in the
WHERE clause to help identify the rows to be removed, then specify those table
names in the USING clause. You can also use the USING clause to specify
subqueries that identify the rows to be removed.
+
+</version>
+
+#### Note
+
+1. Only conditions on the key column can be specified when using AGGREGATE
(UNIQUE) model.
+2. When the selected key column does not exist in a rollup, delete cannot be
performed.
+3. Wheny you use syntax 1, conditions can only have an "and" relationship. If
you want to achieve an "or" relationship, you need to write the conditions in
two DELETE statements.
+4. <version since="1.2" type="inline"> In syntax 1, if it is a partitioned
table, you can specify a partition. If not specified, Doris will infer
partition from the given conditions. In two cases, Doris cannot infer the
partition from conditions: 1) the conditions do not contain partition columns;
2) The operator of the partition column is not in. When a partition table does
not specify the partition, or the partition cannot be inferred from the
conditions, the session variable delete_wi [...]
+5. This statement may reduce query efficiency for a period of time after
execution. The degree of impact depends on the number of delete conditions
specified in the statement. The more conditions you specify, the greater the
impact.
### Example
@@ -81,6 +114,65 @@ Notice:
WHERE k1 >= 3 AND k2 = "abc";
````
+<version since="dev">
+
+4. use the result of `t2` join `t3` to romve rows from `t1`
+
+```sql
+-- create t1, t2, t3 tables
+CREATE TABLE t1
+ (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
+UNIQUE KEY (id)
+DISTRIBUTED BY HASH (id)
+PROPERTIES('replication_num'='1', "function_column.sequence_col" = "c4");
+
+CREATE TABLE t2
+ (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
+DISTRIBUTED BY HASH (id)
+PROPERTIES('replication_num'='1');
+
+CREATE TABLE t3
+ (id INT)
+DISTRIBUTED BY HASH (id)
+PROPERTIES('replication_num'='1');
+
+-- insert data
+INSERT INTO t1 VALUES
+ (1, 1, '1', 1.0, '2000-01-01'),
+ (2, 2, '2', 2.0, '2000-01-02'),
+ (3, 3, '3', 3.0, '2000-01-03');
+
+INSERT INTO t2 VALUES
+ (1, 10, '10', 10.0, '2000-01-10'),
+ (2, 20, '20', 20.0, '2000-01-20'),
+ (3, 30, '30', 30.0, '2000-01-30'),
+ (4, 4, '4', 4.0, '2000-01-04'),
+ (5, 5, '5', 5.0, '2000-01-05');
+
+INSERT INTO t3 VALUES
+ (1),
+ (4),
+ (5);
+
+-- remove rows from t1
+DELETE FROM t1
+ USING t2 INNER JOIN t3 ON t2.id = t3.id
+ WHERE t1.id = t2.id;
+```
+
+the expect result is only remove the row where id = 1 in table t1
+
+```
++----+----+----+--------+------------+
+| id | c1 | c2 | c3 | c4 |
++----+----+----+--------+------------+
+| 2 | 2 | 2 | 2.0 | 2000-01-02 |
+| 3 | 3 | 3 | 3.0 | 2000-01-03 |
++----+----+----+--------+------------+
+```
+
+</version>
+
### Keywords
DELETE
diff --git
a/docs/en/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
b/docs/en/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
index 1ec248c653..49019ae62d 100644
---
a/docs/en/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
+++
b/docs/en/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
@@ -53,10 +53,6 @@ value:
<version since="dev">
-UPDATE_FROM
-
-</version>
-
```sql
UPDATE target_table
SET assignment_list
@@ -64,6 +60,8 @@ UPDATE target_table
WHERE condition
```
+</version>
+
#### Required Parameters
+ target_table: The target table of the data to be updated. Can be of the form
'db_name.table_name'
@@ -74,21 +72,13 @@ UPDATE target_table
<version since="dev">
-UPDATE_FROM
++ FROM additional_tables: Specifies one or more tables to use for selecting
rows to update or for setting new values. Note that if you want use target
table here, you should give it a alias explicitly.
</version>
-+ FROM additional_tables: Specifies one or more tables to use for selecting
rows to update or for setting new values. Note that if you want use target
table here, you should give it a alias explicitly.
-
#### Note
-<version since="dev">
-
-UPDATE_FROM
-
-</version>
-
-The current UPDATE statement only supports row updates on the Unique model.
+The current UPDATE statement only supports row updates on the UNIQUE KEY model.
### Example
@@ -108,10 +98,6 @@ UPDATE test SET v1 = v1+1 WHERE k1=1;
<version since="dev">
-UPDATE_FROM
-
-</version>
-
3. use the result of `t2` join `t3` to update `t1`
```sql
@@ -169,6 +155,8 @@ the expect result is only update the row where id = 1 in
table t1
+----+----+----+--------+------------+
```
+</version>
+
### Keywords
UPDATE
diff --git
a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
index 998fe38421..7c1198d31a 100644
---
a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
+++
b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/DELETE.md
@@ -36,25 +36,60 @@ DELETE
该操作会同时删除和此 base index 相关的 rollup index 的数据。
-语法：
+#### Syntax
+
+语法一：该语法只能指定过滤谓词
```SQL
-DELETE FROM table_name [PARTITION partition_name | PARTITIONS (p1, p2)]
+DELETE FROM table_name [PARTITION partition_name | PARTITIONS (partition_name
[, partition_name])]
WHERE
-column_name1 op { value | value_list } [ AND column_name2 op { value |
value_list } ...];
+column_name op { value | value_list } [ AND column_name op { value |
value_list } ...];
+```
+
+<version since="dev">
+
+语法二：该语法只能在UNIQUE KEY模型表上使用
+
+```sql
+DELETE FROM table_name
+ [PARTITION partition_name | PARTITIONS (partition_name [, partition_name])]
+ [USING additional_tables]
+ WHERE condition
```
-说明：
-1. op 的可选类型包括：=, >, <, >=, <=, !=, in, not in
-2. 使用聚合类的表模型（AGGREGATE、UNIQUE）只能指定 key 列上的条件。
-3. 当选定的 key 列不存在于某个 rollup 中时，无法进行 delete。
-4. 条件之间只能是“与”的关系。若希望达成“或”的关系，需要将条件分写在两个 DELETE 语句中。
-5. <version since="1.2" type="inline"> 如果为分区表，需要指定分区，如果不指定，doris
会从条件中推断出分区。两种情况下，doris 无法从条件中推断出分区: 1) 条件中不包含分区列；2) 分区列的 op 为 not
in。当分区表未指定分区，或者无法从条件中推断分区的时候，需要设置会话变量 delete_without_partition 为 true，此时 delete
会应用到所有分区。</version>
+</version>
+
+#### Required Parameters
+
++ table_name: 指定需要删除数据的表
++ column_name: 属于table_name的列
++ op: 逻辑比较操作符，可选类型包括：=, >, <, >=, <=, !=, in, not in
++ value | value_list: 做逻辑比较的值或值列表
+
+<version since="dev">
+
++ WHERE condition: 指定一个用于选择删除行的条件
+
+</version>
+
+
+#### Optional Parameters
-注意：
-1. 该语句可能会降低执行后一段时间内的查询效率。
-2. 影响程度取决于语句中指定的删除条件的数量。
-3. 指定的条件越多，影响越大。
++ PARTITION partition_name | PARTITIONS (partition_name [, partition_name]):
指定执行删除数据的分区名，如果表不存在此分区，则报错
+
+<version since="dev">
+
++ USING additional_tables: 如果需要在WHERE语句中使用其他的表来帮助识别需要删除的行，则可以在USING中指定这些表或者查询。
+
+</version>
+
+#### Note
+
+1. 使用聚合类的表模型（AGGREGATE、UNIQUE）只能指定 key 列上的条件。
+2. 当选定的 key 列不存在于某个 rollup 中时，无法进行 delete。
+3. 语法一中，条件之间只能是“与”的关系。若希望达成“或”的关系，需要将条件分写在两个 DELETE 语句中。
+4. <version since="1.2" type="inline"> 语法一中，如果为分区表，需要指定分区，如果不指定，doris
会从条件中推断出分区。两种情况下，doris 无法从条件中推断出分区: 1) 条件中不包含分区列；2) 分区列的 op 为 not
in。当分区表未指定分区，或者无法从条件中推断分区的时候，需要设置会话变量 delete_without_partition 为 true，此时 delete
会应用到所有分区。</version>
+5. 该语句可能会降低执行后一段时间内的查询效率。影响程度取决于语句中指定的删除条件的数量。指定的条件越多，影响越大。
### Example
@@ -79,6 +114,65 @@ column_name1 op { value | value_list } [ AND column_name2
op { value | value_lis
WHERE k1 >= 3 AND k2 = "abc";
```
+<version since="dev">
+
+1. 使用`t2`和`t3`表连接的结果，删除`t1`中的数据
+
+```sql
+-- 创建t1, t2, t3三张表
+CREATE TABLE t1
+ (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
+UNIQUE KEY (id)
+DISTRIBUTED BY HASH (id)
+PROPERTIES('replication_num'='1', "function_column.sequence_col" = "c4");
+
+CREATE TABLE t2
+ (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)
+DISTRIBUTED BY HASH (id)
+PROPERTIES('replication_num'='1');
+
+CREATE TABLE t3
+ (id INT)
+DISTRIBUTED BY HASH (id)
+PROPERTIES('replication_num'='1');
+
+-- 插入数据
+INSERT INTO t1 VALUES
+ (1, 1, '1', 1.0, '2000-01-01', '2000-01-01'),
+ (2, 2, '2', 2.0, '2000-01-02', '2000-01-02'),
+ (3, 3, '3', 3.0, '2000-01-03', '2000-01-03');
+
+INSERT INTO t2 VALUES
+ (1, 10, '10', 10.0, '2000-01-10'),
+ (2, 20, '20', 20.0, '2000-01-20'),
+ (3, 30, '30', 30.0, '2000-01-30'),
+ (4, 4, '4', 4.0, '2000-01-04'),
+ (5, 5, '5', 5.0, '2000-01-05');
+
+INSERT INTO t3 VALUES
+ (1),
+ (4),
+ (5);
+
+-- 删除 t1 中的数据
+DELETE FROM t1
+ USING t2 INNER JOIN t3 ON t2.id = t3.id
+ WHERE t1.id = t2.id;
+```
+
+预期结果为，删除了`t1`表`id`为`1`的列
+
+```
++----+----+----+--------+------------+
+| id | c1 | c2 | c3 | c4 |
++----+----+----+--------+------------+
+| 2 | 2 | 2 | 2.0 | 2000-01-02 |
+| 3 | 3 | 3 | 3.0 | 2000-01-03 |
++----+----+----+--------+------------+
+```
+
+</version>
+
### Keywords
DELETE
diff --git
a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
index 72164fba71..5fb4f0939e 100644
---
a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
+++
b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md
@@ -53,10 +53,6 @@ value:
<version since="dev">
-UPDATE_FROM
-
-</version>
-
```sql
UPDATE target_table
SET assignment_list
@@ -64,6 +60,7 @@ UPDATE target_table
WHERE condition
```
+</version>
#### Required Parameters
@@ -83,13 +80,7 @@ UPDATE_FROM
#### Note
-<version since="dev">
-
-UPDATE_FROM
-
-</version>
-
-当前 UPDATE 语句仅支持在 Unique 模型上的行更新。
+当前 UPDATE 语句仅支持在 UNIQUE KEY 模型上的行更新。
### Example
@@ -109,10 +100,6 @@ UPDATE test SET v1 = v1+1 WHERE k1=1;
<version since="dev">
-UPDATE_FROM
-
-</version>
-
3. 使用`t2`和`t3`表连接的结果，更新`t1`
```sql
@@ -170,6 +157,8 @@ UPDATE t1
+----+----+----+--------+------------+
```
+</version>
+
### Keywords
UPDATE
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index d556c548db..fb153f80bc 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -650,6 +650,8 @@ nonterminal StatementBase stmt, show_stmt, show_param,
help_stmt, load_stmt,
import_columns_stmt, import_delete_on_stmt, import_sequence_stmt,
import_where_stmt, install_plugin_stmt, uninstall_plugin_stmt,
import_preceding_filter_stmt, unlock_tables_stmt, lock_tables_stmt,
refresh_stmt, clean_stmt, analyze_stmt, show_mtmv_stmt;
+nonterminal FromClause opt_using_clause;
+
nonterminal String transaction_label;
nonterminal ImportColumnDesc import_column_desc;
nonterminal List<ImportColumnDesc> import_column_descs;
@@ -4181,9 +4183,20 @@ cancel_param ::=
// Delete stmt
delete_stmt ::=
- KW_DELETE KW_FROM table_name:table opt_partition_names:partitionNames
where_clause:wherePredicate
+ KW_DELETE KW_FROM table_name:table opt_partition_names:partitionNames
opt_using_clause:fromClause where_clause:wherePredicate
+ {:
+ RESULT = new DeleteStmt(table, partitionNames, fromClause,
wherePredicate);
+ :}
+ ;
+
+opt_using_clause ::=
+ /* empty */
+ {:
+ RESULT = null;
+ :}
+ | KW_USING table_ref_list:l
{:
- RESULT = new DeleteStmt(table, partitionNames, wherePredicate);
+ RESULT = new FromClause(l);
:}
;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/DeleteStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/DeleteStmt.java
index 03ac98ebe6..a36495251c 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/DeleteStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/DeleteStmt.java
@@ -18,7 +18,12 @@
package org.apache.doris.analysis;
import org.apache.doris.analysis.CompoundPredicate.Operator;
+import org.apache.doris.catalog.Column;
+import org.apache.doris.catalog.Database;
import org.apache.doris.catalog.Env;
+import org.apache.doris.catalog.KeysType;
+import org.apache.doris.catalog.OlapTable;
+import org.apache.doris.catalog.Table;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.Config;
import org.apache.doris.common.ErrorCode;
@@ -27,39 +32,60 @@ import org.apache.doris.common.UserException;
import org.apache.doris.common.util.Util;
import org.apache.doris.mysql.privilege.PrivPredicate;
import org.apache.doris.qe.ConnectContext;
+import org.apache.doris.qe.SessionVariable;
+import com.google.common.annotations.VisibleForTesting;
import com.google.common.base.Joiner;
+import com.google.common.base.Preconditions;
import com.google.common.collect.Lists;
import java.util.LinkedList;
import java.util.List;
public class DeleteStmt extends DdlStmt {
- private final TableName tbl;
+ private final TableName tableName;
private final PartitionNames partitionNames;
- private Expr wherePredicate;
+ private final FromClause fromClause;
+ private final Expr wherePredicate;
- private List<Predicate> deleteConditions;
+ private final List<Predicate> deleteConditions = new LinkedList<>();
+
+ private InsertStmt insertStmt;
+ private Table targetTable;
+ private final List<SelectListItem> selectListItems = Lists.newArrayList();
+ private final List<String> cols = Lists.newArrayList();
public DeleteStmt(TableName tableName, PartitionNames partitionNames, Expr
wherePredicate) {
- this.tbl = tableName;
+ this(tableName, partitionNames, null, wherePredicate);
+ }
+
+ public DeleteStmt(TableName tableName, PartitionNames partitionNames,
FromClause fromClause, Expr wherePredicate) {
+ this.tableName = tableName;
this.partitionNames = partitionNames;
+ this.fromClause = fromClause;
this.wherePredicate = wherePredicate;
- this.deleteConditions = new LinkedList<Predicate>();
}
public String getTableName() {
- return tbl.getTbl();
+ return tableName.getTbl();
}
public String getDbName() {
- return tbl.getDb();
+ return tableName.getDb();
}
public List<String> getPartitionNames() {
return partitionNames == null ? Lists.newArrayList() :
partitionNames.getPartitionNames();
}
+ public FromClause getFromClause() {
+ return fromClause;
+ }
+
+ public InsertStmt getInsertStmt() {
+ return insertStmt;
+ }
+
public List<Predicate> getDeleteConditions() {
return deleteConditions;
}
@@ -68,13 +94,7 @@ public class DeleteStmt extends DdlStmt {
public void analyze(Analyzer analyzer) throws UserException {
super.analyze(analyzer);
- if (tbl == null) {
- throw new AnalysisException("Table is not set");
- }
-
- tbl.analyze(analyzer);
- // disallow external catalog
- Util.prohibitExternalCatalog(tbl.getCtl(),
this.getClass().getSimpleName());
+ analyzeTargetTable(analyzer);
if (partitionNames != null) {
partitionNames.analyze(analyzer);
@@ -83,23 +103,108 @@ public class DeleteStmt extends DdlStmt {
}
}
- if (wherePredicate == null) {
- throw new AnalysisException("Where clause is not set");
+ // analyze predicate
+ if (fromClause == null) {
+ analyzePredicate(wherePredicate);
+ } else {
+ constructInsertStmt();
}
+ }
- // analyze predicate
- analyzePredicate(wherePredicate);
-
- // check access
- if
(!Env.getCurrentEnv().getAccessManager().checkTblPriv(ConnectContext.get(),
tbl.getDb(), tbl.getTbl(),
-
PrivPredicate.LOAD)) {
-
ErrorReport.reportAnalysisException(ErrorCode.ERR_TABLEACCESS_DENIED_ERROR,
"LOAD",
-
ConnectContext.get().getQualifiedUser(),
-
ConnectContext.get().getRemoteIP(), tbl.getDb() + ": " + tbl.getTbl());
+ private void constructInsertStmt() throws AnalysisException {
+ if (ConnectContext.get() != null &&
ConnectContext.get().getSessionVariable().isInDebugMode()) {
+ throw new AnalysisException("Delete is forbidden since current
session is in debug mode."
+ + " Please check the following session variables: "
+ + String.join(", ", SessionVariable.DEBUG_VARIABLES));
+ }
+ for (Column column : targetTable.getColumns()) {
+ Expr expr;
+ if (!column.isVisible() &&
column.getName().equalsIgnoreCase(Column.DELETE_SIGN)) {
+ expr = new BoolLiteral(true);
+ } else if (column.isKey() || !column.isVisible() ||
(!column.isAllowNull() && !column.hasDefaultValue())) {
+ expr = new SlotRef(tableName, column.getName());
+ } else {
+ continue;
+ }
+ selectListItems.add(new SelectListItem(expr, null));
+ cols.add(column.getName());
}
+
+ FromClause fromUsedInInsert;
+ TableRef tableRef = new TableRef(tableName, null, partitionNames);
+ if (fromClause == null) {
+ fromUsedInInsert = new FromClause(Lists.newArrayList(tableRef));
+ } else {
+ fromUsedInInsert = fromClause.clone();
+ fromUsedInInsert.getTableRefs().add(0, tableRef);
+ }
+ SelectStmt selectStmt = new SelectStmt(
+ // select list
+ new SelectList(selectListItems, false),
+ // from clause
+ fromUsedInInsert,
+ // where expr
+ wherePredicate,
+ // group by
+ null,
+ // having
+ null,
+ // order by
+ null,
+ // limit
+ LimitElement.NO_LIMIT
+ );
+
+ insertStmt = new InsertStmt(
+ new InsertTarget(tableName, null),
+ null,
+ cols,
+ new InsertSource(selectStmt),
+ null);
}
- private void analyzePredicate(Expr predicate) throws AnalysisException {
+ private void analyzeTargetTable(Analyzer analyzer) throws
AnalysisException {
+ // step1: analyze table name and origin table alias
+ if (tableName == null) {
+ throw new AnalysisException("Table is not set");
+ }
+ tableName.analyze(analyzer);
+ // disallow external catalog
+ Util.prohibitExternalCatalog(tableName.getCtl(),
this.getClass().getSimpleName());
+ // check load privilege, select privilege will check when analyze
insert stmt
+ if (!Env.getCurrentEnv().getAccessManager()
+ .checkTblPriv(ConnectContext.get(), tableName.getDb(),
tableName.getTbl(), PrivPredicate.LOAD)) {
+
ErrorReport.reportAnalysisException(ErrorCode.ERR_SPECIFIC_ACCESS_DENIED_ERROR,
"LOAD",
+ ConnectContext.get().getQualifiedUser(),
+ ConnectContext.get().getRemoteIP(), tableName.getDb() + ":
" + tableName.getTbl());
+ }
+
+ // step2: resolve table name with catalog, only unique olap table
could be updated with using
+ String dbName = tableName.getDb();
+ String targetTableName = tableName.getTbl();
+ Preconditions.checkNotNull(dbName);
+ Preconditions.checkNotNull(targetTableName);
+ Database database =
Env.getCurrentInternalCatalog().getDbOrAnalysisException(dbName);
+ targetTable = database.getTableOrAnalysisException(tableName.getTbl());
+ if (fromClause != null && (targetTable.getType() !=
Table.TableType.OLAP
+ || ((OlapTable) targetTable).getKeysType() !=
KeysType.UNIQUE_KEYS)) {
+ throw new AnalysisException("Only unique table could use delete
with using.");
+ }
+
+ // step3: register table to ensure we could analyze column name on the
left side of set exprs.
+ targetTable.readLock();
+ try {
+ analyzer.registerOlapTable(targetTable, tableName, null);
+ } finally {
+ targetTable.readUnlock();
+ }
+ }
+
+ @VisibleForTesting
+ void analyzePredicate(Expr predicate) throws AnalysisException {
+ if (predicate == null) {
+ throw new AnalysisException("Where clause is not set");
+ }
if (predicate instanceof BinaryPredicate) {
BinaryPredicate binaryPredicate = (BinaryPredicate) predicate;
Expr leftExpr = binaryPredicate.getChild(0);
@@ -154,7 +259,7 @@ public class DeleteStmt extends DdlStmt {
@Override
public String toSql() {
StringBuilder sb = new StringBuilder();
- sb.append("DELETE FROM ").append(tbl.toSql());
+ sb.append("DELETE FROM ").append(tableName.toSql());
if (partitionNames != null) {
sb.append(" PARTITION (");
sb.append(Joiner.on(",
").join(partitionNames.getPartitionNames()));
@@ -163,5 +268,4 @@ public class DeleteStmt extends DdlStmt {
sb.append(" WHERE ").append(wherePredicate.toSql());
return sb.toString();
}
-
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/PartitionNames.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/PartitionNames.java
index 12e18bbbe2..1140dfc677 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/PartitionNames.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/PartitionNames.java
@@ -73,7 +73,7 @@ public class PartitionNames implements ParseNode, Writable {
throw new AnalysisException("No partition specified in partition
lists");
}
// check if partition name is not empty string
- if (partitionNames.stream().anyMatch(entity ->
Strings.isNullOrEmpty(entity))) {
+ if (partitionNames.stream().anyMatch(Strings::isNullOrEmpty)) {
throw new AnalysisException("there are empty partition name");
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/Column.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/Column.java
index 72bb65a20a..0201c41656 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/Column.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/Column.java
@@ -792,6 +792,10 @@ public class Column implements Writable,
GsonPostProcessable {
isCompoundKey = compoundKey;
}
+ public boolean hasDefaultValue() {
+ return defaultValue != null || realDefaultValue != null ||
defaultValueExprDef != null;
+ }
+
@Override
public void gsonPostProcess() throws IOException {
// This just for bugfix. Because when user upgrade from 0.x to 1.1.x,
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java
index bf9f2dbbd1..964952ed04 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/StmtExecutor.java
@@ -22,6 +22,7 @@ import org.apache.doris.analysis.ArrayLiteral;
import org.apache.doris.analysis.CreateTableAsSelectStmt;
import org.apache.doris.analysis.DdlStmt;
import org.apache.doris.analysis.DecimalLiteral;
+import org.apache.doris.analysis.DeleteStmt;
import org.apache.doris.analysis.DropTableStmt;
import org.apache.doris.analysis.EnterStmt;
import org.apache.doris.analysis.ExecuteStmt;
@@ -609,7 +610,11 @@ public class StmtExecutor implements ProfileWriter {
} else if (parsedStmt instanceof UpdateStmt) {
handleUpdateStmt();
} else if (parsedStmt instanceof DdlStmt) {
- handleDdlStmt();
+ if (parsedStmt instanceof DeleteStmt && ((DeleteStmt)
parsedStmt).getFromClause() != null) {
+ handleDeleteStmt();
+ } else {
+ handleDdlStmt();
+ }
} else if (parsedStmt instanceof ShowStmt) {
handleShow();
} else if (parsedStmt instanceof KillStmt) {
@@ -1934,13 +1939,26 @@ public class StmtExecutor implements ProfileWriter {
parsedStmt = updateStmt.getInsertStmt();
execute();
if (MysqlStateType.ERR.equals(context.getState().getStateType())) {
- LOG.warn("update data error, stmt={}", parsedStmt.toSql());
+ LOG.warn("update data error, stmt={}", updateStmt.toSql());
}
} catch (Exception e) {
LOG.warn("update data error, stmt={}", parsedStmt.toSql(), e);
}
}
+ private void handleDeleteStmt() {
+ try {
+ DeleteStmt deleteStmt = (DeleteStmt) parsedStmt;
+ parsedStmt = deleteStmt.getInsertStmt();
+ execute();
+ if (MysqlStateType.ERR.equals(context.getState().getStateType())) {
+ LOG.warn("delete data error, stmt={}", deleteStmt.toSql());
+ }
+ } catch (Exception e) {
+ LOG.warn("delete data error, stmt={}", parsedStmt.toSql(), e);
+ }
+ }
+
private void handleDdlStmt() {
try {
DdlExecutor.execute(context.getEnv(), (DdlStmt) parsedStmt);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/DeleteStmtTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/DeleteStmtTest.java
index 1e21c201bc..a009bc1631 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/DeleteStmtTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/DeleteStmtTest.java
@@ -77,7 +77,7 @@ public class DeleteStmtTest {
DeleteStmt deleteStmt = new DeleteStmt(new TableName(internalCtl,
"testDb", "testTbl"),
new PartitionNames(false, Lists.newArrayList("partition")),
likePredicate);
try {
- deleteStmt.analyze(analyzer);
+ deleteStmt.analyzePredicate(likePredicate);
} catch (UserException e) {
Assert.assertTrue(e.getMessage().contains("Where clause only
supports compound predicate, binary predicate, is_null predicate or in
predicate"));
}
@@ -93,7 +93,7 @@ public class DeleteStmtTest {
new PartitionNames(false, Lists.newArrayList("partition")),
compoundPredicate);
try {
- deleteStmt.analyze(analyzer);
+ deleteStmt.analyzePredicate(compoundPredicate);
} catch (UserException e) {
Assert.assertTrue(e.getMessage().contains("should be AND"));
}
@@ -106,7 +106,7 @@ public class DeleteStmtTest {
deleteStmt = new DeleteStmt(new TableName(internalCtl, "testDb",
"testTbl"),
new PartitionNames(false, Lists.newArrayList("partition")),
compoundPredicate);
try {
- deleteStmt.analyze(analyzer);
+ deleteStmt.analyzePredicate(compoundPredicate);
} catch (UserException e) {
Assert.assertTrue(e.getMessage().contains("Where clause only
supports compound predicate, binary predicate, is_null predicate or in
predicate"));
}
@@ -121,7 +121,7 @@ public class DeleteStmtTest {
deleteStmt = new DeleteStmt(new TableName(internalCtl, "testDb",
"testTbl"),
new PartitionNames(false, Lists.newArrayList("partition")),
compoundPredicate);
try {
- deleteStmt.analyze(analyzer);
+ deleteStmt.analyzePredicate(compoundPredicate);
} catch (UserException e) {
Assert.assertTrue(e.getMessage().contains("Right expr of binary
predicate should be value"));
}
@@ -136,7 +136,7 @@ public class DeleteStmtTest {
deleteStmt = new DeleteStmt(new TableName(internalCtl, "testDb",
"testTbl"),
new PartitionNames(false, Lists.newArrayList("partition")),
compoundPredicate);
try {
- deleteStmt.analyze(analyzer);
+ deleteStmt.analyzePredicate(compoundPredicate);
} catch (UserException e) {
Assert.assertTrue(e.getMessage().contains("Left expr of binary
predicate should be column name"));
}
@@ -149,7 +149,7 @@ public class DeleteStmtTest {
deleteStmt = new DeleteStmt(new TableName(internalCtl, "testDb",
"testTbl"), null, compoundPredicate);
try {
- deleteStmt.analyze(analyzer);
+ deleteStmt.analyzePredicate(compoundPredicate);
} catch (UserException e) {
e.printStackTrace();
Assert.assertTrue(e.getMessage().contains("Partition is not set"));
@@ -175,7 +175,7 @@ public class DeleteStmtTest {
deleteStmt = new DeleteStmt(new TableName(internalCtl, "testDb",
"testTbl"),
new PartitionNames(false, Lists.newArrayList("partition")),
compoundPredicate);
try {
- deleteStmt.analyze(analyzer);
+ deleteStmt.analyzePredicate(compoundPredicate);
} catch (UserException e) {
Assert.fail();
}
@@ -183,21 +183,6 @@ public class DeleteStmtTest {
// multi partition
deleteStmt = new DeleteStmt(new TableName(internalCtl, "testDb",
"testTbl"),
new PartitionNames(false, Lists.newArrayList("partition1",
"partiton2")), compoundPredicate);
- try {
- deleteStmt.analyze(analyzer);
- Assert.assertEquals(Lists.newArrayList("partition1", "partiton2"),
deleteStmt.getPartitionNames());
- } catch (UserException e) {
- Assert.fail();
- }
-
- // no partition
- deleteStmt = new DeleteStmt(new TableName(internalCtl, "testDb",
"testTbl"), null, compoundPredicate);
- try {
- deleteStmt.analyze(analyzer);
- Assert.assertEquals(Lists.newArrayList(),
deleteStmt.getPartitionNames());
- } catch (UserException e) {
- Assert.fail();
- }
+ Assert.assertEquals(Lists.newArrayList("partition1", "partiton2"),
deleteStmt.getPartitionNames());
}
-
}
diff --git a/regression-test/data/delete_p0/test_delete_using.out
b/regression-test/data/delete_p0/test_delete_using.out
new file mode 100644
index 0000000000..34c9f2e8bb
--- /dev/null
+++ b/regression-test/data/delete_p0/test_delete_using.out
@@ -0,0 +1,5 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !complex_delete --
+2 2 2 2.0 2000-01-02
+3 3 3 3.0 2000-01-03
+
diff --git a/regression-test/suites/delete_p0/test_delete_using.groovy
b/regression-test/suites/delete_p0/test_delete_using.groovy
new file mode 100644
index 0000000000..505c18cf15
--- /dev/null
+++ b/regression-test/suites/delete_p0/test_delete_using.groovy
@@ -0,0 +1,58 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_delete_using") {
+ def tbName1 = "test_delete_unique_1"
+ def tbName2 = "test_delete_unique_2"
+ def tbName3 = "test_delete_unique_3"
+
+ sql "DROP TABLE IF EXISTS ${tbName1}"
+ sql "DROP TABLE IF EXISTS ${tbName2}"
+ sql "DROP TABLE IF EXISTS ${tbName3}"
+
+ // test complex update syntax
+ sql """
+ create table ${tbName1} (id int, c1 bigint, c2 string, c3 double, c4
date) unique key (id) distributed by hash(id) properties('replication_num'='1');
+ """
+ sql """
+ create table ${tbName2} (id int, c1 bigint, c2 string, c3 double, c4
date) unique key (id) distributed by hash(id) properties('replication_num'='1');
+ """
+ sql """
+ create table ${tbName3} (id int) distributed by hash (id)
properties('replication_num'='1');
+ """
+ sql """
+ insert into ${tbName1} values(1, 1, '1', 1.0, '2000-01-01'),(2, 2,
'2', 2.0, '2000-01-02'),(3, 3, '3', 3.0, '2000-01-03');
+ """
+ sql """
+ insert into ${tbName2} values(1, 10, '10', 10.0, '2000-01-10'),(2, 20,
'20', 20.0, '2000-01-20'),(3, 30, '30', 30.0, '2000-01-30'),(4, 4, '4', 4.0,
'2000-01-04'),(5, 5, '5', 5.0, '2000-01-05');
+ """
+ sql """
+ insert into ${tbName3} values(1), (4), (5);
+ """
+
+ sql """
+ DELETE FROM ${tbName1} USING ${tbName2} inner join ${tbName3} on
${tbName2}.id = ${tbName3}.id where ${tbName1}.id = ${tbName2}.id;
+ """
+
+ qt_complex_delete """
+ select * from ${tbName1} order by id;
+ """
+
+ sql "DROP TABLE IF EXISTS ${tbName1}"
+ sql "DROP TABLE IF EXISTS ${tbName2}"
+ sql "DROP TABLE IF EXISTS ${tbName3}"
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
