Mathematical Operation Functions

Relational Operators

All data types can be compared by using relational operators and the result is returned as a BOOLEAN value.

Relationship operators are binary operators. Two compared data types must be of the same type or they must support implicit conversion.

Table 1 lists all relational operators supported by Flink SQL.

**Table 1** Relational Operators
Operator	Returned Data Type	Description
A = B	BOOLEAN	If A is equal to B, then TRUE is returned. Otherwise, FALSE is returned. This operator is used for value assignment.
A <> B	BOOLEAN	If A is not equal to B, then TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, then NULL is returned. This operator follows the standard SQL syntax.
A < B	BOOLEAN	If A is less than B, then TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, then NULL is returned.
A <= B	BOOLEAN	If A is less than or equal to B, then TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, then NULL is returned.
A > B	BOOLEAN	If A is greater than B, then TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, then NULL is returned.
A >= B	BOOLEAN	If A is greater than or equal to B, then TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, then NULL is returned.
A IS NULL	BOOLEAN	If A is NULL, then TRUE is returned. Otherwise, FALSE is returned.
A IS NOT NULL	BOOLEAN	If A is not NULL, then TRUE is returned. Otherwise, FALSE is returned.
A IS DISTINCT FROM B	BOOLEAN	If A is not equal to B, TRUE is returned. NULL indicates A equals B.
A IS NOT DISTINCT FROM B	BOOLEAN	If A is equal to B, TRUE is returned. NULL indicates A equals B.
A BETWEEN [ASYMMETRIC \| SYMMETRIC] B AND C	BOOLEAN	If A is greater than or equal to B but less than or equal to C, TRUE is returned. ASYMMETRIC: indicates that B and C are location-related. For example, "A BETWEEN ASYMMETRIC B AND C" is equivalent to "A BETWEEN B AND C". SYMMETRIC: indicates that B and C are not location-related. For example, "A BETWEEN SYMMETRIC B AND C" is equivalent to "A BETWEEN B AND C) OR (A BETWEEN C AND B".
A NOT BETWEEN B [ASYMMETRIC \| SYMMETRIC]AND C	BOOLEAN	If A is less than B or greater than C, TRUE is returned. ASYMMETRIC: indicates that B and C are location-related. For example, "A NOT BETWEEN ASYMMETRIC B AND C" is equivalent to "A NOT BETWEEN B AND C". SYMMETRIC: indicates that B and C are not location-related. For example, "A NOT BETWEEN SYMMETRIC B AND C" is equivalent to "(A NOT BETWEEN B AND C) OR (A NOT BETWEEN C AND B)".
A LIKE B [ ESCAPE C ]	BOOLEAN	If A matches pattern B, TRUE is returned. The escape character C can be defined as required.
A NOT LIKE B [ ESCAPE C ]	BOOLEAN	If A does not match pattern B, TRUE is returned. The escape character C can be defined as required.
A SIMILAR TO B [ ESCAPE C ]	BOOLEAN	If A matches regular expression B, TRUE is returned. The escape character C can be defined as required.
A NOT SIMILAR TO B [ ESCAPE C ]	BOOLEAN	If A does not match regular expression B, TRUE is returned. The escape character C can be defined as required.
value IN (value [, value]* )	BOOLEAN	If the value is equal to any value in the list, TRUE is returned.
value NOT IN (value [, value]* )	BOOLEAN	If the value is not equal to any value in the list, TRUE is returned.
EXISTS (sub-query)	BOOLEAN	If sub-query returns at least one row, TRUE is returned.
value IN (sub-query)	BOOLEAN	If value is equal to a row returned by subquery, TRUE is returned.
value NOT IN (sub-query)	BOOLEAN	If value is not equal to a row returned by subquery, TRUE is returned.

Precautions

Values of the double, real, and float types may be different in precision. The equal sign (=) is not recommended for comparing two values of the double type. You are advised to obtain the absolute value by subtracting these two values of the double type and determine whether they are the same based on the absolute value. If the absolute value is small enough, the two values of the double data type are regarded equal. For example:
```
abs(0.9999999999 - 1.0000000000) < 0.000000001 //The precision decimal places of 0.9999999999 and 1.0000000000 are 10, while the precision decimal place of 0.000000001 is 9. Therefore, 0.9999999999 can be regarded equal to 1.0000000000.
```

Comparison between data of the numeric type and character strings is allowed. During comparison using relational operators, including >, <, ≤, and ≥, data of the string type is converted to numeric type by default. No characters other than numeric characters are allowed.
Character strings can be compared using relational operators.

Logical Operators

Common logical operators are AND, OR, and NOT. Their priority order is NOT > AND > OR.

Table 2 lists the calculation rules. A and B indicate logical expressions.

**Table 2** Logical Operators
Operator	Returned Data Type	Description
A OR B	BOOLEAN	If A or B is TRUE, TRUE is returned. Three-valued logic is supported.
A AND B	BOOLEAN	If both A and B are TRUE, TRUE is returned. Three-valued logic is supported.
NOT A	BOOLEAN	If A is not TRUE, TRUE is returned. If A is UNKNOWN, UNKNOWN is returned.
A IS FALSE	BOOLEAN	If A is TRUE, TRUE is returned. If A is UNKNOWN, FALSE is returned.
A IS NOT FALSE	BOOLEAN	If A is not FALSE, TRUE is returned. If A is UNKNOWN, TRUE is returned.
A IS TRUE	BOOLEAN	If A is TRUE, TRUE is returned. If A is UNKNOWN, FALSE is returned.
A IS NOT TRUE	BOOLEAN	If A is not TRUE, TRUE is returned. If A is UNKNOWN, TRUE is returned.
A IS UNKNOWN	BOOLEAN	If A is UNKNOWN, TRUE is returned.
A IS NOT UNKNOWN	BOOLEAN	If A is not UNKNOWN, TRUE is returned.

Precautions

Only data of the Boolean type can be used for calculation using logical operators. Implicit type conversion is not supported.

Arithmetic Operators

Arithmetic operators include binary operators and unary operators, for all of which, the returned results are of the numeric type. Table 3 lists arithmetic operators supported by Flink SQL.

**Table 3** Arithmetic Operators
Operator	Returned Data Type	Description
+ numeric	All numeric types	Returns numbers.
- numeric	All numeric types	Returns negative numbers.
A + B	All numeric types	A plus B. The result type is associated with the operation data type. For example, if floating-point number is added to an integer, the result will be a floating-point number.
A - B	All numeric types	A minus B. The result type is associated with the operation data type.
A * B	All numeric types	Multiply A and B. The result type is associated with the operation data type.
A / B	All numeric types	Divide A by B. The result is a number of the double type (double-precision number).
POWER(A, B)	All numeric types	Returns the value of A raised to the power B.
ABS(numeric)	All numeric types	Returns the absolute value of a specified value.
MOD(A, B)	All numeric types	Returns the remainder (modulus) of A divided by B. A negative value is returned only when A is a negative value.
SQRT(A)	All numeric types	Returns the square root of A.
LN(A)	All numeric types	Returns the nature logarithm of A (base e).
LOG10(A)	All numeric types	Returns the base 10 logarithms of A.
LOG2(A)	All numeric types	Returns the base 2 logarithm of A.
LOG(B) LOG(A, B)	All numeric types	When called with one argument, returns the natural logarithm of B. When called with two arguments, this function returns the logarithm of B to the base A. B must be greater than 0 and A must be greater than 1.
EXP(A)	All numeric types	Return the value of e raised to the power of a.
CEIL(A) CEILING(A)	All numeric types	Return the smallest integer that is greater than or equal to a. For example: ceil(21.2) = 22.
FLOOR(A)	All numeric types	Return the largest integer that is less than or equal to a. For example: floor(21.2) = 21.
SIN(A)	All numeric types	Returns the sine value of A.
COS(A)	All numeric types	Returns the cosine value of A.
TAN(A)	All numeric types	Returns the tangent value of A.
COT(A)	All numeric types	Returns the cotangent value of A.
ASIN(A)	All numeric types	Returns the arc sine value of A.
ACOS(A)	All numeric types	Returns the arc cosine value of A.
ATAN(A)	All numeric types	Returns the arc tangent value of A.
ATAN2(A, B)	All numeric types	Returns the arc tangent of a coordinate (A, B).
COSH(A)	All numeric types	Returns the hyperbolic cosine of A. Return value type is DOUBLE.
DEGREES(A)	All numeric types	Convert the value of a from radians to degrees.
RADIANS(A)	All numeric types	Convert the value of a from degrees to radians.
SIGN(A)	All numeric types	Returns the sign of A. 1 is returned if A is positive. –1 is returned if A is negative. Otherwise, 0 is returned.
ROUND(A, d)	All numeric types	Returns a number rounded to d decimal places for A. For example: round(21.263,2) = 21.26.
PI	All numeric types	Returns the value of pi.
E()	All numeric types	Returns the value of e.
RAND()	All numeric types	Returns a pseudorandom double value in the range [0.0, 1.0)
RAND(A)	All numeric types	Returns a pseudorandom double value in the range [0.0, 1.0) with an initial seed A. Two RAND functions will return identical sequences of numbers if they have the same initial seed.
RAND_INTEGER(A)	All numeric types	Returns a pseudorandom double value in the range [0.0, A)
RAND_INTEGER(A, B)	All numeric types	Returns a pseudorandom double value in the range [0.0, B) with an initial seed A.
UUID()	All numeric types	Returns a UUID string.
BIN(A)	All numeric types	Returns a string representation of integer A in binary format. Returns NULL if A is NULL.
HEX(A) HEX(B)	All numeric types	Returns a string representation of an integer A value or a string B in hex format. Returns NULL if the argument is NULL.
TRUNCATE(A, d)	All numeric types	Returns a number of truncated to d decimal places. Returns NULL if A or d is NULL. Example: truncate (42.345, 2) = 42.340 truncate(42.345) = 42.000
PI()	All numeric types	Returns the value of pi.