This is an automated email from the ASF dual-hosted git repository. cbeikov pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 79d5001b945cbb9c47046b1b28b2345f78a72dc8 Author: Christian Beikov <[email protected]> AuthorDate: Wed Feb 19 19:55:29 2020 +0100 [CALCITE-3810] Render ANTI and SEMI join to NOT EXISTS and EXISTS in the JDBC adapter. Also add forgotten IS_DISTINCT_FROM translation support --- .../calcite/rel/rel2sql/RelToSqlConverter.java | 96 +++++++++++++++++ .../apache/calcite/rel/rel2sql/SqlImplementor.java | 16 ++- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 114 +++++++++++++++++++++ 3 files changed, 221 insertions(+), 5 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java index 5cc2606..63df2c4 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java @@ -76,6 +76,8 @@ import org.apache.calcite.sql.fun.SqlRowOperator; import org.apache.calcite.sql.fun.SqlSingleValueAggFunction; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.parser.SqlParserPos; +import org.apache.calcite.sql.util.SqlShuttle; +import org.apache.calcite.sql.util.SqlVisitor; import org.apache.calcite.sql.validate.SqlValidatorUtil; import org.apache.calcite.util.ImmutableBitSet; import org.apache.calcite.util.Pair; @@ -145,8 +147,42 @@ public class RelToSqlConverter extends SqlImplementor throw new AssertionError("Need to implement " + e.getClass().getName()); } + /** + * A SqlShuttle to replace references to a column of a table alias with the expression + * from the select item that is the source of that column. + * ANTI- and SEMI-joins generate an alias for right hand side relation which + * is used in the ON condition. But that alias is never created, so we have to inline references. + */ + private static class AliasReplacementShuttle extends SqlShuttle { + private final String tableAlias; + private final RelDataType tableType; + private final SqlNodeList replaceSource; + + AliasReplacementShuttle(String tableAlias, RelDataType tableType, SqlNodeList replaceSource) { + this.tableAlias = tableAlias; + this.tableType = tableType; + this.replaceSource = replaceSource; + } + + @Override public SqlNode visit(SqlIdentifier id) { + if (tableAlias.equals(id.names.get(0))) { + int index = tableType.getField( + id.names.get(1), false, false).getIndex(); + SqlNode selectItem = replaceSource.get(index); + if (selectItem.getKind() == SqlKind.AS) { + selectItem = ((SqlCall) selectItem).operand(0); + } + return selectItem.clone(id.getParserPosition()); + } + return id; + } + } + /** @see #dispatch */ public Result visit(Join e) { + if (e.getJoinType() == JoinRelType.ANTI || e.getJoinType() == JoinRelType.SEMI) { + return visitAntiOrSemiJoin(e); + } final Result leftResult = visitChild(0, e.getLeft()).resetAlias(); final Result rightResult = visitChild(1, e.getRight()).resetAlias(); final Context leftContext = leftResult.qualifiedContext(); @@ -175,6 +211,66 @@ public class RelToSqlConverter extends SqlImplementor return result(join, leftResult, rightResult); } + protected Result visitAntiOrSemiJoin(Join e) { + final Result leftResult = visitChild(0, e.getLeft()).resetAlias(); + final Result rightResult = visitChild(1, e.getRight()).resetAlias(); + final Context leftContext = leftResult.qualifiedContext(); + final Context rightContext = rightResult.qualifiedContext(); + SqlNode sqlCondition = null; + + SqlSelect sqlSelect = leftResult.asSelect(); + sqlCondition = convertConditionToSqlNode(e.getCondition(), + leftContext, + rightContext, + e.getLeft().getRowType().getFieldCount(), + dialect); + if (leftResult.neededAlias != null) { + SqlVisitor<SqlNode> visitor = new AliasReplacementShuttle(leftResult.neededAlias, + e.getLeft().getRowType(), sqlSelect.getSelectList()); + sqlCondition = sqlCondition.accept(visitor); + } + SqlNode fromPart = rightResult.asFrom(); + SqlSelect existsSqlSelect; + if (fromPart.getKind() == SqlKind.SELECT) { + existsSqlSelect = (SqlSelect) fromPart; + existsSqlSelect.setSelectList( + new SqlNodeList(ImmutableList.of(SqlLiteral.createExactNumeric("1", POS)), POS)); + if (existsSqlSelect.getWhere() != null) { + sqlCondition = SqlStdOperatorTable.AND.createCall(POS, + existsSqlSelect.getWhere(), + sqlCondition); + } + existsSqlSelect.setWhere(sqlCondition); + } else { + existsSqlSelect = + new SqlSelect(POS, null, + new SqlNodeList( + ImmutableList.of(SqlLiteral.createExactNumeric("1", POS)), POS), + fromPart, sqlCondition, null, + null, null, null, null, null, null); + } + sqlCondition = SqlStdOperatorTable.EXISTS.createCall(POS, existsSqlSelect); + if (e.getJoinType() == JoinRelType.ANTI) { + sqlCondition = SqlStdOperatorTable.NOT.createCall(POS, sqlCondition); + } + if (sqlSelect.getWhere() != null) { + sqlCondition = SqlStdOperatorTable.AND.createCall(POS, + sqlSelect.getWhere(), + sqlCondition); + } + sqlSelect.setWhere(sqlCondition); + SqlNode resultNode; + if (leftResult.neededAlias != null) { + resultNode = SqlStdOperatorTable.AS.createCall( + new SqlNodeList( + ImmutableList.of(sqlSelect, + new SqlIdentifier(leftResult.neededAlias, POS)), POS)); + } else { + resultNode = sqlSelect; + } + return result(resultNode, leftResult, rightResult); + } + private boolean isCrossJoin(final Join e) { return e.getJoinType() == JoinRelType.INNER && e.getCondition().isAlwaysTrue(); } 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 979f313..31927a0 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 @@ -255,6 +255,7 @@ public abstract class SqlImplementor { return sqlCondition; case EQUALS: + case IS_DISTINCT_FROM: case IS_NOT_DISTINCT_FROM: case NOT_EQUALS: case GREATER_THAN: @@ -464,11 +465,16 @@ public abstract class SqlImplementor { public Result result(SqlNode join, Result leftResult, Result rightResult) { final ImmutableMap.Builder<String, RelDataType> builder = ImmutableMap.builder(); - collectAliases(builder, join, - Iterables.concat(leftResult.aliases.values(), - rightResult.aliases.values()).iterator()); - return new Result(join, Expressions.list(Clause.FROM), null, null, - builder.build()); + if (join.getKind() == SqlKind.JOIN) { + collectAliases(builder, join, + Iterables.concat(leftResult.aliases.values(), + rightResult.aliases.values()).iterator()); + return new Result(join, Expressions.list(Clause.FROM), null, null, + builder.build()); + } else { + return new Result(join, Expressions.list(Clause.FROM), null, null, + leftResult.aliases); + } } private void collectAliases(ImmutableMap.Builder<String, RelDataType> builder, 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 490bfa1..234323f 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 @@ -651,6 +651,120 @@ public class RelToSqlConverterTest { assertThat(sqlString, notNullValue()); } + @Test public void testAntiJoin() { + final RelBuilder builder = relBuilder(); + final RelNode root = builder + .scan("DEPT") + .scan("EMP") + .join( + JoinRelType.ANTI, builder.equals( + builder.field(2, 1, "DEPTNO"), + builder.field(2, 0, "DEPTNO"))) + .project(builder.field("DEPTNO")) + .build(); + final String expectedSql = "SELECT \"DEPTNO\"\n" + + "FROM \"scott\".\"DEPT\"\n" + + "WHERE NOT EXISTS (SELECT 1\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\")"; + assertThat(toSql(root), isLinux(expectedSql)); + } + + @Test public void testSemiJoin() { + final RelBuilder builder = relBuilder(); + final RelNode root = builder + .scan("DEPT") + .scan("EMP") + .join( + JoinRelType.SEMI, builder.equals( + builder.field(2, 1, "DEPTNO"), + builder.field(2, 0, "DEPTNO"))) + .project(builder.field("DEPTNO")) + .build(); + final String expectedSql = "SELECT \"DEPTNO\"\n" + + "FROM \"scott\".\"DEPT\"\n" + + "WHERE EXISTS (SELECT 1\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\")"; + assertThat(toSql(root), isLinux(expectedSql)); + } + + @Test public void testSemiJoinFilter() { + final RelBuilder builder = relBuilder(); + final RelNode root = builder + .scan("DEPT") + .scan("EMP") + .filter( + builder.call(SqlStdOperatorTable.GREATER_THAN, + builder.field(builder.peek().getRowType().getField("EMPNO", false, false).getIndex()), + builder.literal((short) 10))) + .join( + JoinRelType.SEMI, builder.equals( + builder.field(2, 1, "DEPTNO"), + builder.field(2, 0, "DEPTNO"))) + .project(builder.field("DEPTNO")) + .build(); + final String expectedSql = "SELECT \"DEPTNO\"\n" + + "FROM \"scott\".\"DEPT\"\n" + + "WHERE EXISTS (SELECT 1\n" + + "FROM (SELECT *\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE \"EMPNO\" > 10) AS \"t\"\n" + + "WHERE \"DEPT\".\"DEPTNO\" = \"t\".\"DEPTNO\")"; + assertThat(toSql(root), isLinux(expectedSql)); + } + + @Test public void testSemiJoinProject() { + final RelBuilder builder = relBuilder(); + final RelNode root = builder + .scan("DEPT") + .scan("EMP") + .project( + builder.field(builder.peek().getRowType().getField("EMPNO", false, false).getIndex()), + builder.field(builder.peek().getRowType().getField("DEPTNO", false, false).getIndex())) + .join( + JoinRelType.SEMI, builder.equals( + builder.field(2, 1, "DEPTNO"), + builder.field(2, 0, "DEPTNO"))) + .project(builder.field("DEPTNO")) + .build(); + final String expectedSql = "SELECT \"DEPTNO\"\n" + + "FROM \"scott\".\"DEPT\"\n" + + "WHERE EXISTS (SELECT 1\n" + + "FROM (SELECT \"EMPNO\", \"DEPTNO\"\n" + + "FROM \"scott\".\"EMP\") AS \"t\"\n" + + "WHERE \"DEPT\".\"DEPTNO\" = \"t\".\"DEPTNO\")"; + assertThat(toSql(root), isLinux(expectedSql)); + } + + @Test public void testSemiNestedJoin() { + final RelBuilder builder = relBuilder(); + final RelNode base = builder + .scan("EMP") + .scan("EMP") + .join( + JoinRelType.INNER, builder.equals( + builder.field(2, 0, "EMPNO"), + builder.field(2, 1, "EMPNO"))) + .build(); + final RelNode root = builder + .scan("DEPT") + .push(base) + .join( + JoinRelType.SEMI, builder.equals( + builder.field(2, 1, "DEPTNO"), + builder.field(2, 0, "DEPTNO"))) + .project(builder.field("DEPTNO")) + .build(); + final String expectedSql = "SELECT \"DEPTNO\"\n" + + "FROM \"scott\".\"DEPT\"\n" + + "WHERE EXISTS (SELECT 1\n" + + "FROM \"scott\".\"EMP\"\n" + + "INNER JOIN \"scott\".\"EMP\" AS \"EMP0\" ON \"EMP\".\"EMPNO\" = \"EMP0\".\"EMPNO\"\n" + + "WHERE \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\")"; + assertThat(toSql(root), isLinux(expectedSql)); + } + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-2792";>[CALCITE-2792] * Stackoverflow while evaluating filter with large number of OR conditions</a>. */
