This is an automated email from the ASF dual-hosted git repository.
dataroaring 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 9d4ecf7c6df branch-3.0: [fix](jdbc catalog) Improve conjunct
expression handling in JdbcScanNode #50542 (#50647)
9d4ecf7c6df is described below
commit 9d4ecf7c6df0232df341af73d9bf5f17ba6db1b0
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Wed May 14 09:47:35 2025 +0800
branch-3.0: [fix](jdbc catalog) Improve conjunct expression handling in
JdbcScanNode #50542 (#50647)
Cherry-picked from #50542
Co-authored-by: zy-kkk <[email protected]>
---
.../doris/datasource/jdbc/source/JdbcScanNode.java | 78 +++-
.../datasource/jdbc/source/JdbcScanNodeTest.java | 496 +++++++++++++++++++++
.../jdbc/test_oracle_jdbc_catalog.out | Bin 7894 -> 10560 bytes
.../jdbc/test_oracle_jdbc_catalog.groovy | 64 +++
4 files changed, 637 insertions(+), 1 deletion(-)
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 911d932adde..31026e6b877 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
@@ -21,10 +21,13 @@ import org.apache.doris.analysis.Analyzer;
import org.apache.doris.analysis.BinaryPredicate;
import org.apache.doris.analysis.BoolLiteral;
import org.apache.doris.analysis.CastExpr;
+import org.apache.doris.analysis.CompoundPredicate;
+import org.apache.doris.analysis.CompoundPredicate.Operator;
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.InPredicate;
import org.apache.doris.analysis.NullLiteral;
import org.apache.doris.analysis.SlotDescriptor;
import org.apache.doris.analysis.SlotRef;
@@ -54,6 +57,7 @@ import com.google.common.base.MoreObjects;
import com.google.common.collect.Lists;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
+import org.jetbrains.annotations.NotNull;
import java.util.ArrayList;
import java.util.List;
@@ -346,6 +350,25 @@ public class JdbcScanNode extends ExternalScanNode {
}
public static String conjunctExprToString(TOdbcTableType tableType, Expr
expr, TableIf tbl) {
+ if (expr == null) {
+ return "";
+ }
+ if (expr instanceof CompoundPredicate) {
+ CompoundPredicate compoundPredicate = (CompoundPredicate) expr;
+ if (compoundPredicate.getOp() == Operator.NOT) {
+ String childString = conjunctExprToString(tableType,
compoundPredicate.getChild(0), tbl);
+ return "(NOT " + childString + ")";
+ } else {
+ String leftString = conjunctExprToString(tableType,
compoundPredicate.getChild(0), tbl);
+ String rightString = "";
+ if (compoundPredicate.getChildren().size() > 1) {
+ rightString = conjunctExprToString(tableType,
compoundPredicate.getChild(1), tbl);
+ }
+ String opString = compoundPredicate.getOp().toString();
+ return "(" + leftString + " " + opString + " " + rightString +
")";
+ }
+ }
+
if (expr.contains(DateLiteral.class) && expr instanceof
BinaryPredicate) {
ArrayList<Expr> children = expr.getChildren();
String filter =
children.get(0).toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
@@ -362,6 +385,34 @@ public class JdbcScanNode extends ExternalScanNode {
return filter;
}
+ if (expr.contains(DateLiteral.class) && expr instanceof InPredicate) {
+ InPredicate inPredicate = (InPredicate) expr;
+ Expr leftChild = inPredicate.getChild(0);
+ String filter =
leftChild.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
+
+ if (inPredicate.isNotIn()) {
+ filter += " NOT";
+ }
+ filter += " IN (";
+
+ List<String> inItemStrings = new ArrayList<>();
+ for (int i = 1; i < inPredicate.getChildren().size(); i++) {
+ Expr inItem = inPredicate.getChild(i);
+ if (tableType.equals(TOdbcTableType.ORACLE)) {
+ inItemStrings.add(handleOracleDateFormat(inItem, tbl));
+ } else if (tableType.equals(TOdbcTableType.TRINO) ||
tableType.equals(TOdbcTableType.PRESTO)) {
+ inItemStrings.add(handleTrinoDateFormat(inItem, tbl));
+ } else {
+
inItemStrings.add(inItem.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl));
+ }
+ }
+
+ filter += String.join(", ", inItemStrings);
+ filter += ")";
+
+ return filter;
+ }
+
// Only for old planner
if (expr.contains(BoolLiteral.class) &&
"1".equals(expr.getStringValue()) && expr.getChildren().isEmpty()) {
return "1 = 1";
@@ -373,11 +424,36 @@ public class JdbcScanNode extends ExternalScanNode {
private static String handleOracleDateFormat(Expr expr, TableIf tbl) {
if (expr.isConstant()
&& (expr.getType().isDatetime() ||
expr.getType().isDatetimeV2())) {
- return "to_date('" + expr.getStringValue() + "', 'yyyy-mm-dd
hh24:mi:ss')";
+ String dateStr = expr.getStringValue();
+ // Check if the date string contains milliseconds/microseconds
+ if (dateStr.contains(".")) {
+ // For Oracle, we need to use to_timestamp for fractional
seconds
+ // Extract date part and fractional seconds part
+ String formatModel = getString(dateStr);
+ return "to_timestamp('" + dateStr + "', '" + formatModel +
"')";
+ }
+ // Regular datetime without fractional seconds
+ return "to_date('" + dateStr + "', 'yyyy-mm-dd hh24:mi:ss')";
}
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
+ @NotNull
+ private static String getString(String dateStr) {
+ String[] parts = dateStr.split("\\.");
+ String fractionPart = parts[1];
+ // Determine the format model based on the length of fractional seconds
+ String formatModel;
+ if (fractionPart.length() <= 3) {
+ // Milliseconds (up to 3 digits)
+ formatModel = "yyyy-mm-dd hh24:mi:ss.FF3";
+ } else {
+ // Microseconds (up to 6 digits)
+ formatModel = "yyyy-mm-dd hh24:mi:ss.FF6";
+ }
+ return formatModel;
+ }
+
private static String handleTrinoDateFormat(Expr expr, TableIf tbl) {
if (expr.isConstant()) {
if (expr.getType().isDate() || expr.getType().isDateV2()) {
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
b/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
new file mode 100644
index 00000000000..ac78a2c6e29
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
@@ -0,0 +1,496 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.datasource.jdbc.source;
+
+import org.apache.doris.analysis.BinaryPredicate;
+import org.apache.doris.analysis.BinaryPredicate.Operator;
+import org.apache.doris.analysis.BoolLiteral;
+import org.apache.doris.analysis.CompoundPredicate;
+import org.apache.doris.analysis.DateLiteral;
+import org.apache.doris.analysis.FloatLiteral;
+import org.apache.doris.analysis.IntLiteral;
+import org.apache.doris.analysis.IsNullPredicate;
+import org.apache.doris.analysis.LikePredicate;
+import org.apache.doris.analysis.SlotRef;
+import org.apache.doris.analysis.StringLiteral;
+import org.apache.doris.catalog.JdbcTable;
+import org.apache.doris.catalog.Type;
+import org.apache.doris.thrift.TOdbcTableType;
+
+import mockit.Expectations;
+import mockit.Mocked;
+import org.junit.Assert;
+import org.junit.Test;
+
+public class JdbcScanNodeTest {
+
+ @Mocked
+ private JdbcTable mockTable;
+
+ @Test
+ public void testSimpleBinaryPredicate() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate predicate = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, predicate, mockTable);
+ Assert.assertEquals("(\"ID\" = 1)", result);
+
+ result = JdbcScanNode.conjunctExprToString(TOdbcTableType.ORACLE,
predicate, mockTable);
+ Assert.assertEquals("(\"ID\" = 1)", result);
+ }
+
+ @Test
+ public void testSimpleCompoundPredicate() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate leftPred = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ StringLiteral stringLiteral = new StringLiteral("test");
+ BinaryPredicate rightPred = new BinaryPredicate(Operator.EQ, nameSlot,
stringLiteral);
+
+ CompoundPredicate compoundPredicate = new
CompoundPredicate(CompoundPredicate.Operator.OR, leftPred, rightPred);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, compoundPredicate,
mockTable);
+ Assert.assertEquals("((\"ID\" = 1) OR (\"NAME\" = 'test'))", result);
+ }
+
+ @Test
+ public void testNestedCompoundPredicate() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // ID = 1 OR (NAME = 'test' AND AGE > 18)
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate leftPred = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ StringLiteral stringLiteral = new StringLiteral("test");
+ BinaryPredicate namePred = new BinaryPredicate(Operator.EQ, nameSlot,
stringLiteral);
+
+ SlotRef ageSlot = new SlotRef(null, "AGE");
+ IntLiteral ageLiteral = new IntLiteral(18);
+ BinaryPredicate agePred = new BinaryPredicate(Operator.GT, ageSlot,
ageLiteral);
+
+ CompoundPredicate innerComp = new
CompoundPredicate(CompoundPredicate.Operator.AND, namePred, agePred);
+ CompoundPredicate outerComp = new
CompoundPredicate(CompoundPredicate.Operator.OR, leftPred, innerComp);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, outerComp, mockTable);
+ Assert.assertEquals("((\"ID\" = 1) OR ((\"NAME\" = 'test') AND
(\"AGE\" > 18)))", result);
+ }
+
+ @Test
+ public void testComplexNestedCompoundPredicate() {
+
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // (ID = 1 OR NAME = 'test') AND (AGE > 18 OR DEPT = 'HR')
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate idPred = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ StringLiteral nameLiteral = new StringLiteral("test");
+ BinaryPredicate namePred = new BinaryPredicate(Operator.EQ, nameSlot,
nameLiteral);
+
+ SlotRef ageSlot = new SlotRef(null, "AGE");
+ IntLiteral ageLiteral = new IntLiteral(18);
+ BinaryPredicate agePred = new BinaryPredicate(Operator.GT, ageSlot,
ageLiteral);
+
+ SlotRef deptSlot = new SlotRef(null, "DEPT");
+ StringLiteral deptLiteral = new StringLiteral("HR");
+ BinaryPredicate deptPred = new BinaryPredicate(Operator.EQ, deptSlot,
deptLiteral);
+
+ CompoundPredicate leftComp = new
CompoundPredicate(CompoundPredicate.Operator.OR, idPred, namePred);
+ CompoundPredicate rightComp = new
CompoundPredicate(CompoundPredicate.Operator.OR, agePred, deptPred);
+ CompoundPredicate outerComp = new
CompoundPredicate(CompoundPredicate.Operator.AND, leftComp, rightComp);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, outerComp, mockTable);
+ Assert.assertEquals("(((\"ID\" = 1) OR (\"NAME\" = 'test')) AND
((\"AGE\" > 18) OR (\"DEPT\" = 'HR')))", result);
+ }
+
+ @Test
+ public void testDateLiteralOracle() throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ DateLiteral dateLiteral = new DateLiteral("2023-01-01 12:34:56",
Type.DATETIME);
+
+ SlotRef dateSlot = new SlotRef(null, "CREATE_TIME");
+ BinaryPredicate datePred = new BinaryPredicate(Operator.GE, dateSlot,
dateLiteral);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.ORACLE, datePred, mockTable);
+ Assert.assertTrue(result.contains("to_date('2023-01-01 12:34:56',
'yyyy-mm-dd hh24:mi:ss')"));
+ Assert.assertTrue(result.startsWith("\"CREATE_TIME\" >= "));
+ }
+
+ @Test
+ public void testDateLiteralTrino() throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ DateLiteral dateLiteral = new DateLiteral("2023-01-01 12:34:56",
Type.DATETIME);
+
+ SlotRef dateSlot = new SlotRef(null, "CREATE_TIME");
+ BinaryPredicate datePred = new BinaryPredicate(Operator.GE, dateSlot,
dateLiteral);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.TRINO, datePred, mockTable);
+ Assert.assertTrue(result.contains("timestamp '2023-01-01 12:34:56'"));
+ Assert.assertTrue(result.startsWith("\"CREATE_TIME\" >= "));
+ }
+
+ @Test
+ public void testDateLiteralCompoundPredicateOracle() throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // ID = 1 OR CREATE_TIME >= '2023-01-01'
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate idPred = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ DateLiteral dateLiteral = new DateLiteral("2023-01-01 12:34:56",
Type.DATETIME);
+
+ SlotRef dateSlot = new SlotRef(null, "CREATE_TIME");
+ BinaryPredicate datePred = new BinaryPredicate(Operator.GE, dateSlot,
dateLiteral);
+
+ CompoundPredicate compPred = new
CompoundPredicate(CompoundPredicate.Operator.OR, idPred, datePred);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.ORACLE, compPred, mockTable);
+ Assert.assertTrue(result.contains("to_date('2023-01-01 12:34:56',
'yyyy-mm-dd hh24:mi:ss')"));
+ Assert.assertTrue(result.contains("\"ID\" = 1"));
+ Assert.assertTrue(result.contains(" OR "));
+ }
+
+ @Test
+ public void testBoolLiteral() {
+ // 1 = 1 (true literal)
+ BoolLiteral boolLiteral = new BoolLiteral(true);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, boolLiteral, mockTable);
+ Assert.assertEquals("1 = 1", result);
+ }
+
+ @Test
+ public void testComplexPredicateWithDateComparisons() throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // (ID = 1 OR NAME = 'test') AND (CREATE_TIME >= '2023-01-01' AND
UPDATE_TIME <= '2023-12-31')
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate idPred = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ StringLiteral nameLiteral = new StringLiteral("test");
+ BinaryPredicate namePred = new BinaryPredicate(Operator.EQ, nameSlot,
nameLiteral);
+
+ DateLiteral startDateLiteral = new DateLiteral("2023-01-01 00:00:00",
Type.DATETIME);
+ DateLiteral endDateLiteral = new DateLiteral("2023-12-31 23:59:59",
Type.DATETIME);
+
+ SlotRef createTimeSlot = new SlotRef(null, "CREATE_TIME");
+ BinaryPredicate createTimePred = new BinaryPredicate(Operator.GE,
createTimeSlot, startDateLiteral);
+
+ SlotRef updateTimeSlot = new SlotRef(null, "UPDATE_TIME");
+ BinaryPredicate updateTimePred = new BinaryPredicate(Operator.LE,
updateTimeSlot, endDateLiteral);
+
+ CompoundPredicate leftComp = new
CompoundPredicate(CompoundPredicate.Operator.OR, idPred, namePred);
+ CompoundPredicate rightComp = new
CompoundPredicate(CompoundPredicate.Operator.AND, createTimePred,
updateTimePred);
+ CompoundPredicate outerComp = new
CompoundPredicate(CompoundPredicate.Operator.AND, leftComp, rightComp);
+
+ // Test for Oracle
+ String oracleResult =
JdbcScanNode.conjunctExprToString(TOdbcTableType.ORACLE, outerComp, mockTable);
+ Assert.assertTrue(oracleResult.contains("to_date('2023-01-01
00:00:00', 'yyyy-mm-dd hh24:mi:ss')"));
+ Assert.assertTrue(oracleResult.contains("to_date('2023-12-31
23:59:59', 'yyyy-mm-dd hh24:mi:ss')"));
+
+ // Test for Trino
+ String trinoResult =
JdbcScanNode.conjunctExprToString(TOdbcTableType.TRINO, outerComp, mockTable);
+ Assert.assertTrue(trinoResult.contains("timestamp '2023-01-01
00:00:00'"));
+ Assert.assertTrue(trinoResult.contains("timestamp '2023-12-31
23:59:59'"));
+
+ // Test for MySQL (no special date formatting)
+ String mysqlResult =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, outerComp, mockTable);
+ Assert.assertTrue(mysqlResult.contains("'2023-01-01 00:00:00'"));
+ Assert.assertTrue(mysqlResult.contains("'2023-12-31 23:59:59'"));
+ }
+
+ @Test
+ public void testDifferentComparisonOperators() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ SlotRef ageSlot = new SlotRef(null, "AGE");
+ IntLiteral ageLiteral = new IntLiteral(30);
+
+ // AGE < 30
+ BinaryPredicate ltPred = new BinaryPredicate(Operator.LT, ageSlot,
ageLiteral);
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, ltPred, mockTable);
+ Assert.assertEquals("(\"AGE\" < 30)", result);
+
+ // AGE <= 30
+ BinaryPredicate lePred = new BinaryPredicate(Operator.LE, ageSlot,
ageLiteral);
+ result = JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL,
lePred, mockTable);
+ Assert.assertEquals("(\"AGE\" <= 30)", result);
+
+ // AGE > 30
+ BinaryPredicate gtPred = new BinaryPredicate(Operator.GT, ageSlot,
ageLiteral);
+ result = JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL,
gtPred, mockTable);
+ Assert.assertEquals("(\"AGE\" > 30)", result);
+
+ // AGE >= 30
+ BinaryPredicate gePred = new BinaryPredicate(Operator.GE, ageSlot,
ageLiteral);
+ result = JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL,
gePred, mockTable);
+ Assert.assertEquals("(\"AGE\" >= 30)", result);
+
+ // AGE != 30
+ BinaryPredicate nePred = new BinaryPredicate(Operator.NE, ageSlot,
ageLiteral);
+ result = JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL,
nePred, mockTable);
+ Assert.assertEquals("(\"AGE\" != 30)", result);
+ }
+
+ @Test
+ public void testIsNullPredicates() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // NAME IS NULL
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ IsNullPredicate isNullPred = new IsNullPredicate(nameSlot, false);
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, isNullPred, mockTable);
+ Assert.assertEquals("\"NAME\" IS NULL", result);
+
+ // NAME IS NOT NULL
+ IsNullPredicate isNotNullPred = new IsNullPredicate(nameSlot, true);
+ result = JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL,
isNotNullPred, mockTable);
+ Assert.assertEquals("\"NAME\" IS NOT NULL", result);
+ }
+
+ @Test
+ public void testCompoundIsNullPredicates() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // ID = 1 AND NAME IS NULL
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate idPred = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ IsNullPredicate isNullPred = new IsNullPredicate(nameSlot, false);
+
+ CompoundPredicate compPred = new
CompoundPredicate(CompoundPredicate.Operator.AND, idPred, isNullPred);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, compPred, mockTable);
+ Assert.assertEquals("((\"ID\" = 1) AND \"NAME\" IS NULL)", result);
+ }
+
+ @Test
+ public void testLikePredicates() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // NAME LIKE 'test%'
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ StringLiteral pattern = new StringLiteral("test%");
+ LikePredicate likePred = new
LikePredicate(LikePredicate.Operator.LIKE, nameSlot, pattern);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, likePred, mockTable);
+ Assert.assertEquals("\"NAME\" LIKE 'test%'", result);
+
+ }
+
+ @Test
+ public void testFloatLiteral() {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // SALARY > 5000.50
+ SlotRef salarySlot = new SlotRef(null, "SALARY");
+ FloatLiteral floatLiteral = new FloatLiteral(5000.50);
+ BinaryPredicate salaryPred = new BinaryPredicate(Operator.GT,
salarySlot, floatLiteral);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, salaryPred, mockTable);
+ Assert.assertEquals("(\"SALARY\" > 5000.5)", result);
+ }
+
+ @Test
+ public void testVeryComplexNestedPredicate() {
+
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // (ID > 10 AND ID < 100) OR (NAME LIKE 'test%' AND (DEPT = 'HR' OR
SALARY > 5000.0))
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral id10 = new IntLiteral(10);
+ IntLiteral id100 = new IntLiteral(100);
+ BinaryPredicate idGt10 = new BinaryPredicate(Operator.GT, idSlot,
id10);
+ BinaryPredicate idLt100 = new BinaryPredicate(Operator.LT, idSlot,
id100);
+
+ CompoundPredicate idRange = new
CompoundPredicate(CompoundPredicate.Operator.AND, idGt10, idLt100);
+
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ StringLiteral pattern = new StringLiteral("test%");
+ LikePredicate nameLike = new
LikePredicate(LikePredicate.Operator.LIKE, nameSlot, pattern);
+
+ SlotRef deptSlot = new SlotRef(null, "DEPT");
+ StringLiteral deptLiteral = new StringLiteral("HR");
+ BinaryPredicate deptEq = new BinaryPredicate(Operator.EQ, deptSlot,
deptLiteral);
+
+ SlotRef salarySlot = new SlotRef(null, "SALARY");
+ FloatLiteral salaryLiteral = new FloatLiteral(5000.0);
+ BinaryPredicate salaryGt = new BinaryPredicate(Operator.GT,
salarySlot, salaryLiteral);
+
+ CompoundPredicate deptOrSalary = new
CompoundPredicate(CompoundPredicate.Operator.OR, deptEq, salaryGt);
+ CompoundPredicate nameAndDeptSalary = new
CompoundPredicate(CompoundPredicate.Operator.AND, nameLike, deptOrSalary);
+
+ CompoundPredicate finalPred = new
CompoundPredicate(CompoundPredicate.Operator.OR, idRange, nameAndDeptSalary);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, finalPred, mockTable);
+ Assert.assertEquals("(((\"ID\" > 10) AND (\"ID\" < 100)) OR (\"NAME\"
LIKE 'test%' AND ((\"DEPT\" = 'HR') OR (\"SALARY\" > 5000))))", result);
+ }
+
+ @Test
+ public void testTripleNestedCompoundPredicate() {
+
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // (ID = 1 OR (NAME = 'test' AND (AGE > 18 OR DEPT = 'HR')))
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate idPred = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ SlotRef nameSlot = new SlotRef(null, "NAME");
+ StringLiteral nameLiteral = new StringLiteral("test");
+ BinaryPredicate namePred = new BinaryPredicate(Operator.EQ, nameSlot,
nameLiteral);
+
+ SlotRef ageSlot = new SlotRef(null, "AGE");
+ IntLiteral ageLiteral = new IntLiteral(18);
+ BinaryPredicate agePred = new BinaryPredicate(Operator.GT, ageSlot,
ageLiteral);
+
+ SlotRef deptSlot = new SlotRef(null, "DEPT");
+ StringLiteral deptLiteral = new StringLiteral("HR");
+ BinaryPredicate deptPred = new BinaryPredicate(Operator.EQ, deptSlot,
deptLiteral);
+
+ CompoundPredicate innerComp = new
CompoundPredicate(CompoundPredicate.Operator.OR, agePred, deptPred);
+ CompoundPredicate middleComp = new
CompoundPredicate(CompoundPredicate.Operator.AND, namePred, innerComp);
+ CompoundPredicate outerComp = new
CompoundPredicate(CompoundPredicate.Operator.OR, idPred, middleComp);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, outerComp, mockTable);
+ Assert.assertEquals("((\"ID\" = 1) OR ((\"NAME\" = 'test') AND
((\"AGE\" > 18) OR (\"DEPT\" = 'HR'))))", result);
+ }
+}
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 1612fc30ab3..d2806d74ab6 100644
Binary files
a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out and
b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
differ
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 d2b3669ab45..4dd2607d484 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
@@ -136,6 +136,70 @@ suite("test_oracle_jdbc_catalog",
"p0,external,oracle,external_docker,external_d
order_qt_date6 """ select * from TEST_DATE where (T1 < '2022-01-22
00:00:00' or T1 > '2022-01-20 00:00:00') and (T1 < '2022-01-23 00:00:00' or T1
> '2022-01-19 00:00:00'); """
order_qt_date7 """select * from TEST_TIMESTAMP where T2 <
str_to_date('2020-12-21 12:34:56', '%Y-%m-%d %H:%i:%s');"""
+ // Test compound predicates processing - Multiple test cases for NOT,
AND, OR combinations and nesting
+ order_qt_compound1 """select * from TEST_TIMESTAMP where T1 is not
null order by ID;"""
+ order_qt_compound2 """select * from TEST_TIMESTAMP where NOT (T1 is
null) order by ID;"""
+ order_qt_compound3 """select * from TEST_TIMESTAMP where T1 is not
null AND T1 < '2013-02-01 00:00:00' order by ID;"""
+ order_qt_compound4 """select * from TEST_TIMESTAMP where T1 is not
null OR T2 is not null order by ID;"""
+ order_qt_compound5 """select * from TEST_TIMESTAMP where NOT (T1 is
null OR T2 is null) order by ID;"""
+ order_qt_compound6 """select * from TEST_TIMESTAMP where (T1 is not
null AND T1 < '2013-02-01 00:00:00') OR (T2 is not null AND T2 > '2019-01-01
00:00:00') order by ID;"""
+ order_qt_compound7 """select * from TEST_TIMESTAMP where NOT ((T1 is
null AND T2 is null) OR (T3 is null AND T4 is null)) order by ID;"""
+
+ // Test IN operator with date/time values
+ order_qt_in_date1 """select * from TEST_TIMESTAMP where T1 IN
('2013-01-21 05:23:01', '2013-11-12 20:32:56') order by ID;"""
+ order_qt_in_date2 """select * from TEST_TIMESTAMP where T1 NOT IN
('2013-01-21 05:23:01') order by ID;"""
+
+ // Test date/time comparisons and combinations
+ order_qt_date_complex1 """select * from TEST_TIMESTAMP where T1 <
'2013-05-01 00:00:00' AND T1 > '2013-01-01 00:00:00' order by ID;"""
+ order_qt_date_complex2 """select * from TEST_TIMESTAMP where T1
BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59' order by ID;"""
+ order_qt_date_complex3 """select * from TEST_TIMESTAMP where T2 >=
'2019-01-01 00:00:00' OR T3 >= '2019-01-01 00:00:00' order by ID;"""
+ order_qt_date_complex4 """select * from TEST_TIMESTAMP where NOT (T1 <
'2013-01-01 00:00:00' OR T1 > '2014-01-01 00:00:00') order by ID;"""
+
+ // Test NOT operator in date/time expressions
+ order_qt_time_not1 """select * from TEST_TIMESTAMP where NOT (T1 >
'2013-06-01 00:00:00') order by ID;"""
+ order_qt_time_not2 """select * from TEST_TIMESTAMP where NOT (T1
BETWEEN '2013-05-01 00:00:00' AND '2013-12-31 23:59:59') order by ID;"""
+ order_qt_time_not3 """select * from TEST_TIMESTAMP where T1 IS NOT
NULL AND NOT (T1 > '2013-10-01 00:00:00') order by ID;"""
+
+ // Test complex date/time comparisons and multiple time field
combinations
+ order_qt_time_multi1 """select * from TEST_TIMESTAMP where (T1 <
'2013-06-01 00:00:00' OR T2 > '2019-01-01 00:00:00') order by ID;"""
+ order_qt_time_multi2 """select * from TEST_TIMESTAMP where (T1 IS NOT
NULL AND T1 < '2013-06-01 00:00:00') OR (T2 IS NOT NULL AND T2 > '2019-01-01
00:00:00') order by ID;"""
+ order_qt_time_multi3 """select * from TEST_TIMESTAMP where NOT ((T1 IS
NULL) OR (T2 IS NULL AND T3 IS NULL)) order by ID;"""
+
+ // Test date comparisons using to_date function
+ order_qt_time_func1 """select * from TEST_TIMESTAMP where T1 >
str_to_date('2013-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') order by ID;"""
+ order_qt_time_func2 """select * from TEST_TIMESTAMP where T1 BETWEEN
str_to_date('2013-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') AND
str_to_date('2013-12-31 23:59:59', '%Y-%m-%d %H:%i:%s') order by ID;"""
+ order_qt_time_func3 """select * from TEST_TIMESTAMP where NOT (T1 <
str_to_date('2013-01-01 00:00:00', '%Y-%m-%d %H:%i:%s')) order by ID;"""
+
+ // Test complex AND/OR/NOT combinations with date/time comparisons
+ order_qt_time_complex1 """select * from TEST_TIMESTAMP where (T1 >
'2013-01-01 00:00:00' AND T1 < '2013-12-31 23:59:59') OR (T2 > '2018-01-01
00:00:00' AND T2 < '2020-01-01 00:00:00') order by ID;"""
+ order_qt_time_complex2 """select * from TEST_TIMESTAMP where NOT ((T1
< '2013-01-01 00:00:00' OR T1 > '2014-01-01 00:00:00') AND (T2 IS NULL OR T2 <
'2018-01-01 00:00:00')) order by ID;"""
+ order_qt_time_complex3 """select * from TEST_TIMESTAMP where (T1 IN
('2013-01-21 05:23:01', '2013-11-12 20:32:56') OR T2 IS NOT NULL) AND NOT (T3
IS NULL AND T4 IS NULL) order by ID;"""
+
+ // Test deeply nested date/time conditions
+ order_qt_nested1 """select * from TEST_TIMESTAMP where NOT (NOT (T1 >
'2013-01-01 00:00:00')) order by ID;"""
+ order_qt_nested2 """select * from TEST_TIMESTAMP where NOT ((T1 IS
NULL OR T1 > '2013-10-01 00:00:00') AND (T2 IS NULL OR T2 < '2019-01-01
00:00:00')) order by ID;"""
+
+ // Test date format edge cases
+ order_qt_edge1 """select * from TEST_TIMESTAMP where T1 = '2013-01-21
05:23:01' order by ID;"""
+ order_qt_edge2 """select * from TEST_TIMESTAMP where T2 = '2019-11-12
20:33:57.999' order by ID;"""
+ order_qt_edge3 """select * from TEST_TIMESTAMP where T3 = '2019-11-12
20:33:57.999998' order by ID;"""
+
+ // Test CURDATE() function and CAST combinations
+ order_qt_curdate1 """select * from TEST_TIMESTAMP where ID = 1 OR (T2
>= CAST(CURDATE() AS DATETIME) AND T1 >= CAST(CURDATE() AS DATETIME) AND T3 NOT
IN (CAST(CURDATE() AS DATETIME))) order by ID;"""
+ order_qt_curdate2 """select * from TEST_TIMESTAMP where T1 >=
CAST(CURDATE() AS DATETIME) order by ID;"""
+ order_qt_curdate3 """select * from TEST_TIMESTAMP where T1 IN
(CAST(CURDATE() AS DATETIME), CAST(CURDATE() + INTERVAL 1 DAY AS DATETIME))
order by ID;"""
+ order_qt_curdate4 """select * from TEST_TIMESTAMP where T2 NOT IN
(CAST(CURDATE() AS DATETIME), CAST(CURDATE() + INTERVAL 1 DAY AS DATETIME))
order by ID;"""
+
+ // Test complex nested conditions with date functions
+ order_qt_complex1 """select * from TEST_TIMESTAMP where (T1 <
CAST(CURDATE() AS DATETIME) OR T2 > CAST(CURDATE() AS DATETIME)) AND NOT (T3 =
CAST(CURDATE() AS DATETIME)) order by ID;"""
+ order_qt_complex2 """select * from TEST_TIMESTAMP where NOT ((T1 <
CAST(CURDATE() AS DATETIME) AND T2 IS NULL) OR (T3 > CAST(CURDATE() AS
DATETIME))) order by ID;"""
+
+ // Test CAST, CURDATE with multiple time conditions
+ order_qt_complex3 """select * from TEST_TIMESTAMP where (ID = 1 AND T1
>= CAST(CURDATE() AS DATETIME)) OR (ID = 2 AND T2 BETWEEN CAST(CURDATE() AS
DATETIME) AND CAST(CURDATE() + INTERVAL 7 DAY AS DATETIME)) order by ID;"""
+
+ // Test mixed date functions and string dates
+ order_qt_mixed """select * from TEST_TIMESTAMP where T1 >=
CAST(CURDATE() AS DATETIME) AND T2 < '2023-01-01 00:00:00' order by ID;"""
+
// test nvl
explain {
sql("SELECT * FROM STUDENT WHERE nvl(score, 0) < 95;")
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
