This is an automated email from the ASF dual-hosted git repository.
zykkk 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 7943c7bdd8a [fix](catalog) fix data_sub/data_add func pushdown in
jdbcscan (#30807)
7943c7bdd8a is described below
commit 7943c7bdd8ae1cba7259950ca1664321ad5c678c
Author: zy-kkk <[email protected]>
AuthorDate: Mon Feb 5 22:51:26 2024 +0800
[fix](catalog) fix data_sub/data_add func pushdown in jdbcscan (#30807)
---
.../external/jdbc/JdbcFunctionPushDownRule.java | 64 +++++++++++++---
.../jdbc/test_mysql_jdbc_catalog.out | 42 ++++++++++
.../jdbc/test_mysql_jdbc_catalog.groovy | 89 ++++++++++++++++++++++
3 files changed, 184 insertions(+), 11 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java
index d328952593f..74e04256f43 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcFunctionPushDownRule.java
@@ -20,6 +20,7 @@ package org.apache.doris.planner.external.jdbc;
import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.FunctionCallExpr;
import org.apache.doris.analysis.FunctionName;
+import org.apache.doris.analysis.TimestampArithmeticExpr;
import org.apache.doris.catalog.TableIf.TableType;
import org.apache.doris.thrift.TOdbcTableType;
@@ -75,10 +76,6 @@ public class JdbcFunctionPushDownRule {
REPLACE_MYSQL_FUNCTIONS.put("to_date", "date");
}
- private static boolean isReplaceMysqlFunctions(String functionName) {
- return REPLACE_MYSQL_FUNCTIONS.containsKey(functionName.toLowerCase());
- }
-
private static final Map<String, String> REPLACE_CLICKHOUSE_FUNCTIONS =
Maps.newHashMap();
static {
@@ -86,16 +83,20 @@ public class JdbcFunctionPushDownRule {
REPLACE_CLICKHOUSE_FUNCTIONS.put("unix_timestamp", "toUnixTimestamp");
}
- private static boolean isReplaceClickHouseFunctions(String functionName) {
- return
REPLACE_CLICKHOUSE_FUNCTIONS.containsKey(functionName.toLowerCase());
- }
-
private static final Map<String, String> REPLACE_ORACLE_FUNCTIONS =
Maps.newHashMap();
static {
REPLACE_ORACLE_FUNCTIONS.put("ifnull", "nvl");
}
+ private static boolean isReplaceMysqlFunctions(String functionName) {
+ return REPLACE_MYSQL_FUNCTIONS.containsKey(functionName.toLowerCase());
+ }
+
+ private static boolean isReplaceClickHouseFunctions(String functionName) {
+ return
REPLACE_CLICKHOUSE_FUNCTIONS.containsKey(functionName.toLowerCase());
+ }
+
private static boolean isReplaceOracleFunctions(String functionName) {
return
REPLACE_ORACLE_FUNCTIONS.containsKey(functionName.toLowerCase());
}
@@ -141,6 +142,8 @@ public class JdbcFunctionPushDownRule {
}
replaceFunctionNameIfNecessary(func, replaceFunction,
functionCallExpr, tableType);
+
+ expr = replaceGenericFunctionExpr(functionCallExpr, func);
}
List<Expr> children = expr.getChildren();
@@ -153,7 +156,7 @@ public class JdbcFunctionPushDownRule {
return expr;
}
- private static String replaceFunctionNameIfNecessary(String func,
Predicate<String> replaceFunction,
+ private static void replaceFunctionNameIfNecessary(String func,
Predicate<String> replaceFunction,
FunctionCallExpr functionCallExpr, TOdbcTableType tableType) {
if (replaceFunction.test(func)) {
String newFunc;
@@ -168,9 +171,48 @@ public class JdbcFunctionPushDownRule {
}
if (newFunc != null) {
functionCallExpr.setFnName(FunctionName.createBuiltinName(newFunc));
- func = functionCallExpr.getFnName().getFunction();
}
}
- return func;
+ }
+
+ // Function used to convert nereids planner's function to old planner's
function
+ private static Expr replaceGenericFunctionExpr(FunctionCallExpr
functionCallExpr, String func) {
+ Map<String, String> supportedTimeUnits = Maps.newHashMap();
+ supportedTimeUnits.put("years", "YEAR");
+ supportedTimeUnits.put("months", "MONTH");
+ supportedTimeUnits.put("weeks", "WEEK");
+ supportedTimeUnits.put("days", "DAY");
+ supportedTimeUnits.put("hours", "HOUR");
+ supportedTimeUnits.put("minutes", "MINUTE");
+ supportedTimeUnits.put("seconds", "SECOND");
+
+ String baseFuncName = null;
+ String timeUnit = null;
+
+ for (Map.Entry<String, String> entry : supportedTimeUnits.entrySet()) {
+ if (func.endsWith(entry.getKey() + "_add")) {
+ baseFuncName = "date_add";
+ timeUnit = entry.getValue();
+ break;
+ } else if (func.endsWith(entry.getKey() + "_sub")) {
+ baseFuncName = "date_sub";
+ timeUnit = entry.getValue();
+ break;
+ }
+ }
+
+ if (baseFuncName != null && timeUnit != null) {
+ if (functionCallExpr.getChildren().size() == 2) {
+ Expr child1 = functionCallExpr.getChild(0);
+ Expr child2 = functionCallExpr.getChild(1);
+ return new TimestampArithmeticExpr(
+ baseFuncName,
+ child1,
+ child2,
+ timeUnit
+ );
+ }
+ }
+ return functionCallExpr;
}
}
diff --git
a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
index 555665f08a1..dea68a6b71f 100644
--- a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
@@ -368,3 +368,45 @@ year SMALLINT Yes false \N NONE
-- !money_format --
1
+-- !date_add_year --
+2 2022-01-01
+
+-- !date_add_month --
+2 2022-01-01
+
+-- !date_add_week --
+2 2022-01-01
+
+-- !date_add_day --
+2 2022-01-01
+
+-- !date_add_hour --
+2 2022-01-01
+
+-- !date_add_min --
+2 2022-01-01
+
+-- !date_add_sec --
+2 2022-01-01
+
+-- !date_sub_year --
+2 2022-01-01
+
+-- !date_sub_month --
+2 2022-01-01
+
+-- !date_sub_week --
+2 2022-01-01
+
+-- !date_sub_day --
+2 2022-01-01
+
+-- !date_sub_hour --
+2 2022-01-01
+
+-- !date_sub_min --
+2 2022-01-01
+
+-- !date_sub_sec --
+2 2022-01-01
+
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
index 3457ae2f6f8..71391ed21ea 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
@@ -421,6 +421,95 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
contains "QUERY: SELECT `k6`, `k8` FROM `doris_test`.`test1`
WHERE ((`k8` = 1))"
}
sql """ set enable_ext_func_pred_pushdown = "true"; """
+ // test date_add
+ sql """ set disable_nereids_rules='NORMALIZE_REWRITE_RULES'; """
+ order_qt_date_add_year """ select * from test_zd where
date_add(d_z,interval 1 year) = '2023-01-01' order by 1; """
+ explain {
+ sql("select * from test_zd where date_add(d_z,interval 1 year)
= '2023-01-01' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_add(`d_z`, INTERVAL 1 year) = '2023-01-01')"
+ }
+ order_qt_date_add_month """ select * from test_zd where
date_add(d_z,interval 1 month) = '2022-02-01' order by 1; """
+ explain {
+ sql("select * from test_zd where date_add(d_z,interval 1
month) = '2022-02-01' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_add(`d_z`, INTERVAL 1 month) = '2022-02-01')"
+ }
+ order_qt_date_add_week """ select * from test_zd where
date_add(d_z,interval 1 week) = '2022-01-08' order by 1; """
+ explain {
+ sql("select * from test_zd where date_add(d_z,interval 1 week)
= '2022-01-08' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_add(`d_z`, INTERVAL 1 week) = '2022-01-08')"
+ }
+ order_qt_date_add_day """ select * from test_zd where
date_add(d_z,interval 1 day) = '2022-01-02' order by 1; """
+ explain {
+ sql("select * from test_zd where date_add(d_z,interval 1 day)
= '2022-01-02' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_add(`d_z`, INTERVAL 1 day) = '2022-01-02')"
+ }
+ order_qt_date_add_hour """ select * from test_zd where
date_add(d_z,interval 1 hour) = '2022-01-01 01:00:00' order by 1; """
+ explain {
+ sql("select * from test_zd where date_add(d_z,interval 1 hour)
= '2022-01-01 01:00:00' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_add(`d_z`, INTERVAL 1 hour) = '2022-01-01
01:00:00')"
+ }
+ order_qt_date_add_min """ select * from test_zd where
date_add(d_z,interval 1 minute) = '2022-01-01 00:01:00' order by 1; """
+ explain {
+ sql("select * from test_zd where date_add(d_z,interval 1
minute) = '2022-01-01 00:01:00' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_add(`d_z`, INTERVAL 1 minute) = '2022-01-01
00:01:00')"
+ }
+ order_qt_date_add_sec """ select * from test_zd where
date_add(d_z,interval 1 second) = '2022-01-01 00:00:01' order by 1; """
+ explain {
+ sql("select * from test_zd where date_add(d_z,interval 1
second) = '2022-01-01 00:00:01' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_add(`d_z`, INTERVAL 1 second) = '2022-01-01
00:00:01')"
+ }
+ // date_sub
+ order_qt_date_sub_year """ select * from test_zd where
date_sub(d_z,interval 1 year) = '2021-01-01' order by 1; """
+ explain {
+ sql("select * from test_zd where date_sub(d_z,interval 1 year)
= '2021-01-01' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_sub(`d_z`, INTERVAL 1 year) = '2021-01-01')"
+ }
+ order_qt_date_sub_month """ select * from test_zd where
date_sub(d_z,interval 1 month) = '2021-12-01' order by 1; """
+ explain {
+ sql("select * from test_zd where date_sub(d_z,interval 1
month) = '2021-12-01' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_sub(`d_z`, INTERVAL 1 month) = '2021-12-01')"
+ }
+ order_qt_date_sub_week """ select * from test_zd where
date_sub(d_z,interval 1 week) = '2021-12-25' order by 1; """
+ explain {
+ sql("select * from test_zd where date_sub(d_z,interval 1 week)
= '2021-12-25' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_sub(`d_z`, INTERVAL 1 week) = '2021-12-25')"
+ }
+ order_qt_date_sub_day """ select * from test_zd where
date_sub(d_z,interval 1 day) = '2021-12-31' order by 1; """
+ explain {
+ sql("select * from test_zd where date_sub(d_z,interval 1 day)
= '2021-12-31' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_sub(`d_z`, INTERVAL 1 day) = '2021-12-31')"
+ }
+ order_qt_date_sub_hour """ select * from test_zd where
date_sub(d_z,interval 1 hour) = '2021-12-31 23:00:00' order by 1; """
+ explain {
+ sql("select * from test_zd where date_sub(d_z,interval 1 hour)
= '2021-12-31 23:00:00' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_sub(`d_z`, INTERVAL 1 hour) = '2021-12-31
23:00:00')"
+ }
+ order_qt_date_sub_min """ select * from test_zd where
date_sub(d_z,interval 1 minute) = '2021-12-31 23:59:00' order by 1; """
+ explain {
+ sql("select * from test_zd where date_sub(d_z,interval 1
minute) = '2021-12-31 23:59:00' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_sub(`d_z`, INTERVAL 1 minute) = '2021-12-31
23:59:00')"
+ }
+ order_qt_date_sub_sec """ select * from test_zd where
date_sub(d_z,interval 1 second) = '2021-12-31 23:59:59' order by 1; """
+ explain {
+ sql("select * from test_zd where date_sub(d_z,interval 1
second) = '2021-12-31 23:59:59' order by 1;")
+
+ contains " QUERY: SELECT `id`, `d_z` FROM
`doris_test`.`test_zd` WHERE (date_sub(`d_z`, INTERVAL 1 second) = '2021-12-31
23:59:59')"
+ }
+ sql """ set disable_nereids_rules=''; """
+
} finally {
res_dbs_log = sql "show databases;"
for(int i = 0;i < res_dbs_log.size();i++) {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
