This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new db7051ca1b8 [fix](jdbc catalog) Disable oracle scan null operator
pushdown #41563 (#42179)
db7051ca1b8 is described below
commit db7051ca1b80d3aed0382dfb5b9302570d013a3c
Author: Rayner Chen <[email protected]>
AuthorDate: Mon Oct 21 16:38:54 2024 +0800
[fix](jdbc catalog) Disable oracle scan null operator pushdown #41563
(#42179)
cherry pick from #41563
Co-authored-by: zy-kkk <[email protected]>
---
.../doris/datasource/jdbc/source/JdbcScanNode.java | 14 +++++++++
.../java/org/apache/doris/qe/SessionVariable.java | 7 +++++
.../jdbc/test_oracle_jdbc_catalog.out | 35 ++++++++++++++++++++++
.../jdbc/test_oracle_jdbc_catalog.groovy | 26 ++++++++++++++++
4 files changed, 82 insertions(+)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
index 6b63c7c7b6e..ffd56ca4d73 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
@@ -24,6 +24,7 @@ import org.apache.doris.analysis.DateLiteral;
import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.ExprSubstitutionMap;
import org.apache.doris.analysis.FunctionCallExpr;
+import org.apache.doris.analysis.NullLiteral;
import org.apache.doris.analysis.SlotDescriptor;
import org.apache.doris.analysis.SlotRef;
import org.apache.doris.analysis.TupleDescriptor;
@@ -304,6 +305,13 @@ public class JdbcScanNode extends ExternalScanNode {
}
private static boolean shouldPushDownConjunct(TOdbcTableType tableType,
Expr expr) {
+ // Prevent pushing down expressions with NullLiteral to Oracle
+ if (ConnectContext.get() != null
+ &&
!ConnectContext.get().getSessionVariable().jdbcOracleNullPredicatePushdown
+ && containsNullLiteral(expr)
+ && tableType.equals(TOdbcTableType.ORACLE)) {
+ return false;
+ }
if (containsFunctionCallExpr(expr)) {
if (tableType.equals(TOdbcTableType.MYSQL) ||
tableType.equals(TOdbcTableType.CLICKHOUSE)
|| tableType.equals(TOdbcTableType.ORACLE)) {
@@ -369,4 +377,10 @@ public class JdbcScanNode extends ExternalScanNode {
}
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
+
+ private static boolean containsNullLiteral(Expr expr) {
+ List<NullLiteral> nullExprList = Lists.newArrayList();
+ expr.collect(NullLiteral.class, nullExprList);
+ return !nullExprList.isEmpty();
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
index 21aab7513e0..4ff283fd577 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
@@ -506,6 +506,8 @@ public class SessionVariable implements Serializable,
Writable {
public static final String JDBC_CLICKHOUSE_QUERY_FINAL =
"jdbc_clickhouse_query_final";
+ public static final String JDBC_ORACLE_NULL_PREDICATE_PUSHDOWN =
"jdbc_oracle_null_predicate_pushdown";
+
public static final String ENABLE_MEMTABLE_ON_SINK_NODE =
"enable_memtable_on_sink_node";
@@ -685,6 +687,11 @@ public class SessionVariable implements Serializable,
Writable {
"Whether to add the FINAL keyword to the query SQL when
querying ClickHouse JDBC external tables."})
public boolean jdbcClickhouseQueryFinal = false;
+ @VariableMgr.VarAttr(name = JDBC_ORACLE_NULL_PREDICATE_PUSHDOWN,
needForward = true,
+ description = {"是否允许将 NULL 谓词下推到 Oracle JDBC 外部表。",
+ "Whether to allow NULL predicates to be pushed down to
Oracle JDBC external tables."})
+ public boolean jdbcOracleNullPredicatePushdown = false;
+
@VariableMgr.VarAttr(name = ROUND_PRECISE_DECIMALV2_VALUE)
public boolean roundPreciseDecimalV2Value = false;
diff --git
a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
index 82afecb61bd..c32cd8d172a 100644
--- a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
@@ -269,3 +269,38 @@ doris
1 111 123 7456123.89 573 34 673.43 34.1264 60.0
23.231 99 9999 999999999 999999999999999999 999 99999
9999999999 9999999999999999999 1 china beijing alice
abcdefghrjkmnopq 123.45 12300 0.0012345 2022-01-21T05:23:01
2019-11-12T20:33:57.999 2019-11-12T20:33:57.999998
2019-11-12T20:33:57.999996 2019-11-12T20:33:57.999997 223-9 12
10:23:1.123457000
2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N \N
+-- !null_operator1 --
+1 alice 20 99.5
+2 bob 21 90.5
+3 jerry 23 88.0
+4 andy 21 93.0
+
+-- !null_operator2 --
+2 bob 21 90.5
+3 jerry 23 88.0
+4 andy 21 93.0
+
+-- !null_operator3 --
+1 alice 20 99.5
+
+-- !null_operator4 --
+
+-- !null_operator5 --
+
+-- !null_operator6 --
+
+-- !null_operator7 --
+3 jerry 23 88.0
+
+-- !null_operator8 --
+1 alice 20 99.5
+4 andy 21 93.0
+
+-- !null_operator9 --
+
+-- !null_operator10 --
+1 alice 20 99.5
+2 bob 21 90.5
+3 jerry 23 88.0
+4 andy 21 93.0
+
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
index 571dda0e5d8..8e498030209 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
@@ -297,5 +297,31 @@ suite("test_oracle_jdbc_catalog",
"p0,external,oracle,external_docker,external_d
qt_query_ojdbc6_all_types """ select * from
oracle_ojdbc6.DORIS_TEST.TEST_ALL_TYPES order by 1; """
sql """drop catalog if exists oracle_ojdbc6; """
+
+ // test oracle null operator
+ sql """ drop catalog if exists oracle_null_operator; """
+ sql """ create catalog if not exists oracle_null_operator properties(
+ "type"="jdbc",
+ "user"="doris_test",
+ "password"="123456",
+ "jdbc_url" =
"jdbc:oracle:thin:@${externalEnvIp}:${oracle_port}:${SID}",
+ "driver_url" = "${driver_url}",
+ "driver_class" = "oracle.jdbc.driver.OracleDriver"
+ );"""
+
+ sql """ use oracle_null_operator.DORIS_TEST; """
+ order_qt_null_operator1 """ SELECT * FROM STUDENT WHERE (id IS NOT
NULL OR NULL); """
+ order_qt_null_operator2 """ SELECT * FROM STUDENT WHERE (age > 20 OR
NULL); """
+ order_qt_null_operator3 """ SELECT * FROM STUDENT WHERE (name =
'alice' AND age = 20); """
+ order_qt_null_operator4 """ SELECT * FROM STUDENT WHERE (LENGTH(name)
> 3 AND NULL); """
+ order_qt_null_operator5 """ SELECT * FROM STUDENT WHERE (age = NULL);
"""
+ order_qt_null_operator6 """ SELECT * FROM STUDENT WHERE (score IS
NULL); """
+ order_qt_null_operator7 """ SELECT * FROM STUDENT WHERE ((age > 20 AND
score < 90) OR NULL); """
+ order_qt_null_operator8 """ SELECT * FROM STUDENT WHERE (age BETWEEN
20 AND 25) AND (name LIKE 'a%'); """
+ order_qt_null_operator9 """ SELECT * FROM STUDENT WHERE (id IS NOT
NULL AND NULL); """
+ order_qt_null_operator10 """ SELECT * FROM STUDENT WHERE (name IS NULL
OR age IS NOT NULL); """
+
+ sql """ drop catalog if exists oracle_null_operator; """
+
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
