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 1c142309a6 [refactor](jdbc catalog) refactor JdbcFunctionPushDownRule
(#23826)
1c142309a6 is described below
commit 1c142309a6e69a78c4a3db7a336c40ffccd69e47
Author: zy-kkk <[email protected]>
AuthorDate: Fri Sep 15 22:16:07 2023 +0800
[refactor](jdbc catalog) refactor JdbcFunctionPushDownRule (#23826)
1. Change from using string matching function to using Expr matching
2. Replace the `nvl` function with `ifnull` when pushed down to MySQL
3. Adapt ClickHouse's `from_unixtime` function to push down
4. Non-function filtering can still be pushed down when
`enable_func_pushdown` is set to false
---
.../docker-compose/clickhouse/clickhouse.yaml.tpl | 2 +-
.../clickhouse/init/03-create-table.sql | 8 ++
.../docker-compose/clickhouse/init/04-insert.sql | 3 +
.../external/jdbc/JdbcFunctionPushDownRule.java | 123 ++++++++++++++++++---
.../doris/planner/external/jdbc/JdbcScanNode.java | 60 +++++++---
.../doris/planner/external/odbc/OdbcScanNode.java | 15 ++-
.../jdbc/test_clickhouse_jdbc_catalog.out | Bin 2766 -> 2782 bytes
.../jdbc/test_mysql_jdbc_catalog.out | 46 ++++----
.../jdbc/test_clickhouse_jdbc_catalog.groovy | 9 ++
.../jdbc/test_mysql_jdbc_catalog.groovy | 61 ++++++++--
10 files changed, 264 insertions(+), 63 deletions(-)
diff --git a/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl
b/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl
index 3832680dea..6e34c459be 100644
--- a/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl
+++ b/docker/thirdparties/docker-compose/clickhouse/clickhouse.yaml.tpl
@@ -19,7 +19,7 @@ version: "2.1"
services:
doris--clickhouse:
- image: "clickhouse/clickhouse-server:latest"
+ image: "clickhouse/clickhouse-server:23.3"
restart: always
environment:
CLICKHOUSE_PASSWORD: 123456
diff --git
a/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql
b/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql
index c13fac5cf9..46d2e6ffc3 100644
--- a/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/clickhouse/init/03-create-table.sql
@@ -131,3 +131,11 @@ CREATE TABLE doris_test.final_test
)
ENGINE = ReplacingMergeTree
ORDER BY key;
+
+CREATE TABLE doris_test.ts
+(
+ id Int64,
+ ts UInt64
+)
+ENGINE = MergeTree
+ORDER BY id;
\ No newline at end of file
diff --git a/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql
b/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql
index 69c2ebd25f..c5e83eefe0 100644
--- a/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/clickhouse/init/04-insert.sql
@@ -39,3 +39,6 @@ INSERT INTO doris_test.json VALUES ('1','{"a": 1, "b": { "c":
2, "d": [1, 2, 3]
INSERT INTO doris_test.final_test Values (1, 'first');
INSERT INTO doris_test.final_test Values (1, 'second');
+INSERT INTO doris_test.ts values (1,1694438743);
+
+
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 bac9d62dbe..d1a44cb700 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
@@ -17,33 +17,130 @@
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.thrift.TOdbcTableType;
+import com.google.common.base.Preconditions;
+import com.google.common.collect.Maps;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+
+import java.util.List;
+import java.util.Map;
import java.util.TreeSet;
+import java.util.function.Predicate;
public class JdbcFunctionPushDownRule {
- private static final TreeSet<String> UNSUPPORTED_MYSQL_FUNCTIONS = new
TreeSet<>(String.CASE_INSENSITIVE_ORDER);
+ private static final Logger LOG =
LogManager.getLogger(JdbcFunctionPushDownRule.class);
+ private static final TreeSet<String> MYSQL_UNSUPPORTED_FUNCTIONS = new
TreeSet<>(String.CASE_INSENSITIVE_ORDER);
+
+ static {
+ MYSQL_UNSUPPORTED_FUNCTIONS.add("date_trunc");
+ MYSQL_UNSUPPORTED_FUNCTIONS.add("money_format");
+ }
+
+ private static final TreeSet<String> CLICKHOUSE_SUPPORTED_FUNCTIONS = new
TreeSet<>(String.CASE_INSENSITIVE_ORDER);
+
+ static {
+ CLICKHOUSE_SUPPORTED_FUNCTIONS.add("from_unixtime");
+ }
+
+ private static boolean isMySQLFunctionUnsupported(String functionName) {
+ return
MYSQL_UNSUPPORTED_FUNCTIONS.contains(functionName.toLowerCase());
+ }
+
+ private static boolean isClickHouseFunctionUnsupported(String
functionName) {
+ return
!CLICKHOUSE_SUPPORTED_FUNCTIONS.contains(functionName.toLowerCase());
+ }
+
+
+ private static final Map<String, String> REPLACE_MYSQL_FUNCTIONS =
Maps.newHashMap();
+
+ static {
+ REPLACE_MYSQL_FUNCTIONS.put("nvl", "ifnull");
+ }
+
+ 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 {
- UNSUPPORTED_MYSQL_FUNCTIONS.add("date_trunc");
- UNSUPPORTED_MYSQL_FUNCTIONS.add("money_format");
+ REPLACE_CLICKHOUSE_FUNCTIONS.put("from_unixtime", "FROM_UNIXTIME");
+ }
+
+ private static boolean isReplaceClickHouseFunctions(String functionName) {
+ return
REPLACE_CLICKHOUSE_FUNCTIONS.containsKey(functionName.toLowerCase());
}
- public static boolean isUnsupportedFunctions(TOdbcTableType tableType,
String filter) {
- if (tableType.equals(TOdbcTableType.MYSQL)) {
- return isMySQLUnsupportedFunctions(filter);
+ public static Expr processFunctions(TOdbcTableType tableType, Expr expr,
List<String> errors) {
+ if (tableType == null || expr == null) {
+ return expr;
+ }
+
+ Predicate<String> checkFunction;
+ Predicate<String> replaceFunction;
+
+ if (TOdbcTableType.MYSQL.equals(tableType)) {
+ replaceFunction =
JdbcFunctionPushDownRule::isReplaceMysqlFunctions;
+ checkFunction =
JdbcFunctionPushDownRule::isMySQLFunctionUnsupported;
+ } else if (TOdbcTableType.CLICKHOUSE.equals(tableType)) {
+ replaceFunction =
JdbcFunctionPushDownRule::isReplaceClickHouseFunctions;
+ checkFunction =
JdbcFunctionPushDownRule::isClickHouseFunctionUnsupported;
} else {
- return false;
+ return expr;
}
+
+ return processFunctionsRecursively(expr, checkFunction,
replaceFunction, errors, tableType);
}
- private static boolean isMySQLUnsupportedFunctions(String filter) {
- for (String func : UNSUPPORTED_MYSQL_FUNCTIONS) {
- if (filter.contains(func)) {
- return true;
+ private static Expr processFunctionsRecursively(Expr expr,
Predicate<String> checkFunction,
+ Predicate<String> replaceFunction, List<String> errors,
TOdbcTableType tableType) {
+ if (expr instanceof FunctionCallExpr) {
+ FunctionCallExpr functionCallExpr = (FunctionCallExpr) expr;
+ String func = functionCallExpr.getFnName().getFunction();
+
+ Preconditions.checkArgument(!func.isEmpty(), "function can not be
empty");
+
+ func = replaceFunctionNameIfNecessary(func, replaceFunction,
functionCallExpr, tableType);
+
+ if (!func.isEmpty() && checkFunction.test(func)) {
+ String errMsg = "Unsupported function: " + func + " in expr: "
+ expr.toMySql()
+ + " in JDBC Table Type: " + tableType;
+ LOG.warn(errMsg);
+ errors.add(errMsg);
}
}
- return false;
+
+ List<Expr> children = expr.getChildren();
+ for (int i = 0; i < children.size(); i++) {
+ Expr child = children.get(i);
+ Expr newChild = processFunctionsRecursively(child, checkFunction,
replaceFunction, errors, tableType);
+ expr.setChild(i, newChild);
+ }
+
+ return expr;
}
-}
+ private static String replaceFunctionNameIfNecessary(String func,
Predicate<String> replaceFunction,
+ FunctionCallExpr functionCallExpr, TOdbcTableType tableType) {
+ if (replaceFunction.test(func)) {
+ String newFunc;
+ if (TOdbcTableType.MYSQL.equals(tableType)) {
+ newFunc = REPLACE_MYSQL_FUNCTIONS.get(func.toLowerCase());
+ } else if (TOdbcTableType.CLICKHOUSE.equals(tableType)) {
+ newFunc = REPLACE_CLICKHOUSE_FUNCTIONS.get(func);
+ } else {
+ newFunc = null;
+ }
+ if (newFunc != null) {
+
functionCallExpr.setFnName(FunctionName.createBuiltinName(newFunc));
+ func = functionCallExpr.getFnName().getFunction();
+ }
+ }
+ return func;
+ }
+}
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 94b6aeb160..ccba1a165f 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
@@ -19,6 +19,7 @@ package org.apache.doris.planner.external.jdbc;
import org.apache.doris.analysis.Analyzer;
import org.apache.doris.analysis.BinaryPredicate;
+import org.apache.doris.analysis.BoolLiteral;
import org.apache.doris.analysis.DateLiteral;
import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.ExprSubstitutionMap;
@@ -113,7 +114,7 @@ public class JdbcScanNode extends ExternalScanNode {
break;
}
}
- //clean conjusts cause graph sannnode no need conjuncts
+ // clean conjusts cause graph sannnode no need conjuncts
conjuncts = Lists.newArrayList();
}
@@ -133,19 +134,32 @@ public class JdbcScanNode extends ExternalScanNode {
}
ArrayList<Expr> conjunctsList = Expr.cloneList(conjuncts, sMap);
+ List<String> errors = Lists.newArrayList();
+ List<Expr> pushDownConjuncts =
collectConjunctsToPushDown(conjunctsList, errors);
+
+ for (Expr individualConjunct : pushDownConjuncts) {
+ String filter = conjunctExprToString(jdbcType, individualConjunct);
+ filters.add(filter);
+ conjuncts.remove(individualConjunct);
+ }
+ }
+
+ private List<Expr> collectConjunctsToPushDown(List<Expr> conjunctsList,
List<String> errors) {
+ List<Expr> pushDownConjuncts = new ArrayList<>();
for (Expr p : conjunctsList) {
if (shouldPushDownConjunct(jdbcType, p)) {
- String filter = conjunctExprToString(jdbcType, p);
- if (filter.equals("TRUE")) {
- filter = "1 = 1";
- }
- if (JdbcFunctionPushDownRule.isUnsupportedFunctions(jdbcType,
filter)) {
- continue;
+ List<Expr> individualConjuncts = p.getConjuncts();
+ for (Expr individualConjunct : individualConjuncts) {
+ Expr newp =
JdbcFunctionPushDownRule.processFunctions(jdbcType, individualConjunct, errors);
+ if (!errors.isEmpty()) {
+ errors.clear();
+ continue;
+ }
+ pushDownConjuncts.add(newp);
}
- filters.add(filter);
- conjuncts.remove(p);
}
}
+ return pushDownConjuncts;
}
private void createJdbcColumns() {
@@ -282,7 +296,7 @@ public class JdbcScanNode extends ExternalScanNode {
@Override
public int getNumInstances() {
return
ConnectContext.get().getSessionVariable().getEnablePipelineEngine()
- ?
ConnectContext.get().getSessionVariable().getParallelExecInstanceNum() : 1;
+ ?
ConnectContext.get().getSessionVariable().getParallelExecInstanceNum() : 1;
}
@Override
@@ -292,17 +306,22 @@ public class JdbcScanNode extends ExternalScanNode {
tbl.getId(), -1L);
}
- // Now some database have different function call like doris, now doris do
not
- // push down the function call except MYSQL
- public static boolean shouldPushDownConjunct(TOdbcTableType tableType,
Expr expr) {
- if (!tableType.equals(TOdbcTableType.MYSQL)) {
- List<FunctionCallExpr> fnExprList = Lists.newArrayList();
- expr.collect(FunctionCallExpr.class, fnExprList);
- if (!fnExprList.isEmpty()) {
+ private static boolean shouldPushDownConjunct(TOdbcTableType tableType,
Expr expr) {
+ if (containsFunctionCallExpr(expr)) {
+ if (tableType.equals(TOdbcTableType.MYSQL) ||
tableType.equals(TOdbcTableType.CLICKHOUSE)) {
+ return Config.enable_func_pushdown;
+ } else {
return false;
}
+ } else {
+ return true;
}
- return Config.enable_func_pushdown;
+ }
+
+ private static boolean containsFunctionCallExpr(Expr expr) {
+ List<FunctionCallExpr> fnExprList = Lists.newArrayList();
+ expr.collect(FunctionCallExpr.class, fnExprList);
+ return !fnExprList.isEmpty();
}
public static String conjunctExprToString(TOdbcTableType tableType, Expr
expr) {
@@ -338,6 +357,11 @@ public class JdbcScanNode extends ExternalScanNode {
}
}
+ // only for old planner
+ if (expr.contains(BoolLiteral.class) &&
expr.getStringValue().equals("1") && expr.getChildren().isEmpty()) {
+ return "1 = 1";
+ }
+
return expr.toMySql();
}
}
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 0f27e9da58..bf4e835e4f 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
@@ -20,6 +20,7 @@ package org.apache.doris.planner.external.odbc;
import org.apache.doris.analysis.Analyzer;
import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.ExprSubstitutionMap;
+import org.apache.doris.analysis.FunctionCallExpr;
import org.apache.doris.analysis.SlotDescriptor;
import org.apache.doris.analysis.SlotRef;
import org.apache.doris.analysis.TupleDescriptor;
@@ -28,6 +29,7 @@ import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.JdbcTable;
import org.apache.doris.catalog.OdbcTable;
import org.apache.doris.common.AnalysisException;
+import org.apache.doris.common.Config;
import org.apache.doris.common.UserException;
import org.apache.doris.planner.PlanNodeId;
import org.apache.doris.planner.external.ExternalScanNode;
@@ -181,7 +183,7 @@ public class OdbcScanNode extends ExternalScanNode {
}
ArrayList<Expr> odbcConjuncts = Expr.cloneList(conjuncts, sMap);
for (Expr p : odbcConjuncts) {
- if (JdbcScanNode.shouldPushDownConjunct(odbcType, p)) {
+ if (shouldPushDownConjunct(odbcType, p)) {
String filter = JdbcScanNode.conjunctExprToString(odbcType, p);
filters.add(filter);
conjuncts.remove(p);
@@ -224,4 +226,15 @@ public class OdbcScanNode extends ExternalScanNode {
return
ConnectContext.get().getSessionVariable().getEnablePipelineEngine()
?
ConnectContext.get().getSessionVariable().getParallelExecInstanceNum() : 1;
}
+
+ public static boolean shouldPushDownConjunct(TOdbcTableType tableType,
Expr expr) {
+ if (!tableType.equals(TOdbcTableType.MYSQL)) {
+ List<FunctionCallExpr> fnExprList = Lists.newArrayList();
+ expr.collect(FunctionCallExpr.class, fnExprList);
+ if (!fnExprList.isEmpty()) {
+ return false;
+ }
+ }
+ return Config.enable_func_pushdown;
+ }
}
diff --git
a/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out
index 0b847974ef..05372e3f0a 100644
Binary files
a/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out
and
b/regression-test/data/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.out
differ
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 5037157a24..d5cc90fa80 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
@@ -238,29 +238,6 @@ VIEWS
\N
2023-06-17T10:00
--- !filter1 --
-1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
-
--- !filter2 --
-1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
-2 8 9 8 2900.42 1 6 97486621.73
59634489.39 c 3 2 0 a e 7 4
-3 5 7 3 6276.86 8 9 32758730.38
10260499.72 c 8 1 0 d c 9 2
-4 3 7 5 2449.00 6 3 91359059.28
64743145.92 e 7 8 0 b d 8 4
-5 6 4 5 9137.82 2 7 26526675.70
90098303.36 a 6 7 0 d e 4 1
-6 3 6 8 7601.25 4 9 49117098.47
46499188.80 c 3 3 0 c d 4 8
-7 3 2 8 5297.81 9 3 23753694.20
96930000.64 c 7 2 0 b e 1 5
-8 3 6 7 3683.85 5 7 26056250.91
1127755.43 b 7 6 0 d b 4 7
-9 3 9 1 4785.38 1 5 95199488.12
94869703.42 a 4 4 0 c d 2 4
-
--- !filter3 --
-1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
-
--- !date_trunc --
-2023-06-17T10:00
-
--- !money_format --
-1
-
-- !test_insert1 --
doris1 18
@@ -305,3 +282,26 @@ sys
-- !mysql_view --
10086 4294967295 201
+-- !filter1 --
+1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
+
+-- !filter2 --
+1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
+2 8 9 8 2900.42 1 6 97486621.73
59634489.39 c 3 2 0 a e 7 4
+3 5 7 3 6276.86 8 9 32758730.38
10260499.72 c 8 1 0 d c 9 2
+4 3 7 5 2449.00 6 3 91359059.28
64743145.92 e 7 8 0 b d 8 4
+5 6 4 5 9137.82 2 7 26526675.70
90098303.36 a 6 7 0 d e 4 1
+6 3 6 8 7601.25 4 9 49117098.47
46499188.80 c 3 3 0 c d 4 8
+7 3 2 8 5297.81 9 3 23753694.20
96930000.64 c 7 2 0 b e 1 5
+8 3 6 7 3683.85 5 7 26056250.91
1127755.43 b 7 6 0 d b 4 7
+9 3 9 1 4785.38 1 5 95199488.12
94869703.42 a 4 4 0 c d 2 4
+
+-- !filter3 --
+1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
+
+-- !date_trunc --
+2023-06-17T10:00
+
+-- !money_format --
+1
+
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy
index 9c2050ab70..b01f200574 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_clickhouse_jdbc_catalog.groovy
@@ -68,6 +68,15 @@ suite("test_clickhouse_jdbc_catalog",
"p0,external,clickhouse,external_docker,ex
order_qt_final1 """select * from final_test"""
sql "set jdbc_clickhouse_query_final = false;"
order_qt_final2 """select * from final_test"""
+ order_qt_func_push """select * from ts where
from_unixtime(ts,'yyyyMMdd') >= '2022-01-01';"""
+ explain {
+ sql("select * from ts where from_unixtime(ts,'yyyyMMdd') >=
'2022-01-01';")
+ contains """QUERY: SELECT "id", "ts" FROM "doris_test"."ts" WHERE
(FROM_UNIXTIME(ts, '%Y%m%d') >= '2022-01-01')"""
+ }
+ explain {
+ sql("select * from ts where nvl(ts,null) >= '2022-01-01';")
+ contains """QUERY: SELECT "id", "ts" FROM "doris_test"."ts""""
+ }
sql """ drop catalog if exists ${catalog_name} """
}
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 cc8f4fd0fe..3717f17bec 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
@@ -112,11 +112,6 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
order_qt_auto_default_t """insert into ${auto_default_t}(name)
values('a'); """
order_qt_dt """select * from ${dt}; """
order_qt_dt_null """select * from ${dt_null} order by 1; """
- order_qt_filter1 """select * from ${ex_tb17} where id = 1; """
- order_qt_filter2 """select * from ${ex_tb17} where 1=1 order by 1; """
- order_qt_filter3 """select * from ${ex_tb17} where id = 1 and 1 = 1;
"""
- order_qt_date_trunc """ SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03
17:39:05'; """
- order_qt_money_format """ select k8 from test1 where money_format(k8)
= '1.00'; """
// test insert
String uuid1 = UUID.randomUUID().toString();
@@ -238,8 +233,60 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"jdbc.driver_url" = "${driver_url}",
"jdbc.driver_class" = "com.mysql.cj.jdbc.Driver");
"""
- qt_mysql_view """ select * from view_catalog.doris_test.mysql_view
order by col_1;"""
- sql """ drop catalog if exists view_catalog; """
+ qt_mysql_view """ select * from view_catalog.doris_test.mysql_view
order by col_1;"""
+ sql """ drop catalog if exists view_catalog; """
+
+ sql """ drop catalog if exists mysql_fun_push_catalog """
+ sql """ CREATE CATALOG mysql_fun_push_catalog PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="root",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
+ "jdbc.driver_url" = "${driver_url}",
+ "jdbc.driver_class" = "com.mysql.cj.jdbc.Driver");
+ """
+
+ sql """switch mysql_fun_push_catalog"""
+ sql """ use ${ex_db_name}"""
+ sql """ admin set frontend config ("enable_func_pushdown" = "true");
"""
+ order_qt_filter1 """select * from ${ex_tb17} where id = 1; """
+ order_qt_filter2 """select * from ${ex_tb17} where 1=1 order by 1; """
+ order_qt_filter3 """select * from ${ex_tb17} where id = 1 and 1 = 1;
"""
+ order_qt_date_trunc """ SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03
17:39:05'; """
+ order_qt_money_format """ select k8 from test1 where money_format(k8)
= '1.00'; """
+ explain {
+ sql("select k8 from test1 where money_format(k8) = '1.00';")
+
+ contains "QUERY: SELECT `k8` FROM `doris_test`.`test1`"
+ }
+ explain {
+ sql ("SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03
17:39:05';")
+
+ contains "QUERY: SELECT `timestamp0` FROM `doris_test`.`dt`"
+ }
+ explain {
+ sql ("SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03 17:39:05'
and timestamp0 > '2022-01-01';;")
+
+ contains "QUERY: SELECT `timestamp0` FROM `doris_test`.`dt` WHERE
(timestamp0 > '2022-01-01 00:00:00')"
+ }
+ explain {
+ sql ("select k6, k8 from test1 where nvl(k6, null) = 1;")
+
+ contains "QUERY: SELECT `k6`, `k8` FROM `doris_test`.`test1` WHERE
(ifnull(k6, NULL) = 1)"
+ }
+ explain {
+ sql ("select k6, k8 from test1 where nvl(nvl(k6, null),null) = 1;")
+
+ contains "QUERY: SELECT `k6`, `k8` FROM `doris_test`.`test1` WHERE
(ifnull(ifnull(k6, NULL), NULL) = 1)"
+ }
+ sql """ admin set frontend config ("enable_func_pushdown" = "false");
"""
+ explain {
+ sql ("select k6, k8 from test1 where nvl(k6, null) = 1 and k8 =
1;")
+
+ contains "QUERY: SELECT `k6`, `k8` FROM `doris_test`.`test1` WHERE
(k8 = 1)"
+ }
+ sql """ admin set frontend config ("enable_func_pushdown" = "true");
"""
+ sql """ drop catalog if exists mysql_fun_push_catalog; """
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
