[CALCITE-4249] JDBC adapter cannot translate NOT LIKE in join condition
Fix bug translating Sarg to SQL, and add a test case for it.
(The test case only demonstrates the bug on [CALCITE-4262]
branch, where SqlToRelCovnerter uses RelBuilder.filter rather
than LogicalFilter.create, and therefore introduces a Sarg.)
diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index f0756a8..3d26ed2 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -51,7 +51,6 @@
import org.apache.calcite.sql.JoinType;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlBasicCall;
-import org.apache.calcite.sql.SqlBinaryOperator;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlDynamicParam;
@@ -66,6 +65,7 @@
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlSelectKeyword;
import org.apache.calcite.sql.SqlSetOperator;
+import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.fun.SqlCase;
import org.apache.calcite.sql.fun.SqlCountAggFunction;
@@ -129,6 +129,21 @@
protected final Map<CorrelationId, Context> correlTableMap = new HashMap<>();
+ /** Private RexBuilder for short-lived expressions. It has its own
+ * dedicated type factory, so don't trust the types to be canonized. */
+ final RexBuilder rexBuilder =
+ new RexBuilder(new SqlTypeFactoryImpl(RelDataTypeSystemImpl.DEFAULT));
+
+ /** Maps a {@link SqlKind} to a {@link SqlOperator} that implements NOT
+ * applied to that kind. */
+ private static final Map<SqlKind, SqlOperator> NOT_KIND_OPERATORS =
+ ImmutableMap.<SqlKind, SqlOperator>builder()
+ .put(SqlKind.IN, SqlStdOperatorTable.NOT_IN)
+ .put(SqlKind.NOT_IN, SqlStdOperatorTable.IN)
+ .put(SqlKind.LIKE, SqlStdOperatorTable.NOT_LIKE)
+ .put(SqlKind.SIMILAR, SqlStdOperatorTable.NOT_SIMILAR_TO)
+ .build();
+
protected SqlImplementor(SqlDialect dialect) {
this.dialect = Objects.requireNonNull(dialect);
}
@@ -284,21 +299,27 @@
final SqlOperator op;
final Context joinContext;
switch (node.getKind()) {
+ case NOT:
+ final RexNode operand0 = ((RexCall) node).getOperands().get(0);
+ final SqlOperator notOperator = NOT_KIND_OPERATORS.get(operand0.getKind());
+ if (notOperator != null) {
+ return convertConditionToSqlNode(
+ leftContext.implementor().rexBuilder.makeCall(notOperator,
+ ((RexCall) operand0).operands), leftContext, rightContext,
+ leftFieldCount, dialect);
+ }
+ // fall through
case AND:
case OR:
operands = ((RexCall) node).getOperands();
op = ((RexCall) node).getOperator();
- SqlNode sqlCondition = null;
+ final List<SqlNode> sqlOperands = new ArrayList<>();
for (RexNode operand : operands) {
- SqlNode x = convertConditionToSqlNode(operand, leftContext,
- rightContext, leftFieldCount, dialect);
- if (sqlCondition == null) {
- sqlCondition = x;
- } else {
- sqlCondition = op.createCall(POS, sqlCondition, x);
- }
+ sqlOperands.add(
+ convertConditionToSqlNode(operand, leftContext,
+ rightContext, leftFieldCount, dialect));
}
- return sqlCondition;
+ return SqlUtil.createCall(op, POS, sqlOperands);
case EQUALS:
case IS_DISTINCT_FROM:
@@ -774,19 +795,16 @@
case NOT:
RexNode operand = ((RexCall) rex).operands.get(0);
final SqlNode node = toSql(program, operand);
- switch (operand.getKind()) {
- case IN:
- assert operand instanceof RexSubQuery
- : "scalar IN is no longer allowed in RexCall: " + rex;
- return SqlStdOperatorTable.NOT_IN
- .createCall(POS, ((SqlCall) node).getOperandList());
- case LIKE:
- return SqlStdOperatorTable.NOT_LIKE
- .createCall(POS, ((SqlCall) node).getOperandList());
- case SIMILAR:
- return SqlStdOperatorTable.NOT_SIMILAR_TO
- .createCall(POS, ((SqlCall) node).getOperandList());
- default:
+ final SqlOperator inverseOperator = NOT_KIND_OPERATORS.get(operand.getKind());
+ if (inverseOperator != null) {
+ switch (operand.getKind()) {
+ case IN:
+ assert operand instanceof RexSubQuery
+ : "scalar IN is no longer allowed in RexCall: " + rex;
+ }
+ return inverseOperator.createCall(POS,
+ ((SqlCall) node).getOperandList());
+ } else {
return SqlStdOperatorTable.NOT.createCall(POS, node);
}
@@ -821,17 +839,7 @@
nodeList.add(dialect.getCastSpec(call.getType()));
}
}
- if (op instanceof SqlBinaryOperator && nodeList.size() > 2) {
- // In RexNode trees, OR and AND have any number of children;
- // SqlCall requires exactly 2. So, convert to a balanced binary
- // tree for OR/AND, left-deep binary tree for others.
- if (op.kind == SqlKind.OR || op.kind == SqlKind.AND) {
- return createBalancedCall(op, nodeList, 0, nodeList.size());
- } else {
- return createLeftCall(op, nodeList);
- }
- }
- return op.createCall(new SqlNodeList(nodeList, POS));
+ return SqlUtil.createCall(op, POS, nodeList);
}
}
@@ -841,9 +849,6 @@
private <C extends Comparable<C>> SqlNode toSql(RexProgram program,
RexNode operand, RelDataType type, Sarg<C> sarg) {
final List<SqlNode> orList = new ArrayList<>();
- final RexBuilder rexBuilder =
- new RexBuilder(
- new SqlTypeFactoryImpl(RelDataTypeSystemImpl.DEFAULT));
final SqlNode operandSql = toSql(program, operand);
if (sarg.containsNull) {
orList.add(SqlStdOperatorTable.IS_NULL.createCall(POS, operandSql));
@@ -852,8 +857,8 @@
final SqlNodeList list = sarg.rangeSet.asRanges().stream()
.map(range ->
toSql(program,
- rexBuilder.makeLiteral(range.lowerEndpoint(), type, true,
- true)))
+ implementor().rexBuilder.makeLiteral(range.lowerEndpoint(),
+ type, true, true)))
.collect(SqlNode.toList());
switch (list.size()) {
case 1:
@@ -867,17 +872,11 @@
} else {
final RangeSets.Consumer<C> consumer =
new RangeToSql<>(operandSql, orList, v ->
- toSql(program, rexBuilder.makeLiteral(v, type, false)));
+ toSql(program,
+ implementor().rexBuilder.makeLiteral(v, type, false)));
RangeSets.forEach(sarg.rangeSet, consumer);
}
- switch (orList.size()) {
- case 0:
- return SqlLiteral.createBoolean(false, POS);
- case 1:
- return orList.get(0);
- default:
- return SqlStdOperatorTable.OR.createCall(POS, orList);
- }
+ return SqlUtil.createCall(SqlStdOperatorTable.OR, POS, orList);
}
/** Converts an expression from {@link RexWindowBound} to {@link SqlNode}
@@ -1058,30 +1057,6 @@
+ rexWindowBound);
}
- private SqlNode createLeftCall(SqlOperator op, List<SqlNode> nodeList) {
- SqlNode node = op.createCall(new SqlNodeList(nodeList.subList(0, 2), POS));
- for (int i = 2; i < nodeList.size(); i++) {
- node = op.createCall(new SqlNodeList(ImmutableList.of(node, nodeList.get(i)), POS));
- }
- return node;
- }
-
- /**
- * Create a balanced binary call from sql node list,
- * start inclusive, end exclusive.
- */
- private SqlNode createBalancedCall(SqlOperator op,
- List<SqlNode> nodeList, int start, int end) {
- assert start < end && end <= nodeList.size();
- if (start + 1 == end) {
- return nodeList.get(start);
- }
- int mid = (end - start) / 2 + start;
- SqlNode leftNode = createBalancedCall(op, nodeList, start, mid);
- SqlNode rightNode = createBalancedCall(op, nodeList, mid, end);
- return op.createCall(new SqlNodeList(ImmutableList.of(leftNode, rightNode), POS));
- }
-
private List<SqlNode> toSql(RexProgram program, List<RexNode> operandList) {
final List<SqlNode> list = new ArrayList<>();
for (RexNode rex : operandList) {
@@ -1230,7 +1205,9 @@
}
private void addAnd(SqlNode... nodes) {
- list.add(SqlStdOperatorTable.AND.createCall(POS, nodes));
+ list.add(
+ SqlUtil.createCall(SqlStdOperatorTable.AND, POS,
+ ImmutableList.copyOf(nodes)));
}
private SqlNode op(SqlOperator op, C value) {
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
index 13144d4..b68f085 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
@@ -1098,6 +1098,63 @@
return (RelNode) rel;
}
+ /** Creates a call to an operator.
+ *
+ * <p>Deals with the fact the AND and OR are binary. */
+ public static SqlNode createCall(SqlOperator op, SqlParserPos pos,
+ List<SqlNode> operands) {
+ switch (op.kind) {
+ case OR:
+ case AND:
+ // In RexNode trees, OR and AND have any number of children;
+ // SqlCall requires exactly 2. So, convert to a balanced binary
+ // tree for OR/AND, left-deep binary tree for others.
+ switch (operands.size()) {
+ case 0:
+ return SqlLiteral.createBoolean(op.kind == SqlKind.AND, pos);
+ case 1:
+ return operands.get(0);
+ default:
+ return createBalancedCall(op, pos, operands, 0, operands.size());
+ case 2:
+ case 3:
+ case 4:
+ case 5:
+ // fall through
+ }
+ // fall through
+ }
+ if (op instanceof SqlBinaryOperator && operands.size() > 2) {
+ return createLeftCall(op, pos, operands);
+ }
+ return op.createCall(pos, operands);
+ }
+
+ private static SqlNode createLeftCall(SqlOperator op, SqlParserPos pos,
+ List<SqlNode> nodeList) {
+ SqlNode node = op.createCall(pos, nodeList.subList(0, 2));
+ for (int i = 2; i < nodeList.size(); i++) {
+ node = op.createCall(pos, node, nodeList.get(i));
+ }
+ return node;
+ }
+
+ /**
+ * Creates a balanced binary call from sql node list,
+ * start inclusive, end exclusive.
+ */
+ private static SqlNode createBalancedCall(SqlOperator op, SqlParserPos pos,
+ List<SqlNode> operands, int start, int end) {
+ assert start < end && end <= operands.size();
+ if (start + 1 == end) {
+ return operands.get(start);
+ }
+ int mid = (end - start) / 2 + start;
+ SqlNode leftNode = createBalancedCall(op, pos, operands, start, mid);
+ SqlNode rightNode = createBalancedCall(op, pos, operands, mid, end);
+ return op.createCall(pos, leftNode, rightNode);
+ }
+
//~ Inner Classes ----------------------------------------------------------
/**
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index c8a6065..e3b4350 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -219,6 +219,16 @@
sql(query).ok(expected);
}
+ @Test void testSelectWhereNotEqualsOrNull() {
+ String query = "select \"product_id\", \"shelf_width\"\n"
+ + "from \"product\"\n"
+ + "where \"net_weight\" <> 10 or \"net_weight\" is null";
+ final String expected = "SELECT \"product_id\", \"shelf_width\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"net_weight\" <> 10 OR \"net_weight\" IS NULL";
+ sql(query).ok(expected);
+ }
+
@Test void testSelectQueryWithWhereClauseOfBasicOperators() {
String query = "select * from \"product\" "
+ "where (\"product_id\" = 10 OR \"product_id\" <= 5) "
@@ -2422,6 +2432,30 @@
assertThat(s, notNullValue()); // sufficient that conversion did not throw
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-4249">[CALCITE-4249]
+ * JDBC adapter cannot translate NOT LIKE in join condition</a>. */
+ @Test void testJoinOnNotLike() {
+ final Function<RelBuilder, RelNode> relFn = b ->
+ b.scan("EMP")
+ .scan("DEPT")
+ .join(JoinRelType.LEFT,
+ b.and(
+ b.equals(b.field(2, 0, "DEPTNO"),
+ b.field(2, 1, "DEPTNO")),
+ b.not(
+ b.call(SqlStdOperatorTable.LIKE,
+ b.field(2, 1, "DNAME"),
+ b.literal("ACCOUNTING")))))
+ .build();
+ final String expectedSql = "SELECT *\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "LEFT JOIN \"scott\".\"DEPT\" "
+ + "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\" "
+ + "AND \"DEPT\".\"DNAME\" NOT LIKE 'ACCOUNTING'";
+ relFn(relFn).ok(expectedSql);
+ }
+
@Test void testCartesianProductWithInnerJoinSyntax() {
String query = "select * from \"department\"\n"
+ "INNER JOIN \"employee\" ON TRUE";
