This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 1d2d0bd411c5dbe6e5270f6e247152fb4bc3f521
Author: huanghaibin <284824...@qq.com>
AuthorDate: Tue Mar 5 20:45:28 2024 +0800
[fix](update) Update set value should consider sequence column (#31626)
When using update command to set column value, if the column is sequence
column, the column 'DORIS_SEQUENCE_COL' should also be set to the same value.
---
.../java/org/apache/doris/analysis/UpdateStmt.java | 14 ++++++-
.../trees/plans/commands/UpdateCommand.java | 13 +++++-
.../unique/test_unique_table_new_sequence.out | 20 ++++-----
regression-test/data/update/test_update.out | 49 ++++++++++++++++++++++
regression-test/suites/update/test_update.groovy | 41 +++++++++++++++++-
5 files changed, 122 insertions(+), 15 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/UpdateStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/UpdateStmt.java
index 08e755b35ce..5ab3cee4b9e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/UpdateStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/UpdateStmt.java
@@ -35,6 +35,7 @@ import org.apache.doris.qe.SessionVariable;
import com.google.common.collect.Lists;
import java.util.List;
+import java.util.Optional;
import java.util.Set;
import java.util.TreeSet;
@@ -211,12 +212,23 @@ public class UpdateStmt extends DdlStmt {
&& !hasVariant) {
isPartialUpdate = true;
}
+ Optional<Column> sequenceMapCol = Optional.empty();
+ OlapTable olapTable = (OlapTable) targetTable;
+ if (olapTable.hasSequenceCol() && olapTable.getSequenceMapCol() !=
null) {
+ sequenceMapCol = olapTable.getFullSchema().stream()
+ .filter(col ->
col.getName().equalsIgnoreCase(olapTable.getSequenceMapCol())).findFirst();
+ }
for (Column column : targetTable.getColumns()) {
Expr expr = new SlotRef(targetTableRef.getAliasAsName(),
column.getName());
boolean existInExpr = false;
for (BinaryPredicate setExpr : setExprs) {
Expr lhs = setExpr.getChild(0);
- if (((SlotRef) lhs).getColumn().equals(column)) {
+ Column exprColumn = ((SlotRef) lhs).getColumn();
+ // when updating the sequence map column, the real sequence
column need to set with the same value.
+ boolean isSequenceMapColumn = sequenceMapCol.isPresent()
+ && exprColumn.equals(sequenceMapCol.get());
+ if (exprColumn.equals(column) || (olapTable.hasSequenceCol()
+ && column.equals(olapTable.getSequenceCol()) &&
isSequenceMapColumn)) {
expr = setExpr.getChild(1);
existInExpr = true;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/UpdateCommand.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/UpdateCommand.java
index e0512715c55..133fc36a1da 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/UpdateCommand.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/UpdateCommand.java
@@ -110,6 +110,7 @@ public class UpdateCommand extends Command implements
ForwardWithSync, Explainab
}
List<NamedExpression> selectItems = Lists.newArrayList();
String tableName = tableAlias != null ? tableAlias :
targetTable.getName();
+ Expression setExpr = null;
for (Column column : targetTable.getFullSchema()) {
// if it sets sequence column in stream load phase, the sequence
map column is null, we query it.
if (!column.isVisible() && !column.isSequenceColumn()) {
@@ -117,12 +118,21 @@ public class UpdateCommand extends Command implements
ForwardWithSync, Explainab
}
if (colNameToExpression.containsKey(column.getName())) {
Expression expr = colNameToExpression.get(column.getName());
+ // when updating the sequence map column, the real sequence
column need to set with the same value.
+ boolean isSequenceMapColumn = targetTable.hasSequenceCol()
+ && targetTable.getSequenceMapCol() != null
+ &&
column.getName().equalsIgnoreCase(targetTable.getSequenceMapCol());
+ if (setExpr == null && isSequenceMapColumn) {
+ setExpr = expr;
+ }
selectItems.add(expr instanceof UnboundSlot
? ((NamedExpression) expr)
: new UnboundAlias(expr));
colNameToExpression.remove(column.getName());
} else {
- if (column.hasOnUpdateDefaultValue()) {
+ if (column.isSequenceColumn() && setExpr != null) {
+ selectItems.add(new UnboundAlias(setExpr,
column.getName()));
+ } else if (column.hasOnUpdateDefaultValue()) {
Expression defualtValueExpression =
new
NereidsParser().parseExpression(column.getOnUpdateDefaultValueExpr()
.toSqlWithoutTbl());
@@ -141,7 +151,6 @@ public class UpdateCommand extends Command implements
ForwardWithSync, Explainab
if (cte.isPresent()) {
logicalQuery = ((LogicalPlan)
cte.get().withChildren(logicalQuery));
}
-
boolean isPartialUpdate = targetTable.getEnableUniqueKeyMergeOnWrite()
&& selectItems.size() < targetTable.getColumns().size()
&& !targetTable.hasVariantColumns();
diff --git
a/regression-test/data/data_model_p0/unique/test_unique_table_new_sequence.out
b/regression-test/data/data_model_p0/unique/test_unique_table_new_sequence.out
index e8a4784bbaa..2e8ddff65bb 100644
---
a/regression-test/data/data_model_p0/unique/test_unique_table_new_sequence.out
+++
b/regression-test/data/data_model_p0/unique/test_unique_table_new_sequence.out
@@ -23,12 +23,12 @@
-- !part --
1 10 15
2 5 14
-3 6 11
+3 6 13
-- !all --
1 10 15 16 17
2 5 14 13 14
-3 6 11 14 15
+3 6 13 14 15
-- !count --
4
@@ -37,13 +37,13 @@
1 10 15
15 8 19
2 5 14
-3 6 11
+3 6 13
-- !all --
1 10 15 16 17 0 4 15
15 8 19 20 21 0 7 19
-2 5 14 13 14 0 5 12
-3 6 11 14 15 0 6 13
+2 5 14 13 14 0 5 14
+3 6 13 14 15 0 2 13
-- !desc --
k1 INT Yes true \N
@@ -104,12 +104,12 @@ __DORIS_SEQUENCE_COL__ INT Yes false \N
REPLACE
-- !part --
1 10 15
2 5 14
-3 6 11
+3 6 13
-- !all --
1 10 15 16 17
2 5 14 13 14
-3 6 11 14 15
+3 6 13 14 15
-- !count --
4
@@ -118,13 +118,13 @@ __DORIS_SEQUENCE_COL__ INT Yes false \N
REPLACE
1 10 15
15 8 19
2 5 14
-3 6 11
+3 6 13
-- !all --
1 10 15 16 17 0 4 15
15 8 19 20 21 0 7 19
-2 5 14 13 14 0 5 12
-3 6 11 14 15 0 6 13
+2 5 14 13 14 0 5 14
+3 6 13 14 15 0 2 13
-- !desc --
k1 INT Yes true \N
diff --git a/regression-test/data/update/test_update.out
b/regression-test/data/update/test_update.out
new file mode 100644
index 00000000000..3ca76ca7f73
--- /dev/null
+++ b/regression-test/data/update/test_update.out
@@ -0,0 +1,49 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql1 --
+1 2024-01-31 100
+2 2024-01-31 100
+
+-- !sql2 --
+1 2024-01-31 200
+2 2024-01-31 200
+
+-- !sql3 --
+1 2024-01-31 200
+2 2024-01-31 300
+
+-- !sql4 --
+1 2024-01-31 400
+2 2024-01-31 300
+
+-- !sql5 --
+1 2024-01-31 400
+2 2024-01-31 300
+
+-- !sql6 --
+1 2024-01-31 500
+2 2024-01-31 300
+
+-- !sql1 --
+1 2024-01-31 100
+2 2024-01-31 100
+
+-- !sql2 --
+1 2024-01-31 200
+2 2024-01-31 200
+
+-- !sql3 --
+1 2024-01-31 200
+2 2024-01-31 300
+
+-- !sql4 --
+1 2024-01-31 400
+2 2024-01-31 300
+
+-- !sql5 --
+1 2024-01-31 400
+2 2024-01-31 300
+
+-- !sql6 --
+1 2024-01-31 500
+2 2024-01-31 300
+
diff --git a/regression-test/suites/update/test_update.groovy
b/regression-test/suites/update/test_update.groovy
index 5731c5b1de2..bdad5b21e9b 100644
--- a/regression-test/suites/update/test_update.groovy
+++ b/regression-test/suites/update/test_update.groovy
@@ -15,6 +15,43 @@
// specific language governing permissions and limitations
// under the License.
suite("test_update", "p0") {
- // todo:test update
- sql "show tables"
+ def tbName = "test_update"
+ for (def use_nereids_planner : [false, true]) {
+ sql " SET enable_nereids_planner = $use_nereids_planner; "
+ sql " SET enable_fallback_to_original_planner = false; "
+ sql "DROP TABLE IF EXISTS ${tbName}"
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tbName} (
+ user_id bigint,
+ date1 date,
+ group_id bigint
+ )
+ UNIQUE KEY(user_id)
+ DISTRIBUTED BY HASH(user_id) BUCKETS 5 properties(
+ "function_column.sequence_col"='group_id',
+ "replication_num" = "1",
+ "in_memory"="false"
+ );
+ """
+ sql "insert into ${tbName} values(1,'20240131',100);"
+ sql "insert into ${tbName} values(2,'20240131',100);"
+ qt_sql1 "select * from ${tbName} order by user_id;"
+ // set group_id to 200 on all record
+ sql "UPDATE ${tbName} SET group_id=200;"
+ // this insert will not work
+ sql "insert into ${tbName} values(2,'20240131',100);"
+ qt_sql2 "select * from ${tbName} order by user_id;"
+ // this insert will work
+ sql "insert into ${tbName} values(2,'20240131',300);"
+ qt_sql3 "select * from ${tbName} order by user_id;"
+ // set group_id to 400 on specific record
+ sql "UPDATE ${tbName} SET group_id=400 WHERE user_id=1;"
+ qt_sql4 "select * from ${tbName} order by user_id;"
+ // this insert will not work
+ sql "insert into ${tbName} values(1,'20240131',300);"
+ qt_sql5 "select * from ${tbName} order by user_id;"
+ // this insert will work
+ sql "insert into ${tbName} values(1,'20240131',500);"
+ qt_sql6 "select * from ${tbName} order by user_id;"
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
