Values

Primitive Types
Expressions that can be used as a value
Automatic Type Casting

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 nanoseconds.

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 to TRUE, but UNKNOWN IS NULL evaluates to FALSE.

Expressions that can be used as a value

Field Reference
Arithmetic Operation
String Operation
Function
Subquery
Variable
Variable Substitution
Environment Variable
Runtime Information
System Defined Constant
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

Arithmetic Operators

String Operation

String Operators

Function

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

System Defined Constant

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

Comparison Operators

Logic Operation

Logic Operators

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, and the result is slightly different from an automatic conversion.

Conversion to	Type	Value	Value after conversion
String	Integer		String representing the decimal integer
	Float		String representing the floating-point decimal
	Datetime		Null
	Boolean		Null
	Ternary		Null
	Null		Null
Integer	String	Representation of a decimal integer	Integer represented by the string
		Other values	Null
	Float		Null
	Datetime		Null
	Boolean		Null
	Ternary		Null
	Null		Null
Float	String	Representation of a floating-point decimal or its exponential notation	Float represented by the string
		‘Inf’, ‘+Inf’	+Inf
		‘-Inf’	-Inf
		‘NaN’	NaN
		Other values	Null
	Integer		Float equivalent to the integer
	Datetime		Null
	Boolean		Null
	Ternary		Null
	Null		Null
Datetime	String	Datetime Formats	Datetime represented by the string
		Other values	Null
	Integer		Null
	Float		Null
	Boolean		Null
	Ternary		Null
	Null		Null
Boolean	String	‘1’, ‘t’, ‘true’	true
		‘0’, ‘f’, ‘false’	false
		Other values	Null
	Integer	1	true
		0	false
		Other values	Null
	Float	1	true
		0	false
		Other values	Null
	Datetime		Null
	Ternary	TRUE	true
		FALSE	false
		UNKNOWN	Null
	Null		Null
Ternary	String	‘1’, ‘t’, ‘true’	TRUE
		‘0’, ‘f’, ‘false’	FALSE
		Other values	UNKNOWN
	Integer	1	TRUE
		0	FALSE
		Other values	UNKNOWN
	Float	1	TRUE
		0	FALSE
		Other values	UNKNOWN
	Datetime		UNKNOWN
	Boolean	true	TRUE
		false	FALSE
	Null		UNKNOWN

String formats representing Datetime