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, 10000),
+  ("Java", 2012, 20000),
+  ("dotNET", 2012, 5000),
+  ("dotNET", 2013, 48000),
+  ("Java", 2013, 30000)
+  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<udf(year):string,dotNET:string,Java:string>
+-- !query 3 output
+2012   15000   20000
+2013   48000   30000
+
+
+-- !query 4
+SELECT * FROM courseSales
+PIVOT (
+  udf(sum(earnings))
+  FOR year IN (2012, 2013)
+)
+-- !query 4 schema
+struct<course:string,2012:string,2013:string>
+-- !query 4 output
+Java   20000   30000
+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<year:int,dotNET_udf(sum(cast(earnings as 
bigint))):string,dotNET_udf(avg(cast(earnings as 
bigint))):string,Java_udf(sum(cast(earnings as 
bigint))):string,Java_udf(avg(cast(earnings as bigint))):string>
+-- !query 5 output
+2012   15000   7500.0  20000   20000.0
+2013   48000   48000.0 30000   30000.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<dotNET:string,Java:string>
+-- !query 6 output
+63000  50000
+
+
+-- !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<dotNET_udf(sum(cast(earnings as 
bigint))):string,dotNET_udf(min(year)):string,Java_udf(sum(cast(earnings as 
bigint))):string,Java_udf(min(year)):string>
+-- !query 7 output
+63000  2012    50000   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<course:string,year:int,1:string,2:string>
+-- !query 8 output
+Java   2012    20000   nan
+Java   2013    nan     30000
+dotNET 2012    15000   nan
+dotNET 2013    nan     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<year:int,dotNET_udf(sum(cast(earnings as 
bigint))):string,dotNET_udf(min(s)):string,Java_udf(sum(cast(earnings as 
bigint))):string,Java_udf(min(s)):string>
+-- !query 9 output
+2012   15000   1       20000   1
+2013   48000   2       30000   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<year:int,dotNET:string,Java:string>
+-- !query 10 output
+2012   15000   20000
+2013   96000   60000
+
+
+-- !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
+20000  30000   20000.0 30000.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<firstYear_s:string,secondYear_s:string,firstYear_a:string,secondYear_a:string,c:string>
+-- !query 12 output
+15000  48000   7500.0  48000.0 dotNET
+20000  30000   20000.0 30000.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<>
+-- !query 15 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 16
+SELECT * FROM (
+  SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+  udf(ceil(udf(sum(earnings)))), avg(earnings) + 1 as a1
+  FOR course IN ('dotNET', 'Java')
+)
+-- !query 16 schema
+struct<year:int,dotNET_udf(CEIL(cast(udf(sum(cast(earnings as bigint))) as 
double))):string,dotNET_a1:double,Java_udf(CEIL(cast(udf(sum(cast(earnings as 
bigint))) as double))):string,Java_a1:double>
+-- !query 16 output
+2012   15000   7501.0  20000   20001.0
+2013   48000   48001.0 30000   30001.0
+
+
+-- !query 17
+SELECT * FROM (
+  SELECT year, course, earnings FROM courseSales
+)
+PIVOT (
+  sum(udf(avg(earnings)))
+  FOR course IN ('dotNET', 'Java')
+)
+-- !query 17 schema
+struct<>
+-- !query 17 output
+org.apache.spark.sql.AnalysisException
+It is not allowed to use an aggregate function in the argument of another 
aggregate function. Please use the inner aggregate function in a sub-query.;
+
+
+-- !query 18
+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))
+)
+-- !query 18 schema
+struct<s:int,[dotNET, 2012]:string,[Java, 2013]:string>
+-- !query 18 output
+1      15000   nan
+2      nan     30000
+
+
+-- !query 19
+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)
+)
+-- !query 19 schema
+struct<year:int,c1:string,c2:string>
+-- !query 19 output
+2012   nan     20000
+2013   48000   nan
+
+
+-- !query 20
+SELECT * FROM (
+  SELECT course, year, earnings, s
+  FROM courseSales
+  JOIN years ON year = y
+)
+PIVOT (
+  udf(sum(earnings))
+  FOR (course, year) IN ('dotNET', 'Java')
+)
+-- !query 20 schema
+struct<>
+-- !query 20 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 21
+SELECT * FROM courseSales
+PIVOT (
+  udf(sum(earnings))
+  FOR year IN (s, 2013)
+)
+-- !query 21 schema
+struct<>
+-- !query 21 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`s`' given input columns: [coursesales.course, 
coursesales.earnings, coursesales.year]; line 4 pos 15
+
+
+-- !query 22
+SELECT * FROM courseSales
+PIVOT (
+  udf(sum(earnings))
+  FOR year IN (course, 2013)
+)
+-- !query 22 schema
+struct<>
+-- !query 22 output
+org.apache.spark.sql.AnalysisException
+Literal expressions required for pivot values, found 'course#x';
+
+
+-- !query 23
+SELECT * FROM (
+  SELECT course, year, a
+  FROM courseSales
+  JOIN yearsWithComplexTypes ON year = y
+)
+PIVOT (
+  udf(min(a))
+  FOR course IN ('dotNET', 'Java')
+)
+-- !query 23 schema
+struct<year:int,dotNET:string,Java:string>
+-- !query 23 output
+2012   [1 1]   [1 1]
+2013   [2 2]   [2 2]
+
+
+-- !query 24
+SELECT * FROM (
+  SELECT course, year, y, a
+  FROM courseSales
+  JOIN yearsWithComplexTypes ON year = y
+)
+PIVOT (
+  udf(max(a))
+  FOR (y, course) IN ((2012, 'dotNET'), (2013, 'Java'))
+)
+-- !query 24 schema
+struct<year:int,[2012, dotNET]:string,[2013, Java]:string>
+-- !query 24 output
+2012   [1 1]   None
+2013   None    [2 2]
 
 Review comment:
   @HyukjinKwon For this particular case (query 24) the `CAST` wont work as its 
is dealing with Array type columns, which again will have different 
representations in Scala (WrappedArray) and Python. AFAIK, casting `array` type 
to `int` will result in `null`/`nan` which is again a problem.   

----------------------------------------------------------------
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

Reply via email to