Repository: spark Updated Branches: refs/heads/branch-2.0 33a213f33 -> 6bf20cd94
[SPARK-17015][SQL] group-by/order-by ordinal and arithmetic tests This patch adds three test files: 1. arithmetic.sql.out 2. order-by-ordinal.sql 3. group-by-ordinal.sql This includes https://github.com/apache/spark/pull/14594. This is a test case change. Author: petermaxlee <petermax...@gmail.com> Closes #14595 from petermaxlee/SPARK-17015. (cherry picked from commit a7b02db457d5fc663ce6a1ef01bf04689870e6b4) Signed-off-by: Reynold Xin <r...@databricks.com> Project: http://git-wip-us.apache.org/repos/asf/spark/repo Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/6bf20cd9 Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/6bf20cd9 Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/6bf20cd9 Branch: refs/heads/branch-2.0 Commit: 6bf20cd9460fd27c3e1e434b1cf31a3778ec3443 Parents: 33a213f Author: petermaxlee <petermax...@gmail.com> Authored: Thu Aug 11 01:43:08 2016 -0700 Committer: Reynold Xin <r...@databricks.com> Committed: Thu Aug 11 10:50:52 2016 -0700 ---------------------------------------------------------------------- .../spark/sql/catalyst/analysis/Analyzer.scala | 24 +- .../resources/sql-tests/inputs/arithmetic.sql | 26 +++ .../sql-tests/inputs/group-by-ordinal.sql | 50 +++++ .../sql-tests/inputs/order-by-ordinal.sql | 36 +++ .../sql-tests/results/arithmetic.sql.out | 178 +++++++++++++++ .../sql-tests/results/group-by-ordinal.sql.out | 168 ++++++++++++++ .../sql-tests/results/order-by-ordinal.sql.out | 143 ++++++++++++ .../org/apache/spark/sql/SQLQuerySuite.scala | 220 ------------------- 8 files changed, 613 insertions(+), 232 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/spark/blob/6bf20cd9/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala ---------------------------------------------------------------------- diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala index 660f523..57c3d9a 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala @@ -547,8 +547,7 @@ class Analyzer( case a: Aggregate if containsStar(a.aggregateExpressions) => if (conf.groupByOrdinal && a.groupingExpressions.exists(IntegerIndex.unapply(_).nonEmpty)) { failAnalysis( - "Group by position: star is not allowed to use in the select list " + - "when using ordinals in group by") + "Star (*) is not allowed in select list when GROUP BY ordinal position is used") } else { a.copy(aggregateExpressions = buildExpandedProjectList(a.aggregateExpressions, a.child)) } @@ -723,9 +722,9 @@ class Analyzer( if (index > 0 && index <= child.output.size) { SortOrder(child.output(index - 1), direction) } else { - throw new UnresolvedException(s, - s"Order/sort By position: $index does not exist " + - s"The Select List is indexed from 1 to ${child.output.size}") + s.failAnalysis( + s"ORDER BY position $index is not in select list " + + s"(valid range is [1, ${child.output.size}])") } case o => o } @@ -737,17 +736,18 @@ class Analyzer( if conf.groupByOrdinal && aggs.forall(_.resolved) && groups.exists(IntegerIndex.unapply(_).nonEmpty) => val newGroups = groups.map { - case IntegerIndex(index) if index > 0 && index <= aggs.size => + case ordinal @ IntegerIndex(index) if index > 0 && index <= aggs.size => aggs(index - 1) match { case e if ResolveAggregateFunctions.containsAggregate(e) => - throw new UnresolvedException(a, - s"Group by position: the '$index'th column in the select contains an " + - s"aggregate function: ${e.sql}. Aggregate functions are not allowed in GROUP BY") + ordinal.failAnalysis( + s"GROUP BY position $index is an aggregate function, and " + + "aggregate functions are not allowed in GROUP BY") case o => o } - case IntegerIndex(index) => - throw new UnresolvedException(a, - s"Group by position: '$index' exceeds the size of the select list '${aggs.size}'.") + case ordinal @ IntegerIndex(index) => + ordinal.failAnalysis( + s"GROUP BY position $index is not in select list " + + s"(valid range is [1, ${aggs.size}])") case o => o } Aggregate(newGroups, aggs, child) http://git-wip-us.apache.org/repos/asf/spark/blob/6bf20cd9/sql/core/src/test/resources/sql-tests/inputs/arithmetic.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/arithmetic.sql b/sql/core/src/test/resources/sql-tests/inputs/arithmetic.sql new file mode 100644 index 0000000..cbe4041 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/arithmetic.sql @@ -0,0 +1,26 @@ + +-- unary minus and plus +select -100; +select +230; +select -5.2; +select +6.8e0; +select -key, +key from testdata where key = 2; +select -(key + 1), - key + 1, +(key + 5) from testdata where key = 1; +select -max(key), +max(key) from testdata; +select - (-10); +select + (-key) from testdata where key = 32; +select - (+max(key)) from testdata; +select - - 3; +select - + 20; +select + + 100; +select - - max(key) from testdata; +select + - key from testdata where key = 33; + +-- other arithmetics +select 1 + 2; +select 1 - 2; +select 2 * 5; +select 5 / 2; +select 5 div 2; +select 5 % 3; +select pmod(-7, 3); http://git-wip-us.apache.org/repos/asf/spark/blob/6bf20cd9/sql/core/src/test/resources/sql-tests/inputs/group-by-ordinal.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/group-by-ordinal.sql b/sql/core/src/test/resources/sql-tests/inputs/group-by-ordinal.sql new file mode 100644 index 0000000..36b469c --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/group-by-ordinal.sql @@ -0,0 +1,50 @@ +-- group by ordinal positions + +create temporary view data as select * from values + (1, 1), + (1, 2), + (2, 1), + (2, 2), + (3, 1), + (3, 2) + as data(a, b); + +-- basic case +select a, sum(b) from data group by 1; + +-- constant case +select 1, 2, sum(b) from data group by 1, 2; + +-- duplicate group by column +select a, 1, sum(b) from data group by a, 1; +select a, 1, sum(b) from data group by 1, 2; + +-- group by a non-aggregate expression's ordinal +select a, b + 2, count(2) from data group by a, 2; + +-- with alias +select a as aa, b + 2 as bb, count(2) from data group by 1, 2; + +-- foldable non-literal: this should be the same as no grouping. +select sum(b) from data group by 1 + 0; + +-- negative cases: ordinal out of range +select a, b from data group by -1; +select a, b from data group by 0; +select a, b from data group by 3; + +-- negative case: position is an aggregate expression +select a, b, sum(b) from data group by 3; +select a, b, sum(b) + 2 from data group by 3; + +-- negative case: nondeterministic expression +select a, rand(0), sum(b) from data group by a, 2; + +-- negative case: star +select * from data group by a, b, 1; + +-- turn of group by ordinal +set spark.sql.groupByOrdinal=false; + +-- can now group by negative literal +select sum(b) from data group by -1; http://git-wip-us.apache.org/repos/asf/spark/blob/6bf20cd9/sql/core/src/test/resources/sql-tests/inputs/order-by-ordinal.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/order-by-ordinal.sql b/sql/core/src/test/resources/sql-tests/inputs/order-by-ordinal.sql new file mode 100644 index 0000000..8d733e7 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/order-by-ordinal.sql @@ -0,0 +1,36 @@ +-- order by and sort by ordinal positions + +create temporary view data as select * from values + (1, 1), + (1, 2), + (2, 1), + (2, 2), + (3, 1), + (3, 2) + as data(a, b); + +select * from data order by 1 desc; + +-- mix ordinal and column name +select * from data order by 1 desc, b desc; + +-- order by multiple ordinals +select * from data order by 1 desc, 2 desc; + +-- 1 + 0 is considered a constant (not an ordinal) and thus ignored +select * from data order by 1 + 0 desc, b desc; + +-- negative cases: ordinal position out of range +select * from data order by 0; +select * from data order by -1; +select * from data order by 3; + +-- sort by ordinal +select * from data sort by 1 desc; + +-- turn off order by ordinal +set spark.sql.orderByOrdinal=false; + +-- 0 is now a valid literal +select * from data order by 0; +select * from data sort by 0; http://git-wip-us.apache.org/repos/asf/spark/blob/6bf20cd9/sql/core/src/test/resources/sql-tests/results/arithmetic.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/arithmetic.sql.out b/sql/core/src/test/resources/sql-tests/results/arithmetic.sql.out new file mode 100644 index 0000000..50ea254 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/arithmetic.sql.out @@ -0,0 +1,178 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 22 + + +-- !query 0 +select -100 +-- !query 0 schema +struct<(-100):int> +-- !query 0 output +-100 + + +-- !query 1 +select +230 +-- !query 1 schema +struct<230:int> +-- !query 1 output +230 + + +-- !query 2 +select -5.2 +-- !query 2 schema +struct<(-5.2):decimal(2,1)> +-- !query 2 output +-5.2 + + +-- !query 3 +select +6.8e0 +-- !query 3 schema +struct<6.8:double> +-- !query 3 output +6.8 + + +-- !query 4 +select -key, +key from testdata where key = 2 +-- !query 4 schema +struct<(-key):int,key:int> +-- !query 4 output +-2 2 + + +-- !query 5 +select -(key + 1), - key + 1, +(key + 5) from testdata where key = 1 +-- !query 5 schema +struct<(-(key + 1)):int,((-key) + 1):int,(key + 5):int> +-- !query 5 output +-2 0 6 + + +-- !query 6 +select -max(key), +max(key) from testdata +-- !query 6 schema +struct<(-max(key)):int,max(key):int> +-- !query 6 output +-100 100 + + +-- !query 7 +select - (-10) +-- !query 7 schema +struct<(-(-10)):int> +-- !query 7 output +10 + + +-- !query 8 +select + (-key) from testdata where key = 32 +-- !query 8 schema +struct<(-key):int> +-- !query 8 output +-32 + + +-- !query 9 +select - (+max(key)) from testdata +-- !query 9 schema +struct<(-max(key)):int> +-- !query 9 output +-100 + + +-- !query 10 +select - - 3 +-- !query 10 schema +struct<(-(-3)):int> +-- !query 10 output +3 + + +-- !query 11 +select - + 20 +-- !query 11 schema +struct<(-20):int> +-- !query 11 output +-20 + + +-- !query 12 +select + + 100 +-- !query 12 schema +struct<100:int> +-- !query 12 output +100 + + +-- !query 13 +select - - max(key) from testdata +-- !query 13 schema +struct<(-(-max(key))):int> +-- !query 13 output +100 + + +-- !query 14 +select + - key from testdata where key = 33 +-- !query 14 schema +struct<(-key):int> +-- !query 14 output +-33 + + +-- !query 15 +select 1 + 2 +-- !query 15 schema +struct<(1 + 2):int> +-- !query 15 output +3 + + +-- !query 16 +select 1 - 2 +-- !query 16 schema +struct<(1 - 2):int> +-- !query 16 output +-1 + + +-- !query 17 +select 2 * 5 +-- !query 17 schema +struct<(2 * 5):int> +-- !query 17 output +10 + + +-- !query 18 +select 5 / 2 +-- !query 18 schema +struct<(CAST(5 AS DOUBLE) / CAST(2 AS DOUBLE)):double> +-- !query 18 output +2.5 + + +-- !query 19 +select 5 div 2 +-- !query 19 schema +struct<CAST((CAST(5 AS DOUBLE) / CAST(2 AS DOUBLE)) AS BIGINT):bigint> +-- !query 19 output +2 + + +-- !query 20 +select 5 % 3 +-- !query 20 schema +struct<(5 % 3):int> +-- !query 20 output +2 + + +-- !query 21 +select pmod(-7, 3) +-- !query 21 schema +struct<pmod((-7), 3):int> +-- !query 21 output +2 http://git-wip-us.apache.org/repos/asf/spark/blob/6bf20cd9/sql/core/src/test/resources/sql-tests/results/group-by-ordinal.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/group-by-ordinal.sql.out b/sql/core/src/test/resources/sql-tests/results/group-by-ordinal.sql.out new file mode 100644 index 0000000..2f10b7e --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/group-by-ordinal.sql.out @@ -0,0 +1,168 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 17 + + +-- !query 0 +create temporary view data as select * from values + (1, 1), + (1, 2), + (2, 1), + (2, 2), + (3, 1), + (3, 2) + as data(a, b) +-- !query 0 schema +struct<> +-- !query 0 output + + + +-- !query 1 +select a, sum(b) from data group by 1 +-- !query 1 schema +struct<a:int,sum(b):bigint> +-- !query 1 output +1 3 +2 3 +3 3 + + +-- !query 2 +select 1, 2, sum(b) from data group by 1, 2 +-- !query 2 schema +struct<1:int,2:int,sum(b):bigint> +-- !query 2 output +1 2 9 + + +-- !query 3 +select a, 1, sum(b) from data group by a, 1 +-- !query 3 schema +struct<a:int,1:int,sum(b):bigint> +-- !query 3 output +1 1 3 +2 1 3 +3 1 3 + + +-- !query 4 +select a, 1, sum(b) from data group by 1, 2 +-- !query 4 schema +struct<a:int,1:int,sum(b):bigint> +-- !query 4 output +1 1 3 +2 1 3 +3 1 3 + + +-- !query 5 +select a, b + 2, count(2) from data group by a, 2 +-- !query 5 schema +struct<a:int,(b + 2):int,count(2):bigint> +-- !query 5 output +1 3 1 +1 4 1 +2 3 1 +2 4 1 +3 3 1 +3 4 1 + + +-- !query 6 +select a as aa, b + 2 as bb, count(2) from data group by 1, 2 +-- !query 6 schema +struct<aa:int,bb:int,count(2):bigint> +-- !query 6 output +1 3 1 +1 4 1 +2 3 1 +2 4 1 +3 3 1 +3 4 1 + + +-- !query 7 +select sum(b) from data group by 1 + 0 +-- !query 7 schema +struct<sum(b):bigint> +-- !query 7 output +9 + + +-- !query 8 +select a, b from data group by -1 +-- !query 8 schema +struct<> +-- !query 8 output +org.apache.spark.sql.AnalysisException +GROUP BY position -1 is not in select list (valid range is [1, 2]); line 1 pos 31 + + +-- !query 9 +select a, b from data group by 0 +-- !query 9 schema +struct<> +-- !query 9 output +org.apache.spark.sql.AnalysisException +GROUP BY position 0 is not in select list (valid range is [1, 2]); line 1 pos 31 + + +-- !query 10 +select a, b from data group by 3 +-- !query 10 schema +struct<> +-- !query 10 output +org.apache.spark.sql.AnalysisException +GROUP BY position 3 is not in select list (valid range is [1, 2]); line 1 pos 31 + + +-- !query 11 +select a, b, sum(b) from data group by 3 +-- !query 11 schema +struct<> +-- !query 11 output +org.apache.spark.sql.AnalysisException +GROUP BY position 3 is an aggregate function, and aggregate functions are not allowed in GROUP BY; line 1 pos 39 + + +-- !query 12 +select a, b, sum(b) + 2 from data group by 3 +-- !query 12 schema +struct<> +-- !query 12 output +org.apache.spark.sql.AnalysisException +GROUP BY position 3 is an aggregate function, and aggregate functions are not allowed in GROUP BY; line 1 pos 43 + + +-- !query 13 +select a, rand(0), sum(b) from data group by a, 2 +-- !query 13 schema +struct<> +-- !query 13 output +org.apache.spark.sql.AnalysisException +nondeterministic expression rand(0) should not appear in grouping expression.; + + +-- !query 14 +select * from data group by a, b, 1 +-- !query 14 schema +struct<> +-- !query 14 output +org.apache.spark.sql.AnalysisException +Star (*) is not allowed in select list when GROUP BY ordinal position is used; + + +-- !query 15 +set spark.sql.groupByOrdinal=false +-- !query 15 schema +struct<key:string,value:string> +-- !query 15 output +spark.sql.groupByOrdinal + + +-- !query 16 +select sum(b) from data group by -1 +-- !query 16 schema +struct<sum(b):bigint> +-- !query 16 output +9 http://git-wip-us.apache.org/repos/asf/spark/blob/6bf20cd9/sql/core/src/test/resources/sql-tests/results/order-by-ordinal.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/order-by-ordinal.sql.out b/sql/core/src/test/resources/sql-tests/results/order-by-ordinal.sql.out new file mode 100644 index 0000000..03a4e72 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/order-by-ordinal.sql.out @@ -0,0 +1,143 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 12 + + +-- !query 0 +create temporary view data as select * from values + (1, 1), + (1, 2), + (2, 1), + (2, 2), + (3, 1), + (3, 2) + as data(a, b) +-- !query 0 schema +struct<> +-- !query 0 output + + + +-- !query 1 +select * from data order by 1 desc +-- !query 1 schema +struct<a:int,b:int> +-- !query 1 output +3 1 +3 2 +2 1 +2 2 +1 1 +1 2 + + +-- !query 2 +select * from data order by 1 desc, b desc +-- !query 2 schema +struct<a:int,b:int> +-- !query 2 output +3 2 +3 1 +2 2 +2 1 +1 2 +1 1 + + +-- !query 3 +select * from data order by 1 desc, 2 desc +-- !query 3 schema +struct<a:int,b:int> +-- !query 3 output +3 2 +3 1 +2 2 +2 1 +1 2 +1 1 + + +-- !query 4 +select * from data order by 1 + 0 desc, b desc +-- !query 4 schema +struct<a:int,b:int> +-- !query 4 output +1 2 +2 2 +3 2 +1 1 +2 1 +3 1 + + +-- !query 5 +select * from data order by 0 +-- !query 5 schema +struct<> +-- !query 5 output +org.apache.spark.sql.AnalysisException +ORDER BY position 0 is not in select list (valid range is [1, 2]); line 1 pos 28 + + +-- !query 6 +select * from data order by -1 +-- !query 6 schema +struct<> +-- !query 6 output +org.apache.spark.sql.AnalysisException +ORDER BY position -1 is not in select list (valid range is [1, 2]); line 1 pos 28 + + +-- !query 7 +select * from data order by 3 +-- !query 7 schema +struct<> +-- !query 7 output +org.apache.spark.sql.AnalysisException +ORDER BY position 3 is not in select list (valid range is [1, 2]); line 1 pos 28 + + +-- !query 8 +select * from data sort by 1 desc +-- !query 8 schema +struct<a:int,b:int> +-- !query 8 output +1 1 +1 2 +2 1 +2 2 +3 1 +3 2 + + +-- !query 9 +set spark.sql.orderByOrdinal=false +-- !query 9 schema +struct<key:string,value:string> +-- !query 9 output +spark.sql.orderByOrdinal + + +-- !query 10 +select * from data order by 0 +-- !query 10 schema +struct<a:int,b:int> +-- !query 10 output +1 1 +1 2 +2 1 +2 2 +3 1 +3 2 + + +-- !query 11 +select * from data sort by 0 +-- !query 11 schema +struct<a:int,b:int> +-- !query 11 output +1 1 +1 2 +2 1 +2 2 +3 1 +3 2 http://git-wip-us.apache.org/repos/asf/spark/blob/6bf20cd9/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 3de5086..d1d5875 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 @@ -487,103 +487,6 @@ class SQLQuerySuite extends QueryTest with SharedSQLContext { Seq(Row(1, 3), Row(2, 3), Row(3, 3))) } - test("Group By Ordinal - basic") { - checkAnswer( - sql("SELECT a, sum(b) FROM testData2 GROUP BY 1"), - sql("SELECT a, sum(b) FROM testData2 GROUP BY a")) - - // duplicate group-by columns - checkAnswer( - sql("SELECT a, 1, sum(b) FROM testData2 GROUP BY a, 1"), - sql("SELECT a, 1, sum(b) FROM testData2 GROUP BY a")) - - checkAnswer( - sql("SELECT a, 1, sum(b) FROM testData2 GROUP BY 1, 2"), - sql("SELECT a, 1, sum(b) FROM testData2 GROUP BY a")) - } - - test("Group By Ordinal - non aggregate expressions") { - checkAnswer( - sql("SELECT a, b + 2, count(2) FROM testData2 GROUP BY a, 2"), - sql("SELECT a, b + 2, count(2) FROM testData2 GROUP BY a, b + 2")) - - checkAnswer( - sql("SELECT a, b + 2 as c, count(2) FROM testData2 GROUP BY a, 2"), - sql("SELECT a, b + 2, count(2) FROM testData2 GROUP BY a, b + 2")) - } - - test("Group By Ordinal - non-foldable constant expression") { - checkAnswer( - sql("SELECT a, b, sum(b) FROM testData2 GROUP BY a, b, 1 + 0"), - sql("SELECT a, b, sum(b) FROM testData2 GROUP BY a, b")) - - checkAnswer( - sql("SELECT a, 1, sum(b) FROM testData2 GROUP BY a, 1 + 2"), - sql("SELECT a, 1, sum(b) FROM testData2 GROUP BY a")) - } - - test("Group By Ordinal - alias") { - checkAnswer( - sql("SELECT a, (b + 2) as c, count(2) FROM testData2 GROUP BY a, 2"), - sql("SELECT a, b + 2, count(2) FROM testData2 GROUP BY a, b + 2")) - - checkAnswer( - sql("SELECT a as b, b as a, sum(b) FROM testData2 GROUP BY 1, 2"), - sql("SELECT a, b, sum(b) FROM testData2 GROUP BY a, b")) - } - - test("Group By Ordinal - constants") { - checkAnswer( - sql("SELECT 1, 2, sum(b) FROM testData2 GROUP BY 1, 2"), - sql("SELECT 1, 2, sum(b) FROM testData2")) - } - - test("Group By Ordinal - negative cases") { - intercept[UnresolvedException[Aggregate]] { - sql("SELECT a, b FROM testData2 GROUP BY -1") - } - - intercept[UnresolvedException[Aggregate]] { - sql("SELECT a, b FROM testData2 GROUP BY 3") - } - - var e = intercept[UnresolvedException[Aggregate]]( - sql("SELECT SUM(a) FROM testData2 GROUP BY 1")) - assert(e.getMessage contains - "Invalid call to Group by position: the '1'th column in the select contains " + - "an aggregate function") - - e = intercept[UnresolvedException[Aggregate]]( - sql("SELECT SUM(a) + 1 FROM testData2 GROUP BY 1")) - assert(e.getMessage contains - "Invalid call to Group by position: the '1'th column in the select contains " + - "an aggregate function") - - var ae = intercept[AnalysisException]( - sql("SELECT a, rand(0), sum(b) FROM testData2 GROUP BY a, 2")) - assert(ae.getMessage contains - "nondeterministic expression rand(0) should not appear in grouping expression") - - ae = intercept[AnalysisException]( - sql("SELECT * FROM testData2 GROUP BY a, b, 1")) - assert(ae.getMessage contains - "Group by position: star is not allowed to use in the select list " + - "when using ordinals in group by") - } - - test("Group By Ordinal: spark.sql.groupByOrdinal=false") { - withSQLConf(SQLConf.GROUP_BY_ORDINAL.key -> "false") { - // If spark.sql.groupByOrdinal=false, ignore the position number. - intercept[AnalysisException] { - sql("SELECT a, sum(b) FROM testData2 GROUP BY 1") - } - // '*' is not allowed to use in the select list when users specify ordinals in group by - checkAnswer( - sql("SELECT * FROM testData2 GROUP BY a, b, 1"), - sql("SELECT * FROM testData2 GROUP BY a, b")) - } - } - test("aggregates with nulls") { checkAnswer( sql("SELECT SKEWNESS(a), KURTOSIS(a), MIN(a), MAX(a)," + @@ -1288,89 +1191,6 @@ class SQLQuerySuite extends QueryTest with SharedSQLContext { } } - test("Test to check we can apply sign to expression") { - - checkAnswer( - sql("SELECT -100"), Row(-100) - ) - - checkAnswer( - sql("SELECT +230"), Row(230) - ) - - checkAnswer( - sql("SELECT -5.2"), Row(BigDecimal(-5.2)) - ) - - checkAnswer( - sql("SELECT +6.8e0"), Row(6.8d) - ) - - checkAnswer( - sql("SELECT -key FROM testData WHERE key = 2"), Row(-2) - ) - - checkAnswer( - sql("SELECT +key FROM testData WHERE key = 3"), Row(3) - ) - - checkAnswer( - sql("SELECT -(key + 1) FROM testData WHERE key = 1"), Row(-2) - ) - - checkAnswer( - sql("SELECT - key + 1 FROM testData WHERE key = 10"), Row(-9) - ) - - checkAnswer( - sql("SELECT +(key + 5) FROM testData WHERE key = 5"), Row(10) - ) - - checkAnswer( - sql("SELECT -MAX(key) FROM testData"), Row(-100) - ) - - checkAnswer( - sql("SELECT +MAX(key) FROM testData"), Row(100) - ) - - checkAnswer( - sql("SELECT - (-10)"), Row(10) - ) - - checkAnswer( - sql("SELECT + (-key) FROM testData WHERE key = 32"), Row(-32) - ) - - checkAnswer( - sql("SELECT - (+Max(key)) FROM testData"), Row(-100) - ) - - checkAnswer( - sql("SELECT - - 3"), Row(3) - ) - - checkAnswer( - sql("SELECT - + 20"), Row(-20) - ) - - checkAnswer( - sql("SELEcT - + 45"), Row(-45) - ) - - checkAnswer( - sql("SELECT + + 100"), Row(100) - ) - - checkAnswer( - sql("SELECT - - Max(key) FROM testData"), Row(100) - ) - - checkAnswer( - sql("SELECT + - key FROM testData WHERE key = 33"), Row(-33) - ) - } - test("Multiple join") { checkAnswer( sql( @@ -2388,46 +2208,6 @@ class SQLQuerySuite extends QueryTest with SharedSQLContext { } } - test("order by ordinal number") { - checkAnswer( - sql("SELECT * FROM testData2 ORDER BY 1 DESC"), - sql("SELECT * FROM testData2 ORDER BY a DESC")) - // If the position is not an integer, ignore it. - checkAnswer( - sql("SELECT * FROM testData2 ORDER BY 1 + 0 DESC, b ASC"), - sql("SELECT * FROM testData2 ORDER BY b ASC")) - checkAnswer( - sql("SELECT * FROM testData2 ORDER BY 1 DESC, b ASC"), - sql("SELECT * FROM testData2 ORDER BY a DESC, b ASC")) - checkAnswer( - sql("SELECT * FROM testData2 SORT BY 1 DESC, 2"), - sql("SELECT * FROM testData2 SORT BY a DESC, b ASC")) - checkAnswer( - sql("SELECT * FROM testData2 ORDER BY 1 ASC, b ASC"), - Seq(Row(1, 1), Row(1, 2), Row(2, 1), Row(2, 2), Row(3, 1), Row(3, 2))) - } - - test("order by ordinal number - negative cases") { - intercept[UnresolvedException[SortOrder]] { - sql("SELECT * FROM testData2 ORDER BY 0") - } - intercept[UnresolvedException[SortOrder]] { - sql("SELECT * FROM testData2 ORDER BY -1 DESC, b ASC") - } - intercept[UnresolvedException[SortOrder]] { - sql("SELECT * FROM testData2 ORDER BY 3 DESC, b ASC") - } - } - - test("order by ordinal number with conf spark.sql.orderByOrdinal=false") { - withSQLConf(SQLConf.ORDER_BY_ORDINAL.key -> "false") { - // If spark.sql.orderByOrdinal=false, ignore the position number. - checkAnswer( - sql("SELECT * FROM testData2 ORDER BY 1 DESC, b ASC"), - sql("SELECT * FROM testData2 ORDER BY b ASC")) - } - } - test("join with using clause") { val df1 = Seq(("r1c1", "r1c2", "t1r1c3"), ("r2c1", "r2c2", "t1r2c3"), ("r3c1x", "r3c2", "t1r3c3")).toDF("c1", "c2", "c3") --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org