This is an automated email from the ASF dual-hosted git repository.
lihaopeng 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 c3712b1114 [bug](jdbc) fix error of jdbc with datetime type in oracle
(#15205)
c3712b1114 is described below
commit c3712b1114faa5fd0acaea42bcccef78ce6d1eb3
Author: zhangstar333 <87313068+zhangstar...@users.noreply.github.com>
AuthorDate: Tue Dec 20 22:05:55 2022 +0800
[bug](jdbc) fix error of jdbc with datetime type in oracle (#15205)
---
docs/en/docs/admin-manual/config/fe-config.md | 10 ++++++++
.../docs/ecosystem/external-table/jdbc-of-doris.md | 2 +-
docs/zh-CN/docs/admin-manual/config/fe-config.md | 10 ++++++++
.../docs/ecosystem/external-table/jdbc-of-doris.md | 2 +-
.../main/java/org/apache/doris/common/Config.java | 7 ++++++
.../org/apache/doris/planner/JdbcScanNode.java | 2 +-
.../org/apache/doris/planner/OdbcScanNode.java | 29 ++++++++++++++++------
7 files changed, 51 insertions(+), 11 deletions(-)
diff --git a/docs/en/docs/admin-manual/config/fe-config.md
b/docs/en/docs/admin-manual/config/fe-config.md
index d72fb16492..10130987d5 100644
--- a/docs/en/docs/admin-manual/config/fe-config.md
+++ b/docs/en/docs/admin-manual/config/fe-config.md
@@ -2572,3 +2572,13 @@ Default：x@8
#### `proxy_auth_enable`
Default：false
+
+#### `enable_func_pushdown`
+
+Default：true
+
+IsMutable：true
+
+MasterOnly：false
+
+Whether to push the filter conditions with functions down to MYSQL, when
exectue query of ODBC、JDBC external tables
diff --git a/docs/en/docs/ecosystem/external-table/jdbc-of-doris.md
b/docs/en/docs/ecosystem/external-table/jdbc-of-doris.md
index faca1eed5f..0f02e66168 100644
--- a/docs/en/docs/ecosystem/external-table/jdbc-of-doris.md
+++ b/docs/en/docs/ecosystem/external-table/jdbc-of-doris.md
@@ -204,7 +204,7 @@ There are different data types among different databases.
Here is a list of the
| DATE | DATETIME |
| SMALLINT | SMALLINT |
| INT | INT |
-| REAL | FLOAT |
+| REAL | DOUBLE |
| FLOAT | DOUBLE |
| NUMBER | DECIMAL |
diff --git a/docs/zh-CN/docs/admin-manual/config/fe-config.md
b/docs/zh-CN/docs/admin-manual/config/fe-config.md
index 85d4ed8d31..b65bc816bd 100644
--- a/docs/zh-CN/docs/admin-manual/config/fe-config.md
+++ b/docs/zh-CN/docs/admin-manual/config/fe-config.md
@@ -2572,3 +2572,13 @@ SmallFileMgr 中存储的最大文件数
#### `proxy_auth_enable`
默认值：false
+
+#### `enable_func_pushdown`
+
+默认值：true
+
+是否可以动态配置：true
+
+是否为 Master FE 节点独有的配置项：false
+
+在ODBC、JDBC的MYSQL外部表查询时，是否将带函数的过滤条件下推到MYSQL中执行
diff --git a/docs/zh-CN/docs/ecosystem/external-table/jdbc-of-doris.md
b/docs/zh-CN/docs/ecosystem/external-table/jdbc-of-doris.md
index 459f55f9f4..6104f863ae 100644
--- a/docs/zh-CN/docs/ecosystem/external-table/jdbc-of-doris.md
+++ b/docs/zh-CN/docs/ecosystem/external-table/jdbc-of-doris.md
@@ -206,7 +206,7 @@ PROPERTIES (
| DATE | DATETIME |
| SMALLINT | SMALLINT |
| INT | INT |
-| REAL | FLOAT |
+| REAL | DOUBLE |
| FLOAT | DOUBLE |
| NUMBER | DECIMAL |
diff --git a/fe/fe-core/src/main/java/org/apache/doris/common/Config.java
b/fe/fe-core/src/main/java/org/apache/doris/common/Config.java
index 971230046f..d6e043509d 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/common/Config.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/common/Config.java
@@ -1920,5 +1920,12 @@ public class Config extends ConfigBase {
*/
@ConfField(mutable = false, masterOnly = true)
public static boolean enable_fqdn_mode = false;
+
+ /**
+ * This is used whether to push down function to MYSQL in external Table
with query sql
+ * like odbc, jdbc for mysql table
+ */
+ @ConfField(mutable = true)
+ public static boolean enable_func_pushdown = true;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java
index b06956fda3..b9da4b45c0 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/JdbcScanNode.java
@@ -102,7 +102,7 @@ public class JdbcScanNode extends ScanNode {
ArrayList<Expr> conjunctsList = Expr.cloneList(conjuncts, sMap);
for (Expr p : conjunctsList) {
if (OdbcScanNode.shouldPushDownConjunct(jdbcType, p)) {
- String filter = p.toMySql();
+ String filter = OdbcScanNode.conjunctExprToString(jdbcType, p);
filters.add(filter);
conjuncts.remove(p);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/OdbcScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/OdbcScanNode.java
index bfec4e871f..f47be29d38 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/OdbcScanNode.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/OdbcScanNode.java
@@ -18,6 +18,7 @@
package org.apache.doris.planner;
import org.apache.doris.analysis.Analyzer;
+import org.apache.doris.analysis.BinaryPredicate;
import org.apache.doris.analysis.DateLiteral;
import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.ExprSubstitutionMap;
@@ -27,6 +28,8 @@ import org.apache.doris.analysis.SlotRef;
import org.apache.doris.analysis.TupleDescriptor;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.OdbcTable;
+import org.apache.doris.catalog.Type;
+import org.apache.doris.common.Config;
import org.apache.doris.common.UserException;
import org.apache.doris.statistics.StatisticalType;
import org.apache.doris.statistics.StatsRecursiveDerive;
@@ -54,7 +57,6 @@ public class OdbcScanNode extends ScanNode {
// Now some database have different function call like doris, now doris do
not
// push down the function call except MYSQL
- // TODO: maybe add a config to decide whether to pushdown the function of
MYSQL
public static boolean shouldPushDownConjunct(TOdbcTableType tableType,
Expr expr) {
if (!tableType.equals(TOdbcTableType.MYSQL)) {
List<FunctionCallExpr> fnExprList = Lists.newArrayList();
@@ -62,14 +64,25 @@ public class OdbcScanNode extends ScanNode {
if (!fnExprList.isEmpty()) {
return false;
}
- //oracle date type is not push down
https://github.com/apache/doris/discussions/15069
- //TODO: Now not sure how to rewrite the needed expr and doesn't
affect other normal
- //if we could rewrite the expr according to the format, then can
pushdown this.
- if (tableType.equals(TOdbcTableType.ORACLE) &&
expr.contains(DateLiteral.class)) {
- return false;
+ }
+ return Config.enable_func_pushdown;
+ }
+
+ public static String conjunctExprToString(TOdbcTableType tableType, Expr
expr) {
+ if (tableType.equals(TOdbcTableType.ORACLE) &&
expr.contains(DateLiteral.class)
+ && (expr instanceof BinaryPredicate)) {
+ ArrayList<Expr> children = expr.getChildren();
+ // k1 OP '2022-12-10 20:55:59' changTo ---> k1 OP
to_date('{}','yyyy-mm-dd hh24:mi:ss')
+ // oracle datetime push down is different:
https://github.com/apache/doris/discussions/15069
+ if (children.get(1).isConstant() &&
(children.get(1).getType().equals(Type.DATETIME) || children
+ .get(1).getType().equals(Type.DATETIMEV2))) {
+ String filter = children.get(0).toSql();
+ filter += ((BinaryPredicate) expr).getOp().toString();
+ filter += "to_date('" + children.get(1).getStringValue() +
"','yyyy-mm-dd hh24:mi:ss')";
+ return filter;
}
}
- return true;
+ return expr.toMySql();
}
private final List<String> columns = new ArrayList<String>();
@@ -190,7 +203,7 @@ public class OdbcScanNode extends ScanNode {
ArrayList<Expr> odbcConjuncts = Expr.cloneList(conjuncts, sMap);
for (Expr p : odbcConjuncts) {
if (shouldPushDownConjunct(odbcType, p)) {
- String filter = p.toMySql();
+ String filter = conjunctExprToString(odbcType, p);
filters.add(filter);
conjuncts.remove(p);
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
