You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by we...@apache.org on 2023/02/28 00:39:15 UTC
[spark] branch master updated: [SPARK-42121][SQL] Add built-in table-valued functions posexplode, posexplode_outer, json_tuple and stack
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 <al...@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 <al...@databricks.com>
Signed-off-by: Wenchen Fan <we...@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-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org