Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/21720#discussion_r200829969
--- Diff: sql/core/src/test/resources/sql-tests/results/pivot.sql.out ---
@@ -144,51 +155,162 @@ PIVOT (
sum(earnings * s)
FOR course IN ('dotNET', 'Java')
)
--- !query 9 schema
+-- !query 10 schema
struct<year:int,dotNET:bigint,Java:bigint>
--- !query 9 output
+-- !query 10 output
2012 15000 20000
2013 96000 60000
--- !query 10
-SELECT 2012_s, 2013_s, 2012_a, 2013_a, c FROM (
+-- !query 11
+SELECT firstYear_s, secondYear_s, firstYear_a, secondYear_a, c FROM (
SELECT year y, course c, earnings e FROM courseSales
)
PIVOT (
sum(e) s, avg(e) a
- FOR y IN (2012, 2013)
+ FOR y IN (2012 as firstYear, 2013 secondYear)
)
--- !query 10 schema
-struct<2012_s:bigint,2013_s:bigint,2012_a:double,2013_a:double,c:string>
--- !query 10 output
+-- !query 11 schema
+struct<firstYear_s:bigint,secondYear_s:bigint,firstYear_a:double,secondYear_a:double,c:string>
+-- !query 11 output
15000 48000 7500.0 48000.0 dotNET
20000 30000 20000.0 30000.0 Java
--- !query 11
+-- !query 12
SELECT * FROM courseSales
PIVOT (
abs(earnings)
FOR year IN (2012, 2013)
)
--- !query 11 schema
+-- !query 12 schema
struct<>
--- !query 11 output
+-- !query 12 output
org.apache.spark.sql.AnalysisException
Aggregate expression required for pivot, found 'abs(earnings#x)';
--- !query 12
+-- !query 13
SELECT * FROM (
SELECT course, earnings FROM courseSales
)
PIVOT (
sum(earnings)
FOR year IN (2012, 2013)
)
--- !query 12 schema
+-- !query 13 schema
struct<>
--- !query 12 output
+-- !query 13 output
org.apache.spark.sql.AnalysisException
cannot resolve '`year`' given input columns:
[__auto_generated_subquery_name.course,
__auto_generated_subquery_name.earnings]; line 4 pos 0
+
+
+-- !query 14
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings)
+ FOR (course, year) IN (('dotNET', 2012), ('Java', 2013))
+)
+-- !query 14 schema
+struct<s:int,[dotNET, 2012]:bigint,[Java, 2013]:bigint>
+-- !query 14 output
+1 15000 NULL
+2 NULL 30000
+
+
+-- !query 15
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings)
+ FOR (course, s) IN (('dotNET', 2) as c1, ('Java', 1) as c2)
+)
+-- !query 15 schema
+struct<year:int,c1:bigint,c2:bigint>
+-- !query 15 output
+2012 NULL 20000
+2013 48000 NULL
+
+
+-- !query 16
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ sum(earnings)
+ FOR (course, year) IN ('dotNET', 'Java')
+)
+-- !query 16 schema
+struct<>
+-- !query 16 output
+org.apache.spark.sql.AnalysisException
+Invalid pivot value 'dotNET': value data type string does not match pivot
column data type struct<course:string,year:int>;
+
+
+-- !query 17
+SELECT * FROM courseSales
+PIVOT (
+ sum(earnings)
+ FOR year IN (s, 2013)
+)
+-- !query 17 schema
+struct<>
+-- !query 17 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`s`' given input columns: [coursesales.course,
coursesales.year, coursesales.earnings]; line 4 pos 15
+
+
+-- !query 18
+SELECT * FROM courseSales
+PIVOT (
+ sum(earnings)
+ FOR year IN (course, 2013)
+)
+-- !query 18 schema
+struct<>
+-- !query 18 output
+org.apache.spark.sql.AnalysisException
+Literal expressions required for pivot values, found 'course#x';
+
+
+-- !query 19
+SELECT * FROM (
+ SELECT course, year, a
+ FROM courseSales
+ JOIN yearsWithArray ON year = y
+)
+PIVOT (
+ min(a)
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 19 schema
+struct<>
+-- !query 19 output
+org.apache.spark.SparkException
+Job 17 cancelled because SparkContext was shut down
+
+
+-- !query 20
+SELECT * FROM (
+ SELECT course, year, y, a
+ FROM courseSales
+ JOIN yearsWithArray ON year = y
+)
+PIVOT (
+ max(a)
+ FOR (y, course) IN ((2012, 'dotNET'), (2013, 'Java'))
+)
+-- !query 20 schema
+struct<>
+-- !query 20 output
+org.apache.spark.SparkException
+Exception thrown in awaitResult:
--- End diff --
?
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
