Skip to main content

Comparison Functions

Equal

Substrait definition

Compares equality of two values. If the two values have different types they are not considered equal, so a float with value 1 will not equal an integer with value 1. If any argument is null, the result is null.

SQL Usage

... WHERE c1 = 'hello'

Not equal

Substrait definition

Checks two values for non equality. Different types will immedietly return true, that the values are not equal. If any argument is null, the result is null.

SQL Usage

... WHERE c1 != 'hello'

Greater than

Substrait definition

Checks if the left value is greater than the right value. If any argument is null, the result is null.

SQL Usage

... WHERE c1 > 1

Greater than or equal

Substrait definition

Checks if the left value is greater than or equal to the right value. If any argument is null, the result is null.

SQL Usage

... WHERE c1 >= 1

Less than

Substrait definition

Checks if the left value is less than the right value. If any argument is null, the result is null.

SQL Usage

... WHERE c1 < 1

Less than or equal

Substrait definition

Checks if the left value is less than or equal to the right value. If any argument is null, the result is null.

SQL Usage

... WHERE c1 <= 1

Between

Substrait definition

Checks if an expression is between two values.

SQL Usage

... WHERE c1 BETWEEN 100 AND 200

Is not null

Substrait definition

Checks if a single argument is not equal to null.

SQL Usage

... WHERE c1 is not null

Is Null

Substrait definition

Checks if a signle argument is null.

SQL Usage

... WHERE c1 is null

Coalesce

Substrait definition

Returns the first value, left to right that is not equal to null. If all values are null, a null value is returned.

SQL Usage

SELECT coalesce(column1, column2) FROM ...

Is Infinite

Substrait definition

Checks if a numeric value is positive or negative infinite. If the value is NaN (0 / 0), or another type, it returns false.

SQL Usage

SELECT is_infinite(column1) FROM ...

Is Finite

Substrait definition

Checks if a numeric value is not positive or negative infinite or NaN. If the value is not numeric it returns false.

SQL Usage

SELECT is_finite(column1) FROM ...

Is NaN

Substrait definition

Checks if an exprssion is not a numeric value. A null value returns null as in the substrait definition.

SQL Usage

SELECT is_nan(column1) FROM ...