VOLATILE | IMMUTABLE behavior in snowflake

Question

I would like to understand the behavior of VOLATILE and IMMUTABLE keywords while we use during Procedure creation

As per the documentation, I have understood the theory

But I am trying to understand how these two keywords are behaving practically.

I have tried following examples

Example - IMMUTABLE

CREATE or REPLACE PROCEDURE HELLO_WORLD()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
IMMUTABLE
AS
    $$
        var sql_command = 
     "select id, name from emp";
    try {
        var stmt = snowflake.execute (
            {sqlText: sql_command}
            );
         var a = []
         while (stmt.next()){
            a.push(stmt.getColumnValue(1))
         }
        return a;   
        }
    catch (err)  {
        return "Failed: " + err;  
        }
        
    $$;
    
CALL HELLO_WORLD();

Example - VOLATILE

CREATE or REPLACE PROCEDURE HELLO_WORLD()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
VOLATILE
AS
    $$
        var sql_command = 
     "select id, name from emp";
    try {
        var stmt = snowflake.execute (
            {sqlText: sql_command}
            );
         var a = []
         while (stmt.next()){
            a.push(stmt.getColumnValue(1))
         }
        return a;   
        }
    catch (err)  {
        return "Failed: " + err;  
        }
        
    $$;
    
CALL HELLO_WORLD();

Both returns same output

[ 3, 2, 1 ]

Can anyone explain with any different example please ?

user_132438 · Answer

Perhaps the difference is easier to understand with a UDF (instead of a stored procedure, but the semantics are the same):

create or replace function x(a string)
returns float 
language javascript
immutable  -- replace with volatile
as '
return Math.random();
';

select x('1')
from table(generator(rowcount => 5))

When the function is immutable: The select returns 5 identical random numbers.

When the function is volatile: The select returns 5 different random numbers.

Perhaps the documentation for the stored procedures should say "The procedure might return different values for the same input" instead of "The procedure might return different values for different rows".

VOLATILE | IMMUTABLE behavior in snowflake

1 Answers