Scalar functions name resolution special behavior

  • Anton Revyako
    Anton Revyako

    Founder of dwh.dev

Scalar functions name resolution special behavior

I never get tired of repeating that writing a universal compiler for different SQL dialects is impossible.

Today I will tell you about the behavior of name resolution in Snowflake inside CREATE VIEW.

When you execute queries, Snowflake looks for the objects specified in the query in the schemas specified in SEARCH_PATH. You can view them like this:

select current_schemas();

But it seems like a good idea to make the creation of VIEWs independent of SEARCHPATH. Otherwise, we will get different results when we work with VIEWs at different SEARCHPATH.

The documentation says the following:

The SEARCH_PATH is not used inside views or UDFs. All unqualifed objects in a view or UDF definition will be resolved in the view’s or UDF’s schema only.

That's great! And it works!

CREATE OR REPLACE DATABASE db1;
CREATE SCHEMA sh1;

CREATE TABLE public.t1(c1 int);

CREATE VIEW sh1.v1 AS
SELECT * FROM t1;

will return

SQL compilation error:
Object 'DB1.SH1.T1' does not exist or not authorized.

And not only for tables. For any objects, except … scalar functions!

CREATE OR REPLACE DATABASE db1;
CREATE SCHEMA sh1;

CREATE TABLE sh1.t1(c1 int);
INSERT INTO sh1.t1(c1) VALUES (1);

CREATE FUNCTION public.test()
RETURNS NUMBER
LANGUAGE SQL
AS '1';

CREATE VIEW sh1.v1 AS
SELECT *, test() c2 FROM t1;

select * from sh1.v1;

will return

C1  C2
1   1

Strange behavior, don't you agree?

In PostgreSQL, for example, it works like this: when creating a VIEW, all objects without schema specification are searched in the public schema. If you want a different schema, specify it by hand.

But maybe I'm being picky? Let's add one more thing…

CREATE FUNCTION sh1.test()
RETURNS NUMBER
LANGUAGE SQL
AS '2';

select * from sh1.v1;

will return

C1  C2
1   2

Oops… i.e. if there is a scalar function in the scheme where VIEW is created, it will be used. If not, the function from PUBLIC will be used.

I.e. if you didn't specify a schema for a scalar function from the PUBLIC schema while creating a VIEW, in a schema other than PUBLIC, then to corrupt the data in your database it is enough to create a function with the same name in the corresponding schema….

Very useful, Snowflake!