[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 <[email protected]> Authored: Fri Jul 14 14:58:44 2017 +0800 Committer: Julian Hyde <[email protected]> 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.
