Math functions

57 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.

abs

Returns a number's nonnegative magnitude while preserving its input type.

abs(N) -> N

Removes the sign of a numeric argument without widening its logical type. The minimum signed integer cannot be represented as a positive value in the same type and raises NUMERIC_OVERFLOW; floating NaN remains NaN and SQL NULL remains NULL. An incompatible nonnumeric type raises TYPE_MISMATCH.

Example

abs(-12)
-- → 12

acos

Returns the principal angle whose cosine equals the supplied ratio.

acos(N) -> float64

Computes inverse cosine as a float64 angle in radians between zero and pi for inputs in [−1,1]. Arguments outside that real domain produce IEEE NaN instead of a SQL NULL result. NULL input remains NULL, and nonnumeric types raise TYPE_MISMATCH.

Example

acos(1)
-- → 0

acosh

Finds the nonnegative inverse hyperbolic cosine of a value at least one.

acosh(N) -> float64

Returns the float64 value y satisfying cosh(y)=x on the nonnegative branch. One yields zero, positive infinity yields infinity, and inputs below one produce IEEE NaN. SQL NULL propagates, while incompatible source types raise TYPE_MISMATCH.

Example

acosh(1)
-- → 0

asin

Computes the principal arcsine angle for a ratio between minus one and one.

asin(N) -> float64

Returns inverse sine in radians as float64, choosing the branch from −pi/2 through pi/2. An input outside [−1,1] gives IEEE NaN rather than an absent SQL value. NULL remains NULL, and a nonnumeric source raises TYPE_MISMATCH.

Example

asin(0)
-- → 0

asinh

Returns the real inverse hyperbolic sine across the entire numeric axis.

asinh(N) -> float64

Computes the float64 y for which sinh(y)=x, accepting both negative and positive finite arguments without a restricted real domain. It is odd, maps zero to zero and preserves the sign of infinities; NaN remains NaN. SQL NULL propagates and incompatible types raise TYPE_MISMATCH.

Example

asinh(0)
-- → 0

atan

Converts a slope into its principal arctangent angle in radians.

atan(N) -> float64

Returns inverse tangent as float64 on the principal branch between −pi/2 and pi/2. Finite inputs can have either sign, infinite inputs approach the corresponding endpoint, and NaN follows IEEE behavior. NULL input returns NULL; a nonnumeric argument raises TYPE_MISMATCH.

Example

atan(0)
-- → 0

atan2

Derives a quadrant-aware direction angle from vertical and horizontal components.

atan2(N,N) -> float64

Accepts y first and x second, returning the direction of the vector (x,y) as a float64 angle in radians. The signs of both operands select the quadrant, and signed zeros and infinities follow IEEE atan2 conventions rather than a simple y/x quotient. Any SQL NULL returns NULL, and nonnumeric operands raise TYPE_MISMATCH.

Example

atan2(0, 1)
-- → 0

atanh

Evaluates inverse hyperbolic tangent for a ratio inside the open unit interval.

atanh(N) -> float64

Returns the float64 y satisfying tanh(y)=x for −1<x<1. Inputs of −1 and one yield the corresponding signed infinities, values outside [−1,1] yield IEEE NaN, and zero yields zero. SQL NULL remains NULL and incompatible input types raise TYPE_MISMATCH.

Example

atanh(0)
-- → 0

cbrt

Finds the real cube root of positive, negative or zero numeric input.

cbrt(N) -> float64

Returns the real cube root as float64, preserving the sign of a finite negative input instead of treating it as a domain failure. Zero maps to zero, infinities retain their sign, and NaN remains NaN. SQL NULL returns NULL; incompatible source types raise TYPE_MISMATCH.

Example

cbrt(-8)
-- → -2

ceil

Rounds upward to the next representable decimal step at an optional precision.

ceil(N) -> N
ceil(N,int32) -> N

Returns the smallest multiple of the requested decimal step that is greater than or equal to the input, retaining its type. Precision defaults to zero, positive precision counts fractional places, and negative precision selects powers of ten; NULL in either argument returns NULL. Invalid precision raises INVALID_ARGUMENT and an unrepresentable typed result raises NUMERIC_OVERFLOW.

Example

ceil(-1.2)
-- → -1

cos

Calculates the horizontal unit-circle component of an angle given in radians.

cos(N) -> float64

Returns cosine as float64 for a radian-valued numeric argument, with zero radians producing one. Finite real results lie between −1 and one, while infinite or NaN arguments produce IEEE NaN rather than NULL. SQL NULL propagates; incompatible types raise TYPE_MISMATCH.

Example

cos(0)
-- → 1

cosh

Computes the average of positive and negative exponentials for a hyperbolic cosine result.

cosh(N) -> float64

Returns cosh(x)=(exp(x)+exp(−x))/2 as float64, an even function whose finite real results are at least one. Large magnitudes can overflow to positive infinity and NaN remains NaN. SQL NULL stays NULL, and nonnumeric input raises TYPE_MISMATCH.

Example

cosh(0)
-- → 1

cot

Returns the cotangent of a radian angle as the cosine-to-sine ratio.

cot(N) -> float64

Computes cotangent as float64, using radians for the numeric angle. Singular angles follow IEEE floating behavior, so zero can produce infinity rather than a SQL NULL or integer division error. NULL input propagates and incompatible argument types raise TYPE_MISMATCH.

Example

cot(0)
-- → Infinity

degrees

Converts an angle expressed in radians into a float64 degree measure.

degrees(N) -> float64

Multiplies the numeric radian value by 180/pi and returns a double-precision degree value. Angles are not normalized to a revolution, so negative and multi-revolution inputs retain their corresponding scale. NULL propagates, non-finite inputs follow IEEE arithmetic, and nonnumeric arguments raise TYPE_MISMATCH.

Example

degrees(0)
-- → 0

e

Provides Euler's constant as a non-null double-precision scalar.

e() -> float64

Returns the float64 approximation of e, the base of natural logarithms, without reading any row values. The zero-argument function is immutable and never returns NULL. Passing arguments is invalid and raises INVALID_ARGUMENT.

Example

e()
-- → 2.718281828459045

erf

Evaluates the Gaussian error integral from zero to the supplied number.

erf(N) -> float64

Returns erf(x), equal to 2/sqrt(pi) times the integral of exp(−t²) from zero to x, as float64. The function is odd, approaches ±1 toward the corresponding infinities, and propagates NaN. SQL NULL stays NULL; a nonnumeric source raises TYPE_MISMATCH.

Example

erf(0)
-- → 0

erfc

Computes the complementary Gaussian error function without subtracting near-one results.

erfc(N) -> float64

Returns the complementary error function erfc(x), mathematically 1−erf(x), using a direct computation suitable for small upper-tail values. Zero returns one, positive infinity returns zero and negative infinity returns two, with NaN preserved. SQL NULL returns NULL and nonnumeric input raises TYPE_MISMATCH.

Example

erfc(0)
-- → 1

exp

Raises Euler's number to a numeric exponent and returns a double-precision result.

exp(N) -> float64

Computes the base-e exponential as float64, with an input of zero producing one. Floating overflow yields positive infinity, very negative inputs can underflow to zero, and NaN follows IEEE behavior rather than becoming NULL. SQL NULL propagates; a nonnumeric argument raises TYPE_MISMATCH.

Example

exp(0)
-- → 1

exp10

Returns ten raised to an exponent as a binary64 number.

exp10(N) -> float64

Computes 10^x for a numeric exponent and returns float64 rather than an exact decimal power. Large positive inputs can overflow to positive infinity and large negative inputs can underflow to zero under IEEE arithmetic. NULL propagates, NaN remains NaN, and incompatible types raise TYPE_MISMATCH.

Example

exp10(2)
-- → 100

exp2

Evaluates a power of two from an integer or fractional exponent.

exp2(N) -> float64

Returns 2^x as float64, including fractional exponents rather than restricting the operation to integer bit shifts. An exponent of zero yields one, while floating overflow and underflow produce infinity and zero respectively. SQL NULL and NaN are preserved in their own domains; nonnumeric input raises TYPE_MISMATCH.

Example

exp2(3)
-- → 8

expm1

Calculates e to the input power minus one with improved accuracy near zero.

expm1(N) -> float64

Computes exp(x)−1 directly as float64, avoiding the cancellation introduced by subtracting one from an already rounded exponential. Zero returns zero and negative infinity approaches the exact result −1; other exceptional floating values follow IEEE behavior. NULL remains NULL, and incompatible argument types raise TYPE_MISMATCH.

Example

expm1(0)
-- → 0

factorial

Returns the exact factorial of a nonnegative integer no greater than twenty.

factorial(uint8) -> uint64

Multiplies the positive integers through n and returns the exact uint64 product, defining 0! as one. Only uint8 inputs from zero through twenty are accepted for evaluation; larger n raises NUMERIC_OVERFLOW rather than returning a wrapped product. SQL NULL propagates, and an incompatible argument type raises TYPE_MISMATCH.

Example

factorial(5)
-- → 120

floor

Selects the greatest decimal-step value no larger than the original number.

floor(N) -> N
floor(N,int32) -> N

Rounds toward negative infinity while preserving the input type, using an optional decimal precision that defaults to zero. Negative precision rounds to tens, hundreds and larger powers of ten, while positive precision retains fractional places; any NULL argument returns NULL. INVALID_ARGUMENT reports an invalid precision, and NUMERIC_OVERFLOW reports an unrepresentable result.

Example

floor(-1.2)
-- → -2

gcd

Finds the largest nonnegative integer dividing both integer arguments.

gcd(I,I) -> I

Returns the greatest common divisor in the resolved integer type, ignoring the signs of ordinary signed inputs. A zero paired with a nonzero value returns the latter's magnitude, and two zeros return zero; signed-minimum inputs or an unrepresentable result raise NUMERIC_OVERFLOW. Any SQL NULL returns NULL and incompatible types raise TYPE_MISMATCH.

Example

gcd(18, 24)
-- → 6

greatest

Selects the largest scalar argument after lossless conversion to a common orderable type.

greatest(C,C,...) -> C

Compares at least two arguments and returns their greatest value using a common orderable scalar type that preserves each input without loss. Any NULL argument makes the result NULL rather than being skipped. Too few arguments raise INVALID_ARGUMENT, and no lossless common type raises TYPE_MISMATCH.

Example

greatest(4, 9, 2)
-- → 9

hypot

Computes a right triangle's hypotenuse from two component lengths using scaled arithmetic.

hypot(N,N) -> float64

Returns sqrt(x²+y²) as float64 while scaling intermediate calculations to avoid unnecessary overflow or underflow. The signs of the components do not change the distance, and a finite result can be computed even when directly squaring a component would overflow. SQL NULL propagates and nonnumeric types raise TYPE_MISMATCH.

Example

hypot(3, 4)
-- → 5

int_div

Divides two integers and returns the quotient truncated toward zero.

int_div(I,I) -> I

Performs integer division in the resolved integer type, discarding the fractional part toward zero rather than toward negative infinity. A zero divisor raises INVALID_ARGUMENT, and dividing the minimum signed value by −1 raises NUMERIC_OVERFLOW. NULL in either operand yields NULL, while incompatible operand types raise TYPE_MISMATCH.

Example

int_div(-7, 3)
-- → -2

int_div_or_zero

Returns a truncated integer quotient with zero substituted for division exceptions.

int_div_or_zero(I,I) -> I

Computes the same toward-zero quotient as int_div for ordinary integer operands. A zero divisor or the signed-minimum divided by −1 overflow case returns a zero of the result type instead of INVALID_ARGUMENT or NUMERIC_OVERFLOW. SQL NULL still returns NULL, and incompatible operand types raise TYPE_MISMATCH.

Example

int_div_or_zero(7, 0)
-- → 0

is_finite

Identifies finite numeric values while rejecting both infinities and floating NaN.

is_finite(N) -> bool

Returns true for every integer and decimal and for floating values that are neither infinite nor NaN. Positive and negative infinity return false, as does NaN; SQL NULL returns NULL rather than false. Arguments outside the numeric domains raise TYPE_MISMATCH.

Example

is_finite(42)
-- → true

is_nan

Recognizes the IEEE not-a-number value without treating infinity as NaN.

is_nan(N) -> bool

Returns true only for a floating NaN, regardless of its payload or sign. Finite floats, infinities, integers and decimals all return false, while a SQL NULL input returns NULL. A nonnumeric source has no valid overload and raises TYPE_MISMATCH.

Example

is_nan(0)
-- → false

lcm

Finds the smallest nonnegative multiple shared by two integer values.

lcm(I,I) -> I

Returns the least common multiple in the resolved integer type, using the magnitudes of the input integers. A zero operand makes the result zero, and a common multiple that cannot fit the result type raises NUMERIC_OVERFLOW. SQL NULL propagates; incompatible noninteger arguments raise TYPE_MISMATCH.

Example

lcm(6, 8)
-- → 24

least

Chooses the smallest among comparable scalar inputs with lossless type unification.

least(C,C,...) -> C

Returns the minimum of two or more scalar arguments after resolving a common orderable type without information loss. SQL NULL in any position propagates to the result instead of being ignored during comparison. Missing required arguments raise INVALID_ARGUMENT, and incompatible types raise TYPE_MISMATCH.

Example

least(4, 9, 2)
-- → 2

lgamma

Returns the natural logarithm of the absolute gamma value for a numeric argument.

lgamma(N) -> float64

Computes ln(abs(gamma(x))) as float64, allowing large gamma magnitudes to be expressed without first materializing the gamma result. Poles produce positive infinity and other exceptional floating inputs follow IEEE behavior. SQL NULL remains NULL; nonnumeric argument types raise TYPE_MISMATCH.

Example

lgamma(1)
-- → 0

ln

Computes the natural logarithm of a numeric value as float64.

ln(N) -> float64

Returns the logarithm to base e, with one mapping to zero. IEEE floating semantics produce negative infinity at zero and NaN for negative arguments instead of converting those results to SQL NULL. NULL input stays NULL, and incompatible nonnumeric types raise TYPE_MISMATCH.

Example

ln(1)
-- → 0

log10

Measures a positive number's exponent on the base-ten logarithmic scale.

log10(N) -> float64

Returns log base ten of the numeric input as float64, so powers of ten map to their decimal exponents. Zero produces negative infinity and a negative argument produces NaN under IEEE semantics, rather than a SQL NULL substitute. NULL propagates and incompatible nonnumeric types raise TYPE_MISMATCH.

Example

log10(100)
-- → 2

log1p

Evaluates the natural logarithm of one plus the input accurately for tiny increments.

log1p(N) -> float64

Computes ln(1+x) directly in float64 to retain accuracy that can be lost by rounding the addition first. An input of zero returns zero, −1 returns negative infinity, and values below −1 produce IEEE NaN. SQL NULL stays NULL, and an incompatible type raises TYPE_MISMATCH.

Example

log1p(0)
-- → 0

log2

Returns the binary logarithm, mapping powers of two to their exponents.

log2(N) -> float64

Computes log base two as float64 for a numeric argument. One returns zero, zero returns negative infinity, and negative input returns IEEE NaN without being converted to SQL NULL. NULL input remains NULL; nonnumeric arguments raise TYPE_MISMATCH.

Example

log2(8)
-- → 3

mod

Returns the remainder after division with the same sign as the dividend.

mod(N,N) -> N

Computes the remainder corresponding to a quotient truncated toward zero, so a negative dividend yields a negative remainder or zero. A zero divisor raises INVALID_ARGUMENT instead of returning a sentinel. SQL NULL in either operand yields NULL, and incompatible numeric types raise TYPE_MISMATCH.

Example

mod(-7, 3)
-- → -1

pi

Supplies the double-precision circle circumference-to-diameter ratio.

pi() -> float64

Returns a float64 approximation to pi, independent of row data or session settings. The function has no arguments and its result is never NULL. Supplying an argument raises INVALID_ARGUMENT.

Example

pi()
-- → 3.141592653589793

pow

Raises a numeric base to an arbitrary exponent and returns float64.

power(N,N) -> float64

Computes the base raised to the second argument, supporting fractional as well as integer exponents. Floating domain failures, such as a negative base with a nonintegral exponent, produce IEEE NaN, while overflow can produce infinity. Any SQL NULL input propagates, and nonnumeric operands raise TYPE_MISMATCH.

Example

power(2, 3)
-- → 8

radians

Transforms a degree measurement into the corresponding angle in radians.

radians(N) -> float64

Multiplies a numeric degree value by pi/180 and returns float64 without restricting the result to a principal interval. Negative angles and values above 360 degrees remain proportional rather than being wrapped. SQL NULL propagates, exceptional floats follow IEEE arithmetic, and nonnumeric inputs raise TYPE_MISMATCH.

Example

radians(0)
-- → 0

rand

Generates a volatile unsigned 32-bit random value for each evaluation.

rand() -> uint32
rand(T) -> uint32

Returns a uint32 random value from zero through 4294967295; the optional argument is ignored and only prevents common-expression elimination. Even a NULL optional argument does not make the result NULL, and repeated calls need not agree. Continuous plans reject the function because these volatile values cannot be replayed deterministically.

Example

rand() BETWEEN 0 AND 4294967295
-- → true

rand64

Produces an independently evaluated random integer spanning the unsigned 64-bit range.

rand64() -> uint64
rand64(T) -> uint64

Returns a volatile uint64 result, with any optional argument ignored rather than used as a seed. NULL in that argument is also ignored, so the function never returns SQL NULL. It is limited to one-shot queries because replaying a continuous plan cannot reproduce its values deterministically.

Example

rand64() >= 0
-- → true

round

Rounds numeric values to decimal precision, sending halfway cases away from zero.

round(N) -> N
round(N,int32) -> N

Returns the nearest value at the optional decimal precision while preserving the input type; precision defaults to zero. Exact halfway cases round away from zero, so positive and negative ties move outward, and any SQL NULL argument propagates. Invalid precision raises INVALID_ARGUMENT and a checked decimal or integer result that cannot fit raises NUMERIC_OVERFLOW.

Example

round(-2.5)
-- → -3

round_age

Maps an age to the representative value of its versioned NYX compatibility band.

round_age(N) -> uint8

Returns uint8 age-band representatives: 0 below age 1, 17 for [1,18), 18 for [18,25), 25 for [25,35), 35 for [35,45), 45 for [45,55), and 55 from age 55 onward. This is the NYX compatibility contract, including fractional ages and the upward representative for young ages. SQL NULL stays NULL; non-finite input raises INVALID_ARGUMENT and nonnumeric types raise TYPE_MISMATCH.

Example

round_age(24)
-- → 18

round_bankers

Resolves decimal rounding ties to the nearest even retained digit.

round_bankers(N) -> N
round_bankers(N,int32) -> N

Rounds to the nearest step at the optional decimal precision, which defaults to zero, preserving the input type. A halfway value chooses the even result rather than always increasing its magnitude, and NULL in either argument returns NULL. Invalid precision raises INVALID_ARGUMENT; an unrepresentable checked result raises NUMERIC_OVERFLOW.

Example

round_bankers(2.5)
-- → 2

round_duration

Buckets elapsed seconds onto the versioned NYX coarse-duration ladder.

round_duration(N) -> uint16

Returns the greatest ladder value no larger than the input seconds, using 0, 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000 and 36000. Values above the final threshold remain in the 36000 bucket, and the result type is uint16. SQL NULL remains NULL; negative or non-finite durations raise INVALID_ARGUMENT and nonnumeric types raise TYPE_MISMATCH.

Example

round_duration(75)
-- → 60

round_to_exp2

Finds the largest power of two no greater than a number, returning zero below one.

round_to_exp2(N) -> N

Returns the power-of-two lower bound in the input numeric type, with exact powers retained unchanged. Values below one, including negative numbers, return zero; SQL NULL remains NULL. NaN and other inputs without a finite lower power bound raise INVALID_ARGUMENT, and incompatible types raise TYPE_MISMATCH.

Example

round_to_exp2(13)
-- → 8

sign

Classifies a number's sign as −1, zero or 1 using an int8 result.

sign(N) -> int8

Returns −1 for negative values, zero for either signed zero, and one for positive values, including signed infinities. NaN raises INVALID_ARGUMENT because int8 cannot encode an unordered floating result. SQL NULL propagates; a nonnumeric argument raises TYPE_MISMATCH.

Example

sign(-42)
-- → -1

sin

Returns the vertical unit-circle component associated with a radian angle.

sin(N) -> float64

Evaluates the sine of an angle in radians as float64 and preserves the odd symmetry of finite numeric arguments. Ordinary real results lie in [−1,1], while infinite and NaN angles produce IEEE NaN instead of an absent SQL value. NULL remains NULL; a nonnumeric argument raises TYPE_MISMATCH.

Example

sin(0)
-- → 0

sinh

Computes half the difference between a number's positive and negative exponentials.

sinh(N) -> float64

Returns sinh(x)=(exp(x)−exp(−x))/2 as float64, with the result retaining the sign of a finite nonzero input. Large magnitudes can overflow to signed infinity, and NaN follows IEEE propagation. SQL NULL returns NULL and nonnumeric types raise TYPE_MISMATCH.

Example

sinh(0)
-- → 0

sqrt

Returns the principal square root of a number using double-precision arithmetic.

sqrt(N) -> float64

Computes the nonnegative square root for nonnegative finite inputs and returns float64. Negative floating input yields IEEE NaN rather than NULL, positive infinity remains infinity, and NaN propagates. SQL NULL is preserved and incompatible nonnumeric types raise TYPE_MISMATCH.

Example

sqrt(9)
-- → 3

tan

Evaluates the tangent slope of an angle measured in radians.

tan(N) -> float64

Returns tangent as float64 for the supplied radian angle. Values close to odd multiples of pi/2 can have very large magnitudes, and floating singularities or non-finite arguments follow IEEE behavior rather than becoming SQL NULL. NULL input remains NULL and incompatible types raise TYPE_MISMATCH.

Example

tan(0)
-- → 0

tanh

Squashes a real number through hyperbolic tangent toward signed unit limits.

tanh(N) -> float64

Computes the float64 ratio sinh(x)/cosh(x), with finite results bounded by −1 and one. Negative and positive infinity return −1 and one respectively, zero returns zero, and NaN is preserved. SQL NULL propagates, while a nonnumeric input raises TYPE_MISMATCH.

Example

tanh(0)
-- → 0

tgamma

Evaluates the gamma function, extending factorial values to real numeric arguments.

tgamma(N) -> float64

Returns gamma(x) as float64, so a positive integer n produces the floating value of (n−1)!. Poles, overflowing magnitudes and invalid floating domains follow IEEE infinity or NaN behavior rather than yielding SQL NULL. NULL input remains NULL and incompatible argument types raise TYPE_MISMATCH.

Example

tgamma(5)
-- → 24

trunc

Discards digits beyond a selected decimal position by moving toward zero.

trunc(N) -> N
trunc(N,int32) -> N

Truncates a numeric value at the optional decimal precision, defaulting to an integer-valued result while retaining the input type. Positive precision keeps fractional places and negative precision removes lower whole-number digits; SQL NULL in either argument returns NULL. Invalid precision raises INVALID_ARGUMENT and an unrepresentable result raises NUMERIC_OVERFLOW.

Example

trunc(-12.9)
-- → -12

width_bucket

Assigns a number to an equal-width histogram bucket with separate underflow and overflow indices.

width_bucket(N,N,N,uint32) -> uint32

Divides the interval between lower and upper bounds into the requested number of equal-width buckets and returns its SQL bucket index. For ascending bounds, values below the lower bound return zero and values at or above the upper bound return count+1; reversed bounds reverse the comparison direction. NULL propagates, equal bounds or a zero count raise INVALID_ARGUMENT, and an unrepresentable index raises NUMERIC_OVERFLOW.

Example

width_bucket(7, 0, 10, 5)
-- → 4