This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new c10fe9017577 [SPARK-58419][SQL] Support ANSI SQL UNNEST in the FROM
clause
c10fe9017577 is described below
commit c10fe90175771b81998df6185a6b5912ec8a60b6
Author: Ganesha Shreedhara <[email protected]>
AuthorDate: Fri Jul 31 21:06:11 2026 +0800
[SPARK-58419][SQL] Support ANSI SQL UNNEST in the FROM clause
### What changes were proposed in this pull request?
This PR adds support for the ANSI SQL `UNNEST` collection derived table in
the FROM clause:
```sql
UNNEST ( expression [ , ... ] ) [ WITH ORDINALITY ] [ table_alias ]
```
UNNEST expands one or more arrays into a relation, producing one row per
element:
- Multiple arrays are expanded in parallel: the number of output rows
equals the length of the longest array, and shorter arrays are padded with
NULLs.
- A NULL array is treated as an empty array and contributes no rows (a NULL
element, by contrast, produces a NULL row).
- WITH ORDINALITY appends a trailing 1-based BIGINT position column.
- Correlated arrays are supported via LATERAL (FROM t, LATERAL
UNNEST(t.arr)), reusing the existing lateral-join
machinery, and LEFT JOIN LATERAL UNNEST(...) ON true preserves outer rows
whose array is empty or NULL.
- Only ARRAY arguments are accepted. Each array contributes exactly one
output column holding its element as-is; unlike inline, an array of structs is
not expanded into one column per field.
The semantics follow PostgreSQL and Trino (parallel multi-array expansion
with NULL padding, 1-based WITH ORDINALITY). BigQuery's single-array UNNEST and
0-based WITH OFFSET are intentionally not adopted.
**References:**
- PostgreSQL:
https://www.postgresql.org/docs/17/queries-table-expressions.html
- Trino: https://trino.io/docs/current/sql/select.html
- BigQuery:
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
Implementation notes:
- Grammar: UNNEST and ORDINALITY are added as non-reserved keywords (like
PIVOT/UNPIVOT), so existing identifiers named unnest/ordinality keep working. A
dedicated relationPrimary alternative (#unnestTable) and an unnest production
are added to SqlBaseParser.g4, with matching lexer tokens. A table-valued
function named unnest can still be invoked by quoting the name: `unnest`(...).
- A new Unnest generator expression implements the zip-and-pad plus
ordinality semantics. AstBuilder.visitUnnestTable desugars UNNEST into a
Generate over a OneRowRelation, reusing the shared FROM-clause aliasing helper
(mayApplyAliasPlan). UnnestTableContext is whitelisted in the two LATERAL
validation sites so correlated and LEFT JOIN LATERAL forms work.
- Unnest uses interpreted evaluation (CodegenFallback) with a lazy iterator
(one output row built per pull), consistent with other non-CollectionGenerator
generators such as ReplicateRows. A dedicated whole-stage-codegen path is left
as future work (tracked separately) and is documented in the class scaladoc.
Example:
-- multiple arrays, parallel expansion with NULL padding, plus ordinality
```SELECT * FROM UNNEST(array(1, 2), array(10, 20, 30)) WITH ORDINALITY AS
t(a, b, ord);```
```
+----+---+---+
| a| b|ord|
+----+---+---+
| 1| 10| 1|
| 2| 20| 2|
|NULL| 30| 3|
+----+---+---+
```
### Why are the changes needed?
UNNEST is part of the SQL:2016 standard and is available in PostgreSQL,
Trino/Presto, and BigQuery (and, via FLATTEN, Snowflake). Spark has no
equivalent ANSI syntax today: users must rewrite UNNEST(...) to LATERAL VIEW
EXPLODE or the DataFrame explode API. This is a recurring pain point in
migrations when porting queries from other engines. Array expansion is an
everyday operation, so this closes a genuine standards-compliance gap with
broad reach.
### Does this PR introduce _any_ user-facing change?
Yes. UNNEST(...) [WITH ORDINALITY] can now be used in the FROM clause. This
is a new feature, not a change to existing behavior; UNNEST and ORDINALITY
remain valid as regular (non-reserved) identifiers, so existing queries continue
to parse unchanged. New user-facing SQL reference documentation is added
under docs/sql-ref-syntax-qry-select-unnest.md.
### How was this patch tested?
Added new tests:
- GeneratorExpressionSuite — unit tests for the Unnest generator: single
and multiple arrays, WITH ORDINALITY, empty/NULL arrays, column
naming/nullability, lazy evaluation (elements read only as rows are pulled),
string/EXPLAIN representation, and type-check errors.
- PlanParserSuite — parser tests: single/multiple arrays, WITH ORDINALITY,
table and column aliases, LATERAL correlation, non-reserved keyword backwards
compatibility (unnest/ordinality as identifiers), and the quoted-name TVF
escape hatch.
- SQLQueryTestSuite golden-file unnest.sql — end-to-end coverage including
parallel padding, NULL vs empty arrays, NULL elements, nested arrays, LEFT JOIN
LATERAL outer-row preservation, arrays of structs, and error cases (non-array
and MAP arguments).
Regression: existing suites that reference unnest/ordinality (e.g.
postgreSQL/with.sql, which uses ordinality as a CTE and table name) pass
without golden-file regeneration, and generators.sql,
generators-resolution-edge-cases.sql, join-lateral.sql, and postgreSQL/join.sql
all pass.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code (Opus 4.8)
Closes #57630 from ganeshashree/SPARK-58419.
Lead-authored-by: Ganesha Shreedhara <[email protected]>
Co-authored-by: Ganesha S <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit fafb64c16c179ddd8057b47344a3a14f6a772090)
Signed-off-by: Wenchen Fan <[email protected]>
---
docs/sql-ref-ansi-compliance.md | 2 +
docs/sql-ref-syntax-qry-select-unnest.md | 113 +++++++++
docs/sql-ref-syntax-qry-select.md | 1 +
docs/sql-ref-syntax.md | 1 +
.../spark/sql/catalyst/parser/SqlBaseLexer.g4 | 2 +
.../spark/sql/catalyst/parser/SqlBaseParser.g4 | 13 +
.../sql/catalyst/expressions/generators.scala | 121 +++++++++
.../spark/sql/catalyst/parser/AstBuilder.scala | 24 ++
.../expressions/GeneratorExpressionSuite.scala | 103 ++++++++
.../sql/catalyst/parser/PlanParserSuite.scala | 79 ++++++
.../jdbc/SparkConnectDatabaseMetaDataSuite.scala | 2 +-
.../sql-tests/analyzer-results/unnest.sql.out | 278 +++++++++++++++++++++
.../src/test/resources/sql-tests/inputs/unnest.sql | 72 ++++++
.../sql-tests/results/keywords-enforced.sql.out | 2 +
.../resources/sql-tests/results/keywords.sql.out | 2 +
.../sql-tests/results/nonansi/keywords.sql.out | 2 +
.../resources/sql-tests/results/unnest.sql.out | 267 ++++++++++++++++++++
.../ThriftServerWithSparkContextSuite.scala | 2 +-
18 files changed, 1084 insertions(+), 2 deletions(-)
diff --git a/docs/sql-ref-ansi-compliance.md b/docs/sql-ref-ansi-compliance.md
index f4ac58a4e3f0..0b8c7c99b77f 100644
--- a/docs/sql-ref-ansi-compliance.md
+++ b/docs/sql-ref-ansi-compliance.md
@@ -684,6 +684,7 @@ Below is a list of all the keywords in Spark SQL.
|OPTIONS|non-reserved|non-reserved|non-reserved|
|OR|reserved|non-reserved|reserved|
|ORDER|reserved|non-reserved|reserved|
+|ORDINALITY|non-reserved|non-reserved|non-reserved|
|OUT|non-reserved|non-reserved|reserved|
|OUTER|reserved|non-reserved|reserved|
|OUTPUTFORMAT|non-reserved|non-reserved|non-reserved|
@@ -823,6 +824,7 @@ Below is a list of all the keywords in Spark SQL.
|UNIQUE|reserved|non-reserved|reserved|
|UNKNOWN|reserved|non-reserved|reserved|
|UNLOCK|non-reserved|non-reserved|non-reserved|
+|UNNEST|non-reserved|non-reserved|non-reserved|
|UNPIVOT|non-reserved|non-reserved|non-reserved|
|UNSET|non-reserved|non-reserved|non-reserved|
|UNTIL|non-reserved|non-reserved|non-reserved|
diff --git a/docs/sql-ref-syntax-qry-select-unnest.md
b/docs/sql-ref-syntax-qry-select-unnest.md
new file mode 100644
index 000000000000..29430998874c
--- /dev/null
+++ b/docs/sql-ref-syntax-qry-select-unnest.md
@@ -0,0 +1,113 @@
+---
+layout: global
+title: UNNEST Clause
+displayTitle: UNNEST Clause
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+### Description
+
+The `UNNEST` clause expands one or more arrays into a table that can be
referenced in the
+`FROM` clause, producing one row per array element. It is the ANSI SQL
collection derived table
+and is a standard alternative to `explode` / `LATERAL VIEW`.
+
+When several arrays are supplied, they are expanded in parallel: the number of
output rows equals
+the length of the longest array, and shorter arrays are padded with `NULL`s. A
`NULL` array is
+treated as an empty array and contributes no elements.
+
+To reference a column of another `FROM` item (a correlated array), use
`UNNEST` on the right-hand
+side of a `LATERAL` join. A `LEFT JOIN LATERAL ... ON true` preserves outer
rows whose array is
+empty or `NULL`.
+
+`UNNEST` is a non-reserved keyword, so it can still be used as a regular table
or column name.
+However, when it appears unquoted at the start of a `FROM` relation followed
by `(`, it is parsed
+as the `UNNEST` clause described here rather than as a call to a table-valued
function named
+`unnest`. To invoke such a function instead, quote the name: `` `unnest`(...)
``.
+
+### Syntax
+
+```sql
+UNNEST ( expression [ , ... ] ) [ WITH ORDINALITY ] [ table_alias ]
+```
+
+### Parameters
+
+* **expression**
+
+ One or more array-typed expressions to expand. Each array contributes one
output column,
+ holding its element as-is (an array of structs is not expanded into one
column per field).
+
+* **WITH ORDINALITY**
+
+ Appends a trailing 1-based `BIGINT` column giving the position of each
element.
+
+* **table_alias**
+
+ Specifies a temporary name with an optional column name list.
+
+ **Syntax:** `[ AS ] table_name [ ( column_name [ , ... ] ) ]`
+
+### Examples
+
+```sql
+-- single array
+SELECT * FROM UNNEST(array(10, 20, 30));
++---+
+|col|
++---+
+| 10|
+| 20|
+| 30|
++---+
+
+-- WITH ORDINALITY and a table alias
+SELECT * FROM UNNEST(array('a', 'b')) WITH ORDINALITY AS t(value, pos);
++-----+---+
+|value|pos|
++-----+---+
+| a| 1|
+| b| 2|
++-----+---+
+
+-- multiple arrays are expanded in parallel and padded with NULLs
+SELECT * FROM UNNEST(array(1, 2), array(10, 20, 30)) AS t(a, b);
++----+---+
+| a| b|
++----+---+
+| 1| 10|
+| 2| 20|
+|NULL| 30|
++----+---+
+
+-- correlated UNNEST over a table column, via LATERAL
+SELECT id, elem
+FROM VALUES (1, array(10, 20)), (2, array(30)) AS data(id, arr),
+ LATERAL UNNEST(arr) AS t(elem);
++---+----+
+| id|elem|
++---+----+
+| 1| 10|
+| 1| 20|
+| 2| 30|
++---+----+
+```
+
+### Related Statements
+
+* [SELECT](sql-ref-syntax-qry-select.html)
+* [LATERAL VIEW Clause](sql-ref-syntax-qry-select-lateral-view.html)
+* [Table-valued Function](sql-ref-syntax-qry-select-tvf.html)
diff --git a/docs/sql-ref-syntax-qry-select.md
b/docs/sql-ref-syntax-qry-select.md
index 57bd4f600260..a97ee766f216 100644
--- a/docs/sql-ref-syntax-qry-select.md
+++ b/docs/sql-ref-syntax-qry-select.md
@@ -94,6 +94,7 @@ SELECT [ hints , ... ] [ ALL | DISTINCT ] { [ [
named_expression | regex_column_
* [Unpivot relation](sql-ref-syntax-qry-select-unpivot.html)
* [Table-value function](sql-ref-syntax-qry-select-tvf.html)
* [Inline table](sql-ref-syntax-qry-select-inline-table.html)
+ * [UNNEST relation](sql-ref-syntax-qry-select-unnest.html)
* [ [LATERAL](sql-ref-syntax-qry-select-lateral-subquery.html) ] (
Subquery )
* [File](sql-ref-syntax-qry-select-file.html)
diff --git a/docs/sql-ref-syntax.md b/docs/sql-ref-syntax.md
index 31ac500f594d..42e52bf04454 100644
--- a/docs/sql-ref-syntax.md
+++ b/docs/sql-ref-syntax.md
@@ -71,6 +71,7 @@ ability to generate logical and physical plan for a given
query using
* [HAVING Clause](sql-ref-syntax-qry-select-having.html)
* [Hints](sql-ref-syntax-qry-select-hints.html)
* [Inline Table](sql-ref-syntax-qry-select-inline-table.html)
+ * [UNNEST Clause](sql-ref-syntax-qry-select-unnest.html)
* [File](sql-ref-syntax-qry-select-file.html)
* [JOIN](sql-ref-syntax-qry-select-join.html)
* [ASOF JOIN](sql-ref-syntax-qry-select-asof-join.html)
diff --git
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
index 1445e4015beb..fea9d6ef7bb3 100644
---
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
+++
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
@@ -400,6 +400,7 @@ OPTION: 'OPTION';
OPTIONS: 'OPTIONS';
OR: 'OR';
ORDER: 'ORDER';
+ORDINALITY: 'ORDINALITY';
OUT: 'OUT';
OUTER: 'OUTER';
OUTPUTFORMAT: 'OUTPUTFORMAT';
@@ -539,6 +540,7 @@ UNION: 'UNION';
UNIQUE: 'UNIQUE';
UNKNOWN: 'UNKNOWN';
UNLOCK: 'UNLOCK';
+UNNEST: 'UNNEST';
UNPIVOT: 'UNPIVOT';
UNSET: 'UNSET';
UNTIL: 'UNTIL';
diff --git
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
index bc6c437a42e2..87a253426edf 100644
---
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
+++
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
@@ -1208,9 +1208,18 @@ relationPrimary
| LEFT_PAREN relation RIGHT_PAREN sample?
watermarkClause? tableAlias #aliasedRelation
| inlineTable
#inlineTableDefault2
+ | unnest #unnestTable
| tableFunctionCallWithTrailingClauses
#tableValuedFunction
;
+// ANSI SQL UNNEST of one or more arrays in the FROM clause, with an optional
+// trailing ordinality column (WITH ORDINALITY). Multiple arrays are expanded
in
+// parallel, padded with NULLs to the length of the longest array.
+unnest
+ : UNNEST LEFT_PAREN expression (COMMA expression)* RIGHT_PAREN
+ (WITH ORDINALITY)? tableAlias
+ ;
+
optionsClause
: WITH options=propertyList
;
@@ -2254,6 +2263,7 @@ ansiNonReserved
| OPEN
| OPTION
| OPTIONS
+ | ORDINALITY
| OUT
| OUTPUTFORMAT
| OVER
@@ -2374,6 +2384,7 @@ ansiNonReserved
| UNCACHE
| UNIFORM
| UNLOCK
+ | UNNEST
| UNPIVOT
| UNSET
| UNTIL
@@ -2695,6 +2706,7 @@ nonReserved
| OPTIONS
| OR
| ORDER
+ | ORDINALITY
| OUT
| OUTER
| OUTPUTFORMAT
@@ -2829,6 +2841,7 @@ nonReserved
| UNIQUE
| UNKNOWN
| UNLOCK
+ | UNNEST
| UNPIVOT
| UNSET
| UNTIL
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/generators.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/generators.scala
index b513b3858bbd..de8d40cd4299 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/generators.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/generators.scala
@@ -701,6 +701,127 @@ object InlineOuterGeneratorBuilder extends
InlineGeneratorBuilderBase {
override def isOuter: Boolean = true
}
+/**
+ * Expands one or more arrays into a table, one row per element, implementing
the ANSI SQL
+ * `UNNEST` collection derived table used in the FROM clause.
+ *
+ * When several arrays are supplied they are expanded in parallel: the number
of output rows is
+ * the length of the longest array, and shorter arrays are padded with NULLs.
A NULL array is
+ * treated as an empty array (contributes no elements). This matches the
multi-array semantics of
+ * PostgreSQL and Trino.
+ *
+ * Each array contributes exactly one output column holding its element as-is;
unlike `inline`,
+ * arrays of structs are not expanded into one column per field. When
`withOrdinality` is set, a
+ * trailing 1-based `BIGINT` ordinality column is appended, matching `WITH
ORDINALITY` in
+ * PostgreSQL and Trino (BigQuery's 0-based `WITH OFFSET` is intentionally not
adopted).
+ *
+ * {{{
+ * SELECT * FROM UNNEST(array(10, 20), array(30)) WITH ORDINALITY ->
+ * 10 30 1
+ * 20 NULL 2
+ * }}}
+ *
+ * This generator uses interpreted evaluation ([[CodegenFallback]]);
`GenerateExec` therefore
+ * disables whole-stage codegen for the enclosing `Generate`. The
per-array/per-ordinality zip with
+ * NULL padding does not map onto the existing [[CollectionGenerator]] codegen
path (which emits a
+ * single `ArrayData`/`MapData`), and its interpreted `eval` is already lazy
(one row built per
+ * pull). Interpreted generation is the same choice made by other
non-`CollectionGenerator`
+ * generators such as [[ReplicateRows]]. A dedicated codegen path (analogous
to `arrays_zip`) is
+ * possible future work if UNNEST becomes hot in whole-stage-codegen pipelines.
+ */
+case class Unnest(children: Seq[Expression], withOrdinality: Boolean)
+ extends Generator with CodegenFallback {
+
+ private lazy val arrayElementTypes: Seq[ArrayType] =
+ children.map(_.dataType.asInstanceOf[ArrayType])
+
+ override def checkInputDataTypes(): TypeCheckResult = {
+ if (children.isEmpty) {
+ throw QueryCompilationErrors.wrongNumArgsError(
+ toSQLId(prettyName), Seq("> 0"), children.length)
+ }
+ val nonArray = children.zipWithIndex.collectFirst {
+ case (e, idx) if !e.dataType.isInstanceOf[ArrayType] => (e, idx)
+ }
+ nonArray match {
+ case Some((e, idx)) =>
+ DataTypeMismatch(
+ errorSubClass = "UNEXPECTED_INPUT_TYPE",
+ messageParameters = Map(
+ "paramIndex" -> ordinalNumber(idx),
+ "requiredType" -> toSQLType(ArrayType),
+ "inputSql" -> toSQLExpr(e),
+ "inputType" -> toSQLType(e.dataType)))
+ case None =>
+ TypeCheckResult.TypeCheckSuccess
+ }
+ }
+
+ override def elementSchema: StructType = {
+ // With a single array keep the `explode`-compatible default name `col`;
with several arrays
+ // use positional names `col0`, `col1`, and so on. A shorter array yields
NULLs in the
+ // padded rows, so a padded column is always nullable regardless of the
array's own
+ // `containsNull`.
+ val padded = children.length > 1
+ val arrayFields = arrayElementTypes.zipWithIndex.map { case (at, idx) =>
+ val name = if (children.length == 1) "col" else s"col$idx"
+ StructField(name, at.elementType, nullable = at.containsNull || padded)
+ }
+ val fields = if (withOrdinality) {
+ arrayFields :+ StructField("ordinality", LongType, nullable = false)
+ } else {
+ arrayFields
+ }
+ StructType(fields)
+ }
+
+ override def eval(input: InternalRow): IterableOnce[InternalRow] = {
+ val arrays = children.map(_.eval(input).asInstanceOf[ArrayData])
+ // The number of output rows is the length of the longest array; a null
array counts as empty.
+ val numRows = arrays.iterator
+ .map(a => if (a == null) 0 else a.numElements())
+ .foldLeft(0)(math.max)
+ val numArrays = arrays.length
+ val numFields = if (withOrdinality) numArrays + 1 else numArrays
+ // Build each output row lazily so that neither a single wide array nor a
correlated LATERAL
+ // UNNEST over many input rows materializes the whole expansion up front;
GenerateExec pulls
+ // rows one at a time. The iterator is single-pass but only ever traversed
once per input row
+ // (GenerateExec's `outer` emptiness check uses `hasNext`, which does not
build a row).
+ (0 until numRows).iterator.map { row =>
+ val fields = new Array[Any](numFields)
+ var col = 0
+ while (col < numArrays) {
+ val arr = arrays(col)
+ fields(col) = if (arr != null && row < arr.numElements() &&
!arr.isNullAt(row)) {
+ arr.get(row, arrayElementTypes(col).elementType)
+ } else {
+ null
+ }
+ col += 1
+ }
+ if (withOrdinality) {
+ // Widen before incrementing so the 1-based ordinality cannot overflow
Int near the top of
+ // the range (the ordinality column is BIGINT).
+ fields(numArrays) = row.toLong + 1L
+ }
+ // Wrap the array by reference (no copy) rather than
InternalRow.fromSeq, which re-copies.
+ new GenericInternalRow(fields)
+ }
+ }
+
+ override def prettyName: String = "unnest"
+
+ // Keep the `withOrdinality` flag out of the default `toString`/EXPLAIN
rendering (where it would
+ // otherwise appear as a bare `true` argument) and instead surface it as a
readable suffix.
+ override def stringArgs: Iterator[Any] = {
+ val childArgs = children.iterator
+ if (withOrdinality) childArgs ++ Iterator("WITH ORDINALITY") else childArgs
+ }
+
+ override protected def withNewChildrenInternal(
+ newChildren: IndexedSeq[Expression]): Unnest = copy(children =
newChildren)
+}
+
@ExpressionDescription(
usage = """_FUNC_() - Get Spark SQL keywords""",
examples = """
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
index 9920df24b6bc..611bd86ea9bd 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
@@ -2068,6 +2068,7 @@ class AstBuilder extends DataTypeAstBuilder
relationPrimary match {
case _: AliasedQueryContext =>
case _: TableValuedFunctionContext =>
+ case _: UnnestTableContext =>
case other =>
throw QueryParsingErrors.invalidLateralJoinRelationError(other)
}
@@ -2552,6 +2553,7 @@ class AstBuilder extends DataTypeAstBuilder
ctx.right match {
case _: AliasedQueryContext =>
case _: TableValuedFunctionContext =>
+ case _: UnnestTableContext =>
case other =>
throw QueryParsingErrors.invalidLateralJoinRelationError(other)
}
@@ -3217,6 +3219,28 @@ class AstBuilder extends DataTypeAstBuilder
buildTvfFromTableFunctionCall(ctx.tableFunctionCall, ctx.tableAlias,
ctx.watermarkClause)
}
+ /**
+ * Create a plan for the ANSI SQL `UNNEST(array [, array ...]) [WITH
ORDINALITY]` relation used
+ * in the FROM clause. It is desugared into a [[Generate]] over a
[[OneRowRelation]] backed by the
+ * [[Unnest]] generator, then wrapped with the optional table/column aliases
via the shared
+ * FROM-clause aliasing helper. Correlated references (e.g. `FROM t, LATERAL
UNNEST(t.arr)`) are
+ * handled by the surrounding `LATERAL` machinery, exactly like generator
table functions such as
+ * `explode`.
+ */
+ override def visitUnnestTable(ctx: UnnestTableContext): LogicalPlan =
withOrigin(ctx) {
+ val unnest = ctx.unnest
+ val expressions = expressionList(unnest.expression)
+ val withOrdinality = unnest.ORDINALITY != null
+ val generate = Generate(
+ Unnest(expressions, withOrdinality),
+ unrequiredChildIndex = Nil,
+ outer = false,
+ qualifier = None,
+ generatorOutput = Nil,
+ child = OneRowRelation())
+ mayApplyAliasPlan(unnest.tableAlias, generate)
+ }
+
/**
* Extract the source name from an identifiedByClause context.
*/
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/GeneratorExpressionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/GeneratorExpressionSuite.scala
index b6a3d61cb13a..b9a245eb3ec3 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/GeneratorExpressionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/GeneratorExpressionSuite.scala
@@ -21,6 +21,7 @@ import org.apache.spark.{SPARK_DOC_ROOT, SparkFunSuite}
import org.apache.spark.sql.AnalysisException
import org.apache.spark.sql.catalyst.InternalRow
import org.apache.spark.sql.catalyst.analysis.TypeCheckResult.DataTypeMismatch
+import org.apache.spark.sql.catalyst.util.GenericArrayData
import org.apache.spark.sql.types._
class GeneratorExpressionSuite extends SparkFunSuite with ExpressionEvalHelper
{
@@ -114,4 +115,106 @@ class GeneratorExpressionSuite extends SparkFunSuite with
ExpressionEvalHelper {
)
)
}
+
+ test("unnest - eval is lazy and only reads elements as rows are pulled") {
+ // Backing array that records which ordinals were read, to prove that
pulling the first N rows
+ // touches only the first N elements rather than materializing the whole
expansion up front.
+ val readOrdinals = scala.collection.mutable.ArrayBuffer.empty[Int]
+ val tracking = new GenericArrayData(Array[Any](10, 20, 30, 40, 50)) {
+ override def get(ordinal: Int, elementType: DataType): AnyRef = {
+ readOrdinals += ordinal
+ super.get(ordinal, elementType)
+ }
+ }
+ val result = Unnest(Seq(Literal(tracking, ArrayType(IntegerType))),
withOrdinality = true)
+ .eval(null)
+ // The returned value is a lazy Iterator, not an eagerly materialized
collection, and building
+ // it must not read any element.
+ assert(result.isInstanceOf[Iterator[_]])
+ // Literal's constructor validates its value by reading element 0; ignore
reads made before the
+ // iterator is created and observe only what pulling rows drives.
+ readOrdinals.clear()
+
+ val it = result.iterator
+ assert(readOrdinals.isEmpty, "no element should be read before the
iterator is advanced")
+ assert(it.next() === create_row(10, 1L))
+ assert(it.next() === create_row(20, 2L))
+ // Only the two consumed rows' elements were read; rows 2..4 remain
untouched.
+ assert(readOrdinals.toSeq === Seq(0, 1))
+ }
+
+ test("unnest - single array") {
+ checkTuple(Unnest(Seq(empty_array), withOrdinality = false), Seq.empty)
+ checkTuple(
+ Unnest(Seq(int_array), withOrdinality = false),
+ Seq(create_row(1), create_row(2), create_row(3)))
+ // A null array is treated as empty and contributes no rows.
+ checkTuple(
+ Unnest(Seq(Literal.create(null, ArrayType(IntegerType))), withOrdinality
= false),
+ Seq.empty)
+ }
+
+ test("unnest - single column naming and ordinality") {
+ // With a single array the output column keeps the default name `col`.
+ assert(Unnest(Seq(int_array), withOrdinality = false).elementSchema ===
+ new StructType().add("col", IntegerType, nullable = false))
+ // WITH ORDINALITY appends a 1-based, non-nullable bigint column.
+ assert(Unnest(Seq(int_array), withOrdinality = true).elementSchema ===
+ new StructType()
+ .add("col", IntegerType, nullable = false)
+ .add("ordinality", LongType, nullable = false))
+ checkTuple(
+ Unnest(Seq(str_array), withOrdinality = true),
+ Seq(create_row("a", 1L), create_row("b", 2L), create_row("c", 3L)))
+ }
+
+ test("unnest - multiple arrays are zipped and padded with nulls") {
+ val short_array = CreateArray(Seq(10, 20).map(Literal(_)))
+ // With several arrays the columns are named positionally and padded
columns are nullable.
+ assert(Unnest(Seq(int_array, short_array), withOrdinality =
false).elementSchema ===
+ new StructType()
+ .add("col0", IntegerType, nullable = true)
+ .add("col1", IntegerType, nullable = true))
+ checkTuple(
+ Unnest(Seq(int_array, short_array), withOrdinality = false),
+ Seq(create_row(1, 10), create_row(2, 20), create_row(3, null)))
+ // WITH ORDINALITY spans the full (longest) length.
+ checkTuple(
+ Unnest(Seq(int_array, short_array), withOrdinality = true),
+ Seq(create_row(1, 10, 1L), create_row(2, 20, 2L), create_row(3, null,
3L)))
+ }
+
+ test("unnest - type checks") {
+ assert(Unnest(Seq(int_array), withOrdinality =
false).checkInputDataTypes().isSuccess)
+
+ // Providing no arguments is rejected.
+ checkError(
+ exception = intercept[AnalysisException] {
+ Unnest(Seq.empty, withOrdinality = false).checkInputDataTypes()
+ },
+ condition = "WRONG_NUM_ARGS.WITHOUT_SUGGESTION",
+ parameters = Map(
+ "functionName" -> "`unnest`",
+ "expectedNum" -> "> 0",
+ "actualNum" -> "0",
+ "docroot" -> SPARK_DOC_ROOT))
+
+ // A non-array argument is rejected, reporting the offending 1-based
parameter index.
+ assert(Unnest(Seq(int_array, Literal(3)), withOrdinality =
false).checkInputDataTypes() ==
+ DataTypeMismatch(
+ errorSubClass = "UNEXPECTED_INPUT_TYPE",
+ messageParameters = Map(
+ "paramIndex" -> "second",
+ "requiredType" -> "\"ARRAY\"",
+ "inputSql" -> "\"3\"",
+ "inputType" -> "\"INT\"")))
+ }
+
+ test("unnest - string representation hides the ordinality flag") {
+ // The `withOrdinality` boolean must not leak into plan output as a bare
`true` argument; it is
+ // rendered as a readable `WITH ORDINALITY` suffix instead (see the
EXPLAIN golden results).
+ assert(Unnest(Seq(int_array), withOrdinality = false).toString ===
"unnest(array(1, 2, 3))")
+ assert(Unnest(Seq(int_array), withOrdinality = true).toString ===
+ "unnest(array(1, 2, 3), WITH ORDINALITY)")
+ }
}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala
index 8368535045ed..c1a32b6d8da7 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/PlanParserSuite.scala
@@ -673,6 +673,85 @@ class PlanParserSuite extends AnalysisTest {
stop = 115))
}
+ test("unnest in FROM clause") {
+ def unnest(
+ exprs: Seq[Expression],
+ withOrdinality: Boolean = false): LogicalPlan =
+ Generate(
+ Unnest(exprs, withOrdinality),
+ unrequiredChildIndex = Nil,
+ outer = false,
+ qualifier = None,
+ generatorOutput = Nil,
+ child = OneRowRelation())
+
+ // Single array.
+ assertEqual(
+ "select * from unnest(array(1, 2, 3))",
+ unnest(Seq(UnresolvedFunction("array", Seq(Literal(1), Literal(2),
Literal(3)),
+ isDistinct = false))).select(star()))
+
+ // Multiple arrays.
+ assertEqual(
+ "select * from unnest(a, b)",
+ unnest(Seq(UnresolvedAttribute("a"),
UnresolvedAttribute("b"))).select(star()))
+
+ // WITH ORDINALITY.
+ assertEqual(
+ "select * from unnest(a) with ordinality",
+ unnest(Seq(UnresolvedAttribute("a")), withOrdinality =
true).select(star()))
+
+ // Table alias only.
+ assertEqual(
+ "select * from unnest(a) t",
+ unnest(Seq(UnresolvedAttribute("a"))).as("t").select(star()))
+
+ // Table alias with column aliases.
+ assertEqual(
+ "select * from unnest(a, b) t(x, y)",
+ SubqueryAlias(
+ "t",
+ UnresolvedSubqueryColumnAliases(
+ Seq("x", "y"),
+ unnest(Seq(UnresolvedAttribute("a"),
UnresolvedAttribute("b"))))).select(star()))
+
+ // Correlated via LATERAL, with WITH ORDINALITY and column aliases.
+ assertEqual(
+ "select * from t, lateral unnest(t.arr) with ordinality u(v, o)",
+ table("t").lateralJoin(
+ SubqueryAlias(
+ "u",
+ UnresolvedSubqueryColumnAliases(
+ Seq("v", "o"),
+ unnest(Seq(UnresolvedAttribute(Seq("t", "arr"))), withOrdinality =
true))))
+ .select(star()))
+
+ // With no arguments the dedicated UNNEST relation rule does not match;
the statement instead
+ // parses as a generic table-valued function call named `unnest`, which is
not registered and
+ // therefore fails later during analysis rather than at parse time.
+ assertEqual(
+ "select * from unnest()",
+ UnresolvedTableValuedFunction("unnest", Nil).select(star()))
+
+ // `UNNEST` and `ORDINALITY` are non-reserved keywords, so they remain
usable as regular
+ // table and column identifiers for backwards compatibility.
+ assertEqual(
+ "select ordinality from unnest",
+ table("unnest").select($"ordinality"))
+ assertEqual(
+ "select unnest.ordinality from unnest",
+ table("unnest").select($"unnest.ordinality"))
+
+ // Quoting the name bypasses the UNNEST relation syntax, so a table-valued
function named
+ // `unnest` can still be invoked. This is the escape hatch for the
non-reserved keyword.
+ assertEqual(
+ "select * from `unnest`(array(1, 2))",
+ UnresolvedTableValuedFunction(
+ "unnest",
+ Seq(UnresolvedFunction("array", Seq(Literal(1), Literal(2)),
isDistinct = false)))
+ .select(star()))
+ }
+
test("joins") {
// Test single joins.
val testUnconditionalJoin = (sql: String, jt: JoinType) => {
diff --git
a/sql/connect/client/jdbc/src/test/scala/org/apache/spark/sql/connect/client/jdbc/SparkConnectDatabaseMetaDataSuite.scala
b/sql/connect/client/jdbc/src/test/scala/org/apache/spark/sql/connect/client/jdbc/SparkConnectDatabaseMetaDataSuite.scala
index 3d3260c750f6..2df258a2dc5c 100644
---
a/sql/connect/client/jdbc/src/test/scala/org/apache/spark/sql/connect/client/jdbc/SparkConnectDatabaseMetaDataSuite.scala
+++
b/sql/connect/client/jdbc/src/test/scala/org/apache/spark/sql/connect/client/jdbc/SparkConnectDatabaseMetaDataSuite.scala
@@ -210,7 +210,7 @@ class SparkConnectDatabaseMetaDataSuite extends
ConnectFunSuite with RemoteSpark
val metadata = conn.getMetaData
// scalastyle:off line.size.limit
// CURRENT_PATH and SYSTEM are excluded: getSQLKeywords drops SQL:2003
reserved words (see companion).
- assert(metadata.getSQLKeywords ===
"ADD,AFTER,AGGREGATE,ALIGN,ALWAYS,ANALYZE,ANTI,ANY_VALUE,APPLY,APPROX,ARCHIVE,ASC,ASOF,AUTO,BERNOULLI,BIN,BINDING,BIN_DISTRIBUTE_RATIO,BIN_END,BIN_START,BUCKET,BUCKETS,BYTE,CACHE,CASCADE,CATALOG,CATALOGS,CDC,CHANGE,CHANGES,CLEAR,CLUSTER,CLUSTERED,CODEGEN,COLLATION,COLLATIONS,COLLECTION,COLUMNS,COMMENT,COMPACT,COMPACTIONS,COMPENSATION,COMPUTE,CONCATENATE,CONTAINS,CONTINUE,COST,CURRENT_DATABASE,CURRENT_SCHEMA,DATA,DATABASE,DATABASES,DATEADD,DATEDIFF
[...]
+ assert(metadata.getSQLKeywords ===
"ADD,AFTER,AGGREGATE,ALIGN,ALWAYS,ANALYZE,ANTI,ANY_VALUE,APPLY,APPROX,ARCHIVE,ASC,ASOF,AUTO,BERNOULLI,BIN,BINDING,BIN_DISTRIBUTE_RATIO,BIN_END,BIN_START,BUCKET,BUCKETS,BYTE,CACHE,CASCADE,CATALOG,CATALOGS,CDC,CHANGE,CHANGES,CLEAR,CLUSTER,CLUSTERED,CODEGEN,COLLATION,COLLATIONS,COLLECTION,COLUMNS,COMMENT,COMPACT,COMPACTIONS,COMPENSATION,COMPUTE,CONCATENATE,CONTAINS,CONTINUE,COST,CURRENT_DATABASE,CURRENT_SCHEMA,DATA,DATABASE,DATABASES,DATEADD,DATEDIFF
[...]
// scalastyle:on line.size.limit
}
}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/unnest.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/unnest.sql.out
new file mode 100644
index 000000000000..a67c021b7942
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/unnest.sql.out
@@ -0,0 +1,278 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE OR REPLACE TEMPORARY VIEW nested AS SELECT * FROM VALUES
+(1, array(10, 20, 30), array('a', 'b')),
+(2, array(40), array('c', 'd', 'e')),
+(3, array(), array()),
+(4, cast(null as array<int>), array('f'))
+AS nested(id, xs, ys)
+-- !query analysis
+CreateViewCommand `nested`, SELECT * FROM VALUES
+(1, array(10, 20, 30), array('a', 'b')),
+(2, array(40), array('c', 'd', 'e')),
+(3, array(), array()),
+(4, cast(null as array<int>), array('f'))
+AS nested(id, xs, ys), false, true, LocalTempView, UNSUPPORTED, true
+ +- Project [id#x, xs#x, ys#x]
+ +- SubqueryAlias nested
+ +- LocalRelation [id#x, xs#x, ys#x]
+
+
+-- !query
+SELECT * FROM UNNEST(array(10, 20, 30))
+-- !query analysis
+Project [col#x]
++- Generate unnest(array(10, 20, 30)), false, [col#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT v FROM UNNEST(array(10, 20, 30)) AS t(v)
+-- !query analysis
+Project [v#x]
++- SubqueryAlias t
+ +- Project [col#x AS v#x]
+ +- Generate unnest(array(10, 20, 30)), false, [col#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(array())
+-- !query analysis
+Project [col#x]
++- Generate unnest(array()), false, [col#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(cast(null as array<int>))
+-- !query analysis
+Project [col#x]
++- Generate unnest(cast(null as array<int>)), false, [col#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(array(10, 20, 30)) WITH ORDINALITY
+-- !query analysis
+Project [col#x, ordinality#xL]
++- Generate unnest(array(10, 20, 30), WITH ORDINALITY), false, [col#x,
ordinality#xL]
+ +- OneRowRelation
+
+
+-- !query
+SELECT val, pos FROM UNNEST(array('x', 'y')) WITH ORDINALITY AS t(val, pos)
+-- !query analysis
+Project [val#x, pos#xL]
++- SubqueryAlias t
+ +- Project [col#x AS val#x, ordinality#xL AS pos#xL]
+ +- Generate unnest(array(x, y), WITH ORDINALITY), false, [col#x,
ordinality#xL]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(array(1, 2), array(10, 20, 30)) AS t(a, b)
+-- !query analysis
+Project [a#x, b#x]
++- SubqueryAlias t
+ +- Project [col0#x AS a#x, col1#x AS b#x]
+ +- Generate unnest(array(1, 2), array(10, 20, 30)), false, [col0#x,
col1#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(array(1, 2), array(10, 20, 30)) WITH ORDINALITY AS t(a,
b, ord)
+-- !query analysis
+Project [a#x, b#x, ord#xL]
++- SubqueryAlias t
+ +- Project [col0#x AS a#x, col1#x AS b#x, ordinality#xL AS ord#xL]
+ +- Generate unnest(array(1, 2), array(10, 20, 30), WITH ORDINALITY),
false, [col0#x, col1#x, ordinality#xL]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(array(struct(1, 'a'), struct(2, 'b'))) AS t(s)
+-- !query analysis
+Project [s#x]
++- SubqueryAlias t
+ +- Project [col#x AS s#x]
+ +- Generate unnest(array(struct(col1, 1, col2, a), struct(col1, 2, col2,
b))), false, [col#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT id, elem FROM nested, LATERAL UNNEST(xs) AS t(elem) ORDER BY id, elem
+-- !query analysis
+Sort [id#x ASC NULLS FIRST, elem#x ASC NULLS FIRST], true
++- Project [id#x, elem#x]
+ +- LateralJoin lateral-subquery#x [xs#x], Inner
+ : +- SubqueryAlias t
+ : +- Project [col#x AS elem#x]
+ : +- Generate unnest(outer(xs#x)), false, [col#x]
+ : +- OneRowRelation
+ +- SubqueryAlias nested
+ +- View (`nested`, [id#x, xs#x, ys#x])
+ +- Project [cast(id#x as int) AS id#x, cast(xs#x as array<int>) AS
xs#x, cast(ys#x as array<string>) AS ys#x]
+ +- Project [id#x, xs#x, ys#x]
+ +- SubqueryAlias nested
+ +- LocalRelation [id#x, xs#x, ys#x]
+
+
+-- !query
+SELECT id, x, y, ord
+FROM nested, LATERAL UNNEST(xs, ys) WITH ORDINALITY AS t(x, y, ord)
+ORDER BY id, ord
+-- !query analysis
+Sort [id#x ASC NULLS FIRST, ord#xL ASC NULLS FIRST], true
++- Project [id#x, x#x, y#x, ord#xL]
+ +- LateralJoin lateral-subquery#x [xs#x && ys#x], Inner
+ : +- SubqueryAlias t
+ : +- Project [col0#x AS x#x, col1#x AS y#x, ordinality#xL AS ord#xL]
+ : +- Generate unnest(outer(xs#x), outer(ys#x), WITH ORDINALITY),
false, [col0#x, col1#x, ordinality#xL]
+ : +- OneRowRelation
+ +- SubqueryAlias nested
+ +- View (`nested`, [id#x, xs#x, ys#x])
+ +- Project [cast(id#x as int) AS id#x, cast(xs#x as array<int>) AS
xs#x, cast(ys#x as array<string>) AS ys#x]
+ +- Project [id#x, xs#x, ys#x]
+ +- SubqueryAlias nested
+ +- LocalRelation [id#x, xs#x, ys#x]
+
+
+-- !query
+SELECT id, elem
+FROM nested LEFT JOIN LATERAL UNNEST(xs) AS t(elem) ON true
+ORDER BY id, elem
+-- !query analysis
+Sort [id#x ASC NULLS FIRST, elem#x ASC NULLS FIRST], true
++- Project [id#x, elem#x]
+ +- LateralJoin lateral-subquery#x [xs#x], LeftOuter, true
+ : +- SubqueryAlias t
+ : +- Project [col#x AS elem#x]
+ : +- Generate unnest(outer(xs#x)), false, [col#x]
+ : +- OneRowRelation
+ +- SubqueryAlias nested
+ +- View (`nested`, [id#x, xs#x, ys#x])
+ +- Project [cast(id#x as int) AS id#x, cast(xs#x as array<int>) AS
xs#x, cast(ys#x as array<string>) AS ys#x]
+ +- Project [id#x, xs#x, ys#x]
+ +- SubqueryAlias nested
+ +- LocalRelation [id#x, xs#x, ys#x]
+
+
+-- !query
+SELECT * FROM UNNEST(array(array(1, 2), array(3))) AS t(inner)
+-- !query analysis
+Project [inner#x]
++- SubqueryAlias t
+ +- Project [col#x AS inner#x]
+ +- Generate unnest(array(array(1, 2), array(3))), false, [col#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(array(1, cast(null as int), 3)) WITH ORDINALITY
+-- !query analysis
+Project [col#x, ordinality#xL]
++- Generate unnest(array(1, cast(null as int), 3), WITH ORDINALITY), false,
[col#x, ordinality#xL]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(array(1), array(10, 20, 30)) AS t(a, b)
+-- !query analysis
+Project [a#x, b#x]
++- SubqueryAlias t
+ +- Project [col0#x AS a#x, col1#x AS b#x]
+ +- Generate unnest(array(1), array(10, 20, 30)), false, [col0#x, col1#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT * FROM UNNEST(42)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"42\"",
+ "inputType" : "\"INT\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"ARRAY\"",
+ "sqlExpr" : "\"unnest(42)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 24,
+ "fragment" : "UNNEST(42)"
+ } ]
+}
+
+
+-- !query
+SELECT * FROM UNNEST(map('a', 1))
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"map(a, 1)\"",
+ "inputType" : "\"MAP<STRING, INT>\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"ARRAY\"",
+ "sqlExpr" : "\"unnest(map(a, 1))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 33,
+ "fragment" : "UNNEST(map('a', 1))"
+ } ]
+}
+
+
+-- !query
+SELECT * FROM UNNEST(array(1, 2), 3)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"3\"",
+ "inputType" : "\"INT\"",
+ "paramIndex" : "second",
+ "requiredType" : "\"ARRAY\"",
+ "sqlExpr" : "\"unnest(array(1, 2), 3)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 36,
+ "fragment" : "UNNEST(array(1, 2), 3)"
+ } ]
+}
+
+
+-- !query
+SELECT * FROM `unnest`(array(1, 2))
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNRESOLVABLE_TABLE_VALUED_FUNCTION",
+ "sqlState" : "42883",
+ "messageParameters" : {
+ "name" : "`unnest`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 35,
+ "fragment" : "`unnest`(array(1, 2))"
+ } ]
+}
diff --git a/sql/core/src/test/resources/sql-tests/inputs/unnest.sql
b/sql/core/src/test/resources/sql-tests/inputs/unnest.sql
new file mode 100644
index 000000000000..3758fda05308
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/unnest.sql
@@ -0,0 +1,72 @@
+-- Tests for the ANSI SQL UNNEST collection derived table in the FROM clause.
+
+CREATE OR REPLACE TEMPORARY VIEW nested AS SELECT * FROM VALUES
+(1, array(10, 20, 30), array('a', 'b')),
+(2, array(40), array('c', 'd', 'e')),
+(3, array(), array()),
+(4, cast(null as array<int>), array('f'))
+AS nested(id, xs, ys);
+
+-- Single array: one row per element, default column name `col`.
+SELECT * FROM UNNEST(array(10, 20, 30));
+
+-- Single array with a table and column alias.
+SELECT v FROM UNNEST(array(10, 20, 30)) AS t(v);
+
+-- Empty array produces no rows.
+SELECT * FROM UNNEST(array());
+
+-- NULL array is treated as empty and produces no rows.
+SELECT * FROM UNNEST(cast(null as array<int>));
+
+-- WITH ORDINALITY appends a 1-based BIGINT position column.
+SELECT * FROM UNNEST(array(10, 20, 30)) WITH ORDINALITY;
+
+-- WITH ORDINALITY with column aliases.
+SELECT val, pos FROM UNNEST(array('x', 'y')) WITH ORDINALITY AS t(val, pos);
+
+-- Multiple arrays are expanded in parallel and padded with NULLs to the
longest length.
+SELECT * FROM UNNEST(array(1, 2), array(10, 20, 30)) AS t(a, b);
+
+-- Multiple arrays with WITH ORDINALITY.
+SELECT * FROM UNNEST(array(1, 2), array(10, 20, 30)) WITH ORDINALITY AS t(a,
b, ord);
+
+-- An array of structs keeps the struct as a single column (unlike inline).
+SELECT * FROM UNNEST(array(struct(1, 'a'), struct(2, 'b'))) AS t(s);
+
+-- Correlated UNNEST over a table column, via LATERAL.
+SELECT id, elem FROM nested, LATERAL UNNEST(xs) AS t(elem) ORDER BY id, elem;
+
+-- Correlated UNNEST of two arrays with ordinality, via LATERAL.
+SELECT id, x, y, ord
+FROM nested, LATERAL UNNEST(xs, ys) WITH ORDINALITY AS t(x, y, ord)
+ORDER BY id, ord;
+
+-- LEFT JOIN LATERAL preserves outer rows when the array is empty or NULL.
+SELECT id, elem
+FROM nested LEFT JOIN LATERAL UNNEST(xs) AS t(elem) ON true
+ORDER BY id, elem;
+
+-- Nested arrays: the element type is preserved as-is (a single array-typed
column).
+SELECT * FROM UNNEST(array(array(1, 2), array(3))) AS t(inner);
+
+-- Array elements that are themselves NULL are emitted as NULL rows (distinct
from a NULL array).
+SELECT * FROM UNNEST(array(1, cast(null as int), 3)) WITH ORDINALITY;
+
+-- The first array being shorter still pads it (not just trailing arrays).
+SELECT * FROM UNNEST(array(1), array(10, 20, 30)) AS t(a, b);
+
+-- Non-array argument is rejected.
+SELECT * FROM UNNEST(42);
+
+-- A MAP argument is rejected (unlike explode, UNNEST is array-only per the
SQL standard).
+SELECT * FROM UNNEST(map('a', 1));
+
+-- A mix of array and non-array arguments is rejected.
+SELECT * FROM UNNEST(array(1, 2), 3);
+
+-- UNNEST is a non-reserved keyword: a table-valued function named `unnest`
can still be invoked
+-- by quoting the name, which bypasses the dedicated UNNEST relation syntax.
Here it resolves as a
+-- generic (unregistered) TVF and fails at analysis, proving the name is not
swallowed by the
+-- grammar.
+SELECT * FROM `unnest`(array(1, 2));
diff --git
a/sql/core/src/test/resources/sql-tests/results/keywords-enforced.sql.out
b/sql/core/src/test/resources/sql-tests/results/keywords-enforced.sql.out
index 7a6608885e3c..943482bece87 100644
--- a/sql/core/src/test/resources/sql-tests/results/keywords-enforced.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/keywords-enforced.sql.out
@@ -277,6 +277,7 @@ OPTION false
OPTIONS false
OR true
ORDER true
+ORDINALITY false
OUT false
OUTER true
OUTPUTFORMAT false
@@ -412,6 +413,7 @@ UNION true
UNIQUE true
UNKNOWN true
UNLOCK false
+UNNEST false
UNPIVOT false
UNSET false
UNTIL false
diff --git a/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
b/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
index db8bfa0e205c..c180dd97c573 100644
--- a/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
@@ -277,6 +277,7 @@ OPTION false
OPTIONS false
OR false
ORDER false
+ORDINALITY false
OUT false
OUTER false
OUTPUTFORMAT false
@@ -412,6 +413,7 @@ UNION false
UNIQUE false
UNKNOWN false
UNLOCK false
+UNNEST false
UNPIVOT false
UNSET false
UNTIL false
diff --git
a/sql/core/src/test/resources/sql-tests/results/nonansi/keywords.sql.out
b/sql/core/src/test/resources/sql-tests/results/nonansi/keywords.sql.out
index db8bfa0e205c..c180dd97c573 100644
--- a/sql/core/src/test/resources/sql-tests/results/nonansi/keywords.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/nonansi/keywords.sql.out
@@ -277,6 +277,7 @@ OPTION false
OPTIONS false
OR false
ORDER false
+ORDINALITY false
OUT false
OUTER false
OUTPUTFORMAT false
@@ -412,6 +413,7 @@ UNION false
UNIQUE false
UNKNOWN false
UNLOCK false
+UNNEST false
UNPIVOT false
UNSET false
UNTIL false
diff --git a/sql/core/src/test/resources/sql-tests/results/unnest.sql.out
b/sql/core/src/test/resources/sql-tests/results/unnest.sql.out
new file mode 100644
index 000000000000..e04dc7ecc531
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/unnest.sql.out
@@ -0,0 +1,267 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE OR REPLACE TEMPORARY VIEW nested AS SELECT * FROM VALUES
+(1, array(10, 20, 30), array('a', 'b')),
+(2, array(40), array('c', 'd', 'e')),
+(3, array(), array()),
+(4, cast(null as array<int>), array('f'))
+AS nested(id, xs, ys)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM UNNEST(array(10, 20, 30))
+-- !query schema
+struct<col:int>
+-- !query output
+10
+20
+30
+
+
+-- !query
+SELECT v FROM UNNEST(array(10, 20, 30)) AS t(v)
+-- !query schema
+struct<v:int>
+-- !query output
+10
+20
+30
+
+
+-- !query
+SELECT * FROM UNNEST(array())
+-- !query schema
+struct<col:void>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM UNNEST(cast(null as array<int>))
+-- !query schema
+struct<col:int>
+-- !query output
+
+
+
+-- !query
+SELECT * FROM UNNEST(array(10, 20, 30)) WITH ORDINALITY
+-- !query schema
+struct<col:int,ordinality:bigint>
+-- !query output
+10 1
+20 2
+30 3
+
+
+-- !query
+SELECT val, pos FROM UNNEST(array('x', 'y')) WITH ORDINALITY AS t(val, pos)
+-- !query schema
+struct<val:string,pos:bigint>
+-- !query output
+x 1
+y 2
+
+
+-- !query
+SELECT * FROM UNNEST(array(1, 2), array(10, 20, 30)) AS t(a, b)
+-- !query schema
+struct<a:int,b:int>
+-- !query output
+1 10
+2 20
+NULL 30
+
+
+-- !query
+SELECT * FROM UNNEST(array(1, 2), array(10, 20, 30)) WITH ORDINALITY AS t(a,
b, ord)
+-- !query schema
+struct<a:int,b:int,ord:bigint>
+-- !query output
+1 10 1
+2 20 2
+NULL 30 3
+
+
+-- !query
+SELECT * FROM UNNEST(array(struct(1, 'a'), struct(2, 'b'))) AS t(s)
+-- !query schema
+struct<s:struct<col1:int,col2:string>>
+-- !query output
+{"col1":1,"col2":"a"}
+{"col1":2,"col2":"b"}
+
+
+-- !query
+SELECT id, elem FROM nested, LATERAL UNNEST(xs) AS t(elem) ORDER BY id, elem
+-- !query schema
+struct<id:int,elem:int>
+-- !query output
+1 10
+1 20
+1 30
+2 40
+
+
+-- !query
+SELECT id, x, y, ord
+FROM nested, LATERAL UNNEST(xs, ys) WITH ORDINALITY AS t(x, y, ord)
+ORDER BY id, ord
+-- !query schema
+struct<id:int,x:int,y:string,ord:bigint>
+-- !query output
+1 10 a 1
+1 20 b 2
+1 30 NULL 3
+2 40 c 1
+2 NULL d 2
+2 NULL e 3
+4 NULL f 1
+
+
+-- !query
+SELECT id, elem
+FROM nested LEFT JOIN LATERAL UNNEST(xs) AS t(elem) ON true
+ORDER BY id, elem
+-- !query schema
+struct<id:int,elem:int>
+-- !query output
+1 10
+1 20
+1 30
+2 40
+3 NULL
+4 NULL
+
+
+-- !query
+SELECT * FROM UNNEST(array(array(1, 2), array(3))) AS t(inner)
+-- !query schema
+struct<inner:array<int>>
+-- !query output
+[1,2]
+[3]
+
+
+-- !query
+SELECT * FROM UNNEST(array(1, cast(null as int), 3)) WITH ORDINALITY
+-- !query schema
+struct<col:int,ordinality:bigint>
+-- !query output
+1 1
+3 3
+NULL 2
+
+
+-- !query
+SELECT * FROM UNNEST(array(1), array(10, 20, 30)) AS t(a, b)
+-- !query schema
+struct<a:int,b:int>
+-- !query output
+1 10
+NULL 20
+NULL 30
+
+
+-- !query
+SELECT * FROM UNNEST(42)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"42\"",
+ "inputType" : "\"INT\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"ARRAY\"",
+ "sqlExpr" : "\"unnest(42)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 24,
+ "fragment" : "UNNEST(42)"
+ } ]
+}
+
+
+-- !query
+SELECT * FROM UNNEST(map('a', 1))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"map(a, 1)\"",
+ "inputType" : "\"MAP<STRING, INT>\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"ARRAY\"",
+ "sqlExpr" : "\"unnest(map(a, 1))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 33,
+ "fragment" : "UNNEST(map('a', 1))"
+ } ]
+}
+
+
+-- !query
+SELECT * FROM UNNEST(array(1, 2), 3)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"3\"",
+ "inputType" : "\"INT\"",
+ "paramIndex" : "second",
+ "requiredType" : "\"ARRAY\"",
+ "sqlExpr" : "\"unnest(array(1, 2), 3)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 36,
+ "fragment" : "UNNEST(array(1, 2), 3)"
+ } ]
+}
+
+
+-- !query
+SELECT * FROM `unnest`(array(1, 2))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNRESOLVABLE_TABLE_VALUED_FUNCTION",
+ "sqlState" : "42883",
+ "messageParameters" : {
+ "name" : "`unnest`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 35,
+ "fragment" : "`unnest`(array(1, 2))"
+ } ]
+}
diff --git
a/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
b/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
index fbc150f79ab4..4dd2948988d6 100644
---
a/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
+++
b/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
@@ -214,7 +214,7 @@ trait ThriftServerWithSparkContextSuite extends
SharedThriftServer {
val sessionHandle = client.openSession(user, "")
val infoValue = client.getInfo(sessionHandle,
GetInfoType.CLI_ODBC_KEYWORDS)
// scalastyle:off line.size.limit
- assert(infoValue.getStringValue ==
"ADD,AFTER,AGGREGATE,ALIGN,ALL,ALTER,ALWAYS,ANALYZE,AND,ANTI,ANY,ANY_VALUE,APPLY,APPROX,ARCHIVE,ARRAY,AS,ASC,ASENSITIVE,ASOF,AT,ATOMIC,AUTHORIZATION,AUTO,BEGIN,BERNOULLI,BETWEEN,BIGINT,BIN,BINARY,BINDING,BIN_DISTRIBUTE_RATIO,BIN_END,BIN_START,BOOLEAN,BOTH,BUCKET,BUCKETS,BY,BYTE,CACHE,CALL,CALLED,CASCADE,CASE,CAST,CATALOG,CATALOGS,CDC,CHANGE,CHANGES,CHAR,CHARACTER,CHECK,CLEAR,CLOSE,CLUSTER,CLUSTERED,CODEGEN,COLLATE,COLLATION,COLLATIONS,COLLECTION,C
[...]
+ assert(infoValue.getStringValue ==
"ADD,AFTER,AGGREGATE,ALIGN,ALL,ALTER,ALWAYS,ANALYZE,AND,ANTI,ANY,ANY_VALUE,APPLY,APPROX,ARCHIVE,ARRAY,AS,ASC,ASENSITIVE,ASOF,AT,ATOMIC,AUTHORIZATION,AUTO,BEGIN,BERNOULLI,BETWEEN,BIGINT,BIN,BINARY,BINDING,BIN_DISTRIBUTE_RATIO,BIN_END,BIN_START,BOOLEAN,BOTH,BUCKET,BUCKETS,BY,BYTE,CACHE,CALL,CALLED,CASCADE,CASE,CAST,CATALOG,CATALOGS,CDC,CHANGE,CHANGES,CHAR,CHARACTER,CHECK,CLEAR,CLOSE,CLUSTER,CLUSTERED,CODEGEN,COLLATE,COLLATION,COLLATIONS,COLLECTION,C
[...]
// scalastyle:on line.size.limit
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]