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 b2bfeffe8ec4 [SPARK-59970][SQL] Reject a temporary variable read 
through an IDENTIFIER clause in a persistent SQL function
b2bfeffe8ec4 is described below

commit b2bfeffe8ec42f9b1c74b12b852b894334528538
Author: Anshul Baliga <[email protected]>
AuthorDate: Fri Nov 6 08:31:43 2026 +0000

    [SPARK-59970][SQL] Reject a temporary variable read through an IDENTIFIER 
clause in a persistent SQL function
    
    ### What changes were proposed in this pull request?
    
    `CreateSQLFunctionCommand` now captures the temporary variables that the 
function body reads only inside IDENTIFIER clauses and treats them like any 
other temporary variable reference: a persistent function is rejected with 
`INVALID_TEMP_OBJ_REFERENCE`, and a temporary function records them in its 
`referredTempVariableNames` property.
    
    The names are recorded by `ResolveIdentifierClause` in 
`AnalysisContext.referredTempVariableNamesUnderIdentifier` (SPARK-59323), but 
the command analyzed the body with `Analyzer.execute`, which opens a fresh 
`AnalysisContext` and discards the accumulator on exit. A new 
`Analyzer.executeWithReferredTempVariablesUnderIdentifier` runs the same 
fixed-point analysis in a context it owns and returns the recorded names 
alongside the analyzed plan. It is the sibling of `executeAndCheckReferre [...]
    
    Only the variables read by the function body itself are attributed to it. A 
SQL function that the body calls is expanded in a context of its own (a new 
`AnalysisContext` flag, `shareIdentifierVariablesWithNestedSQLFunctions`, which 
the function-creation entry point turns off), so a variable that an existing 
function reads through an IDENTIFIER clause stays that function's dependency: 
`CREATE FUNCTION g(x) RETURN f2(x)` is not rejected for `f2`'s variable.
    View bodies keep sharing the accumulator with the functions they expand, as 
SPARK-59323 designed.
    
    `spark.sql.legacy.allowSessionVariableInPersistedView` is honored for 
functions as it is for views: with it set, a persistent function may read the 
variable through an IDENTIFIER clause. As with persisted views, the dependency 
is not stored (a persistent function records no temporary variable names at 
all), so the function only resolves while the variable exists.
    
    Bodies without IDENTIFIER clauses are unaffected.
    
    ### Why are the changes needed?
    
    A persistent SQL function could capture a session-scoped dependency and 
then fail in another session:
    
    ```sql
    DECLARE OR REPLACE VARIABLE col STRING DEFAULT 'a';
    CREATE FUNCTION f(x INT) RETURNS INT RETURN (SELECT IDENTIFIER(col) FROM 
VALUES (1) AS T(a));
    -- succeeds; in a new session:
    SELECT f(1);
    -- [NOT_A_CONSTANT_STRING.NOT_CONSTANT] The expression col used for the 
routine or clause
    -- IDENTIFIER must be a constant STRING which is NOT NULL.
    ```
    
    A direct reference (`RETURN x + length(col)`) was already rejected, because 
`ViewHelper.collectTemporaryVariables` sees the `VariableReference` in the 
analyzed plan. Under an IDENTIFIER clause the reference is gone by then: the 
placeholder is replaced by the expression or plan built from the evaluated name.
    
    SPARK-59323 (#58606) fixed the same gap for CREATE VIEW, ALTER VIEW, CACHE 
TABLE AS SELECT and metric views; its review noted the SQL function path as 
pre-existing and to be tracked separately.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes. `CREATE FUNCTION` (persistent) whose body reads a temporary variable 
only through an IDENTIFIER clause now fails with `INVALID_TEMP_OBJ_REFERENCE` 
at creation, matching a direct variable reference, instead of succeeding and 
failing later when the function is called from a session where the variable 
does not exist. Setting
    `spark.sql.legacy.allowSessionVariableInPersistedView` restores the 
previous behavior; a migration-guide entry is added under 4.3 to 4.4 
(branch-4.3 predates the SPARK-59323 mechanism this builds on). Temporary 
functions are unchanged in behavior; their stored `referredTempVariableNames` 
now also lists such variables.
    
    ### How was this patch tested?
    
    New cases in `sql-udf.sql` (regenerated through `SQLQueryTestSuite`), in 
the existing "permanent functions with temp objects" section:
    
    - a direct temporary variable reference, as the baseline rejection;
    - a variable read through IDENTIFIER in a scalar function body and in a 
table function body,
      both rejected with `INVALID_TEMP_OBJ_REFERENCE` naming the variable (both 
were accepted before
      this change);
    - a temporary function reading the variable through IDENTIFIER is created 
and evaluates;
    - the variable naming a relation (`FROM IDENTIFIER(v)`), a 
`session.`-qualified variable name,
      and two variables in one body, all rejected;
    - with the legacy flag set, a persistent function reading the variable is 
created and evaluates;
      with the flag off again, a function calling it is created and evaluates 
(the callee's variable
      is not attributed to the caller).
    
    `SQLFunctionSuite` gains two tests: the nested case (the callee is created 
under the legacy flag; the caller, created with the flag off, is accepted 
because the callee’s variable is not attributed to it; a direct IDENTIFIER read 
in the caller’s own body is rejected) and one that reads the stored function 
properties to pin that a temporary function records the variable in 
`referredTempVariableNames` while a persistent function created under the 
legacy flag stores none.
    
    ```
    SPARK_GENERATE_GOLDEN_FILES=1 build/sbt "sql/testOnly 
org.apache.spark.sql.SQLQueryTestSuite -- -z sql-udf.sql"
    build/sbt "sql/testOnly org.apache.spark.sql.SQLQueryTestSuite -- -z 
sql-udf.sql -z identifier-clause" "sql/testOnly 
org.apache.spark.sql.execution.SQLFunctionSuite 
org.apache.spark.sql.execution.SimpleMetricViewSuite 
org.apache.spark.sql.connector.DataSourceV2MetadataViewSuite"
    ```
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude claude-opus-4-8
    
    Closes #59226 from anshulbaliga7/SPARK-sql-function-identifier-temp-var.
    
    Authored-by: Anshul Baliga <[email protected]>
    Signed-off-by: Wenchen Fan <[email protected]>
    (cherry picked from commit 352ac9bf22806d6aa2047bf3c62096697d0afeb1)
    Signed-off-by: Wenchen Fan <[email protected]>
---
 docs/sql-migration-guide.md                        |   1 +
 .../spark/sql/catalyst/analysis/Analyzer.scala     |  66 +++++-
 .../analysis/ResolveIdentifierClause.scala         |   6 +-
 .../org/apache/spark/sql/internal/SQLConf.scala    |   4 +-
 .../command/CreateSQLFunctionCommand.scala         |  59 ++++--
 .../sql-tests/analyzer-results/sql-udf.sql.out     | 213 +++++++++++++++++++
 .../test/resources/sql-tests/inputs/sql-udf.sql    |  39 ++++
 .../resources/sql-tests/results/sql-udf.sql.out    | 235 +++++++++++++++++++++
 .../spark/sql/execution/SQLFunctionSuite.scala     |  79 +++++++
 9 files changed, 675 insertions(+), 27 deletions(-)

diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 066aeb55c91c..c23f6f6d1444 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -56,6 +56,7 @@ license: |
 - Since Spark 4.4, the optimizer rule `SimplifyCaseConversionExpressions` no 
longer simplifies mixed case conversions `LOWER(UPPER(str))` to `LOWER(str)` or 
`UPPER(LOWER(str))` to `UPPER(str)`. This preserves standard Unicode case 
mapping semantics for non-ASCII characters such as Latin small letter dotless i 
(`ı` U+0131) and micro sign (`µ` U+00B5).
 - Since Spark 4.4, the Oracle JDBC datasource maps Oracle DATE and TIMESTAMP 
to TimestampNTZType by default, ignoring the JDBC read option 
`preferTimestampNTZ` (DATE only when the driver default 
`oracle.jdbc.mapDateToTimestamp=true` surfaces it as TIMESTAMP; otherwise DATE 
stays DateType). In 4.3 and earlier these were read as TimestampType, or as 
TimestampNTZType only when `preferTimestampNTZ=true`. This faithfully 
represents these zoneless Oracle types. Unaffected: Oracle DATE read wit [...]
 - Since Spark 4.4, a nondeterministic common expression that a conditional 
branch reads more than once is evaluated once, and every read sees that one 
value. `BETWEEN` and `NULLIF` read their input twice, so an expression such as 
`CASE WHEN c THEN monotonically_increasing_id() BETWEEN 3 AND 5 END` previously 
gave each read its own value and could return a different result. Queries whose 
results depended on the earlier behaviour change, and an optimized or physical 
plan may now show a `wi [...]
+- Since Spark 4.4, `CREATE FUNCTION` for a persistent SQL function fails with 
`INVALID_TEMP_OBJ_REFERENCE` when the function body reads a session variable 
through an `IDENTIFIER` clause, matching the existing rejection of a direct 
variable reference. Such a function previously succeeded at creation and failed 
when called from a session where the variable did not exist. To restore the 
previous behavior, set `spark.sql.legacy.allowSessionVariableInPersistedView` 
to `true`.
 - Since Spark 4.4, `format_string` and `printf` pass a decimal argument to 
`java.util.Formatter` as a `java.math.BigDecimal`, so the floating-point 
conversions `%f`, `%e`, and `%g` format decimals instead of failing with 
`IllegalFormatConversionException`. `%a` still rejects a decimal, because 
`Formatter` supports it only for `float` and `double`. As a side effect, `%h` 
on a decimal now hashes the `java.math.BigDecimal` (unscaled value and scale) 
rather than Spark's internal `Decimal` (w [...]
 - Since Spark 4.4, a Hive table that is read through its Hive SerDe, e.g. 
`STORED AS TEXTFILE` or Parquet with `spark.sql.hive.convertMetastoreParquet` 
set to `false`, returns `NULL` for the partition column of the default 
partition `__HIVE_DEFAULT_PARTITION__`. This matches data source tables and the 
Hive Parquet and ORC tables that Spark converts by default. Previously, a 
string partition column returned the name itself, so that a predicate on rows 
such as `a = 1 OR p IS NULL` skipped  [...]
 - Since Spark 4.4, for storage-partitioned joins, 
`spark.sql.requireAllClusterKeysForCoPartition` requires every join key to be 
covered by some partition key instead of matching the partition keys 
positionally. As a result, a join-key column partitioned by more than one 
transform no longer prevents shuffle elimination, and 
`spark.sql.sources.v2.bucketing.allowKeysSubsetOfPartitionKeys.enabled` no 
longer additionally requires `spark.sql.requireAllClusterKeysForCoPartition` to 
be `false` w [...]
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
index 4ffa9c1d8117..7497daa3fbd1 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
@@ -177,12 +177,22 @@ case class AnalysisContext(
     // in a scalar subquery) by persisted-view validation: 
`verifyTemporaryObjectsNotExists`, which
     // the v1 runnable commands call while executing and the v2 commands reach 
during analysis via
     // `CheckViewReferences`. When 
`spark.sql.legacy.allowSessionVariableInPersistedView` is set,
-    // the persisted paths simply discard the set. The single-pass resolver 
has no IDENTIFIER-clause
+    // the persisted paths simply discard the set. SQL function creation 
consumes it the same way
+    // (SPARK-59970): a persistent function rejects the names, or discards 
them under the legacy
+    // flag, and a temporary function stores them as informational metadata in 
its
+    // `referredTempVariableNames` property. The single-pass resolver has no 
IDENTIFIER-clause
     // resolution of its own, so there is no second writer to keep in sync. 
LinkedHashSet keeps
     // insertion order so the recorded names (and any error naming them) are 
deterministic when more
     // than one variable is read via an IDENTIFIER clause.
     referredTempVariableNamesUnderIdentifier: mutable.Set[Seq[String]] =
       mutable.LinkedHashSet.empty,
+    // Whether a SQL function expanded during this analysis shares the 
accumulator above with the
+    // enclosing object. A view or CACHE TABLE AS SELECT body owns the 
dependencies of every
+    // function it expands (functions persist no IDENTIFIER-variable metadata 
of their own that is
+    // consulted on resolution), so they share. A SQL function body being 
created does not: a
+    // variable that an already existing function reads is that function's 
dependency, and
+    // attributing it to the new function would reject or record it there. See 
SPARK-59970.
+    shareIdentifierVariablesWithNestedSQLFunctions: Boolean = true,
     outerPlan: Option[LogicalPlan] = None,
     collation: Option[String] = None,
 
@@ -292,13 +302,20 @@ object AnalysisContext {
       resolutionPathEntries = function.functionStoredResolutionPath
         .map(CatalogManager.deserializePathEntriesOrFail(
           _, "SQL function", function.name.unquotedString)),
-      // Unlike `withAnalysisContext(viewDesc)`, do NOT reset this 
accumulator. A SQL function has
-      // no IDENTIFIER-variable metadata of its own, so a variable its body 
reads through IDENTIFIER
-      // clause is part of the enclosing object's definition (e.g. the 
temporary view or CACHE TABLE
-      // AS SELECT that selects the function), and must be recorded there. 
Sharing the caller's set
-      // records it; a nested view, by contrast, stores its own and so is 
reset.
+      // Unlike `withAnalysisContext(viewDesc)`, do NOT reset this accumulator 
by default. A SQL
+      // function has no IDENTIFIER-variable metadata of its own that is 
consulted on resolution, so
+      // a variable its body reads through an IDENTIFIER clause is part of the 
enclosing object's
+      // definition (e.g. the temporary view or CACHE TABLE AS SELECT that 
selects the function),
+      // and must be recorded there. Sharing the caller's set records it; a 
nested view, by
+      // contrast, stores its own and so is reset. The exception is a SQL 
function body under
+      // creation, which must not inherit the dependencies of the functions it 
calls; see
+      // `shareIdentifierVariablesWithNestedSQLFunctions`.
       referredTempVariableNamesUnderIdentifier =
-        originContext.referredTempVariableNamesUnderIdentifier,
+        if (originContext.shareIdentifierVariablesWithNestedSQLFunctions) {
+          originContext.referredTempVariableNamesUnderIdentifier
+        } else {
+          mutable.LinkedHashSet.empty
+        },
       collation = function.collation)
     set(context)
     try f finally { set(originContext) }
@@ -317,6 +334,17 @@ object AnalysisContext {
     set(context)
     try f finally { set(originContext) }
   }
+
+  /**
+   * Runs `f` with `shareIdentifierVariablesWithNestedSQLFunctions` set to 
`share`; see that field.
+   */
+  def withSharingIdentifierVariablesWithNestedSQLFunctions[A](share: 
Boolean)(f: => A): A = {
+    val originContext = value.get()
+    val context = 
originContext.copy(shareIdentifierVariablesWithNestedSQLFunctions = share)
+    
context.setSinglePassResolverBridgeState(originContext.getSinglePassResolverBridgeState)
+    set(context)
+    try f finally { set(originContext) }
+  }
 }
 
 object Analyzer {
@@ -573,6 +601,30 @@ class Analyzer(
     }
   }
 
+  /**
+   * Like [[execute]], but also returns the temporary variables recorded via 
IDENTIFIER clauses
+   * during this analysis 
(`AnalysisContext.referredTempVariableNamesUnderIdentifier`).
+   *
+   * SQL function creation analyzes the body, inspects it, and only then runs 
`checkAnalysis`, so
+   * it cannot go through 
[[executeAndCheckReferredTempVariablesUnderIdentifier]]. It still needs
+   * the recorded variables, which [[execute]] discards with its analysis 
context: a persistent
+   * function must reject them and a temporary one stores them in its metadata.
+   *
+   * Only the variables read by the body itself are returned. A SQL function 
that the body calls
+   * is expanded in a context of its own, so a variable it reads through an 
IDENTIFIER clause stays
+   * that function's dependency rather than becoming one of the function under 
creation.
+   */
+  def executeWithReferredTempVariablesUnderIdentifier(
+      plan: LogicalPlan): (LogicalPlan, Seq[Seq[String]]) = {
+    AnalysisContext.withNewAnalysisContext {
+      
AnalysisContext.withSharingIdentifierVariablesWithNestedSQLFunctions(share = 
false) {
+        val analyzed = executeSameContext(plan)
+        // Read the accumulator before the context is restored on exit.
+        (analyzed, 
AnalysisContext.get.referredTempVariableNamesUnderIdentifier.toSeq)
+      }
+    }
+  }
+
   def resolver: Resolver = conf.resolver
 
   /**
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveIdentifierClause.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveIdentifierClause.scala
index 24a2300ee1b1..8c3c8e9b678c 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveIdentifierClause.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveIdentifierClause.scala
@@ -115,8 +115,10 @@ class ResolveIdentifierClause(earlyBatches: 
Seq[RuleExecutor[LogicalPlan]#Batch]
         // `CREATE TEMPORARY VIEW IDENTIFIER(v) AS ...`), the evaluated plan 
is that command and `v`
         // names the object rather than a variable it refers to, so recording 
it would wrongly
         // persist it as a dependency. The recorded set is consumed by 
temporary-view create/ALTER
-        // and CACHE TABLE AS SELECT (stored as metadata) and by persisted 
CREATE/ALTER VIEW
-        // (rejected -- unless the legacy flag makes the persisted paths 
discard it instead).
+        // and CACHE TABLE AS SELECT (stored as metadata), by persisted 
CREATE/ALTER VIEW
+        // (rejected -- unless the legacy flag makes the persisted paths 
discard it instead), and
+        // by CREATE FUNCTION (a persistent function rejects or, under the 
legacy flag, discards
+        // it; a temporary function stores it as informational metadata).
         if (recordUnderIdentifier && !resolvedPlan.isInstanceOf[Command]) {
           recordTemporaryVariablesUnderIdentifier(p.identifierExpr)
         }
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
index a5b0658d152e..ec39fc00185d 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
@@ -8836,8 +8836,8 @@ object SQLConf {
     buildConf("spark.sql.legacy.allowSessionVariableInPersistedView")
       .internal()
       .doc(
-        "When set to true, variables can be found under identifiers in a view 
query. Throw " +
-        "otherwise."
+        "When set to true, a persisted view or SQL function may read session 
variables " +
+        "through IDENTIFIER clauses in its body. Throw otherwise."
       )
       .version("4.1.0")
       .booleanConf
diff --git 
a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/CreateSQLFunctionCommand.scala
 
b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/CreateSQLFunctionCommand.scala
index 3c60f9c578f0..f4c46e7c0916 100644
--- 
a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/CreateSQLFunctionCommand.scala
+++ 
b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/CreateSQLFunctionCommand.scala
@@ -32,6 +32,7 @@ import org.apache.spark.sql.connector.catalog.CatalogManager
 import 
org.apache.spark.sql.connector.catalog.CatalogV2Implicits.MultipartIdentifierHelper
 import org.apache.spark.sql.errors.QueryCompilationErrors
 import 
org.apache.spark.sql.execution.command.CreateUserDefinedFunctionCommand._
+import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.types.{DataType, MetadataBuilder, StructField, 
StructType}
 
 /**
@@ -150,7 +151,9 @@ case class CreateSQLFunctionCommand(
       }
 
       // Build the function body and check if the function body can be 
analyzed successfully.
-      val (unresolvedPlan, analyzedPlan, inferredReturnType) = if 
(!isTableFunc) {
+      // The last element is the temporary variables the body reads only 
inside IDENTIFIER
+      // clauses. They are absent from the analyzed plan, so they are captured 
during analysis.
+      val (unresolvedPlan, analyzedPlan, inferredReturnType, identifierVars) = 
if (!isTableFunc) {
         // Build SQL scalar function plan.
         val outputExpr = if (query.isDefined) ScalarSubquery(query.get) else 
expression.get
         val plan: LogicalPlan = returnType.map { t =>
@@ -171,7 +174,8 @@ case class CreateSQLFunctionCommand(
         checkCyclicFunctionReference(catalog, name, plan)
 
         // Check the function body can be analyzed correctly.
-        val analyzed = analyzer.execute(plan)
+        val (analyzed, referredTempVarsUnderIdentifier) =
+          analyzer.executeWithReferredTempVariablesUnderIdentifier(plan)
         val (resolved, resolvedReturnType) = analyzed match {
           case p @ Project(expr :: Nil, _) if expr.resolved =>
             (p, Left(resolveReturnType(expr.dataType, collation)))
@@ -186,7 +190,7 @@ case class CreateSQLFunctionCommand(
         // Check if the SQL function body can be analyzed.
         checkFunctionBodyAnalysis(analyzer, function, resolved)
 
-        (plan, resolved, resolvedReturnType)
+        (plan, resolved, resolvedReturnType, referredTempVarsUnderIdentifier)
       } else {
         // Build SQL table function plan.
         if (query.isEmpty) {
@@ -197,7 +201,8 @@ case class CreateSQLFunctionCommand(
 
         // Construct a lateral join to analyze the function body.
         val plan = LateralJoin(inputPlan, LateralSubquery(query.get), Inner, 
None)
-        val analyzed = analyzer.execute(plan)
+        val (analyzed, referredTempVarsUnderIdentifier) =
+          analyzer.executeWithReferredTempVariablesUnderIdentifier(plan)
         val newPlan = analyzed match {
           case Project(_, j: LateralJoin) => j
           case j: LateralJoin => j
@@ -250,15 +255,22 @@ case class CreateSQLFunctionCommand(
           )
         }
 
-        (plan, analyzed, Right(returnParam))
+        (plan, analyzed, Right(returnParam), referredTempVarsUnderIdentifier)
       }
 
       // A permanent function is not allowed to reference temporary objects.
-      // This should be called after `qe.assertAnalyzed()` (i.e., `plan` can 
be resolved)
-      verifyTemporaryObjectsNotExists(catalog, isTemp, name, unresolvedPlan, 
analyzedPlan)
+      // This should be called after `qe.assertAnalyzed()` (i.e., `plan` can 
be resolved).
+      // Like persisted views, honor 
`spark.sql.legacy.allowSessionVariableInPersistedView` for the
+      // variables read only through IDENTIFIER clauses: with it set they are 
not rejected (and,
+      // as for views, not persisted either).
+      val identifierVarsToReject =
+        if (conf.getConf(SQLConf.VARIABLES_UNDER_IDENTIFIER_IN_VIEW)) Nil else 
identifierVars
+      verifyTemporaryObjectsNotExists(
+        catalog, isTemp, name, unresolvedPlan, analyzedPlan, 
identifierVarsToReject)
 
       // Generate function properties.
-      val properties = generateFunctionProperties(sparkSession, 
unresolvedPlan, analyzedPlan)
+      val properties =
+        generateFunctionProperties(sparkSession, unresolvedPlan, analyzedPlan, 
identifierVars)
 
       // Derive determinism of the SQL function.
       val deterministic = analyzedPlan.deterministic
@@ -367,13 +379,18 @@ case class CreateSQLFunctionCommand(
   /**
    * Permanent functions are not allowed to reference temp objects, including 
temp functions
    * and temp views.
+   *
+   * @param referredTempVarsUnderIdentifier temporary variables the body reads 
only inside
+   *                                        IDENTIFIER clauses, recorded 
during analysis because
+   *                                        the analyzed plan no longer 
mentions them.
    */
   private def verifyTemporaryObjectsNotExists(
       catalog: SessionCatalog,
       isTemporary: Boolean,
       name: FunctionIdentifier,
       child: LogicalPlan,
-      analyzed: LogicalPlan): Unit = {
+      analyzed: LogicalPlan,
+      referredTempVarsUnderIdentifier: Seq[Seq[String]]): Unit = {
     import org.apache.spark.sql.connector.catalog.CatalogV2Implicits._
     if (!isTemporary) {
       val (tempViews, tempFunctions) = 
collectTemporaryObjectsInUnresolvedPlan(catalog, child)
@@ -385,7 +402,8 @@ case class CreateSQLFunctionCommand(
         throw UserDefinedFunctionErrors.invalidTempFuncReference(
           routineName = name.asMultipart, tempFuncName = funcName)
       }
-      val tempVars = ViewHelper.collectTemporaryVariables(analyzed)
+      val tempVars =
+        (ViewHelper.collectTemporaryVariables(analyzed) ++ 
referredTempVarsUnderIdentifier).distinct
       tempVars.foreach { varName =>
         throw UserDefinedFunctionErrors.invalidTempVarReference(
           routineName = name.asMultipart, varName = varName)
@@ -508,22 +526,31 @@ case class CreateSQLFunctionCommand(
    * 2. the catalog and database name when creating the function. This will be 
used to provide
    *    context during nested function resolution.
    * 3. referred temporary object names if the function is a temp function.
+   *
+   * @param referredTempVarsUnderIdentifier temporary variables the body reads 
only inside
+   *                                        IDENTIFIER clauses, see
+   *                                        `verifyTemporaryObjectsNotExists`.
    */
   private def generateFunctionProperties(
       session: SparkSession,
       plan: LogicalPlan,
-      analyzed: LogicalPlan): Map[String, String] = {
+      analyzed: LogicalPlan,
+      referredTempVarsUnderIdentifier: Seq[Seq[String]]): Map[String, String] 
= {
     val catalog = session.sessionState.catalog
     val conf = session.sessionState.conf
     val manager = session.sessionState.catalogManager
 
-    // Only collect temporary object names when the function is a temp 
function.
-    val (tempViews, tempFunctions) = if (isTemp) {
-      collectTemporaryObjectsInUnresolvedPlan(catalog, plan)
+    // Only collect temporary object names when the function is a temp 
function. A persistent
+    // function has already rejected them, or discarded them under the legacy 
flag; as with
+    // persisted views, it stores none.
+    val (tempViews, tempFunctions, tempVars) = if (isTemp) {
+      val (views, functions) = 
collectTemporaryObjectsInUnresolvedPlan(catalog, plan)
+      val vars =
+        (ViewHelper.collectTemporaryVariables(analyzed) ++ 
referredTempVarsUnderIdentifier).distinct
+      (views, functions, vars)
     } else {
-      (Nil, Nil)
+      (Nil, Nil, Nil)
     }
-    val tempVars = ViewHelper.collectTemporaryVariables(analyzed)
 
     // Capture the effective resolution path at function creation time so the 
function
     // body resolves with the same path regardless of the caller's session 
path later.
diff --git 
a/sql/core/src/test/resources/sql-tests/analyzer-results/sql-udf.sql.out 
b/sql/core/src/test/resources/sql-tests/analyzer-results/sql-udf.sql.out
index d387c3979a01..ddc22e6e65da 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/sql-udf.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/sql-udf.sql.out
@@ -3959,6 +3959,219 @@ org.apache.spark.sql.AnalysisException
 }
 
 
+-- !query
+DECLARE OR REPLACE VARIABLE foo3_5_col STRING DEFAULT 'a'
+-- !query analysis
+CreateVariable default(cast(a as string), sql=''a''), true
++- ResolvedIdentifier 
org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, 
session.foo3_5_col
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT RETURN x + length(foo3_5_col)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_col`"
+  }
+}
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a))
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_col`"
+  }
+}
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (a INT)
+RETURN SELECT IDENTIFIER(foo3_5_col) AS a FROM VALUES (1) AS T(a)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_col`"
+  }
+}
+
+
+-- !query
+CREATE TEMPORARY FUNCTION foo3_5e(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a))
+-- !query analysis
+CreateSQLFunctionCommand foo3_5e, x INT, INT, (SELECT IDENTIFIER(foo3_5_col) 
FROM VALUES (1) AS T(a)), false, true, false, false
+
+
+-- !query
+SELECT foo3_5e(0)
+-- !query analysis
+Project [foo3_5e(x#x) AS foo3_5e(0)#x]
+:  +- Project [a#x]
+:     +- SubqueryAlias T
+:        +- LocalRelation [a#x]
++- Project [cast(0 as int) AS x#x]
+   +- OneRowRelation
+
+
+-- !query
+DROP TEMPORARY FUNCTION foo3_5e
+-- !query analysis
+DropFunctionCommand foo3_5e, false, true
+
+
+-- !query
+DECLARE OR REPLACE VARIABLE foo3_5_tbl STRING DEFAULT 't1'
+-- !query analysis
+CreateVariable default(cast(t1 as string), sql=''t1''), true
++- ResolvedIdentifier 
org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, 
session.foo3_5_tbl
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (c1 INT)
+RETURN SELECT c1 FROM IDENTIFIER(foo3_5_tbl)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_tbl`"
+  }
+}
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(session.foo3_5_col) FROM VALUES (1) AS T(a))
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`session`.`foo3_5_col`"
+  }
+}
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (c INT)
+RETURN SELECT IDENTIFIER(foo3_5_col) AS c FROM IDENTIFIER(foo3_5_tbl) AS T(a, 
b)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_tbl`"
+  }
+}
+
+
+-- !query
+SET spark.sql.legacy.allowSessionVariableInPersistedView=true
+-- !query analysis
+SetCommand (spark.sql.legacy.allowSessionVariableInPersistedView,Some(true))
+
+
+-- !query
+CREATE FUNCTION foo3_5f(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a))
+-- !query analysis
+CreateSQLFunctionCommand spark_catalog.default.foo3_5f, x INT, INT, (SELECT 
IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a)), false, false, false, false
+
+
+-- !query
+SELECT foo3_5f(0)
+-- !query analysis
+Project [spark_catalog.default.foo3_5f(x#x) AS 
spark_catalog.default.foo3_5f(0)#x]
+:  +- Project [a#x]
+:     +- SubqueryAlias T
+:        +- LocalRelation [a#x]
++- Project [cast(0 as int) AS x#x]
+   +- OneRowRelation
+
+
+-- !query
+SET spark.sql.legacy.allowSessionVariableInPersistedView=false
+-- !query analysis
+SetCommand (spark.sql.legacy.allowSessionVariableInPersistedView,Some(false))
+
+
+-- !query
+CREATE FUNCTION foo3_5g(x INT) RETURNS INT RETURN foo3_5f(x) + 1
+-- !query analysis
+CreateSQLFunctionCommand spark_catalog.default.foo3_5g, x INT, INT, foo3_5f(x) 
+ 1, false, false, false, false
+
+
+-- !query
+SELECT foo3_5g(0)
+-- !query analysis
+Project [spark_catalog.default.foo3_5g(x#x) AS 
spark_catalog.default.foo3_5g(0)#x]
+:  +- Project [a#x]
+:     +- SubqueryAlias T
+:        +- LocalRelation [a#x]
++- Project [x#x, cast(x#x as int) AS x#x]
+   +- Project [cast(0 as int) AS x#x]
+      +- OneRowRelation
+
+
+-- !query
+DROP FUNCTION foo3_5g
+-- !query analysis
+DropFunctionCommand spark_catalog.default.foo3_5g, false, false
+
+
+-- !query
+DROP FUNCTION foo3_5f
+-- !query analysis
+DropFunctionCommand spark_catalog.default.foo3_5f, false, false
+
+
+-- !query
+DROP TEMPORARY VARIABLE foo3_5_tbl
+-- !query analysis
+DropVariable false
++- ResolvedIdentifier 
org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, 
session.foo3_5_tbl
+
+
+-- !query
+DROP TEMPORARY VARIABLE foo3_5_col
+-- !query analysis
+DropVariable false
++- ResolvedIdentifier 
org.apache.spark.sql.catalyst.analysis.FakeSystemCatalog$@xxxxxxxx, 
session.foo3_5_col
+
+
 -- !query
 CREATE FUNCTION foo3_12a(x INT) RETURNS INT CONTAINS SQL RETURN x
 -- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/inputs/sql-udf.sql 
b/sql/core/src/test/resources/sql-tests/inputs/sql-udf.sql
index e796b7b192b3..b191d218be42 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/sql-udf.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/sql-udf.sql
@@ -817,6 +817,45 @@ CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (a INT) 
RETURN SELECT foo3_5a(x);
 -- Expect error: permanent function cannot reference temporary view
 CREATE FUNCTION foo3_5d(x INT) RETURNS INT RETURN (SELECT SUM(a) FROM t);
 CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (a INT) RETURN SELECT a FROM t;
+-- Expect error: permanent function cannot reference a temporary variable
+DECLARE OR REPLACE VARIABLE foo3_5_col STRING DEFAULT 'a';
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT RETURN x + length(foo3_5_col);
+-- Expect error: the same holds for a variable read only inside an IDENTIFIER 
clause. The analyzed
+-- body no longer mentions it (the clause is replaced by the expression built 
from the evaluated
+-- name), so it has to be recorded during analysis to be rejected here.
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a));
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (a INT)
+RETURN SELECT IDENTIFIER(foo3_5_col) AS a FROM VALUES (1) AS T(a);
+-- A temporary function may read the variable through an IDENTIFIER clause.
+CREATE TEMPORARY FUNCTION foo3_5e(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a));
+SELECT foo3_5e(0);
+DROP TEMPORARY FUNCTION foo3_5e;
+-- Expect error: the variable may also name a relation, or be qualified, or be 
one of several.
+DECLARE OR REPLACE VARIABLE foo3_5_tbl STRING DEFAULT 't1';
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (c1 INT)
+RETURN SELECT c1 FROM IDENTIFIER(foo3_5_tbl);
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(session.foo3_5_col) FROM VALUES (1) AS T(a));
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (c INT)
+RETURN SELECT IDENTIFIER(foo3_5_col) AS c FROM IDENTIFIER(foo3_5_tbl) AS T(a, 
b);
+-- With `spark.sql.legacy.allowSessionVariableInPersistedView` set, a 
persistent function may read
+-- the variable through an IDENTIFIER clause, as a persisted view may. The 
dependency is not
+-- persisted, so the function only resolves while the variable exists in the 
session.
+SET spark.sql.legacy.allowSessionVariableInPersistedView=true;
+CREATE FUNCTION foo3_5f(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a));
+SELECT foo3_5f(0);
+SET spark.sql.legacy.allowSessionVariableInPersistedView=false;
+-- A function that calls foo3_5f does not inherit its IDENTIFIER-clause 
variable: the variable is
+-- foo3_5f's own dependency, so creating the caller succeeds without the 
legacy flag.
+CREATE FUNCTION foo3_5g(x INT) RETURNS INT RETURN foo3_5f(x) + 1;
+SELECT foo3_5g(0);
+DROP FUNCTION foo3_5g;
+DROP FUNCTION foo3_5f;
+DROP TEMPORARY VARIABLE foo3_5_tbl;
+DROP TEMPORARY VARIABLE foo3_5_col;
 
 -- 3.12 SQL data access routine
 -- Scalar functions
diff --git a/sql/core/src/test/resources/sql-tests/results/sql-udf.sql.out 
b/sql/core/src/test/resources/sql-tests/results/sql-udf.sql.out
index 3b522f6e47a6..1d3c9f06c40b 100644
--- a/sql/core/src/test/resources/sql-tests/results/sql-udf.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/sql-udf.sql.out
@@ -4191,6 +4191,241 @@ org.apache.spark.sql.AnalysisException
 }
 
 
+-- !query
+DECLARE OR REPLACE VARIABLE foo3_5_col STRING DEFAULT 'a'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT RETURN x + length(foo3_5_col)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_col`"
+  }
+}
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_col`"
+  }
+}
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (a INT)
+RETURN SELECT IDENTIFIER(foo3_5_col) AS a FROM VALUES (1) AS T(a)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_col`"
+  }
+}
+
+
+-- !query
+CREATE TEMPORARY FUNCTION foo3_5e(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a))
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT foo3_5e(0)
+-- !query schema
+struct<foo3_5e(0):int>
+-- !query output
+1
+
+
+-- !query
+DROP TEMPORARY FUNCTION foo3_5e
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DECLARE OR REPLACE VARIABLE foo3_5_tbl STRING DEFAULT 't1'
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (c1 INT)
+RETURN SELECT c1 FROM IDENTIFIER(foo3_5_tbl)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_tbl`"
+  }
+}
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(session.foo3_5_col) FROM VALUES (1) AS T(a))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`session`.`foo3_5_col`"
+  }
+}
+
+
+-- !query
+CREATE FUNCTION foo3_5d(x INT) RETURNS TABLE (c INT)
+RETURN SELECT IDENTIFIER(foo3_5_col) AS c FROM IDENTIFIER(foo3_5_tbl) AS T(a, 
b)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+  "errorClass" : "INVALID_TEMP_OBJ_REFERENCE",
+  "sqlState" : "42K0F",
+  "messageParameters" : {
+    "obj" : "FUNCTION",
+    "objName" : "`default`.`foo3_5d`",
+    "tempObj" : "VARIABLE",
+    "tempObjName" : "`foo3_5_tbl`"
+  }
+}
+
+
+-- !query
+SET spark.sql.legacy.allowSessionVariableInPersistedView=true
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.legacy.allowSessionVariableInPersistedView   true
+
+
+-- !query
+CREATE FUNCTION foo3_5f(x INT) RETURNS INT
+RETURN (SELECT IDENTIFIER(foo3_5_col) FROM VALUES (1) AS T(a))
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT foo3_5f(0)
+-- !query schema
+struct<spark_catalog.default.foo3_5f(0):int>
+-- !query output
+1
+
+
+-- !query
+SET spark.sql.legacy.allowSessionVariableInPersistedView=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.legacy.allowSessionVariableInPersistedView   false
+
+
+-- !query
+CREATE FUNCTION foo3_5g(x INT) RETURNS INT RETURN foo3_5f(x) + 1
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT foo3_5g(0)
+-- !query schema
+struct<spark_catalog.default.foo3_5g(0):int>
+-- !query output
+2
+
+
+-- !query
+DROP FUNCTION foo3_5g
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP FUNCTION foo3_5f
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TEMPORARY VARIABLE foo3_5_tbl
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+DROP TEMPORARY VARIABLE foo3_5_col
+-- !query schema
+struct<>
+-- !query output
+
+
+
 -- !query
 CREATE FUNCTION foo3_12a(x INT) RETURNS INT CONTAINS SQL RETURN x
 -- !query schema
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/SQLFunctionSuite.scala 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/SQLFunctionSuite.scala
index d7956a9c1217..1e17d49a2b0b 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/SQLFunctionSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/SQLFunctionSuite.scala
@@ -21,6 +21,11 @@ import java.text.SimpleDateFormat
 import java.util.Locale
 
 import org.apache.spark.sql.{AnalysisException, Row}
+import org.apache.spark.sql.catalyst.FunctionIdentifier
+import org.apache.spark.sql.catalyst.analysis.SQLFunctionExpression
+import org.apache.spark.sql.catalyst.catalog.SQLFunction
+import org.apache.spark.sql.catalyst.expressions.Literal
+import org.apache.spark.sql.connector.catalog.CatalogManager
 import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.test.SharedSparkSession
 
@@ -35,6 +40,80 @@ class SQLFunctionSuite extends SharedSparkSession {
     Seq((0, 1), (1, 2)).toDF("a", "b").createOrReplaceTempView("t")
   }
 
+  test("SPARK-59970: a SQL function does not inherit the IDENTIFIER-clause 
variables of the " +
+    "functions it calls") {
+    withUserDefinedFunction("ident_callee" -> false, "ident_caller" -> false) {
+      sql("DECLARE OR REPLACE VARIABLE ident_col STRING DEFAULT 'a'")
+      try {
+        // The callee may only be created with the legacy flag; its dependency 
on `ident_col` is
+        // its own and is not persisted.
+        withSQLConf(SQLConf.VARIABLES_UNDER_IDENTIFIER_IN_VIEW.key -> "true") {
+          sql("CREATE FUNCTION ident_callee(x INT) RETURNS INT " +
+            "RETURN (SELECT IDENTIFIER(ident_col) FROM VALUES (1) AS T(a))")
+        }
+        checkAnswer(sql("SELECT ident_callee(0)"), Row(1))
+        // Expanding the callee must not attribute `ident_col` to the caller: 
creating the caller
+        // succeeds without the flag. (The variable must still exist, since 
the callee's body is
+        // inlined and its IDENTIFIER clause is evaluated during this 
analysis.)
+        sql("CREATE FUNCTION ident_caller(x INT) RETURNS INT RETURN 
ident_callee(x) + 1")
+        // A direct IDENTIFIER read in the caller's own body is still rejected.
+        checkError(
+          exception = intercept[AnalysisException] {
+            sql("CREATE FUNCTION ident_caller2(x INT) RETURNS INT " +
+              "RETURN ident_callee(x) + (SELECT IDENTIFIER(ident_col) FROM 
VALUES (1) AS T(a))")
+          },
+          condition = "INVALID_TEMP_OBJ_REFERENCE",
+          parameters = Map(
+            "obj" -> "FUNCTION",
+            "objName" -> "`default`.`ident_caller2`",
+            "tempObj" -> "VARIABLE",
+            "tempObjName" -> "`ident_col`"))
+        checkAnswer(sql("SELECT ident_caller(0)"), Row(2))
+      } finally {
+        sql("DROP TEMPORARY VARIABLE IF EXISTS ident_col")
+      }
+    }
+  }
+
+  test("SPARK-59970: IDENTIFIER-clause variables are stored for a temporary 
function only") {
+    val variableNamesKey = "referredTempVariableNames"
+    withUserDefinedFunction("ident_temp_fn" -> true, "ident_perm_fn" -> false) 
{
+      sql("DECLARE OR REPLACE VARIABLE ident_col STRING DEFAULT 'a'")
+      try {
+        // A temporary function records the variable in its properties, as it 
does a direct
+        // reference. The property is informational and is not consulted when 
the body is
+        // re-analyzed: a function body resolves variables from the caller's 
context.
+        sql("CREATE TEMPORARY FUNCTION ident_temp_fn(x INT) RETURNS INT " +
+          "RETURN (SELECT IDENTIFIER(ident_col) FROM VALUES (1) AS T(a))")
+        // Temporary functions are registered under the qualified 
`system.session` namespace.
+        val tempFn = spark.sessionState.functionRegistry
+          .lookupFunctionBuilder(FunctionIdentifier(
+            "ident_temp_fn",
+            Some(CatalogManager.SESSION_NAMESPACE),
+            Some(CatalogManager.SYSTEM_CATALOG_NAME))).get
+          .apply(Seq(Literal(0))) match {
+            case f: SQLFunctionExpression => f.function
+            case other => fail(s"unexpected builder result: $other")
+          }
+        assert(tempFn.properties(variableNamesKey) === """[["ident_col"]]""")
+
+        // A persistent function created under the legacy flag stores no 
temporary variable, as a
+        // persisted view does not: the dependency is tolerated, not persisted.
+        withSQLConf(SQLConf.VARIABLES_UNDER_IDENTIFIER_IN_VIEW.key -> "true") {
+          sql("CREATE FUNCTION ident_perm_fn(x INT) RETURNS INT " +
+            "RETURN (SELECT IDENTIFIER(ident_col) FROM VALUES (1) AS T(a))")
+        }
+        val permFn = SQLFunction.fromCatalogFunction(
+          spark.sessionState.catalog.getFunctionMetadata(
+            FunctionIdentifier("ident_perm_fn", Some("default"))),
+          spark.sessionState.sqlParser)
+        assert(permFn.properties(variableNamesKey) === "[]")
+      } finally {
+        sql("DROP TEMPORARY VARIABLE IF EXISTS ident_col")
+      }
+    }
+  }
+
   test("SQL scalar function") {
     withUserDefinedFunction("area" -> false) {
       sql(


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to