Snowflake: Decimal or null input to function results in "Unsupported subquery type"

Question

Given the following function:

CREATE
OR REPLACE FUNCTION myfunction(a float, b float, c float)
RETURNS float AS
$$ 
select sum(1/(1+exp(-(series - c)/4)))
from (
    select (a + ((row_number()) over(order by 0))*1) series
    from table(generator(rowcount => 10000)) x
    qualify series <= b
)
$$;

I get all the expected results when executing the following queries:

select
    myfunction(1, 10, 1);
select
    myfunction(1, 100, 1);
select
    myfunction(1, 10, 1.1);
select
    myfunction(0, 1, 89.87);
select
    myfunction(0, 1, null);

However when I run the following query:

select
    myfunction(a, b, c)
from
    (
        select
            1 as a,
            10 as b,
            1.1 as c
        union
        select
            0 as a,
            1 as b,
            null as c
    );

I get an error:

"Unsupported subquery type cannot be evaluated".

While this query does work:

select
    a, b, myfunction(a, b, c)
from
    (
        select
            1 as a,
            10 as b,
            1 as c
        union
        select
            1 as a,
            100 as b,
            1 as c 
    );

Why can't Snowflake handle null or decimal numbers in the 'c' column when I input multiple rows while individual rows weren't a problem? And how can this function be rewritten to be able to handle these cases?

Answer 1

SQL UDFs are converted to subqueries (for now), and if Snowflake can not determine the data type returned from these subqueries, you get the "Unsupported subquery" error. The issue is not about decimals or null. The issue is A and C variables (which are used in SUM()) contain different values. For example, the following ones work:

select
    myfunction(a, b, c )
from
    (
        select
            1 as a,
            1 as b,
            1.1 as c
        union
        select
            1 as a,
            100 as b,
            1.1  as c
    );

select
    myfunction(a, b, c )
from
    (
        select
            1 as a,
            1 as b,
            null as c
        union
        select
            1 as a,
            100 as b,
            null  as c
    );

You may hit these kinds of errors when you try to write complex functions with SQL UDFs. Sometimes rewriting them can help, but I don't see a way for this one. As a workaround, you may re-write it in JavaScript because JS UDFs are not converted to subqueries:

CREATE
OR REPLACE FUNCTION myfunction(a float, b float, c float)
RETURNS float 
language javascript AS
$$ 
  var res = 0.0;
  
  for (let series = A + 1; series <= B; series++) {
      res += (1/(1+Math.exp(-(series - C)/4))); 
  }
 
  return res;
$$; 

According to my tests, the above UDF returns the same result as the SQL version, and it doesn't hit "Unsupported subquery" error.