Values
Primitive Types
String
Character strings encoded in UTF-8.
Integer
64-bit signed integers.
Float
64-bit floating point numbers.
Boolean
Boolean values. true or false.
Ternary
Values of three-valued logic. TRUE, UNKNOWN or FALSE.
Datetime
Values of Date and time with nano seconds.
Null
Representations of missing values.
NULL is not a state indicating that a value does not exist or UNKNOWN, but a value of a different type from UNKNOWN. Empty cells are set to NULL, and UNKNOWN is used as the results of the operator evaluations.
Note that
NULL IS UNKNOWN
evaluates toTRUE
, butUNKNOWN IS NULL
evaluates toFALSE
.
Expressions that can be used as a value
- Field Reference
- Arithmetic Operation
- String Operation
- Function
- Subquery
- Variable
- Variable Substitution
- Environment Variable
- Runtime Information
- Flag
- Parentheses
- Case Expressions
- Comparison Operation
- Logic Operation
- Cursor Status
Field Reference
References to fields in tables.
A field reference is represented by a column name or a table name and a column name joined with a full stop(U+002E). Fields can also be referred with column numbers in each table starting with 1.
field_reference
: column_name
| table_name.column_name
| table_name.column_number
- table_name
- identifier
- column_name
- identifier
- column_number
- integer
Arithmetic Operation
String Operation
Function
- Logical Functions
- Numeric Functions
- DateTime Functions
- String Functions
- Cryptographic Hash Functions
- Cast Functions
- System Functions
- Aggregate Functions
- Analytic Functions
- User Defined Functions
Subquery
Select Query enclosed in parentheses.
A result set of a subquery must have exactly one field and at most one record. If the result set has no record, that subquery returns null.
Variable
Variable Substitution
Environment Variable
Runtime Information
Flag
Parentheses
Values enclosed in parentheses.
Parentheses are used to specify the order of operations.
Case Expressions
Case syntax with condition
CASE
WHEN condition THEN result
[WHEN condition THEN result ...]
[ELSE result]
END
- condition
- value
- result
- value
This syntax returns the result of the first WHEN expression that condition is TRUE. If no condition is TRUE, then returns the result of the ELSE expression or a null if there is no ELSE expression.
Case syntax with comparison
CASE value
WHEN comparison_value THEN result
[WHEN comparison_value THEN result ...]
[ELSE result]
END
- value
- value
- comparison_value
- value
- result
- value
This syntax returns the result of the first WHEN expression that comparison_value is equal to value. If no comparison_value is match, then returns the result of the ELSE expression or a null if there is no ELSE expression.
Comparison Operation
Logic Operation
Cursor Status
Automatic Type Casting
Every Value has a primitive type. A value is converted to another primitive type as necessary. For example, in arithmetic operations, both left-hand side value and right-hand side value are converted to integer or float values. If the conversion fails, then the value is converted to null.
Field values are imported as strings from csv. You can cast value types expressly by using cast functions. This is useful to format output such as numbers in JSON format.
conversion to | value type | description |
---|---|---|
String | Integer | An integer value is converted to a string representing a decimal integer. |
Float | A float value is converted to a string representing a floating-point decimal. | |
Datetime | A datetime value is converted to a null. | |
Boolean | A boolean value is converted to a null. | |
Ternary | A ternaly value is converted to a null. | |
Null | A null value is kept as it is. | |
Integer | String | If a string is a representation of a decimal integer or its exponential notation, then it is converted to an integer. Otherwise it is converted to a null. |
Float | If a float value has no value after the decimal point, then it is converted to an integer. Otherwise it is converted to a null. | |
Datetime | A datetime value is converted to a null. | |
Boolean | A boolean value is converted to a null. | |
Ternary | A ternaly value is converted to a null. | |
Null | A null value is kept as it is. | |
Float | String | If a string is a representation of a floating-point decimal or its exponential notation, then it is converted to a float. Otherwise it is converted to a null. |
Integer | An integer value is converted to a float. | |
Datetime | A datetime value is converted to a null. | |
Boolean | A boolean value is converted to a null. | |
Ternary | A ternary value is converted to a null. | |
Null | A null value is kept as it is. | |
Datetime | String | If a string value is a representation of an integer, or a float value, then it is converted to a datetime represented by the number as a unix time. If a string value is formatted as a datetime, then it is convered to a datetime. Otherwise, it is converted to a null. |
Integer | An integer value is converted to a null. | |
Float | A float value is converted to a null. | |
Boolean | A boolean value is converted to a null. | |
Ternary | A ternaly value is converted to a null. | |
Null | A null value is kept as it is. | |
Boolean | String | If a string value is any of ‘1’, ‘t’, ‘T’, ‘TRUE’, ‘true’ and ‘True’, then it is converted to true. If a string value is any of ‘0’, ‘f’, ‘F’, ‘FALSE’ and ‘false’, then it is converted to false. Otherwise it is converted to a null. |
Integer | If an integer value is 1, then it is converted to true. If an integer value is 0, then it is converted to false. Otherwise it is converted to a null. | |
Float | If a float value is 1, then it is converted to true. If a float value is 0, then it is converted to false. Otherwise it is converted to a null. | |
Datetime | A datetime value is converted to a null. | |
Ternary | If a ternary value is TRUE, then it is converted to true. If a ternary value is FALSE, then it is converted to false. Otherwise it is converted to a null. | |
Null | A null value is kept as it is. | |
Ternary | String | If a string value is any of ‘1’, ‘t’, ‘T’, ‘TRUE’, ‘true’ and ‘True’, then it is converted to TRUE. If a string value is any of ‘0’, ‘f’, ‘F’, ‘FALSE’ and ‘false’, then it is converted to FALSE. Otherwise it is converted to UNKNOWN. |
Integer | If an integer value is 1, then it is converted to TRUE. If an integer value is 0, then it is converted to FALSE. Otherwise it is converted to UNKNOWN. | |
Float | If a float value is 1, then it is converted to TRUE. If a float value is 0, then it is converted to FALSE. Otherwise it is converted to UNKNOWN. | |
Datetime | A datetime value is converted to UNKNOWN. | |
Boolean | If a boolean value is true, then it is converted to TRUE. If a boolean value is false, then it is converted to FALSE. | |
Null | A null value is converted to UNKNOWN. |