Snowflake Scripting - SELECT INTO variable when no rows returned

Question

I am trying to determine what is the expected behaviour when SELECT INTO does not return any rows.

From Setting Variables to the Results of a SELECT Statement:

In a Snowflake Scripting block, you can set variables to the values in columns specified in a SELECT clause.
SELECT , , ... INTO , , ... 
FROM ... WHERE ...;
The SELECT statement must return a single row.

Now the examples.

Exactly one row - works as described, variables a,b get new values

DECLARE 
    a INT DEFAULT 1;
    b INT DEFAULT 2;
BEGIN
   SELECT x, y
   INTO a, b
   FROM (SELECT 100 AS x, 200 AS y) s;
   
   RETURN CONCAT(a, '    ', b);
END;
-- Output
-- 100    200

More than one row - also works as expected(error):

DECLARE 
    a INT DEFAULT 1;
    b INT DEFAULT 2;
BEGIN
   SELECT x, y
   INTO a, b
   FROM (SELECT 100 AS x, 200 AS y
         UNION ALL
         SELECT 100 AS x, 200 AS y
        ) s;
   
   RETURN CONCAT(a, '    ', b);
END;

A SELECT INTO statement expects exactly 1 returned row, but got 2.

No rows returned - here the output is NULL, the DEFAULT values for a and b are gone

DECLARE 
    a INT DEFAULT 1;
    b INT DEFAULT 2;
BEGIN
   SELECT x, y
   INTO a, b
   FROM (SELECT 100 AS x, 200 AS y) s
   WHERE 1=2;
   
   RETURN CONCAT(a, '    ', b);
END;
-- Output
-- null

For comparison similar setup for Oracle db<>fiddle demo and PostgreSQL db<>fiddle demo

Is the assignment of NULL values an expected behaviour or should I rather get an error like("A SELECT INTO statement expects exactly 1 returned row, but got 0.)"?

user_132438 · Answer

This is great feedback for the team while the feature is in preview — I'm passing it forward to them.

In the meantime I figured out this method to force an exception if the inner select doesn't return at least 1 row:

Add a limit 1 to get only one row.
union an extra row with a division by zero error, that will be raised if the inner query doesn't return at least one row.

DECLARE 
    a INT DEFAULT 1;
    b INT DEFAULT 2;
BEGIN
   SELECT x, y
   INTO a, b
   FROM (
       select *
       from (SELECT 100 AS x, 200 AS y) s
       --WHERE 1=2  -- test with this line uncommented
       union all 
       select 1/0, 1/0
       limit 1
   );   
   RETURN CONCAT(a, '    ', b);
END;

user_9879379 · Answer

The current SQL UDF behaves the same way:

create or replace table test (a int);
create function test_udf() returns number as 'select a from test';

select test_udf();
-- returns "null"

So I think at least it is consistent between the two. I personally believe that it is expected behaviour.

Snowflake Scripting - SELECT INTO variable when no rows returned

2 Answers