Snowflake UDTF (User-Defined-Table-Function) SQL compilation error: Unsupported subquery type cannot be evaluated

Question

Seems like I should be able to join a user defined table function to a select statement. Unfortunately I get an error. On review of other similar questions there was some talk about this being a bug in Snowflake that was fixed, but I do not know if that bugfix covered this exact case.

Note that for my application- assuming I can get past this error, I need to add many about 10 more input arguments and 10+ output values to the function, so a single output scalar function solution will NOT work.

create function tmp_fn(
dd float
,ostrat float
)
returns table (
urounded_soq float
) as
'
select 
cast(dd * ostrat / cast(7 as float) as float) as unrounded_soq
'

create or replace temporary table test_fn as
select 6 dd, 9 ostrat
union all
select 12 dd,38 ostrat
;

--this works, but of course because the argument values are hardcoded, this is useless to my application.
select 
    t.*
    ,f.*
from test_fn t
    join table(tmp_fn(cast(6 as float),cast(9 as float))) f
;

--this does not work, this is the error "SQL compilation error: Unsupported subquery type cannot be evaluated"
select 
    t.*
    ,f.*
from test_fn t
    join table(tmp_fn(cast(t.dd as float),cast(t.ostrat as float))) f
;

Noted that according to:

select current_version();

my snowflake version is 4.19.2

I deleted my earlier answer, since it didn't resolve your issue. I will keep looking into the issue for you. Based on the use case, though, I'd be interested in better understanding why a view wouldn't work for you. I think it would likely perform better than a UDTF. — Mike Walton
First off, in every other major database system I have used, table functions just work and can be a terrific way to optimize queries with complex calculations. In this specific case, the flow starts with a massive snowflake base table that has a records of corporate purchases. Then I take a subset, put it in a temp table and calculate a bunch of stats at multiple levels, and join the stats back into the temp table. I. So a view solution would either have to reference a temp table - (does that even work?), or I'd have to write the temp table - is either option really a good practice? — Nathan T Alexander
Like I said, I'm not giving up on the UDTF, but based on the information you had given me previously, a view would've worked. Now, you've added more complexity, so clearly it won't. OOC, have you looked into the JS UDTF? Perhaps you could actually apply a lot of that logic directly inside that? — Mike Walton
Looks like with latest version 4.20.3, the query above works correctly — Stuart Ozer
Stuart, many thanks for confirming that! I'll get after my administrator. UDTF's are so useful. — Nathan T Alexander

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-06-08T21:27:20

I don't yet know why that's giving you the unsupported subquery type error, but if you convert it to a Javascript UDTF it works fine:

create or replace function tmp_fn(
DD float
,OSTRAT float
)
returns table (UROUNDED_SOQ float)
language javascript
as
$$
{
    processRow: function (row, rowWriter, context){
        rowWriter.writeRow({UROUNDED_SOQ: (row.DD * row.OSTRAT) / 7});
    }
}
$$;

create or replace temporary table test_fn as
select 6 dd, 9 ostrat
union all
select 12 dd,38 ostrat
;

--this works, but of course because the argument values are hardcoded, this is useless to my application.
select 
    t.DD, t.OSTRAT
    ,f.UROUNDED_SOQ
from test_fn t
    join table(tmp_fn(cast(6 as float),cast(9 as float))) f
;

--this does not work, this is the error "SQL compilation error: Unsupported subquery type cannot be evaluated"
select 
    t.DD, t.OSTRAT
    ,urounded_soq
from test_fn t, lateral(table(tmp_fn(t.dd::float,t.ostrat::float)))
;

Snowflake UDTF (User-Defined-Table-Function) SQL compilation error: Unsupported subquery type cannot be evaluated

1 Answers