SQL Writer Semantics for No Support for PARTITION BY ANY Clause

Example queries valid when the UDF does not support the PARTITION BY ANY clause.

Example 1

SELECT * FROM my_tpf( 
  TABLE( SELECT T.x, T.y FROM T ))

This example shows that the SQL writer does not include PARTITION BY clause. The server uses the partition requested by the UDF and since the UDF does not supports any partitioning requirements, the server executes the query without performing any partitioning.

Example 2

SELECT * FROM my_tpf( 
  TABLE( SELECT T.x, T.y FROM T )
  OVER( NO PARTITION BY ))

In this example, the SQL writer requests the NO PARTITION BY clause as part of the input table query specification. As a result, the server executes the query with no runtime partitioning.

Example 3

SELECT * FROM my_tpf( 
  TABLE( SELECT T.x, T.y FROM T )
  OVER( PARTITION BY T.x))

In this example the UDF does not describe any partitioning requirements. However, the SQL writer requests partitioning by column T.x and as a result the server executes the query by performing partitioning on column T.x.

Example 4

SELECT * FROM my_tpf( 
  TABLE( SELECT T.x, T.y FROM T )
  OVER( PARTITION BY T.y))

In this example, the UDF does not describe any partitioning requirements. However, the SQL writer requests partitioning by column T.y. As a result, the server executes the query by performing partitioning on column T.y.

Example 5

SELECT * FROM my_tpf( 
  TABLE( SELECT T.x, T.y FROM T )
  OVER( PARTITION BY T.y, T.x))

In this example, the UDF does not describe any partitioning requirements. However, the SQL writer requests partitioning by columns T.y and T.x. As a result, the server executes the query by performing partitioning on columns T.y and T.x.

Example 6

SELECT * FROM my_tpf( 
  TABLE( SELECT T.x, T.y FROM T )
  OVER( PARTITION BY ANY ))

In this example, the SQL writer requests PARTITION BY ANY partitioning. However, the UDF does not support any partitioning requirements. As a result, the server executes the query by performing row range partitioning.