Yaoxuan Wu created SPARK-61945:
----------------------------------

             Summary: SQL scripting: a temp view that refers to a session 
variable reads a same-named script-local variable instead
                 Key: SPARK-61945
                 URL: https://issues.apache.org/jira/browse/SPARK-61945
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 4.1.1, 4.2.0
            Reporter: Yaoxuan Wu


A temporary view that refers to a session variable reads a SQL-script local 
variable of the same name instead, when the view is used inside a script that 
declares such a local. The view's result then depends on the caller's local 
variables. No error is raised.

{code:sql}
CREATE OR REPLACE TEMP VIEW t AS SELECT id AS k FROM range(10);
DECLARE OR REPLACE VARIABLE sv INT DEFAULT 5;
CREATE OR REPLACE TEMP VIEW tv AS SELECT * FROM t WHERE k > sv;

SELECT count(*) FROM tv;
-- 4

BEGIN DECLARE sv INT DEFAULT 0; SELECT count(*) FROM tv; END;
-- expected: 4  (tv was defined over the session variable system.session.sv = 5)
-- actual:   9  (tv reads the script-local sv = 0)

BEGIN DECLARE other INT DEFAULT 0; SELECT count(*) FROM tv; END;
-- 4
{code}

The same happens for a view created inside a script over a local variable of an 
outer block and read in an inner block that declares a variable of the same 
name:

{code:sql}
BEGIN
  DECLARE v INT DEFAULT 5;
  CREATE OR REPLACE TEMP VIEW vv AS SELECT * FROM t WHERE k > v;
  BEGIN
    DECLARE v INT DEFAULT 0;
    SELECT count(*) FROM vv;   -- 9, expected 4
  END;
END;
{code}

* Same on 4.1.1 and 4.2.0.
* A caller cannot see which variable names a view uses, so any local 
declaration can change a view's result.

Cause (master d3f4a73a): temp views keep their SQL text and are analyzed again 
at each use. The names of the temporary variables the view referred to at 
creation are recorded ({{viewReferredTempVariableNames}} in 
{{AnalysisContext}}). While resolving a view, 
{{VariableResolution.resolveVariable}} accepts only those names, but then calls 
the general {{lookupVariable}}, which first asks the SQL scripting context 
({{SqlScriptingContextManager.get().flatMap(_.getVariableManager)}}) for a 
local variable of that name, and falls back to the session variable manager 
only when there is none.

A name recorded as a session variable reference should be looked up in the 
session variable manager only. A temp view over a script-local variable should 
either capture that variable or be rejected, as a persistent view is 
(INVALID_TEMP_OBJ_REFERENCE).




--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

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

Reply via email to