Tuple Functions and Operators
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 given 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)')). |
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)] |
Operator | Syntax | Description |
|---|---|---|
access operator | tuple[element_index] | Returns the element stored in the given index position. 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. Index starts at 1. |
- 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 to len(tuple). If you omit both, it returns the full tuple. tuple<<int,int,int>>(1, 2, 3)[:] is equivalent to tuple<<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 to len(tuple). tupleVal[-1:4] is equivalent to tupleVal[1:4], and tuple<<int,int,int>>(1, 2, 3)[1:6] is equivalent to tuple<<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 indixes 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 in tuple<<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] returns tuple<<int[]>>(int[2,3]), because the tuple resulting from the [2:2] slice contains only elements that can be sliced by the next slice range.
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) |
Data Types
Query Ocient