[CALCITE-1849] Support RexSubQuery in RelToSqlConverter Close apache/calcite#477
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/e43520a8 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/e43520a8 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/e43520a8 Branch: refs/heads/master Commit: e43520a8164f1ade4389ae853befaf85ff8a2c82 Parents: 9efefbc Author: Zhiqiang-He <absolute...@qq.com> Authored: Mon Jun 19 20:27:06 2017 +0800 Committer: Julian Hyde <jh...@apache.org> Committed: Mon Aug 7 09:33:33 2017 -0700 ---------------------------------------------------------------------- .../calcite/rel/rel2sql/RelToSqlConverter.java | 10 ++ .../calcite/rel/rel2sql/SqlImplementor.java | 51 ++++++++ .../rel/rel2sql/RelToSqlConverterTest.java | 117 ++++++++++++++++++- 3 files changed, 172 insertions(+), 6 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/e43520a8/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java ---------------------------------------------------------------------- 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 4896a74..39587a0 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 @@ -22,6 +22,7 @@ import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Aggregate; import org.apache.calcite.rel.core.AggregateCall; import org.apache.calcite.rel.core.Calc; +import org.apache.calcite.rel.core.CorrelationId; import org.apache.calcite.rel.core.Filter; import org.apache.calcite.rel.core.Intersect; import org.apache.calcite.rel.core.Join; @@ -138,6 +139,7 @@ public class RelToSqlConverter extends SqlImplementor public Result visit(Filter e) { final RelNode input = e.getInput(); Result x = visitChild(0, input); + parseCorrelTable(e, x); if (input instanceof Aggregate) { final Builder builder; if (((Aggregate) input).getInput() instanceof Project) { @@ -158,6 +160,7 @@ public class RelToSqlConverter extends SqlImplementor /** @see #dispatch */ public Result visit(Project e) { Result x = visitChild(0, e.getInput()); + parseCorrelTable(e, x); if (isStar(e.getChildExps(), e.getInput().getRowType())) { return x; } @@ -239,6 +242,7 @@ public class RelToSqlConverter extends SqlImplementor /** @see #dispatch */ public Result visit(Calc e) { Result x = visitChild(0, e.getInput()); + parseCorrelTable(e, x); final RexProgram program = e.getProgram(); Builder builder = program.getCondition() != null @@ -474,6 +478,12 @@ public class RelToSqlConverter extends SqlImplementor } selectList.add(node); } + + private void parseCorrelTable(RelNode relNode, Result x) { + for (CorrelationId id : relNode.getVariablesSet()) { + correlTableMap.put(id, x.qualifiedContext()); + } + } } // End RelToSqlConverter.java http://git-wip-us.apache.org/repos/asf/calcite/blob/e43520a8/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 8b227c7..285cfe4 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 @@ -21,11 +21,14 @@ import org.apache.calcite.linq4j.tree.Expressions; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.AggregateCall; +import org.apache.calcite.rel.core.CorrelationId; import org.apache.calcite.rel.core.JoinRelType; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeField; import org.apache.calcite.rex.RexCall; +import org.apache.calcite.rex.RexCorrelVariable; import org.apache.calcite.rex.RexDynamicParam; +import org.apache.calcite.rex.RexFieldAccess; import org.apache.calcite.rex.RexFieldCollation; import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexLiteral; @@ -34,6 +37,7 @@ import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexOver; import org.apache.calcite.rex.RexPatternFieldRef; import org.apache.calcite.rex.RexProgram; +import org.apache.calcite.rex.RexSubQuery; import org.apache.calcite.rex.RexWindow; import org.apache.calcite.rex.RexWindowBound; import org.apache.calcite.sql.JoinType; @@ -115,6 +119,8 @@ public abstract class SqlImplementor { protected final Set<String> aliasSet = new LinkedHashSet<>(); protected final Map<String, SqlNode> ordinalMap = new HashMap<>(); + protected final Map<CorrelationId, Context> correlTableMap = new HashMap<>(); + protected SqlImplementor(SqlDialect dialect) { this.dialect = Preconditions.checkNotNull(dialect); } @@ -511,6 +517,8 @@ public abstract class SqlImplementor { * @param rex Expression to convert */ public SqlNode toSql(RexProgram program, RexNode rex) { + final RexSubQuery subQuery; + final SqlNode sqlSubQuery; switch (rex.getKind()) { case LOCAL_REF: final int index = ((RexLocalRef) rex).getIndex(); @@ -519,6 +527,13 @@ public abstract class SqlImplementor { case INPUT_REF: return field(((RexInputRef) rex).getIndex()); + case FIELD_ACCESS: + RexFieldAccess access = (RexFieldAccess) rex; + final RexCorrelVariable variable = + (RexCorrelVariable) access.getReferenceExpr(); + final Context aliasContext = correlTableMap.get(variable.id); + return aliasContext.field(access.getField().getIndex()); + case PATTERN_INPUT_REF: final RexPatternFieldRef ref = (RexPatternFieldRef) rex; String pv = ref.getAlpha(); @@ -602,6 +617,42 @@ public abstract class SqlImplementor { final RexDynamicParam caseParam = (RexDynamicParam) rex; 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)); + } else { + final List<SqlNode> cols = toSql(program, operands); + op0 = new SqlNodeList(cols, POS); + } + return subQuery.getOperator().createCall(POS, op0, sqlSubQuery); + + case EXISTS: + case SCALAR_QUERY: + subQuery = (RexSubQuery) rex; + sqlSubQuery = visitChild(0, subQuery.rel).asQueryOrValues(); + return subQuery.getOperator().createCall(POS, sqlSubQuery); + + case NOT: + RexNode operand = ((RexCall) rex).operands.get(0); + final SqlNode node = toSql(program, operand); + switch (operand.getKind()) { + case IN: + 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: + return SqlStdOperatorTable.NOT.createCall(POS, node); + } + default: if (rex instanceof RexOver) { return toSql(program, (RexOver) rex); http://git-wip-us.apache.org/repos/asf/calcite/blob/e43520a8/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 82fee32..15725ec 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 @@ -31,6 +31,7 @@ import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.sql.SqlDialect.DatabaseProduct; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.parser.SqlParser; +import org.apache.calcite.sql2rel.SqlToRelConverter; import org.apache.calcite.test.CalciteAssert; import org.apache.calcite.tools.FrameworkConfig; import org.apache.calcite.tools.Frameworks; @@ -57,20 +58,35 @@ import static org.junit.Assert.assertThat; * Tests for {@link RelToSqlConverter}. */ public class RelToSqlConverterTest { + static final SqlToRelConverter.Config DEFAULT_REL_CONFIG = + SqlToRelConverter.configBuilder() + .withTrimUnusedFields(false) + .withConvertTableAccess(false) + .build(); + + static final SqlToRelConverter.Config NO_EXPAND_CONFIG = + SqlToRelConverter.configBuilder() + .withTrimUnusedFields(false) + .withConvertTableAccess(false) + .withExpand(false) + .build(); + /** Initiates a test case with a given SQL query. */ private Sql sql(String sql) { return new Sql(CalciteAssert.SchemaSpec.JDBC_FOODMART, sql, - SqlDialect.CALCITE, ImmutableList.<Function<RelNode, RelNode>>of()); + SqlDialect.CALCITE, DEFAULT_REL_CONFIG, + ImmutableList.<Function<RelNode, RelNode>>of()); } private static Planner getPlanner(List<RelTraitDef> traitDefs, SqlParser.Config parserConfig, CalciteAssert.SchemaSpec schemaSpec, - Program... programs) { + SqlToRelConverter.Config sqlToRelConf, Program... programs) { final SchemaPlus rootSchema = Frameworks.createRootSchema(true); final FrameworkConfig config = Frameworks.newConfigBuilder() .parserConfig(parserConfig) .defaultSchema(CalciteAssert.addSchema(rootSchema, schemaSpec)) .traitDefs(traitDefs) + .sqlToRelConverterConfig(sqlToRelConf) .programs(programs) .build(); return Frameworks.getPlanner(config); @@ -830,6 +846,88 @@ public class RelToSqlConverterTest { .ok(expectedMssql); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1849";>[CALCITE-1849] + * Support sub-queries (RexSubQuery) in RelToSqlConverter</a>. */ + @Test public void testExistsWithExpand() { + String query = "select \"product_name\" from \"product\" a " + + "where exists (select count(*) " + + "from \"sales_fact_1997\"b " + + "where b.\"product_id\" = a.\"product_id\")"; + String expected = "SELECT \"product_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE EXISTS (SELECT COUNT(*)\n" + + "FROM \"foodmart\".\"sales_fact_1997\"\n" + + "WHERE \"product_id\" = \"product\".\"product_id\")"; + sql(query).config(NO_EXPAND_CONFIG).ok(expected); + } + + @Test public void testNotExistsWithExpand() { + String query = "select \"product_name\" from \"product\" a " + + "where not exists (select count(*) " + + "from \"sales_fact_1997\"b " + + "where b.\"product_id\" = a.\"product_id\")"; + String expected = "SELECT \"product_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE NOT EXISTS (SELECT COUNT(*)\n" + + "FROM \"foodmart\".\"sales_fact_1997\"\n" + + "WHERE \"product_id\" = \"product\".\"product_id\")"; + sql(query).config(NO_EXPAND_CONFIG).ok(expected); + } + + @Test public void testSubQueryInWithExpand() { + String query = "select \"product_name\" from \"product\" a " + + "where \"product_id\" in (select \"product_id\" " + + "from \"sales_fact_1997\"b " + + "where b.\"product_id\" = a.\"product_id\")"; + String expected = "SELECT \"product_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE \"product_id\" IN (SELECT \"product_id\"\n" + + "FROM \"foodmart\".\"sales_fact_1997\"\n" + + "WHERE \"product_id\" = \"product\".\"product_id\")"; + sql(query).config(NO_EXPAND_CONFIG).ok(expected); + } + + @Test public void testSubQueryInWithExpand2() { + String query = "select \"product_name\" from \"product\" a " + + "where \"product_id\" in (1, 2)"; + String expected = "SELECT \"product_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE \"product_id\" = 1 OR \"product_id\" = 2"; + sql(query).config(NO_EXPAND_CONFIG).ok(expected); + } + + @Test public void testSubQueryNotInWithExpand() { + String query = "select \"product_name\" from \"product\" a " + + "where \"product_id\" not in (select \"product_id\" " + + "from \"sales_fact_1997\"b " + + "where b.\"product_id\" = a.\"product_id\")"; + String expected = "SELECT \"product_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE \"product_id\" NOT IN (SELECT \"product_id\"\n" + + "FROM \"foodmart\".\"sales_fact_1997\"\n" + + "WHERE \"product_id\" = \"product\".\"product_id\")"; + sql(query).config(NO_EXPAND_CONFIG).ok(expected); + } + + @Test public void testLike() { + String query = "select \"product_name\" from \"product\" a " + + "where \"product_name\" like 'abc'"; + String expected = "SELECT \"product_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE \"product_name\" LIKE 'abc'"; + sql(query).ok(expected); + } + + @Test public void testNotLike() { + String query = "select \"product_name\" from \"product\" a " + + "where \"product_name\" not like 'abc'"; + String expected = "SELECT \"product_name\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE \"product_name\" NOT LIKE 'abc'"; + sql(query).ok(expected); + } + @Test public void testMatchRecognizePatternExpression() { String sql = "select *\n" + " from \"product\" match_recognize\n" @@ -1806,21 +1904,28 @@ public class RelToSqlConverterTest { private final String sql; private final SqlDialect dialect; private final List<Function<RelNode, RelNode>> transforms; + private final SqlToRelConverter.Config config; Sql(CalciteAssert.SchemaSpec schemaSpec, String sql, SqlDialect dialect, + SqlToRelConverter.Config config, List<Function<RelNode, RelNode>> transforms) { this.schemaSpec = schemaSpec; this.sql = sql; this.dialect = dialect; this.transforms = ImmutableList.copyOf(transforms); + this.config = config; } Sql dialect(SqlDialect dialect) { - return new Sql(schemaSpec, sql, dialect, transforms); + return new Sql(schemaSpec, sql, dialect, config, transforms); + } + + Sql config(SqlToRelConverter.Config config) { + return new Sql(schemaSpec, sql, dialect, config, transforms); } Sql optimize(final RuleSet ruleSet, final RelOptPlanner relOptPlanner) { - return new Sql(schemaSpec, sql, dialect, + return new Sql(schemaSpec, sql, dialect, config, FlatLists.append(transforms, new Function<RelNode, RelNode>() { public RelNode apply(RelNode r) { Program program = Programs.of(ruleSet); @@ -1849,7 +1954,7 @@ public class RelToSqlConverterTest { String exec() { final Planner planner = - getPlanner(null, SqlParser.Config.DEFAULT, schemaSpec); + getPlanner(null, SqlParser.Config.DEFAULT, schemaSpec, config); try { SqlNode parse = planner.parse(sql); SqlNode validate = planner.validate(parse); @@ -1869,7 +1974,7 @@ public class RelToSqlConverterTest { } public Sql schema(CalciteAssert.SchemaSpec schemaSpec) { - return new Sql(schemaSpec, sql, dialect, transforms); + return new Sql(schemaSpec, sql, dialect, config, transforms); } } }
