Snowflake Behavior differently for literal values and table values

Question

I have a function AVGF, which is suppose to output average of comma separated sequence

CREATE OR REPLACE FUNCTION AVGF (STR VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS $$
    var str_array = STR.split(",");
    var total = 0.0
    for (i = 0; i < str_array.length; i += 1) {
        total += parseFloat(str_array[i]);
    }
    return total/str_array.length;
  $$
  ;

It works fine for example mentioned below

SELECT AVGF('1,2,3')

Output: 2

However, when I use it on top of table it doesn't work

SELECT AVGF(concat(TO_CHAR(ic.col1) ,',',TO_CHAR(ic.col2) )::STRING)
from tab

Output: JavaScript execution error: Uncaught TypeError: Cannot read property 'split' of undefined in AVGF at '  var str_array = STR.split(",");' position 21

where tab is table with col1(number) & col2(float)

Any reason for this and any solution?

Anup Thomas Anup Thomas · Accepted Answer · 2020-12-24T04:58:32

This was a casting issue, modified the function as mentioned below to make it work

CREATE OR REPLACE FUNCTION AVGF (STR VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS $$
    var str_array = String(STR).split(",");
    var total = 0.0
    for (i = 0; i < str_array.length; i += 1) {
        total += parseFloat(str_array[i]);
    }
    return total/str_array.length;
  $$
  ;

Snowflake Behavior differently for literal values and table values

1 Answers