[GitHub] [spark] chitralverma commented on a change in pull request #25122: [SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF test base
chitralverma commented on a change in pull request #25122:
[SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF
test base
URL: https://github.com/apache/spark/pull/25122#discussion_r304810312
##
File path: sql/core/src/test/resources/sql-tests/inputs/udf/udf-pivot.sql
##
@@ -0,0 +1,317 @@
+-- This test file was converted from pivot.sql.
+
+-- Note that currently registered UDF returns a string. So there are some
differences, for instance
+-- in string cast within UDF in Scala and Python.
+
+--Note some test cases have been commented as the current integrated UDFs
cannot handle complex types
+
+create temporary view courseSales as select * from values
+ ("dotNET", 2012, 1),
+ ("Java", 2012, 2),
+ ("dotNET", 2012, 5000),
+ ("dotNET", 2013, 48000),
+ ("Java", 2013, 3)
+ as courseSales(course, year, earnings);
+
+create temporary view years as select * from values
+ (2012, 1),
+ (2013, 2)
+ as years(y, s);
+
+create temporary view yearsWithComplexTypes as select * from values
+ (2012, array(1, 1), map('1', 1), struct(1, 'a')),
+ (2013, array(2, 2), map('2', 2), struct(2, 'b'))
+ as yearsWithComplexTypes(y, a, m, s);
+
+-- pivot courses
+SELECT * FROM (
+ SELECT udf(year), course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot years with no subquery
+SELECT * FROM courseSales
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+);
+
+-- pivot courses with multiple aggregations
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(avg(earnings))
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with no group by column
+SELECT * FROM (
+ SELECT udf(course) as course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with no group by column and with multiple aggregations on different
columns
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(year))
+ FOR course IN ('dotNET', 'Java')
+);
+
+--todo nan fix
+-- pivot on join query with multiple group by columns
+SELECT * FROM (
+ SELECT course, year, earnings, udf(s) as s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR s IN (1, 2)
+);
+
+-- pivot on join query with multiple aggregations on different columns
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(s))
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot on join query with multiple columns in one aggregation
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings * s))
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with aliases and projection
+SELECT 2012_s, 2013_s, 2012_a, 2013_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ udf(sum(e)) s, udf(avg(e)) a
+ FOR y IN (2012, 2013)
+);
+
+-- pivot with projection and value aliases
+SELECT firstYear_s, secondYear_s, firstYear_a, secondYear_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ udf(sum(e)) s, udf(avg(e)) a
+ FOR y IN (2012 as firstYear, 2013 secondYear)
+);
+
+-- pivot years with non-aggregate function
+SELECT * FROM courseSales
+PIVOT (
+ udf(abs(earnings))
+ FOR year IN (2012, 2013)
+);
+
+-- pivot with one of the expressions as non-aggregate function
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), year
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with unresolvable columns
+SELECT * FROM (
+ SELECT course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+);
+
+-- pivot with complex aggregate expressions
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(ceil(udf(sum(earnings, avg(earnings) + 1 as a1
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with invalid arguments in aggregate expressions
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ sum(udf(avg(earnings)))
+ FOR course IN ('dotNET', 'Java')
+);
+
+--todo nan fix
+-- pivot on multiple pivot columns
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR (course, year) IN (('dotNET', 2012), ('Java', 2013))
+);
+
+--todo nan fix
+-- pivot on multiple pivot columns with aliased values
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR (course, s) IN (('dotNET', 2) as c1, ('Java', 1) as c2)
+);
+
+-- pivot on multiple pivot columns with values of wrong data types
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON
[GitHub] [spark] chitralverma commented on a change in pull request #25122: [SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF test base
chitralverma commented on a change in pull request #25122:
[SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF
test base
URL: https://github.com/apache/spark/pull/25122#discussion_r304764441
##
File path: sql/core/src/test/resources/sql-tests/inputs/udf/udf-pivot.sql
##
@@ -0,0 +1,317 @@
+-- This test file was converted from pivot.sql.
+
+-- Note that currently registered UDF returns a string. So there are some
differences, for instance
+-- in string cast within UDF in Scala and Python.
+
+--Note some test cases have been commented as the current integrated UDFs
cannot handle complex types
+
+create temporary view courseSales as select * from values
+ ("dotNET", 2012, 1),
+ ("Java", 2012, 2),
+ ("dotNET", 2012, 5000),
+ ("dotNET", 2013, 48000),
+ ("Java", 2013, 3)
+ as courseSales(course, year, earnings);
+
+create temporary view years as select * from values
+ (2012, 1),
+ (2013, 2)
+ as years(y, s);
+
+create temporary view yearsWithComplexTypes as select * from values
+ (2012, array(1, 1), map('1', 1), struct(1, 'a')),
+ (2013, array(2, 2), map('2', 2), struct(2, 'b'))
+ as yearsWithComplexTypes(y, a, m, s);
+
+-- pivot courses
+SELECT * FROM (
+ SELECT udf(year), course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot years with no subquery
+SELECT * FROM courseSales
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+);
+
+-- pivot courses with multiple aggregations
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(avg(earnings))
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with no group by column
+SELECT * FROM (
+ SELECT udf(course) as course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+);
+
+-- pivot with no group by column and with multiple aggregations on different
columns
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(year))
Review comment:
I'll add it
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
With regards,
Apache Git Services
-
To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org
For additional commands, e-mail: reviews-h...@spark.apache.org
[GitHub] [spark] chitralverma commented on a change in pull request #25122: [SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF test base
chitralverma commented on a change in pull request #25122:
[SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF
test base
URL: https://github.com/apache/spark/pull/25122#discussion_r302865222
##
File path: sql/core/src/test/resources/sql-tests/results/udf/udf-pivot.sql.out
##
@@ -0,0 +1,494 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 32
+
+
+-- !query 0
+create temporary view courseSales as select * from values
+ ("dotNET", 2012, 1),
+ ("Java", 2012, 2),
+ ("dotNET", 2012, 5000),
+ ("dotNET", 2013, 48000),
+ ("Java", 2013, 3)
+ as courseSales(course, year, earnings)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view years as select * from values
+ (2012, 1),
+ (2013, 2)
+ as years(y, s)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view yearsWithComplexTypes as select * from values
+ (2012, array(1, 1), map('1', 1), struct(1, 'a')),
+ (2013, array(2, 2), map('2', 2), struct(2, 'b'))
+ as yearsWithComplexTypes(y, a, m, s)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT * FROM (
+ SELECT udf(year), course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 3 schema
+struct
+-- !query 3 output
+2012 15000 2
+2013 48000 3
+
+
+-- !query 4
+SELECT * FROM courseSales
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 4 schema
+struct
+-- !query 4 output
+Java 2 3
+dotNET 15000 48000
+
+
+-- !query 5
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(avg(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 5 schema
+struct
+-- !query 5 output
+2012 15000 7500.0 2 2.0
+2013 48000 48000.0 3 3.0
+
+
+-- !query 6
+SELECT * FROM (
+ SELECT udf(course) as course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 6 schema
+struct
+-- !query 6 output
+63000 5
+
+
+-- !query 7
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(year))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 7 schema
+struct
+-- !query 7 output
+63000 20125 2012
+
+
+-- !query 8
+SELECT * FROM (
+ SELECT course, year, earnings, udf(s) as s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR s IN (1, 2)
+)
+-- !query 8 schema
+struct
+-- !query 8 output
+Java 20122 nan
+Java 2013nan 3
+dotNET 201215000 nan
+dotNET 2013nan 48000
+
+
+-- !query 9
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(s))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 9 schema
+struct
+-- !query 9 output
+2012 15000 1 2 1
+2013 48000 2 3 2
+
+
+-- !query 10
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings * s))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 10 schema
+struct
+-- !query 10 output
+2012 15000 2
+2013 96000 6
+
+
+-- !query 11
+SELECT 2012_s, 2013_s, 2012_a, 2013_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ udf(sum(e)) s, udf(avg(e)) a
+ FOR y IN (2012, 2013)
+)
+-- !query 11 schema
+struct<2012_s:string,2013_s:string,2012_a:string,2013_a:string,c:string>
+-- !query 11 output
+15000 48000 7500.0 48000.0 dotNET
+2 3 2.0 3.0 Java
+
+
+-- !query 12
+SELECT firstYear_s, secondYear_s, firstYear_a, secondYear_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ udf(sum(e)) s, udf(avg(e)) a
+ FOR y IN (2012 as firstYear, 2013 secondYear)
+)
+-- !query 12 schema
+struct
+-- !query 12 output
+15000 48000 7500.0 48000.0 dotNET
+2 3 2.0 3.0 Java
+
+
+-- !query 13
+SELECT * FROM courseSales
+PIVOT (
+ udf(abs(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 13 schema
+struct<>
+-- !query 13 output
+org.apache.spark.sql.AnalysisException
+Aggregate expression required for pivot, but 'coursesales.`earnings`' did not
appear in any aggregate function.;
+
+
+-- !query 14
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), year
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 14 schema
+struct<>
+-- !query 14 output
+org.apache.spark.sql.AnalysisException
+Aggregate expression required for pivot, but
'__auto_generated_subquery_name.`year`' did not appear in any aggregate
function.;
+
+
+-- !query 15
+SELECT * FROM (
+ SELECT course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 15 schema
+struct<>
+--
[GitHub] [spark] chitralverma commented on a change in pull request #25122: [SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF test base
chitralverma commented on a change in pull request #25122:
[SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF
test base
URL: https://github.com/apache/spark/pull/25122#discussion_r302821986
##
File path: sql/core/src/test/resources/sql-tests/results/udf/udf-pivot.sql.out
##
@@ -0,0 +1,494 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 32
+
+
+-- !query 0
+create temporary view courseSales as select * from values
+ ("dotNET", 2012, 1),
+ ("Java", 2012, 2),
+ ("dotNET", 2012, 5000),
+ ("dotNET", 2013, 48000),
+ ("Java", 2013, 3)
+ as courseSales(course, year, earnings)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view years as select * from values
+ (2012, 1),
+ (2013, 2)
+ as years(y, s)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view yearsWithComplexTypes as select * from values
+ (2012, array(1, 1), map('1', 1), struct(1, 'a')),
+ (2013, array(2, 2), map('2', 2), struct(2, 'b'))
+ as yearsWithComplexTypes(y, a, m, s)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT * FROM (
+ SELECT udf(year), course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 3 schema
+struct
+-- !query 3 output
+2012 15000 2
+2013 48000 3
+
+
+-- !query 4
+SELECT * FROM courseSales
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 4 schema
+struct
+-- !query 4 output
+Java 2 3
+dotNET 15000 48000
+
+
+-- !query 5
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(avg(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 5 schema
+struct
+-- !query 5 output
+2012 15000 7500.0 2 2.0
+2013 48000 48000.0 3 3.0
+
+
+-- !query 6
+SELECT * FROM (
+ SELECT udf(course) as course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 6 schema
+struct
+-- !query 6 output
+63000 5
+
+
+-- !query 7
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(year))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 7 schema
+struct
+-- !query 7 output
+63000 20125 2012
+
+
+-- !query 8
+SELECT * FROM (
+ SELECT course, year, earnings, udf(s) as s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR s IN (1, 2)
+)
+-- !query 8 schema
+struct
+-- !query 8 output
+Java 20122 nan
+Java 2013nan 3
+dotNET 201215000 nan
+dotNET 2013nan 48000
+
+
+-- !query 9
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(s))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 9 schema
+struct
+-- !query 9 output
+2012 15000 1 2 1
+2013 48000 2 3 2
+
+
+-- !query 10
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings * s))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 10 schema
+struct
+-- !query 10 output
+2012 15000 2
+2013 96000 6
+
+
+-- !query 11
+SELECT 2012_s, 2013_s, 2012_a, 2013_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ udf(sum(e)) s, udf(avg(e)) a
+ FOR y IN (2012, 2013)
+)
+-- !query 11 schema
+struct<2012_s:string,2013_s:string,2012_a:string,2013_a:string,c:string>
+-- !query 11 output
+15000 48000 7500.0 48000.0 dotNET
+2 3 2.0 3.0 Java
+
+
+-- !query 12
+SELECT firstYear_s, secondYear_s, firstYear_a, secondYear_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ udf(sum(e)) s, udf(avg(e)) a
+ FOR y IN (2012 as firstYear, 2013 secondYear)
+)
+-- !query 12 schema
+struct
+-- !query 12 output
+15000 48000 7500.0 48000.0 dotNET
+2 3 2.0 3.0 Java
+
+
+-- !query 13
+SELECT * FROM courseSales
+PIVOT (
+ udf(abs(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 13 schema
+struct<>
+-- !query 13 output
+org.apache.spark.sql.AnalysisException
+Aggregate expression required for pivot, but 'coursesales.`earnings`' did not
appear in any aggregate function.;
+
+
+-- !query 14
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), year
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 14 schema
+struct<>
+-- !query 14 output
+org.apache.spark.sql.AnalysisException
+Aggregate expression required for pivot, but
'__auto_generated_subquery_name.`year`' did not appear in any aggregate
function.;
+
+
+-- !query 15
+SELECT * FROM (
+ SELECT course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 15 schema
+struct<>
+--
[GitHub] [spark] chitralverma commented on a change in pull request #25122: [SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF test base
chitralverma commented on a change in pull request #25122:
[SPARK-28286][SQL][PYTHON][TESTS][WIP] Convert and port 'pivot.sql' into UDF
test base
URL: https://github.com/apache/spark/pull/25122#discussion_r302817447
##
File path: sql/core/src/test/resources/sql-tests/results/udf/udf-pivot.sql.out
##
@@ -0,0 +1,494 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 32
+
+
+-- !query 0
+create temporary view courseSales as select * from values
+ ("dotNET", 2012, 1),
+ ("Java", 2012, 2),
+ ("dotNET", 2012, 5000),
+ ("dotNET", 2013, 48000),
+ ("Java", 2013, 3)
+ as courseSales(course, year, earnings)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create temporary view years as select * from values
+ (2012, 1),
+ (2013, 2)
+ as years(y, s)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+create temporary view yearsWithComplexTypes as select * from values
+ (2012, array(1, 1), map('1', 1), struct(1, 'a')),
+ (2013, array(2, 2), map('2', 2), struct(2, 'b'))
+ as yearsWithComplexTypes(y, a, m, s)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT * FROM (
+ SELECT udf(year), course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 3 schema
+struct
+-- !query 3 output
+2012 15000 2
+2013 48000 3
+
+
+-- !query 4
+SELECT * FROM courseSales
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 4 schema
+struct
+-- !query 4 output
+Java 2 3
+dotNET 15000 48000
+
+
+-- !query 5
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(avg(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 5 schema
+struct
+-- !query 5 output
+2012 15000 7500.0 2 2.0
+2013 48000 48000.0 3 3.0
+
+
+-- !query 6
+SELECT * FROM (
+ SELECT udf(course) as course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 6 schema
+struct
+-- !query 6 output
+63000 5
+
+
+-- !query 7
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(year))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 7 schema
+struct
+-- !query 7 output
+63000 20125 2012
+
+
+-- !query 8
+SELECT * FROM (
+ SELECT course, year, earnings, udf(s) as s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR s IN (1, 2)
+)
+-- !query 8 schema
+struct
+-- !query 8 output
+Java 20122 nan
+Java 2013nan 3
+dotNET 201215000 nan
+dotNET 2013nan 48000
+
+
+-- !query 9
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings)), udf(min(s))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 9 schema
+struct
+-- !query 9 output
+2012 15000 1 2 1
+2013 48000 2 3 2
+
+
+-- !query 10
+SELECT * FROM (
+ SELECT course, year, earnings, s
+ FROM courseSales
+ JOIN years ON year = y
+)
+PIVOT (
+ udf(sum(earnings * s))
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 10 schema
+struct
+-- !query 10 output
+2012 15000 2
+2013 96000 6
+
+
+-- !query 11
+SELECT 2012_s, 2013_s, 2012_a, 2013_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ udf(sum(e)) s, udf(avg(e)) a
+ FOR y IN (2012, 2013)
+)
+-- !query 11 schema
+struct<2012_s:string,2013_s:string,2012_a:string,2013_a:string,c:string>
+-- !query 11 output
+15000 48000 7500.0 48000.0 dotNET
+2 3 2.0 3.0 Java
+
+
+-- !query 12
+SELECT firstYear_s, secondYear_s, firstYear_a, secondYear_a, c FROM (
+ SELECT year y, course c, earnings e FROM courseSales
+)
+PIVOT (
+ udf(sum(e)) s, udf(avg(e)) a
+ FOR y IN (2012 as firstYear, 2013 secondYear)
+)
+-- !query 12 schema
+struct
+-- !query 12 output
+15000 48000 7500.0 48000.0 dotNET
+2 3 2.0 3.0 Java
+
+
+-- !query 13
+SELECT * FROM courseSales
+PIVOT (
+ udf(abs(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 13 schema
+struct<>
+-- !query 13 output
+org.apache.spark.sql.AnalysisException
+Aggregate expression required for pivot, but 'coursesales.`earnings`' did not
appear in any aggregate function.;
+
+
+-- !query 14
+SELECT * FROM (
+ SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings)), year
+ FOR course IN ('dotNET', 'Java')
+)
+-- !query 14 schema
+struct<>
+-- !query 14 output
+org.apache.spark.sql.AnalysisException
+Aggregate expression required for pivot, but
'__auto_generated_subquery_name.`year`' did not appear in any aggregate
function.;
+
+
+-- !query 15
+SELECT * FROM (
+ SELECT course, earnings FROM courseSales
+)
+PIVOT (
+ udf(sum(earnings))
+ FOR year IN (2012, 2013)
+)
+-- !query 15 schema
+struct<>
+--
