SQL-like query language for csv

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

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

Variable Substitution

Variable Substitution

Environment Variable

Environment Variable

Runtime Information

Runtime Information

System Defined Constant

System Defined Constant

Flag

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

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](/csvq/reference/cast-functions.html#format-of-string-as-datetime)