Table Functions (Except SQL UDTFs): Restrictions With Lateral Table Functions and Outer Lateral Joins¶

In the current release, the following are no longer be allowed:

• Lateral table functions (other than SQL UDTFs) that are specified with the ON clause.

• Outer lateral joins to table functions (other than SQL UDTFs) that specify the ON clause.

Note that this restriction also applies to statements that use clauses equivalent to the ON clause (the USING clause and NATURAL JOIN).

Table functions other than SQL UDTFs include built-in table functions and the UDTFs defined in languages other than SQL.

Previously:

You could specify:

• The ON clause (or the USING clause or NATURAL JOIN) for lateral table functions (other than SQL UDTFs).

  For example:

  SELECT ... FROM my_table
  JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;
  SELECT ... FROM my_table
  INNER JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;
  SELECT ... FROM my_table
  JOIN TABLE(my_js_udtf(col_a))
  ON ...;
  SELECT ... FROM my_table
  INNER JOIN TABLE(my_js_udtf(col_a))
  ON ... ;
  

  Copy

• An outer lateral join to a table function (other than SQL UDTFs), using the ON clause (or the USING clause or NATURAL JOIN).

  For example:

  SELECT ... FROM my_table
  LEFT JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;
  SELECT ... FROM my_table
  FULL JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;
  SELECT ... FROM my_table
  LEFT JOIN TABLE(my_js_udtf(col_a))
  ON ... ;
  SELECT ... FROM my_table
  FULL JOIN TABLE(my_js_udtf(col_a))
  ON ... ;
  

  Copy

Currently:

When you execute the statements above, an error occurs with the following message:

000002 (0A000): Unsupported feature 'lateral table function called with
    OUTER JOIN syntax or a join predicate (ON clause)'

Copy

This restriction does not apply if you are using a comma to specify the join:

SELECT ... FROM <table>,
    TABLE(<ptable_function_other_than_sql_udtf>) ... ;

For example:

SELECT ... FROM my_table,
TABLE(FLATTEN(input=>[col_a]));

This restriction also does not apply if the ON clause (or the USING clause or NATURAL JOIN) is not specified:

SELECT ... FROM <table>
[{ [ INNER  | { LEFT | RIGHT | FULL } [ OUTER ] | CROSS } JOIN
TABLE(<table_function_other_than_sql_udtf>) ...;

For example:

SELECT ... FROM my_table
FULL JOIN TABLE(FLATTEN(input=>[col_a]));
SELECT ... FROM my_table
LEFT JOIN OUTER TABLE(FLATTEN(input=>[col_a]));

This restriction was added because these types of queries can result in inconsistent behavior. These types of queries are not supported.

Ref: 1057