You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/12/15 21:42:56 UTC
calcite git commit: [CALCITE-1010] FETCH/LIMIT and OFFSET in
RelToSqlConverter (Amogh Margoor)
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 <am...@qubole.com>
Authored: Wed Dec 9 03:23:32 2015 -0500
Committer: Julian Hyde <jh...@apache.org>
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