You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by rx...@apache.org on 2016/08/11 08:43:10 UTC
spark git commit: [SPARK-17015][SQL] group-by/order-by ordinal and
arithmetic tests
Repository: spark
Updated Branches:
refs/heads/master 0db373aaf -> a7b02db45
[SPARK-17015][SQL] group-by/order-by ordinal and arithmetic tests
## What changes were proposed in this pull request?
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.
## How was this patch tested?
This is a test case change.
Author: petermaxlee <pe...@gmail.com>
Closes #14595 from petermaxlee/SPARK-17015.
Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/a7b02db4
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/a7b02db4
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/a7b02db4
Branch: refs/heads/master
Commit: a7b02db457d5fc663ce6a1ef01bf04689870e6b4
Parents: 0db373a
Author: petermaxlee <pe...@gmail.com>
Authored: Thu Aug 11 01:43:08 2016 -0700
Committer: Reynold Xin <rx...@databricks.com>
Committed: Thu Aug 11 01:43:08 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/a7b02db4/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 25202b5..14a2a32 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/a7b02db4/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/a7b02db4/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/a7b02db4/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/a7b02db4/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/a7b02db4/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/a7b02db4/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/a7b02db4/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 c3f27f8..eac588f 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)," +
@@ -1298,89 +1201,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(
@@ -2398,46 +2218,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-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org