cloud-fan commented on code in PR #37407:
URL: https://github.com/apache/spark/pull/37407#discussion_r988504705
##########
sql/core/src/test/resources/sql-tests/results/unpivot.sql.out:
##########
@@ -0,0 +1,409 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+create temporary view courseEarnings as select * from values
+ ("dotNET", 15000, 48000, 22500),
+ ("Java", 20000, 30000, NULL)
+ as courseEarnings(course, `2012`, `2013`, `2014`)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM courseEarnings
+UNPIVOT (
+ earningsYear FOR year IN (`2012`, `2013`, `2014`)
+)
+-- !query schema
+struct<course:string,year:string,earningsYear:int>
+-- !query output
+Java 2012 20000
+Java 2013 30000
+dotNET 2012 15000
+dotNET 2013 48000
+dotNET 2014 22500
+
+
+-- !query
+SELECT * FROM courseEarnings
+UNPIVOT INCLUDE NULLS (
+ earningsYear FOR year IN (`2012`, `2013`, `2014`)
+)
+-- !query schema
+struct<course:string,year:string,earningsYear:int>
+-- !query output
+Java 2012 20000
+Java 2013 30000
+Java 2014 NULL
+dotNET 2012 15000
+dotNET 2013 48000
+dotNET 2014 22500
+
+
+-- !query
+SELECT * FROM courseEarnings
+UNPIVOT EXCLUDE NULLS (
+ earningsYear FOR year IN (`2012`, `2013`, `2014`)
+)
+-- !query schema
+struct<course:string,year:string,earningsYear:int>
+-- !query output
+Java 2012 20000
+Java 2013 30000
+dotNET 2012 15000
+dotNET 2013 48000
+dotNET 2014 22500
+
+
+-- !query
+SELECT * FROM (
+ SELECT course, `the.earnings`.* FROM (
+ SELECT course, struct(`2012`, `2013`, `2014`) AS `the.earnings`
+ FROM courseEarnings
+ )
+)
+UNPIVOT (
+ earningsYear FOR year IN (`2012`, `2013`, `2014`)
+)
+-- !query schema
+struct<course:string,year:string,earningsYear:int>
+-- !query output
+Java 2012 20000
+Java 2013 30000
+dotNET 2012 15000
+dotNET 2013 48000
+dotNET 2014 22500
+
+
+-- !query
+SELECT * FROM (
+ SELECT course, struct(`2012`, `2013`, `2014`) AS `the.earnings`
+ FROM courseEarnings
+)
+UNPIVOT (
+ earningsYear FOR year IN (`the.earnings`.`2012`, `the.earnings`.`2013`,
`the.earnings`.`2014`)
+)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNPIVOT_REQUIRES_ATTRIBUTES",
+ "sqlState" : "42000",
+ "messageParameters" : {
+ "empty" : "ids",
+ "given" : "values",
+ "types" : "Alias (the.earnings.2012 AS the.earnings.2012,
the.earnings.2013 AS the.earnings.2013, the.earnings.2014 AS the.earnings.2014)"
+ }
+}
+
+
+-- !query
+SELECT * FROM courseEarnings
+UNPIVOT (
+ earningsYear FOR year IN (`2013`, `2014`)
+)
+-- !query schema
+struct<course:string,2012:int,year:string,earningsYear:int>
+-- !query output
+Java 20000 2013 30000
+dotNET 15000 2013 48000
+dotNET 15000 2014 22500
+
+
+-- !query
+SELECT * FROM courseEarningsAndSales
+UNPIVOT (
+ values FOR year IN ()
+)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42000",
+ "messageParameters" : {
+ "error" : "')'",
+ "hint" : ""
+ }
+}
+
+
+-- !query
+SELECT * FROM courseEarnings
+UNPIVOT (
+ earningsYear FOR year IN (`2012` as `twenty-twelve`, `2013` as
`twenty-thirteen`, `2014` as `twenty-fourteen`)
+)
+-- !query schema
+struct<course:string,year:string,earningsYear:int>
+-- !query output
+Java twenty-thirteen 30000
+Java twenty-twelve 20000
+dotNET twenty-fourteen 22500
+dotNET twenty-thirteen 48000
+dotNET twenty-twelve 15000
+
+
+-- !query
+SELECT up.* FROM courseEarnings
+UNPIVOT (
+ earningsYear FOR year IN (`2012`, `2013`, `2014`)
+) up
+-- !query schema
+struct<course:string,year:string,earningsYear:int>
+-- !query output
+Java 2012 20000
+Java 2013 30000
+dotNET 2012 15000
+dotNET 2013 48000
+dotNET 2014 22500
+
+
+-- !query
+SELECT up.* FROM courseEarnings
+UNPIVOT (
+ earningsYear FOR year IN (`2012`, `2013`, `2014`)
+) AS up
Review Comment:
nit: ideally the SQL test should focus on the end-user behavior, instead of
tiny details like the optional `AS` keyword. We can have a `UnpivotParserSuite`
to focus on these details. See `DDLParserSuite` as an example.
--
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.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
