Repository: spark Updated Branches: refs/heads/master e3d2022e4 -> b84ff7eb6
[SPARK-20719][SQL] Support LIMIT ALL ### What changes were proposed in this pull request? `LIMIT ALL` is the same as omitting the `LIMIT` clause. It is supported by both PrestgreSQL and Presto. This PR is to support it by adding it in the parser. ### How was this patch tested? Added a test case Author: Xiao Li <[email protected]> Closes #17960 from gatorsmile/LimitAll. Project: http://git-wip-us.apache.org/repos/asf/spark/repo Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/b84ff7eb Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/b84ff7eb Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/b84ff7eb Branch: refs/heads/master Commit: b84ff7eb62abe34c33afe4a5888cbd2379672de8 Parents: e3d2022 Author: Xiao Li <[email protected]> Authored: Fri May 12 15:26:10 2017 -0700 Committer: Herman van Hovell <[email protected]> Committed: Fri May 12 15:26:10 2017 -0700 ---------------------------------------------------------------------- .../apache/spark/sql/catalyst/parser/SqlBase.g4 | 2 +- .../spark/sql/catalyst/parser/AstBuilder.scala | 1 + .../test/resources/sql-tests/inputs/limit.sql | 24 ++++++---- .../resources/sql-tests/results/limit.sql.out | 48 ++++++++++++++------ .../org/apache/spark/sql/SQLQuerySuite.scala | 8 ---- 5 files changed, 49 insertions(+), 34 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/spark/blob/b84ff7eb/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 ---------------------------------------------------------------------- diff --git a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 index 2b7c290..f99ce24 100644 --- a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 +++ b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 @@ -334,7 +334,7 @@ queryOrganization (DISTRIBUTE BY distributeBy+=expression (',' distributeBy+=expression)*)? (SORT BY sort+=sortItem (',' sort+=sortItem)*)? windows? - (LIMIT limit=expression)? + (LIMIT (ALL | limit=expression))? ; multiInsertQueryBody http://git-wip-us.apache.org/repos/asf/spark/blob/b84ff7eb/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala ---------------------------------------------------------------------- diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala index 0e974a0..740422b 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala @@ -281,6 +281,7 @@ class AstBuilder(conf: SQLConf) extends SqlBaseBaseVisitor[AnyRef] with Logging val withWindow = withOrder.optionalMap(windows)(withWindows) // LIMIT + // - LIMIT ALL is the same as omitting the LIMIT clause withWindow.optional(limit) { Limit(typedVisit(limit), withWindow) } http://git-wip-us.apache.org/repos/asf/spark/blob/b84ff7eb/sql/core/src/test/resources/sql-tests/inputs/limit.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/limit.sql b/sql/core/src/test/resources/sql-tests/inputs/limit.sql index 2ea35f7..f21912a 100644 --- a/sql/core/src/test/resources/sql-tests/inputs/limit.sql +++ b/sql/core/src/test/resources/sql-tests/inputs/limit.sql @@ -1,23 +1,27 @@ -- limit on various data types -select * from testdata limit 2; -select * from arraydata limit 2; -select * from mapdata limit 2; +SELECT * FROM testdata LIMIT 2; +SELECT * FROM arraydata LIMIT 2; +SELECT * FROM mapdata LIMIT 2; -- foldable non-literal in limit -select * from testdata limit 2 + 1; +SELECT * FROM testdata LIMIT 2 + 1; -select * from testdata limit CAST(1 AS int); +SELECT * FROM testdata LIMIT CAST(1 AS int); -- limit must be non-negative -select * from testdata limit -1; +SELECT * FROM testdata LIMIT -1; +SELECT * FROM testData TABLESAMPLE (-1 ROWS); -- limit must be foldable -select * from testdata limit key > 3; +SELECT * FROM testdata LIMIT key > 3; -- limit must be integer -select * from testdata limit true; -select * from testdata limit 'a'; +SELECT * FROM testdata LIMIT true; +SELECT * FROM testdata LIMIT 'a'; -- limit within a subquery -select * from (select * from range(10) limit 5) where id > 3; +SELECT * FROM (SELECT * FROM range(10) LIMIT 5) WHERE id > 3; + +-- limit ALL +SELECT * FROM testdata WHERE key < 3 LIMIT ALL; http://git-wip-us.apache.org/repos/asf/spark/blob/b84ff7eb/sql/core/src/test/resources/sql-tests/results/limit.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/limit.sql.out b/sql/core/src/test/resources/sql-tests/results/limit.sql.out index cb4e4d0..146abe6 100644 --- a/sql/core/src/test/resources/sql-tests/results/limit.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/limit.sql.out @@ -1,9 +1,9 @@ -- Automatically generated by SQLQueryTestSuite --- Number of queries: 10 +-- Number of queries: 12 -- !query 0 -select * from testdata limit 2 +SELECT * FROM testdata LIMIT 2 -- !query 0 schema struct<key:int,value:string> -- !query 0 output @@ -12,7 +12,7 @@ struct<key:int,value:string> -- !query 1 -select * from arraydata limit 2 +SELECT * FROM arraydata LIMIT 2 -- !query 1 schema struct<arraycol:array<int>,nestedarraycol:array<array<int>>> -- !query 1 output @@ -21,7 +21,7 @@ struct<arraycol:array<int>,nestedarraycol:array<array<int>>> -- !query 2 -select * from mapdata limit 2 +SELECT * FROM mapdata LIMIT 2 -- !query 2 schema struct<mapcol:map<int,string>> -- !query 2 output @@ -30,7 +30,7 @@ struct<mapcol:map<int,string>> -- !query 3 -select * from testdata limit 2 + 1 +SELECT * FROM testdata LIMIT 2 + 1 -- !query 3 schema struct<key:int,value:string> -- !query 3 output @@ -40,7 +40,7 @@ struct<key:int,value:string> -- !query 4 -select * from testdata limit CAST(1 AS int) +SELECT * FROM testdata LIMIT CAST(1 AS int) -- !query 4 schema struct<key:int,value:string> -- !query 4 output @@ -48,7 +48,7 @@ struct<key:int,value:string> -- !query 5 -select * from testdata limit -1 +SELECT * FROM testdata LIMIT -1 -- !query 5 schema struct<> -- !query 5 output @@ -57,35 +57,53 @@ The limit expression must be equal to or greater than 0, but got -1; -- !query 6 -select * from testdata limit key > 3 +SELECT * FROM testData TABLESAMPLE (-1 ROWS) -- !query 6 schema struct<> -- !query 6 output org.apache.spark.sql.AnalysisException -The limit expression must evaluate to a constant value, but got (testdata.`key` > 3); +The limit expression must be equal to or greater than 0, but got -1; -- !query 7 -select * from testdata limit true +SELECT * FROM testdata LIMIT key > 3 -- !query 7 schema struct<> -- !query 7 output org.apache.spark.sql.AnalysisException -The limit expression must be integer type, but got boolean; +The limit expression must evaluate to a constant value, but got (testdata.`key` > 3); -- !query 8 -select * from testdata limit 'a' +SELECT * FROM testdata LIMIT true -- !query 8 schema struct<> -- !query 8 output org.apache.spark.sql.AnalysisException -The limit expression must be integer type, but got string; +The limit expression must be integer type, but got boolean; -- !query 9 -select * from (select * from range(10) limit 5) where id > 3 +SELECT * FROM testdata LIMIT 'a' -- !query 9 schema -struct<id:bigint> +struct<> -- !query 9 output +org.apache.spark.sql.AnalysisException +The limit expression must be integer type, but got string; + + +-- !query 10 +SELECT * FROM (SELECT * FROM range(10) LIMIT 5) WHERE id > 3 +-- !query 10 schema +struct<id:bigint> +-- !query 10 output 4 + + +-- !query 11 +SELECT * FROM testdata WHERE key < 3 LIMIT ALL +-- !query 11 schema +struct<key:int,value:string> +-- !query 11 output +1 1 +2 2 http://git-wip-us.apache.org/repos/asf/spark/blob/b84ff7eb/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala ---------------------------------------------------------------------- diff --git a/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala index cd14d24..b525c9e 100644 --- a/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala +++ b/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala @@ -523,14 +523,6 @@ class SQLQuerySuite extends QueryTest with SharedSQLContext { sortTest() } - test("negative in LIMIT or TABLESAMPLE") { - val expected = "The limit expression must be equal to or greater than 0, but got -1" - var e = intercept[AnalysisException] { - sql("SELECT * FROM testData TABLESAMPLE (-1 rows)") - }.getMessage - assert(e.contains(expected)) - } - test("CTE feature") { checkAnswer( sql("with q1 as (select * from testData limit 10) select * from q1"), --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
