This is an automated email from the ASF dual-hosted git repository.
zhoujinsong pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/amoro.git
The following commit(s) were added to refs/heads/master by this push:
new cba754f59 [AMORO-4220] [Improvement] Support BETWEEN and reversed
comparison SQL filters in ExpressionUtil. (#4221)
cba754f59 is described below
commit cba754f5994341e5a9da445c67843e4469f20217
Author: slfan1989 <[email protected]>
AuthorDate: Tue May 19 11:11:08 2026 +0800
[AMORO-4220] [Improvement] Support BETWEEN and reversed comparison SQL
filters in ExpressionUtil. (#4221)
[AMORO-4420] [Improvement] Support BETWEEN and reversed comparison SQL
filters in ExpressionUtil.
---
.../org/apache/amoro/utils/ExpressionUtil.java | 98 +++++++++++++++++++---
.../org/apache/amoro/utils/TestExpressionUtil.java | 43 ++++++++++
2 files changed, 128 insertions(+), 13 deletions(-)
diff --git
a/amoro-format-iceberg/src/main/java/org/apache/amoro/utils/ExpressionUtil.java
b/amoro-format-iceberg/src/main/java/org/apache/amoro/utils/ExpressionUtil.java
index 370a45532..0bda777ac 100644
---
a/amoro-format-iceberg/src/main/java/org/apache/amoro/utils/ExpressionUtil.java
+++
b/amoro-format-iceberg/src/main/java/org/apache/amoro/utils/ExpressionUtil.java
@@ -25,6 +25,7 @@ import net.sf.jsqlparser.expression.NotExpression;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
+import net.sf.jsqlparser.expression.operators.relational.Between;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
@@ -142,29 +143,38 @@ public class ExpressionUtil {
: Expressions.isNull(column.name());
} else if (whereExpr instanceof EqualsTo) {
EqualsTo eq = (EqualsTo) whereExpr;
- Types.NestedField column = getColumn(eq.getLeftExpression(),
tableColumns);
- return Expressions.equal(column.name(),
getValue(eq.getRightExpression(), column));
+ return convertComparisonExpression(
+ eq.getLeftExpression(), eq.getRightExpression(), tableColumns,
ComparisonOperator.EQ);
} else if (whereExpr instanceof NotEqualsTo) {
NotEqualsTo ne = (NotEqualsTo) whereExpr;
- Types.NestedField column = getColumn(ne.getLeftExpression(),
tableColumns);
- return Expressions.notEqual(column.name(),
getValue(ne.getRightExpression(), column));
+ return convertComparisonExpression(
+ ne.getLeftExpression(), ne.getRightExpression(), tableColumns,
ComparisonOperator.NE);
} else if (whereExpr instanceof GreaterThan) {
GreaterThan gt = (GreaterThan) whereExpr;
- Types.NestedField column = getColumn(gt.getLeftExpression(),
tableColumns);
- return Expressions.greaterThan(column.name(),
getValue(gt.getRightExpression(), column));
+ return convertComparisonExpression(
+ gt.getLeftExpression(), gt.getRightExpression(), tableColumns,
ComparisonOperator.GT);
} else if (whereExpr instanceof GreaterThanEquals) {
GreaterThanEquals ge = (GreaterThanEquals) whereExpr;
- Types.NestedField column = getColumn(ge.getLeftExpression(),
tableColumns);
- return Expressions.greaterThanOrEqual(
- column.name(), getValue(ge.getRightExpression(), column));
+ return convertComparisonExpression(
+ ge.getLeftExpression(), ge.getRightExpression(), tableColumns,
ComparisonOperator.GE);
} else if (whereExpr instanceof MinorThan) {
MinorThan lt = (MinorThan) whereExpr;
- Types.NestedField column = getColumn(lt.getLeftExpression(),
tableColumns);
- return Expressions.lessThan(column.name(),
getValue(lt.getRightExpression(), column));
+ return convertComparisonExpression(
+ lt.getLeftExpression(), lt.getRightExpression(), tableColumns,
ComparisonOperator.LT);
} else if (whereExpr instanceof MinorThanEquals) {
MinorThanEquals le = (MinorThanEquals) whereExpr;
- Types.NestedField column = getColumn(le.getLeftExpression(),
tableColumns);
- return Expressions.lessThanOrEqual(column.name(),
getValue(le.getRightExpression(), column));
+ return convertComparisonExpression(
+ le.getLeftExpression(), le.getRightExpression(), tableColumns,
ComparisonOperator.LE);
+ } else if (whereExpr instanceof Between) {
+ Between between = (Between) whereExpr;
+ Types.NestedField column = getColumn(between.getLeftExpression(),
tableColumns);
+ Expression betweenExpression =
+ Expressions.and(
+ Expressions.greaterThanOrEqual(
+ column.name(), getValue(between.getBetweenExpressionStart(),
column)),
+ Expressions.lessThanOrEqual(
+ column.name(), getValue(between.getBetweenExpressionEnd(),
column)));
+ return between.isNot() ? Expressions.not(betweenExpression) :
betweenExpression;
} else if (whereExpr instanceof InExpression) {
InExpression in = (InExpression) whereExpr;
Types.NestedField column = getColumn(in.getLeftExpression(),
tableColumns);
@@ -197,6 +207,42 @@ public class ExpressionUtil {
throw new UnsupportedOperationException("Unsupported expression: " +
whereExpr);
}
+ private static Expression convertComparisonExpression(
+ net.sf.jsqlparser.expression.Expression leftExpr,
+ net.sf.jsqlparser.expression.Expression rightExpr,
+ List<Types.NestedField> tableColumns,
+ ComparisonOperator operator) {
+ if (leftExpr instanceof Column) {
+ Types.NestedField column = getColumn(leftExpr, tableColumns);
+ return convertColumnComparison(column, getValue(rightExpr, column),
operator);
+ } else if (rightExpr instanceof Column) {
+ Types.NestedField column = getColumn(rightExpr, tableColumns);
+ return convertColumnComparison(column, getValue(leftExpr, column),
operator.reverse());
+ }
+
+ throw new IllegalArgumentException(
+ "Expected at least one column reference, got: " + leftExpr + " and " +
rightExpr);
+ }
+
+ private static Expression convertColumnComparison(
+ Types.NestedField column, Object value, ComparisonOperator operator) {
+ switch (operator) {
+ case EQ:
+ return Expressions.equal(column.name(), value);
+ case NE:
+ return Expressions.notEqual(column.name(), value);
+ case GT:
+ return Expressions.greaterThan(column.name(), value);
+ case GE:
+ return Expressions.greaterThanOrEqual(column.name(), value);
+ case LT:
+ return Expressions.lessThan(column.name(), value);
+ case LE:
+ return Expressions.lessThanOrEqual(column.name(), value);
+ }
+ throw new UnsupportedOperationException("Unsupported comparison operator:
" + operator);
+ }
+
private static Types.NestedField getColumn(
net.sf.jsqlparser.expression.Expression expr, List<Types.NestedField>
tableColumns) {
if (expr instanceof Column) {
@@ -274,4 +320,30 @@ public class ExpressionUtil {
}
return timestampStr;
}
+
+ private enum ComparisonOperator {
+ EQ,
+ NE,
+ GT,
+ GE,
+ LT,
+ LE;
+
+ private ComparisonOperator reverse() {
+ switch (this) {
+ case GT:
+ return LT;
+ case GE:
+ return LE;
+ case LT:
+ return GT;
+ case LE:
+ return GE;
+ case EQ:
+ case NE:
+ return this;
+ }
+ throw new UnsupportedOperationException("Unsupported comparison
operator: " + this);
+ }
+ }
}
diff --git
a/amoro-format-iceberg/src/test/java/org/apache/amoro/utils/TestExpressionUtil.java
b/amoro-format-iceberg/src/test/java/org/apache/amoro/utils/TestExpressionUtil.java
index 03707da7b..f5729b937 100644
---
a/amoro-format-iceberg/src/test/java/org/apache/amoro/utils/TestExpressionUtil.java
+++
b/amoro-format-iceberg/src/test/java/org/apache/amoro/utils/TestExpressionUtil.java
@@ -77,6 +77,49 @@ public class TestExpressionUtil {
epochMicroSecond, "'2022-01-01T12:12:12'",
Types.TimestampType.withoutZone());
}
+ @Test
+ public void testConvertSqlToIcebergExpressionWithBetween() {
+ List<Types.NestedField> fields = new ArrayList<>();
+ fields.add(Types.NestedField.optional(1, "column_a",
Types.IntegerType.get()));
+
+ assertEqualExpressions(
+ Expressions.and(
+ Expressions.greaterThanOrEqual("column_a", 1),
+ Expressions.lessThanOrEqual("column_a", 10)),
+ convertSqlFilterToIcebergExpression("column_a BETWEEN 1 AND 10",
fields));
+ assertEqualExpressions(
+ Expressions.not(
+ Expressions.and(
+ Expressions.greaterThanOrEqual("column_a", 1),
+ Expressions.lessThanOrEqual("column_a", 10))),
+ convertSqlFilterToIcebergExpression("column_a NOT BETWEEN 1 AND 10",
fields));
+ }
+
+ @Test
+ public void testConvertSqlToIcebergExpressionWithLiteralOnLeftComparison() {
+ List<Types.NestedField> fields = new ArrayList<>();
+ fields.add(Types.NestedField.optional(1, "column_a",
Types.IntegerType.get()));
+
+ assertEqualExpressions(
+ Expressions.lessThan("column_a", 1),
+ convertSqlFilterToIcebergExpression("1 > column_a", fields));
+ assertEqualExpressions(
+ Expressions.lessThanOrEqual("column_a", 1),
+ convertSqlFilterToIcebergExpression("1 >= column_a", fields));
+ assertEqualExpressions(
+ Expressions.greaterThan("column_a", 1),
+ convertSqlFilterToIcebergExpression("1 < column_a", fields));
+ assertEqualExpressions(
+ Expressions.greaterThanOrEqual("column_a", 1),
+ convertSqlFilterToIcebergExpression("1 <= column_a", fields));
+ assertEqualExpressions(
+ Expressions.equal("column_a", 1),
+ convertSqlFilterToIcebergExpression("1 = column_a", fields));
+ assertEqualExpressions(
+ Expressions.notEqual("column_a", 1),
+ convertSqlFilterToIcebergExpression("1 != column_a", fields));
+ }
+
public <T> void testConvertSqlToIcebergExpressionByType(T exprValue, String
sqlValue, Type type) {
List<Types.NestedField> fields = new ArrayList<>();
fields.add(Types.NestedField.optional(1, "column_a", type));
