CODEWITHSUNDEEP
javascriptsnowflake-cloud-data-platform
Ravi
Ravi

Reputation: 667

javascript function giving NULL inside a snowflake function

I have created a function inside snowflake using Javascript to calculate the string distance However the function is giving me NULL values. I am not able to debug the function.

Code:

CREATE OR REPLACE FUNCTION cat_map_trial4(a string,b string)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
function levenshteinDistance(s1, s2) {
    if (!s1.length) return toString(s2.length);
    if (!s2.length) return toString(s1.length);

    return toString(Math.min(
        levenshteinDistance(s1.substr(1), s2) + 1,
        levenshteinDistance(s2.substr(1), s1) + 1,
        levenshteinDistance(s1.substr(1), s2.substr(1)) + (s1[0] !== s2[0] ? 1 : 0)
    ) + 1);
}
$$


select cat_map_trial4('stackoverflow', 'stack') as dist;

Output

dist
NULL

What is going wrong with Javascript ? Any help would be appreciated. Thank you.

Upvotes: 0

Views: 235

Answers (1)

Marcin Zukowski
Marcin Zukowski

Reputation: 4729

There's a number of issues in your code

  • you define a function, but do not use it, you need to add a call at the end
  • you incorrectly use toString, if you insist on strings, use String(value)
  • if you use strings, you can't add them with a '+' to add their numeric values...
  • you have an unnecessary +1 at the end
  • you use a very slow implementation of this algorithm, you should be using something faster, read up on various ways to compute it

Here's a corrected version (still slow, but works):

CREATE OR REPLACE FUNCTION cat_map_trial4(a string,b string)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
function levenshteinDistance(s1, s2) {
    if (!s1.length) return s2.length;
    if (!s2.length) return s1.length;
    return Math.min(
        levenshteinDistance(s1.substr(1), s2) + 1,
        levenshteinDistance(s2.substr(1), s1) + 1,
        levenshteinDistance(s1.substr(1), s2.substr(1)) + (s1[0] !== s2[0] ? 1 : 0)
    );
}
return levenshteinDistance(A, B);
$$;

But the biggest problem with your code is that it's completely unnecessary, as Snowflake, being as awesome as it is, provides a built-in, fast EDITDISTANCE function :)

Upvotes: 2

Related Questions