This is an automated email from the ASF dual-hosted git repository.
zhangchen 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 4510e16845 [improvement](delete) support delete predicate on value
column for merge-on-write unique table (#21933)
4510e16845 is described below
commit 4510e16845e0d7b98f9243753fbb8b61112ee213
Author: bobhan1 <bh2444151...@outlook.com>
AuthorDate: Wed Aug 16 12:18:05 2023 +0800
[improvement](delete) support delete predicate on value column for
merge-on-write unique table (#21933)
Previously, delete statement with conditions on value columns are only
supported on duplicate tables. After we introduce delete sign mechanism to do
batch delete, a delete statement with conditions on value columns on unique
tables will be transformed into the corresponding insert into ...,
__DELETE_SIGN__ select ... statement. However, for unique table with
merge-on-write enabled, the overhead of inserting these data can be eliminated.
So this PR add the ability to allow delete predi [...]
---
be/src/olap/delete_handler.cpp | 7 +-
be/test/olap/delete_handler_test.cpp | 14 +-
.../java/org/apache/doris/analysis/DeleteStmt.java | 17 ++-
.../java/org/apache/doris/load/DeleteHandler.java | 18 ++-
.../data/delete_p0/test_delete_on_value.out | 94 +++++++++++++
.../suites/delete_p0/test_delete_on_value.groovy | 153 +++++++++++++++++++++
6 files changed, 280 insertions(+), 23 deletions(-)
diff --git a/be/src/olap/delete_handler.cpp b/be/src/olap/delete_handler.cpp
index b7bbfe2908..1df44e0004 100644
--- a/be/src/olap/delete_handler.cpp
+++ b/be/src/olap/delete_handler.cpp
@@ -177,12 +177,9 @@ Status DeleteHandler::check_condition_valid(const
TabletSchema& schema, const TC
// the condition column type should not be float or double.
const TabletColumn& column = schema.column(field_index);
- if ((!column.is_key() && schema.keys_type() != KeysType::DUP_KEYS) ||
- column.type() == FieldType::OLAP_FIELD_TYPE_DOUBLE ||
+ if (column.type() == FieldType::OLAP_FIELD_TYPE_DOUBLE ||
column.type() == FieldType::OLAP_FIELD_TYPE_FLOAT) {
- return Status::Error<DELETE_INVALID_CONDITION>(
- "field is not key column, or storage model is not duplicate,
or data type is float "
- "or double.");
+ return Status::Error<DELETE_INVALID_CONDITION>("data type is float or
double.");
}
// Check operator and operands size are matched.
diff --git a/be/test/olap/delete_handler_test.cpp
b/be/test/olap/delete_handler_test.cpp
index 28308b1048..c2aa5f1efe 100644
--- a/be/test/olap/delete_handler_test.cpp
+++ b/be/test/olap/delete_handler_test.cpp
@@ -406,7 +406,6 @@ TEST_F(TestDeleteConditionHandler,
StoreCondInvalidParameters) {
DeletePredicatePB del_pred;
Status failed_res =
DeleteHandler::generate_delete_predicate(*tablet->tablet_schema(),
conditions,
&del_pred);
- ;
EXPECT_EQ(Status::Error<DELETE_INVALID_PARAMETERS>(""), failed_res);
}
@@ -423,7 +422,6 @@ TEST_F(TestDeleteConditionHandler,
StoreCondNonexistentColumn) {
DeletePredicatePB del_pred;
Status failed_res =
DeleteHandler::generate_delete_predicate(*tablet->tablet_schema(),
conditions,
&del_pred);
- ;
EXPECT_EQ(Status::Error<DELETE_INVALID_CONDITION>(""), failed_res);
// 'v'是value列
@@ -434,10 +432,9 @@ TEST_F(TestDeleteConditionHandler,
StoreCondNonexistentColumn) {
condition.condition_values.push_back("5");
conditions.push_back(condition);
- failed_res =
DeleteHandler::generate_delete_predicate(*tablet->tablet_schema(), conditions,
- &del_pred);
- ;
- EXPECT_EQ(Status::Error<DELETE_INVALID_CONDITION>(""), failed_res);
+ Status success_res =
DeleteHandler::generate_delete_predicate(*tablet->tablet_schema(),
+ conditions,
&del_pred);
+ EXPECT_EQ(Status::OK(), success_res);
// value column in duplicate model can be deleted;
conditions.clear();
@@ -447,9 +444,8 @@ TEST_F(TestDeleteConditionHandler,
StoreCondNonexistentColumn) {
condition.condition_values.push_back("5");
conditions.push_back(condition);
- Status success_res =
DeleteHandler::generate_delete_predicate(*dup_tablet->tablet_schema(),
- conditions,
&del_pred);
- ;
+ success_res =
DeleteHandler::generate_delete_predicate(*dup_tablet->tablet_schema(),
conditions,
+ &del_pred);
EXPECT_EQ(Status::OK(), success_res);
}
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 ac28d5850e..351fd4f82c 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
@@ -329,10 +329,19 @@ public class DeleteStmt extends DdlStmt {
// Due to rounding errors, most floating-point numbers end up
being slightly imprecise,
// it also means that numbers expected to be equal often differ
slightly, so we do not allow compare with
// floating-point numbers, floating-point number not allowed in
where clause
- if (!column.isKey() && table.getKeysType() != KeysType.DUP_KEYS
- || column.getDataType().isFloatingPointType()) {
- throw new AnalysisException("Column[" + columnName + "] is not
key column or storage model "
- + "is not duplicate or column type is float or
double.");
+ if (column.getDataType().isFloatingPointType()) {
+ throw new AnalysisException("Column[" + columnName + "] type
is float or double.");
+ }
+ if (!column.isKey()) {
+ if (table.getKeysType() == KeysType.AGG_KEYS) {
+ throw new AnalysisException("delete predicate on value
column only supports Unique table with"
+ + " merge-on-write enabled and Duplicate table,
but " + "Table[" + table.getName()
+ + "] is an Aggregate table.");
+ } else if (table.getKeysType() == KeysType.UNIQUE_KEYS &&
!table.getEnableUniqueKeyMergeOnWrite()) {
+ throw new AnalysisException("delete predicate on value
column only supports Unique table with"
+ + " merge-on-write enabled and Duplicate table,
but " + "Table[" + table.getName()
+ + "] is an Aggregate table.");
+ }
}
if (condition instanceof BinaryPredicate) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/load/DeleteHandler.java
b/fe/fe-core/src/main/java/org/apache/doris/load/DeleteHandler.java
index 578b3cd4d0..494fbd5db3 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/load/DeleteHandler.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/load/DeleteHandler.java
@@ -688,11 +688,19 @@ public class DeleteHandler implements Writable {
// Due to rounding errors, most floating-point numbers end up
being slightly imprecise,
// it also means that numbers expected to be equal often differ
slightly, so we do not allow compare with
// floating-point numbers, floating-point number not allowed in
where clause
- if (!column.isKey() && table.getKeysType() != KeysType.DUP_KEYS
- || column.getDataType().isFloatingPointType()) {
- //
ErrorReport.reportDdlException(ErrorCode.ERR_NOT_KEY_COLUMN, columnName);
- throw new DdlException("Column[" + columnName + "] is not key
column or storage model "
- + "is not duplicate or column type is float or
double.");
+ if (column.getDataType().isFloatingPointType()) {
+ throw new DdlException("Column[" + columnName + "] type is
float or double.");
+ }
+ if (!column.isKey()) {
+ if (table.getKeysType() == KeysType.AGG_KEYS) {
+ throw new DdlException("delete predicate on value column
only supports Unique table with"
+ + " merge-on-write enabled and Duplicate table,
but " + "Table[" + table.getName()
+ + "] is an Aggregate table.");
+ } else if (table.getKeysType() == KeysType.UNIQUE_KEYS &&
!table.getEnableUniqueKeyMergeOnWrite()) {
+ throw new DdlException("delete predicate on value column
only supports Unique table with"
+ + " merge-on-write enabled and Duplicate table,
but " + "Table[" + table.getName()
+ + "] is an Aggregate table.");
+ }
}
if (condition instanceof BinaryPredicate) {
diff --git a/regression-test/data/delete_p0/test_delete_on_value.out
b/regression-test/data/delete_p0/test_delete_on_value.out
new file mode 100644
index 0000000000..2bc8846297
--- /dev/null
+++ b/regression-test/data/delete_p0/test_delete_on_value.out
@@ -0,0 +1,94 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+1 1 1
+2 2 2
+3 3 3
+4 4 4
+5 5 5
+6 6 6
+7 7 7
+8 8 8
+9 9 9
+
+-- !sql --
+1 1 1
+2 2 2
+3 3 3
+5 5 5
+6 6 6
+7 7 7
+8 8 8
+9 9 9
+
+-- !sql --
+1 1 1
+2 2 2
+8 8 8
+9 9 9
+
+-- !sql --
+1 1 1 0
+2 2 2 0
+3 3 3 0
+4 4 4 0
+5 5 5 0
+6 6 6 0
+7 7 7 0
+8 8 8 0
+9 9 9 0
+
+-- !sql --
+1 1 1
+2 2 2
+4 4 4
+5 5 5
+8 8 8
+9 9 9
+
+-- !sql --
+1 1 1
+2 2 2
+4 4 4
+8 8 8
+9 9 9
+
+-- !sql --
+1 1 1 0
+2 2 2 0
+3 3 3 0
+4 4 4 0
+4 4 4 0
+5 5 5 0
+5 5 5 0
+6 6 6 0
+7 7 7 0
+8 8 8 0
+9 9 9 0
+
+-- !sql --
+1 1 10
+
+-- !sql --
+1 1 5 0 3 5
+1 1 10 0 2 10
+
+-- !sql --
+
+-- !sql --
+1 1 5 0 3 5
+1 1 10 0 2 10
+
+-- !sql --
+1 1 10
+
+-- !sql --
+1 1 5 0 3 5
+1 1 10 0 2 10
+
+-- !sql --
+
+-- !sql --
+1 \N \N 1 4 10
+1 1 5 0 3 5
+1 1 10 0 2 10
+
diff --git a/regression-test/suites/delete_p0/test_delete_on_value.groovy
b/regression-test/suites/delete_p0/test_delete_on_value.groovy
new file mode 100644
index 0000000000..353e3d64f7
--- /dev/null
+++ b/regression-test/suites/delete_p0/test_delete_on_value.groovy
@@ -0,0 +1,153 @@
+// 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_on_value") {
+ sql 'set enable_nereids_planner=false'
+ sql "set experimental_enable_nereids_planner=false;"
+ sql 'set enable_nereids_dml=false'
+
+ sql "set skip_storage_engine_merge=false;"
+ sql "set skip_delete_bitmap=false;"
+ sql "set skip_delete_predicate=false;"
+ def tableName = "test_delete_on_value"
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """ CREATE TABLE ${tableName} (
+ `x` BIGINT NOT NULL,
+ `y` BIGINT NULL,
+ `z` BIGINT NULL)
+ ENGINE=OLAP
+ UNIQUE KEY(`x`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`x`) BUCKETS 4
+ PROPERTIES (
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true"
+ );"""
+ sql """ insert into ${tableName}
values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7),(8,8,8),(9,9,9);
"""
+ qt_sql "select * from ${tableName} order by x,y,z;"
+ sql "delete from ${tableName} where y=4;"
+ qt_sql "select * from ${tableName} order by x,y,z;"
+ sql "delete from ${tableName} where z>=3 and z<=7;"
+ qt_sql "select * from ${tableName} order by x,y,z;"
+ sql "set skip_delete_predicate=true;"
+ qt_sql "select x,y,z,__DORIS_DELETE_SIGN__ from ${tableName} order by
x,y,z,__DORIS_DELETE_SIGN__;"
+ sql "set skip_delete_predicate=false;"
+ sql "insert into ${tableName} values(4,4,4),(5,5,5);"
+ qt_sql "select * from ${tableName} order by x,y,z;"
+ sql "delete from ${tableName} where y=5;"
+ qt_sql "select * from ${tableName} order by x,y,z;"
+ sql "set skip_storage_engine_merge=true;"
+ sql "set skip_delete_bitmap=true;"
+ sql "set skip_delete_predicate=true;"
+ qt_sql "select x,y,z,__DORIS_DELETE_SIGN__ from ${tableName} order by
x,y,z,__DORIS_DELETE_SIGN__;"
+ sql "DROP TABLE IF EXISTS ${tableName};"
+
+
+ def tableName2 = "test_delete_on_value2"
+ sql """ DROP TABLE IF EXISTS ${tableName2} """
+ sql """ CREATE TABLE ${tableName2} (
+ `x` BIGINT NOT NULL,
+ `y` BIGINT REPLACE_IF_NOT_NULL NULL,
+ `z` BIGINT REPLACE_IF_NOT_NULL NULL)
+ ENGINE=OLAP
+ AGGREGATE KEY(`x`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`x`) BUCKETS 4
+ PROPERTIES (
+ "replication_num" = "1"
+ );"""
+ sql """ insert into ${tableName2} values(1,1,1); """
+ test {
+ sql "delete from ${tableName2} where y=4;"
+ exception "delete predicate on value column only supports Unique table
with merge-on-write enabled and Duplicate table, but
Table[test_delete_on_value2] is an Aggregate table."
+ }
+
+ sql "set skip_storage_engine_merge=false;"
+ sql "set skip_delete_bitmap=false;"
+ sql "set skip_delete_predicate=false;"
+ def tableName3 = "test_delete_on_value_with_seq_col"
+ sql """ DROP TABLE IF EXISTS ${tableName3} """
+ sql """ CREATE TABLE ${tableName3} (
+ `x` BIGINT NOT NULL,
+ `y` BIGINT NULL,
+ `z` BIGINT NULL)
+ ENGINE=OLAP
+ UNIQUE KEY(`x`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`x`) BUCKETS 4
+ PROPERTIES (
+ "replication_num" = "1",
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "z"
+ );"""
+ sql "insert into ${tableName3} values(1,1,10);"
+ sql "insert into ${tableName3} values(1,1,5);"
+ qt_sql "select * from ${tableName3} order by x,y,z;"
+ sql "set skip_storage_engine_merge=true;"
+ sql "set skip_delete_bitmap=true;"
+ sql "set skip_delete_predicate=true;"
+ qt_sql "select * from ${tableName3} order by x,y,z;"
+ sql "set skip_storage_engine_merge=false;"
+ sql "set skip_delete_bitmap=false;"
+ sql "set skip_delete_predicate=false;"
+ sql "delete from ${tableName3} where z>=10;"
+ qt_sql "select * from ${tableName3} order by x,y,z;"
+ sql "set skip_storage_engine_merge=true;"
+ sql "set skip_delete_bitmap=true;"
+ sql "set skip_delete_predicate=true;"
+ qt_sql "select * from ${tableName3} order by x,y,z;"
+ sql "DROP TABLE IF EXISTS ${tableName3}"
+
+ sql "set skip_storage_engine_merge=false;"
+ sql "set skip_delete_bitmap=false;"
+ sql "set skip_delete_predicate=false;"
+ def tableName4 = "test_delete_on_value_with_seq_col_mor"
+ sql """ DROP TABLE IF EXISTS ${tableName4} """
+ sql """ CREATE TABLE ${tableName4} (
+ `x` BIGINT NOT NULL,
+ `y` BIGINT NULL,
+ `z` BIGINT NULL)
+ ENGINE=OLAP
+ UNIQUE KEY(`x`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`x`) BUCKETS 4
+ PROPERTIES (
+ "replication_num" = "1",
+ "function_column.sequence_col" = "z"
+ );"""
+ sql "insert into ${tableName4} values(1,1,10);"
+ sql "insert into ${tableName4} values(1,1,5);"
+ qt_sql "select * from ${tableName4} order by x,y,z;"
+ sql "set skip_storage_engine_merge=true;"
+ sql "set skip_delete_bitmap=true;"
+ sql "set skip_delete_predicate=true;"
+ qt_sql "select * from ${tableName4} order by x,y,z;"
+ sql "set skip_storage_engine_merge=false;"
+ sql "set skip_delete_bitmap=false;"
+ sql "set skip_delete_predicate=false;"
+ sql "delete from ${tableName4} where z>=10;"
+ qt_sql "select * from ${tableName4} order by x,y,z;"
+ sql "set skip_storage_engine_merge=true;"
+ sql "set skip_delete_bitmap=true;"
+ sql "set skip_delete_predicate=true;"
+ qt_sql "select * from ${tableName4} order by x,y,z;"
+ sql "DROP TABLE IF EXISTS ${tableName4};"
+
+ sql "set skip_storage_engine_merge=false;"
+ sql "set skip_delete_bitmap=false;"
+ sql "set skip_delete_predicate=false;"
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
