CODEWITHSUNDEEP
snowflake-cloud-data-platform
Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Snowflake Scripting - SELECT INTO variable when no rows returned

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 <column1>, <column2>, ... INTO <variable1>, <variable2>, ... 
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.)"?

Upvotes: 3

Views: 2945

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

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;

Upvotes: 2

Eric Lin
Eric Lin

Reputation: 1510

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.

Upvotes: 0

Related Questions