Tuple Functions
| Function | Syntax | Purpose |
|---|---|---|
| Constructor | tuple<<TYPE1,TYPE2, …, TYPEn>>(e1, e2, …, en) | Construct a tuple given elements. NULL is also supported as an element. |
| Implicit Constructor | tuple(e1, e2, …, en) | Construct a tuple with the specified elements. Types of the tuple are inferred from the inner elements. |
| convert tuple to string | char(tuple) | Converts a tuple to its string representation. |
| convert string to tuple | string_to_tuple(str, format) | Converts the string representation of a tuple (e.g., ‘tuple<<INT,BIGINT,CHAR>>(1,2,NULL)’) into a tuple. |
| tuple casting | cast_to_tuple(tuple, format) | Converts a tuple into another tuple of a different type (e.g., cast_to_tuple(tuple<<int,int>>(1,2), ‘TUPLE(BIGINT,DOUBLE)’)). |
Tuple Function Examples
| Function | SQL statement | Result |
|---|---|---|
| type constructor | tuple<<INT,BIGINT>>(1,2) | tuple holding INT(1) and BIGINT(2) |
| type constructor for array of tuples | tuple<<INT,BIGINT>>[](tuple<<INT,BIGINT>>(1,2), tuple<<INT,BIGINT>>(3,4)) | array holding 2 tuples: [(1,2), (1,2)] |
| char(<tuple value>) | char(tuple<<INT,BIGINT>>(1,2)) | ‘tuple<<INT,BIGINT>>(1,2)‘ |
| string_to_array | string_to_array(‘tuple<<INT,BIGINT>>[tuple<<INT,BIGINT>>(1,2)]’,‘ARRAY(TUPLE(INT,BIGINT))‘) | tuple<<INT,BIGINT>>[tuple<<INT,BIGINT>>(1,2)] |
Tuple Operators
| Operator | Syntax | Description |
|---|---|---|
| access operator | tuple[element_index] | Returns the element stored in the specified index position. The index starts at 1. Access out of bounds returns a NULL. |
| slice operator | tuple[left_index:right_index] | Returns the subtuple ranging from left_index to right_index inclusive. The index starts at 1. |
About the Tuple Slice Operator
- Each index starts at
1. - If the tuple is NULL or either index value is NULL, the result of the slicing is NULL.
- If you omit the left index, it is equivalent to using
1. If you omit the right index, it is equivalent tolen(tuple). If you omit both, it returns the full tuple.tuple<<int,int,int>>(1, 2, 3)[:]is equivalent totuple<<int,int,int>>(1, 2, 3). - Values below zero for the left index convert to
1, and values beyond the tuple length for the right index convert tolen(tuple).tupleVal[-1:4]is equivalent totupleVal[1:4], andtuple<<int,int,int>>(1, 2, 3)[1:6]is equivalent totuple<<int,int,int>>(1, 2, 3)[1:3]. - Ranges completely out of tuple bounds throw an invalid argument error, e.g.,
tuple<<int,int>>(1,2)[4:5]. - Sequential slices slice each dimension of tuples.
tuple<<tuple<<int,int,int>>,tuple<<int,int,int>>>>(tuple<<int,int,int>>(1, 2, 3), tuple<<int,int,int>>(4, 5, 6))[1:1][2:3]=tuple<<tuple<<int,int>>>>(tuple<<int,int>>(2, 3)) - More than N sequential slices of an N-dimensional tuple, for example, three sequential slices on a two-dimensional tuple throw an error.
tuple<<int>>(1)[:][:] - You cannot combine slicing with the access operator. If you attempt such a combination, any access operator
[n]converts to[:n].tupleVal[4][1:6]=tupleVal[:4][1:6]. - Slicing with non-constant indexes throws an error.
- Slicing a tuple of arrays such as
tuple<<int[],double[]>>(int[1,2,3],double[4,5,6])[1:1][2:3]slices[1:1]on the tuple and[2:3]on the resulting inner arrays, resulting intuple<<int[]>>(int[2,3]). - Slicing a tuple with a mix of containers and primitives such as
tuple<<int,int[]>>(1,int[1,2,3])throws an error if, at any given depth, the slice is not valid for one or more elements. Slicing the tuple with[:][2:3]fails because you cannot slice an integer, and the resultant tuple from[:]includes an integer. However, slicing with[2:2][2:3]returnstuple<<int[]>>(int[2,3])because the tuple resulting from the[2:2]slice contains only elements that the next slice range can slice.
Operator Examples
| Operator | SQL Predicate | Result |
|---|---|---|
| access operator | tuple<<CHAR,BIGINT>>(‘SQL’,2)[1] | ‘SQL’ |
| access operator | tuple<<CHAR,BIGINT>>(‘SQL’,2)[2] | NULL |
| slice operator | tuple<<CHAR,BIGINT,BIGINT>>(‘SQL’,1,2)[1:2] | tuple<<CHAR,BIGINT>>(‘SQL’,1) |