2

I have this view in Snowflake:

create or replace view foo as 
select $1 as id_foo, $2 as sales
  from (values (1,100),(2,200),(3,300));

And this user-defined table function:

CREATE OR REPLACE FUNCTION build_aux_table ( restriction number )
  RETURNS TABLE ( aux_id number )
  AS 'select $1 as aux_id from (VALUES (1),(2),(3)) where aux_id = restriction';

The following query works, and returns 3 rows:

 select id_foo, baz.aux_id
  from foo
 cross join table(build_aux_table(foo.id_foo)) baz;

However, I didn't expect the query to compile, because the UDTF-generated table with which we are joining depends on a column from the first table. My understanding was that this sort of intra-table dependency required a LATERAL join, like the following (which also works):

select id_foo, baz.aux_id
  from foo
 cross join lateral build_aux_table(foo.id_foo) baz;

Why does the query without LATERAL work?

danidiaz
  • 26,936
  • 4
  • 45
  • 95
  • 1
    Nice spot. I would expect same behaviour: [Table Functions](https://docs.snowflake.com/en/sql-reference/functions-table.html) `"Table functions can also be applied to a set of rows using the LATERAL construct."` – Lukasz Szozda Apr 24 '21 at 11:05
  • 1
    @LukaszSzozda Curiously, the [FLATTEN](https://docs.snowflake.com/en/sql-reference/functions/flatten.html) keyword is described as a table function, and yet it does require FLATTEN. – danidiaz Apr 24 '21 at 11:24

2 Answers2

2

The code:

select id_foo, baz.aux_id
from foo
cross join table(build_aux_table(foo.id_foo)) baz;

is basically the same as:

select id_foo, baz.aux_id
from foo
,table(build_aux_table(foo.id_foo)) baz;

It is described in docs:

Calling JavaScript UDTFs in Queries

This simple example shows how to call a UDTF. This example passes literal values.

SELECT * FROM TABLE(js_udtf(10.0::FLOAT, 20.0::FLOAT));

This example calls a UDTF and passes it values from another table. In this example, the UDTF named js_udtf is called once for each row in the table named tab1. Each time that the function is called, it is passed values from columns c1 and c2 of the current row.

SELECT * FROM tab1, TABLE(js_udtf(tab1.c1, tab1.c2)) ;

Sample UDTFs - Examples with joins

Use the UDTF in a join with another table; note that the join column from the table is passed as an argument to the function.

select * 
from favorite_years y join table(favorite_colors(y.year)) c;
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

It's not mentioned explicitly in the Snowflake manual (at least where you linked to), but it's standard behavior that the LATERAL keyword is optional for functions. Quoting the PostgreSQL manual:

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case.

Bold emphasis mine.

Makes sense too, IMHO. Where would a function get its arguments, if not from other tables? Would be restricted to manual input without lateral references.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    One alternative source of arguments could be side-effects, like getting the current timestamp or generating some random value (but perhaps views could work just as well there). – danidiaz Apr 24 '21 at 11:25
  • 1
    @danidiaz: Useful applications. But where would a *reference* point to, if not to lateral tables (table expressions) in the `FROM` clause? – Erwin Brandstetter Apr 24 '21 at 11:33