Home Snowflake JavaScript Stored Procedure Returns Success Despite Expected Failure

Questions

Snowflake JavaScript Stored Procedure Returns Success Despite Expected Failure

April 3, 2024

I’ve recently been experimenting with Snowflake’s JavaScript stored procedures and encountered an unexpected behavior. I created a simple stored procedure as outlined in the Snowflake documentation here.

Here’s the stored procedure I created:

create procedure broken()
      returns varchar not null
      language javascript
      as
      $$
      var result = "";
      try {
          snowflake.execute( {sqlText: "Invalid Command!;"} );
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
      $$
      ;

The purpose of this stored procedure is to deliberately fail by executing an invalid SQL command and then catch the error using a try-catch block.

When I call the stored procedure, it indeed returns the expected error message, indicating a failure:

call broken();

Output:

+---------------------------------------------------------+
| BROKEN                                                  |
|---------------------------------------------------------|
| Failed: Code: 1003                                      |
|   State: 42000                                          |
|   Message: SQL compilation error:                       |
| syntax error line 1 at position 0 unexpected 'Invalid'. |
| Stack Trace:                                            |
| Snowflake.execute, line 4 position 20                   |
+---------------------------------------------------------+

However, upon checking Snowflake’s query history, I noticed that this procedure is marked as a successful run. This seems contradictory since the procedure explicitly aims to fail. I expect it to be tagged as a failed execution.

Could anyone shed some light on why Snowflake considers this a successful run despite the expected failure? Is there a different approach to handle such scenarios and ensure that failed stored procedure executions are correctly reflected in the query history? Any insights or suggestions would be greatly appreciated. Thank you!

>Solution :

return a string "I HAVE FAILED" is a success, because it happened, where-as, actually failing:

create procedure actual_failure()
    returns varchar not null
    language javascript as
$$
    snowflake.execute( {sqlText: "Invalid Command!;"} );
    return "Succeeded";
$$
;

then