Welcome To TechBrothersIT: Snowflake SQL Tutorial 142 - My Comma Join To A Table Function Silently Dropped A Region

Snowflake SQL Tutorial 142 - My Comma Join To A Table Function Silently Dropped A Region

Snowflake SQL · Episode 142

In this post we are going to cover SQL table functions in Snowflake, which are also called UDTFs.

A scalar function gives you one value per row. A table function gives you a whole table per row. You declare the columns it returns, the body is a query rather than an expression, and you call it inside the word TABLE.

And the join that makes one useful is written with a comma, which is the part nobody expects, and which quietly drops rows.

We will start with one function over one table and build it up step by step. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 142 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's write a table function
Now let's see what the catalog keeps
Which column names does the caller get?
The argument types are stricter than you expect
Now the reason table functions exist at all
SQL has a word for this join, so let's try it
And here is what the comma join did to us
What else can a table function do?
Now overloading, which was simple last episode and is not here
FLATTEN is a table function too
And what a table function refuses
One last thing: the teardown

Let's create some sample data

A table of sales, with some regions holding more rows than others. That imbalance matters later.

Sample data

-- Sample data for this post.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE SALES_T142 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

INSERT INTO SALES_T142 (SALE_ID, REGION, AMOUNT) VALUES
    (1, 'north', 100.00), (2, 'south', 250.50), (3, 'east',  75.25),
    (4, 'west',  310.00), (5, 'north',  45.00), (6, 'south',  88.25),
    (7, 'north', 500.00);

Query

SELECT REGION, COUNT(*) AS HOW_MANY, SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T142
GROUP BY REGION
ORDER BY REGION;

Result4 rows · 3 columns

REGION	HOW_MANY	TOTAL_AMOUNT
east	1	75.25
north	3	645.00
south	2	338.75
west	1	310.00

Seven rows across four regions. North has three, south has two, and east and west have one each.

Let's write a table function

RETURNS TABLE, then the columns it gives back with their types, then a body that is a query.

Query

CREATE OR REPLACE FUNCTION SALES_IN_T142(R VARCHAR)
RETURNS TABLE (SALE_ID INT, THE_REGION VARCHAR, THE_AMOUNT NUMBER(10,2))
AS
$$
    SELECT SALE_ID, REGION, AMOUNT
    FROM SALES_T142
    WHERE REGION = R
$$;

Result1 row · 1 column

status
Function SALES_IN_T142 successfully created.

Now let's call it. Notice the word TABLE wrapped around the call.

Query

SELECT SALE_ID, THE_REGION, THE_AMOUNT
FROM TABLE(SALES_IN_T142('north'))
ORDER BY SALE_ID;

Result3 rows · 3 columns

SALE_ID	THE_REGION	THE_AMOUNT
1	north	100.00
5	north	45.00
7	north	500.00

Three rows. The northern ones. Now let's take that word away.

Query

SELECT SALE_ID FROM SALES_IN_T142('north');

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 33 unexpected '('.

001003, a syntax error at the opening bracket. So TABLE is not decoration. Without it the parser does not know what it is looking at.

And what about a region with nothing in it?

Query

SELECT COUNT(*) AS ROWS_IT_RETURNED
FROM TABLE(SALES_IN_T142('nowhere'));

Result1 row · 1 column

ROWS_IT_RETURNED
0

Zero. Not a NULL and not an error. An empty table, which is exactly what a table function should give you when there is nothing to give.

Now let's see what the catalog keeps

SHOW USER FUNCTIONS

SHOW USER FUNCTIONS LIKE '%\\_T142';

Result1 row · 21 columns · scroll →

created_on	name	schema_name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	catalog_name	is_table_function	valid_for_clustering	is_secure	secrets	external_access_integrations	is_external_function	language	is_memoizable	is_data_metric	is_ai_function
2026-10-03 13:53:03.718000-07:00	SALES_IN_T142	PUBLIC	N	N	N	1	1	SALES_IN_T142(VARCHAR) RETURN TABLE (SALE_ID NUMBER, THE_REGION VARCHAR, THE_AMOUNT NUMBER)	user-defined function	DEMO_DB	Y	N	N			N	SQL	N	N	N

Twenty one columns, the same as a scalar function. Two of them are worth finding. is_table_function reads Y, which is how the catalog tells this apart from a scalar one. And arguments holds the whole signature, including RETURN TABLE and every declared column name and type.

The secrets and external_access_integrations cells are empty strings, which the grid marks as empty rather than as NULL.

DESCRIBE FUNCTION

DESCRIBE FUNCTION SALES_IN_T142(VARCHAR);

Result4 rows · 2 columns

property	value
signature	(R VARCHAR)
returns	TABLE (SALE_ID NUMBER, THE_REGION VARCHAR, THE_AMOUNT NUMBER)
language	SQL
body	SELECT SALE_ID, REGION, AMOUNT FROM SALES_T142 WHERE REGION = R

Four property rows: the signature, the return type, the language and the body.

INFORMATION_SCHEMA.FUNCTIONS

SELECT FUNCTION_NAME,
       ARGUMENT_SIGNATURE,
       DATA_TYPE,
       FUNCTION_LANGUAGE
FROM INFORMATION_SCHEMA.FUNCTIONS
WHERE FUNCTION_SCHEMA = 'PUBLIC'
  AND ENDSWITH(FUNCTION_NAME, '_T142')
ORDER BY FUNCTION_NAME;

Result1 row · 4 columns

FUNCTION_NAME	ARGUMENT_SIGNATURE	DATA_TYPE	FUNCTION_LANGUAGE
SALES_IN_T142	(R VARCHAR)	TABLE (SALE_ID NUMBER, THE_REGION VARCHAR, THE_AMOUNT NUMBER)	SQL

And the view puts the whole TABLE (...) declaration in its DATA_TYPE column, which is an odd place for it and a useful one.

Which column names does the caller get?

There are two sets of names in play: the ones declared in the RETURNS TABLE clause, and the aliases written inside the body. Let's make them deliberately different and find out which ones win.

Query

CREATE OR REPLACE FUNCTION NAMES_T142()
RETURNS TABLE (DECLARED_FIRST INT, DECLARED_SECOND VARCHAR)
AS
$$
    SELECT SALE_ID AS ALIASED_IN_THE_BODY,
           REGION  AS ALSO_ALIASED
    FROM SALES_T142
    ORDER BY SALE_ID
$$;

Result1 row · 1 column

status
Function NAMES_T142 successfully created.

Now SELECT * and see which names come out.

Query

SELECT * FROM TABLE(NAMES_T142()) LIMIT 3;

Result3 rows · 2 columns

DECLARED_FIRST	DECLARED_SECOND
1	north
2	south
3	east

The declared ones. Three rows of the table, because that query has a LIMIT 3 on it to keep the grid short.

So asking for the declared names works, as you would expect.

Query

SELECT DECLARED_FIRST, DECLARED_SECOND FROM TABLE(NAMES_T142()) LIMIT 3;

Result3 rows · 2 columns

DECLARED_FIRST	DECLARED_SECOND
1	north
2	south
3	east

And asking for an alias written in the body?

Query

SELECT ALIASED_IN_THE_BODY FROM TABLE(NAMES_T142()) LIMIT 3;

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier 'ALIASED_IN_THE_BODY'

000904, invalid identifier. The body's aliases are not visible to the caller at all. As far as anything outside the function is concerned they do not exist.

One more thing about that function. Its body has an ORDER BY in it, and the call we just made did not. Let's ask for everything it returns.

Query

SELECT DECLARED_FIRST FROM TABLE(NAMES_T142());

Result7 rows · 1 column

DECLARED_FIRST
1
2
3
4
5
6
7

Seven rows, and they came back in order, 1 through 7.

What happened is not what is guaranteed

That is what happened on this run. It is not a promise, and we are not going to teach you to rely on it. If you need an order, write the ORDER BY in the query that reads the function, not in the body.

The argument types are stricter than you expect

Let's write one that takes two arguments, a region and a cutoff declared as NUMBER(10,2).

Query

CREATE OR REPLACE FUNCTION OVER_T142(R VARCHAR, CUTOFF NUMBER(10,2))
RETURNS TABLE (SALE_ID INT, THE_AMOUNT NUMBER(10,2))
AS
$$
    SELECT SALE_ID, AMOUNT
    FROM SALES_T142
    WHERE REGION = R AND AMOUNT > CUTOFF
$$;

Result1 row · 1 column

status
Function OVER_T142 successfully created.

And call it with two literals. Both obviously the right shape.

Query

SELECT SALE_ID, THE_AMOUNT
FROM TABLE(OVER_T142('north', 50.00))
ORDER BY SALE_ID;

Query failed — Snowflake returned an error

001044 (42P13): SQL compilation error: error line 2 at position 11
Invalid argument types for function 'OVER_T142': (VARCHAR(5), NUMBER(2,0))

001044, invalid argument types. Now read the second type it reports.

To Snowflake the literal 50.00 is a number with two digits and no decimals. And that is not NUMBER(10,2).

So let's cast the literal and try again.

Query

SELECT SALE_ID, THE_AMOUNT
FROM TABLE(OVER_T142('north', 50.00::NUMBER(10,2)))
ORDER BY SALE_ID;

Result2 rows · 2 columns

SALE_ID	THE_AMOUNT
1	100.00
7	500.00

Two rows. Exactly what we wanted the first time.

Not the same as last episode

Which is worth sitting with, because in Episode 141 a scalar function took a date into a varchar argument happily. A table function will not widen a numeric literal. Same account, same version, different rules.
If a call fails with invalid argument types and the arguments look right, that is the first thing to check.

Now the reason table functions exist at all

So far we have called the function with a literal, which a plain view could have done. The point of a table function is that each row of another table can supply its own arguments.

Let's make a table of cutoffs, one per region.

A table

CREATE OR REPLACE TABLE CUTOFFS_T142 (
    REGION     VARCHAR(10),
    THE_CUTOFF NUMBER(10,2)
);

Result1 row · 1 column

status
Table CUTOFFS_T142 successfully created.

Four rows

INSERT INTO CUTOFFS_T142 (REGION, THE_CUTOFF)
VALUES ('north', 50.00), ('south', 100.00),
       ('east', 1000.00), ('west', 0.00);

Result1 row · 1 column

number of rows inserted
4

And what it holds

SELECT REGION, THE_CUTOFF FROM CUTOFFS_T142 ORDER BY REGION;

Result4 rows · 2 columns

REGION	THE_CUTOFF
east	1000.00
north	50.00
south	100.00
west	0.00

Four regions with four different cutoffs. East's is a thousand, which nothing in the sales table clears. West's is zero, which everything clears.

And here is the join. A comma, then TABLE, and the function reading columns out of the row to its left.

Query

SELECT c.REGION,
       c.THE_CUTOFF,
       s.SALE_ID,
       s.THE_AMOUNT
FROM CUTOFFS_T142 c,
     TABLE(OVER_T142(c.REGION, c.THE_CUTOFF)) s
ORDER BY c.REGION, s.SALE_ID;

Result4 rows · 4 columns

REGION	THE_CUTOFF	SALE_ID	THE_AMOUNT
north	50.00	1	100.00
north	50.00	7	500.00
south	100.00	2	250.50
west	0.00	4	310.00

Four rows. Each region got its own cutoff and the function ran once per row with those arguments. North appears twice, south once and west once.

That is a correlated table function, and it is the shape you came here for.

SQL has a word for this join, so let's try it

The word is LATERAL.

Query

SELECT c.REGION, COUNT(*) AS HOW_MANY_CLEARED_THE_CUTOFF
FROM CUTOFFS_T142 c,
     LATERAL TABLE(OVER_T142(c.REGION, c.THE_CUTOFF)) s
GROUP BY c.REGION
ORDER BY c.REGION;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 3 at position 13 unexpected 'TABLE'.
syntax error line 3 at position 51 unexpected ')'.

001003, a syntax error at TABLE. So the word LATERAL is refused in front of TABLE on this version.

The same query with the word taken out is fine.

Query

SELECT c.REGION, COUNT(*) AS HOW_MANY_CLEARED_THE_CUTOFF
FROM CUTOFFS_T142 c,
     TABLE(OVER_T142(c.REGION, c.THE_CUTOFF)) s
GROUP BY c.REGION
ORDER BY c.REGION;

Result3 rows · 2 columns

REGION	HOW_MANY_CLEARED_THE_CUTOFF
north	2
south	1
west	1

Three groups. And that number is the next thing to look at, because the cutoff table has four rows in it.

And here is what the comma join did to us

Let's count the regions the comma join kept, and the regions there are.

Regions the comma join kept

SELECT COUNT(DISTINCT c.REGION) AS REGIONS_THE_COMMA_JOIN_KEPT
FROM CUTOFFS_T142 c,
     TABLE(OVER_T142(c.REGION, c.THE_CUTOFF)) s;

Result1 row · 1 column

REGIONS_THE_COMMA_JOIN_KEPT
3

Regions there are

SELECT COUNT(*) AS REGIONS_IN_THE_CUTOFF_TABLE FROM CUTOFFS_T142;

Result1 row · 1 column

REGIONS_IN_THE_CUTOFF_TABLE
4

Three kept out of four. East's cutoff is a thousand, nothing cleared it, so the function returned an empty table for that row and the row vanished with it.

Silently, is the word

A comma join to a table function is an inner join. A row whose function returns nothing is dropped, and nothing in the result says so. You get a shorter answer and no warning.
This is the trap in the title of this post, and it is easy to ship.

So let's try the obvious fix, a left join with LATERAL.

Query

SELECT c.REGION, COUNT(s.SALE_ID) AS HOW_MANY
FROM CUTOFFS_T142 c
LEFT JOIN LATERAL TABLE(OVER_T142(c.REGION, c.THE_CUTOFF)) s ON TRUE
GROUP BY c.REGION
ORDER BY c.REGION;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 3 at position 18 unexpected 'TABLE'.
syntax error line 3 at position 56 unexpected ')'.

Refused as well, with the same error. So that is not the spelling either.

This one is.

Query

SELECT c.REGION, COUNT(s.SALE_ID) AS HOW_MANY
FROM CUTOFFS_T142 c
LEFT OUTER JOIN TABLE(OVER_T142(c.REGION, c.THE_CUTOFF)) s ON TRUE
GROUP BY c.REGION
ORDER BY c.REGION;

Result4 rows · 2 columns

REGION	HOW_MANY
east	0
north	2
south	1
west	1

LEFT OUTER JOIN TABLE(...) ON TRUE, and there are four rows now. East is back, with a zero against it, which is the answer we wanted all along.

One last thing about the join. The arguments have to come from a table to the left. Let's put the cutoffs on the right instead.

Query

SELECT c.REGION, s.SALE_ID
FROM TABLE(OVER_T142(c.REGION, c.THE_CUTOFF)) s,
     CUTOFFS_T142 c;

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 2 at position 21
invalid identifier 'C.REGION'

000904, invalid identifier. It cannot see what has not been read yet.

What else can a table function do?

Four things, quickly. It can take no arguments at all.

Query

CREATE OR REPLACE FUNCTION EVERYTHING_T142()
RETURNS TABLE (SALE_ID INT, THE_AMOUNT NUMBER(10,2))
AS
$$
    SELECT SALE_ID, AMOUNT FROM SALES_T142
$$;

Result1 row · 1 column

status
Function EVERYTHING_T142 successfully created.

And calling it

SELECT COUNT(*) AS ROWS_IT_RETURNED FROM TABLE(EVERYTHING_T142());

Result1 row · 1 column

ROWS_IT_RETURNED
7

Seven rows, the whole table, out of a function with empty brackets.

Its body can call a scalar function.

A scalar one

CREATE OR REPLACE FUNCTION WITH_TAX_T142(AMOUNT NUMBER(10,2))
RETURNS NUMBER(12,4)
AS
$$
    AMOUNT * 1.20
$$;

Result1 row · 1 column

status
Function WITH_TAX_T142 successfully created.

And a table one that uses it

CREATE OR REPLACE FUNCTION TAXED_IN_T142(R VARCHAR)
RETURNS TABLE (SALE_ID INT, THE_AMOUNT NUMBER(10,2), TAXED NUMBER(12,4))
AS
$$
    SELECT SALE_ID, AMOUNT, WITH_TAX_T142(AMOUNT)
    FROM SALES_T142
    WHERE REGION = R
$$;

Result1 row · 1 column

status
Function TAXED_IN_T142 successfully created.

And what it gives

SELECT SALE_ID, THE_AMOUNT, TAXED
FROM TABLE(TAXED_IN_T142('south'))
ORDER BY SALE_ID;

Result2 rows · 3 columns

SALE_ID	THE_AMOUNT	TAXED
2	250.50	300.6000
6	88.25	105.9000

Two rows, with the scalar function's arithmetic in the third column. The scalar one ran inside the table one.

Its body can read another table function.

Query

CREATE OR REPLACE FUNCTION NESTED_T142(R VARCHAR)
RETURNS TABLE (HOW_MANY NUMBER)
AS
$$
    SELECT COUNT(*) FROM TABLE(SALES_IN_T142(R))
$$;

Result1 row · 1 column

status
Function NESTED_T142 successfully created.

And whether that works

SELECT HOW_MANY FROM TABLE(NESTED_T142('north'));

Result1 row · 1 column

HOW_MANY
3

Three, which is the number of northern rows. Nesting works in both directions.

And it can back a view, with the argument fixed inside the view.

Query

CREATE OR REPLACE VIEW NORTH_VIEW_T142 AS
SELECT SALE_ID, THE_AMOUNT FROM TABLE(SALES_IN_T142('north'));

Result1 row · 1 column

status
View NORTH_VIEW_T142 successfully created.

And what it shows

SELECT SALE_ID, THE_AMOUNT FROM NORTH_VIEW_T142 ORDER BY SALE_ID;

Result3 rows · 2 columns

SALE_ID	THE_AMOUNT
1	100.00
5	45.00
7	500.00

Three rows, and it behaves like any other view.

Now overloading, which was simple last episode and is not here

Let's add a second function with the same name, taking a number instead of a string.

Query

CREATE OR REPLACE FUNCTION SALES_IN_T142(MIN_AMOUNT NUMBER(10,2))
RETURNS TABLE (SALE_ID INT, THE_REGION VARCHAR, THE_AMOUNT NUMBER(10,2))
AS
$$
    SELECT SALE_ID, REGION, AMOUNT
    FROM SALES_T142
    WHERE AMOUNT >= MIN_AMOUNT
$$;

Result1 row · 1 column

status
Function SALES_IN_T142 successfully created.

Created. Give it a string and the string one answers.

Query

SELECT COUNT(*) AS ROWS_FOR_A_STRING FROM TABLE(SALES_IN_T142('north'));

Result1 row · 1 column

ROWS_FOR_A_STRING
3

Three rows. Now give it a plain number.

Query

SELECT COUNT(*) AS ROWS_FOR_A_NUMBER FROM TABLE(SALES_IN_T142(100.00));

Query failed — Snowflake returned an error

001044 (42P13): SQL compilation error: error line 1 at position 48
Invalid argument types for function 'SALES_IN_T142': (NUMBER(3,0))

001044 again. So even with an overload sitting right there that takes a number, the literal is not widened to match it. Overload resolution does not rescue you from the strictness we met earlier.

Cast it, and it finds the number one.

Query

SELECT COUNT(*) AS ROWS_FOR_A_NUMBER
FROM TABLE(SALES_IN_T142(100.00::NUMBER(10,2)));

Result1 row · 1 column

ROWS_FOR_A_NUMBER
4

Four rows, which is every sale of a hundred or more. And the catalog does list both of them.

SHOW USER FUNCTIONS

SHOW USER FUNCTIONS LIKE 'SALES\\_IN\\_T142';

Result2 rows · 21 columns · scroll →

created_on	name	schema_name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	catalog_name	is_table_function	valid_for_clustering	is_secure	secrets	external_access_integrations	is_external_function	language	is_memoizable	is_data_metric	is_ai_function
2026-10-03 13:53:12.137000-07:00	SALES_IN_T142	PUBLIC	N	N	N	1	1	SALES_IN_T142(NUMBER) RETURN TABLE (SALE_ID NUMBER, THE_REGION VARCHAR, THE_AMOUNT NUMBER)	user-defined function	DEMO_DB	Y	N	N			N	SQL	N	N	N
2026-10-03 13:53:03.718000-07:00	SALES_IN_T142	PUBLIC	N	N	N	1	1	SALES_IN_T142(VARCHAR) RETURN TABLE (SALE_ID NUMBER, THE_REGION VARCHAR, THE_AMOUNT NUMBER)	user-defined function	DEMO_DB	Y	N	N			N	SQL	N	N	N

Two entries, one name, different argument strings.

FLATTEN is a table function too

You have used one of these already without calling it that. Let's set up some JSON.

A table

CREATE OR REPLACE TABLE DOCS_T142 (
    DOC_ID INT,
    BODY   VARIANT
);

Result1 row · 1 column

status
Table DOCS_T142 successfully created.

Two documents

INSERT INTO DOCS_T142 (DOC_ID, BODY)
SELECT 1, PARSE_JSON('{"items":[10,20,30]}')
UNION ALL
SELECT 2, PARSE_JSON('{"items":[]}');

Result1 row · 1 column

number of rows inserted
2

One document with three items, one with an empty array. Now let's join it the same way, with a comma, and look at the word in front of FLATTEN.

Query

SELECT d.DOC_ID, f.VALUE AS THE_ITEM
FROM DOCS_T142 d,
     LATERAL FLATTEN(INPUT => d.BODY:items) f
ORDER BY d.DOC_ID, THE_ITEM;

Result3 rows · 2 columns

DOC_ID	THE_ITEM
1	10
1	20
1	30

LATERAL. The word that was refused in front of TABLE is accepted in front of FLATTEN.

Two spellings, one word

That is an inconsistency to carry around, not a rule to work out. LATERAL FLATTEN is the spelling everybody writes, and it works. LATERAL TABLE looks like its twin and does not.

Three rows, and all three belong to the first document. So let's count the documents that survived.

Query

SELECT COUNT(DISTINCT DOC_ID) AS DOCUMENTS_THE_LATERAL_KEPT
FROM DOCS_T142 d,
     LATERAL FLATTEN(INPUT => d.BODY:items) f;

Result1 row · 1 column

DOCUMENTS_THE_LATERAL_KEPT
1

One, out of two. The document with the empty array produced nothing and was dropped. Exactly the same trap as the comma join to our own function.

But FLATTEN has an answer built in.

Query

SELECT d.DOC_ID, f.VALUE AS THE_ITEM
FROM DOCS_T142 d,
     LATERAL FLATTEN(INPUT => d.BODY:items, OUTER => TRUE) f
ORDER BY d.DOC_ID, THE_ITEM;

Result4 rows · 2 columns

DOC_ID	THE_ITEM
1	10
1	20
1	30
2	NULL

Four rows now, and the second document is back with a real NULL in its item cell. That is OUTER => TRUE doing the job that LEFT OUTER JOIN ... ON TRUE did for our own function.

And what a table function refuses

First, one that catches everybody once. A table function in a select list.

Query

SELECT SALES_IN_T142('north') AS IN_A_SELECT_LIST;

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function SALES_IN_T142.

002139, unknown function. Which is a confusing message, because it plainly does exist. It is just not a scalar function, and in a select list a scalar function is the only kind Snowflake looks for.

A WHERE clause gives the same thing.

Query

SELECT SALE_ID FROM SALES_T142 WHERE SALES_IN_T142('north') > 0;

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function SALES_IN_T142.

And one that genuinely does not exist gives a different code.

Query

SELECT SALE_ID FROM TABLE(NO_SUCH_UDTF_T142('north'));

Query failed — Snowflake returned an error

002142 (42601): SQL compilation error:
Unknown table function NO_SUCH_UDTF_T142

002142, unknown table function. So the two messages are worth telling apart. 002139 inside a TABLE(...) means you have the wrong kind of function. 002142 means you have the wrong name.

Now the declaration. Three columns declared over a two column body.

Query

CREATE OR REPLACE FUNCTION WRONGCOLS_T142()
RETURNS TABLE (A INT, B INT, C INT)
AS
$$
    SELECT SALE_ID, AMOUNT FROM SALES_T142
$$;

Query failed — Snowflake returned an error

090212 (42601): Mismatch between declared return signature column count (3) and actual column count (2)

090212, and it names both counts. And declaring no columns at all gives the same code.

Query

CREATE OR REPLACE FUNCTION NOCOLS_T142()
RETURNS TABLE ()
AS
$$
    SELECT SALE_ID FROM SALES_T142
$$;

Query failed — Snowflake returned an error

090212 (42601): Mismatch between declared return signature column count (0) and actual column count (1)

Caught at create time, both of them. And a body that is an INSERT is refused the same way a scalar body was.

Query

CREATE OR REPLACE FUNCTION ANINSERT_T142()
RETURNS TABLE (A INT)
AS
$$
    INSERT INTO SALES_T142 (SALE_ID) VALUES (99)
$$;

Query failed — Snowflake returned an error

090203 (42601): Compilation of SQL UDF failed: SQL compilation error:
syntax error line 2 at position 11 unexpected 'INTO'.
syntax error line 2 at position 28 unexpected 'SALE_ID'.
syntax error line 2 at position 37 unexpected 'VALUES'.

090203. A body is a query, not a statement.

And the wrong number of arguments comes back as the same code as the wrong type.

Query

SELECT SALE_ID FROM TABLE(OVER_T142('north'));

Query failed — Snowflake returned an error

001044 (42P13): SQL compilation error: error line 1 at position 26
Invalid argument types for function 'OVER_T142': (VARCHAR(5))

001044 again, reporting the types it was actually handed.

One last thing: the teardown

Teardown

DROP VIEW IF EXISTS NORTH_VIEW_T142;

Result1 row · 1 column

status
NORTH_VIEW_T142 successfully dropped.

Teardown, one overload

DROP FUNCTION IF EXISTS SALES_IN_T142(VARCHAR);

Result1 row · 1 column

status
SALES_IN_T142 successfully dropped.

Teardown, the other overload

DROP FUNCTION IF EXISTS SALES_IN_T142(NUMBER(10,2));

Result1 row · 1 column

status
SALES_IN_T142 successfully dropped.

Two drops for one name, by signature, because there are two functions.

Teardown, for one that was never created

DROP FUNCTION IF EXISTS WRONGCOLS_T142();

Result1 row · 1 column

status
Drop statement executed successfully (WRONGCOLS_T142 already dropped).

That message reads differently because the function with three declared columns was refused at create time. There was nothing to drop.

Teardown

DROP TABLE IF EXISTS SALES_T142;

Result1 row · 1 column

status
SALES_T142 successfully dropped.

And the checks.

Where we are

SELECT CURRENT_DATABASE() AS THE_DATABASE,
       CURRENT_SCHEMA()   AS THE_SCHEMA;

Result1 row · 2 columns

THE_DATABASE	THE_SCHEMA
DEMO_DB	PUBLIC

And what is left

SHOW USER FUNCTIONS LIKE '%\\_T142';

Result0 rows · 21 columns · scroll →

created_on	name	schema_name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	catalog_name	is_table_function	valid_for_clustering	is_secure	secrets	external_access_integrations	is_external_function	language	is_memoizable	is_data_metric	is_ai_function

And the tables

SELECT COUNT(*) AS PROBE_TABLES_LEFT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND ENDSWITH(TABLE_NAME, '_T142');

Result1 row · 1 column

PROBE_TABLES_LEFT
0

Still in PUBLIC, no functions of ours left and no tables of ours left. Back where we started.

Key points

A table function declares the columns it returns with RETURNS TABLE, and its body is a query rather than an expression.
The call must be wrapped in TABLE(...). Without it you get 001003.
A region with no rows gives you an empty table, not a NULL and not an error.
The declared column names win. The body's aliases are invisible to the caller, and asking for one is 000904.
An ORDER BY inside the body is not a promise about the order you get out.
Argument types are strict. The literal 50.00 is a two digit number with no decimals, and that is not NUMBER(10,2).
The point of a table function is correlation: FROM t, TABLE(f(t.col)) runs it once per row with that row's values.
LATERAL is refused in front of TABLE on this version. The comma on its own is the spelling that works.
A comma join to a table function is an inner join and drops rows silently. Three regions came back out of four.
LEFT OUTER JOIN TABLE(...) ON TRUE keeps them, and gives the missing one a zero.
The arguments must come from a table to the left. To the right, they cannot be seen.
A table function can take no arguments, call a scalar function, call another table function and back a view.
Overload resolution does not widen an uncast numeric literal. It is 001044 even with a numeric overload in place.
FLATTEN is one of these, it does take LATERAL, and OUTER => TRUE is its answer to the dropped row.
A table function in a select list is 002139, unknown function. One that really does not exist is 002142, unknown table function.
A column count mismatch is 090212, at create time.

Next in this series

Episode 143 — JavaScript UDFs. A body with loops, branches and a try/catch in it, what Snowflake does to your argument's name before any of that runs, and what happens to a large integer on the way through.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The last three grids show the session back in PUBLIC with nothing of its own left behind. Every count in the text is read off one of the grids on this page, including the regions the comma join kept. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake SQL table function documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 142 - My Comma Join To A Table Function Silently Dropped A Region

Let's create some sample data

Let's write a table function

Now let's see what the catalog keeps

Which column names does the caller get?

The argument types are stricter than you expect

Now the reason table functions exist at all

SQL has a word for this join, so let's try it

And here is what the comma join did to us

What else can a table function do?

Now overloading, which was simple last episode and is not here

FLATTEN is a table function too

And what a table function refuses

One last thing: the teardown

Key points

No comments:

Post a Comment