This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new b2bac26c173 [fix](jdbc catalog) Disable oracle scan null operator
pushdown (#41563) (#41712)
b2bac26c173 is described below
commit b2bac26c17374e0ce00065e530e18ac0792188be
Author: zy-kkk <[email protected]>
AuthorDate: Fri Oct 11 21:01:05 2024 +0800
[fix](jdbc catalog) Disable oracle scan null operator pushdown (#41563)
(#41712)
Because Oracle versions below Oracle21 do not support null as an
operator, and considering that most users' Oracle versions are below
Oracle21, we disable Oracle's null operator pushdown by default.
pick (#41563)
---
.../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 26df521d9e9..cbb391bd3fa 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;
@@ -297,6 +298,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)) {
@@ -362,4 +370,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 6a2a6014fcc..da95433e01f 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
@@ -481,6 +481,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";
@@ -654,6 +656,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]
