assumeNotNull #

The function returns the equivalent NotNullable Value of a Nullable Type. If the raw value is NULL, the result is implementation-specific. See also the ifNull and coalesce functions.
Syntax

assumeNotNull(x)

Param:

• x— raw value.

Return value

• If not NULL, returns the raw value of Not Nullable Type.
• If the raw value is NULL, the result is implementation-specific.

Example

CREATE TABLE IF NOT EXISTS test.functionAssumeNotNull ( x Int8,  y Nullable(Int8)) ENGINE = CnchMergeTree ORDER BY x; 
INSERT INTO test.functionAssumeNotNull VALUES (1,NULL),(2,3); 
SELECT * FROM test.functionAssumeNotNull;

┌─x─┬─y────┐ 
│ 1 │ ᴺᵁᴸᴸ │ 
│ 2 │ 3    │ 
└───┴──────┘

Apply the assumeNotNull function to the y Column.

SELECT assumeNotNull(y),toTypeName(assumeNotNull(y))  FROM test.functionAssumeNotNull;

┌─assumeNotNull(y)─┬─toTypeName(assumeNotNull(y))─┐ 
│ 0                │ Int8                         │ 
│ 3                │ Int8                         │ 
└──────────────────┴──────────────────────────────┘ 
Plain text

coalesce #

Checks from left to right whether a NULL Param is passed and returns the first NotNULL Param.
Syntax

coalesce(x,...)

Param

• Any Quantity of Params of Not composite Type. All Params' Data type must be compatible.

Return value

• The first NotNULL argument.
• NULL, if all Params are NULL.

Example
Consider a contact List, where multiple Method / way to contact the Client may be specified.

CREATE TABLE IF NOT EXISTS test.functionCoalesce (name String, mail Nullable(String), phone Nullable(String), icq Nullable(UInt32)) ENGINE=CnchMergeTree ORDER BY name; 
INSERT INTO test.functionCoalesce VALUES ('client 1', NULL, '123-45-67', 123), ('client 2', NULL, NULL, NULL); 
SELECT * FROM test.functionCoalesce;

┌─name─────┬─mail─┬─phone─────┬─icq──┐ 
│ client 1 │ ᴺᵁᴸᴸ │ 123-45-67 │ 123  │ 
│ client 2 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ      │ ᴺᵁᴸᴸ │ 
└──────────┴──────┴───────────┴──────┘

The Type of the mail Field and phone Field is String, but the Type of the icq Field is UInt32, so it needs to be converted to String.
Get the first Available contact Method / way for the Client from the contact List:

SELECT name, coalesce(mail, phone, CAST(icq,'Nullable(String)')) FROM test.functionCoalesce;

┌─name─────┬─coalesce(mail, phone, CAST(icq, 'Nullable(String)'))─┐ 
│ client 1 │ 123-45-67                                            │ 
│ client 2 │ ᴺᵁᴸᴸ                                                 │ 
└──────────┴──────────────────────────────────────────────────────┘

ifNull #

If the primary Param is NULL, returns another Value.
Syntax

ifNull(x,alt)

Param:

• x— Value to check for NULL.
• altx—If NULL, the function returns this Value.

Return value

• x if x is Not NULL.
• alt if its Value is NULL. xNULL

Example

SELECT ifNull('a', 'b');

┌─ifNull('a', 'b')─┐ 
│ a                │ 
└──────────────────┘

SELECT ifNull(NULL, 'b');

┌─ifNull(NULL, 'b')─┐ 
│ b                 │ 
└───────────────────┘

isNotNull #

Checks whether the Param is NULL.
Syntax

isNotNull(x)

Param:

• x— Value with Not composite Data type.

Return value

• 0 if x is NULL.
• 1 if x is Not NULL.

Example
Input table:

CREATE TABLE IF NOT EXISTS test.functionIsNotNull (x UInt8, y Nullable(UInt8)) ENGINE=CnchMergeTree ORDER BY x; 
INSERT INTO test.functionIsNotNull VALUES (1, NULL),(2,3); 
SELECT * FROM test.functionIsNotNull;

┌─x─┬─y────┐ 
│ 1 │ ᴺᵁᴸᴸ │ 
│ 2 │ 3    │ 
└───┴──────┘

Query:

SELECT x FROM test.functionIsNotNull WHERE isNotNull(y);

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

isNull #

Checks whether the Param is NULL.
Syntax

isNull(x)

Param

• x— Value with Not composite Data type.

Return value

• 1 if x is NULL.
• 0 if x is Not NULL.

Example
Input table:

CREATE TABLE IF NOT EXISTS test.functionIsNull (x UInt8, y Nullable(UInt8)) ENGINE=CnchMergeTree ORDER BY x; 
INSERT INTO test.functionIsNull VALUES (1, NULL),(2,3); 
SELECT * FROM test.functionIsNull;

┌─x─┬─y────┐ 
│ 1 │ ᴺᵁᴸᴸ │ 
│ 2 │ 3    │ 
└───┴──────┘

Query:

SELECT x FROM test.functionIsNull WHERE isNull(y);

┌─x─┐ 
│ 1 │ 
└───┘

nullIf #

If the Params are equal, returns NULL.
Syntax

nullIf(x, y)

Param

• x, y— Values for comparison. They must be compatible Types, otherwise Bytehouse will generate Abnormal.

Return value

• NULL, if the Params are equal.
• x if the Params are not equal.

Example

SELECT nullIf(1, 1);

┌─nullIf(1, 1)─┐ 
│ ᴺᵁᴸᴸ         │ 
└──────────────┘

SELECT nullIf(1, 2);

┌─nullIf(1, 2)─┐ 
│ 1            │ 
└──────────────┘

toNullable #

Converts the Param Type to Nullable.
Syntax

toNullable(x)

Param

• x— Any Value of Not composite Type.

Return value

• Enter a value with Type Nullable.

Example

SELECT toTypeName(10);

┌─toTypeName(10)─┐ 
│ UInt8          │ 
└────────────────┘

SELECT toTypeName(toNullable(10));

┌─toTypeName(toNullable(10))─┐ 
│ Nullable(UInt8)            │ 
└────────────────────────────┘