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 ffee5d607de [fix](partial-update) insert only without auto_inc column
should not use partial update (#38229)
ffee5d607de is described below
commit ffee5d607def041c66feeede7bededb1624485ab
Author: camby <[email protected]>
AuthorDate: Mon Jul 29 15:01:12 2024 +0800
[fix](partial-update) insert only without auto_inc column should not use
partial update (#38229)
For primary key tables and unique_key_partial_update enabled:
1. for `insert into table values(xxx)`, we should try full column insert
instead of partial update;
2. If `insert into table(cols)` include all columns except the
AUTO_INCREMENT column, we should use full column insert;
---
.../apache/doris/analysis/NativeInsertStmt.java | 13 ++++-
.../trees/plans/commands/insert/InsertUtils.java | 35 ++++++-----
.../test_partial_update_auto_inc.out | 65 +++++++++++++++++++++
.../insert_into_table/partial_update.groovy | 2 +-
.../partial_update_complex.groovy | 2 +-
.../test_partial_update_auto_inc.groovy | 67 ++++++++++++++++++++++
.../test_partial_update_native_insert_stmt.groovy | 2 +-
...artial_update_native_insert_stmt_complex.groovy | 2 +-
8 files changed, 168 insertions(+), 20 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java
index 75b125c05fc..4d9a3ab6980 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/NativeInsertStmt.java
@@ -1315,6 +1315,7 @@ public class NativeInsertStmt extends InsertStmt {
if (hasEmptyTargetColumns) {
return;
}
+ boolean hasMissingColExceptAutoInc = false;
for (Column col : olapTable.getFullSchema()) {
boolean exists = false;
for (Column insertCol : targetColumns) {
@@ -1327,10 +1328,18 @@ public class NativeInsertStmt extends InsertStmt {
break;
}
}
- if (col.isKey() && !exists) {
- throw new UserException("Partial update should include all key
columns, missing: " + col.getName());
+ if (!exists && !col.isAutoInc()) {
+ if (col.isKey()) {
+ throw new UserException("Partial update should include all
key columns, missing: " + col.getName());
+ }
+ if (col.isVisible()) {
+ hasMissingColExceptAutoInc = true;
+ }
}
}
+ if (!hasMissingColExceptAutoInc) {
+ return;
+ }
isPartialUpdate = true;
for (String name : targetColumnNames) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/insert/InsertUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/insert/InsertUtils.java
index e99e81b9778..67374254c8a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/insert/InsertUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/insert/InsertUtils.java
@@ -280,21 +280,28 @@ public class InsertUtils {
} else {
if (unboundLogicalSink.getDMLCommandType() ==
DMLCommandType.INSERT) {
if (unboundLogicalSink.getColNames().isEmpty()) {
- throw new AnalysisException("You must explicitly
specify the columns to be updated when "
- + "updating partial columns using the
INSERT statement.");
- }
- for (Column col : olapTable.getFullSchema()) {
- Optional<String> insertCol =
unboundLogicalSink.getColNames().stream()
- .filter(c ->
c.equalsIgnoreCase(col.getName())).findFirst();
- if (col.isKey() && !insertCol.isPresent()) {
- throw new AnalysisException("Partial update
should include all key columns, missing: "
- + col.getName());
+ ((UnboundTableSink<? extends Plan>)
unboundLogicalSink).setPartialUpdate(false);
+ } else {
+ boolean hasMissingColExceptAutoInc = false;
+ for (Column col : olapTable.getFullSchema()) {
+ Optional<String> insertCol =
unboundLogicalSink.getColNames().stream()
+ .filter(c ->
c.equalsIgnoreCase(col.getName())).findFirst();
+ if (col.isKey() && !col.isAutoInc() &&
!insertCol.isPresent()) {
+ throw new AnalysisException("Partial
update should include all key columns,"
+ + " missing: " + col.getName());
+ }
+ if
(!col.getGeneratedColumnsThatReferToThis().isEmpty()
+ && col.getGeneratedColumnInfo() ==
null && !insertCol.isPresent()) {
+ throw new AnalysisException("Partial
update should include"
+ + " all ordinary columns
referenced"
+ + " by generated columns, missing:
" + col.getName());
+ }
+ if (!col.isAutoInc() && !insertCol.isPresent()
&& col.isVisible()) {
+ hasMissingColExceptAutoInc = true;
+ }
}
- if
(!col.getGeneratedColumnsThatReferToThis().isEmpty()
- && col.getGeneratedColumnInfo() == null &&
!insertCol.isPresent()) {
- throw new AnalysisException("Partial update
should include"
- + " all ordinary columns referenced"
- + " by generated columns, missing: " +
col.getName());
+ if (!hasMissingColExceptAutoInc) {
+ ((UnboundTableSink<? extends Plan>)
unboundLogicalSink).setPartialUpdate(false);
}
}
}
diff --git
a/regression-test/data/unique_with_mow_p0/partial_update/test_partial_update_auto_inc.out
b/regression-test/data/unique_with_mow_p0/partial_update/test_partial_update_auto_inc.out
new file mode 100644
index 00000000000..380575499e2
--- /dev/null
+++
b/regression-test/data/unique_with_mow_p0/partial_update/test_partial_update_auto_inc.out
@@ -0,0 +1,65 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select_1 --
+doris1
+doris2
+
+-- !select_2 --
+2
+
+-- !select_3 --
+doris1
+doris2
+doris3
+doris4
+
+-- !select_4 --
+4
+
+-- !select_1 --
+doris1
+doris2
+
+-- !select_2 --
+2
+
+-- !select_3 --
+doris1
+doris2
+doris3
+doris4
+
+-- !select_4 --
+4
+
+-- !select_1 --
+doris1
+doris2
+
+-- !select_2 --
+2
+
+-- !select_3 --
+doris1
+doris2
+doris3
+doris4
+
+-- !select_4 --
+4
+
+-- !select_1 --
+doris1
+doris2
+
+-- !select_2 --
+2
+
+-- !select_3 --
+doris1
+doris2
+doris3
+doris4
+
+-- !select_4 --
+4
+
diff --git
a/regression-test/suites/nereids_p0/insert_into_table/partial_update.groovy
b/regression-test/suites/nereids_p0/insert_into_table/partial_update.groovy
index c734bcf1846..fd2145a71ed 100644
--- a/regression-test/suites/nereids_p0/insert_into_table/partial_update.groovy
+++ b/regression-test/suites/nereids_p0/insert_into_table/partial_update.groovy
@@ -56,7 +56,7 @@ suite("nereids_partial_update_native_insert_stmt", "p0") {
qt_1 """ select * from ${tableName} order by id; """
test {
sql """insert into ${tableName}
values(2,400),(1,200),(4,400)"""
- exception "You must explicitly specify the columns to be
updated when updating partial columns using the INSERT statement."
+ exception "Column count doesn't match value count"
}
sql "set enable_unique_key_partial_update=false;"
sql "sync;"
diff --git
a/regression-test/suites/nereids_p0/insert_into_table/partial_update_complex.groovy
b/regression-test/suites/nereids_p0/insert_into_table/partial_update_complex.groovy
index 537b812d01c..a8433241e2e 100644
---
a/regression-test/suites/nereids_p0/insert_into_table/partial_update_complex.groovy
+++
b/regression-test/suites/nereids_p0/insert_into_table/partial_update_complex.groovy
@@ -91,7 +91,7 @@ suite("nereids_partial_update_native_insert_stmt_complex",
"p0") {
sql """insert into ${tbName1}
select ${tbName2}.id, ${tbName2}.c1, ${tbName2}.c3 * 100
from ${tbName2} inner join ${tbName3} on ${tbName2}.id =
${tbName3}.id; """
- exception "You must explicitly specify the columns to be
updated when updating partial columns using the INSERT statement"
+ exception "insert into cols should be corresponding to the
query output"
}
sql "truncate table ${tbName1};"
sql "truncate table ${tbName2};"
diff --git
a/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_auto_inc.groovy
b/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_auto_inc.groovy
new file mode 100644
index 00000000000..d0d1ecf9542
--- /dev/null
+++
b/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_auto_inc.groovy
@@ -0,0 +1,67 @@
+
+// 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_partial_update_auto_inc") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "select 1;" // to create database
+
+ for (def use_mow : [false, true]) {
+ for (def use_nereids_planner : [false, true]) {
+ logger.info("current params: use_mow: ${use_mow},
use_nereids_planner: ${use_nereids_planner}")
+ connect(user = context.config.jdbcUser, password =
context.config.jdbcPassword, url = context.config.jdbcUrl) {
+ sql "use ${db};"
+
+ if (use_nereids_planner) {
+ sql """ set enable_nereids_dml = true; """
+ sql """ set enable_nereids_planner=true; """
+ sql """ set enable_fallback_to_original_planner=false; """
+ } else {
+ sql """ set enable_nereids_dml = false; """
+ sql """ set enable_nereids_planner = false; """
+ }
+
+ // create table
+ sql """ DROP TABLE IF EXISTS
test_primary_key_partial_update_auto_inc """
+ sql """ CREATE TABLE test_primary_key_partial_update_auto_inc (
+ `id` BIGINT NOT NULL AUTO_INCREMENT,
+ `name` varchar(65533) NOT NULL COMMENT "用户姓名" )
+ UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS
1
+ PROPERTIES("replication_num" = "1",
"enable_unique_key_merge_on_write" = "${use_mow}"); """
+
+ sql """ set enable_unique_key_partial_update=true; """
+ sql """ insert into
test_primary_key_partial_update_auto_inc(name) values("doris1"); """
+ sql """ set enable_unique_key_partial_update=false; """
+ sql """ insert into
test_primary_key_partial_update_auto_inc(name) values("doris2"); """
+ sql "sync"
+
+ qt_select_1 """ select name from
test_primary_key_partial_update_auto_inc order by name; """
+ qt_select_2 """ select count(distinct id) from
test_primary_key_partial_update_auto_inc; """
+
+ sql """ set enable_unique_key_partial_update=true; """
+ sql """ insert into test_primary_key_partial_update_auto_inc
values(100,"doris3"); """
+ sql """ set enable_unique_key_partial_update=false; """
+ sql """ insert into test_primary_key_partial_update_auto_inc
values(101, "doris4"); """
+ sql "sync"
+ qt_select_3 """ select name from
test_primary_key_partial_update_auto_inc order by name; """
+ qt_select_4 """ select count(distinct id) from
test_primary_key_partial_update_auto_inc; """
+
+ sql """ DROP TABLE IF EXISTS
test_primary_key_partial_update_auto_inc """
+ }
+ }
+ }
+}
diff --git
a/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_native_insert_stmt.groovy
b/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_native_insert_stmt.groovy
index 18b7ffe6fc2..f5b7a937bcd 100644
---
a/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_native_insert_stmt.groovy
+++
b/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_native_insert_stmt.groovy
@@ -52,7 +52,7 @@ suite("test_partial_update_native_insert_stmt", "p0") {
qt_1 """ select * from ${tableName} order by id; """
test {
sql """insert into ${tableName}
values(2,400),(1,200),(4,400)"""
- exception "You must explicitly specify the columns to be
updated when updating partial columns using the INSERT statement"
+ exception "Column count doesn't match value count"
}
sql "set enable_unique_key_partial_update=false;"
sql "sync;"
diff --git
a/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_native_insert_stmt_complex.groovy
b/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_native_insert_stmt_complex.groovy
index 99158b66cd6..f014beb309e 100644
---
a/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_native_insert_stmt_complex.groovy
+++
b/regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_native_insert_stmt_complex.groovy
@@ -89,7 +89,7 @@ suite("test_partial_update_native_insert_stmt_complex", "p0")
{
sql """insert into ${tbName1}
select ${tbName2}.id, ${tbName2}.c1, ${tbName2}.c3 * 100
from ${tbName2} inner join ${tbName3} on ${tbName2}.id =
${tbName3}.id; """
- exception "You must explicitly specify the columns to be
updated when updating partial columns using the INSERT statement"
+ exception "insert into cols should be corresponding to the
query output"
}
sql "truncate table ${tbName1};"
sql "truncate table ${tbName2};"
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
