Other

FQDN

Returns the fully qualified domain name of the RawTree server.

Syntax

fqdn()

Returned value

Returns the fully qualified domain name of the RawTree server. String

Examples

Usage example

SELECT fqdn()

┌─FQDN()──────────────────────────┐
│ rawtree.us-east-2.internal │
└─────────────────────────────────┘

Introduced in version 20.1.

MACNumToString

Interprets a UInt64 number as a MAC address in big endian format. Returns the corresponding MAC address in format AA:BB:CC:DD:EE:FF (colon-separated numbers in hexadecimal form) as string.

Syntax

MACNumToString(num)

Arguments

• num — UInt64 number. UInt64

Returned value

Returns a MAC address in format AA:BB:CC:DD:EE:FF. String

Examples

Usage example

SELECT MACNumToString(149809441867716) AS mac_address;

┌─mac_address───────┐
│ 88:00:11:22:33:44 │
└───────────────────┘

Introduced in version 1.1.

MACStringToNum

The inverse function of MACNumToString. If the MAC address has an invalid format, it returns 0.

Syntax

MACStringToNum(s)

Arguments

• s — MAC address string. String

Returned value

Returns a UInt64 number. UInt64

Examples

Usage example

SELECT MACStringToNum('01:02:03:04:05:06') AS mac_numeric;

1108152157446

Introduced in version 1.1.

MACStringToOUI

Given a MAC address in format AA:BB:CC:DD:EE:FF (colon-separated numbers in hexadecimal form), returns the first three octets as a UInt64 number. If the MAC address has an invalid format, it returns 0.

Syntax

MACStringToOUI(s)

Arguments

• s — MAC address string. String

Returned value

First three octets as UInt64 number. UInt64

Examples

Usage example

SELECT MACStringToOUI('00:50:56:12:34:56') AS oui;

20566

Introduced in version 1.1.

__applyFilter

Special function for JOIN runtime filtering.

Syntax

__applyFilter(filter_name, key)

Arguments

• filter_name — Internal name of runtime filter. It is built by BuildRuntimeFilterStep. String
• key — Value of any type that is checked to be present in the filter

Returned value

False if the key should be filtered Bool

Examples

Example

This function is not supposed to be used in user queries. It might be added to query plan during optimization.

Introduced in version 25.10.

__patchPartitionID

Internal function. Receives the name of a part and a hash of patch part's column names. Returns the name of partition of patch part. The argument must be a correct name of part, the behaviour is undefined otherwise.

Introduced in version 25.5.

authenticatedUser

If the session user has been switched using the EXECUTE AS command, this function returns the name of the original user that was used for authentication and creating the session. Alias: authUser()

Syntax

authenticatedUser()

Returned value

The name of the authenticated user. String

Examples

Usage example

EXECUTE as u1;
            SELECT currentUser(), authenticatedUser();

┌─currentUser()─┬─authenticatedUser()─┐
│ u1            │ default             │
└───────────────┴─────────────────────┘

Introduced in version 25.11.

bar

Builds a bar chart. Draws a band with width proportional to (x - min) and equal to width characters when x = max. The band is drawn with accuracy to one eighth of a symbol.

Syntax

bar(x, min, max[, width])

Arguments

• x — Size to display. (U)Int* or Float* or Decimal
• min — The minimum value. (U)Int* or Float* or Decimal
• max — The maximum value. (U)Int* or Float* or Decimal
• width — Optional. The width of the bar in characters. The default is 80. const (U)Int* or const Float* or const Decimal

Returned value

Returns a unicode-art bar string. String

Examples

Usage example

SELECT
toHour(EventTime) AS h,
count() AS c,
bar(c, 0, 600000, 20) AS bar
FROM test.hits
GROUP BY h
ORDER BY h ASC

┌──h─┬──────c─┬─bar────────────────┐
│  0 │ 292907 │ █████████▋         │
│  1 │ 180563 │ ██████             │
│  2 │ 114861 │ ███▋               │
│  3 │  85069 │ ██▋                │
│  4 │  68543 │ ██▎                │
│  5 │  78116 │ ██▌                │
│  6 │ 113474 │ ███▋               │
│  7 │ 170678 │ █████▋             │
│  8 │ 278380 │ █████████▎         │
│  9 │ 391053 │ █████████████      │
│ 10 │ 457681 │ ███████████████▎   │
│ 11 │ 493667 │ ████████████████▍  │
│ 12 │ 509641 │ ████████████████▊  │
│ 13 │ 522947 │ █████████████████▍ │
│ 14 │ 539954 │ █████████████████▊ │
│ 15 │ 528460 │ █████████████████▌ │
│ 16 │ 539201 │ █████████████████▊ │
│ 17 │ 523539 │ █████████████████▍ │
│ 18 │ 506467 │ ████████████████▊  │
│ 19 │ 520915 │ █████████████████▎ │
│ 20 │ 521665 │ █████████████████▍ │
│ 21 │ 542078 │ ██████████████████ │
│ 22 │ 493642 │ ████████████████▍  │
│ 23 │ 400397 │ █████████████▎     │
└────┴────────┴────────────────────┘

Introduced in version 1.1.

blockNumber

Returns a monotonically increasing sequence number of the block containing the row. The returned block number is updated on a best-effort basis, i.e. it may not be fully accurate.

Syntax

blockNumber()

Returned value

Sequence number of the data block where the row is located. UInt64

Examples

Basic usage

SELECT blockNumber()
FROM
(
    SELECT *
    FROM system.numbers
    LIMIT 10
) SETTINGS max_block_size = 2

┌─blockNumber()─┐
│             7 │
│             7 │
└───────────────┘
┌─blockNumber()─┐
│             8 │
│             8 │
└───────────────┘
┌─blockNumber()─┐
│             9 │
│             9 │
└───────────────┘
┌─blockNumber()─┐
│            10 │
│            10 │
└───────────────┘
┌─blockNumber()─┐
│            11 │
│            11 │
└───────────────┘

Introduced in version 1.1.

blockSerializedSize

Returns the uncompressed size in bytes of a block of values on disk.

Syntax

blockSerializedSize(x1[, x2[, ...]])

Arguments

• x1[, x2, ...] — Any number of values for which to get the uncompressed size of the block. Any

Returned value

Returns the number of bytes that will be written to disk for a block of values without compression. UInt64

Examples

Usage example

SELECT blockSerializedSize(maxState(1)) AS x;

┌─x─┐
│ 2 │
└───┘

Introduced in version 20.3.

blockSize

In RawTree, queries are processed in blocks (chunks). This function returns the size (row count) of the block the function is called on.

Syntax

blockSize()

Returned value

Returns the number of rows in the current block. UInt64

Examples

Usage example

SELECT blockSize()
FROM system.numbers LIMIT 5

┌─blockSize()─┐
│           5 │
│           5 │
│           5 │
│           5 │
│           5 │
└─────────────┘

Introduced in version 1.1.

buildId

Returns the build ID generated by a compiler for the running RawTree server binary. If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise it produces a constant value.

Syntax

buildId()

Returned value

Returns the build ID. String

Examples

Usage example

SELECT buildId()

┌─buildId()────────────────────────────────┐
│ AB668BEF095FAA6BD26537F197AC2AF48A927FB4 │
└──────────────────────────────────────────┘

Introduced in version 20.5.

byteSize

Returns an estimation of the uncompressed byte size of its arguments in memory. For String arguments, the function returns the string length + 8 (length). If the function has multiple arguments, the function accumulates their byte sizes.

Syntax

byteSize(arg1[, arg2, ...])

Arguments

• arg1[, arg2, ...] — Values of any data type for which to estimate the uncompressed byte size. Any

Returned value

Returns an estimation of the byte size of the arguments in memory. UInt64

Examples

Usage example

SELECT byteSize('string')

┌─byteSize('string')─┐
│                 15 │
└────────────────────┘

Multiple arguments

SELECT byteSize(NULL, 1, 0.3, '')

┌─byteSize(NULL, 1, 0.3, '')─┐
│                         19 │
└────────────────────────────┘

Introduced in version 21.1.

catboostEvaluate

Evaluate an external catboost model. CatBoost is an open-source gradient boosting library developed by Yandex for machine learning. Accepts a path to a catboost model and model arguments (features).

Prerequisites

1. Build the catboost evaluation library

Before evaluating catboost models, the libcatboostmodel.<so|dylib> library must be made available. See CatBoost documentation how to compile it.

Next, specify the path to libcatboostmodel.<so|dylib> in the rawtree configuration:

<rawtree>
...
    <catboost_lib_path>/path/to/libcatboostmodel.so</catboost_lib_path>
...
</rawtree>

For security and isolation reasons, the model evaluation does not run in the server process but in the rawtree-library-bridge process. At the first execution of catboostEvaluate(), the server starts the library bridge process if it is not running already. Both processes communicate using a HTTP interface. By default, port 9012 is used. A different port can be specified as follows - this is useful if port 9012 is already assigned to a different service.

<library_bridge>
    <port>9019</port>
</library_bridge>

2. Train a catboost model using libcatboost

See Training and applying models for how to train catboost models from a training data set.

Syntax

catboostEvaluate(path_to_model, feature_1[, feature_2, ..., feature_n])

Arguments

• path_to_model — Path to catboost model. const String
• feature — One or more model features/arguments. Float*

Returned value

Returns the model evaluation result. Float64

Examples

catboostEvaluate

SELECT catboostEvaluate('/root/occupy.bin', Temperature, Humidity, Light, CO2, HumidityRatio) AS prediction FROM occupancy LIMIT 1

4.695691092573497

Introduced in version 22.9.

colorOKLABToSRGB

Converts a color from the OKLab perceptual color space to the sRGB color space.

The input color is specified in the OKLab color space. If the input values are outside the typical OKLab ranges, the result is implementation-defined.

OKLab uses three components:

• L: perceptual lightness (typically in the range [0..1])
• a: green-red opponent axis
• b: blue-yellow opponent axis

The a and b components are theoretically unbounded, but in practice are between -0.4 and 0.4. OKLab is designed to be perceptually uniform while remaining inexpensive to compute.

The conversion is intended to be the inverse of colorSRGBToOKLAB and consists of the following stages:

1. Conversion from OKLab to linear sRGB.
2. Conversion from linear sRGB to gamma-encoded sRGB.

The optional gamma argument specifies the exponent used when converting from linear sRGB to gamma-encoded RGB values. If not specified, a default gamma value is used for consistency with colorSRGBToOKLAB.

For more information about the OKLab color space and its relationship to sRGB, see https://developer.mozilla.org/en-US/docs/Web/CSS/Reference/Values/color_value/oklab .

Syntax

colorOKLABToSRGB(tuple [, gamma])

Arguments

• tuple — A tuple of three numeric values L, a, b, where L is in the range [0...1]. Tuple(Float64, Float64, Float64)
• gamma — Optional. The exponent that is used to transform linear sRGB back to sRGB by applying (x ^ (1 / gamma)) * 255 for each channel x. Defaults to 2.2. Float64

Returned value

Returns a tuple (R, G, B) representing sRGB color values. Tuple(Float64, Float64, Float64)

Examples

Convert OKLAB to sRGB (Float)

SELECT colorOKLABToSRGB((0.4466, 0.0991, 0.44)) AS rgb;

┌─rgb──────────────────────┐
│ (198.07056923258935,0,0) │
└──────────────────────────┘

Convert OKLAB to sRGB (UInt8)

WITH colorOKLABToSRGB((0.7, 0.1, 0.54)) AS t
SELECT tuple(toUInt8(t.1), toUInt8(t.2), toUInt8(t.3)) AS RGB;

┌─RGB──────────┐
│ (255,0,0)    │
└──────────────┘

Introduced in version 26.2.

colorOKLCHToSRGB

Converts a colour from the OKLCH perceptual colour space to the familiar sRGB colour space.

If L is outside the range [0...1], C is negative, or H is outside the range [0...360], the result is implementation-defined.

:::note OKLCH is a cylindrical version of the OKLab colour space. It's three coordinates are L (the lightness in the range [0...1]), C (chroma >= 0) and H (hue in degrees from [0...360]). OKLab/OKLCH is designed to be perceptually uniform while remaining cheap to compute. :::

The conversion is the inverse of colorSRGBToOKLCH:

1. OKLCH to OKLab.
2. OKLab to Linear sRGB
3. Linear sRGB to sRGB

The second argument gamma is used at the last stage.

For references of colors in OKLCH space, and how they correspond to sRGB colors please see https://oklch.com/.

Syntax

colorOKLCHToSRGB(tuple [, gamma])

Arguments

• tuple — A tuple of three numeric values L, C, H, where L is in the range [0...1], C >= 0 and H is in the range [0...360]. Tuple(Float64, Float64, Float64)
• gamma — Optional. The exponent that is used to transform linear sRGB back to sRGB by applying (x ^ (1 / gamma)) * 255 for each channel x. Defaults to 2.2. Float64

Returned value

Returns a tuple (R, G, B) representing sRGB color values. Tuple(Float64, Float64, Float64)

Examples

Convert OKLCH to sRGB

SELECT colorOKLCHToSRGB((0.6, 0.12, 40)) AS rgb;

┌─rgb───────────────────────────────────────────────────────┐
│ (186.02058688365264,100.68677189684993,71.67819977081575) │
└───────────────────────────────────────────────────────────┘

Convert OKLCH to sRGB (UInt8)

WITH colorOKLCHToSRGB((0.6, 0.12, 40)) AS t
SELECT tuple(toUInt8(t.1), toUInt8(t.2), toUInt8(t.3)) AS RGB;

┌─RGB──────────┐
│ (186,100,71) │
└──────────────┘

Introduced in version 25.7.

colorSRGBToOKLAB

Converts a colour encoded in the sRGB colour space to the perceptually uniform OKLAB colour space.

If any input channel is outside [0...255] or the gamma value is non-positive, the behaviour is implementation-defined.

:::note OKLAB is a perceptually uniform color space. Its three coordinates are L (the lightness in the range [0...1]), a (Green-Red axis) and b (Blue-Yellow axis). OKLab is designed to be perceptually uniform while remaining cheap to compute. :::

The conversion consists of two stages:

1. sRGB to Linear sRGB
2. Linear sRGB to OKLab

Syntax

colorSRGBToOKLAB(tuple[, gamma])

Arguments

• tuple — Tuple of three values R, G, B in the range [0...255]. Tuple(UInt8, UInt8, UInt8)
• gamma — Optional. Exponent that is used to linearize sRGB by applying (x / 255)^gamma to each channel x. Defaults to 2.2. Float64

Returned value

Returns a tuple (L, a, b) representing the OKLAB color space values. Tuple(Float64, Float64, Float64)

Examples

Convert sRGB to OKLAB

SELECT colorSRGBToOKLAB((128, 64, 32), 2.2) AS lab;

┌─lab──────────────────────────────────────────────────────────┐
│ (0.4436238384931984,0.07266246769242975,0.07500108778529994) │
└──────────────────────────────────────────────────────────────┘

Introduced in version 26.2.

colorSRGBToOKLCH

Converts a colour encoded in the sRGB colour space to the perceptually uniform OKLCH colour space.

If any input channel is outside [0...255] or the gamma value is non-positive, the behaviour is implementation-defined.

:::note OKLCH is a cylindrical version of the OKLab colour space. It's three coordinates are L (the lightness in the range [0...1]), C (chroma >= 0) and H (the hue in degrees from [0...360]). OKLab/OKLCH is designed to be perceptually uniform while remaining cheap to compute. :::

The conversion consists of three stages:

1. sRGB to Linear sRGB
2. Linear sRGB to OKLab
3. OKLab to OKLCH.

For references of colors in the OKLCH space, and how they correspond to sRGB colors, please see https://OKLCH.com/.

Syntax

colorSRGBToOKLCH(tuple[, gamma])

Arguments

• tuple — Tuple of three values R, G, B in the range [0...255]. Tuple(UInt8, UInt8, UInt8)
• gamma — Optional. Exponent that is used to linearize sRGB by applying (x / 255)^gamma to each channel x. Defaults to 2.2. Float64

Returned value

Returns a tuple (L, C, H) representing the OKLCH color space values. Tuple(Float64, Float64, Float64)

Examples

Convert sRGB to OKLCH

SELECT colorSRGBToOKLCH((128, 64, 32), 2.2) AS lch;

┌─lch───────────────────────────────────────────────────────┐
│ (0.4436238384931984,0.1044269954567863,45.90734548193018) │
└───────────────────────────────────────────────────────────┘

Introduced in version 25.7.

connectionId

Returns the connection ID of the client that submitted the current query. This function is most useful in debugging scenarios. It was created for compatibility with MySQL's CONNECTION_ID function. It is not typically used in production queries.

Syntax

connectionId()

Returned value

Returns the connection ID of the current client. UInt64

Examples

Usage example

SELECT connectionId();

┌─connectionId()─┐
│              0 │
└────────────────┘

Introduced in version 21.3.

countDigits

Returns the number of decimal digits needed to represent a value.

:::note This function takes into account the scales of decimal values i.e., it calculates the result over the underlying integer type which is (value * scale).

For example:

• countDigits(42) = 2
• countDigits(42.000) = 5
• countDigits(0.04200) = 4 :::

:::tip You can check decimal overflow for Decimal64 with countDigits(x) > 18, although it is slower than isDecimalOverflow. :::

Syntax

countDigits(x)

Arguments

• x — An integer or decimal value. (U)Int* or Decimal

Returned value

Returns the number of digits needed to represent x. UInt8

Examples

Usage example

SELECT countDigits(toDecimal32(1, 9)), countDigits(toDecimal32(-1, 9)),
       countDigits(toDecimal64(1, 18)), countDigits(toDecimal64(-1, 18)),
       countDigits(toDecimal128(1, 38)), countDigits(toDecimal128(-1, 38));

┌─countDigits(toDecimal32(1, 9))─┬─countDigits(toDecimal32(-1, 9))─┬─countDigits(toDecimal64(1, 18))─┬─countDigits(toDecimal64(-1, 18))─┬─countDigits(toDecimal128(1, 38))─┬─countDigits(toDecimal128(-1, 38))─┐
│                             10 │                              10 │                              19 │                               19 │                               39 │                                39 │
└────────────────────────────────┴─────────────────────────────────┴─────────────────────────────────┴──────────────────────────────────┴──────────────────────────────────┴───────────────────────────────────┘

Introduced in version 20.8.

currentDatabase

Returns the name of the current database. Useful in table engine parameters of CREATE TABLE queries where you need to specify the database.

Also see the SET statement.

Syntax

currentDatabase()

Returned value

Returns the current database name. String

Examples

Usage example

SELECT currentDatabase()

┌─currentDatabase()─┐
│ default           │
└───────────────────┘

Introduced in version 1.1.

currentProfiles

Returns an array of the setting profiles for the current user.

Syntax

currentProfiles()

Returned value

Returns an array of setting profiles for the current user. Array(String)

Examples

Usage example

SELECT currentProfiles();

┌─currentProfiles()─────────────────────────────┐
│ ['default', 'readonly_user', 'web_analytics'] │
└───────────────────────────────────────────────┘

Introduced in version 21.9.

currentQueryID

Returns current Query id.

Syntax

currentQueryID()

Examples

Example

SELECT currentQueryID();

┌─currentQueryID()─────────────────────┐
│ 1280d0e8-1a08-4524-be6e-77975bb68e7d │
└──────────────────────────────────────┘

currentRoles

Returns an array of the roles which are assigned to the current user.

Syntax

currentRoles()

Returned value

Returns an array of the roles which are assigned to the current user. Array(String)

Examples

Usage example

SELECT currentRoles();

┌─currentRoles()─────────────────────────────────┐
│ ['sql-console-role:jane.smith@rawtree.com'] │
└────────────────────────────────────────────────┘

Introduced in version 21.9.

currentSchemas

Same as function currentDatabase but

• accepts a boolean argument which is ignored
• returns the database name as an array with a single value.

Function currentSchemas only exists for compatibility with PostgreSQL. Please use currentDatabase instead.

Also see the SET statement.

Syntax

currentSchemas(bool)

Arguments

• bool — A boolean value, which is ignored. Bool

Returned value

Returns a single-element array with the name of the current database. Array(String)

Examples

Usage example

SELECT currentSchemas(true)

┌─currentSchemas(true)─┐
│ ['default']          │
└──────────────────────┘

Introduced in version 23.7.

currentUser

Returns the name of the current user. In case of a distributed query, the name of the user who initiated the query is returned.

Syntax

currentUser()

Returned value

Returns the name of the current user, otherwise the login of the user who initiated the query. String

Examples

Usage example

SELECT currentUser()

┌─currentUser()─┐
│ default       │
└───────────────┘

Introduced in version 20.1.

defaultProfiles

Returns an array of default setting profile names for the current user.

Syntax

defaultProfiles()

Returned value

Returns an array of default setting profile names for the current user. Array(String)

Examples

Usage example

SELECT defaultProfiles();

┌─defaultProfiles()─┐
│ ['default']       │
└───────────────────┘

Introduced in version 21.9.

defaultRoles

Returns an array of default roles for the current user.

Syntax

defaultRoles()

Returned value

Returns an array of default roles for the current user. Array(String)

Examples

Usage example

SELECT defaultRoles();

┌─defaultRoles()─────────────────────────────────┐
│ ['sql-console-role:jane.smith@rawtree.com'] │
└────────────────────────────────────────────────┘

Introduced in version 21.9.

defaultValueOfArgumentType

Returns the default value for a given data type. Does not include default values for custom columns set by the user.

Syntax

defaultValueOfArgumentType(expression)

Arguments

• expression — Arbitrary type of value or an expression that results in a value of an arbitrary type. Any

Returned value

Returns 0 for numbers, an empty string for strings or NULL for Nullable types. UInt8 or String or NULL

Examples

Usage example

SELECT defaultValueOfArgumentType(CAST(1 AS Int8));

┌─defaultValueOfArgumentType(CAST(1, 'Int8'))─┐
│                                           0 │
└─────────────────────────────────────────────┘

Nullable example

SELECT defaultValueOfArgumentType(CAST(1 AS Nullable(Int8)));

┌─defaultValueOfArgumentType(CAST(1, 'Nullable(Int8)'))─┐
│                                                  ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────────┘

Introduced in version 1.1.

defaultValueOfTypeName

Returns the default value for the given type name.

Syntax

defaultValueOfTypeName(type)

Arguments

• type — A string representing a type name. String

Returned value

Returns the default value for the given type name: 0 for numbers, an empty string for strings, or NULL for Nullable UInt8 or String or NULL

Examples

Usage example

SELECT defaultValueOfTypeName('Int8');

┌─defaultValueOfTypeName('Int8')─┐
│                              0 │
└────────────────────────────────┘

Nullable example

SELECT defaultValueOfTypeName('Nullable(Int8)');

┌─defaultValueOfTypeName('Nullable(Int8)')─┐
│                                     ᴺᵁᴸᴸ │
└──────────────────────────────────────────┘

Introduced in version 1.1.

displayName

Returns the value of display_name from config or the server's Fully Qualified Domain Name (FQDN) if not set.

Syntax

displayName()

Returned value

Returns the value of display_name from config or server FQDN if not set. String

Examples

Usage example

SELECT displayName();

┌─displayName()─┐
│ production    │
└───────────────┘

Introduced in version 22.11.

dumpColumnStructure

Outputs a detailed description of the internal structure of a column and its data type.

Syntax

dumpColumnStructure(x)

Arguments

• x — Value for which to get the description of. Any

Returned value

Returns a description of the column structure used for representing the value. String

Examples

Usage example

SELECT dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'));

┌─dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'))─┐
│ DateTime, Const(size = 1, UInt32(size = 1))                  │
└──────────────────────────────────────────────────────────────┘

Introduced in version 1.1.

enabledProfiles

Returns an array of setting profile names which are enabled for the current user.

Syntax

enabledProfiles()

Returned value

Returns an array of setting profile names which are enabled for the current user. Array(String)

Examples

Usage example

SELECT enabledProfiles();

┌─enabledProfiles()─────────────────────────────────────────────────┐
│ ['default', 'readonly_user', 'web_analytics', 'batch_processing'] │
└───────────────────────────────────────────────────────────────────┘

Introduced in version 21.9.

enabledRoles

Returns an array of the roles which are enabled for the current user.

Syntax

enabledRoles()

Returned value

Returns an array of role names which are enabled for the current user. Array(String)

Examples

Usage example

SELECT enabledRoles();

┌─enabledRoles()─────────────────────────────────────────────────┐
│ ['general_data', 'sql-console-role:jane.smith@rawtree.com'] │
└────────────────────────────────────────────────────────────────┘

Introduced in version 21.9.

errorCodeToName

Returns the textual name of a numeric RawTree error code. The mapping from numeric error codes to error names is available here.

Syntax

errorCodeToName(error_code)

Arguments

• error_code — RawTree error code. (U)Int* or Float* or Decimal

Returned value

Returns the textual name of error_code. String

Examples

Usage example

SELECT errorCodeToName(252);

┌─errorCodeToName(252)─┐
│ TOO_MANY_PARTS       │
└──────────────────────┘

Introduced in version 20.12.

file

Reads a file as a string and loads the data into the specified column. The file content is not interpreted.

Also see the file table function.

Syntax

file(path[, default])

Arguments

• path — The path of the file relative to the user_files_path. Supports wildcards *, **, ?, {abc,def} and {N..M} where N, M are numbers and 'abc', 'def' are strings. String
• default — The value returned if the file does not exist or cannot be accessed. String or NULL

Returned value

Returns the file content as a string. String

Examples

Insert files into a table

INSERT INTO table SELECT file('a.txt'), file('b.txt');

Introduced in version 21.3.

filesystemAvailable

Returns the amount of free space in the filesystem hosting the database persistence. The returned value is always smaller than the total free space (filesystemUnreserved) because some space is reserved for the operating system.

Syntax

filesystemAvailable([disk_name])

Arguments

• disk_name — Optional. The disk name to find the amount of free space for. If omitted, uses the default disk. String or FixedString

Returned value

Returns the amount of remaining space available in bytes. UInt64

Examples

Usage example

SELECT formatReadableSize(filesystemAvailable()) AS "Available space";

┌─Available space─┐
│ 30.75 GiB       │
└─────────────────┘

Introduced in version 20.1.

filesystemCapacity

Returns the capacity of the filesystem in bytes. Needs the path to the data directory to be configured.

Syntax

filesystemCapacity([disk_name])

Arguments

• disk_name — Optional. The disk name to get the capacity for. If omitted, uses the default disk. String or FixedString

Returned value

Returns the capacity of the filesystem in bytes. UInt64

Examples

Usage example

SELECT formatReadableSize(filesystemCapacity()) AS "Capacity";

┌─Capacity──┐
│ 39.32 GiB │
└───────────┘

Introduced in version 20.1.

filesystemUnreserved

Returns the total amount of free space on the filesystem hosting the database persistence (previously filesystemFree). See also filesystemAvailable.

Syntax

filesystemUnreserved([disk_name])

Arguments

• disk_name — Optional. The disk name for which to find the total amount of free space. If omitted, uses the default disk. String or FixedString

Returned value

Returns the amount of free space in bytes. UInt64

Examples

Usage example

SELECT formatReadableSize(filesystemUnreserved()) AS "Free space";

┌─Free space─┐
│ 32.39 GiB  │
└────────────┘

Introduced in version 22.12.

finalizeAggregation

Given an aggregation state, this function returns the result of aggregation (or the finalized state when using a -State combinator).

Syntax

finalizeAggregation(state)

Arguments

• state — State of aggregation. AggregateFunction

Returned value

Returns the finalized result of aggregation. Any

Examples

Usage example

SELECT finalizeAggregation(arrayReduce('maxState', [1, 2, 3]));

┌─finalizeAggregation(arrayReduce('maxState', [1, 2, 3]))─┐
│                                                       3 │
└─────────────────────────────────────────────────────────┘

Combined with initializeAggregation

WITH initializeAggregation('sumState', number) AS one_row_sum_state
SELECT
    number,
    finalizeAggregation(one_row_sum_state) AS one_row_sum,
    runningAccumulate(one_row_sum_state) AS cumulative_sum
FROM numbers(5);

┌─number─┬─one_row_sum─┬─cumulative_sum─┐
│      0 │           0 │              0 │
│      1 │           1 │              1 │
│      2 │           2 │              3 │
│      3 │           3 │              6 │
│      4 │           4 │             10 │
└────────┴─────────────┴────────────────┘

Introduced in version 1.1.

flipCoordinates

Flips the x and y coordinates of geometric objects. This operation swaps latitude and longitude, which is useful for converting between different coordinate systems or correcting coordinate order.

For a Point, it swaps the x and y coordinates. For complex geometries (LineString, Polygon, MultiPolygon, Ring, MultiLineString), it recursively applies the transformation to each coordinate pair.

The function supports both individual geometry types (Point, Ring, Polygon, MultiPolygon, LineString, MultiLineString) and the Geometry variant type.

Syntax

flipCoordinates(geometry)

Arguments

• geometry — The geometry to transform. Supported types: Point (Tuple(Float64, Float64)), Ring (Array(Point)), Polygon (Array(Ring)), MultiPolygon (Array(Polygon)), LineString (Array(Point)), MultiLineString (Array(LineString)), or Geometry (a variant containing any of these types).

Returned value

The geometry with flipped coordinates. The return type matches the input type. Point or Ring or Polygon or MultiPolygon or LineString or MultiLineString or Geometry

Examples

basic_point

SELECT flipCoordinates((1.0, 2.0));

(2.0, 1.0)

ring

SELECT flipCoordinates([(1.0, 2.0), (3.0, 4.0)]);

[(2.0, 1.0), (4.0, 3.0)]

polygon

SELECT flipCoordinates([[(1.0, 2.0), (3.0, 4.0)], [(5.0, 6.0), (7.0, 8.0)]]);

[[(2.0, 1.0), (4.0, 3.0)], [(6.0, 5.0), (8.0, 7.0)]]

geometry_wkt

SELECT flipCoordinates(readWkt('POINT(10 20)'));

(20, 10)

geometry_polygon_wkt

SELECT flipCoordinates(readWkt('POLYGON((0 0, 5 0, 5 5, 0 5, 0 0))'));

[[(0, 0), (0, 5), (5, 5), (5, 0), (0, 0)]]

Introduced in version 25.10.

formatQuery

Returns a formatted, possibly multi-line, version of the given SQL query. Throws in case of a parsing error. [example:multiline]

Syntax

formatQuery(query)

Arguments

• query — The SQL query to be formatted. String

Returned value

The formatted query String

Examples

multiline

SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT
    a,
    b
FROM tab
WHERE (a > 3) AND (b < 3)

formatQueryOrNull

Returns a formatted, possibly multi-line, version of the given SQL query. Returns NULL in case of a parsing error. [example:multiline]

Syntax

formatQueryOrNull(query)

Arguments

• query — The SQL query to be formatted. String

Returned value

The formatted query String

Examples

multiline

SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT
    a,
    b
FROM tab
WHERE (a > 3) AND (b < 3)

formatQuerySingleLine

Like formatQuery() but the returned formatted string contains no line breaks. Throws in case of a parsing error. [example:multiline]

Syntax

formatQuerySingleLine(query)

Arguments

• query — The SQL query to be formatted. String

Returned value

The formatted query String

Examples

multiline

SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)

formatQuerySingleLineOrNull

Like formatQuery() but the returned formatted string contains no line breaks. Returns NULL in case of a parsing error. [example:multiline]

Syntax

formatQuerySingleLineOrNull(query)

Arguments

• query — The SQL query to be formatted. String

Returned value

The formatted query String

Examples

multiline

SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)

formatReadableDecimalSize

Given a size (number of bytes), this function returns a readable, rounded size with suffix (KB, MB, etc.) as a string.

The opposite operations of this function are parseReadableSize.

Syntax

formatReadableDecimalSize(x)

Arguments

• x — Size in bytes. UInt64

Returned value

Returns a readable, rounded size with suffix as a string. String

Examples

Format file sizes

SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableDecimalSize(filesize_bytes) AS filesize

┌─filesize_bytes─┬─filesize───┐
│              1 │ 1.00 B     │
│           1024 │ 1.02 KB    │
│        1048576 │ 1.05 MB    │
│      192851925 │ 192.85 MB  │
└────────────────┴────────────┘

Introduced in version 22.11.

formatReadableQuantity

Given a number, this function returns a rounded number with suffix (thousand, million, billion, etc.) as a string.

This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values.

Syntax

formatReadableQuantity(x)

Arguments

• x — A number to format. UInt64

Returned value

Returns a rounded number with suffix as a string. String

Examples

Format numbers with suffixes

SELECT
    arrayJoin([1024, 1234 * 1000, (4567 * 1000) * 1000, 98765432101234]) AS number,
    formatReadableQuantity(number) AS number_for_humans

┌─────────number─┬─number_for_humans─┐
│           1024 │ 1.02 thousand     │
│        1234000 │ 1.23 million      │
│     4567000000 │ 4.57 billion      │
│ 98765432101234 │ 98.77 trillion    │
└────────────────┴───────────────────┘

Introduced in version 20.10.

formatReadableSize

Given a size (number of bytes), this function returns a readable, rounded size with suffix (KiB, MiB, etc.) as string.

The opposite operations of this function are parseReadableSize, parseReadableSizeOrZero, and parseReadableSizeOrNull. This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values.

Syntax

formatReadableSize(x)

Arguments

• x — Size in bytes. UInt64

Returned value

Returns a readable, rounded size with suffix as a string. String

Examples

Format file sizes

SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableSize(filesize_bytes) AS filesize

┌─filesize_bytes─┬─filesize───┐
│              1 │ 1.00 B     │
│           1024 │ 1.00 KiB   │
│        1048576 │ 1.00 MiB   │
│      192851925 │ 183.92 MiB │
└────────────────┴────────────┘

Introduced in version 1.1.

formatReadableTimeDelta

Given a time interval (delta) in seconds, this function returns a time delta with year/month/day/hour/minute/second/millisecond/microsecond/nanosecond as a string.

This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values.

Syntax

formatReadableTimeDelta(column[, maximum_unit, minimum_unit])

Arguments

• column — A column with a numeric time delta. Float64
• maximum_unit — Optional. Maximum unit to show. Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years. Default value: years. const String
• minimum_unit — Optional. Minimum unit to show. All smaller units are truncated. Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years. If explicitly specified value is bigger than maximum_unit, an exception will be thrown. Default value: seconds if maximum_unit is seconds or bigger, nanoseconds otherwise. const String

Returned value

Returns a time delta as a string. String

Examples

Usage example

SELECT
    arrayJoin([100, 12345, 432546534]) AS elapsed,
    formatReadableTimeDelta(elapsed) AS time_delta

┌────elapsed─┬─time_delta─────────────────────────────────────────────────────┐
│        100 │ 1 minute and 40 seconds                                        │
│      12345 │ 3 hours, 25 minutes and 45 seconds                             │
│  432546534 │ 13 years, 8 months, 17 days, 7 hours, 48 minutes and 54 seconds│
└────────────┴────────────────────────────────────────────────────────────────┘

With maximum unit

SELECT
    arrayJoin([100, 12345, 432546534]) AS elapsed,
    formatReadableTimeDelta(elapsed, 'minutes') AS time_delta

┌────elapsed─┬─time_delta─────────────────────────────────────────────────────┐
│        100 │ 1 minute and 40 seconds                                         │
│      12345 │ 205 minutes and 45 seconds                                      │
│  432546534 │ 7209108 minutes and 54 seconds                                  │
└────────────┴─────────────────────────────────────────────────────────────────┘

Introduced in version 20.12.

generateRandomStructure

Generates random table structure in the format column1_name column1_type, column2_name column2_type, ....

Syntax

generateRandomStructure([number_of_columns, seed])

Arguments

• number_of_columns — The desired number of columns in the resultant table structure. If set to 0 or Null, the number of columns will be random from 1 to 128. Default value: Null. UInt64
• seed — Random seed to produce stable results. If seed is not specified or set to Null, it is randomly generated. UInt64

Returned value

Randomly generated table structure. String

Examples

Usage example

SELECT generateRandomStructure()

c1 Decimal32(5), c2 Date, c3 Tuple(LowCardinality(String), Int128, UInt64, UInt16, UInt8, IPv6), c4 Array(UInt128), c5 UInt32, c6 IPv4, c7 Decimal256(64), c8 Decimal128(3), c9 UInt256, c10 UInt64, c11 DateTime

with specified number of columns

SELECT generateRandomStructure(1)

c1 Map(UInt256, UInt16)

with specified seed

SELECT generateRandomStructure(NULL, 33)

c1 DateTime, c2 Enum8('c2V0' = 0, 'c2V1' = 1, 'c2V2' = 2, 'c2V3' = 3), c3 LowCardinality(Nullable(FixedString(30))), c4 Int16, c5 Enum8('c5V0' = 0, 'c5V1' = 1, 'c5V2' = 2, 'c5V3' = 3), c6 Nullable(UInt8), c7 String, c8 Nested(e1 IPv4, e2 UInt8, e3 UInt16, e4 UInt16, e5 Int32, e6 Map(Date, Decimal256(70)))

Introduced in version 23.5.

generateSerialID

Generates and returns sequential numbers starting from the previous counter value. This function takes a string argument - a series identifier, and an optional starting value. The server should be configured with Keeper. The series are stored in Keeper nodes under the path, which can be configured in series_keeper_path in the server configuration.

Syntax

generateSerialID(series_identifier[, start_value])

Arguments

• series_identifier — Series identifier const String
• start_value — Optional. Starting value for the counter. Defaults to 0. Note: this value is only used when creating a new series and is ignored if the series already exists UInt*

Returned value

Returns sequential numbers starting from the previous counter value. UInt64

Examples

first call

SELECT generateSerialID('id1')

┌─generateSerialID('id1')──┐
│                        1 │
└──────────────────────────┘

second call

SELECT generateSerialID('id1')

┌─generateSerialID('id1')──┐
│                        2 │
└──────────────────────────┘

column call

SELECT *, generateSerialID('id1') FROM test_table

┌─CounterID─┬─UserID─┬─ver─┬─generateSerialID('id1')──┐
│         1 │      3 │   3 │                        3 │
│         1 │      1 │   1 │                        4 │
│         1 │      2 │   2 │                        5 │
│         1 │      5 │   5 │                        6 │
│         1 │      4 │   4 │                        7 │
└───────────┴────────┴─────┴──────────────────────────┘

with start value

SELECT generateSerialID('id2', 100)

┌─generateSerialID('id2', 100)──┐
│                           100 │
└───────────────────────────────┘

with start value second call

SELECT generateSerialID('id2', 100)

┌─generateSerialID('id2', 100)──┐
│                           101 │
└───────────────────────────────┘

Introduced in version 25.1.

getClientHTTPHeader

Gets the value of an HTTP header. If there is no such header or the current request is not performed via the HTTP interface, the function returns an empty string. Certain HTTP headers (e.g., Authentication and X-RawTree-*) are restricted.

:::note Setting allow_get_client_http_header is required The function requires the setting allow_get_client_http_header to be enabled. The setting is not enabled by default for security reasons, because some headers, such as Cookie, could contain sensitive info. :::

HTTP headers are case sensitive for this function. If the function is used in the context of a distributed query, it returns non-empty result only on the initiator node.

Syntax

getClientHTTPHeader(name)

Arguments

• name — The HTTP header name. String

Returned value

Returns the value of the header. String

Examples

Usage example

SELECT getClientHTTPHeader('Content-Type');

┌─getClientHTTPHeader('Content-Type')─┐
│ application/x-www-form-urlencoded   │
└─────────────────────────────────────┘

Introduced in version 24.5.

getMacro

Returns the value of a macro from the server configuration file. Macros are defined in the <macros> section of the configuration file and can be used to distinguish servers by convenient names even if they have complicated hostnames. If the function is executed in the context of a distributed table, it generates a normal column with values relevant to each shard.

Syntax

getMacro(name)

Arguments

• name — The name of the macro to retrieve. const String

Returned value

Returns the value of the specified macro. String

Examples

Basic usage

SELECT getMacro('test');

┌─getMacro('test')─┐
│ Value            │
└──────────────────┘

Introduced in version 20.1.

getMaxTableNameLengthForDatabase

Returns the maximum table name length in a specified database.

Syntax

getMaxTableNameLengthForDatabase(database_name)

Arguments

• database_name — The name of the specified database. String

Returned value

Returns the length of the maximum table name, an Integer

Examples

typical

SELECT getMaxTableNameLengthForDatabase('default');

┌─getMaxTableNameLengthForDatabase('default')─┐
            │                                         206 │
            └─────────────────────────────────────────────┘

getMergeTreeSetting

Returns the current value of a MergeTree setting.

Syntax

getMergeTreeSetting(setting_name)

Arguments

• setting_name — The setting name. String

Returned value

Returns the merge tree setting's current value.

Examples

Usage example

SELECT getMergeTreeSetting('index_granularity');

┌─getMergeTreeSetting('index_granularity')─┐
│                                     8192 │
└──────────────────────────────────────────┘

Introduced in version 25.6.

getOSKernelVersion

Returns a string with the OS kernel version.

Syntax

getOSKernelVersion()

Returned value

Returns the current OS kernel version. String

Examples

Usage example

SELECT getOSKernelVersion();

┌─getOSKernelVersion()────┐
│ Linux 4.15.0-55-generic │
└─────────────────────────┘

Introduced in version 21.11.

getServerPort

Returns the server's port number for a given protocol.

Syntax

getServerPort(port_name)

Arguments

• port_name — The name of the port. String

Returned value

Returns the server port number. UInt16

Examples

Usage example

SELECT getServerPort('tcp_port');

┌─getServerPort('tcp_port')─┐
│                      9000 │
└───────────────────────────┘

Introduced in version 21.10.

getServerSetting

Returns the currently set value, given a server setting name.

Syntax

getServerSetting(setting_name')

Arguments

• setting_name — The server setting name. String

Returned value

Returns the server setting's current value. Any

Examples

Usage example

SELECT getServerSetting('allow_use_jemalloc_memory');

┌─getServerSetting('allow_use_jemalloc_memory')─┐
│ true                                          │
└───────────────────────────────────────────────┘

Introduced in version 25.6.

getSetting

Returns the current value of a setting.

Syntax

getSetting(setting_name)

Arguments

• setting_Name — The setting name. const String

Returned value

Returns the setting's current value. Any

Examples

Usage example

SELECT getSetting('enable_analyzer');
SET enable_analyzer = false;
SELECT getSetting('enable_analyzer');

┌─getSetting('⋯_analyzer')─┐
│ true                     │
└──────────────────────────┘
┌─getSetting('⋯_analyzer')─┐
│ false                    │
└──────────────────────────┘

Introduced in version 20.7.

getSettingOrDefault

Returns the current value of a setting or returns the default value specified in the second argument if the setting is not set in the current profile.

Syntax

getSettingOrDefault(setting_name, default_value)

Arguments

• setting_name — The setting name. String
• default_value — Value to return if custom_setting is not set. Value may be of any data type or Null.

Returned value

Returns the current value of the specified setting or default_value if the setting is not set.

Examples

Usage example

SELECT getSettingOrDefault('custom_undef1', 'my_value');
SELECT getSettingOrDefault('custom_undef2', 100);
SELECT getSettingOrDefault('custom_undef3', NULL);

my_value
100
NULL

Introduced in version 24.10.

getSizeOfEnumType

Returns the number of fields in the given Enum.

Syntax

getSizeOfEnumType(x)

Arguments

• x — Value of type Enum. Enum

Returned value

Returns the number of fields with Enum input values. UInt8/16

Examples

Usage example

SELECT getSizeOfEnumType(CAST('a' AS Enum8('a' = 1, 'b' = 2))) AS x;

┌─x─┐
│ 2 │
└───┘

Introduced in version 1.1.

getSubcolumn

Receives the expression or identifier and constant string with the name of subcolumn.

Returns requested subcolumn extracted from the expression.

Examples

getSubcolumn

SELECT getSubcolumn(array_col, 'size0'), getSubcolumn(tuple_col, 'elem_name')

getTypeSerializationStreams

Enumerates stream paths of a data type. This function is intended for developmental use.

Syntax

getTypeSerializationStreams(col)

Arguments

• col — Column or string representation of a data-type from which the data type will be detected. Any

Returned value

Returns an array with all the serialization sub-stream paths. Array(String)

Examples

tuple

SELECT getTypeSerializationStreams(tuple('a', 1, 'b', 2))

['{TupleElement(1), Regular}','{TupleElement(2), Regular}','{TupleElement(3), Regular}','{TupleElement(4), Regular}']

map

SELECT getTypeSerializationStreams('Map(String, Int64)')

['{ArraySizes}','{ArrayElements, TupleElement(keys), Regular}','{ArrayElements, TupleElement(values), Regular}']

Introduced in version 22.6.

globalVariable

Takes a constant string argument and returns the value of the global variable with that name. This function is intended for compatibility with MySQL and not needed or useful for normal operation of RawTree. Only few dummy global variables are defined.

Syntax

globalVariable(name)

Arguments

• name — Global variable name. String

Returned value

Returns the value of variable name. Any

Examples

globalVariable

SELECT globalVariable('max_allowed_packet')

67108864

Introduced in version 20.5.

hasColumnInTable

Checks if a specific column exists in a database table. For elements in a nested data structure, the function checks for the existence of a column. For the nested data structure itself, the function returns 0.

Syntax

hasColumnInTable([hostname[, username[, password]],]database, table, column)

Arguments

• database — Name of the database. const String
• table — Name of the table. const String
• column — Name of the column. const String
• hostname — Optional. Remote server name to perform the check on. const String
• username — Optional. Username for remote server. const String
• password — Optional. Password for remote server. const String

Returned value

Returns 1 if the given column exists, 0 otherwise. UInt8

Examples

Check an existing column

SELECT hasColumnInTable('system','metrics','metric')

1

Check a non-existing column

SELECT hasColumnInTable('system','metrics','non-existing_column')

0

Introduced in version 1.1.

hasThreadFuzzer

Returns whether the thread fuzzer is enabled. THis function is only useful for testing and debugging.

Syntax

hasThreadFuzzer()

Returned value

Returns whether Thread Fuzzer is effective. UInt8

Examples

Check Thread Fuzzer status

SELECT hasThreadFuzzer()

┌─hasThreadFuzzer()─┐
│                 0 │
└───────────────────┘

Introduced in version 20.6.

hostName

Returns the name of the host on which this function was executed. If the function executes on a remote server (distributed processing), the remote server name is returned. If the function executes in the context of a distributed table, it generates a normal column with values relevant to each shard. Otherwise it produces a constant value.

Syntax

hostName()

Returned value

Returns the host name. String

Examples

Usage example

SELECT hostName()

┌─hostName()─┐
│ rawtree │
└────────────┘

Introduced in version 20.5.

icebergBucket

Implements logic for the iceberg bucket transform

Syntax

icebergBucket(N, value)

Arguments

• N — The number of buckets, modulo. const (U)Int*
• value — The source value to transform. (U)Int* or Bool or Decimal or Float* or String or FixedString or UUID or Date or Time or DateTime

Returned value

Returns a 32-bit hash of the source value. Int32

Examples

Example

SELECT icebergBucket(5, 1.0 :: Float32)

4

Introduced in version 25.5.

icebergTruncate

Implements logic of iceberg truncate transform: https://iceberg.apache.org/spec/#truncate-transform-details.

Syntax

icebergTruncate(N, value)

Arguments

• value — The value to transform. String or (U)Int* or Decimal

Returned value

The same type as the argument

Examples

Example

SELECT icebergTruncate(3, 'iceberg')

ice

Introduced in version 25.3.

identity

This function returns the argument you pass to it, which is useful for debugging and testing. It lets you bypass index usage to see full scan performance instead. The query analyzer ignores anything inside identity functions when looking for indexes to use, and it also disables constant folding.

Syntax

identity(x)

Arguments

• x — Input value. Any

Returned value

Returns the input value unchanged. Any

Examples

Usage example

SELECT identity(42)

42

Introduced in version 1.1.

ignore

Accepts arbitrary arguments and unconditionally returns 0.

Syntax

ignore(x)

Arguments

• x — An input value which is unused and passed only so as to avoid a syntax error. Any

Returned value

Always returns 0. UInt8

Examples

Usage example

SELECT ignore(0, 'RawTree', NULL)

┌─ignore(0, 'RawTree', NULL)─┐
│                             0 │
└───────────────────────────────┘

Introduced in version 1.1.

indexHint

This function is intended for debugging and introspection. It ignores its argument and always returns 1. The arguments are not evaluated.

During index analysis, the argument of this function is assumed to not be wrapped in indexHint. This allows you to select data in index ranges by the corresponding condition but without further filtering by this condition. The index in RawTree is sparse and using indexHint will yield more data than specifying the same condition directly.

SELECT * FROM test WHERE key = 123;

SELECT * FROM test WHERE indexHint(key = 123);

Note: It is not possible to optimize a query with the indexHint function. The indexHint function does not optimize the query, as it does not provide any additional information for the query analysis. Having an expression inside the indexHint function is not anyhow better than without the indexHint function. The indexHint function can be used only for introspection and debugging purposes and it does not improve performance. If you see the usage of indexHint by anyone other than RawTree contributors, it is likely a mistake and you should remove it.

Syntax

indexHint(expression)

Arguments

• expression — Any expression for index range selection. Expression

Returned value

Returns 1 in all cases. UInt8

Examples

Usage example with date filtering

SELECT FlightDate AS k, count() FROM ontime WHERE indexHint(k = '2025-09-15') GROUP BY k ORDER BY k ASC;

┌──────────k─┬─count()─┐
│ 2025-09-14 │    7071 │
│ 2025-09-15 │   16428 │
│ 2025-09-16 │    1077 │
│ 2025-09-30 │    8167 │
└────────────┴─────────┘

Introduced in version 1.1.

initialQueryID

Returns the ID of the initial current query. Other parameters of a query can be extracted from field initial_query_id in system.query_log.

In contrast to queryID function, initialQueryID returns the same results on different shards.

Syntax

initialQueryID()

Returned value

Returns the ID of the initial current query. String

Examples

Usage example

CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT initialQueryID() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());

┌─count(DISTINCT t)─┐
│                 1 │
└───────────────────┘

Introduced in version 1.1.

initialQueryStartTime

Returns the start time of the initial current query. initialQueryStartTime returns the same results on different shards.

Syntax

initialQueryStartTime()

Returned value

Returns the start time of the initial current query. DateTime

Examples

Usage example

CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT initialQueryStartTime() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());

┌─count(DISTINCT t)─┐
│                 1 │
└───────────────────┘

Introduced in version 25.4.

initializeAggregation

Calculates the result of an aggregate function based on a single value. This function can be used to initialize aggregate functions with combinator -State. You can create states of aggregate functions and insert them to columns of type AggregateFunction or use initialized aggregates as default values.

Syntax

initializeAggregation(aggregate_function, arg1[, arg2, ...])

Arguments

• aggregate_function — Name of the aggregation function to initialize. String
• arg1[, arg2, ...] — Arguments of the aggregate function. Any

Returned value

Returns the result of aggregation for every row passed to the function. The return type is the same as the return type of the function that initializeAggregation takes as a first argument. Any

Examples

Basic usage with uniqState

SELECT uniqMerge(state) FROM (SELECT initializeAggregation('uniqState', number % 3) AS state FROM numbers(10000));

┌─uniqMerge(state)─┐
│                3 │
└──────────────────┘

Usage with sumState and finalizeAggregation

SELECT finalizeAggregation(state), toTypeName(state) FROM (SELECT initializeAggregation('sumState', number % 3) AS state FROM numbers(5));

┌─finalizeAggregation(state)─┬─toTypeName(state)─────────────┐
│                          0 │ AggregateFunction(sum, UInt8) │
│                          1 │ AggregateFunction(sum, UInt8) │
│                          2 │ AggregateFunction(sum, UInt8) │
│                          0 │ AggregateFunction(sum, UInt8) │
│                          1 │ AggregateFunction(sum, UInt8) │
└────────────────────────────┴───────────────────────────────┘

Introduced in version 20.6.

isConstant

Returns whether the argument is a constant expression. A constant expression is an expression whose result is known during query analysis, i.e. before execution. For example, expressions over literals are constant expressions. This function is mostly intended for development, debugging and demonstration.

Syntax

isConstant(x)

Arguments

• x — An expression to check. Any

Returned value

Returns 1 if x is constant, 0 if x is non-constant. UInt8

Examples

Constant expression

SELECT isConstant(x + 1)
FROM (SELECT 43 AS x)

┌─isConstant(plus(x, 1))─┐
│                      1 │
└────────────────────────┘

Constant with function

WITH 3.14 AS pi
SELECT isConstant(cos(pi))

┌─isConstant(cos(pi))─┐
│                   1 │
└─────────────────────┘

Non-constant expression

SELECT isConstant(number)
FROM numbers(1)

┌─isConstant(number)─┐
│                  0 │
└────────────────────┘

Behavior of the now() function

SELECT isConstant(now())

┌─isConstant(now())─┐
│                 1 │
└───────────────────┘

Introduced in version 20.3.

isDecimalOverflow

Checks if a decimal number has too many digits to fit properly in a Decimal data type with given precision.

Syntax

isDecimalOverflow(value[, precision])

Arguments

• value — Decimal value to check. Decimal
• precision — Optional. The precision of the Decimal type. If omitted, the initial precision of the first argument is used. UInt8

Returned value

Returns 1 if the decimal value has more digits than allowed by its precision, 0 if the decimal value satisfies the specified precision. UInt8

Examples

Usage example

SELECT isDecimalOverflow(toDecimal32(1000000000, 0), 9),
       isDecimalOverflow(toDecimal32(1000000000, 0)),
       isDecimalOverflow(toDecimal32(-1000000000, 0), 9),
       isDecimalOverflow(toDecimal32(-1000000000, 0));

┌─isDecimalOverflow(toDecimal32(1000000000, 0), 9)─┬─isDecimalOverflow(toDecimal32(1000000000, 0))─┬─isDecimalOverflow(toDecimal32(-1000000000, 0), 9)─┬─isDecimalOverflow(toDecimal32(-1000000000, 0))─┐
│                                                1 │                                             1 │                                                 1 │                                              1 │
└──────────────────────────────────────────────────┴───────────────────────────────────────────────┴───────────────────────────────────────────────────┴────────────────────────────────────────────────┘

Introduced in version 20.8.

joinGet

Allows you to extract data from a table the same way as from a dictionary. Gets data from Join tables using the specified join key.

:::note Only supports tables created with the ENGINE = Join(ANY, LEFT, <join_keys>) statement. :::

Syntax

joinGet(join_storage_table_name, value_column, join_keys)

Arguments

• join_storage_table_name — An identifier which indicates where to perform the search. The identifier is searched in the default database (see parameter default_database in the config file). To override the default database, use the USE database_name query or specify the database and the table through a dot, like database_name.table_name. String
• value_column — The name of the column of the table that contains required data. const String
• join_keys — A list of join keys. Any

Returned value

Returns list of values corresponded to list of keys. Any

Examples

Usage example

CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1,11)(2,12)(4,13);

SELECT joinGet(db_test.id_val, 'val', toUInt32(1));

┌─joinGet(db_test.id_val, 'val', toUInt32(1))─┐
│                                          11 │
└─────────────────────────────────────────────┘

Usage with table from current database

USE db_test;
SELECT joinGet(id_val, 'val', toUInt32(2));

┌─joinGet(id_val, 'val', toUInt32(2))─┐
│                                  12 │
└─────────────────────────────────────┘

Using arrays as join keys

CREATE TABLE some_table (id1 UInt32, id2 UInt32, name String) ENGINE = Join(ANY, LEFT, id1, id2);
INSERT INTO some_table VALUES (1, 11, 'a') (2, 12, 'b') (3, 13, 'c');

SELECT joinGet(some_table, 'name', 1, 11);

┌─joinGet(some_table, 'name', 1, 11)─┐
│ a                                  │
└────────────────────────────────────┘

Introduced in version 18.16.

joinGetOrNull

Allows you to extract data from a table the same way as from a dictionary. Gets data from Join tables using the specified join key. Unlike joinGet it returns NULL when the key is missing.

:::note Only supports tables created with the ENGINE = Join(ANY, LEFT, <join_keys>) statement. :::

Syntax

joinGetOrNull(join_storage_table_name, value_column, join_keys)

Arguments

• join_storage_table_name — An identifier which indicates where to perform the search. The identifier is searched in the default database (see parameter default_database in the config file). To override the default database, use the USE database_name query or specify the database and the table through a dot, like database_name.table_name. String
• value_column — The name of the column of the table that contains required data. const String
• join_keys — A list of join keys. Any

Returned value

Returns a list of values corresponding to the list of keys, or NULL if a key is not found. Any

Examples

Usage example

CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1,11)(2,12)(4,13);

SELECT joinGetOrNull(db_test.id_val, 'val', toUInt32(1)), joinGetOrNull(db_test.id_val, 'val', toUInt32(999));

┌─joinGetOrNull(db_test.id_val, 'val', toUInt32(1))─┬─joinGetOrNull(db_test.id_val, 'val', toUInt32(999))─┐
│                                                11 │                                                ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────┴─────────────────────────────────────────────────────┘

Introduced in version 20.4.

lowCardinalityIndices

Returns the position of a value in the dictionary of a LowCardinality column. Positions start at 1. Since LowCardinality have per-part dictionaries, this function may return different positions for the same value in different parts.

Syntax

lowCardinalityIndices(col)

Arguments

• col — A low cardinality column. LowCardinality

Returned value

The position of the value in the dictionary of the current part. UInt64

Examples

Usage examples

DROP TABLE IF EXISTS test;
CREATE TABLE test (s LowCardinality(String)) ENGINE = Memory;

-- create two parts:

INSERT INTO test VALUES ('ab'), ('cd'), ('ab'), ('ab'), ('df');
INSERT INTO test VALUES ('ef'), ('cd'), ('ab'), ('cd'), ('ef');

SELECT s, lowCardinalityIndices(s) FROM test;

┌─s──┬─lowCardinalityIndices(s)─┐
│ ab │                        1 │
│ cd │                        2 │
│ ab │                        1 │
│ ab │                        1 │
│ df │                        3 │
└────┴──────────────────────────┘
┌─s──┬─lowCardinalityIndices(s)─┐
│ ef │                        1 │
│ cd │                        2 │
│ ab │                        3 │
│ cd │                        2 │
│ ef │                        1 │
└────┴──────────────────────────┘

Introduced in version 18.12.

lowCardinalityKeys

Returns the dictionary values of a LowCardinality column. If the block is smaller or larger than the dictionary size, the result will be truncated or extended with default values. Since LowCardinality have per-part dictionaries, this function may return different dictionary values in different parts.

Syntax

lowCardinalityKeys(col)

Arguments

• col — A low cardinality column. LowCardinality

Returned value

Returns the dictionary keys. UInt64

Examples

lowCardinalityKeys

DROP TABLE IF EXISTS test;
CREATE TABLE test (s LowCardinality(String)) ENGINE = Memory;

-- create two parts:

INSERT INTO test VALUES ('ab'), ('cd'), ('ab'), ('ab'), ('df');
INSERT INTO test VALUES ('ef'), ('cd'), ('ab'), ('cd'), ('ef');

SELECT s, lowCardinalityKeys(s) FROM test;

┌─s──┬─lowCardinalityKeys(s)─┐
│ ef │                       │
│ cd │ ef                    │
│ ab │ cd                    │
│ cd │ ab                    │
│ ef │                       │
└────┴───────────────────────┘
┌─s──┬─lowCardinalityKeys(s)─┐
│ ab │                       │
│ cd │ ab                    │
│ ab │ cd                    │
│ ab │ df                    │
│ df │                       │
└────┴───────────────────────┘

Introduced in version 18.12.

materialize

Turns a constant into a full column containing a single value. Full columns and constants are represented differently in memory. Functions usually execute different code for normal and constant arguments, although the result should typically be the same. This function can be used to debug this behavior.

Syntax

materialize(x)

Arguments

• x — A constant. Any

Returned value

Returns a full column containing the constant value. Any

Examples

Usage example

-- In the example below the `countMatches` function expects a constant second argument.
-- This behaviour can be debugged by using the `materialize` function to turn a constant into a full column,
-- verifying that the function throws an error for a non-constant argument.

SELECT countMatches('foobarfoo', 'foo');
SELECT countMatches('foobarfoo', materialize('foo'));

2
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type of argument #2 'pattern' of function countMatches, expected constant String, got String

Introduced in version 1.1.

minSampleSizeContinuous

Calculates the minimum required sample size for an A/B test comparing means of a continuous metric in two samples.

Uses the formula described in this article. Assumes equal sizes of treatment and control groups. Returns the required sample size for one group (i.e. the sample size required for the whole experiment is twice the returned value). Also assumes equal variance of the test metric in treatment and control groups.

Syntax

minSampleSizeContinuous(baseline, sigma, mde, power, alpha)

Arguments

• baseline — Baseline value of a metric. (U)Int* or Float*
• sigma — Baseline standard deviation of a metric. (U)Int* or Float*
• mde — Minimum detectable effect (MDE) as percentage of the baseline value (e.g. for a baseline value 112.25 the MDE 0.03 means an expected change to 112.25 ± 112.25*0.03). (U)Int* or Float*
• power — Required statistical power of a test (1 - probability of Type II error). (U)Int* or Float*
• alpha — Required significance level of a test (probability of Type I error). (U)Int* or Float*

Returned value

Returns a named Tuple with 3 elements: minimum_sample_size, detect_range_lower and detect_range_upper. These are respectively: the required sample size, the lower bound of the range of values not detectable with the returned required sample size, calculated as baseline * (1 - mde), and the upper bound of the range of values not detectable with the returned required sample size, calculated as baseline * (1 + mde) (Float64). Tuple(Float64, Float64, Float64)

Examples

minSampleSizeContinuous

SELECT minSampleSizeContinuous(112.25, 21.1, 0.03, 0.80, 0.05) AS sample_size

(616.2931945826209,108.8825,115.6175)

Introduced in version 23.10.

minSampleSizeConversion

Calculates minimum required sample size for an A/B test comparing conversions (proportions) in two samples.

Uses the formula described in this article. Assumes equal sizes of treatment and control groups. Returns the sample size required for one group (i.e. the sample size required for the whole experiment is twice the returned value).

Syntax

minSampleSizeConversion(baseline, mde, power, alpha)

Arguments

• baseline — Baseline conversion. Float*
• mde — Minimum detectable effect (MDE) as percentage points (e.g. for a baseline conversion 0.25 the MDE 0.03 means an expected change to 0.25 ± 0.03). Float*
• power — Required statistical power of a test (1 - probability of Type II error). Float*
• alpha — Required significance level of a test (probability of Type I error). Float*

Returned value

Returns a named Tuple with 3 elements: minimum_sample_size, detect_range_lower, detect_range_upper. These are, respectively: the required sample size, the lower bound of the range of values not detectable with the returned required sample size, calculated as baseline - mde, the upper bound of the range of values not detectable with the returned required sample size, calculated as baseline + mde. Tuple(Float64, Float64, Float64)

Examples

minSampleSizeConversion

SELECT minSampleSizeConversion(0.25, 0.03, 0.80, 0.05) AS sample_size

(3396.077603219163,0.22,0.28)

Introduced in version 22.6.

neighbor

Returns a value from a column at a specified offset from the current row. This function is deprecated and error-prone because it operates on the physical order of data blocks which may not correspond to the logical order expected by users. Consider using proper window functions instead.

The function can be enabled by setting allow_deprecated_error_prone_window_functions = 1.

Syntax

neighbor(column, offset[, default_value])

Arguments

• column — The source column. Any
• offset — The offset from the current row. Positive values look forward, negative values look backward. Integer
• default_value — Optional. The value to return if the offset goes beyond the data bounds. If not specified, uses the default value for the column type. Any

Returned value

Returns a value from the specified offset, or default if out of bounds. Any

Examples

Usage example

SELECT number, neighbor(number, 2) FROM system.numbers LIMIT 10;

┌─number─┬─neighbor(number, 2)─┐
│      0 │                   2 │
│      1 │                   3 │
│      2 │                   4 │
│      3 │                   5 │
│      4 │                   6 │
│      5 │                   7 │
│      6 │                   8 │
│      7 │                   9 │
│      8 │                   0 │
│      9 │                   0 │
└────────┴─────────────────────┘

With default value

SELECT number, neighbor(number, 2, 999) FROM system.numbers LIMIT 10;

┌─number─┬─neighbor(number, 2, 999)─┐
│      0 │                        2 │
│      1 │                        3 │
│      2 │                        4 │
│      3 │                        5 │
│      4 │                        6 │
│      5 │                        7 │
│      6 │                        8 │
│      7 │                        9 │
│      8 │                      999 │
│      9 │                      999 │
└────────┴──────────────────────────┘

Introduced in version 20.1.

nested

This is a function used internally by the RawTree engine and not meant to be used directly.

Returns the array of tuples from multiple arrays.

The first argument must be a constant array of Strings determining the names of the resulting Tuple. The other arguments must be arrays of the same size.

Examples

nested

SELECT nested(['keys', 'values'], ['key_1', 'key_2'], ['value_1','value_2'])

normalizeQuery

Replaces literals, sequences of literals and complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs) with placeholder ?.

Syntax

normalizeQuery(x)

Arguments

• x — Sequence of characters. String

Returned value

Returns the given sequence of characters with placeholders. String

Examples

Usage example

SELECT normalizeQuery('[1, 2, 3, x]') AS query

┌─query────┐
│ [?.., x] │
└──────────┘

Introduced in version 20.8.

normalizeQueryKeepNames

Replaces literals and sequences of literals with placeholder ? but does not replace complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs). This helps better analyze complex query logs.

Syntax

normalizeQueryKeepNames(x)

Arguments

• x — Sequence of characters. String

Returned value

Returns the given sequence of characters with placeholders. String

Examples

Usage example

SELECT normalizeQuery('SELECT 1 AS aComplexName123'), normalizeQueryKeepNames('SELECT 1 AS aComplexName123')

┌─normalizeQuery('SELECT 1 AS aComplexName123')─┬─normalizeQueryKeepNames('SELECT 1 AS aComplexName123')─┐
│ SELECT ? AS `?`                               │ SELECT ? AS aComplexName123                            │
└───────────────────────────────────────────────┴────────────────────────────────────────────────────────┘

Introduced in version 21.2.

normalizedQueryHash

Returns identical 64 bit hash values without the values of literals for similar queries. Can be helpful in analyzing query logs.

Syntax

normalizedQueryHash(x)

Arguments

• x — Sequence of characters. String

Returned value

Returns a 64 bit hash value. UInt64

Examples

Usage example

SELECT normalizedQueryHash('SELECT 1 AS `xyz`') != normalizedQueryHash('SELECT 1 AS `abc`') AS res

┌─res─┐
│   1 │
└─────┘

Introduced in version 20.8.

normalizedQueryHashKeepNames

Like normalizedQueryHash it returns identical 64 bit hash values without the values of literals for similar queries, but it does not replace complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs) with a placeholder before hashing. Can be helpful in analyzing query logs.

Syntax

normalizedQueryHashKeepNames(x)

Arguments

• x — Sequence of characters. String

Returned value

Returns a 64 bit hash value. UInt64

Examples

Usage example