Nullable

Notice: Some of the examples below are referenced from ClickHouse Documentation but have been adapted and modified to work in ByConity.

assumeNotNull

Results in an equivalent non- Nullable value for a Nullable type. In case the original value is NULL the result is undetermined. See also ifNull and coalesce functions.

Syntax

assumeNotNull(x)

Arguments:

• x — The original value.

Returned values

• The original value from the non- Nullable type, if it is not NULL .
• Implementation specific result if the original value was NULL .

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

coalesce

Checks from left to right whether NULL arguments were passed and returns the first non- NULL argument.

Syntax

coalesce(x,...)

Arguments

• Any number of parameters of a non-compound type. All parameters must be compatible by data type.

Returned values

• The first non- NULL argument.
• NULL , if all arguments are NULL .

Example Consider a list of contacts that may specify multiple ways to contact a customer.

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 mail and phone fields are of type String, but the icq field is UInt32 , so it needs to be converted to String . Get the first available contact method for the customer 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

Returns an alternative value if the main argument is NULL .

Syntax

ifNull(x,alt)

Arguments:

• x — The value to check for NULL .
• alt — The value that the function returns if x is NULL .

Returned values

• The value x , if x is not NULL .
• The value alt , if x is NULL .

Example

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

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

SELECT ifNull(NULL, 'b');

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

isNotNull

Checks whether the argument is NULL.

Syntax

isNotNull(x)

Arguments:

• x — A value with a non-compound data type.

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

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

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

isNull

Checks whether the argument is NULL.

Syntax

isNull(x)

Arguments

• x — A value with a non-compound data type.

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

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

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

nullIf

Returns NULL if the arguments are equal.

Syntax

nullIf(x, y)

Arguments

• x , y — Values for comparison. They must be compatible types, or ByConity will generate an exception.

Returned values

• NULL , if the arguments are equal.
• The x value, if the arguments are not equal.

Example

SELECT nullIf(1, 1);

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

SELECT nullIf(1, 2);

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

toNullable

Converts the argument type to Nullable .

Syntax

toNullable(x)

Arguments

• x — The value of any non-compound type.

Returned value

• The input value with a Nullable type.

Example

SELECT toTypeName(10);

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

SELECT toTypeName(toNullable(10));

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