Example queries valid for two-column partitioning on column 1 (c1) and column 2 (c2).
SELECT * FROM my_tpf( TABLE( SELECT T.x, T.y FROM T ) OVER( PARTITION BY T.y, T.x ))
In this example, the UDF describes to the server that the data is partitioned by columns T.y and T.x. The SQL writer also requests partitioning on the same column. When the two columns match, the above query proceeds without any errors using this negotiated query:
my_tpf( TABLE( SELECT T.x, T.y FROM T )
OVER ( PARTITION BY T.y, T.x ) )
V4 describe_parameter_get API returns: { 2, 2, 1 }
SELECT * FROM my_tpf( TABLE( SELECT T.x, T.y FROM T ) OVER( PARTITION BY ANY ))
In this example, the SQL writer does not specify a specific column for partitioning. Instead the SQL writer partitions the input table. The UDF requests partitioning on columns T.y and T.x, and as a result, the server partitions the input data on the columns T.y and T.x.
SELECT * FROM my_tpf( TABLE( SELECT T.x, T.y FROM T ) SELECT * FROM my_tpf( TABLE( SELECT T.x, T.y FROM T ) OVER ( PARTITION BY DEFAULT ))
This example shows that the SQL writer does not include PARTITION BY clause or the PARTITION BY DEFAULT clause. The server uses the partition requested by the UDF, and since the UDF describes that it requires partitioning on columns T.y and T.x, the server executes the query by performing partitioning on columns T.y and T.x.
SELECT * FROM my_tpf( TABLE( SELECT T.x, T.y FROM T ) OVER( PARTITION BY T.x,T.y))
This example is semantically identical to Example 1. The ordering of the two columns are different, but within a given partition, the values for columns T.x and T.y stay the same. Both columns (T.x, T.y) and columns (T.y, T.x) result in the same logical partitioning of data.