その他の関数

FQDN()

SELECT fqdn()

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

MACNumToString(num)

SELECT MACNumToString(149809441867716) AS mac_address;

┌─mac_address───────┐
│ 88:40:3A:91:07:C4 │
└───────────────────┘

MACStringToNum(s)

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

1108152157446

MACStringToOUI(s)

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

20566

authenticatedUser()

CREATE USER u1;
EXECUTE AS u1 SELECT currentUser(), authenticatedUser();
DROP USER u1;

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

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

CREATE TABLE hits (EventTime DateTime('UTC')) ENGINE = Memory;

-- One row per page view, spread over the hours of a day.
INSERT INTO hits
SELECT toDateTime('2026-01-01 00:00:00', 'UTC') + toIntervalHour(hour)
FROM (
    SELECT
        number AS hour,
        [293, 181, 115, 85, 69, 78, 113, 171, 278, 391, 458, 494, 510, 523, 540, 528, 539, 524, 506, 521, 522, 542, 494, 400][number + 1] AS count
    FROM numbers(24)
)
ARRAY JOIN range(count);

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

┌──h─┬───c─┬─bar────────────────┐
│  0 │ 293 │ █████████▊         │
│  1 │ 181 │ ██████             │
│  2 │ 115 │ ███▊               │
│  3 │  85 │ ██▊                │
│  4 │  69 │ ██▎                │
│  5 │  78 │ ██▌                │
│  6 │ 113 │ ███▊               │
│  7 │ 171 │ █████▋             │
│  8 │ 278 │ █████████▎         │
│  9 │ 391 │ █████████████      │
│ 10 │ 458 │ ███████████████▎   │
│ 11 │ 494 │ ████████████████▍  │
│ 12 │ 510 │ █████████████████  │
│ 13 │ 523 │ █████████████████▍ │
│ 14 │ 540 │ ██████████████████ │
│ 15 │ 528 │ █████████████████▌ │
│ 16 │ 539 │ █████████████████▉ │
│ 17 │ 524 │ █████████████████▍ │
│ 18 │ 506 │ ████████████████▊  │
│ 19 │ 521 │ █████████████████▎ │
│ 20 │ 522 │ █████████████████▍ │
│ 21 │ 542 │ ██████████████████ │
│ 22 │ 494 │ ████████████████▍  │
│ 23 │ 400 │ █████████████▎     │
└────┴─────┴────────────────────┘

blockNumber()

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

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

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

SELECT blockSerializedSize(maxState(1)) AS x;

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

blockSize()

SELECT blockSize()
FROM system.numbers LIMIT 5

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

buildId()

SELECT buildId()

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

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

SELECT byteSize('string')

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

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

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

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

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

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

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

4.695691092573497

colorOKLABToSRGB(tuple [, gamma])

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

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

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) │
└───────────┘

colorOKLCHToSRGB(tuple [, gamma])

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

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

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) │
└──────────────┘

colorSRGBToOKLAB(tuple[, gamma])

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

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

colorSRGBToOKLCH(tuple[, gamma])

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

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

connectionId()

SELECT connectionId();

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

countDigits(x)

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 │
└────────────────────────────────┴─────────────────────────────────┴─────────────────────────────────┴──────────────────────────────────┴──────────────────────────────────┴───────────────────────────────────┘

currentDatabase()

SELECT currentDatabase()

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

SELECT CURRENT_DATABASE

┌─CURRENT_DATABASE─┐
│ default          │
└──────────────────┘

currentHandler()

SELECT currentHandler()

currentProfiles()

SELECT currentProfiles();

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

currentQueryID()

SELECT currentQueryID();

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

currentRequestURL()

SELECT currentRequestURL()

currentRoles()

SELECT currentRoles();

┌─currentRoles()─┐
│ []             │
└────────────────┘

currentSchemas(bool)

SELECT currentSchemas(true)

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

currentUser()

SELECT currentUser()

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

SELECT CURRENT_USER

┌─CURRENT_USER─┐
│ default      │
└──────────────┘

defaultProfiles()

SELECT defaultProfiles();

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

defaultRoles()

SELECT defaultRoles();

┌─defaultRoles()─┐
│ []             │
└────────────────┘

defaultValueOfArgumentType(expression)

SELECT defaultValueOfArgumentType(CAST(1 AS Int8));

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

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

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

defaultValueOfTypeName(type)

SELECT defaultValueOfTypeName('Int8');

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

SELECT defaultValueOfTypeName('Nullable(Int8)');

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

digits(n, offset[, length])

SELECT digits(1234567890, 7)

7890

SELECT digits(1234567890, 7, 2)

78

SELECT digits(1234567890, -3)

890

SELECT digits(1234567890, 3, -2)

345678

SELECT digits(1234567890, 11)

0

displayName()

SELECT displayName();

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

dumpColumnStructure(x)

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))                  │
└──────────────────────────────────────────────────────────────┘

enabledProfiles()

SELECT enabledProfiles();

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

enabledRoles()

SELECT enabledRoles();

┌─enabledRoles()─┐
│ []             │
└────────────────┘

errorCodeToName(error_code)

SELECT errorCodeToName(252);

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

file(path[, default])

INSERT INTO FUNCTION file('a.txt', 'RawBLOB') SELECT 'Hello' SETTINGS engine_file_truncate_on_insert = 1;
INSERT INTO FUNCTION file('b.txt', 'RawBLOB') SELECT 'World!' SETTINGS engine_file_truncate_on_insert = 1;

CREATE TABLE data (a String, b String) ENGINE = Memory;
INSERT INTO data SELECT file('a.txt'), file('b.txt');

SELECT * FROM data;

┌─a─────┬─b──────┐
│ Hello │ World! │
└───────┴────────┘

filesystemAvailable([disk_name])

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

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

filesystemCapacity([disk_name])

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

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

filesystemUnreserved([disk_name])

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

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

finalizeAggregation(state)

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

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

SET allow_deprecated_error_prone_window_functions = 1;
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 │
└────────┴─────────────┴────────────────┘

flipCoordinates(geometry)

SELECT flipCoordinates((1.0, 2.0));

(2,1)

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

[(2,1),(4,3)]

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

[[(2,1),(4,3)],[(6,5),(8,7)]]

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

(20,10)

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

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

formatQuery(query)

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

SELECT\n    a,\n    b\nFROM tab\nWHERE (a > 3) AND (b < 3)

formatQueryFromJSON(json[, original_query])

SELECT formatQueryFromJSON(parseQueryToJSON('SELECT a, b FROM t WHERE x > 1'));

┌─formatQueryFromJSON(parseQueryToJSON('SELECT a, b FROM t WHERE x > 1'))─┐
│ SELECT a, b FROM t WHERE x > 1                                          │
└─────────────────────────────────────────────────────────────────────────┘

SELECT formatQueryFromJSON(parseQueryToJSON('SELECT a FROM t'), 'SELECT /* comment */ a FROM t');

┌─formatQueryFromJSON(parseQueryToJSON('SELECT a FROM t'), 'SELECT /* comment */ a FROM t')─┐
│ SELECT /* comment */ a FROM t                                                             │
└───────────────────────────────────────────────────────────────────────────────────────────┘

formatQueryOrNull(query)

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

SELECT\n    a,\n    b\nFROM tab\nWHERE (a > 3) AND (b < 3)

formatQuerySingleLine(query)

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(query)

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(value[, precision])

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 │
└────────────────┴───────────┘

SELECT
    formatReadableDecimalSize(192851925, 0) AS no_decimals,
    formatReadableDecimalSize(192851925, 4) AS four_decimals

┌─no_decimals─┬─four_decimals─┐
│ 193 MB      │ 192.8519 MB   │
└─────────────┴───────────────┘

formatReadableQuantity(value[, precision])

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    │
└────────────────┴───────────────────┘

SELECT
    formatReadableQuantity(98765432101234, 0) AS no_decimals,
    formatReadableQuantity(98765432101234, 4) AS four_decimals

┌─no_decimals─┬─four_decimals────┐
│ 99 trillion │ 98.7654 trillion │
└─────────────┴──────────────────┘

formatReadableSize(value[, precision])

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 │
└────────────────┴────────────┘

SELECT
    formatReadableSize(192851925, 0) AS no_decimals,
    formatReadableSize(192851925, 4) AS four_decimals

┌─no_decimals─┬─four_decimals─┐
│ 184 MiB     │ 183.9179 MiB  │
└─────────────┴───────────────┘

formatReadableTimeDelta(column[, maximum_unit, minimum_unit])

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 │
└───────────┴─────────────────────────────────────────────────────────────────┘

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 │
└───────────┴────────────────────────────────┘

SELECT formatReadableTimeDelta(INTERVAL 12345 SECOND) AS time_delta

┌─time_delta─────────────────────────┐
│ 3 hours, 25 minutes and 45 seconds │
└────────────────────────────────────┘

fuzzQuery(query)

SET allow_fuzz_query_functions = 1; SELECT fuzzQuery('SELECT 1');

generateRandomStructure([number_of_columns, seed])

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

SELECT generateRandomStructure(1)

c1 Map(UInt256, UInt16)

SELECT generateRandomStructure(NULL, 33)

c1 DateTime, c2 Enum8(\'c2V0\' = -123, \'c2V1\' = 94, \'c2V2\' = -105, \'c2V3\' = 67), c3 Nullable(UInt8), c4 String, c5 Nested(e1 IPv4, e2 UInt8, e3 UInt16, e4 UInt16, e5 Int32, e6 Map(Date, Decimal256(71))), c6 UInt32, c7 Bool, c8 Float64

generateSerialID(series_identifier[, start_value])

SELECT generateSerialID('id1')

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

SELECT generateSerialID('id1')

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

CREATE TABLE test_table (CounterID UInt32, UserID UInt32, ver UInt32) ENGINE = Memory;
INSERT INTO test_table VALUES (1, 3, 3), (1, 1, 1), (1, 2, 2), (1, 5, 5), (1, 4, 4);

SELECT *, generateSerialID('id1') FROM test_table

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

SELECT generateSerialID('id2', 100)

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

SELECT generateSerialID('id2', 100)

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

echo "SELECT getClientHTTPHeader('Content-Type') SETTINGS allow_get_client_http_header = 1" | \
    curl 'http://localhost:8123/' --data-binary @- -H 'Content-Type: application/x-www-form-urlencoded'

getClientHTTPHeader(name)

-- Over a non-HTTP interface (such as `clickhouse-client` or `clickhouse-local`) there are
-- no request headers, so the function returns an empty string. See the description above
-- for an HTTP example that returns the actual header value.
SELECT getClientHTTPHeader('Content-Type') SETTINGS allow_get_client_http_header = 1

getMacro(name)

SELECT getMacro('test');

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

getMaxTableNameLengthForDatabase(database_name)

SELECT getMaxTableNameLengthForDatabase('default');

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

getMergeTreeSetting(setting_name)

SELECT getMergeTreeSetting('index_granularity');

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

getOSKernelVersion()

SELECT getOSKernelVersion();

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

getServerPort(port_name)

SELECT getServerPort('tcp_port');

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

getServerSetting(setting_name')

SELECT getServerSetting('allow_use_jemalloc_memory');

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

getSetting(setting_name)

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

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

getSettingOrDefault(setting_name, default_value)

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

my_value
100
\N

getSizeOfEnumType(x)

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

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

getSubcolumn(nested_value, subcolumn_name)

SELECT getSubcolumn(array_col, 'size0'), getSubcolumn(tuple_col, 'elem_name')
FROM values('array_col Array(UInt32), tuple_col Tuple(elem_name String)', ([1, 2, 3], tuple('abc')));

┌─getSubcolumn(array_col, 'size0')─┬─getSubcolumn(tuple_col, 'elem_name')─┐
│                                3 │ abc                                  │
└──────────────────────────────────┴──────────────────────────────────────┘

getTypeSerializationStreams(col)

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

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

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

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

globalVariable(name)

SELECT globalVariable('max_allowed_packet')

67108864

hasColumnInTable(database, table, column)

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

1

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

0

hasThreadFuzzer()

SELECT hasThreadFuzzer()

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

highlightQuery(query)

SELECT highlightQuery('SELECT 1')

[(0,6,'keyword'),(7,8,'number')]

hostName()

SELECT hostName()

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

icebergBucket(N, value)

SELECT icebergBucket(5, 1.0 :: Float32)

4

icebergTruncate(N, value)

SELECT icebergTruncate(3, 'iceberg')

ice

identity(x)

SELECT identity(42)

42

ignore(x)

SELECT ignore(0, 'ClickHouse', NULL)

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

indexHint(expression)

-- `index_granularity` is lowered to 8 here only to keep the example small enough to follow.
-- Do not change it in production: the default of 8192 is what makes the index sparse and cheap,
-- and a small value makes the index large and slows queries down.
CREATE TABLE ontime (FlightDate Date, Carrier String)
ENGINE = MergeTree ORDER BY FlightDate
SETTINGS index_granularity = 8;

-- Ten flights on each of four days, so a granule of eight rows spans more than one day.
INSERT INTO ontime SELECT toDate('2025-09-14') + intDiv(number, 10), 'AA' FROM numbers(40);

-- The granules that hold the rows of 2025-09-15 also hold rows of the neighbouring days,
-- and `indexHint` returns all of them.
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 │       2 │
│ 2025-09-15 │      10 │
│ 2025-09-16 │       4 │
└────────────┴─────────┘

initialQueryID()

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());

┌─countDistinct(t)─┐
│                1 │
└──────────────────┘

initialQueryStartTime()

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());

┌─countDistinct(t)─┐
│                1 │
└──────────────────┘

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

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

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

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) │
└────────────────────────────┴───────────────────────────────┘

isConstant(x)

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

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

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

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

SELECT isConstant(number)
FROM numbers(1)

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

SELECT isConstant(now())

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

isDecimalOverflow(value[, precision])

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 │
└──────────────────────────────────────────────────┴───────────────────────────────────────────────┴───────────────────────────────────────────────────┴────────────────────────────────────────────────┘

joinGet(join_storage_table_name, value_column, join_keys)

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

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

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

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

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

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', toUInt32(1), toUInt32(11));

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

joinGetOrNull(join_storage_table_name, value_column, join_keys)

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

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

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

lowCardinalityIndices(col)

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');

-- the order the blocks are read in is not defined, so sort the result to make it reproducible:

SELECT s, lowCardinalityIndices(s) AS index FROM test ORDER BY s, index;

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

lowCardinalityKeys(col)

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 │                       │
└────┴───────────────────────┘

materialize(x)

-- 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');

2

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

Received exception:
Code: 44. DB::Exception: A value of illegal type was provided as 2nd argument 'pattern' to function 'countMatches'. Expected: constant String, got: String. (ILLEGAL_COLUMN)

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

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

(616.2931945826209,108.8825,115.6175)

minSampleSizeConversion(baseline, mde, power, alpha)

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

(3396.077603219163,0.22,0.28)

neighbor(column, offset[, default_value])

SET allow_deprecated_error_prone_window_functions = 1;
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 │
└────────┴─────────────────────┘

SET allow_deprecated_error_prone_window_functions = 1;
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 │
└────────┴──────────────────────────┘

normalizeQuery(x)

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

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

normalizeQueryKeepNames(x)

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                            │
└───────────────────────────────────────────────┴────────────────────────────────────────────────────────┘

normalizedQueryHash(x)

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

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

normalizedQueryHashKeepNames(x)

SELECT normalizedQueryHash('SELECT 1 AS `xyz123`') != normalizedQueryHash('SELECT 1 AS `abc123`') AS normalizedQueryHash;
SELECT normalizedQueryHashKeepNames('SELECT 1 AS `xyz123`') != normalizedQueryHashKeepNames('SELECT 1 AS `abc123`') AS normalizedQueryHashKeepNames;

┌─normalizedQueryHash─┐
│                   0 │
└─────────────────────┘
┌─normalizedQueryHashKeepNames─┐
│                            1 │
└──────────────────────────────┘

obfuscateQuery(query[, tag])

SELECT obfuscateQuery('SELECT name, age FROM users WHERE age > 30')

SELECT fruit, number FROM table WHERE number > 12

SELECT obfuscateQuery('SELECT * FROM t', 1), obfuscateQuery('SELECT * FROM t', 2)

SELECT a FROM b, SELECT c FROM d

SELECT obfuscateQuery('SELECT 1') AS a, obfuscateQuery('SELECT 1') AS b

SELECT 1	SELECT 1

obfuscateQueryWithSeed(query, seed)

SELECT obfuscateQueryWithSeed('SELECT name FROM users', 42)

SELECT name FROM users

SELECT obfuscateQueryWithSeed('SELECT id, value FROM data', 'myseed')

SELECT id, value FROM data

SELECT obfuscateQueryWithSeed('SELECT 1', 100) = obfuscateQueryWithSeed('SELECT 1', 100)

1

parseQueryToJSON(sql)

SELECT formatQueryFromJSON(parseQueryToJSON('SELECT 1'));

┌─formatQueryFromJSON(parseQueryToJSON('SELECT 1'))─┐
│ SELECT 1                                          │
└───────────────────────────────────────────────────┘

parseReadableSize(x)

SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB']) AS readable_sizes, parseReadableSize(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
└────────────────┴─────────┘

parseReadableSizeOrNull(x)

SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes, parseReadableSizeOrNull(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
│ invalid        │    ᴺᵁᴸᴸ │
└────────────────┴─────────┘

parseReadableSizeOrZero(x)

SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes, parseReadableSizeOrZero(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
│ invalid        │       0 │
└────────────────┴─────────┘

parseTimeDelta(timestr)

SELECT parseTimeDelta('11s+22min')

┌─parseTimeDelta('11s+22min')─┐
│                        1331 │
└─────────────────────────────┘

SELECT parseTimeDelta('1yr2mo')

┌─parseTimeDelta('1yr2mo')─┐
│                 36806400 │
└──────────────────────────┘

partitionId(column1[, column2, ...])

DROP TABLE IF EXISTS tab;

CREATE TABLE tab
(
  i int,
  j int
)
ENGINE = MergeTree
PARTITION BY i
ORDER BY tuple();

INSERT INTO tab VALUES (1, 1), (1, 2), (1, 3), (2, 4), (2, 5), (2, 6);

SELECT i, j, partitionId(i), _partition_id FROM tab ORDER BY i, j;

┌─i─┬─j─┬─partitionId(i)─┬─_partition_id─┐
│ 1 │ 1 │ 1              │ 1             │
│ 1 │ 2 │ 1              │ 1             │
│ 1 │ 3 │ 1              │ 1             │
│ 2 │ 4 │ 2              │ 2             │
│ 2 │ 5 │ 2              │ 2             │
│ 2 │ 6 │ 2              │ 2             │
└───┴───┴────────────────┴───────────────┘

pgGetUserById(oid)

SELECT pg_get_userbyid(10)

┌─pg_get_userbyid(10)─┐
│ default             │
└─────────────────────┘

pgTableIsVisible(oid)

SELECT pg_table_is_visible(0)

┌─pg_table_is_visible(0)─┐
│                      1 │
└────────────────────────┘

queryID()

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

┌─countDistinct(t)─┐
│                3 │
└──────────────────┘

revision()

SELECT revision()

┌─revision()─┐
│      54485 │
└────────────┘

rowNumberInAllBlocks()

-- The data is processed in blocks of two rows: rowNumberInBlock restarts from 0 in every block,
-- while rowNumberInAllBlocks keeps counting across them.
SELECT
    number,
    rowNumberInBlock(),
    rowNumberInAllBlocks()
FROM system.numbers
LIMIT 6
SETTINGS max_block_size = 2

┌─number─┬─rowNumberInBlock()─┬─rowNumberInAllBlocks()─┐
│      0 │                  0 │                      0 │
│      1 │                  1 │                      1 │
│      2 │                  0 │                      2 │
│      3 │                  1 │                      3 │
│      4 │                  0 │                      4 │
│      5 │                  1 │                      5 │
└────────┴────────────────────┴────────────────────────┘

rowNumberInBlock()

SELECT rowNumberInBlock()
FROM
(
    SELECT *
    FROM system.numbers_mt
    LIMIT 10
) SETTINGS max_block_size = 2

┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
│                  0 │
│                  1 │
│                  0 │
│                  1 │
│                  0 │
│                  1 │
│                  0 │
│                  1 │
└────────────────────┘

runningAccumulate(agg_state[, grouping])

SET allow_deprecated_error_prone_window_functions = 1;
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 │
└────────┴─────────────┴────────────────┘

runningConcurrency(start, end)

CREATE TABLE example_table (start Date, end Date) ENGINE = Memory;
INSERT INTO example_table VALUES ('2025-03-03', '2025-03-11'), ('2025-03-06', '2025-03-08'), ('2025-03-07', '2025-03-09'), ('2025-03-11', '2025-03-12');

SELECT start, runningConcurrency(start, end) FROM example_table;

┌──────start─┬─runningConcurrency(start, end)─┐
│ 2025-03-03 │                              1 │
│ 2025-03-06 │                              2 │
│ 2025-03-07 │                              3 │
│ 2025-03-11 │                              1 │
└────────────┴────────────────────────────────┘

runningDifference(x)

SET allow_deprecated_error_prone_window_functions = 1;

CREATE TABLE events
(
    EventID UInt32,
    EventDate Date,
    EventTime DateTime
)
ENGINE = Memory;

INSERT INTO events VALUES
    (1106, '2025-11-24', '2025-11-24 00:00:04'),
    (1107, '2025-11-24', '2025-11-24 00:00:05'),
    (1108, '2025-11-24', '2025-11-24 00:00:05'),
    (1109, '2025-11-24', '2025-11-24 00:00:09'),
    (1110, '2025-11-24', '2025-11-24 00:00:10');

SELECT
    EventID,
    EventTime,
    runningDifference(EventTime) AS delta
FROM
(
    SELECT
        EventID,
        EventTime
    FROM events
    WHERE EventDate = '2025-11-24'
    ORDER BY EventTime ASC, EventID ASC
    LIMIT 5
);

┌─EventID─┬───────────EventTime─┬─delta─┐
│    1106 │ 2025-11-24 00:00:04 │     0 │
│    1107 │ 2025-11-24 00:00:05 │     1 │
│    1108 │ 2025-11-24 00:00:05 │     0 │
│    1109 │ 2025-11-24 00:00:09 │     4 │
│    1110 │ 2025-11-24 00:00:10 │     1 │
└─────────┴─────────────────────┴───────┘

SET allow_deprecated_error_prone_window_functions = 1;
SELECT
    number,
    runningDifference(number + 1) AS diff
FROM numbers(100000)
WHERE diff != 1;