UDF not working inside transform() & lambda (SQL)

r0nald
New Contributor II

Below is toy example of what I'm trying to achieve, but don't understand why it fails. Can anyone explain why, and suggest a fix or not overly bloated workaround?

%sql
create or replace function status_map(status int)
returns string
return map(
10, "STATUS_10",
20, "STATUS_20"
)[status];

with s1 as (
select array(map('v1', 10), map('v1', 20)) as arr
)
-- WORKS: select transform(arr, e -> e.v1) from s1
-- WORKS: select transform(arr, e -> status_map(10)) from s1

-- WHY THIS NO WORKS?!?!?111
select transform(arr, e -> status_map(e.v1)) from s1

The code below fails with:

Error in SQL statement: AnalysisException: Resolved attribute(s) e#134732 missing from in operator !Project [cast(lambda e#134732[v1] as int) AS status#134734].; line 8 pos 27 

Behavior is same if I use named_struct() instead of map().