Data Types

Engine types are used when defining columns in table DDLs.
The corresponding JDBC and ODBC types are provided as references for implementing custom drivers or client libraries.

Arrays

An array is declared by appending [] for each dimension to a base type.

Examples

string[]
integer[][]

Type Conversions

Data types can be converted either implicitly (automatically) or explicitly (using conversion functions).

Implicit conversions are applied automatically in expressions and filters to make development more convenient. Explicit conversions, on the other hand, require the use of the CAST keyword or the CONVERT() function.

Key Rules for Type Conversion

• Any type can be implicitly converted to the object type.
• The object type can be explicitly cast to any supported type.
• null can be assigned to any type.
• All implicit conversions are valid as explicit conversions.
• Implicit conversion of literals is permitted if it does not result in loss of information.
• By default, the system property widenComparisonToString is false:
  • If a comparison cannot be safely converted, it will raise an exception.
• If widenComparisonToString is set to true, broader (widening) conversions may be applied automatically in expressions.
  • See the System Properties section in the Administrator’s Guide for more detail.

Example

SELECT * FROM my.table WHERE created_by = 'not a date'

If created_by is a date column, and widenComparisonToString is false, this will raise an error because 'not a date' cannot be converted to a valid date.

Conversion Matrix

Special Conversion Cases

Some conversions in the query engine occur automatically in specific contexts. This section highlights those implicit conversions and how the engine interprets certain literal values.

String Literal Conversions

String literals in SQL are automatically converted to their implied types, especially during comparisons in WHERE clauses.

SELECT * FROM my.table WHERE created_by = '2025-01-02'

If the column created_by is of type date, the string '2025-01-02' will be implicitly converted to a DATE.

Boolean Conversions

Literal strings and numeric values can be automatically interpreted as booleans.

Date and Time Conversions

Strings that match JDBC-compatible formats are implicitly converted to DATE, TIME, or TIMESTAMP types depending on their structure.

Escaped Literal Syntax

Instead of relying on implicit conversions, you can explicitly define typed literals using escape syntax, which is part of the SQL standard. This method avoids ambiguity and makes your intent clear.