[CALCITE-2444] Handle IN expressions when converting SqlNode to SQL (Zoltan Haindrich)
Previously RelToSqlConverter could not handle IN expressions because it never saw them: SqlToRelConverter had converted them to OR, and if an IN expression had been created internally - say via RelBuilder - RelToSqlConverter would throw a ClassCastException. Now we convert IN expressions to "IN" SQL, as you would expect. Close apache/calcite#805 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/37a6f9f4 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/37a6f9f4 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/37a6f9f4 Branch: refs/heads/master Commit: 37a6f9f4f85d59185225e8883df74ffa8ca94c1b Parents: a5378a3 Author: Zoltan Haindrich <[email protected]> Authored: Tue Aug 28 09:30:07 2018 +0200 Committer: Julian Hyde <[email protected]> Committed: Mon Sep 17 09:43:03 2018 -0700 ---------------------------------------------------------------------- .../calcite/rel/rel2sql/SqlImplementor.java | 26 ++++--- .../rel/rel2sql/RelToSqlConverterTest.java | 73 ++++++++++++++++++++ 2 files changed, 90 insertions(+), 9 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/37a6f9f4/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java ---------------------------------------------------------------------- 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 b26f29b..4d524b8 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 @@ -560,17 +560,25 @@ public abstract class SqlImplementor { return new SqlDynamicParam(caseParam.getIndex(), POS); case IN: - subQuery = (RexSubQuery) rex; - sqlSubQuery = visitChild(0, subQuery.rel).asQueryOrValues(); - List<RexNode> operands = subQuery.operands; - SqlNode op0; - if (operands.size() == 1) { - op0 = toSql(program, operands.get(0)); + if (rex instanceof RexSubQuery) { + subQuery = (RexSubQuery) rex; + sqlSubQuery = + visitChild(0, subQuery.rel).asQueryOrValues(); + final List<RexNode> operands = subQuery.operands; + SqlNode op0; + if (operands.size() == 1) { + op0 = toSql(program, operands.get(0)); + } else { + final List<SqlNode> cols = toSql(program, operands); + op0 = new SqlNodeList(cols, POS); + } + return subQuery.getOperator().createCall(POS, op0, sqlSubQuery); } else { - final List<SqlNode> cols = toSql(program, operands); - op0 = new SqlNodeList(cols, POS); + final RexCall call = (RexCall) rex; + final List<SqlNode> cols = toSql(program, call.operands); + return call.getOperator().createCall(POS, cols.get(0), + new SqlNodeList(cols.subList(1, cols.size()), POS)); } - return subQuery.getOperator().createCall(POS, op0, sqlSubQuery); case EXISTS: case SCALAR_QUERY: http://git-wip-us.apache.org/repos/asf/calcite/blob/37a6f9f4/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- 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 572f5fc..c3c724e 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 @@ -24,6 +24,7 @@ import org.apache.calcite.plan.hep.HepProgram; import org.apache.calcite.plan.hep.HepProgramBuilder; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.rules.UnionMergeRule; +import org.apache.calcite.rex.RexNode; import org.apache.calcite.runtime.FlatLists; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.sql.SqlCall; @@ -36,14 +37,17 @@ import org.apache.calcite.sql.dialect.CalciteSqlDialect; import org.apache.calcite.sql.dialect.HiveSqlDialect; import org.apache.calcite.sql.dialect.JethroDataSqlDialect; import org.apache.calcite.sql.dialect.MysqlSqlDialect; +import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.parser.SqlParser; import org.apache.calcite.sql2rel.SqlToRelConverter; import org.apache.calcite.test.CalciteAssert; +import org.apache.calcite.test.RelBuilderTest; import org.apache.calcite.tools.FrameworkConfig; import org.apache.calcite.tools.Frameworks; import org.apache.calcite.tools.Planner; import org.apache.calcite.tools.Program; import org.apache.calcite.tools.Programs; +import org.apache.calcite.tools.RelBuilder; import org.apache.calcite.tools.RuleSet; import org.apache.calcite.tools.RuleSets; @@ -76,6 +80,9 @@ public class RelToSqlConverterTest { .withExpand(false) .build(); + final RelBuilder builder = RelBuilder.create(RelBuilderTest.config().build()); + final RelBuilder empScan = builder.scan("EMP"); + /** Initiates a test case with a given SQL query. */ private Sql sql(String sql) { return new Sql(CalciteAssert.SchemaSpec.JDBC_FOODMART, sql, @@ -364,6 +371,72 @@ public class RelToSqlConverterTest { sql(query).withHive().ok(expected); } + + private String unparseRelTree(RelNode root) { + SqlDialect dialect = SqlDialect.DatabaseProduct.CALCITE.getDialect(); + final RelToSqlConverter converter = new RelToSqlConverter(dialect); + final SqlNode sqlNode = converter.visitChild(0, root).asStatement(); + return sqlNode.toSqlString(dialect).getSql(); + } + + /** + * Tests that IN can be un-parsed. + * + * <p>This cannot be tested using "sql", because because Calcite's SQL parser + * replaces INs with ORs or sub-queries. + */ + @Test public void testUnparseIn1() { + final RexNode condition = + builder.call(SqlStdOperatorTable.IN, builder.field("DEPTNO"), + builder.literal(21)); + final String sql = unparseRelTree(empScan.filter(condition).build()); + final String expectedSql = "SELECT *\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE \"DEPTNO\" IN (21)"; + assertThat(sql, is(expectedSql)); + } + + @Test public void testUnparseIn2() { + final RexNode filter = + builder.call(SqlStdOperatorTable.IN, builder.field("DEPTNO"), + builder.literal(20), builder.literal(21)); + final String sql = unparseRelTree(empScan.filter(filter).build()); + final String expectedSql = "SELECT *\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE \"DEPTNO\" IN (20, 21)"; + assertThat(sql, is(expectedSql)); + } + + @Test public void testUnparseInStruct1() { + final RexNode condition = + builder.call(SqlStdOperatorTable.IN, + builder.call(SqlStdOperatorTable.ROW, builder.field("DEPTNO"), + builder.field("JOB")), + builder.call(SqlStdOperatorTable.ROW, builder.literal(1), + builder.literal("PRESIDENT"))); + final String sql = unparseRelTree(empScan.filter(condition).build()); + final String expectedSql = "SELECT *\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE ROW(\"DEPTNO\", \"JOB\") IN (ROW(1, 'PRESIDENT'))"; + assertThat(sql, is(expectedSql)); + } + + @Test public void testUnparseInStruct2() { + final RexNode condition = + builder.call(SqlStdOperatorTable.IN, + builder.call(SqlStdOperatorTable.ROW, builder.field("DEPTNO"), + builder.field("JOB")), + builder.call(SqlStdOperatorTable.ROW, builder.literal(1), + builder.literal("PRESIDENT")), + builder.call(SqlStdOperatorTable.ROW, builder.literal(2), + builder.literal("PRESIDENT"))); + final String sql = unparseRelTree(empScan.filter(condition).build()); + final String expectedSql = "SELECT *\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE ROW(\"DEPTNO\", \"JOB\") IN (ROW(1, 'PRESIDENT'), ROW(2, 'PRESIDENT'))"; + assertThat(sql, is(expectedSql)); + } + @Test public void testSelectQueryWithLimitClause() { String query = "select \"product_id\" from \"product\" limit 100 offset 10"; final String expected = "SELECT product_id\n"
