Wenchen Fan created SPARK-61977:
-----------------------------------
Summary: Resolve a generator output alias in the same SELECT list
before outer references and SQL variables
Key: SPARK-61977
URL: https://issues.apache.org/jira/browse/SPARK-61977
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: Wenchen Fan
A SELECT item that references the output alias of a preceding generator in the
same SELECT list, such as `tag` in `SELECT explode(tags) AS tag, upper(tag)`,
binds to a same-named column of the outer query (inside a LATERAL or correlated
subquery) or to a same-named SQL variable, instead of the generator output. No
error is raised; the query silently returns wrong results.
{code:sql}
CREATE TEMPORARY VIEW users AS SELECT * FROM VALUES ('ann', 'vip'), ('bob',
'new') AS t(user_name, tag);
CREATE TEMPORARY VIEW sessions AS
SELECT * FROM VALUES ('ann', array('mobile', 'promo')), ('bob',
array('repeat')) AS t(user_name, tags);
SELECT u.user_name, x.tag, x.tag_uc
FROM users u, LATERAL (
SELECT explode(s.tags) AS tag, upper(tag) AS tag_uc
FROM sessions s WHERE s.user_name = u.user_name) x;
-- actual: [ann,mobile,VIP] [ann,promo,VIP] [bob,repeat,NEW]
-- expected: [ann,mobile,MOBILE] [ann,promo,PROMO] [bob,repeat,REPEAT]
DECLARE VARIABLE tag STRING DEFAULT 'var';
SELECT explode(array('a', 'b')) AS tag, upper(tag);
-- actual: [a,VAR] [b,VAR]
-- expected: [a,A] [b,B]
{code}
The reference stays unresolved until ExtractGenerator moves the generator into
a Generate node, and in the meantime the last-resort resolution in
ResolveReferences binds it to an outer reference or a SQL variable. A lateral
column alias in the same position already has higher priority than outer
references.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]