Snowflake JavaScript UDTF编译失败：无分隔符拆分字符串问题

SQL Server中，未指定分隔符的SPLIT函数会按单个字符拆分字符串；但Snowflake的同名函数在无分隔符时会直接返回原字符串。为此编写了Snowflake JavaScript UDTF，用于实现按单个字符拆分字符串，并返回包含字符索引与字符的表，原函数代码如下：

CREATE OR REPLACE FUNCTION split_word(WORD VARCHAR)
    RETURNS TABLE (ID smallint, LETTER CHAR(1))
    LANGUAGE JAVASCRIPT
    AS $$
      {
        initialize: function (argumentInfo, context) {/*...*/},
        processRow: function (row, rowWriter, context) {
           var ccount = 0;
           var warray = row.WORD.split('');
           var wlen = row.WORD.length;
           while (ccount < wlen) {
             rowWriter.writeRow( {ID: ccount + 1},{LETTER: warray[ccount]} );
             ccount = ccount + 1;
           }
        },
        finalize: function (rowWriter, context) {/*...*/}
      }
      $$;

执行查询select * from table(split_word('Jose'));时触发编译/查询失败，错误信息：

000603 (XX000): SQL execution internal error:
Processing aborted due to error 300010:3011697808; incident 3311582.

而对应的纯JavaScript逻辑可正常运行，代码及输出如下：

word='Jose';
var ccount = 0;
var warray = word.split('');
var wlen = word.length
while (ccount < wlen) {
    console.log( {ID: ccount + 1,LETTER: warray[ccount]} );
  ccount = ccount + 1;
}

输出：

>node .\split.js
{ ID: 1, LETTER: 'J' }
{ ID: 2, LETTER: 'o' }
{ ID: 3, LETTER: 's' }
{ ID: 4, LETTER: 'e' }

问题原因 #

核心错误在于rowWriter.writeRow()的调用方式不符合Snowflake JavaScript UDTF的规范：

• Snowflake的rowWriter.writeRow()仅接受单个参数，该参数是包含所有输出列键值对的对象
• 原代码中错误地将ID和LETTER拆分为两个独立对象传入，导致UDTF执行时内部逻辑异常，触发系统错误

修正后的函数代码 #

将writeRow的参数合并为单个对象即可解决问题：

CREATE OR REPLACE FUNCTION split_word(WORD VARCHAR)
    RETURNS TABLE (ID smallint, LETTER CHAR(1))
    LANGUAGE JAVASCRIPT
    AS $$
      {
        initialize: function (argumentInfo, context) {},
        processRow: function (row, rowWriter, context) {
           var ccount = 0;
           var warray = row.WORD.split('');
           var wlen = row.WORD.length;
           while (ccount < wlen) {
             // 合并为单个对象传入writeRow
             rowWriter.writeRow( {
               ID: ccount + 1,
               LETTER: warray[ccount]
             } );
             ccount = ccount + 1;
           }
        },
        finalize: function (rowWriter, context) {}
      }
      $$;

执行修正后的查询select * from table(split_word('Jose'));，将得到预期结果：

内容的提问来源于stack exchange，提问作者Jose Pla