CAST

The CAST operator converts a value from one data type to another. CAST supports explicit type conversions according to SQL standard semantics.

Syntax

CAST(expression AS target_type)

Parameters

  • expression

    The value or expression to be converted.

  • target_type

    The target data type. Can be a primitive type (INTEGER, BIGINT, FLOAT, DOUBLE, STRING, BOOLEAN) or an array type (e.g., INTEGER ARRAY, STRING ARRAY).

Supported Conversions

Numeric Conversions

  • Integer types: INTEGER ↔ BIGINT

  • Floating-point types: FLOAT ↔ DOUBLE

  • Mixed numeric: INTEGER/BIGINT ↔ FLOAT/DOUBLE

  • Narrowing conversions (e.g., BIGINT → INTEGER, DOUBLE → FLOAT) validate range and throw errors on overflow

  • Floating-point to INTEGER conversions use rounding (Math.round)

String Conversions

  • Any primitive type can be converted to STRING

  • STRING can be converted to numeric types (INTEGER, BIGINT, FLOAT, DOUBLE) with validation

  • Invalid string-to-numeric conversions throw errors

Boolean Conversions

  • BOOLEAN → INTEGER: true = 1, false = 0

  • INTEGER → BOOLEAN: 0 = false, non-zero = true

  • STRING → BOOLEAN: accepts “true”/”1” → true, “false”/”0” → false (case-insensitive)

Array Conversions

  • Arrays can be cast between compatible element types

  • Element type conversion rules follow the same rules as scalar conversions

  • Empty arrays can be cast to any array type

  • Invalid element conversions cause the entire operation to fail

SQL Standard Compatibility

This implementation follows SQL standard CAST semantics with the following characteristics:

  • Explicit type conversion (unlike implicit promotion)

  • Runtime validation with error reporting for invalid conversions

  • Range checking for narrowing conversions

  • NULL propagation: CAST(NULL AS any_type) returns NULL

Empty Array Handling: The system requires explicit CAST for empty array literals. An empty array literal [] without CAST is invalid and must be written as CAST([] AS type ARRAY) to specify the target element type. This ensures type safety and prevents ambiguity in array operations.

Examples

Basic Numeric Conversions

Convert INTEGER to different numeric types:

CREATE TABLE numbers(id BIGINT, value INTEGER, PRIMARY KEY(id))
INSERT INTO numbers VALUES (1, 42)

SELECT CAST(value AS DOUBLE) AS value_as_double FROM numbers WHERE id = 1

Result:

value_as_double

42.0

String to Numeric Conversion

Parse numeric strings:

CREATE TABLE data(id BIGINT, str_value STRING, PRIMARY KEY(id))
INSERT INTO data VALUES (1, '123')

SELECT CAST(str_value AS INTEGER) AS parsed_number FROM data WHERE id = 1

Result:

parsed_number

123

Numeric to String Conversion

Convert numbers to strings:

CREATE TABLE numbers(id BIGINT, value INTEGER, PRIMARY KEY(id))
INSERT INTO numbers VALUES (1, 42)

SELECT CAST(value AS STRING) AS value_as_string FROM numbers WHERE id = 1

Result:

value_as_string

"42"

Boolean Conversions

Convert between boolean and INTEGER:

CREATE TABLE flags(id BIGINT, active BOOLEAN, PRIMARY KEY(id))
INSERT INTO flags VALUES (1, true), (2, false)

SELECT CAST(active AS INTEGER) AS active_as_int FROM flags

Result:

active_as_int

1

0

Array Type Conversion

Convert arrays between element types:

CREATE TABLE arrays(id BIGINT, PRIMARY KEY(id))
INSERT INTO arrays VALUES (1)

SELECT CAST([1, 2, 3] AS STRING ARRAY) AS string_array FROM arrays WHERE id = 1

Result:

string_array

["1", "2", "3"]

Empty Array Casting

Empty arrays must specify target type:

CREATE TABLE arrays(id BIGINT, PRIMARY KEY(id))
INSERT INTO arrays VALUES (1)

SELECT CAST([] AS INTEGER ARRAY) AS empty_int_array FROM arrays WHERE id = 1

Result:

empty_int_array

[]

Nested Conversions

Combine multiple CAST operations:

CREATE TABLE numbers(id BIGINT, value INTEGER, PRIMARY KEY(id))
INSERT INTO numbers VALUES (1, 42)

SELECT CAST(CAST(value AS STRING) AS DOUBLE) AS nested_cast FROM numbers WHERE id = 1

Result:

nested_cast

42.0

Error Handling

CAST operations that fail will raise a INVALID_CAST error (error code 22F3H). This includes:

  • Invalid string-to-numeric conversions

  • Range overflow in narrowing conversions

  • Invalid boolean string values

  • Incompatible type conversions

  • NULL array element types

Invalid Conversions

String values that cannot be parsed as numbers result in errors:

CREATE TABLE data(id BIGINT, str_value STRING, PRIMARY KEY(id))
INSERT INTO data VALUES (1, 'invalid')

SELECT CAST(str_value AS INTEGER) FROM data WHERE id = 1
-- Error: Cannot cast string 'invalid' to INT
-- Error Code: 22F3H (INVALID_CAST)

Range Overflow

Narrowing conversions that exceed target type range result in errors:

CREATE TABLE numbers(id BIGINT, PRIMARY KEY(id))
INSERT INTO numbers VALUES (1)

SELECT CAST(9223372036854775807 AS INTEGER) FROM numbers WHERE id = 1
-- Error: Value out of range for INT
-- Error Code: 22F3H (INVALID_CAST)