This is an automated email from the ASF dual-hosted git repository.
zykkk pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new 5c19ae070ec [pick][fix](catalog) fix data_sub/data_add func pushdown
in jdbcscan (#30882)
5c19ae070ec is described below
commit 5c19ae070ecf9934b177b24b20603a22540d9d8f
Author: zy-kkk <zhongy...@gmail.com>
AuthorDate: Tue Feb 6 15:49:31 2024 +0800
[pick][fix](catalog) fix data_sub/data_add func pushdown in jdbcscan
(#30882)
---
.../external/jdbc/JdbcFunctionPushDownRule.java | 64 +++++++++++++---
.../jdbc/test_mysql_jdbc_catalog.out | 42 +++++++++++
.../jdbc/test_mysql_jdbc_catalog.groovy | 88 ++++++++++++++++++++++
3 files changed, 183 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 af836d95c02..b02db0b1ee6 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
@@ -399,3 +399,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 fdc1f84e382..08590d08fea 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
@@ -416,6 +416,94 @@ 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 """ admin set frontend config ("enable_func_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: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
