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]

Reply via email to