This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 161e220384dfbd85f896a95cac40cca23141583e Author: macroguo <[email protected]> AuthorDate: Mon Aug 7 21:46:24 2023 +0800 [CALCITE-5184] Support "LIMIT start, ALL" in MySQL conformance, equivalent to "OFFSET start" Add tests for LIMIT to sort.iq; add 'scott-mysql' and 'scott-lenient' Quidem data sources, similar to the 'scott' data source but with particular conformances. (Julian Hyde) Close apache/calcite#3352 Co-authored-by: Julian Hyde <[email protected]> Co-authored-by: macroguo <[email protected]> --- core/src/main/codegen/templates/Parser.jj | 16 +- .../apache/calcite/runtime/CalciteResource.java | 4 +- .../calcite/runtime/CalciteResource.properties | 2 +- .../org/apache/calcite/test/CoreQuidemTest.java | 21 +++ core/src/test/resources/sql/sort.iq | 165 +++++++++++++++++++++ site/_docs/reference.md | 2 +- .../apache/calcite/sql/parser/SqlParserTest.java | 45 +++++- 7 files changed, 240 insertions(+), 15 deletions(-) diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 0a11b28426..6a4991fc1b 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -758,18 +758,28 @@ void FetchClause(SqlNode[] offsetFetch) : */ void LimitClause(Span s, SqlNode[] offsetFetch) : { + final String error; } { // Postgres-style syntax. "LIMIT ... OFFSET ..." <LIMIT> { s.add(this); } ( - // MySQL-style syntax. "LIMIT start, count" + // MySQL-style syntax. "LIMIT start, count" or "LIMIT start, ALL" LOOKAHEAD(2) offsetFetch[0] = UnsignedNumericLiteralOrParam() - <COMMA> offsetFetch[1] = UnsignedNumericLiteralOrParam() { + <COMMA> + ( + offsetFetch[1] = UnsignedNumericLiteralOrParam() { + error = "count"; + } + | + <ALL> { + error = "ALL"; + } + ) { if (!this.conformance.isLimitStartCountAllowed()) { throw SqlUtil.newContextException(s.end(this), - RESOURCE.limitStartCountNotAllowed()); + RESOURCE.limitStartCountOrAllNotAllowed(error)); } } | 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 1b267bc598..c09c309ccc 100644 --- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java +++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java @@ -39,8 +39,8 @@ public interface CalciteResource { @BaseMessage("Percent remainder ''%'' is not allowed under the current SQL conformance level") ExInst<CalciteException> percentRemainderNotAllowed(); - @BaseMessage("''LIMIT start, count'' is not allowed under the current SQL conformance level") - ExInst<CalciteException> limitStartCountNotAllowed(); + @BaseMessage("''LIMIT start, {0}'' is not allowed under the current SQL conformance level") + ExInst<CalciteException> limitStartCountOrAllNotAllowed(String a0); @BaseMessage("''OFFSET start LIMIT count'' is not allowed under the current SQL conformance level") ExInst<CalciteException> offsetLimitNotAllowed(); 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 ca9003edfd..8dfd6fb9f3 100644 --- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties +++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties @@ -21,7 +21,7 @@ ParserContext=line {0,number,#}, column {1,number,#} BangEqualNotAllowed=Bang equal ''!='' is not allowed under the current SQL conformance level PercentRemainderNotAllowed=Percent remainder ''%'' is not allowed under the current SQL conformance level -LimitStartCountNotAllowed=''LIMIT start, count'' is not allowed under the current SQL conformance level +LimitStartCountOrAllNotAllowed=''LIMIT start, {0}'' is not allowed under the current SQL conformance level OffsetLimitNotAllowed=''OFFSET start LIMIT count'' is not allowed under the current SQL conformance level ApplyNotAllowed=APPLY operator is not allowed under the current SQL conformance level ValueNotAllowed=VALUE is not allowed under the current SQL conformance level diff --git a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java index 6814907db4..9f498b7df2 100644 --- a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java +++ b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java @@ -17,6 +17,7 @@ package org.apache.calcite.test; import org.apache.calcite.config.CalciteConnectionProperty; +import org.apache.calcite.sql.validate.SqlConformanceEnum; import net.hydromatic.quidem.Quidem; @@ -64,6 +65,26 @@ class CoreQuidemTest extends QuidemTest { ExtensionDdlExecutor.class.getName() + "#PARSER_FACTORY") .with(CalciteAssert.Config.SCOTT) .connect(); + case "scott-lenient": + // Same as "scott", but uses LENIENT conformance. + // TODO: add a way to change conformance without defining a new + // connection + return CalciteAssert.that() + .with(CalciteConnectionProperty.PARSER_FACTORY, + ExtensionDdlExecutor.class.getName() + "#PARSER_FACTORY") + .with(CalciteConnectionProperty.CONFORMANCE, + SqlConformanceEnum.LENIENT) + .with(CalciteAssert.Config.SCOTT) + .connect(); + case "scott-mysql": + // Same as "scott", but uses MySQL conformance. + return CalciteAssert.that() + .with(CalciteConnectionProperty.PARSER_FACTORY, + ExtensionDdlExecutor.class.getName() + "#PARSER_FACTORY") + .with(CalciteConnectionProperty.CONFORMANCE, + SqlConformanceEnum.MYSQL_5) + .with(CalciteAssert.Config.SCOTT) + .connect(); default: return super.connect(name, reference); } diff --git a/core/src/test/resources/sql/sort.iq b/core/src/test/resources/sql/sort.iq index 38ce22de5b..4e11362b24 100644 --- a/core/src/test/resources/sql/sort.iq +++ b/core/src/test/resources/sql/sort.iq @@ -138,6 +138,171 @@ EnumerableSort(sort0=[$0], sort1=[$1], dir0=[DESC], dir1=[ASC]) EnumerableTableScan(table=[[scott, DEPT]]) !plan +# Return the 2nd and 3rd department +select * +from dept +order by deptno limit 2 offset 1; ++--------+----------+---------+ +| DEPTNO | DNAME | LOC | ++--------+----------+---------+ +| 20 | RESEARCH | DALLAS | +| 30 | SALES | CHICAGO | ++--------+----------+---------+ +(2 rows) + +!ok + +# Return the 2nd and 3rd department (using ansi FETCH syntax) +select * +from dept +order by deptno offset 1 fetch next 2 rows only; ++--------+----------+---------+ +| DEPTNO | DNAME | LOC | ++--------+----------+---------+ +| 20 | RESEARCH | DALLAS | +| 30 | SALES | CHICAGO | ++--------+----------+---------+ +(2 rows) + +!ok + +# Return the 1st and 2nd departments +select * +from dept +order by deptno limit 2; ++--------+------------+----------+ +| DEPTNO | DNAME | LOC | ++--------+------------+----------+ +| 10 | ACCOUNTING | NEW YORK | +| 20 | RESEARCH | DALLAS | ++--------+------------+----------+ +(2 rows) + +!ok + +# Return the 1st and 2nd departments (using ansi FETCH syntax) +select * +from dept +order by deptno fetch next 2 rows only; ++--------+------------+----------+ +| DEPTNO | DNAME | LOC | ++--------+------------+----------+ +| 10 | ACCOUNTING | NEW YORK | +| 20 | RESEARCH | DALLAS | ++--------+------------+----------+ +(2 rows) + +!ok + +# Return 2nd and subsequent departments +select * +from dept +order by deptno offset 1; ++--------+------------+---------+ +| DEPTNO | DNAME | LOC | ++--------+------------+---------+ +| 20 | RESEARCH | DALLAS | +| 30 | SALES | CHICAGO | +| 40 | OPERATIONS | BOSTON | ++--------+------------+---------+ +(3 rows) + +!ok + +# No limit +select * +from dept +order by deptno; ++--------+------------+----------+ +| DEPTNO | DNAME | LOC | ++--------+------------+----------+ +| 10 | ACCOUNTING | NEW YORK | +| 20 | RESEARCH | DALLAS | +| 30 | SALES | CHICAGO | +| 40 | OPERATIONS | BOSTON | ++--------+------------+----------+ +(4 rows) + +!ok + +!use scott-lenient + +# Return the 2nd and 3rd department, using 'offset .. limit', +# which is only allowed in lenient conformance (and Trino) +select * +from dept +order by deptno offset 1 limit 2; ++--------+----------+---------+ +| DEPTNO | DNAME | LOC | ++--------+----------+---------+ +| 20 | RESEARCH | DALLAS | +| 30 | SALES | CHICAGO | ++--------+----------+---------+ +(2 rows) + +!ok + +!use scott-mysql + +# Returns 2nd and 3rd department, expressed using MySQL syntax +select * +from dept +order by deptno limit 1, 2; ++--------+----------+---------+ +| DEPTNO | DNAME | LOC | ++--------+----------+---------+ +| 20 | RESEARCH | DALLAS | +| 30 | SALES | CHICAGO | ++--------+----------+---------+ +(2 rows) + +!ok + +# Return the 1st and 2nd departments +select * +from dept +order by deptno limit 2; ++--------+------------+----------+ +| DEPTNO | DNAME | LOC | ++--------+------------+----------+ +| 10 | ACCOUNTING | NEW YORK | +| 20 | RESEARCH | DALLAS | ++--------+------------+----------+ +(2 rows) + +!ok + +# Return 2nd and subsequent departments, expressed using MySQL syntax +select * +from dept +order by deptno limit 1, all; ++--------+------------+---------+ +| DEPTNO | DNAME | LOC | ++--------+------------+---------+ +| 20 | RESEARCH | DALLAS | +| 30 | SALES | CHICAGO | +| 40 | OPERATIONS | BOSTON | ++--------+------------+---------+ +(3 rows) + +!ok + +# No limit, expressed using MySQL syntax +select * +from dept +order by deptno limit all; ++--------+------------+----------+ +| DEPTNO | DNAME | LOC | ++--------+------------+----------+ +| 10 | ACCOUNTING | NEW YORK | +| 20 | RESEARCH | DALLAS | +| 30 | SALES | CHICAGO | +| 40 | OPERATIONS | BOSTON | ++--------+------------+----------+ +(4 rows) + +!ok + !use post # [CALCITE-603] WITH ... ORDER BY cannot find table diff --git a/site/_docs/reference.md b/site/_docs/reference.md index cf521bc0ce..abd151131f 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -192,7 +192,7 @@ query: | query INTERSECT [ ALL | DISTINCT ] query } [ ORDER BY orderItem [, orderItem ]* ] - [ LIMIT { [ start, ] count | ALL } ] + [ LIMIT [ start, ] { count | ALL } ] [ OFFSET start { ROW | ROWS } ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] diff --git a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java index d78fab4324..c23377c0c3 100644 --- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -36,6 +36,7 @@ import org.apache.calcite.sql.test.SqlTestFactory; import org.apache.calcite.sql.test.SqlTests; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.util.SqlShuttle; +import org.apache.calcite.sql.validate.SqlConformance; import org.apache.calcite.sql.validate.SqlConformanceEnum; import org.apache.calcite.test.DiffTestCase; import org.apache.calcite.test.IntervalTest; @@ -3730,11 +3731,6 @@ public class SqlParserTest { sql("select a from foo limit 2,3 ^fetch^ next 4 rows only") .withConformance(SqlConformanceEnum.LENIENT) .fails("(?s).*Encountered \"fetch\" at line 1.*"); - - // "limit start, all" is not valid - sql("select a from foo limit 2, ^all^") - .withConformance(SqlConformanceEnum.LENIENT) - .fails("(?s).*Encountered \"all\" at line 1.*"); } /** Test case for @@ -3768,11 +3764,44 @@ public class SqlParserTest { sql("select a from foo offset 2 limit 3 ^fetch^ next 4 rows only") .withConformance(SqlConformanceEnum.LENIENT) .fails("(?s).*Encountered \"fetch\" at line 1.*"); + } - // "limit start, all" is not valid - sql("select a from foo offset 1 limit 2, ^all^") + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-5184";>[CALCITE-5184] + * Support "LIMIT start, ALL" (in MySQL conformance)</a>. + * + * @see SqlConformance#isLimitStartCountAllowed() */ + @Test void testLimitStartAll() { + String expected = "SELECT `A`\n" + + "FROM `FOO`\n" + + "OFFSET 2 ROWS"; + sql("select a from foo ^limit 2, all^") + .withConformance(SqlConformanceEnum.DEFAULT) + .fails("'LIMIT start, ALL' is not allowed under the " + + "current SQL conformance level") + .withConformance(SqlConformanceEnum.MYSQL_5) + .ok(expected) .withConformance(SqlConformanceEnum.LENIENT) - .fails("(?s).*Encountered \"all\" at line 1.*"); + .ok(expected); + + // 'limit 2, all' will override 'offset 1' (if allowed by conformance) + sql("select a from foo ^offset 1 limit 2, all^") + .withConformance(SqlConformanceEnum.DEFAULT) + .fails("'LIMIT start, ALL' is not allowed under the " + + "current SQL conformance level") + .withConformance(SqlConformanceEnum.MYSQL_5) + .fails("'OFFSET start LIMIT count' is not allowed under the " + + "current SQL conformance level") + .withConformance(SqlConformanceEnum.LENIENT) + .ok(expected); + + // 'offset 1' will override 'limit 2, all' + String expected2 = "SELECT `A`\n" + + "FROM `FOO`\n" + + "OFFSET 1 ROWS"; + sql("select a from foo limit 2, all offset 1") + .withConformance(SqlConformanceEnum.LENIENT) + .ok(expected2); } @Test void testSqlInlineComment() {
