This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 8f1dce91b6 [CALCITE-6966] Change JoinConditionOrExpansionRule name and
accept more predicates that will allow the expansion to be performed
8f1dce91b6 is described below
commit 8f1dce91b61309db67355fa62372cff45d9803e9
Author: Zhen Chen <[email protected]>
AuthorDate: Sun Apr 20 09:50:32 2025 +0800
[CALCITE-6966] Change JoinConditionOrExpansionRule name and accept more
predicates that will allow the expansion to be performed
---
.../org/apache/calcite/rel/rules/CoreRules.java | 4 +-
...nsionRule.java => JoinExpandOrToUnionRule.java} | 152 +++++++++++++++------
.../java/org/apache/calcite/test/JdbcTest.java | 10 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 35 ++++-
.../org/apache/calcite/test/RelOptRulesTest.xml | 45 ++++++
5 files changed, 195 insertions(+), 51 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index 76b3fb5e13..f7aa685e87 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -565,8 +565,8 @@ private CoreRules() {}
FilterJoinRule.JoinConditionPushRule.JoinConditionPushRuleConfig.DEFAULT.toRule();
/** Rule that transforms a join with OR conditions into a UNION ALL of
multiple joins. */
- public static final JoinConditionOrExpansionRule
JOIN_CONDITION_OR_EXPANSION_RULE =
- JoinConditionOrExpansionRule.Config.DEFAULT.toRule();
+ public static final JoinExpandOrToUnionRule JOIN_EXPAND_OR_TO_UNION_RULE =
+ JoinExpandOrToUnionRule.Config.DEFAULT.toRule();
/** Rule to add a semi-join into a {@link Join}. */
public static final JoinAddRedundantSemiJoinRule
JOIN_ADD_REDUNDANT_SEMI_JOIN =
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/JoinConditionOrExpansionRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/JoinExpandOrToUnionRule.java
similarity index 76%
rename from
core/src/main/java/org/apache/calcite/rel/rules/JoinConditionOrExpansionRule.java
rename to
core/src/main/java/org/apache/calcite/rel/rules/JoinExpandOrToUnionRule.java
index a5e2d670e5..eafb92a872 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/JoinConditionOrExpansionRule.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/JoinExpandOrToUnionRule.java
@@ -27,6 +27,7 @@
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.rex.RexVisitorImpl;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.tools.RelBuilder;
@@ -50,14 +51,17 @@
* and other examples for other kinds of joins
* can be found in the code below.
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── Join[OR(t1.id=t2.id, t1.age=t2.age), inner]
* ├── TableScan[t1]
* └── TableScan[t2]
+ * }</pre>
*
* <p>into
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── UnionAll
* ├── Join[t1.id=t2.id, inner]
* │ ├── TableScan[t1]
@@ -65,15 +69,15 @@
* └── Join[t1.age=t2.age AND t1.id≠t2.id, inner]
* ├─── TableScan[t1]
* └─── TableScan[t2]
- *
+ * }</pre>
*/
@Value.Enclosing
-public class JoinConditionOrExpansionRule
- extends RelRule<JoinConditionOrExpansionRule.Config>
+public class JoinExpandOrToUnionRule
+ extends RelRule<JoinExpandOrToUnionRule.Config>
implements TransformationRule {
- /** Creates an JoinConditionExpansionOrRule. */
- protected JoinConditionOrExpansionRule(Config config) {
+ /** Creates an JoinExpandOrToUnionRule. */
+ protected JoinExpandOrToUnionRule(Config config) {
super(config);
}
@@ -109,6 +113,10 @@ protected JoinConditionOrExpansionRule(Config config) {
default:
return;
}
+ if (expanded instanceof Join
+ && ((Join) expanded).getCondition().equals(join.getCondition())) {
+ return;
+ }
call.transformTo(expanded);
}
@@ -140,44 +148,99 @@ private List<RexNode> splitCond(Join join) {
}
private boolean isValidCond(RexNode node, int leftFieldCount) {
- if (!(node instanceof RexCall)) {
+ boolean hasJoinKeyCond = false;
+ List<RexNode> conds = RelOptUtil.conjunctions(node);
+ for (RexNode cond : conds) {
+ // When components of the "call" are predicates that contain
+ // equality (when the above conditions are met), that are
+ // single-side (refer to only on of the collections joined),
+ // or which are constant, they will all trigger the expansion.
+ if (!doesNotReferToBothInputs(cond, leftFieldCount)) {
+ return false;
+ }
+
+ if (RexUtil.SubQueryFinder.find(cond) != null
+ || RexUtil.containsCorrelation(cond)) {
+ // The "call" does not support sub-queries or correlation yet
+ return false;
+ }
+
+ if (cond instanceof RexCall) {
+ RexCall call = (RexCall) cond;
+ // Checks if the "call" is valid for use as a join key.
+ if (isEquiJoinCond(call, leftFieldCount)) {
+ hasJoinKeyCond = true;
+ }
+ }
+ }
+ return hasJoinKeyCond;
+ }
+
+ private boolean isEquiJoinCond(RexCall call, int leftFieldCount) {
+ if (call.getKind() != SqlKind.EQUALS
+ && call.getKind() != SqlKind.IS_NOT_DISTINCT_FROM) {
return false;
}
- RexCall call = (RexCall) node;
- SqlKind kind = call.getKind();
- switch (kind) {
- case EQUALS:
- case IS_NOT_DISTINCT_FROM:
- RexNode left = call.getOperands().get(0);
- RexNode right = call.getOperands().get(1);
-
- if (left instanceof RexInputRef && right instanceof RexInputRef) {
- RexInputRef leftRef = (RexInputRef) left;
- RexInputRef rightRef = (RexInputRef) right;
- return (leftRef.getIndex() < leftFieldCount && rightRef.getIndex() >=
leftFieldCount)
- || (leftRef.getIndex() >= leftFieldCount && rightRef.getIndex() <
leftFieldCount);
+ RexNode left = call.getOperands().get(0);
+ RexNode right = call.getOperands().get(1);
+
+ if (left instanceof RexInputRef && right instanceof RexInputRef) {
+ int leftIndex = ((RexInputRef) left).getIndex();
+ int rightIndex = ((RexInputRef) right).getIndex();
+
+ return (leftIndex < leftFieldCount && rightIndex >= leftFieldCount)
+ || (rightIndex < leftFieldCount && leftIndex >= leftFieldCount);
+ }
+ return false;
+ }
+
+ private boolean doesNotReferToBothInputs(RexNode rex, int leftFieldCount) {
+ RexInputRefCounter counter = new RexInputRefCounter(leftFieldCount);
+ rex.accept(counter);
+ return counter.doesNotReferToBothInputs();
+ }
+
+ /**
+ * Counts the number of InputRefs in a RexNode expression. */
+ private static class RexInputRefCounter extends RexVisitorImpl<Void> {
+ private int leftFieldCount;
+ public int leftInputRefCount = 0;
+ public int rightInputRefCount = 0;
+
+ RexInputRefCounter(int leftFieldCount) {
+ super(true);
+ this.leftFieldCount = leftFieldCount;
+ }
+
+ @Override public Void visitInputRef(RexInputRef inputRef) {
+ if (inputRef.getIndex() < leftFieldCount) {
+ leftFieldCount++;
+ } else {
+ rightInputRefCount++;
}
- return false;
- case AND:
- return call.getOperands().stream()
- .allMatch(op -> isValidCond(op, leftFieldCount));
- default:
- return false;
+ return null;
+ }
+
+ public boolean doesNotReferToBothInputs() {
+ return leftInputRefCount == 0 || rightInputRefCount == 0;
}
}
/**
* This method will make the following conversions.
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── Join[OR(t1.id=t2.id, t1.age=t2.age), left]
* ├── TableScan[t1]
* └── TableScan[t2]
+ *}</pre>
*
* <p>into
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── UnionAll
* ├── Join[t1.id=t2.id, inner]
* │ ├── TableScan[t1]
@@ -191,6 +254,7 @@ private boolean isValidCond(RexNode node, int
leftFieldCount) {
* │ ├── TableScan[t1]
* │ └── TableScan[t2]
* └── TableScan[t2]
+ * }</pre>
*/
private RelNode expandLeftOrRightJoin(Join join, boolean isLeftJoin,
RelBuilder relBuilder) {
@@ -212,14 +276,17 @@ private List<RelNode>
expandLeftOrRightJoinToRelNodes(Join join, List<RexNode> o
/**
* This method will make the following conversions.
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── Join[OR(t1.id=t2.id, t1.age=t2.age), full]
* ├── TableScan[t1]
* └── TableScan[t2]
+ * }</pre>
*
* <p>into
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── UnionAll
* ├── Join[t1.id=t2.id, inner]
* │ ├── TableScan[t1]
@@ -239,6 +306,7 @@ private List<RelNode> expandLeftOrRightJoinToRelNodes(Join
join, List<RexNode> o
* │ ├── TableScan[t2]
* │ └── TableScan[t1]
* └── TableScan[t1]
+ * }</pre>
*/
private RelNode expandFullJoin(Join join, RelBuilder relBuilder) {
List<RexNode> orConds = splitCond(join);
@@ -265,14 +333,17 @@ private RelNode expandFullJoin(Join join, RelBuilder
relBuilder) {
/**
* This method will make the following conversions.
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── Join[OR(t1.id=t2.id, t1.age=t2.age), inner]
* ├── TableScan[t1]
* └── TableScan[t2]
+ * }</pre>
*
* <p>into
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── UnionAll
* ├── Join[t1.id=t2.id, inner]
* │ ├── TableScan[t1]
@@ -280,6 +351,7 @@ private RelNode expandFullJoin(Join join, RelBuilder
relBuilder) {
* └── Join[t1.age=t2.age AND t1.id≠t2.id, inner]
* ├─── TableScan[t1]
* └─── TableScan[t2]
+ * }</pre>
*/
private RelNode expandInnerJoin(Join join, RelBuilder relBuilder) {
List<RexNode> orConds = splitCond(join);
@@ -310,18 +382,22 @@ private List<RelNode> expandInnerJoinToRelNodes(Join
join, List<RexNode> orConds
/**
* This method will make the following conversions.
*
- * <p>Project[*]
+ * <pre>{@code
+ * Project[*]
* └── Join[OR(id=id0, age=age0), anti]
* ├── TableScan[tbl]
* └── TableScan[tbl]
+ * }</pre>
*
* <p>into
*
- * <p>HashJoin[id=id0, anti]
+ * <pre>{@code
+ * HashJoin[id=id0, anti]
* ├── HashJoin[age=age0, anti]
* │ ├── TableScan[tbl]
* │ └── TableScan[tbl]
* └── TableScan[tbl]
+ * }</pre>
*/
private RelNode expandAntiJoin(Join join, RelBuilder relBuilder) {
List<RexNode> orConds = splitCond(join);
@@ -369,11 +445,11 @@ private RelNode expandAntiJoinToRelNode(Join join,
List<RexNode> orConds,
/** Rule configuration. */
@Value.Immutable
public interface Config extends RelRule.Config {
- Config DEFAULT = ImmutableJoinConditionOrExpansionRule.Config.of()
+ Config DEFAULT = ImmutableJoinExpandOrToUnionRule.Config.of()
.withOperandFor(Join.class);
- @Override default JoinConditionOrExpansionRule toRule() {
- return new JoinConditionOrExpansionRule(this);
+ @Override default JoinExpandOrToUnionRule toRule() {
+ return new JoinExpandOrToUnionRule(this);
}
/** Defines an operand tree for the given classes. */
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 95b1266027..ddad5eead2 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -1999,7 +1999,7 @@ private void checkResultSetMetaData(Connection
connection, String sql)
CalciteAssert.hr()
.query(sql)
.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
- planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.addRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE);
planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
})
.returnsUnordered(returns);
@@ -2039,7 +2039,7 @@ private void checkResultSetMetaData(Connection
connection, String sql)
CalciteAssert.hr()
.query(sql)
.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
- planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.addRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE);
planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
})
.returnsUnordered(returns);
@@ -2072,7 +2072,7 @@ private void checkResultSetMetaData(Connection
connection, String sql)
CalciteAssert.hr()
.query(sql)
.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
- planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.addRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE);
planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
})
.returnsUnordered(returns);
@@ -2110,7 +2110,7 @@ private void checkResultSetMetaData(Connection
connection, String sql)
CalciteAssert.hr()
.query(sql)
.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
- planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.addRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE);
planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
})
.returnsUnordered(returns);
@@ -2144,7 +2144,7 @@ private void checkResultSetMetaData(Connection
connection, String sql)
CalciteAssert.hr()
.query(sql)
.withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
- planner.addRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE);
+ planner.addRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE);
planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
})
.returnsUnordered(returns);
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 7345adb00d..1718d2e788 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -9054,7 +9054,30 @@ private RelOptFixture spatial(String sql) {
String sql = "select *\n"
+ "from EMP as p1\n"
+ "inner join EMP as p2 on p1.empno = p2.empno or p1.mgr = p2.mgr";
- sql(sql).withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ sql(sql).withRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE)
+ .check();
+ }
+
+ @Test void testJoinConditionOrExpansionRule2() {
+ String sql = "select * from empnullables as t1 inner join empnullables as
t2\n"
+ + "on (t1.empno = t2.empno\n"
+ + "and t1.job = 'Job1'\n"
+ + "and t1.ename in ('a', 'bb', 'cc')\n"
+ + "and t1.sal > 120 and t1.sal < 3000\n"
+ + "and t1.mgr = t1.comm)\n"
+ + "or\n"
+ + "(t1.deptno = t2.deptno\n"
+ + "and t2.job = 'Job2'\n"
+ + "and t2.ename in ('a', 'bb', 'cc')\n"
+ + "and t2.sal > 110 and t2.sal < 3000\n"
+ + "and t1.mgr + 10 < ln(15))\n"
+ + "or\n"
+ + "(t1.ename = 'Jensen'\n"
+ + "and t2.comm > 10)\n"
+ + "or\n"
+ + "t1.mgr between 10.0 and 20\n";
+
+ sql(sql).withRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE)
.check();
}
@@ -9066,7 +9089,7 @@ private RelOptFixture spatial(String sql) {
+ "from EMP as p1\n"
+ "inner join EMP as p2 on p1.mgr < p2.mgr or\n"
+ "p1.empno = p2.empno or p1.sal < 0 or ln(p1.sal) < 10";
- sql(sql).withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ sql(sql).withRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE)
.check();
}
@@ -9077,7 +9100,7 @@ private RelOptFixture spatial(String sql) {
String sql = "select *\n"
+ "from EMP as p1\n"
+ "right join DEPT as p2 on p1.empno = p2.deptno or p1.ename <
p2.name";
- sql(sql).withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ sql(sql).withRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE)
.check();
}
@@ -9088,7 +9111,7 @@ private RelOptFixture spatial(String sql) {
String sql = "select *\n"
+ "from EMP as p1\n"
+ "left join EMP as p2 on p1.empno = p2.empno or p1.sal = p2.sal";
- sql(sql).withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ sql(sql).withRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE)
.check();
}
@@ -9109,7 +9132,7 @@ private RelOptFixture spatial(String sql) {
b.field(2, 1, "JOB"))))
.build();
relFn(relFn)
- .withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ .withRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE)
.check();
}
@@ -9130,7 +9153,7 @@ private RelOptFixture spatial(String sql) {
b.field(2, 1, "JOB"))))
.build();
relFn(relFn)
- .withRule(CoreRules.JOIN_CONDITION_OR_EXPANSION_RULE)
+ .withRule(CoreRules.JOIN_EXPAND_OR_TO_UNION_RULE)
.check();
}
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 7f283af937..f5d3ede283 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -6752,6 +6752,51 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalJoin(condition=[AND(=($3, $12), <>($0, $9))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinConditionOrExpansionRule2">
+ <Resource name="sql">
+ <![CDATA[select * from empnullables as t1 inner join empnullables as t2
+on (t1.empno = t2.empno
+and t1.job = 'Job1'
+and t1.ename in ('a', 'bb', 'cc')
+and t1.sal > 120 and t1.sal < 3000
+and t1.mgr = t1.comm)
+or
+(t1.deptno = t2.deptno
+and t2.job = 'Job2'
+and t2.ename in ('a', 'bb', 'cc')
+and t2.sal > 110 and t2.sal < 3000
+and t1.mgr + 10 < ln(15))
+or
+(t1.ename = 'Jensen'
+and t2.comm > 10)
+or
+t1.mgr between 10.0 and 20
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalJoin(condition=[OR(AND(=($0, $9), =($2, 'Job1'), SEARCH($1,
Sarg['a':VARCHAR(20), 'bb':VARCHAR(20), 'cc':VARCHAR(20)]:VARCHAR(20)),
SEARCH($5, Sarg[(120..3000)]), =($3, $6)), AND(=($7, $16), =($11, 'Job2'),
SEARCH($10, Sarg['a':VARCHAR(20), 'bb':VARCHAR(20),
'cc':VARCHAR(20)]:VARCHAR(20)), SEARCH($14, Sarg[(110..3000)]), <(CAST(+($3,
10)):DOUBLE, LN(15))), AND(=($1, 'Jensen'), >($15, 10)),
SEARCH(CAST($3):DECIMAL(11, 1), Sarg[[10.0:DECIMAL(11, 1)..20.0:DECIMAL(11,
1)]]:DECIMAL(1 [...]
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalUnion(all=[true])
+ LogicalJoin(condition=[AND(=($0, $9), =($2, 'Job1'), SEARCH($1,
Sarg['a':VARCHAR(20), 'bb':VARCHAR(20), 'cc':VARCHAR(20)]:VARCHAR(20)),
SEARCH($5, Sarg[(120..3000)]), =($3, $6))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalJoin(condition=[AND(=($7, $16), =($11, 'Job2'), SEARCH($10,
Sarg['a':VARCHAR(20), 'bb':VARCHAR(20), 'cc':VARCHAR(20)]:VARCHAR(20)),
SEARCH($14, Sarg[(110..3000)]), <(CAST(+($3, 10)):DOUBLE, LN(15)), OR(<>($0,
$9), <>($2, 'Job1'), SEARCH($1, Sarg[(-∞..'a':VARCHAR(20)),
('a':VARCHAR(20)..'bb':VARCHAR(20)), ('bb':VARCHAR(20)..'cc':VARCHAR(20)),
('cc':VARCHAR(20)..+∞)]:VARCHAR(20)), SEARCH($5, Sarg[(-∞..120], [3000..+∞)]),
<>($3, $6)))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalJoin(condition=[AND(OR(AND(=($1, 'Jensen'), >($15, 10)),
SEARCH(CAST($3):DECIMAL(11, 1), Sarg[[10.0:DECIMAL(11, 1)..20.0:DECIMAL(11,
1)]]:DECIMAL(11, 1))), OR(<>($0, $9), <>($2, 'Job1'), SEARCH($1,
Sarg[(-∞..'a':VARCHAR(20)), ('a':VARCHAR(20)..'bb':VARCHAR(20)),
('bb':VARCHAR(20)..'cc':VARCHAR(20)), ('cc':VARCHAR(20)..+∞)]:VARCHAR(20)),
SEARCH($5, Sarg[(-∞..120], [3000..+∞)]), <>($3, $6)), OR(<>($7, $16), <>($11,
'Job2'), SEARCH($10, Sarg[(-∞..'a':VARCHAR(20)), ('a':VARCHAR(20 [...]
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
</Resource>
</TestCase>
