Repository: calcite Updated Branches: refs/heads/master f12aa72ec -> 82fd259c6
[CALCITE-1010] FETCH/LIMIT and OFFSET in RelToSqlConverter (Amogh Margoor) Enhance Dialect and SqlWriter to support FETCH/LIMIT and OFFSET. Add Redshift dialect. Close apache/calcite#179 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/82fd259c Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/82fd259c Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/82fd259c Branch: refs/heads/master Commit: 82fd259c64e94eb58bf163183871e1c9d6332b62 Parents: f12aa72 Author: Amogh Margoor <[email protected]> Authored: Wed Dec 9 03:23:32 2015 -0500 Committer: Julian Hyde <[email protected]> Committed: Tue Dec 15 11:46:25 2015 -0800 ---------------------------------------------------------------------- .../calcite/rel/rel2sql/RelToSqlConverter.java | 21 ++++- .../java/org/apache/calcite/sql/SqlDialect.java | 80 +++++++++++++------- .../apache/calcite/sql/SqlSelectOperator.java | 1 + .../java/org/apache/calcite/sql/SqlWriter.java | 7 +- .../calcite/sql/pretty/SqlPrettyWriter.java | 47 ++++++++++++ .../rel/rel2sql/RelToSqlConverterTest.java | 63 ++++++++++++--- 6 files changed, 176 insertions(+), 43 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/82fd259c/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 9a7f359..4bfd402 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 @@ -260,14 +260,27 @@ public class RelToSqlConverter extends SqlImplementor /** @see #dispatch */ public Result visit(Sort e) { - final Result x = visitChild(0, e.getInput()); - final Builder builder = x.builder(e, Clause.ORDER_BY); + Result x = visitChild(0, e.getInput()); + Builder builder = x.builder(e, Clause.ORDER_BY); List<SqlNode> orderByList = Expressions.list(); for (RelFieldCollation field : e.getCollation().getFieldCollations()) { builder.addOrderItem(orderByList, field); } - builder.setOrderBy(new SqlNodeList(orderByList, POS)); - return builder.result(); + if (!orderByList.isEmpty()) { + builder.setOrderBy(new SqlNodeList(orderByList, POS)); + x = builder.result(); + } + if (e.fetch != null) { + builder = x.builder(e, Clause.FETCH); + builder.setFetch(builder.context.toSql(null, e.fetch)); + x = builder.result(); + } + if (e.offset != null) { + builder = x.builder(e, Clause.OFFSET); + builder.setOffset(builder.context.toSql(null, e.offset)); + x = builder.result(); + } + return x; } /** @see #dispatch */ http://git-wip-us.apache.org/repos/asf/calcite/blob/82fd259c/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 bacf239..88c6d63 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -183,45 +183,50 @@ public class SqlDialect { public static DatabaseProduct getProduct( String productName, String productVersion) { - final String upperProductName = productName.toUpperCase(); - if (productName.equals("ACCESS")) { + final String upperProductName = productName.toUpperCase().trim(); + switch (upperProductName) { + case "ACCESS": return DatabaseProduct.ACCESS; - } else if (upperProductName.trim().equals("APACHE DERBY")) { + case "APACHE DERBY": return DatabaseProduct.DERBY; - } else if (upperProductName.trim().equals("DBMS:CLOUDSCAPE")) { + case "DBMS:CLOUDSCAPE": return DatabaseProduct.DERBY; - } else if (productName.startsWith("DB2")) { - return DatabaseProduct.DB2; - } else if (upperProductName.contains("FIREBIRD")) { - return DatabaseProduct.FIREBIRD; - } else if (productName.equals("Hive")) { + case "HIVE": return DatabaseProduct.HIVE; - } else if (productName.startsWith("Informix")) { - return DatabaseProduct.INFORMIX; - } else if (upperProductName.equals("INGRES")) { + case "INGRES": return DatabaseProduct.INGRES; - } else if (productName.equals("Interbase")) { + case "INTERBASE": return DatabaseProduct.INTERBASE; - } else if (upperProductName.equals("LUCIDDB")) { + case "LUCIDDB": return DatabaseProduct.LUCIDDB; - } else if (upperProductName.contains("SQL SERVER")) { - return DatabaseProduct.MSSQL; - } else if (upperProductName.contains("PARACCEL")) { - return DatabaseProduct.PARACCEL; - } else if (productName.equals("Oracle")) { + case "ORACLE": return DatabaseProduct.ORACLE; - } else if (productName.equals("Phoenix")) { + case "PHOENIX": return DatabaseProduct.PHOENIX; - } else if (upperProductName.contains("POSTGRE")) { - return DatabaseProduct.POSTGRESQL; - } else if (upperProductName.contains("NETEZZA")) { - return DatabaseProduct.NETEZZA; - } else if (upperProductName.equals("MYSQL (INFOBRIGHT)")) { + case "MYSQL (INFOBRIGHT)": return DatabaseProduct.INFOBRIGHT; - } else if (upperProductName.equals("MYSQL")) { + case "MYSQL": return DatabaseProduct.MYSQL; + case "REDSHIFT": + return DatabaseProduct.REDSHIFT; + } + // Now the fuzzy matches. + if (productName.startsWith("DB2")) { + return DatabaseProduct.DB2; + } else if (upperProductName.contains("FIREBIRD")) { + return DatabaseProduct.FIREBIRD; + } else if (productName.startsWith("Informix")) { + return DatabaseProduct.INFORMIX; + } else if (upperProductName.contains("NETEZZA")) { + return DatabaseProduct.NETEZZA; + } else if (upperProductName.contains("PARACCEL")) { + return DatabaseProduct.PARACCEL; } else if (productName.startsWith("HP Neoview")) { return DatabaseProduct.NEOVIEW; + } else if (upperProductName.contains("POSTGRE")) { + return DatabaseProduct.POSTGRESQL; + } else if (upperProductName.contains("SQL SERVER")) { + return DatabaseProduct.MSSQL; } else if (upperProductName.contains("SYBASE")) { return DatabaseProduct.SYBASE; } else if (upperProductName.contains("TERADATA")) { @@ -466,6 +471,24 @@ public class SqlDialect { } } + /** + * Returns whether the dialect supports OFFSET/FETCH clauses + * introduced by SQL:2008, for instance + * {@code OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY}. + * If false, we assume that the dialect supports the alternative syntax + * {@code LIMIT 20 OFFSET 10}. + */ + public boolean supportsOffsetFetch() { + switch (databaseProduct) { + case MYSQL: + case HIVE: + case REDSHIFT: + return false; + default: + return true; + } + } + /** Returns how NULL values are sorted if an ORDER BY item does not contain * NULLS ASCENDING or NULLS DESCENDING. */ public NullCollation getNullCollation() { @@ -575,7 +598,12 @@ public class SqlDialect { HSQLDB("Hsqldb", null, NullCollation.HIGH), VERTICA("Vertica", "\"", NullCollation.HIGH), SQLSTREAM("SQLstream", "\"", NullCollation.HIGH), + + /** Paraccel, now called Actian Matrix. Redshift is based on this, so + * presumably the dialect capabilities are similar. */ PARACCEL("Paraccel", "\"", NullCollation.HIGH), + REDSHIFT("Redshift", "\"", NullCollation.HIGH), + /** * Placeholder for the unknown database. * http://git-wip-us.apache.org/repos/asf/calcite/blob/82fd259c/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java index 888a070..6a09c92 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java @@ -236,6 +236,7 @@ public class SqlSelectOperator extends SqlOperator { unparseListClause(writer, select.orderBy); writer.endList(orderFrame); } + writer.fetchOffset(select.fetch, select.offset); writer.endList(selectFrame); } http://git-wip-us.apache.org/repos/asf/calcite/blob/82fd259c/core/src/main/java/org/apache/calcite/sql/SqlWriter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlWriter.java b/core/src/main/java/org/apache/calcite/sql/SqlWriter.java index ac03856..8e72026 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlWriter.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlWriter.java @@ -310,6 +310,11 @@ public interface SqlWriter { void identifier(String name); /** + * Prints the OFFSET/FETCH clause. + */ + void fetchOffset(SqlNode fetch, SqlNode offset); + + /** * Prints a new line, and indents. */ void newlineAndIndent(); @@ -456,7 +461,7 @@ public interface SqlWriter { * {@link SqlWriter#endList(Frame)}. If other code starts a frame in the mean * time, the sub-frame is put onto a stack. */ - public interface Frame { + interface Frame { } /** Frame type. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/82fd259c/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java b/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java index 5637f01..2be197e 100644 --- a/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java +++ b/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java @@ -902,6 +902,53 @@ public class SqlPrettyWriter implements SqlWriter { setNeedWhitespace(true); } + public void fetchOffset(SqlNode fetch, SqlNode offset) { + if (fetch == null && offset == null) { + return; + } + if (dialect.supportsOffsetFetch()) { + if (offset != null) { + this.newlineAndIndent(); + final Frame offsetFrame = + this.startList(FrameTypeEnum.OFFSET); + this.keyword("OFFSET"); + offset.unparse(this, -1, -1); + this.keyword("ROWS"); + this.endList(offsetFrame); + } + if (fetch != null) { + this.newlineAndIndent(); + final Frame fetchFrame = + this.startList(FrameTypeEnum.FETCH); + this.keyword("FETCH"); + this.keyword("NEXT"); + fetch.unparse(this, -1, -1); + this.keyword("ROWS"); + this.keyword("ONLY"); + this.endList(fetchFrame); + } + } else { + // Dialect does not support OFFSET/FETCH clause. + // Assume it uses LIMIT/OFFSET. + if (fetch != null) { + this.newlineAndIndent(); + final Frame fetchFrame = + this.startList(FrameTypeEnum.FETCH); + this.keyword("LIMIT"); + fetch.unparse(this, -1, -1); + this.endList(fetchFrame); + } + if (offset != null) { + this.newlineAndIndent(); + final Frame offsetFrame = + this.startList(FrameTypeEnum.OFFSET); + this.keyword("OFFSET"); + offset.unparse(this, -1, -1); + this.endList(offsetFrame); + } + } + } + public Frame startFunCall(String funName) { keyword(funName); setNeedWhitespace(false); http://git-wip-us.apache.org/repos/asf/calcite/blob/82fd259c/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 7514791..1d26eb8 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 @@ -28,12 +28,11 @@ import org.apache.calcite.tools.Frameworks; import org.apache.calcite.tools.Planner; import org.apache.calcite.tools.Program; -import org.junit.Ignore; import org.junit.Test; import java.util.List; -import static org.hamcrest.CoreMatchers.equalTo; +import static org.hamcrest.CoreMatchers.is; import static org.junit.Assert.assertThat; import static org.junit.Assert.assertTrue; @@ -43,28 +42,32 @@ import static org.junit.Assert.assertTrue; public class RelToSqlConverterTest { private Planner logicalPlanner = getPlanner(null); - private void checkRel2Sql(Planner planner, String query, String expectedQeury) { + private void checkRel2Sql(Planner planner, String query, String expectedQuery, + SqlDialect dialect) { try { SqlNode parse = planner.parse(query); SqlNode validate = planner.validate(parse); RelNode rel = planner.rel(validate).rel; final RelToSqlConverter converter = - new RelToSqlConverter(SqlDialect.CALCITE); + new RelToSqlConverter(dialect); final SqlNode sqlNode = converter.visitChild(0, rel).asQuery(); - assertThat(sqlNode.toSqlString(SqlDialect.CALCITE).getSql(), - equalTo(expectedQeury)); + assertThat(sqlNode.toSqlString(dialect).getSql(), + is(expectedQuery)); } catch (Exception e) { assertTrue("Parsing failed throwing error: " + e.getMessage(), false); } } + private void checkRel2Sql(Planner planner, String query, String expectedQuery) { + checkRel2Sql(planner, query, expectedQuery, SqlDialect.CALCITE); + } + private Planner getPlanner(List<RelTraitDef> traitDefs, Program... programs) { return getPlanner(traitDefs, SqlParser.Config.DEFAULT, programs); } private Planner getPlanner(List<RelTraitDef> traitDefs, - SqlParser.Config parserConfig, - Program... programs) { + SqlParser.Config parserConfig, Program... programs) { final SchemaPlus rootSchema = Frameworks.createRootSchema(true); final FrameworkConfig config = Frameworks.newConfigBuilder() .parserConfig(parserConfig) @@ -81,10 +84,9 @@ public class RelToSqlConverterTest { String query = "select * from \"product\""; checkRel2Sql(this.logicalPlanner, query, - "SELECT *\nFROM \"foodmart\".\"product\""); + "SELECT *\nFROM \"foodmart\".\"product\""); } - @Test public void testSimpleSelectQueryFromProductTable() { String query = "select \"product_id\", \"product_class_id\" from \"product\""; @@ -268,15 +270,52 @@ public class RelToSqlConverterTest { + "ORDER BY \"net_weight\", \"gross_weight\" DESC, \"low_fat\""); } - @Ignore @Test public void testSelectQueryWithLimitClause() { String query = "select \"product_id\" from \"product\" limit 100 offset 10"; checkRel2Sql(this.logicalPlanner, query, + "SELECT product_id\n" + + "FROM foodmart.product\n" + + "LIMIT 100\nOFFSET 10", + SqlDialect.DatabaseProduct.HIVE.getDialect()); + } + + @Test + public void testSelectQueryWithLimitClauseWithoutOrder() { + String query = "select \"product_id\" from \"product\" limit 100 offset 10"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "OFFSET 10 ROWS\n" + + "FETCH NEXT 100 ROWS ONLY"); + } + + @Test + public void testSelectQueryWithLimitOffsetClause() { + String query = "select \"product_id\" from \"product\" order by \"net_weight\" asc" + + " limit 100 offset 10"; + checkRel2Sql(this.logicalPlanner, + query, + "SELECT \"product_id\", \"net_weight\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "ORDER BY \"net_weight\"\n" + + "OFFSET 10 ROWS\n" + + "FETCH NEXT 100 ROWS ONLY"); + } + + @Test + 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, "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" - + "LIMIT 100 OFFSET 10"); + + "ORDER BY \"product_id\"\n" + + "OFFSET 10 ROWS\n" + + "FETCH NEXT 100 ROWS ONLY"); } @Test
