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 2017/07/18 17:10:44 UTC
[4/4] calcite git commit: [CALCITE-1886] Support "LIMIT [offset,
] row_count", per MySQL (Kaiwang Chen)
[CALCITE-1886] Support "LIMIT [offset,] row_count", per MySQL (Kaiwang Chen)
Close apache/calcite#498
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/bdb953fa
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/bdb953fa
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/bdb953fa
Branch: refs/heads/master
Commit: bdb953fa06d6549039c2967404b0194ab7ae6a8c
Parents: 7ef986e
Author: kaiwang.ckw <ka...@alibaba-inc.com>
Authored: Fri Jul 14 14:58:44 2017 +0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Jul 17 23:05:38 2017 -0700
----------------------------------------------------------------------
core/src/main/codegen/templates/Parser.jj | 22 ++++++++-
.../apache/calcite/runtime/CalciteResource.java | 3 ++
.../sql/validate/SqlAbstractConformance.java | 4 ++
.../calcite/sql/validate/SqlConformance.java | 16 +++++++
.../sql/validate/SqlConformanceEnum.java | 10 ++++
.../calcite/runtime/CalciteResource.properties | 1 +
.../calcite/sql/parser/SqlParserTest.java | 48 ++++++++++++++++++++
site/_docs/reference.md | 6 ++-
8 files changed, 108 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/bdb953fa/core/src/main/codegen/templates/Parser.jj
----------------------------------------------------------------------
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 67d1a1b..a2789d0 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -575,6 +575,12 @@ JAVACODE boolean matchesPrefix(int[] seq, int[][] prefixes)
* [ OFFSET start ]</pre>
* </blockquote>
*
+ * <p>MySQL syntax for limit:
+ *
+ * <blockquote><pre>
+ * [ LIMIT { count | start, count } ]</pre>
+ * </blockquote>
+ *
* <p>SQL:2008 syntax for limit:
*
* <blockquote><pre>
@@ -600,11 +606,25 @@ SqlNode OrderedQueryOrExpr(ExprContext exprContext) :
]
[
// Postgres-style syntax. "LIMIT ... OFFSET ..."
- <LIMIT> ( count = UnsignedNumericLiteral() | <ALL> )
+ <LIMIT>
+ (
+ // MySQL-style syntax. "LIMIT start, count"
+ start = UnsignedNumericLiteral()
+ <COMMA> count = UnsignedNumericLiteral() {
+ if (!this.conformance.isLimitStartCountAllowed()) {
+ throw new ParseException(RESOURCE.limitStartCountNotAllowed().str());
+ }
+ }
+ |
+ count = UnsignedNumericLiteral()
+ |
+ <ALL>
+ )
]
[
// ROW or ROWS is required in SQL:2008 but we make it optional
// because it is not present in Postgres-style syntax.
+ // If you specify both LIMIT start and OFFSET, OFFSET wins.
<OFFSET> start = UnsignedNumericLiteral() [ <ROW> | <ROWS> ]
]
[
http://git-wip-us.apache.org/repos/asf/calcite/blob/bdb953fa/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index f79fa36..84f21e1 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -34,6 +34,9 @@ public interface CalciteResource {
@BaseMessage("Bang equal ''!='' is not allowed under the current SQL conformance level")
ExInst<CalciteException> bangEqualNotAllowed();
+ @BaseMessage("''LIMIT start, count'' is not allowed under the current SQL conformance level")
+ ExInst<CalciteException> limitStartCountNotAllowed();
+
@BaseMessage("APPLY operator is not allowed under the current SQL conformance level")
ExInst<CalciteException> applyNotAllowed();
http://git-wip-us.apache.org/repos/asf/calcite/blob/bdb953fa/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index 78f2df3..4cd8bb8 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -74,6 +74,10 @@ public abstract class SqlAbstractConformance implements SqlConformance {
public boolean allowExtend() {
return SqlConformanceEnum.DEFAULT.allowExtend();
}
+
+ public boolean isLimitStartCountAllowed() {
+ return SqlConformanceEnum.DEFAULT.isLimitStartCountAllowed();
+ }
}
// End SqlAbstractConformance.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/bdb953fa/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index 91238d3..9f8e3c0 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -269,6 +269,22 @@ public interface SqlConformance {
*/
boolean allowExtend();
+ /**
+ * Whether to allow the SQL syntax "{@code LIMIT start, count}".
+ *
+ * <p>The equivalent syntax in standard SQL is
+ * "{@code OFFSET start ROW FETCH FIRST count ROWS ONLY}",
+ * and in PostgreSQL "{@code LIMIT count OFFSET start}".
+ *
+ * <p>MySQL and CUBRID allow this behavior.
+ *
+ * <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#LENIENT},
+ * {@link SqlConformanceEnum#MYSQL_5};
+ * false otherwise.
+ */
+
+ boolean isLimitStartCountAllowed();
}
// End SqlConformance.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/bdb953fa/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index 74d31f1..754e24e 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -208,6 +208,16 @@ public enum SqlConformanceEnum implements SqlConformance {
return false;
}
}
+
+ public boolean isLimitStartCountAllowed() {
+ switch (this) {
+ case LENIENT:
+ case MYSQL_5:
+ return true;
+ default:
+ return false;
+ }
+ }
}
// End SqlConformanceEnum.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/bdb953fa/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
----------------------------------------------------------------------
diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 8451ed3..360fb6e 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -18,6 +18,7 @@
#
ParserContext=line {0,number,#}, column {1,number,#}
BangEqualNotAllowed=Bang equal ''!='' is not allowed under the current SQL conformance level
+LimitStartCountNotAllowed=''LIMIT start, count'' is not allowed under the current SQL conformance level
ApplyNotAllowed=APPLY operator is not allowed under the current SQL conformance level
IllegalLiteral=Illegal {0} literal {1}: {2}
IdentifierTooLong=Length of identifier ''{0}'' must be less than or equal to {1,number,#} characters
http://git-wip-us.apache.org/repos/asf/calcite/blob/bdb953fa/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index e70e291..b9db101 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -2531,6 +2531,54 @@ public class SqlParserTest {
.ok(expected);
}
+ @Test public void testLimitStartCount() {
+ conformance = SqlConformanceEnum.DEFAULT;
+ final String error = "'LIMIT start, count' is not allowed under the "
+ + "current SQL conformance level";
+ sql("select a from foo limit 1,2")
+ .fails(error);
+
+ // "limit all" is equivalent to no limit
+ final String expected0 = "SELECT `A`\n"
+ + "FROM `FOO`";
+ sql("select a from foo limit all")
+ .ok(expected0);
+
+ final String expected1 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "ORDER BY `X`";
+ sql("select a from foo order by x limit all")
+ .ok(expected1);
+
+ conformance = SqlConformanceEnum.LENIENT;
+ final String expected2 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "OFFSET 2 ROWS\n"
+ + "FETCH NEXT 3 ROWS ONLY";
+ sql("select a from foo limit 2,3")
+ .ok(expected2);
+
+ // "offset 4" overrides the earlier "2"
+ final String expected3 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "OFFSET 4 ROWS\n"
+ + "FETCH NEXT 3 ROWS ONLY";
+ sql("select a from foo limit 2,3 offset 4")
+ .ok(expected3);
+
+ // "fetch next 4" overrides the earlier "limit 3"
+ final String expected4 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "OFFSET 2 ROWS\n"
+ + "FETCH NEXT 4 ROWS ONLY";
+ sql("select a from foo limit 2,3 fetch next 4 rows only")
+ .ok(expected4);
+
+ // "limit start, all" is not valid
+ sql("select a from foo limit 2, ^all^")
+ .fails("(?s).*Encountered \"all\" at line 1.*");
+ }
+
@Test public void testSqlInlineComment() {
check(
"select 1 from t --this is a comment\n",
http://git-wip-us.apache.org/repos/asf/calcite/blob/bdb953fa/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 4756468..9fe6876 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -153,7 +153,7 @@ query:
| query INTERSECT [ ALL | DISTINCT ] query
}
[ ORDER BY orderItem [, orderItem ]* ]
- [ LIMIT { count | ALL } ]
+ [ LIMIT [ start, ] { count | ALL } ]
[ OFFSET start { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ]
@@ -283,6 +283,10 @@ but is not standard SQL and is only allowed in certain
CROSS APPLY and OUTER APPLY are only allowed in certain
[conformance levels]({{ site.apiRoot }}/org/apache/calcite/sql/validate/SqlConformance.html#isApplyAllowed--).
+"LIMIT start, count" is equivalent to "LIMIT count OFFSET start"
+but is only allowed in certain
+[conformance levels]({{ site.apiRoot }}/org/apache/calcite/sql/validate/SqlConformance.html#isLimitStartCountAllowed--).
+
## Keywords
The following is a list of SQL keywords.