This is an automated email from the ASF dual-hosted git repository. wenchen pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push: new a7e61f9cbd1 [SPARK-42121][SQL] Add built-in table-valued functions posexplode, posexplode_outer, json_tuple and stack a7e61f9cbd1 is described below commit a7e61f9cbd17c8eb3d3281c2ca09dba602ee86af Author: allisonwang-db <allison.w...@databricks.com> AuthorDate: Tue Feb 28 08:38:50 2023 +0800 [SPARK-42121][SQL] Add built-in table-valued functions posexplode, posexplode_outer, json_tuple and stack ### What changes were proposed in this pull request? This PR adds new builtin table-valued functions `posexplode`, `posexplode_outer`, `json_tuple` and `stack`. ### Why are the changes needed? To improve the usability of table-valued generator functions. Now all generate functions can be used as table value functions. ### Does this PR introduce _any_ user-facing change? Yes. After this PR, 4 new table-valued generator functions can be used in the FROM clause of a query. ### How was this patch tested? New SQL query tests Closes #40151 from allisonwang-db/spark-42121-posexplode. Authored-by: allisonwang-db <allison.w...@databricks.com> Signed-off-by: Wenchen Fan <wenc...@databricks.com> --- .../sql/catalyst/analysis/FunctionRegistry.scala | 6 +- .../resources/sql-tests/inputs/join-lateral.sql | 35 ++ .../sql-tests/inputs/table-valued-functions.sql | 48 +++ .../sql-tests/results/join-lateral.sql.out | 207 +++++++++ .../results/table-valued-functions.sql.out | 473 +++++++++++++++++++++ 5 files changed, 768 insertions(+), 1 deletion(-) diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala index d87cc0126cf..103e6aae603 100644 --- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala +++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala @@ -987,7 +987,11 @@ object TableFunctionRegistry { generator[Explode]("explode"), generator[Explode]("explode_outer", outer = true), generator[Inline]("inline"), - generator[Inline]("inline_outer", outer = true) + generator[Inline]("inline_outer", outer = true), + generator[JsonTuple]("json_tuple"), + generator[PosExplode]("posexplode"), + generator[PosExplode]("posexplode_outer", outer = true), + generator[Stack]("stack") ) val builtin: SimpleTableFunctionRegistry = { diff --git a/sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql b/sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql index 408a152d9b8..29ff29d6630 100644 --- a/sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql +++ b/sql/core/src/test/resources/sql-tests/inputs/join-lateral.sql @@ -464,6 +464,41 @@ SELECT * FROM array_struct LEFT JOIN LATERAL INLINE(arr) t(k, v) ON id = k; SELECT * FROM array_struct JOIN LATERAL INLINE_OUTER(arr); DROP VIEW array_struct; +-- lateral join with table-valued functions posexplode and posexplode_outer +SELECT * FROM LATERAL posexplode(ARRAY(1, 2)); +SELECT * FROM t1, LATERAL posexplode(ARRAY(c1, c2)) t2(pos, c3); +SELECT * FROM t1 JOIN LATERAL posexplode(ARRAY(c1, c2)) t(pos, c3) ON t1.c1 = c3; +SELECT * FROM t3, LATERAL posexplode(c2) t2(pos, v); +SELECT * FROM t3 JOIN LATERAL posexplode(c2) t(pos, c3) ON t3.c1 = c3; +SELECT * FROM t3, LATERAL posexplode_outer(c2) t2(pos, v); +SELECT * FROM t3 LEFT JOIN LATERAL posexplode(c2) t(pos, c3) ON t3.c1 = c3; +SELECT * FROM t3 LEFT JOIN LATERAL posexplode_outer(c2) t(pos, c3) ON t3.c1 = c3; + +-- lateral join with table-valued function json_tuple +CREATE OR REPLACE TEMP VIEW json_table(key, jstring) AS VALUES + ('1', '{"f1": "1", "f2": "2", "f3": 3, "f5": 5.23}'), + ('2', '{"f1": "1", "f3": "3", "f2": 2, "f4": 4.01}'), + ('3', '{"f1": 3, "f4": "4", "f3": "3", "f2": 2, "f5": 5.01}'), + ('4', cast(null as string)), + ('5', '{"f1": null, "f5": ""}'), + ('6', '[invalid JSON string]'); +SELECT t1.key, t2.* FROM json_table t1, LATERAL json_tuple(t1.jstring, 'f1', 'f2', 'f3', 'f4', 'f5') t2; +SELECT t1.key, t2.* FROM json_table t1, LATERAL json_tuple(t1.jstring, 'f1', 'f2', 'f3', 'f4', 'f5') t2 WHERE t2.c0 IS NOT NULL; +SELECT t1.key, t2.* FROM json_table t1 + JOIN LATERAL json_tuple(t1.jstring, 'f1', 'f2', 'f3', 'f4', 'f5') t2(f1, f2, f3, f4, f5) + ON t1.key = t2.f1; +SELECT t1.key, t2.* FROM json_table t1 + LEFT JOIN LATERAL json_tuple(t1.jstring, 'f1', 'f2', 'f3', 'f4', 'f5') t2(f1, f2, f3, f4, f5) + ON t1.key = t2.f1; +DROP VIEW json_table; + +-- lateral join with table-valued function stack +SELECT t.* FROM t1, LATERAL stack(2, 'Key', c1, 'Value', c2) t; +SELECT t.* FROM t1 JOIN LATERAL stack(1, c1, c2) t(x, y); +SELECT t.* FROM t1 JOIN t3 ON t1.c1 = t3.c1 JOIN LATERAL stack(1, t1.c2, t3.c2) t; +-- expect error +SELECT t.* FROM t1, LATERAL stack(c1, c2); + -- clean up DROP VIEW t1; DROP VIEW t2; diff --git a/sql/core/src/test/resources/sql-tests/inputs/table-valued-functions.sql b/sql/core/src/test/resources/sql-tests/inputs/table-valued-functions.sql index b00a22d4c15..2b809f9a7c8 100644 --- a/sql/core/src/test/resources/sql-tests/inputs/table-valued-functions.sql +++ b/sql/core/src/test/resources/sql-tests/inputs/table-valued-functions.sql @@ -73,3 +73,51 @@ select * from inline(array(struct(1, 2), struct(2, 3))) t(a, b, c); -- inline_outer select * from inline_outer(array(struct(1, 'a'), struct(2, 'b'))); select * from inline_outer(array_remove(array(struct(1, 'a')), struct(1, 'a'))); + +-- posexplode +select * from posexplode(array()); +select * from posexplode(array(1, 2)); +select * from posexplode(array(1, 2)) t(pos, x); +select * from posexplode(map()); +select * from posexplode(map('a', 1, 'b', 2)); +select * from posexplode(map('a', 1, 'b', 2)) t(pos, k, v); + +-- posexplode with erroneous input +select * from posexplode(1); +select * from posexplode(1, 2); +select * from posexplode(explode(array(1))); +select * from posexplode(array(1, 2)) t(x); + +-- posexplode +select * from posexplode_outer(array()); +select * from posexplode_outer(array(1, 2)); +select * from posexplode_outer(map()); +select * from posexplode_outer(map('a', 1, 'b', 2)); + +-- json_tuple +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'b'); +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'c'); +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'a'); +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'b') AS t(x, y); +select * from json_tuple('{"a": bad, "b": string}', 'a', 'b'); + +-- json_tuple with erroneous input +select * from json_tuple(); +select * from json_tuple('{"a": 1}'); +select * from json_tuple('{"a": 1}', 1); +select * from json_tuple('{"a": 1}', null); +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'b') AS t(x); + +-- stack +select * from stack(1, 1, 2, 3); +select * from stack(2, 1, 2, 3); +select * from stack(3, 1, 2, 3) t(x); +select * from stack(4, 1, 2, 3) t(x); +select * from stack(2, 1, 1.1, 'a', 2, 2.2, 'b') t(a, b, c); +select * from stack(2, 1, 1.1, null, 2, null, 'b') t(a, b, c); + +-- stack with erroneous input +select * from stack(); +select * from stack(2, 1, 2, 3) t(a, b, c); +select * from stack(2, 1, '1.1', 'a', 2, 2.2, 'b'); +select * from stack(2, explode(array(1, 2, 3))); diff --git a/sql/core/src/test/resources/sql-tests/results/join-lateral.sql.out b/sql/core/src/test/resources/sql-tests/results/join-lateral.sql.out index 0324320a0e5..bad4407a3f1 100644 --- a/sql/core/src/test/resources/sql-tests/results/join-lateral.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/join-lateral.sql.out @@ -1568,6 +1568,213 @@ struct<> +-- !query +SELECT * FROM LATERAL posexplode(ARRAY(1, 2)) +-- !query schema +struct<pos:int,col:int> +-- !query output +0 1 +1 2 + + +-- !query +SELECT * FROM t1, LATERAL posexplode(ARRAY(c1, c2)) t2(pos, c3) +-- !query schema +struct<c1:int,c2:int,pos:int,c3:int> +-- !query output +0 1 0 0 +0 1 1 1 +1 2 0 1 +1 2 1 2 + + +-- !query +SELECT * FROM t1 JOIN LATERAL posexplode(ARRAY(c1, c2)) t(pos, c3) ON t1.c1 = c3 +-- !query schema +struct<c1:int,c2:int,pos:int,c3:int> +-- !query output +0 1 0 0 +1 2 0 1 + + +-- !query +SELECT * FROM t3, LATERAL posexplode(c2) t2(pos, v) +-- !query schema +struct<c1:int,c2:array<int>,pos:int,v:int> +-- !query output +0 [0,1] 0 0 +0 [0,1] 1 1 +1 [2] 0 2 +NULL [4] 0 4 + + +-- !query +SELECT * FROM t3 JOIN LATERAL posexplode(c2) t(pos, c3) ON t3.c1 = c3 +-- !query schema +struct<c1:int,c2:array<int>,pos:int,c3:int> +-- !query output +0 [0,1] 0 0 + + +-- !query +SELECT * FROM t3, LATERAL posexplode_outer(c2) t2(pos, v) +-- !query schema +struct<c1:int,c2:array<int>,pos:int,v:int> +-- !query output +0 [0,1] 0 0 +0 [0,1] 1 1 +1 [2] 0 2 +2 [] NULL NULL +NULL [4] 0 4 + + +-- !query +SELECT * FROM t3 LEFT JOIN LATERAL posexplode(c2) t(pos, c3) ON t3.c1 = c3 +-- !query schema +struct<c1:int,c2:array<int>,pos:int,c3:int> +-- !query output +0 [0,1] 0 0 +1 [2] NULL NULL +2 [] NULL NULL +NULL [4] NULL NULL + + +-- !query +SELECT * FROM t3 LEFT JOIN LATERAL posexplode_outer(c2) t(pos, c3) ON t3.c1 = c3 +-- !query schema +struct<c1:int,c2:array<int>,pos:int,c3:int> +-- !query output +0 [0,1] 0 0 +1 [2] NULL NULL +2 [] NULL NULL +NULL [4] NULL NULL + + +-- !query +CREATE OR REPLACE TEMP VIEW json_table(key, jstring) AS VALUES + ('1', '{"f1": "1", "f2": "2", "f3": 3, "f5": 5.23}'), + ('2', '{"f1": "1", "f3": "3", "f2": 2, "f4": 4.01}'), + ('3', '{"f1": 3, "f4": "4", "f3": "3", "f2": 2, "f5": 5.01}'), + ('4', cast(null as string)), + ('5', '{"f1": null, "f5": ""}'), + ('6', '[invalid JSON string]') +-- !query schema +struct<> +-- !query output + + + +-- !query +SELECT t1.key, t2.* FROM json_table t1, LATERAL json_tuple(t1.jstring, 'f1', 'f2', 'f3', 'f4', 'f5') t2 +-- !query schema +struct<key:string,c0:string,c1:string,c2:string,c3:string,c4:string> +-- !query output +1 1 2 3 NULL 5.23 +2 1 2 3 4.01 NULL +3 3 2 3 4 5.01 +4 NULL NULL NULL NULL NULL +5 NULL NULL NULL NULL +6 NULL NULL NULL NULL NULL + + +-- !query +SELECT t1.key, t2.* FROM json_table t1, LATERAL json_tuple(t1.jstring, 'f1', 'f2', 'f3', 'f4', 'f5') t2 WHERE t2.c0 IS NOT NULL +-- !query schema +struct<key:string,c0:string,c1:string,c2:string,c3:string,c4:string> +-- !query output +1 1 2 3 NULL 5.23 +2 1 2 3 4.01 NULL +3 3 2 3 4 5.01 + + +-- !query +SELECT t1.key, t2.* FROM json_table t1 + JOIN LATERAL json_tuple(t1.jstring, 'f1', 'f2', 'f3', 'f4', 'f5') t2(f1, f2, f3, f4, f5) + ON t1.key = t2.f1 +-- !query schema +struct<key:string,f1:string,f2:string,f3:string,f4:string,f5:string> +-- !query output +1 1 2 3 NULL 5.23 +3 3 2 3 4 5.01 + + +-- !query +SELECT t1.key, t2.* FROM json_table t1 + LEFT JOIN LATERAL json_tuple(t1.jstring, 'f1', 'f2', 'f3', 'f4', 'f5') t2(f1, f2, f3, f4, f5) + ON t1.key = t2.f1 +-- !query schema +struct<key:string,f1:string,f2:string,f3:string,f4:string,f5:string> +-- !query output +1 1 2 3 NULL 5.23 +2 NULL NULL NULL NULL NULL +3 3 2 3 4 5.01 +4 NULL NULL NULL NULL NULL +5 NULL NULL NULL NULL NULL +6 NULL NULL NULL NULL NULL + + +-- !query +DROP VIEW json_table +-- !query schema +struct<> +-- !query output + + + +-- !query +SELECT t.* FROM t1, LATERAL stack(2, 'Key', c1, 'Value', c2) t +-- !query schema +struct<col0:string,col1:int> +-- !query output +Key 0 +Key 1 +Value 1 +Value 2 + + +-- !query +SELECT t.* FROM t1 JOIN LATERAL stack(1, c1, c2) t(x, y) +-- !query schema +struct<x:int,y:int> +-- !query output +0 1 +1 2 + + +-- !query +SELECT t.* FROM t1 JOIN t3 ON t1.c1 = t3.c1 JOIN LATERAL stack(1, t1.c2, t3.c2) t +-- !query schema +struct<col0:int,col1:array<int>> +-- !query output +1 [0,1] +2 [2] + + +-- !query +SELECT t.* FROM t1, LATERAL stack(c1, c2) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "DATATYPE_MISMATCH.NON_FOLDABLE_INPUT", + "sqlState" : "42K09", + "messageParameters" : { + "inputExpr" : "\"outer(spark_catalog.default.t1.c1)\"", + "inputName" : "n", + "inputType" : "\"INT\"", + "sqlExpr" : "\"stack(outer(spark_catalog.default.t1.c1), outer(spark_catalog.default.t1.c2))\"" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 29, + "stopIndex" : 41, + "fragment" : "stack(c1, c2)" + } ] +} + + -- !query DROP VIEW t1 -- !query schema diff --git a/sql/core/src/test/resources/sql-tests/results/table-valued-functions.sql.out b/sql/core/src/test/resources/sql-tests/results/table-valued-functions.sql.out index e381c798465..64ae32da28a 100644 --- a/sql/core/src/test/resources/sql-tests/results/table-valued-functions.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/table-valued-functions.sql.out @@ -534,3 +534,476 @@ select * from inline_outer(array_remove(array(struct(1, 'a')), struct(1, 'a'))) struct<col1:int,col2:string> -- !query output NULL NULL + + +-- !query +select * from posexplode(array()) +-- !query schema +struct<pos:int,col:void> +-- !query output + + + +-- !query +select * from posexplode(array(1, 2)) +-- !query schema +struct<pos:int,col:int> +-- !query output +0 1 +1 2 + + +-- !query +select * from posexplode(array(1, 2)) t(pos, x) +-- !query schema +struct<pos:int,x:int> +-- !query output +0 1 +1 2 + + +-- !query +select * from posexplode(map()) +-- !query schema +struct<pos:int,key:void,value:void> +-- !query output + + + +-- !query +select * from posexplode(map('a', 1, 'b', 2)) +-- !query schema +struct<pos:int,key:string,value:int> +-- !query output +0 a 1 +1 b 2 + + +-- !query +select * from posexplode(map('a', 1, 'b', 2)) t(pos, k, v) +-- !query schema +struct<pos:int,k:string,v:int> +-- !query output +0 a 1 +1 b 2 + + +-- !query +select * from posexplode(1) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE", + "sqlState" : "42K09", + "messageParameters" : { + "inputSql" : "\"1\"", + "inputType" : "\"INT\"", + "paramIndex" : "1", + "requiredType" : "(\"ARRAY\" or \"MAP\")", + "sqlExpr" : "\"posexplode(1)\"" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 27, + "fragment" : "posexplode(1)" + } ] +} + + +-- !query +select * from posexplode(1, 2) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION", + "sqlState" : "42605", + "messageParameters" : { + "actualNum" : "2", + "docroot" : "https://spark.apache.org/docs/latest", + "expectedNum" : "1", + "functionName" : "`posexplode`" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 30, + "fragment" : "posexplode(1, 2)" + } ] +} + + +-- !query +select * from posexplode(explode(array(1))) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "UNSUPPORTED_GENERATOR.NESTED_IN_EXPRESSIONS", + "sqlState" : "0A000", + "messageParameters" : { + "expression" : "\"posexplode(explode(array(1)))\"" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 43, + "fragment" : "posexplode(explode(array(1)))" + } ] +} + + +-- !query +select * from posexplode(array(1, 2)) t(x) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "_LEGACY_ERROR_TEMP_2307", + "messageParameters" : { + "aliasesNum" : "1", + "funcName" : "posexplode", + "outColsNum" : "2" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 42, + "fragment" : "posexplode(array(1, 2)) t(x)" + } ] +} + + +-- !query +select * from posexplode_outer(array()) +-- !query schema +struct<pos:int,col:void> +-- !query output +NULL NULL + + +-- !query +select * from posexplode_outer(array(1, 2)) +-- !query schema +struct<pos:int,col:int> +-- !query output +0 1 +1 2 + + +-- !query +select * from posexplode_outer(map()) +-- !query schema +struct<pos:int,key:void,value:void> +-- !query output +NULL NULL NULL + + +-- !query +select * from posexplode_outer(map('a', 1, 'b', 2)) +-- !query schema +struct<pos:int,key:string,value:int> +-- !query output +0 a 1 +1 b 2 + + +-- !query +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'b') +-- !query schema +struct<c0:string,c1:string> +-- !query output +1 2 + + +-- !query +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'c') +-- !query schema +struct<c0:string,c1:string> +-- !query output +1 NULL + + +-- !query +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'a') +-- !query schema +struct<c0:string,c1:string> +-- !query output +1 1 + + +-- !query +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'b') AS t(x, y) +-- !query schema +struct<x:string,y:string> +-- !query output +1 2 + + +-- !query +select * from json_tuple('{"a": bad, "b": string}', 'a', 'b') +-- !query schema +struct<c0:string,c1:string> +-- !query output +NULL NULL + + +-- !query +select * from json_tuple() +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION", + "sqlState" : "42605", + "messageParameters" : { + "actualNum" : "0", + "docroot" : "https://spark.apache.org/docs/latest", + "expectedNum" : "> 1", + "functionName" : "`json_tuple`" + } +} + + +-- !query +select * from json_tuple('{"a": 1}') +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION", + "sqlState" : "42605", + "messageParameters" : { + "actualNum" : "1", + "docroot" : "https://spark.apache.org/docs/latest", + "expectedNum" : "> 1", + "functionName" : "`json_tuple`" + } +} + + +-- !query +select * from json_tuple('{"a": 1}', 1) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "DATATYPE_MISMATCH.NON_STRING_TYPE", + "sqlState" : "42K09", + "messageParameters" : { + "funcName" : "`json_tuple`", + "sqlExpr" : "\"json_tuple({\"a\": 1}, 1)\"" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 39, + "fragment" : "json_tuple('{\"a\": 1}', 1)" + } ] +} + + +-- !query +select * from json_tuple('{"a": 1}', null) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "DATATYPE_MISMATCH.NON_STRING_TYPE", + "sqlState" : "42K09", + "messageParameters" : { + "funcName" : "`json_tuple`", + "sqlExpr" : "\"json_tuple({\"a\": 1}, NULL)\"" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 42, + "fragment" : "json_tuple('{\"a\": 1}', null)" + } ] +} + + +-- !query +select * from json_tuple('{"a": 1, "b": 2}', 'a', 'b') AS t(x) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "_LEGACY_ERROR_TEMP_2307", + "messageParameters" : { + "aliasesNum" : "1", + "funcName" : "json_tuple", + "outColsNum" : "2" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 62, + "fragment" : "json_tuple('{\"a\": 1, \"b\": 2}', 'a', 'b') AS t(x)" + } ] +} + + +-- !query +select * from stack(1, 1, 2, 3) +-- !query schema +struct<col0:int,col1:int,col2:int> +-- !query output +1 2 3 + + +-- !query +select * from stack(2, 1, 2, 3) +-- !query schema +struct<col0:int,col1:int> +-- !query output +1 2 +3 NULL + + +-- !query +select * from stack(3, 1, 2, 3) t(x) +-- !query schema +struct<x:int> +-- !query output +1 +2 +3 + + +-- !query +select * from stack(4, 1, 2, 3) t(x) +-- !query schema +struct<x:int> +-- !query output +1 +2 +3 +NULL + + +-- !query +select * from stack(2, 1, 1.1, 'a', 2, 2.2, 'b') t(a, b, c) +-- !query schema +struct<a:int,b:decimal(2,1),c:string> +-- !query output +1 1.1 a +2 2.2 b + + +-- !query +select * from stack(2, 1, 1.1, null, 2, null, 'b') t(a, b, c) +-- !query schema +struct<a:int,b:decimal(2,1),c:string> +-- !query output +1 1.1 NULL +2 NULL b + + +-- !query +select * from stack() +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "WRONG_NUM_ARGS.WITHOUT_SUGGESTION", + "sqlState" : "42605", + "messageParameters" : { + "actualNum" : "0", + "docroot" : "https://spark.apache.org/docs/latest", + "expectedNum" : "> 1", + "functionName" : "`stack`" + } +} + + +-- !query +select * from stack(2, 1, 2, 3) t(a, b, c) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "_LEGACY_ERROR_TEMP_2307", + "messageParameters" : { + "aliasesNum" : "3", + "funcName" : "stack", + "outColsNum" : "2" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 42, + "fragment" : "stack(2, 1, 2, 3) t(a, b, c)" + } ] +} + + +-- !query +select * from stack(2, 1, '1.1', 'a', 2, 2.2, 'b') +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "DATATYPE_MISMATCH.STACK_COLUMN_DIFF_TYPES", + "sqlState" : "42K09", + "messageParameters" : { + "columnIndex" : "1", + "leftParamIndex" : "2", + "leftType" : "\"STRING\"", + "rightParamIndex" : "5", + "rightType" : "\"DECIMAL(2,1)\"", + "sqlExpr" : "\"stack(2, 1, 1.1, a, 2, 2.2, b)\"" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 50, + "fragment" : "stack(2, 1, '1.1', 'a', 2, 2.2, 'b')" + } ] +} + + +-- !query +select * from stack(2, explode(array(1, 2, 3))) +-- !query schema +struct<> +-- !query output +org.apache.spark.sql.AnalysisException +{ + "errorClass" : "UNSUPPORTED_GENERATOR.NESTED_IN_EXPRESSIONS", + "sqlState" : "0A000", + "messageParameters" : { + "expression" : "\"stack(2, explode(array(1, 2, 3)))\"" + }, + "queryContext" : [ { + "objectType" : "", + "objectName" : "", + "startIndex" : 15, + "stopIndex" : 47, + "fragment" : "stack(2, explode(array(1, 2, 3)))" + } ] +} --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org