This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit c3108bc1231b2aa4ba25227d225544fef4576508 Author: Julian Hyde <[email protected]> AuthorDate: Fri Jul 12 14:33:12 2019 -0700 [CALCITE-3191] In JDBC adapter for MySQL, implement Values by generating SELECT without FROM Revisit SqlDialect methods supportsAliasedValues() and hasImplicitTableAlias(); make requiresAliasForFromItems() public (was protected), and add List<String> getSingleRowTableName(). --- .../calcite/rel/rel2sql/RelToSqlConverter.java | 56 +++++++++------- .../apache/calcite/rel/rel2sql/SqlImplementor.java | 38 ++++++++++- .../java/org/apache/calcite/sql/SqlDialect.java | 29 ++++++++- .../calcite/sql/dialect/MysqlSqlDialect.java | 9 +++ .../calcite/sql/dialect/OracleSqlDialect.java | 14 +++- .../calcite/sql/dialect/PostgresqlSqlDialect.java | 2 +- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 74 +++++++++++++++++++--- 7 files changed, 184 insertions(+), 38 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 c6a707d..9981ce7 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 @@ -381,14 +381,12 @@ public class RelToSqlConverter extends SqlImplementor final List<SqlNode> values2 = new ArrayList<>(); final SqlNodeList exprList = exprList(context, tuple); for (Pair<SqlNode, String> value : Pair.zip(exprList, fieldNames)) { - values2.add( - SqlStdOperatorTable.AS.createCall(POS, value.left, - new SqlIdentifier(value.right, POS))); + values2.add(as(value.left, value.right)); } list.add( new SqlSelect(POS, null, new SqlNodeList(values2, POS), - new SqlIdentifier("DUAL", POS), null, null, + getDual(), null, null, null, null, null, null, null)); } if (list.isEmpty()) { @@ -397,15 +395,27 @@ public class RelToSqlConverter extends SqlImplementor // This would return an empty result set with the same number of columns as the field names. final List<SqlNode> nullColumnNames = new ArrayList<>(); for (String fieldName : fieldNames) { - SqlCall nullColumnName = SqlStdOperatorTable.AS.createCall( - POS, SqlLiteral.createNull(POS), - new SqlIdentifier(fieldName, POS)); + SqlCall nullColumnName = as(SqlLiteral.createNull(POS), fieldName); nullColumnNames.add(nullColumnName); } - query = new SqlSelect(POS, null, - new SqlNodeList(nullColumnNames, POS), - new SqlIdentifier("DUAL", POS), createAlwaysFalseCondition(), null, - null, null, null, null, null); + final SqlIdentifier dual = getDual(); + if (dual == null) { + query = new SqlSelect(POS, null, + new SqlNodeList(nullColumnNames, POS), null, null, null, null, + null, null, null, null); + + // Wrap "SELECT 1 AS x" + // as "SELECT * FROM (SELECT 1 AS x) AS t WHERE false" + query = new SqlSelect(POS, null, + new SqlNodeList(ImmutableList.of(SqlIdentifier.star(POS)), POS), + as(query, "t"), createAlwaysFalseCondition(), null, null, + null, null, null, null); + } else { + query = new SqlSelect(POS, null, + new SqlNodeList(nullColumnNames, POS), + dual, createAlwaysFalseCondition(), null, + null, null, null, null, null); + } } else if (list.size() == 1) { query = list.get(0); } else { @@ -434,15 +444,12 @@ public class RelToSqlConverter extends SqlImplementor } query = SqlStdOperatorTable.VALUES.createCall(selects); if (rename) { - final List<SqlNode> list = new ArrayList<>(); - list.add(query); - list.add(new SqlIdentifier("t", POS)); - for (String fieldName : fieldNames) { - list.add(new SqlIdentifier(fieldName, POS)); - } - query = SqlStdOperatorTable.AS.createCall(POS, list); + query = as(query, "t", fieldNames.toArray(new String[0])); } if (isEmpty) { + if (!rename) { + query = as(query, "t"); + } query = new SqlSelect(POS, null, null, query, createAlwaysFalseCondition(), @@ -453,6 +460,14 @@ public class RelToSqlConverter extends SqlImplementor return result(query, clauses, e, null); } + private SqlIdentifier getDual() { + final List<String> names = dialect.getSingleRowTableName(); + if (names == null) { + return null; + } + return new SqlIdentifier(names, POS); + } + private SqlNode createAlwaysFalseCondition() { // Building the select query in the form: // select * from VALUES(NULL,NULL ...) where 1=0 @@ -698,11 +713,6 @@ public class RelToSqlConverter extends SqlImplementor return result(matchRecognize, Expressions.list(Clause.FROM), e, null); } - private SqlCall as(SqlNode e, String alias) { - return SqlStdOperatorTable.AS.createCall(POS, e, - new SqlIdentifier(alias, POS)); - } - @Override public void addSelect(List<SqlNode> selectList, SqlNode node, RelDataType rowType) { String name = rowType.getFieldNames().get(selectList.size()); 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 3371209..cca13f7 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 @@ -123,12 +123,25 @@ public abstract class SqlImplementor { String name = rowType.getFieldNames().get(selectList.size()); String alias = SqlValidatorUtil.getAlias(node, -1); if (alias == null || !alias.equals(name)) { - node = SqlStdOperatorTable.AS.createCall( - POS, node, new SqlIdentifier(name, POS)); + node = as(node, name); } selectList.add(node); } + /** Convenience method for creating column and table aliases. + * + * <p>{@code AS(e, "c")} creates "e AS c"; + * {@code AS(e, "t", "c1", "c2"} creates "e AS t (c1, c2)". */ + protected SqlCall as(SqlNode e, String alias, String... fieldNames) { + final List<SqlNode> operandList = new ArrayList<>(); + operandList.add(e); + operandList.add(new SqlIdentifier(alias, POS)); + for (String fieldName : fieldNames) { + operandList.add(new SqlIdentifier(fieldName, POS)); + } + return SqlStdOperatorTable.AS.createCall(POS, operandList); + } + /** Returns whether a list of expressions projects all fields, in order, * from the input, with the same names. */ public static boolean isStar(List<RexNode> exps, RelDataType inputRowType, @@ -413,10 +426,31 @@ public abstract class SqlImplementor { || ((SqlCall) node).getOperator() == SqlStdOperatorTable.AS || ((SqlCall) node).getOperator() == SqlStdOperatorTable.VALUES) : node; + if (requiresAlias(node)) { + node = as(node, "t"); + } return new SqlSelect(POS, SqlNodeList.EMPTY, null, node, null, null, null, SqlNodeList.EMPTY, null, null, null); } + /** Returns whether we need to add an alias if this node is to be the FROM + * clause of a SELECT. */ + private boolean requiresAlias(SqlNode node) { + if (!dialect.requiresAliasForFromItems()) { + return false; + } + switch (node.getKind()) { + case IDENTIFIER: + return !dialect.hasImplicitTableAlias(); + case AS: + case JOIN: + case EXPLICIT_TABLE: + return false; + default: + return true; + } + } + /** Context for translating a {@link RexNode} expression (within a * {@link RelNode}) into a {@link SqlNode} expression (within a SQL parse * tree). */ 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 237f8b3..d362ff2 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -54,6 +54,7 @@ import java.util.Objects; import java.util.Set; import java.util.function.Supplier; import javax.annotation.Nonnull; +import javax.annotation.Nullable; /** * <code>SqlDialect</code> encapsulates the differences between dialects of SQL. @@ -588,7 +589,20 @@ public class SqlDialect { } // -- behaviors -- - protected boolean requiresAliasForFromItems() { + + /** Whether a sub-query in the FROM clause must have an alias. + * + * <p>For example, in PostgreSQL, this query is legal: + * + * <blockquote>{@code SELECT * FROM (SELECT * FROM Emp) As e}</blockquote> + * + * <p>but remove the alias {@code e} and it is not: + * + * <blockquote>{@code SELECT * FROM (SELECT * FROM Emp)}</blockquote> + * + * <p>In Oracle, both queries are legal. + */ + public boolean requiresAliasForFromItems() { return false; } @@ -979,6 +993,19 @@ public class SqlDialect { return true; } + /** Returns the name of the system table that has precisely one row. + * If there is no such table, returns null, and we will generate SELECT with + * no FROM clause. + * + * <p>For {@code VALUES 1}, + * Oracle returns ["DUAL"] and we generate "SELECT 1 FROM DUAL"; + * MySQL returns null and we generate "SELECT 1". + */ + @Experimental + public @Nullable List<String> getSingleRowTableName() { + return null; + } + /** * Copies settings from this dialect into a parser configuration. * diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java index 742bd02..2c55466 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java @@ -72,6 +72,15 @@ public class MysqlSqlDialect extends SqlDialect { return false; } + @Override public boolean requiresAliasForFromItems() { + return true; + } + + public boolean supportsAliasedValues() { + // MySQL supports VALUES only in INSERT; not in a FROM clause + return false; + } + @Override public void unparseOffsetFetch(SqlWriter writer, SqlNode offset, SqlNode fetch) { unparseFetchUsingLimit(writer, offset, fetch); diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java index 14c72da..4217005 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java @@ -38,6 +38,10 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.SqlTypeName; +import com.google.common.collect.ImmutableList; + +import java.util.List; + /** * A <code>SqlDialect</code> implementation for the Oracle database. */ @@ -115,19 +119,23 @@ public class OracleSqlDialect extends SqlDialect { @Override public void unparseDateTimeLiteral(SqlWriter writer, SqlAbstractDateTimeLiteral literal, int leftPrec, int rightPrec) { if (literal instanceof SqlTimestampLiteral) { - writer.literal("TO_TIMESTAMP ('" + writer.literal("TO_TIMESTAMP('" + literal.toFormattedString() + "', 'YYYY-MM-DD HH24:MI:SS.FF')"); } else if (literal instanceof SqlDateLiteral) { - writer.literal("TO_DATE ('" + writer.literal("TO_DATE('" + literal.toFormattedString() + "', 'YYYY-MM-DD')"); } else if (literal instanceof SqlTimeLiteral) { - writer.literal("TO_TIME ('" + writer.literal("TO_TIME('" + literal.toFormattedString() + "', 'HH24:MI:SS.FF')"); } else { super.unparseDateTimeLiteral(writer, literal, leftPrec, rightPrec); } } + @Override public List<String> getSingleRowTableName() { + return ImmutableList.of("DUAL"); + } + @Override public void unparseCall(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) { diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java index 987c944..8a4cc77 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java @@ -91,7 +91,7 @@ public class PostgresqlSqlDialect extends SqlDialect { -1, -1, null, null, SqlParserPos.ZERO); } - @Override protected boolean requiresAliasForFromItems() { + @Override public boolean requiresAliasForFromItems() { return true; } 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 eea7962..f013e1f 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 @@ -18,12 +18,13 @@ package org.apache.calcite.rel.rel2sql; import org.apache.calcite.config.NullCollation; import org.apache.calcite.plan.RelOptPlanner; +import org.apache.calcite.plan.RelOptRule; import org.apache.calcite.plan.RelTraitDef; import org.apache.calcite.plan.hep.HepPlanner; -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.core.JoinRelType; +import org.apache.calcite.rel.rules.PruneEmptyRules; import org.apache.calcite.rel.rules.UnionMergeRule; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; @@ -61,6 +62,7 @@ import org.apache.calcite.tools.RelBuilder; import org.apache.calcite.tools.RuleSet; import org.apache.calcite.tools.RuleSets; import org.apache.calcite.util.TestUtil; +import org.apache.calcite.util.Util; import com.google.common.collect.ImmutableList; import com.google.common.collect.ImmutableMap; @@ -735,6 +737,27 @@ public class RelToSqlConverterTest { sql(query).ok(expected); } + @Test public void testHaving4() { + final String query = "select \"product_id\"\n" + + "from (\n" + + " select \"product_id\", avg(\"gross_weight\") as agw\n" + + " from \"product\"\n" + + " where \"net_weight\" < 100\n" + + " group by \"product_id\")\n" + + "where agw > 50\n" + + "group by \"product_id\"\n" + + "having avg(agw) > 60\n"; + final String expected = "SELECT \"product_id\"\n" + + "FROM (SELECT \"product_id\", AVG(\"gross_weight\") AS \"AGW\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "WHERE \"net_weight\" < 100\n" + + "GROUP BY \"product_id\"\n" + + "HAVING AVG(\"gross_weight\") > 50) AS \"t2\"\n" + + "GROUP BY \"product_id\"\n" + + "HAVING AVG(\"AGW\") > 60"; + sql(query).ok(expected); + } + @Test public void testSelectQueryWithOrderByClause() { String query = "select \"product_id\" from \"product\" order by \"net_weight\""; final String expected = "SELECT \"product_id\", \"net_weight\"\n" @@ -1662,11 +1685,9 @@ public class RelToSqlConverterTest { + "SELECT \"product_class_id\" AS \"PRODUCT_ID\"\n" + "FROM \"foodmart\".\"product_class\""; - final HepProgram program = - new HepProgramBuilder().addRuleClass(UnionMergeRule.class).build(); final RuleSet rules = RuleSets.ofList(UnionMergeRule.INSTANCE); sql(query) - .optimize(rules, new HepPlanner(program)) + .optimize(rules, null) .ok(expected); } @@ -3180,6 +3201,10 @@ public class RelToSqlConverterTest { final String expectedHsqldb = "SELECT a\n" + "FROM (VALUES (1, 'x '),\n" + " (2, 'yy')) AS t (a, b)"; + final String expectedMysql = "SELECT `a`\n" + + "FROM (SELECT 1 AS `a`, 'x ' AS `b`\n" + + "UNION ALL\n" + + "SELECT 2 AS `a`, 'yy' AS `b`) AS `t`"; final String expectedPostgresql = "SELECT \"a\"\n" + "FROM (VALUES (1, 'x '),\n" + " (2, 'yy')) AS \"t\" (\"a\", \"b\")"; @@ -3194,6 +3219,8 @@ public class RelToSqlConverterTest { sql(sql) .withHsqldb() .ok(expectedHsqldb) + .withMysql() + .ok(expectedMysql) .withPostgresql() .ok(expectedPostgresql) .withOracle() @@ -3204,6 +3231,31 @@ public class RelToSqlConverterTest { .ok(expectedRedshift); } + @Test public void testValuesEmpty() { + final String sql = "select *\n" + + "from (values (1, 'a'), (2, 'bb')) as t(x, y)\n" + + "limit 0"; + final RuleSet rules = + RuleSets.ofList(PruneEmptyRules.SORT_FETCH_ZERO_INSTANCE); + final String expectedMysql = "SELECT *\n" + + "FROM (SELECT NULL AS `X`, NULL AS `Y`) AS `t`\n" + + "WHERE 1 = 0"; + final String expectedOracle = "SELECT NULL \"X\", NULL \"Y\"\n" + + "FROM \"DUAL\"\n" + + "WHERE 1 = 0"; + final String expectedPostgresql = "SELECT *\n" + + "FROM (VALUES (NULL, NULL)) AS \"t\" (\"X\", \"Y\")\n" + + "WHERE 1 = 0"; + sql(sql) + .optimize(rules, null) + .withMysql() + .ok(expectedMysql) + .withOracle() + .ok(expectedOracle) + .withPostgresql() + .ok(expectedPostgresql); + } + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-2118";>[CALCITE-2118] * RelToSqlConverter should only generate "*" if field names match</a>. */ @@ -3603,7 +3655,7 @@ public class RelToSqlConverterTest { @Test public void testDateLiteralOracle() { String query = "SELECT DATE '1978-05-02' FROM \"employee\""; - String expected = "SELECT TO_DATE ('1978-05-02', 'YYYY-MM-DD')\n" + String expected = "SELECT TO_DATE('1978-05-02', 'YYYY-MM-DD')\n" + "FROM \"foodmart\".\"employee\""; sql(query) .withOracle() @@ -3612,7 +3664,8 @@ public class RelToSqlConverterTest { @Test public void testTimestampLiteralOracle() { String query = "SELECT TIMESTAMP '1978-05-02 12:34:56.78' FROM \"employee\""; - String expected = "SELECT TO_TIMESTAMP ('1978-05-02 12:34:56.78', 'YYYY-MM-DD HH24:MI:SS.FF')\n" + String expected = "SELECT TO_TIMESTAMP('1978-05-02 12:34:56.78'," + + " 'YYYY-MM-DD HH24:MI:SS.FF')\n" + "FROM \"foodmart\".\"employee\""; sql(query) .withOracle() @@ -3621,7 +3674,7 @@ public class RelToSqlConverterTest { @Test public void testTimeLiteralOracle() { String query = "SELECT TIME '12:34:56.78' FROM \"employee\""; - String expected = "SELECT TO_TIME ('12:34:56.78', 'HH24:MI:SS.FF')\n" + String expected = "SELECT TO_TIME('12:34:56.78', 'HH24:MI:SS.FF')\n" + "FROM \"foodmart\".\"employee\""; sql(query) .withOracle() @@ -3798,7 +3851,12 @@ public class RelToSqlConverterTest { return new Sql(schema, sql, dialect, config, FlatLists.append(transforms, r -> { Program program = Programs.of(ruleSet); - return program.run(relOptPlanner, r, r.getTraitSet(), + final RelOptPlanner p = + Util.first(relOptPlanner, + new HepPlanner( + new HepProgramBuilder().addRuleClass(RelOptRule.class) + .build())); + return program.run(p, r, r.getTraitSet(), ImmutableList.of(), ImmutableList.of()); })); }
