Repository: calcite Updated Branches: refs/heads/master c933c79f7 -> b4df7c97b
[CALCITE-1332] JDBC adapter for DB2 should always use aliases for tables: x.y.z AS z (Minji Kim) Hold aliases in order-preserving Map rather than List<Pair>. Close apache/calcite#261 fix up Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/b4df7c97 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/b4df7c97 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/b4df7c97 Branch: refs/heads/master Commit: b4df7c97bb7905cb2c510a08830b8bd710107119 Parents: c933c79 Author: Minji Kim <[email protected]> Authored: Wed Jul 27 11:37:45 2016 -0700 Committer: Julian Hyde <[email protected]> Committed: Thu Jul 28 12:14:40 2016 -0700 ---------------------------------------------------------------------- .../calcite/adapter/jdbc/JdbcImplementor.java | 4 +- .../calcite/adapter/jdbc/JdbcTableScan.java | 5 +- .../calcite/rel/rel2sql/RelToSqlConverter.java | 11 +- .../calcite/rel/rel2sql/SqlImplementor.java | 90 +++++--- .../java/org/apache/calcite/sql/SqlDialect.java | 31 +++ .../rel/rel2sql/RelToSqlConverterTest.java | 230 +++++++++++++++---- 6 files changed, 283 insertions(+), 88 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/b4df7c97/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcImplementor.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcImplementor.java b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcImplementor.java index e7da0ef..79ec62f 100644 --- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcImplementor.java +++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcImplementor.java @@ -22,7 +22,7 @@ import org.apache.calcite.rel.rel2sql.RelToSqlConverter; import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.util.Util; -import java.util.Collections; +import com.google.common.collect.ImmutableList; /** * State for generating a SQL statement. @@ -36,7 +36,7 @@ public class JdbcImplementor extends RelToSqlConverter { /** @see #dispatch */ public Result visit(JdbcTableScan scan) { return result(scan.jdbcTable.tableName(), - Collections.singletonList(Clause.FROM), scan); + ImmutableList.of(Clause.FROM), scan, null); } public Result implement(RelNode node) { http://git-wip-us.apache.org/repos/asf/calcite/blob/b4df7c97/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTableScan.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTableScan.java b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTableScan.java index 96cabc3..7ef8938 100644 --- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTableScan.java +++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTableScan.java @@ -22,7 +22,8 @@ import org.apache.calcite.plan.RelTraitSet; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.TableScan; -import java.util.Collections; +import com.google.common.collect.ImmutableList; + import java.util.List; /** @@ -49,7 +50,7 @@ public class JdbcTableScan extends TableScan implements JdbcRel { public JdbcImplementor.Result implement(JdbcImplementor implementor) { return implementor.result(jdbcTable.tableName(), - Collections.singletonList(JdbcImplementor.Clause.FROM), this); + ImmutableList.of(JdbcImplementor.Clause.FROM), this, null); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/b4df7c97/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 4bfd402..04f3343 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 @@ -54,10 +54,11 @@ import org.apache.calcite.util.ReflectUtil; import org.apache.calcite.util.ReflectiveVisitor; import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableMap; import java.util.ArrayList; -import java.util.Collections; import java.util.List; +import java.util.Map; /** * Utility to convert relational expressions to SQL abstract syntax tree. @@ -177,7 +178,7 @@ public class RelToSqlConverter extends SqlImplementor public Result visit(TableScan e) { final SqlIdentifier identifier = new SqlIdentifier(e.getTable().getQualifiedName(), SqlParserPos.ZERO); - return result(identifier, Collections.singletonList(Clause.FROM), e); + return result(identifier, ImmutableList.of(Clause.FROM), e, null); } /** @see #dispatch */ @@ -228,8 +229,8 @@ public class RelToSqlConverter extends SqlImplementor /** @see #dispatch */ public Result visit(Values e) { final List<String> fields = e.getRowType().getFieldNames(); - final List<Clause> clauses = Collections.singletonList(Clause.SELECT); - final List<Pair<String, RelDataType>> pairs = ImmutableList.of(); + final List<Clause> clauses = ImmutableList.of(Clause.SELECT); + final Map<String, RelDataType> pairs = ImmutableMap.of(); final Context context = aliasContext(pairs, false); final List<SqlSelect> selects = new ArrayList<>(); for (List<RexLiteral> tuple : e.getTuples()) { @@ -255,7 +256,7 @@ public class RelToSqlConverter extends SqlImplementor select); } } - return result(query, clauses, e); + return result(query, clauses, e, null); } /** @see #dispatch */ http://git-wip-us.apache.org/repos/asf/calcite/blob/b4df7c97/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 1f116b2..3fc030f 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 @@ -59,11 +59,11 @@ import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.type.SqlTypeUtil; import org.apache.calcite.sql.validate.SqlValidatorUtil; -import org.apache.calcite.util.Pair; import org.apache.calcite.util.Util; import com.google.common.base.Preconditions; import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableMap; import com.google.common.collect.ImmutableSet; import org.slf4j.Logger; @@ -73,8 +73,8 @@ import java.util.AbstractList; import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; -import java.util.Collections; import java.util.HashMap; +import java.util.LinkedHashMap; import java.util.LinkedHashSet; import java.util.List; import java.util.Map; @@ -233,7 +233,7 @@ public abstract class SqlImplementor { final SqlCall node = operator.createCall(new SqlNodeList(list, POS)); final List<Clause> clauses = Expressions.list(Clause.SET_OP); - return result(node, clauses, rel); + return result(node, clauses, rel, null); } /** @@ -382,25 +382,45 @@ public abstract class SqlImplementor { } /** Creates a result based on a single relational expression. */ - public Result result(SqlNode node, Collection<Clause> clauses, RelNode rel) { + public Result result(SqlNode node, Collection<Clause> clauses, + RelNode rel, Map<String, RelDataType> aliases) { + assert aliases == null + || aliases.size() < 2 + || aliases instanceof LinkedHashMap + || aliases instanceof ImmutableMap + : "must use a Map implementation that preserves order"; final String alias2 = SqlValidatorUtil.getAlias(node, -1); final String alias3 = alias2 != null ? alias2 : "t"; final String alias4 = SqlValidatorUtil.uniquify( alias3, aliasSet, SqlValidatorUtil.EXPR_SUGGESTER); - final String alias5 = alias2 == null || !alias2.equals(alias4) ? alias4 - : null; + if (aliases != null + && !aliases.isEmpty() + && !dialect.hasImplicitTableAlias()) { + return new Result(node, clauses, alias4, aliases); + } + + final String alias5; + if (alias2 == null + || !alias2.equals(alias4) + || !dialect.hasImplicitTableAlias()) { + alias5 = alias4; + } else { + alias5 = null; + } return new Result(node, clauses, alias5, - Collections.singletonList(Pair.of(alias4, rel.getRowType()))); + ImmutableMap.of(alias4, rel.getRowType())); } /** Creates a result based on a join. (Each join could contain one or more * relational expressions.) */ public Result result(SqlNode join, Result leftResult, Result rightResult) { - final List<Pair<String, RelDataType>> list = new ArrayList<>(); - list.addAll(leftResult.aliases); - list.addAll(rightResult.aliases); - return new Result(join, Expressions.list(Clause.FROM), null, list); + final Map<String, RelDataType> aliases = + ImmutableMap.<String, RelDataType>builder() + .putAll(leftResult.aliases) + .putAll(rightResult.aliases) + .build(); + return new Result(join, Expressions.list(Clause.FROM), null, aliases); } /** Wraps a node in a SELECT statement that has no clauses: @@ -642,15 +662,15 @@ public abstract class SqlImplementor { } private static int computeFieldCount( - List<Pair<String, RelDataType>> aliases) { + Map<String, RelDataType> aliases) { int x = 0; - for (Pair<String, RelDataType> alias : aliases) { - x += alias.right.getFieldCount(); + for (RelDataType type : aliases.values()) { + x += type.getFieldCount(); } return x; } - public Context aliasContext(List<Pair<String, RelDataType>> aliases, + public Context aliasContext(Map<String, RelDataType> aliases, boolean qualified) { return new AliasContext(aliases, qualified); } @@ -663,10 +683,10 @@ public abstract class SqlImplementor { * "table alias" based on the current sub-query's FROM clause. */ public class AliasContext extends Context { private final boolean qualified; - private final List<Pair<String, RelDataType>> aliases; + private final Map<String, RelDataType> aliases; - /** Creates an AliasContext; use {@link #aliasContext(List, boolean)}. */ - protected AliasContext(List<Pair<String, RelDataType>> aliases, + /** Creates an AliasContext; use {@link #aliasContext(Map, boolean)}. */ + protected AliasContext(Map<String, RelDataType> aliases, boolean qualified) { super(computeFieldCount(aliases)); this.aliases = aliases; @@ -674,8 +694,8 @@ public abstract class SqlImplementor { } public SqlNode field(int ordinal) { - for (Pair<String, RelDataType> alias : aliases) { - final List<RelDataTypeField> fields = alias.right.getFieldList(); + for (Map.Entry<String, RelDataType> alias : aliases.entrySet()) { + final List<RelDataTypeField> fields = alias.getValue().getFieldList(); if (ordinal < fields.size()) { RelDataTypeField field = fields.get(ordinal); final SqlNode mappedSqlNode = @@ -685,7 +705,7 @@ public abstract class SqlImplementor { } return new SqlIdentifier(!qualified ? ImmutableList.of(field.getName()) - : ImmutableList.of(alias.left, field.getName()), + : ImmutableList.of(alias.getKey(), field.getName()), POS); } ordinal -= fields.size(); @@ -721,11 +741,11 @@ public abstract class SqlImplementor { public class Result { final SqlNode node; private final String neededAlias; - private final List<Pair<String, RelDataType>> aliases; + private final Map<String, RelDataType> aliases; final Expressions.FluentList<Clause> clauses; public Result(SqlNode node, Collection<Clause> clauses, String neededAlias, - List<Pair<String, RelDataType>> aliases) { + Map<String, RelDataType> aliases) { this.node = node; this.neededAlias = neededAlias; this.aliases = aliases; @@ -744,7 +764,7 @@ public abstract class SqlImplementor { * <p>When you have called * {@link Builder#setSelect(SqlNodeList)}, * {@link Builder#setWhere(SqlNode)} etc. call - * {@link Builder#result(SqlNode, Collection, RelNode)} + * {@link Builder#result(SqlNode, Collection, RelNode, Map)} * to fix the new query. * * @param rel Relational expression being implemented @@ -787,9 +807,18 @@ public abstract class SqlImplementor { } }; } else { - newContext = aliasContext(aliases, aliases.size() > 1); + boolean qualified = + !dialect.hasImplicitTableAlias() || aliases.size() > 1; + if (needNew) { + newContext = + aliasContext(ImmutableMap.of(neededAlias, rel.getRowType()), + qualified); + } else { + newContext = aliasContext(aliases, qualified); + } } - return new Builder(rel, clauseList, select, newContext); + return new Builder(rel, clauseList, select, newContext, + needNew ? null : aliases); } // make private? @@ -826,6 +855,9 @@ public abstract class SqlImplementor { if (node instanceof SqlSelect) { return (SqlSelect) node; } + if (!dialect.hasImplicitTableAlias()) { + return wrapSelect(asFrom()); + } return wrapSelect(node); } @@ -853,13 +885,15 @@ public abstract class SqlImplementor { final List<Clause> clauses; private final SqlSelect select; public final Context context; + private final Map<String, RelDataType> aliases; public Builder(RelNode rel, List<Clause> clauses, SqlSelect select, - Context context) { + Context context, Map<String, RelDataType> aliases) { this.rel = rel; this.clauses = clauses; this.select = select; this.context = context; + this.aliases = aliases; } public void setSelect(SqlNodeList nodeList) { @@ -906,7 +940,7 @@ public abstract class SqlImplementor { } public Result result() { - return SqlImplementor.this.result(select, clauses, rel); + return SqlImplementor.this.result(select, clauses, rel, aliases); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/b4df7c97/core/src/main/java/org/apache/calcite/sql/SqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java index fc04120..88e7f51 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -417,6 +417,37 @@ public class SqlDialect { return getDatabaseProduct() == DatabaseProduct.POSTGRESQL; } + /** Returns whether a qualified table in the FROM clause has an implicit alias + * which consists of just the table name. + * + * <p>For example, in {@link DatabaseProduct#ORACLE} + * + * <blockquote>SELECT * FROM sales.emp</blockquote> + * + * <p>is equivalent to + * + * <blockquote>SELECT * FROM sales.emp AS emp</blockquote> + * + * <p>and therefore + * + * <blockquote>SELECT emp.empno FROM sales.emp</blockquote> + * + * <p>is valid. But {@link DatabaseProduct#DB2} does not have an implicit + * alias, so the previous query it not valid; you need to write + * + * <blockquote>SELECT sales.emp.empno FROM sales.emp</blockquote> + * + * <p>Returns true for all databases except DB2. + */ + public boolean hasImplicitTableAlias() { + switch (databaseProduct) { + case DB2: + return false; + default: + return true; + } + } + /** * Converts a timestamp to a SQL timestamp literal, e.g. * {@code TIMESTAMP '2009-12-17 12:34:56'}. http://git-wip-us.apache.org/repos/asf/calcite/blob/b4df7c97/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 1d70131..85f4720 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 @@ -20,6 +20,7 @@ import org.apache.calcite.plan.RelTraitDef; import org.apache.calcite.rel.RelNode; import org.apache.calcite.schema.SchemaPlus; 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.test.CalciteAssert; @@ -83,16 +84,14 @@ public class RelToSqlConverterTest { @Test public void testSimpleSelectStarFromProductTable() { String query = "select * from \"product\""; - checkRel2Sql(this.logicalPlanner, - query, - "SELECT *\nFROM \"foodmart\".\"product\""); + checkRel2Sql(logicalPlanner, query, + "SELECT *\nFROM \"foodmart\".\"product\""); } @Test public void testSimpleSelectQueryFromProductTable() { String query = "select \"product_id\", \"product_class_id\" from \"product\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\", \"product_class_id\"\n" + "FROM \"foodmart\".\"product\""); } @@ -103,8 +102,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithWhereClauseOfLessThan() { String query = "select \"product_id\", \"shelf_width\" from \"product\" where \"product_id\" < 10"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\", \"shelf_width\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"product_id\" < 10"); @@ -115,8 +113,7 @@ public class RelToSqlConverterTest { String query = "select * from \"product\" " + "where (\"product_id\" = 10 OR \"product_id\" <= 5) " + "AND (80 >= \"shelf_width\" OR \"shelf_width\" > 30)"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT *\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE (\"product_id\" = 10 OR \"product_id\" <= 5) " @@ -127,8 +124,7 @@ public class RelToSqlConverterTest { @Test public void testSelectQueryWithGroupBy() { String query = "select count(*) from \"product\" group by \"product_class_id\", \"product_id\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\", \"product_id\""); @@ -137,8 +133,7 @@ public class RelToSqlConverterTest { @Test public void testSelectQueryWithMinAggregateFunction() { String query = "select min(\"net_weight\") from \"product\" group by \"product_class_id\" "; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT MIN(\"net_weight\")\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""); @@ -148,8 +143,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithMinAggregateFunction1() { String query = "select \"product_class_id\", min(\"net_weight\") from" + " \"product\" group by \"product_class_id\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_class_id\", MIN(\"net_weight\")\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""); @@ -159,8 +153,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithSumAggregateFunction() { String query = "select sum(\"net_weight\") from \"product\" group by \"product_class_id\" "; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT SUM(\"net_weight\")\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""); @@ -171,8 +164,7 @@ public class RelToSqlConverterTest { String query = "select sum(\"net_weight\"), min(\"low_fat\"), count(*)" + " from \"product\" group by \"product_class_id\" "; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT SUM(\"net_weight\"), MIN(\"low_fat\"), COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""); @@ -183,8 +175,7 @@ public class RelToSqlConverterTest { String query = "select \"product_class_id\", sum(\"net_weight\"), min(\"low_fat\"), count(*)" + " from \"product\" group by \"product_class_id\" "; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_class_id\", SUM(\"net_weight\"), MIN(\"low_fat\"), COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\""); @@ -195,8 +186,7 @@ public class RelToSqlConverterTest { String query = "select \"product_class_id\", \"product_id\", count(*) from \"product\" group " + "by \"product_class_id\", \"product_id\" "; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_class_id\", \"product_id\", COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\", \"product_id\""); @@ -206,8 +196,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithGroupByAndProjectList1() { String query = "select count(*) from \"product\" group by \"product_class_id\", \"product_id\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" + "GROUP BY \"product_class_id\", \"product_id\""); @@ -217,8 +206,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithGroupByHaving() { String query = "select count(*) from \"product\" group by \"product_class_id\"," + " \"product_id\" having \"product_id\" > 10"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT COUNT(*)\n" + "FROM (SELECT \"product_class_id\", \"product_id\", COUNT(*)\n" + "FROM \"foodmart\".\"product\"\n" @@ -229,8 +217,7 @@ public class RelToSqlConverterTest { @Test public void testSelectQueryWithOrderByClause() { String query = "select \"product_id\" from \"product\" order by \"net_weight\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\", \"net_weight\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"net_weight\""); @@ -240,8 +227,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithOrderByClause1() { String query = "select \"product_id\", \"net_weight\" from \"product\" order by \"net_weight\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\", \"net_weight\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"net_weight\""); @@ -251,8 +237,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithTwoOrderByClause() { String query = "select \"product_id\" from \"product\" order by \"net_weight\", \"gross_weight\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\", \"net_weight\", \"gross_weight\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"net_weight\", \"gross_weight\""); @@ -263,8 +248,7 @@ public class RelToSqlConverterTest { String query = "select \"product_id\" from \"product\" order by \"net_weight\" asc, " + "\"gross_weight\" desc, \"low_fat\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\", \"net_weight\", \"gross_weight\", \"low_fat\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"net_weight\", \"gross_weight\" DESC, \"low_fat\""); @@ -273,8 +257,7 @@ public class RelToSqlConverterTest { @Test public void testSelectQueryWithLimitClause() { String query = "select \"product_id\" from \"product\" limit 100 offset 10"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT product_id\n" + "FROM foodmart.product\n" + "LIMIT 100\nOFFSET 10", @@ -284,8 +267,7 @@ public class RelToSqlConverterTest { @Test public void testSelectQueryWithLimitClauseWithoutOrder() { String query = "select \"product_id\" from \"product\" limit 100 offset 10"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" + "OFFSET 10 ROWS\n" @@ -296,8 +278,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithLimitOffsetClause() { String query = "select \"product_id\" from \"product\" order by \"net_weight\" asc" + " limit 100 offset 10"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\", \"net_weight\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"net_weight\"\n" @@ -309,8 +290,7 @@ public class RelToSqlConverterTest { public void testSelectQueryWithFetchOffsetClause() { String query = "select \"product_id\" from \"product\" order by \"product_id\"" + " offset 10 rows fetch next 100 rows only"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"product_id\"\n" @@ -323,8 +303,7 @@ public class RelToSqlConverterTest { String query = "select count(*), \"units_per_case\" from \"product\" where \"cases_per_pallet\" > 100 " + "group by \"product_id\", \"units_per_case\" order by \"units_per_case\" desc"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT COUNT(*), \"units_per_case\"\n" + "FROM \"foodmart\".\"product\"\n" + "WHERE \"cases_per_pallet\" > 100\n" @@ -339,8 +318,7 @@ public class RelToSqlConverterTest { + "where \"hire_date\" > '2015-01-01' " + "and (\"position_title\" = 'SDE' or \"position_title\" = 'SDM') " + "group by \"store_id\", \"position_title\""; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT COUNT(*), SUM(\"employee_id\")\n" + "FROM \"foodmart\".\"reserve_employee\"\n" + "WHERE \"hire_date\" > '2015-01-01' " @@ -357,8 +335,7 @@ public class RelToSqlConverterTest { + " join \"product_class\" as pc using (\"product_class_id\")\n" + "where c.\"city\" = 'San Francisco'\n" + "and pc.\"product_department\" = 'Snacks'\n"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT *\nFROM \"foodmart\".\"sales_fact_1997\"\n" + "INNER JOIN \"foodmart\".\"customer\" " + "ON \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\"\n" @@ -374,14 +351,165 @@ public class RelToSqlConverterTest { String query = "select * from \"department\" where \"department_id\" in (\n" + " select \"department_id\" from \"employee\"\n" + " where \"store_id\" < 150)"; - checkRel2Sql(this.logicalPlanner, - query, + checkRel2Sql(logicalPlanner, query, "SELECT \"department\".\"department_id\", \"department\".\"department_description\"\n" + "FROM \"foodmart\".\"department\"\nINNER JOIN " + "(SELECT \"department_id\"\nFROM \"foodmart\".\"employee\"\n" + "WHERE \"store_id\" < 150\nGROUP BY \"department_id\") AS \"t1\" " + "ON \"department\".\"department_id\" = \"t1\".\"department_id\""); } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1332";>[CALCITE-1332] + * DB2 should always use aliases for tables: x.y.z AS z</a>. */ + @Test public void testDb2DialectJoinStar() { + String query = "select * " + + "from \"foodmart\".\"employee\" A " + + "join \"foodmart\".\"department\" B\n" + + "on A.\"department_id\" = B.\"department_id\""; + final String expected = "SELECT *\n" + + "FROM foodmart.employee AS employee\n" + + "INNER JOIN foodmart.department AS department " + + "ON employee.department_id = department.department_id"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectSelfJoinStar() { + String query = "select * " + + "from \"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + + "on A.\"department_id\" = B.\"department_id\""; + final String expected = "SELECT *\n" + + "FROM foodmart.employee AS employee\n" + + "INNER JOIN foodmart.employee AS employee0 " + + "ON employee.department_id = employee0.department_id"; + checkRel2Sql(logicalPlanner, query, + expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectJoin() { + String query = "select A.\"employee_id\", B.\"department_id\" " + + "from \"foodmart\".\"employee\" A join \"foodmart\".\"department\" B\n" + + "on A.\"department_id\" = B.\"department_id\""; + final String expected = "SELECT" + + " employee.employee_id, department.department_id\n" + + "FROM foodmart.employee AS employee\n" + + "INNER JOIN foodmart.department AS department " + + "ON employee.department_id = department.department_id"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectSelfJoin() { + String query = "select A.\"employee_id\", B.\"employee_id\" from " + + "\"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + + "on A.\"department_id\" = B.\"department_id\""; + final String expected = "SELECT" + + " employee.employee_id, employee0.employee_id AS employee_id0\n" + + "FROM foodmart.employee AS employee\n" + + "INNER JOIN foodmart.employee AS employee0 " + + "ON employee.department_id = employee0.department_id"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectWhere() { + String query = "select A.\"employee_id\" from " + + "\"foodmart\".\"employee\" A where A.\"department_id\" < 1000"; + final String expected = "SELECT employee.employee_id\n" + + "FROM foodmart.employee AS employee\n" + + "WHERE employee.department_id < 1000"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectJoinWhere() { + String query = "select A.\"employee_id\", B.\"department_id\" " + + "from \"foodmart\".\"employee\" A join \"foodmart\".\"department\" B\n" + + "on A.\"department_id\" = B.\"department_id\" " + + "where A.\"employee_id\" < 1000"; + final String expected = "SELECT" + + " employee.employee_id, department.department_id\n" + + "FROM foodmart.employee AS employee\n" + + "INNER JOIN foodmart.department AS department " + + "ON employee.department_id = department.department_id\n" + + "WHERE employee.employee_id < 1000"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectSelfJoinWhere() { + String query = "select A.\"employee_id\", B.\"employee_id\" from " + + "\"foodmart\".\"employee\" A join \"foodmart\".\"employee\" B\n" + + "on A.\"department_id\" = B.\"department_id\" " + + "where B.\"employee_id\" < 2000"; + final String expected = "SELECT " + + "employee.employee_id, employee0.employee_id AS employee_id0\n" + + "FROM foodmart.employee AS employee\n" + + "INNER JOIN foodmart.employee AS employee0 " + + "ON employee.department_id = employee0.department_id\n" + + "WHERE employee0.employee_id < 2000"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectCast() { + String query = "select \"hire_date\", cast(\"hire_date\" as varchar(10)) " + + "from \"foodmart\".\"reserve_employee\""; + final String expected = "SELECT reserve_employee.hire_date, " + + "CAST(reserve_employee.hire_date AS VARCHAR(10))\n" + + "FROM foodmart.reserve_employee AS reserve_employee"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectSelectQueryWithGroupByHaving() { + String query = "select count(*) from \"product\" " + + "group by \"product_class_id\", \"product_id\" " + + "having \"product_id\" > 10"; + final String expected = "SELECT COUNT(*)\n" + + "FROM (SELECT product.product_class_id, product.product_id, COUNT" + + "(*)\n" + + "FROM foodmart.product AS product\n" + + "GROUP BY product.product_class_id, product.product_id) AS t0\n" + + "WHERE t0.product_id > 10"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + + @Test public void testDb2DialectSelectQueryComplex() { + String query = "select count(*), \"units_per_case\" " + + "from \"product\" where \"cases_per_pallet\" > 100 " + + "group by \"product_id\", \"units_per_case\" " + + "order by \"units_per_case\" desc"; + final String expected = "SELECT COUNT(*), product.units_per_case\n" + + "FROM foodmart.product AS product\n" + + "WHERE product.cases_per_pallet > 100\n" + + "GROUP BY product.product_id, product.units_per_case\n" + + "ORDER BY product.units_per_case DESC"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + + @Test public void testDb2DialectSelectQueryWithGroup() { + String query = "select count(*), sum(\"employee_id\") " + + "from \"reserve_employee\" " + + "where \"hire_date\" > '2015-01-01' " + + "and (\"position_title\" = 'SDE' or \"position_title\" = 'SDM') " + + "group by \"store_id\", \"position_title\""; + final String expected = "SELECT" + + " COUNT(*), SUM(reserve_employee.employee_id)\n" + + "FROM foodmart.reserve_employee AS reserve_employee\n" + + "WHERE reserve_employee.hire_date > '2015-01-01' " + + "AND (reserve_employee.position_title = 'SDE' OR " + + "reserve_employee.position_title = 'SDM')\n" + + "GROUP BY reserve_employee.store_id, reserve_employee.position_title"; + checkRel2Sql(logicalPlanner, query, expected, + DatabaseProduct.DB2.getDialect()); + } + } // End RelToSqlConverterTest.java
