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]