This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit ecc635d1cddec3a3633d783863d4e7d25b4a90db Author: starocean999 <[email protected]> AuthorDate: Wed Sep 13 17:22:39 2023 +0800 [fix](planner)allow infer predicate for external table (#24227) CREATE EXTERNAL TABLE `dim_server` ( `col1` varchar(50) NOT NULL, `col2` varchar(50) NOT NULL ) create view ads_oreo_sid_report ( `col1` , `col2` ) AS select tmp.col1,tmp.col2 from ( select 'abc' as col1,'def' as col2 ) tmp inner join dim_server ds on tmp.col1 = ds.col1 and tmp.col2 = ds.col2; select * from ads_oreo_sid_report where col1='abc' and col2='def'; before this pr, col1='abc' and col2='def' can't be pushed to dim_server. now the 2 predicates can be pushed to odbc table. --- .../org/apache/doris/planner/MysqlScanNode.java | 4 ++ .../apache/doris/planner/SingleNodePlanner.java | 3 +- .../doris/planner/external/jdbc/JdbcScanNode.java | 4 ++ .../doris/planner/external/odbc/OdbcScanNode.java | 4 ++ .../jdbc/test_mysql_jdbc_catalog_nereids.out | 23 -------- .../test_push_conjunct_external_table.groovy | 67 ++++++++++++++++++++++ .../jdbc/test_mysql_jdbc_catalog_nereids.groovy | 5 +- 7 files changed, 85 insertions(+), 25 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/MysqlScanNode.java b/fe/fe-core/src/main/java/org/apache/doris/planner/MysqlScanNode.java index 506e95c4b8..2f3e49b8d9 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/planner/MysqlScanNode.java +++ b/fe/fe-core/src/main/java/org/apache/doris/planner/MysqlScanNode.java @@ -88,6 +88,10 @@ public class MysqlScanNode extends ExternalScanNode { return output.toString(); } output.append(prefix).append("Query: ").append(getMysqlQueryStr()).append("\n"); + if (!conjuncts.isEmpty()) { + Expr expr = convertConjunctsToAndCompoundPredicate(conjuncts); + output.append(prefix).append("PREDICATES: ").append(expr.toSql()).append("\n"); + } return output.toString(); } diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java b/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java index 1707549cf5..b0e0f93dc3 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java +++ b/fe/fe-core/src/main/java/org/apache/doris/planner/SingleNodePlanner.java @@ -2047,7 +2047,8 @@ public class SingleNodePlanner { throw new UserException("Not supported table type" + tblRef.getTable().getType()); } if (scanNode instanceof OlapScanNode || scanNode instanceof EsScanNode - || scanNode instanceof FileQueryScanNode) { + || scanNode instanceof OdbcScanNode || scanNode instanceof JdbcScanNode + || scanNode instanceof FileQueryScanNode || scanNode instanceof MysqlScanNode) { if (analyzer.enableInferPredicate()) { PredicatePushDown.visitScanNode(scanNode, tblRef.getJoinOp(), analyzer); } diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java index 3247d1136b..94b6aeb160 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java +++ b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java @@ -220,6 +220,10 @@ public class JdbcScanNode extends ExternalScanNode { return output.toString(); } output.append(prefix).append("QUERY: ").append(getJdbcQueryStr()).append("\n"); + if (!conjuncts.isEmpty()) { + Expr expr = convertConjunctsToAndCompoundPredicate(conjuncts); + output.append(prefix).append("PREDICATES: ").append(expr.toSql()).append("\n"); + } return output.toString(); } diff --git a/fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcScanNode.java b/fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcScanNode.java index 832922ef81..0f27e9da58 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcScanNode.java +++ b/fe/fe-core/src/main/java/org/apache/doris/planner/external/odbc/OdbcScanNode.java @@ -104,6 +104,10 @@ public class OdbcScanNode extends ExternalScanNode { return output.toString(); } output.append(prefix).append("QUERY: ").append(getOdbcQueryStr()).append("\n"); + if (!conjuncts.isEmpty()) { + Expr expr = convertConjunctsToAndCompoundPredicate(conjuncts); + output.append(prefix).append("PREDICATES: ").append(expr.toSql()).append("\n"); + } return output.toString(); } diff --git a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.out b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.out index ce244d7788..51f6d728ea 100644 --- a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.out +++ b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.out @@ -1,27 +1,4 @@ -- This file is automatically generated. You should know what you did if you want to edit this --- !ex_tb0_explain -- -PLAN FRAGMENT 0 - OUTPUT EXPRS: - id[#0] - PARTITION: UNPARTITIONED - - VRESULT SINK - - 1:VEXCHANGE - offset: 0 - -PLAN FRAGMENT 1 - - PARTITION: RANDOM - - STREAM DATA SINK - EXCHANGE ID: 01 - UNPARTITIONED - - 0:VJdbcScanNode - TABLE: `doris_test`.`ex_tb0` - QUERY: SELECT `id` FROM `doris_test`.`ex_tb0` WHERE (id = 111) - -- !ex_tb0_where -- 111 diff --git a/regression-test/suites/correctness_p0/test_push_conjunct_external_table.groovy b/regression-test/suites/correctness_p0/test_push_conjunct_external_table.groovy new file mode 100644 index 0000000000..ad59c0f61b --- /dev/null +++ b/regression-test/suites/correctness_p0/test_push_conjunct_external_table.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_push_conjunct_external_table") { + sql """set enable_nereids_planner=false;""" + sql """ DROP TABLE IF EXISTS dim_server; """ + + sql """ + CREATE EXTERNAL TABLE `dim_server` ( + `col1` varchar(50) NOT NULL, + `col2` varchar(50) NOT NULL + ) ENGINE=mysql + PROPERTIES + ( + "host" = "127.0.0.1", + "port" = "8239", + "user" = "mysql_user", + "password" = "mysql_passwd", + "database" = "mysql_db_test", + "table" = "mysql_table_test", + "charset" = "utf8mb4" + ); + """ + + sql """ + DROP view if exists ads_oreo_sid_report; + """ + + sql """ + create view ads_oreo_sid_report + ( + `col1` , + `col2` + ) + AS + select + tmp.col1,tmp.col2 + from ( + select 'abc' as col1,'def' as col2 + ) tmp + inner join dim_server ds on tmp.col1 = ds.col1 and tmp.col2 = ds.col2; + """ + + explain { + sql """select * from ads_oreo_sid_report where col1='abc' and col2='def';""" + contains "`ds`.`col1` = 'abc'" + contains "`ds`.`col2` = 'def'" + } + + sql """ DROP TABLE IF EXISTS dim_server; """ + sql """ DROP view if exists ads_oreo_sid_report; """ +} + diff --git a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.groovy b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.groovy index c7f48482f1..60c42a369c 100644 --- a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.groovy +++ b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.groovy @@ -74,7 +74,10 @@ suite("test_mysql_jdbc_catalog_nereids", "p0") { sql """switch ${catalog_name}""" sql """ use ${ex_db_name}""" - qt_ex_tb0_explain """explain select id from ${ex_tb0} where id = 111;""" + explain { + sql("""select id from ${ex_tb0} where id = 111;""") + contains "WHERE (id = 111)" + } qt_ex_tb0_where """select id from ${ex_tb0} where id = 111;""" order_qt_ex_tb0 """ select id, name from ${ex_tb0} order by id; """ sql """ insert into internal.${internal_db_name}.${inDorisTable} select id, name from ${ex_tb0}; """ --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
