Mathematical Functions¶
 abs(x) [same as x] ¶
Returns the absolute value of
x
.
 acos(x) double ¶
Returns the inverse cosine (a.k.a. arc cosine) of
x
.
 acosh(x) double ¶
Returns inverse hyperbolic cosine of
x
.
 asin(x) double ¶
Returns the arc sine of
x
.
 asinh(x) double ¶
Returns inverse hyperbolic sine of
x
.
 atan(x) double ¶
Returns the arc tangent of
x
.
 atan2(y, x) double ¶
Returns the arc tangent of
y / x
. For compatibility with Spark, returns 0 for the following corner cases: * atan2(0.0, 0.0) * atan2(0.0, 0.0) * atan2(0.0, 0.0) * atan2(0.0, 0.0)
 atanh(x) double ¶
Returns inverse hyperbolic tangent of
x
.
 add(x, y) [same as x] ¶
Returns the result of adding x to y. The types of x and y must be the same. Corresponds to sparks’s operator
+
.
 add(x, y) decimal ¶
Returns the result of adding
x
toy
. The argument types should be DECIMAL, and can have different precisions and scales. Fast path is implemented for cases that should not overflow. For the others, the whole parts and fractional parts of input decimals are added separately and combined finally. The result type is calculated with the max precision of input precisions, the max scale of input scales, and one extra digit for possible carrier. Overflow results in null output. Corresponds to Spark’s operator+
.SELECT CAST(1.1232100 as DECIMAL(38, 7)) + CAST(1 as DECIMAL(10, 0));  DECIMAL(38, 6) 2.123210 SELECT CAST(999999999999999999999999999.999 as DECIMAL(30, 3)) + CAST(999999999999999999999999999.999 as DECIMAL(30, 3));  DECIMAL(31, 3) 1999999999999999999999999999.998 SELECT CAST(99999999999999999999999999999999.99998 as DECIMAL(38, 6)) + CAST(99999999999999999999999999999999.99999 as DECIMAL(38, 5));  DECIMAL(38, 6) 0.000010 SELECT CAST(99999999999999999999999999999999990.0 as DECIMAL(38, 3)) + CAST(0.00001 as DECIMAL(38, 7));  DECIMAL(38, 6) NULL
 bin(x) varchar ¶
Returns the string representation of the long value
x
represented in binary.
 ceil(x) [same as x] ¶
Returns
x
rounded up to the nearest integer. Supported types are: BIGINT and DOUBLE.
 checked_add(x, y) [same as x] ¶
Returns the result of adding x to y. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to Spark’s operator
+
withfailOnError
as true.
 checked_divide(x, y) [same as x] ¶
Returns the results of dividing x by y. The types of x and y must be the same. Division by zero results in an error. Corresponds to Spark’s operator
/
withfailOnError
as true.
 checked_multiply(x, y) [same as x] ¶
Returns the result of multiplying x by y. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to Spark’s operator
*
withfailOnError
as true.
 checked_subtract(x, y) [same as x] ¶
Returns the result of subtracting y from x. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to Spark’s operator

withfailOnError
as true.
 cos(x) double ¶
Returns the cosine of
x
.
 cosh(x) double ¶
Returns the hyperbolic cosine of
x
.
 csc(x) double ¶
Returns the cosecant of
x
.
 degrees(x) double ¶
Converts angle x in radians to degrees.
 divide(x, y) double ¶
Returns the results of dividing x by y. Performs floating point division. Supported type is DOUBLE. Corresponds to Spark’s operator
/
.SELECT 3 / 2;  1.5 SELECT 2L / 2L;  1.0 SELECT 3 / 0;  NULL
 divide(x, y) decimal ¶
Returns the results of dividing x by y. Supported type is DECIMAL which can be different precision and scale. Performs floating point division. The result type depends on the precision and scale of x and y. Overflow results return null. Corresponds to Spark’s operator
/
.SELECT CAST(1 as DECIMAL(17, 3)) / CAST(2 as DECIMAL(17, 3));  decimal 0.500000000000000000000 SELECT CAST(1 as DECIMAL(20, 3)) / CAST(20 as DECIMAL(20, 2));  decimal 0.0500000000000000000 SELECT CAST(1 as DECIMAL(20, 3)) / CAST(0 as DECIMAL(20, 3));  NULL
 exp(x) double ¶
Returns Euler’s number raised to the power of
x
.
 expm1(x) double ¶
Returns Euler’s number raised to the power of
x
, minus 1, which isexp(x)  1
in math. This function expm1(x) is more accurate thanexp(x)  1
, whenx
is close to zero. If the argument is NaN, the result is NaN. If the argument is positive infinity, then the result is positive infinity. If the argument is negative infinity, then the result is 1.0. If the argument is zero, then the result is a zero with the same sign as the argument.
 floor(x) [same as x] ¶
Returns
x
rounded down to the nearest integer. Supported types are: BIGINT and DOUBLE.
 hex(x) varchar ¶
Converts
x
to hexadecimal. Supported types are: BIGINT, VARBINARY and VARCHAR. If the argument is a VARCHAR or VARBINARY, the result is string where each input byte is represented using 2 hex characters. If the argument is a positive BIGINT, the result is a hex representation of the number (up to 16 characters), if the argument is a negative BIGINT, the result is a hex representation of the number which will be treated as two’s complement.SELECT hex("Spark SQL");  537061726B2053514C SELECT hex(17);  11 SELECT hex(1);  FFFFFFFFFFFFFFFF
 hypot(a, b) double ¶
Returns the square root of a squared plus b squared.
 isnan(x) boolean ¶
Returns true if x is Nan, or false otherwise. Returns false is x is NULL. Supported types are: REAL, DOUBLE.
 log(base, expr) double ¶
Returns the logarithm of
expr
withbase
. Returns NULL if eitherexpr
orbase
is less than or equal to 0.
 log1p(x) double ¶
Returns the natural logarithm of the “given value
x
plus one”. Returns NULL if x is less than or equal to 1.
 log2(x) double ¶
Returns the logarithm of
x
with base 2. Return null for zero and nonpositive input.
 log10(x) double ¶
Returns the logarithm of
x
with base 10. Return null for zero and nonpositive input.
 multiply(x, y) [same as x] ¶
Returns the result of multiplying x by y. The types of x and y must be the same. Corresponds to Spark’s operator
*
.
 multiply(x, y) [decimal] ¶
Returns the result of multiplying x by y. The types of x and y must be decimal which can be different precision and scale. The result type depends on the precision and scale of x and y. Overflow results return null. Corresponds to Spark’s operator
*
.SELECT CAST(1 as DECIMAL(17, 3)) * CAST(2 as DECIMAL(17, 3));  decimal 2.000000 SELECT CAST(1 as DECIMAL(20, 3)) * CAST(20 as DECIMAL(20, 2));  decimal 20.00000 SELECT CAST(1 as DECIMAL(20, 3)) * CAST(0 as DECIMAL(20, 3));  decimal 0.000000 SELECT CAST(201e38 as DECIMAL(38, 38)) * CAST(301e38 as DECIMAL(38, 38));  decimal 0.0000000000000000000000000000000000000
 not(x) boolean ¶
Logical not.
SELECT not true;  false SELECT not false;  true SELECT not NULL;  NULL
 pmod(n, m) [same as n] ¶
Returns the positive remainder of n divided by m. Supported types are: TINYINT, SMALLINT, INTEGER, BIGINT, REAL and DOUBLE.
 power(x, p) double ¶
Returns
x
raised to the power ofp
.
 rand() double ¶
Returns a random value with uniformly distributed values in [0, 1).
SELECT rand();  0.9629742951434543
 rand(seed) double ¶
Returns a random value with uniformly distributed values in [0, 1) using a seed formed by combining userspecified
seed
and the configuration spark.partition_id. The framework is responsible for deterministic partitioning of the data and assigning unique spark.partition_id to each thread (in a deterministic way) .seed
must be constant. NULLseed
is identical to zeroseed
.SELECT rand(0);  0.5488135024422883 SELECT rand(NULL);  0.5488135024422883
 random() double ¶
An alias for
rand()
.
 random(seed) double ¶
An alias for
rand(seed)
.
 remainder(n, m) [same as n] ¶
Returns the modulus (remainder) of
n
divided bym
. Corresponds to Spark’s operator%
. Supported types are: TINYINT, SMALLINT, INTEGER, BIGINT, REAL and DOUBLE.
 rint(x) double ¶
Returns the double value that is closest in value to the argument and is equal to a mathematical integer. Returns
x
ifx
is a positive or negative infinity or a NaN.SELECT rint(12.3456);  12.0
 round(x, d) [same as x] ¶
Returns
x
rounded tod
decimal places using HALF_UP rounding mode. In HALF_UP rounding, the digit 5 is rounded up. Supported types forx
are integral and floating point types.
 sec(x) double ¶
Returns the secant of
x
.
 sinh(x) double ¶
Returns hyperbolic sine of
x
.
 subtract(x, y) [same as x] ¶
Returns the result of subtracting y from x. The types of x and y must be the same. Corresponds to Spark’s operator

.
 subtract(x, y) decimal ¶
Returns the result of subtracting
y
fromx
. Reuses the logic of add function for decimal type. Corresponds to Spark’s operator
.SELECT CAST(1.1232100 as DECIMAL(38, 7))  CAST(1 as DECIMAL(10, 0));  DECIMAL(38, 6) 0.123210 SELECT CAST(999999999999999999999999999.999 as DECIMAL(30, 3))  CAST(999999999999999999999999999.999 as DECIMAL(30, 3));  DECIMAL(31, 3) 0.000 SELECT CAST(99999999999999999999999999999999.99998 as DECIMAL(38, 6))  CAST(0.00001 as DECIMAL(38, 5));  DECIMAL(38, 6) 99999999999999999999999999999999.999990 SELECT CAST(99999999999999999999999999999999990.0 as DECIMAL(38, 3))  CAST(0.00001 as DECIMAL(38, 7));  DECIMAL(38, 6) NULL
 unaryminus(x) [same as x] ¶
Returns the negative of x. Corresponds to Spark’s operator

.
 unhex(x) varbinary ¶
Converts hexadecimal varchar
x
to varbinary.x
is considered case insensitive and expected to contain only hexadecimal characters 09 and AF. Ifx
contains nonhexadecimal character, the function returns NULL. Whenx
contains an even number of characters, each pair is converted to a single byte. The number of bytes in the result is half the number of bytes in the input. Whenx
contains an odd number of characters, the first character is decoded into the first byte of the result and the remaining pairs of characters are decoded into subsequent bytes. This behavior matches Spark 3.3.2 and newer.SELECT unhex("23");  # SELECT unhex("f");  \x0F SELECT unhex("b2323");  \x0B## SELECT unhex("G");  NULL SELECT unhex("G23");  NULL
 width_bucket(x, bound1, bound2, n) bigint ¶
Returns the zerobased bucket number to which
x
would be assigned in an equiwidth histogram withn
buckets, in the rangebound1
tobound2
. bound1 can be greater than bound2. If bound1 less than bound2, if x less than bound1 return 0, if x greater than or equal to bound2 return n + 1. If bound1 greater than bound2, if x greater than bound1 return 0, if x less than or equal to bound2 return n + 1. n must be a positive integral value. x, bound1, and bound2 cannot be NaN. bound1, and bound2 must be finite. bound1 cannot equal bound2; Otherwise, the function will return NULL.SELECT width_bucket(1.0, 0.0, 10.0, 5);  0 SELECT width_bucket(0.1, 0.0, 10.0, 5);  1 SELECT width_bucket(10.1, 0.0, 10.0, 5);  6 SELECT width_bucket(1.0, 10.0, 0.0, 5);  6 SELECT width_bucket(0.1, 10.0, 0.0, 5);  5 SELECT width_bucket(10.1, 10.0, 0.0, 5);  0 SELECT width_bucket(10.1, 10.0, 10.0, 5);  NULL