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]

Reply via email to