This is an automated email from the ASF dual-hosted git repository.
morningman 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 8749e5208f7 [fix](jdbc catalog) fix insert into jdbc table column
order (#27855)
8749e5208f7 is described below
commit 8749e5208f748589c5b71e4295205767e1bae91d
Author: zy-kkk <[email protected]>
AuthorDate: Fri Dec 1 20:46:48 2023 +0800
[fix](jdbc catalog) fix insert into jdbc table column order (#27855)
---
.../apache/doris/analysis/NativeInsertStmt.java | 16 +++++++++-----
.../doris/planner/external/jdbc/JdbcTableSink.java | 1 +
.../jdbc/test_doris_jdbc_catalog.out | 4 ++++
.../jdbc/test_doris_jdbc_catalog.groovy | 25 ++++++++++++++++++++++
4 files changed, 41 insertions(+), 5 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 2f268de58cd..b69ed837b83 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
@@ -998,12 +998,18 @@ public class NativeInsertStmt extends InsertStmt {
brokerDesc);
dataPartition = dataSink.getOutputPartition();
} else if (targetTable instanceof JdbcTable) {
- //for JdbcTable,we need to pass the currently written column to
`JdbcTableSink`
- //to generate the prepare insert statment
- List<String> insertCols = Lists.newArrayList();
- for (Column column : targetColumns) {
- insertCols.add(column.getName());
+ // For JdbcTable, reorder targetColumns to match the order in
targetTable.getFullSchema()
+ List<String> insertCols = new ArrayList<>();
+ Set<String> targetColumnNames = targetColumns.stream()
+ .map(Column::getName)
+ .collect(Collectors.toSet());
+
+ for (Column column : targetTable.getFullSchema()) {
+ if (targetColumnNames.contains(column.getName())) {
+ insertCols.add(column.getName());
+ }
}
+
dataSink = new JdbcTableSink((JdbcTable) targetTable, insertCols);
dataPartition = DataPartition.UNPARTITIONED;
} else {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcTableSink.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcTableSink.java
index 6a767ff1856..1f85ac3feff 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcTableSink.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcTableSink.java
@@ -73,6 +73,7 @@ public class JdbcTableSink extends DataSink {
strBuilder.append(prefix + "TABLE TYPE:
").append(jdbcType.toString()).append("\n");
strBuilder.append(prefix + "TABLENAME OF EXTERNAL TABLE:
").append(externalTableName).append("\n");
strBuilder.append(prefix + "EnableTransaction:
").append(useTransaction ? "true" : "false").append("\n");
+ strBuilder.append(prefix + "PreparedStatement SQL:
").append(insertSql).append("\n");
return strBuilder.toString();
}
diff --git
a/regression-test/data/external_table_p0/jdbc/test_doris_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_doris_jdbc_catalog.out
index 2e686056d40..b4ac823517a 100644
--- a/regression-test/data/external_table_p0/jdbc/test_doris_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_doris_jdbc_catalog.out
@@ -114,6 +114,10 @@ true 1 1 1 1 1 1.0
1.0 1.00000 1.0000000000 2021-01-01 2021-01-01T00:00 a
a {"a"
1 [1] [1] [1] [1] [1] [1] [1] [1]
[1.00000] [1.0000000000] ["2021-01-01"] ["2021-01-01 00:00:00.000"]
["a"] ["a"] ["a"]
2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N
+-- !sql --
+g1 1 2 3 4 p1
+g2 1 2 3 4 p2
+
-- !sql --
doris_jdbc_catalog
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_doris_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_doris_jdbc_catalog.groovy
index c92f285972d..4464c186a6c 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_doris_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_doris_jdbc_catalog.groovy
@@ -158,6 +158,25 @@ suite("test_doris_jdbc_catalog",
"p0,external,doris,external_docker,external_doc
sql """insert into ${arr_table} values (2, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null);"""
order_qt_arr1 """ select * from ${arr_table} order by int_col; """
+ sql """drop table if exists test_insert_order"""
+
+ sql """
+ CREATE TABLE test_insert_order (
+ gameid varchar(50) NOT NULL DEFAULT "",
+ aid int(11) NOT NULL DEFAULT "0",
+ bid int(11) NOT NULL DEFAULT "0",
+ cid int(11) NOT NULL DEFAULT "0",
+ did int(11) NOT NULL DEFAULT "0",
+ pname varchar(255) NOT NULL DEFAULT "其他"
+ ) ENGINE=OLAP
+ UNIQUE KEY(gameid, aid, bid, cid)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(gameid) BUCKETS 3
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
sql """ set return_object_data_as_binary=true """
order_qt_tb1 """ select pin_id, hll_union_agg(user_log_acct) from
${hllTable} group by pin_id; """
@@ -180,6 +199,11 @@ suite("test_doris_jdbc_catalog",
"p0,external,doris,external_docker,external_doc
order_qt_query_ctas_base """ select * from
internal.${internal_db_name}.ctas_base order by int_col; """
order_qt_query_ctas_arr """ select * from
internal.${internal_db_name}.ctas_arr order by int_col; """
+ // test insert order
+ sql """insert into test_insert_order(gameid,did,cid,bid,aid,pname)
values('g1',4,3,2,1,'p1')""";
+ sql """insert into test_insert_order(gameid,did,cid,bid,aid,pname) select
'g2',4,3,2,1,'p2'""";
+ qt_sql """select * from test_insert_order order by gameid, aid, bid, cid,
did;"""
+
//clean
qt_sql """select current_catalog()"""
sql "switch internal"
@@ -189,5 +213,6 @@ suite("test_doris_jdbc_catalog",
"p0,external,doris,external_docker,external_doc
sql """ drop table if exists ${hllTable} """
sql """ drop table if exists ${base_table} """
sql """ drop table if exists ${arr_table} """
+ sql """ drop table if exists test_insert_order """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
