Kubling Documentation

Aggregate Functions

Aggregate functions operate on sets of values from a group produced by an explicit or implicit GROUP BY clause, returning a single scalar value computed from the group.

`COUNT(*)`

Counts the number of values (including nulls and duplicates) in a group. Returns an integer.

SELECT COUNT(*) AS total_nodes FROM NODE;

Expected output:

| total_nodes |
|-------------|
| 56          |

`COUNT(x)`

Counts the number of non-null values in a group. Returns an integer.

SELECT COUNT(status__phase) AS known_phases FROM NODE;

Expected output:

| known_phases |
|--------------|
| 45           |

`COUNT_BIG(*)`

Counts the number of values (including nulls and duplicates) in a group. Returns a long. This is useful when you expect a very large number of rows.

SELECT COUNT_BIG(*) AS total_pods FROM POD;

Expected output:

| total_pods |
|------------|
| 120000000  |

`COUNT_BIG(x)`

Counts the number of non-null values in a group. Returns a long.

SELECT COUNT_BIG(metadata__name) AS named_pods FROM POD;

`SUM(x)`

Calculates the sum of the values (excluding nulls) in a group. Useful for numeric columns.

SELECT 
  SUM(CAST(jsonPathAsString(status__capacity, '$.cpu') AS integer)) AS total_cpu_capacity
FROM NODE;

`AVG(x)`

Calculates the average of the values (excluding nulls) in a group.

SELECT 
  AVG(CAST(jsonPathAsString(status__capacity, '$.memory') AS integer)) AS avg_memory_capacity
FROM NODE;

`MIN(x)`

Finds the minimum value in a group (excluding nulls).

SELECT 
  MIN(CAST(jsonPathAsString(status__capacity, '$.memory') AS integer)) AS min_memory_capacity
FROM NODE;

`MAX(x)`

Finds the maximum value in a group (excluding nulls).

SELECT 
  MAX(CAST(jsonPathAsString(status__capacity, '$.memory') AS integer)) AS max_memory_capacity
FROM NODE;

`ANY(x)` / `SOME(x)`

Returns TRUE if any value in the group is TRUE (excluding null).

SELECT 
  ANY(spec__unschedulable) AS any_unschedulable
FROM NODE;

`EVERY(x)`

Returns TRUE if every value in the group is TRUE (excluding null).

SELECT 
  EVERY(spec__unschedulable) AS all_unschedulable
FROM NODE;

`VAR_POP(x)`

Calculates the population variance of the values in a group, excluding nulls.

SELECT 
  VAR_POP(CAST(jsonPathAsString(status__capacity, '$.memory') AS integer)) AS memory_variance
FROM NODE;

`VAR_SAMP(x)`

Calculates the sample variance of the values in a group, excluding nulls.

SELECT 
  VAR_SAMP(CAST(jsonPathAsString(status__capacity, '$.memory') AS integer)) AS memory_sample_variance
FROM NODE;

`STDDEV_POP(x)`

Calculates the population standard deviation of the values in a group, excluding nulls.

SELECT 
  STDDEV_POP(CAST(jsonPathAsString(status__capacity, '$.memory') AS integer)) AS memory_std_dev
FROM NODE;

`STDDEV_SAMP(x)`

Calculates the sample standard deviation of the values in a group, excluding nulls.

SELECT 
  STDDEV_SAMP(CAST(jsonPathAsString(status__capacity, '$.memory') AS integer)) AS memory_sample_std_dev
FROM NODE;

Window Functions

Kubling provides ANSI SQL 2003 window functions, allowing aggregate functions to be applied to subsets of the result set without requiring a GROUP BY clause. Window functions are similar to aggregate functions but require the use of an OVER clause or a window specification.

Usage

aggregate [FILTER (WHERE ...)] OVER ( [PARTITION BY ...] [ORDER BY ...] [frame] )
| FIRST_VALUE(val) OVER ( [PARTITION BY ...] [ORDER BY ...] [frame] )
| LAST_VALUE(val) OVER ( [PARTITION BY ...] [ORDER BY ...] [frame] )
| analytical OVER ( [PARTITION BY ...] [ORDER BY ...] )

Where:

Partition Clause: Divides the result set into partitions, each treated independently.
Frame Clause: Defines the subset of the partition for which the window function is calculated.

Partition Clause:

PARTITION BY expression [, expression]*

Frame Clause:

RANGE | ROWS frameBound 
| BETWEEN frameBound AND frameBound

Frame Bound Options:

UNBOUNDED PRECEDING 
| UNBOUNDED FOLLOWING
| n PRECEDING 
| n FOLLOWING
| CURRENT ROW

Analytical Function Definitions

Ranking Functions:

RANK() - Assigns a rank to each row within a partition, with gaps for identical values.
DENSE_RANK() - Similar to RANK() but without gaps between rank values.
PERCENT_RANK() - Calculates the relative rank of a row within a partition as (RANK - 1) / (RC - 1), where RC is the total row count.
CUME_DIST() - Computes the cumulative distribution as PR / RC, where PR is the rank of the row including peers and RC is the total row count.

Value Functions:

FIRST_VALUE(val) - Returns the first value in the window frame.
LAST_VALUE(val) - Returns the last value in the window frame.
LEAD(val [, offset [, default]]) - Returns the value at the specified offset ahead of the current row.
LAG(val [, offset [, default]]) - Returns the value at the specified offset behind the current row.
NTH_VALUE(val, n) - Returns the nth value in the window frame.

Row Value Functions:

ROW_NUMBER() - Assigns a unique sequential number to each row within a partition.
NTILE(n) - Distributes rows into n approximately equal parts.

Processing Notes

Window functions can only appear in the SELECT and ORDER BY clauses.
Window functions cannot be nested.
The PARTITION BY and ORDER BY clauses cannot contain subqueries or outer references.
The default frame is RANGE UNBOUNDED PRECEDING, which also implies the default end bound of CURRENT ROW.
RANGE computes over a row and its peers, while ROWS computes over every row individually.
LEAD, LAG, NTH_VALUE require an ORDER BY in the window specification.

Examples: Windowed Results

SELECT 
  name, 
  salary, 
  MAX(salary) OVER (PARTITION BY name) AS max_sal,
  RANK() OVER (ORDER BY salary) AS rank, 
  DENSE_RANK() OVER (ORDER BY salary) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY salary) AS row_num 
FROM Employees.STAFF;

| name  | salary | max_sal | rank | dense_rank | row_num |
|-------|--------|---------|------|------------|---------|
| John  | 100000 | 100000  | 2    | 2          | 2       |
| Henry | 50000  | 50000   | 5    | 4          | 5       |
| John  | 60000  | 100000  | 3    | 3          | 3       |
| Suzie | 60000  | 150000  | 3    | 3          | 4       |
| Suzie | 150000 | 150000  | 1    | 1          | 1       |

Considerations and Limitations

Windowed aggregates cannot use DISTINCT if the window specification is ordered.
Analytical value functions like LEAD, LAG, NTH_VALUE require an ordering clause.
RANGE cannot use n PRECEDING or n FOLLOWING.

Additional Notes

For predictable ordering, always use ORDER BY in your SELECT statement, as window functions alone do not guarantee row order.

General Functions

Numeric functions

Kubling provides arithmetic operators and functions for numeric calculations, random value generation, rounding, trigonometry, formatting, and bitwise operations.

Arithmetic operators

Operator	Accepted types	Description
`+`	`integer`, `long`, `float`, `double`, `biginteger`, `bigdecimal`	Adds two numeric values.
`-`	`integer`, `long`, `float`, `double`, `biginteger`, `bigdecimal`	Subtracts one numeric value from another.
`*`	`integer`, `long`, `float`, `double`, `biginteger`, `bigdecimal`	Multiplies two numeric values.
`/`	`integer`, `long`, `float`, `double`, `biginteger`, `bigdecimal`	Divides one numeric value by another.

Random values

Function	Arguments	Returns	Description
`RANDINT()`	None	`integer`	Returns a random integer.
`RANDLONG()`	None	`long`	Returns a random long value.
`RANDFLOAT()`	None	`float`	Returns a random float value.
`RAND()`	None	`double`	Returns a random value using the generator already initialized for the query. If no generator exists, one is initialized using the system clock.
`RAND(x)`	`x`: seed value	`double`	Initializes a new random generator using `x` as its seed and returns a random value. The seed only affects Kubling `RAND` evaluations, not random functions executed by underlying sources.

Absolute values and rounding

Function	Arguments	Returns	Description
`ABS(x)`	`x`: numeric	Same type as `x`	Returns the absolute value of `x`.
`CEILING(x)`	`x`: `double` or `float`	Numeric	Returns the smallest integer value greater than or equal to `x`.
`FLOOR(x)`	`x`: `double` or `float`	Numeric	Returns the largest integer value less than or equal to `x`.
`ROUND(x, y)`	`x`: numeric; `y`: number of decimal places	Numeric	Rounds `x` to `y` decimal places.
`SIGN(x)`	`x`: numeric	`integer`	Returns `1` when `x` is positive, `0` when it is zero, and `-1` when it is negative.

Trigonometric functions

Function	Arguments	Returns	Description
`ACOS(x)`	`x`: `double` or `bigdecimal`	Numeric	Returns the arc cosine of `x`.
`ASIN(x)`	`x`: `double` or `bigdecimal`	Numeric	Returns the arc sine of `x`.
`ATAN(x)`	`x`: `double` or `bigdecimal`	Numeric	Returns the arc tangent of `x`.
`ATAN2(x, y)`	`x`, `y`: `double` or `bigdecimal`	Numeric	Returns an angle based on the signs of both arguments, selecting the correct quadrant.
`COS(x)`	`x`: `double` or `bigdecimal`	Numeric	Returns the cosine of `x`.
`COT(x)`	`x`: `double` or `bigdecimal`	Numeric	Returns the cotangent of `x`.
`SIN(x)`	`x`: `double` or `bigdecimal`	Numeric	Returns the sine of `x`.
`TAN(x)`	`x`: `double` or `bigdecimal`	Numeric	Returns the tangent of `x`.
`DEGREES(x)`	`x`: `double` or `bigdecimal`	Numeric	Converts an angle from radians to degrees.
`RADIANS(x)`	`x`: `double` or `bigdecimal`	Numeric	Converts an angle from degrees to radians.

Exponents and logarithms

Function	Arguments	Returns	Description
`EXP(x)`	`x`: `double` or `float`	Numeric	Returns Euler’s number raised to the power of `x`.
`LOG(x)`	`x`: `double` or `float`	Numeric	Returns the natural logarithm of `x`.
`LOG10(x)`	`x`: `double` or `float`	Numeric	Returns the base-10 logarithm of `x`.
`POWER(x, y)`	`x`, `y`: supported numeric values	Numeric	Returns `x` raised to the power of `y`.
`SQRT(x)`	`x`: `long`, `double`, or `bigdecimal`	Numeric	Returns the square root of `x`.
`MOD(x, y)`	`x`, `y`: numeric	Numeric	Returns the remainder of `x / y`.

Numeric formatting

Function	Arguments	Returns	Description
`FORMATBIGDECIMAL(x, format)`	`x`: `bigdecimal`; `format`: `string`	`string`	Formats `x` using the supplied format.
`FORMATBIGINTEGER(x, format)`	`x`: `biginteger`; `format`: `string`	`string`	Formats `x` using the supplied format.
`FORMATDOUBLE(x, format)`	`x`: `double`; `format`: `string`	`string`	Formats `x` using the supplied format.
`FORMATFLOAT(x, format)`	`x`: `float`; `format`: `string`	`string`	Formats `x` using the supplied format.
`FORMATINTEGER(x, format)`	`x`: `integer`; `format`: `string`	`string`	Formats `x` using the supplied format.
`FORMATLONG(x, format)`	`x`: `long`; `format`: `string`	`string`	Formats `x` using the supplied format.

Bitwise functions

Function	Arguments	Returns	Description
`BITAND(x, y)`	`x`, `y`: `integer`	`integer`	Returns the bitwise AND of `x` and `y`.
`BITOR(x, y)`	`x`, `y`: `integer`	`integer`	Returns the bitwise OR of `x` and `y`.
`BITXOR(x, y)`	`x`, `y`: `integer`	`integer`	Returns the bitwise XOR of `x` and `y`.
`BITNOT(x)`	`x`: `integer`	`integer`	Returns the bitwise complement of `x`.

Examples

-- Rounding and Power Functions
SELECT 
  ROUND(123.4567, 2) AS rounded_value,  -- Returns 123.46
  POWER(2, 3) AS powered_value;  -- Returns 8
 
-- Trigonometric Functions
SELECT 
  SIN(PI()/2) AS sin_value,  -- Returns 1
  COS(PI()) AS cos_value,    -- Returns -1
  TAN(PI()/4) AS tan_value;  -- Returns 1
 
-- Logarithm and Exponent Functions
SELECT 
  LOG(2.71828) AS natural_log,  -- Returns approximately 1
  LOG10(1000) AS log_base_10,   -- Returns 3
  EXP(1) AS exp_value;          -- Returns approximately 2.71828
 
-- Bitwise Operations
SELECT 
  BITAND(5, 3) AS bitwise_and,  -- Returns 1 (0101 AND 0011)
  BITOR(5, 3) AS bitwise_or,    -- Returns 7 (0101 OR 0011)
  BITXOR(5, 3) AS bitwise_xor;  -- Returns 6 (0101 XOR 0011)

String operators

Operator	Accepted types	Returns	Description
`x \|\| y`	`x`, `y`: `string` or `clob`	String-compatible value	Concatenates `x` and `y`. Returns `null` if either value is `null`.

String functions

Concatenation and case conversion

Function	Arguments	Returns	Description
`CONCAT(x, y)`	`x`, `y`: `string`	`string`	Concatenates `x` and `y` using ANSI null semantics. Returns `null` if either value is `null`.
`CONCAT2(x, y)`	`x`, `y`: `string`	`string`	Concatenates `x` and `y` using non-ANSI null semantics. If either value is `null`, returns the non-null value.
`INITCAP(x)`	`x`: `string`	`string`	Capitalizes the first letter of each word in `x` and converts the remaining letters to lowercase.
`LCASE(x)`	`x`: `string`	`string`	Converts `x` to lowercase.
`UCASE(x)`	`x`: `string`	`string`	Converts `x` to uppercase.

Character conversion and inspection

Function	Arguments	Returns	Description
`ASCII(x)`	`x`: `string`	`integer`	Returns the ASCII value of the first character in `x`. Returns `null` when `x` is an empty string.
`CHR(x)` / `CHAR(x)`	`x`: `integer`	`string`	Returns the character corresponding to the ASCII value `x`.
`LENGTH(x)`	`x`: `string`	`integer`	Returns the number of characters in `x`.
`ENDSWITH(suffix, source)`	`suffix`, `source`: `string`	`boolean`	Returns `true` if `source` ends with `suffix`. Returns `null` if either value is `null`.
`TOBASE64(str)`	`str`: `string`	`string`	Encodes `str` as Base64 using UTF-8.
`TOBASE64(str, encoding)`	`str`, `encoding`: `string`	`string`	Encodes `str` as Base64 using the specified character encoding.
`TOKENIZE(str, delimiter)`	`str`: `string`; `delimiter`: `char`	`string[]`	Splits `str` using the specified delimiter and returns the resulting tokens as an array.

Searching and extracting

Function	Arguments	Returns	Description
`LEFT(x, length)`	`x`: `string`; `length`: `integer`	`string`	Returns the leftmost `length` characters of `x`.
`RIGHT(x, length)`	`x`: `string`; `length`: `integer`	`string`	Returns the rightmost `length` characters of `x`.
`LOCATE(search, source)`	`search`, `source`: `string`	`integer`	Returns the position of the first occurrence of `search` in `source`.
`LOCATE(search, source, start)`	`search`, `source`: `string`; `start`: `integer`	`integer`	Returns the position of the first occurrence of `search` in `source`, beginning at position `start`.
`SUBSTRING(x, start)`	`x`: `string`; `start`: `integer`	`string`	Returns the substring of `x` beginning at position `start`.
`SUBSTRING(x, start, length)`	`x`: `string`; `start`, `length`: `integer`	`string`	Returns up to `length` characters from `x`, beginning at position `start`.
`INSERT(source, start, length, replacement)`	`source`, `replacement`: `string`; `start`, `length`: `integer`	`string`	Replaces `length` characters in `source`, beginning at position `start`, with `replacement`.

Padding and trimming

Function	Arguments	Returns	Description
`LPAD(x, length)`	`x`: `string`; `length`: `integer`	`string`	Pads `x` with spaces on the left until it reaches the requested length.
`LPAD(x, length, padding)`	`x`, `padding`: `string`; `length`: `integer`	`string`	Pads `x` on the left using `padding` until it reaches the requested length.
`RPAD(x, length)`	`x`: `string`; `length`: `integer`	`string`	Pads `x` with spaces on the right until it reaches the requested length.
`RPAD(x, length, padding)`	`x`, `padding`: `string`; `length`: `integer`	`string`	Pads `x` on the right using `padding` until it reaches the requested length.
`LTRIM(x)`	`x`: `string`	`string`	Removes leading whitespace from `x`.
`RTRIM(x)`	`x`: `string`	`string`	Removes trailing whitespace from `x`.
`TRIM([[LEADING\|TRAILING\|BOTH] [character] FROM] source)`	`character`, `source`: `string`	`string`	Removes the selected character from the beginning, end, or both ends of `source`.

Replacement, repetition, and escaping

Function	Arguments	Returns	Description
`REPEAT(x, instances)`	`x`: `string`; `instances`: `integer`	`string`	Repeats `x` the specified number of times.
`REPLACE(source, search, replacement)`	`source`, `search`, `replacement`: `string`	`string`	Replaces every occurrence of `search` in `source` with `replacement`.
`REGEXP_REPLACE(source, pattern, replacement [, flags])`	All arguments: `string`	`string`	Replaces occurrences matching `pattern` in `source`. Supported flags include global (`g`), multiline (`m`), and case-insensitive (`i`) matching.
`SPACE(length)`	`length`: `integer`	`string`	Returns a string containing the requested number of space characters.
`UNESCAPE(x)`	`x`: `string`	`string`	Resolves escaped characters in `x`, including Unicode and octal sequences.

URL query construction

Function	Arguments	Returns	Description
`QUERYSTRING(path [, expression [AS name] ...])`	`path`: `string`; optional named expressions	`string`	Creates a URL query string from `path` and the supplied expressions.

Encoding and compression

Function	Arguments	Returns	Description
`COMPRESS(x)`	`x`: `string`	`clob`	Compresses `x` using the Deflate algorithm and UTF-8 encoding. Returns the compressed content as a Base64-encoded `clob`.
`COMPRESS(x, charset)`	`x`, `charset`: `string`	`clob`	Compresses `x` using the Deflate algorithm and the specified character set. Returns the compressed content as a Base64-encoded `clob`.
`DECOMPRESS(x)`	`x`: `string`	`clob`	Decompresses a Base64-encoded Deflate value using UTF-8 as the character set.
`DECOMPRESS(x, charset)`	`x`, `charset`: `string`	`clob`	Decompresses a Base64-encoded Deflate value using the specified character set.

Examples

-- Basic String Operations
SELECT 
  CONCAT('Hello', ' World') AS greeting,  -- Returns 'Hello World'
  LENGTH('Hello World') AS length,        -- Returns 11
  INITCAP('hello world') AS capitalized;  -- Returns 'Hello World'
 
-- Substring and Padding
SELECT 
  SUBSTRING('abcdef', 2, 3) AS substring,  -- Returns 'bcd'
  LPAD('123', 5, '0') AS padded_left,      -- Returns '00123'
  RPAD('123', 5, '0') AS padded_right;     -- Returns '12300'
 
-- Trimming and Repeating
SELECT 
  TRIM('  Hello World  ') AS trimmed,     -- Returns 'Hello World'
  REPEAT('abc', 3) AS repeated;           -- Returns 'abcabcabc'
 
-- Advanced String Replacement
SELECT 
  REPLACE('Hello World', 'World', 'SQL') AS replaced,       -- Returns 'Hello SQL'
  REGEXP_REPLACE('abc123def', '[0-9]', 'X', 'g') AS masked; -- Returns 'abcXXXdef'

Type conversion functions

Function	Arguments	Returns	Description
`CONVERT(x, type)`	`x`: any value; `type`: standard Kubling data type	Target `type`	Converts `x` to the specified data type.
`CAST(x AS type)`	`x`: any value; `type`: standard Kubling data type	Target `type`	Converts `x` to the specified data type. Equivalent to `CONVERT(x, type)`.
`TO_CHARS(blob, encoding)`	`blob`: `blob`; `encoding`: `string`	`clob`	Decodes the binary content of `blob` into characters using the specified character encoding.
`TO_CHARS(blob, encoding, well_formed)`	`blob`: `blob`; `encoding`: `string`; `well_formed`: `boolean`	`clob`	Decodes the binary content of `blob` using the specified character encoding. The `well_formed` flag controls the handling of malformed or invalid byte sequences.
`TO_BYTES(clob, encoding)`	`clob`: `clob`; `encoding`: `string`	`blob`	Encodes the character content of `clob` into bytes using the specified character encoding.
`TO_BYTES(clob, encoding, well_formed)`	`clob`: `clob`; `encoding`: `string`; `well_formed`: `boolean`	`blob`	Encodes the character content of `clob` using the specified character encoding. The `well_formed` flag controls the handling of malformed or invalid character sequences.

💡

Conversion only changes the value’s data type. Additional target type options, such as length, precision, or scale, are ignored.

Examples

-- Basic Type Conversion
SELECT 
  CAST('123' AS integer) AS int_value,      -- Returns 123
  CONVERT('123.45', double) AS dbl_value,   -- Returns 123.45
  CAST(123 AS string) AS str_value;         -- Returns '123'
 
-- Converting Dates and Timestamps
SELECT 
  CAST('2025-10-10' AS date) AS date_value,             -- Returns 2025-10-10
  CONVERT('2025-10-10 23:59:59', timestamp) AS ts_value; -- Returns 2025-10-10 23:59:59.000
 
-- Converting JSON Strings
SELECT 
  CAST('[1, 2, 3]' AS json) AS json_array,        -- Returns [1,2,3]
  CONVERT('{"key": "value"}', json) AS json_obj;  -- Returns {"key": "value"}

Date and time functions

Current date and time

The current date and time functions return a stable value throughout the execution of a single user command.

Function	Arguments	Returns	Description
`CURDATE()` / `CURRENT_DATE()`	None	`date`	Returns the current date. All invocations within the same user command return the same value.
`CURTIME()` / `CURRENT_TIME()`	None	`time`	Returns the current time. All invocations within the same user command return the same value.
`NOW()` / `CURRENT_TIMESTAMP()`	None	`timestamp`	Returns the current date and time with millisecond precision. All invocations within the same user command return the same value.

Date and time components

Function	Arguments	Returns	Description
`DAYNAME(x)`	`x`: `date` or `timestamp`	`string`	Returns the localized name of the day of the week.
`DAYOFMONTH(x)`	`x`: `date` or `timestamp`	`integer`	Returns the day of the month.
`DAYOFWEEK(x)`	`x`: `date` or `timestamp`	`integer`	Returns the day of the week, where Sunday is `1` and Saturday is `7`.
`DAYOFYEAR(x)`	`x`: `date` or `timestamp`	`integer`	Returns the day number within the year.
`HOUR(x)`	`x`: `time` or `timestamp`	`integer`	Returns the hour component using the 24-hour clock.
`MINUTE(x)`	`x`: `time` or `timestamp`	`integer`	Returns the minute component.
`MONTH(x)`	`x`: `date` or `timestamp`	`integer`	Returns the month component.
`MONTHNAME(x)`	`x`: `date` or `timestamp`	`string`	Returns the localized name of the month.
`QUARTER(x)`	`x`: `date` or `timestamp`	`integer`	Returns the quarter of the year, from `1` to `4`.
`SECOND(x)`	`x`: `time` or `timestamp`	`integer`	Returns the seconds component.
`WEEK(x)`	`x`: `date` or `timestamp`	`integer`	Returns the week of the year, from `1` to `53`.
`YEAR(x)`	`x`: `date` or `timestamp`	`integer`	Returns the four-digit year.

Component extraction

Function	Arguments	Returns	Description
`EXTRACT(field FROM x)`	`field`: supported date or time field; `x`: `date`, `time`, or `timestamp`	`integer` or `double`	Extracts the specified component from `x`. Supported fields include `YEAR`, `MONTH`, `DAYOFMONTH`, `HOUR`, `MINUTE`, `SECOND`, and `EPOCH`.

Formatting and parsing

Function	Arguments	Returns	Description
`FORMATDATE(x, format)`	`x`: `date`; `format`: `string`	`string`	Formats `x` using the specified date pattern.
`FORMATTIME(x, format)`	`x`: `time`; `format`: `string`	`string`	Formats `x` using the specified time pattern.
`FORMATTIMESTAMP(x, format)`	`x`: `timestamp`; `format`: `string`	`string`	Formats `x` using the specified timestamp pattern.
`PARSEDATE(value, format)`	`value`, `format`: `string`	`date`	Parses `value` as a date using the specified pattern.
`PARSETIME(value, format)`	`value`, `format`: `string`	`time`	Parses `value` as a time using the specified pattern.
`PARSETIMESTAMP(value, format)`	`value`, `format`: `string`	`timestamp`	Parses `value` as a timestamp using the specified pattern.

Epoch conversion

Function	Arguments	Returns	Description
`EPOCH(x)`	`x`: `date` or `timestamp`	`double`	Returns the number of seconds elapsed since the Unix epoch, including fractional microseconds.
`FROM_MILLIS(milliseconds)`	`milliseconds`: `long`	`timestamp`	Creates a timestamp from the number of milliseconds elapsed since the Unix epoch.
`TO_MILLIS(x)`	`x`: `timestamp`	`long`	Returns the number of milliseconds elapsed between the Unix epoch and `x`.
`FROM_UNIXTIME(seconds)`	`seconds`: `long`	`string`	Converts Unix epoch seconds to a timestamp string using the runtime’s default time zone. The result uses the format `yyyy-MM-dd HH:mm:ss`.
`UNIX_TIMESTAMP(value)`	`value`: `string`	`long`	Parses a timestamp string and returns the corresponding Unix epoch value in seconds. The input must use the JDBC timestamp format `yyyy-[m]m-[d]d HH:mm:ss[.f...]`.

💡

FROM_UNIXTIME and UNIX_TIMESTAMP use the runtime’s default time zone. Their results may therefore differ between Kubling instances configured with different time zones.

Timestamp construction and timezone adjustment

Function	Arguments	Returns	Description
`TIMESTAMPCREATE(date, time)`	`date`: `date`; `time`: `time`	`timestamp`	Creates a timestamp by combining the supplied date and time values.
`MODIFYTIMEZONE(timestamp, source_timezone, target_timezone)`	`timestamp`: `timestamp`; `source_timezone`, `target_timezone`: `string`	`timestamp`	Adjusts `timestamp` by applying the offset difference between the source and target time zones.

`TIMESTAMPADD`

Adds a specified interval amount to a timestamp.

TIMESTAMPADD(interval, count, timestamp)

Arguments:

Name	Description
`interval`	A datetime interval unit, can be one of the following keywords: `SQL_TSI_FRAC_SECOND`, `SQL_TSI_SECOND`, `SQL_TSI_MINUTE`, `SQL_TSI_HOUR`, `SQL_TSI_DAY`, `SQL_TSI_WEEK`, `SQL_TSI_MONTH`, `SQL_TSI_QUARTER`, `SQL_TSI_YEAR`
`count`	A long or integer count of units to add to the timestamp. Negative values subtract that number of units.
`timestamp`	A datetime expression.

Examples:

SELECT TIMESTAMPADD(SQL_TSI_MONTH, 12,'2025-10-10');
SELECT TIMESTAMPADD(SQL_TSI_SECOND, 12, CONVERT('2025-10-10 23:59:59', timestamp));

`TIMESTAMPDIFF`

Calculates the number of date part intervals crossed between two timestamps and returns a long value.

TIMESTAMPDIFF(interval, startTime, endTime)

Arguments:

Name	Description
`interval`	A datetime interval unit, the same as keywords used by `TIMESTAMPADD`.
`startTime`	A datetime expression.
`endTime`	A datetime expression.

Examples:

SELECT TIMESTAMPDIFF(SQL_TSI_MONTH,'2000-01-02','2025-10-10');
SELECT TIMESTAMPDIFF(SQL_TSI_SECOND,'2000-01-02 00:00:00','2025-10-10 23:59:59');
SELECT TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,'2000-01-02 00:00:00.0','2025-10-10 23:59:59.999999');

Note:

If endTime > startTime, a non-negative number is returned.
If endTime < startTime, a non-positive number is returned.
The date part difference is counted regardless of how close the timestamps are.
For example, '2025-01-02 00:00:00.0' is still considered 1 hour ahead of '2025-01-01 23:59:59.999999'.

Decode functions

Decode functions map an input value to a result using a delimiter-separated sequence of search-result pairs.

The mapping string uses the following structure:

search<delimiter>result<delimiter>search<delimiter>result[<delimiter>default]

If no delimiter is provided, a comma (,) is used.

When the input matches one of the search values, the corresponding result is returned. If no match exists, the optional default value is returned. When no default is provided, the original input value is returned.

Function	Arguments	Returns	Description
`DECODESTRING(input, mappings [, delimiter])`	`input`, `mappings`: `string`; `delimiter`: optional `string`	`string`	Decodes `input` using the search-result pairs defined in `mappings`.
`DECODEINTEGER(input, mappings [, delimiter])`	`input`, `mappings`: `string`; `delimiter`: optional `string`	`integer`	Decodes `input` using the search-result pairs defined in `mappings` and converts the selected result to an integer.

💡

Values returned by DECODEINTEGER, including the optional default value or the original input fallback, must be convertible to integer.

Examples

Basic String Decoding

SELECT 
  metadata__name AS pod_name,
  metadata__namespace AS namespace,
  clusterName,
  DECODESTRING(status, 'True:Ready,False:Not Ready,Unknown:Unknown Status', ':') AS status_message
FROM POD_STATUS_CONDITION
WHERE status IS NOT NULL;

pod_name                                |namespace         |clusterName|status_message|
----------------------------------------+------------------+-----------+--------------+
haproxy-ingress-r579p                   |ingress-controller|kube1      |Ready,False   |
haproxy-ingress-r579p                   |ingress-controller|kube1      |Ready,False   |
haproxy-ingress-r579p                   |ingress-controller|kube1      |Ready,False   |
haproxy-ingress-r579p                   |ingress-controller|kube1      |Ready,False   |
haproxy-ingress-r579p                   |ingress-controller|kube1      |Ready,False   |
ingress-default-backend-6468f96589-cm9kh|ingress-controller|kube1      |Ready,False   |

Using Default Value

SELECT DECODESTRING(status, '200:OK,404:Not Found', ':', 'Unknown Status') AS status_message 
FROM API_LOGS;

Integer Decoding

SELECT DECODEINTEGER(priority, '1:Low,2:Medium,3:High', ':', '0') AS priority_label 
FROM TASKS;

Choice Functions

Choice functions are used to handle null values or to select the first non-null value from a list. They are essential when dealing with incomplete data or when default values are required.

COALESCE

Returns the first non-null value in a list of expressions.

SELECT 
  name,
  COALESCE(email, 'No Email Provided') AS contact
FROM USERS;

Expected output:

| name   | contact               |
|--------|-----------------------|
| Alice  | [email protected]     |
| Bob    | No Email Provided     |
| Carol  | [email protected]     |

IFNULL

Similar to COALESCE, but only accepts two arguments. Returns the second argument if the first is null.

SELECT 
  name,
  IFNULL(phone, 'No Phone Available') AS phone_number
FROM USERS;

Expected output:

| name   | phone_number         |
|--------|----------------------|
| Alice  | 123-456-7890         |
| Bob    | No Phone Available   |
| Carol  | 987-654-3210         |

NVL

Another variation of IFNULL with the same behavior.

SELECT 
  metadata__name,
  NVL(spec__externalID, 'No External ID Provided') AS user_address
FROM NODE;

Expected output:

metadata__name  |user_address           |
----------------+-----------------------+
master-1		|123					|
master-2		|No External ID Provided|
node-1  		|ABC					|
node-2  		|No External ID Provided|

NULLIF

Returns null if the two arguments are equal, otherwise returns the first argument.

SELECT 
  metadata__name,
  NULLIF(spec__podCIDR, '10.42.0.0/24') AS active_status
FROM NODE;

Cryptographic functions

Hashing

Function	Arguments	Returns	Description
`MD5(str)`	`str`: `string`	`varbinary`	Computes the MD5 digest of the UTF-8 encoded value of `str`.
`MD5(bin)`	`bin`: `varbinary`	`varbinary`	Computes the MD5 digest of the supplied binary value.
`HASH(str)`	`str`: `string`	`string`	Computes the SHA-256 digest of the UTF-8 encoded value of `str` and returns it as a URL-safe Base64 string without padding.
`SHA1(str)`	`str`: `string`	`varbinary`	Computes the SHA-1 digest of the UTF-8 encoded value of `str`.
`SHA1(bin)`	`bin`: `varbinary`	`varbinary`	Computes the SHA-1 digest of the supplied binary value.
`SHA2_256(str)`	`str`: `string`	`varbinary`	Computes the SHA-256 digest of the UTF-8 encoded value of `str`.
`SHA2_256(bin)`	`bin`: `varbinary`	`varbinary`	Computes the SHA-256 digest of the supplied binary value.
`SHA2_512(str)`	`str`: `string`	`varbinary`	Computes the SHA-512 digest of the UTF-8 encoded value of `str`.
`SHA2_512(bin)`	`bin`: `varbinary`	`varbinary`	Computes the SHA-512 digest of the supplied binary value.

⚠️

MD5 and SHA-1 are retained for compatibility and should not be used for new security-sensitive applications. Prefer SHA-256 or SHA-512.

Symmetric encryption

Function	Arguments	Returns	Description
`AES_ENCRYPT(x, key)`	`x`: `string` or `varbinary`; `key`: `string`	`varbinary`	Encrypts `x` using `AES/CBC/PKCS5Padding` and the supplied key.
`AES_DECRYPT(x, key)`	`x`: `varbinary`; `key`: `string`	`varbinary`	Decrypts an AES-encrypted binary value using the supplied key.

Certificate inspection

Function	Arguments	Returns	Description
`X509_CERT_EXP_DAYS(certificate)`	`certificate`: `string`	`json`	Returns expiration information for the supplied X.509 certificate as a JSON value.
`PKCS7_CERT_EXP_DAYS(certificate)`	`certificate`: `string`	`json`	Returns expiration information for the supplied PKCS#7 certificate as a JSON value.

Vector Functions v25.1+

Vector functions are used for mathematical operations on vectors, such as distance calculations and transformations. These are especially useful for machine learning and similarity computations.

`vectorStringToArray`

Converts a string representation of a vector into an array of double.

SELECT 
  vectorStringToArray('[0.1, 0.2, 0.3]') AS vector_array;

Expected output:

| vector_array    |
|-----------------|
| [0.1, 0.2, 0.3] |

`euclideanDistance`

Calculates the Euclidean distance between two vectors.

SELECT 
  euclideanDistance(
    vectorStringToArray('[1.0, 2.0, 3.0]'),
    vectorStringToArray('[4.0, 5.0, 6.0]')
  ) AS distance;

Expected output:

| distance |
|----------|
| 5.196    |

`innerProduct`

Calculates the dot product of two vectors.

SELECT 
  innerProduct(
    vectorStringToArray('[1.0, 2.0, 3.0]'),
    vectorStringToArray('[4.0, 5.0, 6.0]')
  ) AS dot_product;

Expected output:

| dot_product |
|-------------|
| 32.0        |

`cosineDistance`

Calculates the cosine distance between two vectors.

SELECT 
  cosineDistance(
    vectorStringToArray('[1.0, 0.0, 0.0]'),
    vectorStringToArray('[0.0, 1.0, 0.0]')
  ) AS cosine_distance;

Expected output:

| cosine_distance |
|-----------------|
| 1.0             |

Document functions v24.5.3+

Document functions allow JSON and YAML content to be parsed, constructed, queried, and transformed directly within SQL expressions.

Function	Arguments	Returns	Description
`jsonParse(value, skipValidation)`	`value`: `clob` or `blob`; `skipValidation`: `boolean`	`json`	Parses `value` as a JSON document. When `skipValidation` is `true`, Kubling skips validation before parsing the supplied content.
`jsonObject(c1, c2, ..., cn)`	`c1` through `cn`: columns	`json`	Creates a JSON object from the supplied columns. Each field uses the column name as its key and the column value as its value.
`yamlAsJSON(value)`	`value`: `clob`	`json`	Parses a valid YAML document and returns its equivalent JSON representation.
`jsonPath(value, path)`	`value`: `string` or `clob`; `path`: `string`	`json`	Evaluates an RFC 9535 JSONPath expression against a valid JSON document. The result is always returned as `json`, including when the expression selects a scalar value.
`jsonJq(value, query)`	`value`: `string` or `clob`; `query`: `string`	`json`	Applies a jq query to a valid JSON document. The result is always returned as `json`, including when the query produces a scalar value.
`jsonPathAsString(value, path)`	`value`: `string` or `clob`; `path`: `string`	`string`	Evaluates a JSONPath expression and returns the result as a string instead of a `json` value.
`jsonJqAsString(value, query)`	`value`: `string` or `clob`; `query`: `string`	`string`	Applies a jq query and returns the result as a string instead of a `json` value.

💡

jsonPath and jsonJq always return the json data type, even when the selected result is a single scalar value. Use the corresponding AsString function or an explicit type conversion when a string result is required.

Examples

Parsing JSON Documents

SELECT jsonParse(config_json, true) AS parsed_config
FROM CONFIG_TABLE;

Extracting Fields with JsonPath

SELECT jsonObject(metadata__name, spec__providerID) AS node_summary
FROM NODE;

(assuming metadata__name and spec__providerID are string columns)

Using JSON Objects Directly

SELECT jsonObject(metadata__labels, status__nodeInfo) AS node_summary
FROM NODE;

Expected output (assuming metadata__labels and status__nodeInfo are json columns):

| node_summary                         |
|--------------------------------------|
| {"metadata__name":"kbl-dev-master-1","status__conditions":[ {
  "lastHeartbeatTime" : "2025-05-18T14:30:15Z",
  "lastTransitionTime" : "2025-03-12T14:07:28Z",
  "message" : "kubelet has sufficient memory available",
  "reason" : "KubeletHasSufficientMemory",
  "status" : "False",
  "type" : "MemoryPressure"
}, {
  "lastHeartbeatTime" : "2025-05-18T14:30:15Z",
  "lastTransitionTime" : "2025-03-12T14:07:28Z",
  "message" : "kubelet has no disk pressure",
  "reason" : "KubeletHasNoDiskPressure",
  "status" : "False",
  "type" : "DiskPressure"
} ]} |

YAML to JSON Conversion

SELECT yamlAsJSON(manifest_yaml) AS manifest_json
FROM CONFIG_MAPS;

Expected output (assuming manifest_yaml contains valid YAML documents):

| manifest_json   |
|-----------------|
| {"key":"value"} |
| {"foo":"bar"}   |

SQL Spatial

Aggregate Functions

COUNT(*)

COUNT(x)

COUNT_BIG(*)

COUNT_BIG(x)

SUM(x)

AVG(x)

MIN(x)

MAX(x)

ANY(x) / SOME(x)

EVERY(x)

VAR_POP(x)

VAR_SAMP(x)

STDDEV_POP(x)

STDDEV_SAMP(x)

Window Functions

Usage

Analytical Function Definitions

Ranking Functions:

Value Functions:

Row Value Functions:

Processing Notes

Examples: Windowed Results

Considerations and Limitations

Additional Notes

General Functions

Numeric functions

Arithmetic operators

Random values

Absolute values and rounding

Trigonometric functions

Exponents and logarithms

Numeric formatting

Bitwise functions

Examples

String operators

String functions

Concatenation and case conversion

Character conversion and inspection

Searching and extracting

Padding and trimming

Replacement, repetition, and escaping

URL query construction

Encoding and compression

Examples

Type conversion functions

Examples

Date and time functions

Current date and time

Date and time components

Component extraction

Formatting and parsing

Epoch conversion

Timestamp construction and timezone adjustment

TIMESTAMPADD

TIMESTAMPDIFF

Decode functions

Examples

Choice Functions

COALESCE

IFNULL

NVL

NULLIF

Cryptographic functions

Hashing

Symmetric encryption

Certificate inspection

Vector Functions v25.1+

vectorStringToArray

euclideanDistance

innerProduct

cosineDistance

Document functions v24.5.3+

Examples

`COUNT(*)`

`COUNT(x)`

`COUNT_BIG(*)`

`COUNT_BIG(x)`

`SUM(x)`

`AVG(x)`

`MIN(x)`

`MAX(x)`

`ANY(x)` / `SOME(x)`

`EVERY(x)`

`VAR_POP(x)`

`VAR_SAMP(x)`

`STDDEV_POP(x)`

`STDDEV_SAMP(x)`

`TIMESTAMPADD`

`TIMESTAMPDIFF`

`vectorStringToArray`

`euclideanDistance`

`innerProduct`

`cosineDistance`