BFT - Home

The B(ig) F(unction) T(axonomy)

The BFT aims to be a comprehensive catalogue of functions. Functions are the backbone of any compute system, but they are chronically under documented and often full of corner cases whose behavior differs in various systems. By documenting exhaustively documenting these corner cases we hope to make it possible for systems to fully describe their behaviors. This will make it easier to know what problems will be encountered switching between systems and, in some cases, make it possible to obtain the correct behavior through expression transformation or a precise application of function options.

Aggregate Functions

Arithmetic Functions

Boolean Functions

Comparison Functions

Datetime Functions

Logarithmic Functions

Rounding Functions

String Functions

Arithmetic Functions

Abs	Calculate the absolute value of the argument. Integer values allow the specification of overflow behavior to handle the unevenness of the twos complement, e.g. Int8 range [-128 : 127].
Acos	Get the arccosine of a value in radians.
Acosh	Get the hyperbolic arccosine of a value in radians.
Add	Add two values.
Asin	Get the arcsine of a value in radians.
Asinh	Get the hyperbolic arcsine of a value in radians.
Atan	Get the arctangent of a value in radians.
Atan2	Get the arctangent of values given as x/y pairs.
Atanh	Get the hyperbolic arctangent of a value in radians.
Avg	Average a set of values. For integral types, this truncates partial values.
Bitwise_and	Return the bitwise AND result for two integer inputs.
Bitwise_not	Return the bitwise NOT result for one integer input.
Bitwise_or	Return the bitwise OR result for two given integer inputs.
Bitwise_xor	Return the bitwise XOR result for two integer inputs.
Corr	Calculates the value of Pearson's correlation coefficient between `x` and `y`. If there is no input, null is returned.
Cos	Get the cosine of a value in radians.
Cosh	Get the hyperbolic cosine of a value in radians.
Divide	Divide x by y. In the case of integer division, partial values are truncated (i.e. rounded towards 0). The `on_division_by_zero` option governs behavior in cases where y is 0 and x is not 0. `LIMIT` means positive or negative infinity (depending on the sign of x and y). If x and y are both 0 or both +/-infinity, behavior will be governed by `on_domain_error`.
Exp	The mathematical constant e, raised to the power of the value.
Factorial	Return the factorial of a given integer input. The factorial of 0! is 1 by convention. Negative inputs will raise an error.
Max	Max a set of values.
Median	Calculate the median for a set of values. Returns null if applied to zero records. For the integer implementations, the rounding option determines how the median should be rounded if it ends up midway between two values. For the floating point implementations, they specify the usual floating point rounding mode.
Min	Min a set of values.
Mode	Calculates mode for a set of values. If there is no input, null is returned.
Modulus	Get the remainder when dividing one value by another.
Multiply	Multiply two values.
Negate	Negation of the value
Power	Take the power with x as the base and y as exponent.
Product	Product of a set of values. Returns 1 for empty input.
Quantile	Calculates quantiles for a set of values. This function will divide the aggregated values (passed via the distribution argument) over N equally-sized bins, where N is passed via a constant argument. It will then return the values at the boundaries of these bins in list form. If the input is appropriately sorted, this computes the quantiles of the distribution. The function can optionally return the first and/or last element of the input, as specified by the `boundaries` argument. If the input is appropriately sorted, this will thus be the minimum and/or maximum values of the distribution. When the boundaries do not lie exactly on elements of the incoming distribution, the function will interpolate between the two nearby elements. If the interpolated value cannot be represented exactly, the `rounding` option controls how the value should be selected or computed. The function fails and returns null in the following cases: - `n` is null or less than one; - any value in `distribution` is null. The function returns an empty list if `n` equals 1 and `boundaries` is set to `NEITHER`.
Sign	Return the signedness of the argument. Integer values return signedness with the same type as the input. Possible return values are [-1, 0, 1] Floating point values return signedness with the same type as the input. Possible return values are [-1.0, -0.0, 0.0, 1.0, NaN]
Sin	Get the sine of a value in radians.
Sinh	Get the hyperbolic sine of a value in radians.
Sqrt	Square root of the value
Std_dev	Calculates standard-deviation for a set of values.
Subtract	Subtract one value from another.
Sum	Sum a set of values. The sum of zero elements yields null.
Tan	Get the tangent of a value in radians.
Tanh	Get the hyperbolic tangent of a value in radians.
Variance	Calculates variance for a set of values.

Boolean Functions

And	The boolean `and` using Kleene logic. This function behaves as follows with nulls: true and null = null null and true = null false and null = false null and false = false null and null = null In other words, in this context a null value really means "unknown", and an unknown value `and` false is always false. Behavior for 0 or 1 inputs is as follows: and() -> true and(x) -> x
And_not	The boolean `and` of one value and the negation of the other using Kleene logic. This function behaves as follows with nulls: true and not null = null null and not false = null false and not null = false null and not true = false null and not null = null In other words, in this context a null value really means "unknown", and an unknown value `and not` true is always false, as is false `and not` an unknown value.
Bool_and	If any value in the input is false, false is returned. If the input is empty or only contains nulls, null is returned. Otherwise, true is returned.
Bool_or	If any value in the input is true, true is returned. If the input is empty or only contains nulls, null is returned. Otherwise, false is returned.
Not	The `not` of a boolean value. When a null is input, a null is output.
Or	The boolean `or` using Kleene logic. This function behaves as follows with nulls: true or null = true null or true = true false or null = null null or false = null null or null = null In other words, in this context a null value really means "unknown", and an unknown value `or` true is always true. Behavior for 0 or 1 inputs is as follows: or() -> false or(x) -> x
Xor	The boolean `xor` of two values using Kleene logic. When a null is encountered in either input, a null is output.

Comparison Functions

Between	Whether the `expression` is greater than or equal to `low` and less than or equal to `high`. `expression` BETWEEN `low` AND `high` If `low`, `high`, or `expression` are `null`, `null` is returned.
Coalesce	Evaluate arguments from left to right and return the first argument that is not null. Once a non-null argument is found, the remaining arguments are not evaluated. If all arguments are null, return null.
Equal	Whether two values are equal. `equal(x, y) := (x == y)` If either/both of `x` and `y` are `null`, `null` is returned.
Gt	Greater than. gt(x, y) := (x > y) If either/both of `x` and `y` are `null`, `null` is returned.
Gte	Greater than or equal to. gte(x, y) := (x >= y) If either/both of `x` and `y` are `null`, `null` is returned.
Is_finite	Whether a value is finite (neither infinite nor NaN). If `x` is `null`, `null` is returned.
Is_infinite	Whether a value is infinite. If `x` is `null`, `null` is returned.
Is_nan	Whether a value is not a number. If `x` is `null`, `null` is returned.
Is_not_distinct_from	Whether two values are equal. This function treats `null` values as comparable, so `is_not_distinct_from(null, null) == True` This is in contrast to `equal`, in which `null` values do not compare.
Is_not_null	Whether a value is not null. NaN is not null.
Is_null	Whether a value is null. NaN is not null.
Lt	Less than. lt(x, y) := (x < y) If either/both of `x` and `y` are `null`, `null` is returned.
Lte	Less than or equal to. lte(x, y) := (x <= y) If either/both of `x` and `y` are `null`, `null` is returned.
Not_equal	Whether two values are not_equal. `not_equal(x, y) := (x != y)` If either/both of `x` and `y` are `null`, `null` is returned.
Nullif	If two values are equal, return null. Otherwise, return the first value.

Datetime Functions

Add	Add an interval to a date/time type. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.
Add_intervals	Add two intervals together.
Assume_timezone	Convert local timestamp to UTC-relative timestamp_tz using given local time's timezone. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.
Extract	Extract portion of a date/time value. * YEAR Return the year of the value. * ISO_YEAR Return the ISO 8601 week-numbering year. First week of an ISO year has the majority (4 or more) of its days in January. * US_YEAR Return US epidemiological year. First week of US epidemiological year has the majority (4 or more) of its days in January. Last week of US epidemiological year has the year's last Wednesday in it. US epidemiological week starts on Sunday. * QUARTER Return the quarter from the value (1-4). * MONTH Return the month from value (1-12). * DAY Return the day of the month (1-31). * DAY_OF_YEAR Return the day of year (1 to 366). January 1st maps to day number 1, February 1st to 32, etc. * MONDAY_DAY_OF_WEEK Return the day of week from Monday (1) to Sunday (7). * SUNDAY_DAY_OF_WEEK Return the day of week from Sunday (1) to Saturday (7). * MONDAY_WEEK Return week of year number (1-53). First week starts on first Monday of January. * SUNDAY_WEEK Return week of year number (1-53). First week starts on first Sunday of January. * ISO_WEEK Return ISO week of year number (1-53). First ISO calendar week has the majority (4 or more) of its days in January. ISO week starts on Monday. * US_WEEK Return ISO-like week of year number (1-53). First US calendar week has the majority (4 or more) of its days in January. US week starts on Sunday. * HOUR Return the hour (0-23). * MINUTE Return the minute (0-59). * SECOND Return the second (0-59). * MILLISECOND Return number of milliseconds since the last full second. * MICROSECOND Return number of microseconds since the last full millisecond. * SUBSECOND Return number of microseconds since the last full second of the given timestamp. * UNIX_TIME Return number of seconds that have elapsed since 1970-01-01 00:00:00 UTC, ignoring leap seconds. * TIMEZONE_OFFSET Return number of seconds of timezone offset to UTC. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.
Extract_boolean	Extract boolean values of a date/time value. * IS_LEAP_YEAR Return true if year of the given value is a leap year and false otherwise. * IS_DST Return true if DST (Daylight Savings Time) is observed at the given value in the given timezone. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.
Gt	greater than
Gte	greater than or equal to
Local_timestamp	Convert UTC-relative timestamp_tz to local timestamp using given local time's timezone. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.
Lt	less than
Lte	less than or equal to
Round_calendar	Round a given timestamp/date/time to a multiple of a time unit. If the given timestamp is not already an exact multiple from the last origin unit in the given timezone, the resulting point is chosen as one of the two nearest multiples. Which of these is chosen is governed by rounding: FLOOR means to use the earlier one, CEIL means to use the later one, ROUND_TIE_DOWN means to choose the nearest and tie to the earlier one if equidistant, ROUND_TIE_UP means to choose the nearest and tie to the later one if equidistant. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.
Round_temporal	Round a given timestamp/date/time to a multiple of a time unit. If the given timestamp is not already an exact multiple from the origin in the given timezone, the resulting point is chosen as one of the two nearest multiples. Which of these is chosen is governed by rounding: FLOOR means to use the earlier one, CEIL means to use the later one, ROUND_TIE_DOWN means to choose the nearest and tie to the earlier one if equidistant, ROUND_TIE_UP means to choose the nearest and tie to the later one if equidistant. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.
Strftime	Convert timestamp/date/time to string using provided format, see https://man7.org/linux/man-pages/man3/strftime.3.html for reference. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.
Strptime	Parse string into timestamp/date/time using provided format, see https://man7.org/linux/man-pages/man3/strptime.3.html for reference. If timezone is present in timestamp and provided as parameter an error is thrown. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is supplied as parameter and present in the parsed string the parsed timezone is used. If parameter supplied timezone is invalid an error is thrown.
Subtract	Subtract an interval from a date/time type. Timezone strings must be as defined by IANA timezone database (https://www.iana.org/time-zones). Examples: "Pacific/Marquesas", "Etc/GMT+1". If timezone is invalid an error is thrown.

String Functions

Bit_length	Return the number of bits in the input string.
Capitalize	Capitalize the first character of the input string. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.
Center	Center the input string by padding the sides with a single `character` until the specified `length` of the string has been reached. By default, if the `length` will be reached with an uneven number of padding, the extra padding will be applied to the right side. The side with extra padding can be controlled with the `padding` option. Behavior is undefined if the number of characters passed to the `character` argument is not 1.
Char_length	Return the number of characters in the input string. The length includes trailing spaces.
Concat	Concatenate strings.
Concat_ws	Concatenate strings together separated by a separator.
Contains	Whether the `input` string contains the `substring`. The `case_sensitivity` option applies to the `substring` argument.
Count_substring	Return the number of non-overlapping occurrences of a substring in an input string. The `case_sensitivity` option applies to the `substring` argument.
Ends_with	Whether `input` string ends with the substring. The `case_sensitivity` option applies to the `substring` argument.
Left	Extract `count` characters starting from the left of the string.
Like	Are two strings like each other. The `case_sensitivity` option applies to the `match` argument.
Lower	Transform the string to lower case characters. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.
Lpad	Left-pad the input string with the string of 'characters' until the specified length of the string has been reached. If the input string is longer than 'length', remove characters from the right-side to shorten it to 'length' characters. If the string of 'characters' is longer than the remaining 'length' needed to be filled, only pad until 'length' has been reached. If 'characters' is not specified, the default value is a single space.
Ltrim	Remove any occurrence of the characters from the left side of the string. If no characters are specified, spaces are removed.
Octet_length	Return the number of bytes in the input string.
Regexp_count_substring	Return the number of non-overlapping occurrences of a regular expression pattern in an input string. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the `position` argument. Specifying `1` means to search for matches starting at the first character of the input string, `2` means the second character, and so on. The `position` argument should be a positive non-zero integer. The `case_sensitivity` option specifies case-sensitive or case-insensitive matching. Enabling the `multiline` option will treat the input string as multiple lines. This makes the `^` and `$` characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the `dotall` option makes the `.` character match line terminator characters in a string. Behavior is undefined if the regex fails to compile or the position value is out of range.
Regexp_match_substring	Extract a substring that matches the given regular expression pattern. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The occurrence of the pattern to be extracted is specified using the `occurrence` argument. Specifying `1` means the first occurrence will be extracted, `2` means the second occurrence, and so on. The `occurrence` argument should be a positive non-zero integer. The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the `position` argument. Specifying `1` means to search for matches starting at the first character of the input string, `2` means the second character, and so on. The `position` argument should be a positive non-zero integer. The `case_sensitivity` option specifies case-sensitive or case-insensitive matching. Enabling the `multiline` option will treat the input string as multiple lines. This makes the `^` and `$` characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the `dotall` option makes the `.` character match line terminator characters in a string. Behavior is undefined if the regex fails to compile, the occurrence value is out of range, or the position value is out of range.
Regexp_replace	Search a string for a substring that matches a given regular expression pattern and replace it with a replacement string. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github .io/icu/userguide/strings/regexp.html). The occurrence of the pattern to be replaced is specified using the `occurrence` argument. Specifying `1` means only the first occurrence will be replaced, `2` means the second occurrence, and so on. Specifying `0` means all occurrences will be replaced. The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the `position` argument. Specifying `1` means to search for matches starting at the first character of the input string, `2` means the second character, and so on. The `position` argument should be a positive non-zero integer. The replacement string can capture groups using numbered backreferences. The `case_sensitivity` option specifies case-sensitive or case-insensitive matching. Enabling the `multiline` option will treat the input string as multiple lines. This makes the `^` and `$` characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the `dotall` option makes the `.` character match line terminator characters in a string. Behavior is undefined if the regex fails to compile, the replacement contains an illegal back-reference, the occurrence value is out of range, or the position value is out of range.
Regexp_string_split	Split a string into a list of strings, based on a regular expression pattern. The substrings matched by the pattern will be used as the separators to split the input string and will not be included in the resulting list. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The `case_sensitivity` option specifies case-sensitive or case-insensitive matching. Enabling the `multiline` option will treat the input string as multiple lines. This makes the `^` and `$` characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the `dotall` option makes the `.` character match line terminator characters in a string.
Regexp_strpos	Return the position of an occurrence of the given regular expression pattern in a string. The first character of the string is at position 1. The regular expression pattern should follow the International Components for Unicode implementation (https://unicode-org.github.io/icu/userguide/strings/regexp.html). The number of characters from the beginning of the string to begin starting to search for pattern matches can be specified using the `position` argument. Specifying `1` means to search for matches starting at the first character of the input string, `2` means the second character, and so on. The `position` argument should be a positive non-zero integer. Which occurrence to return the position of is specified using the `occurrence` argument. Specifying `1` means the position first occurrence will be returned, `2` means the position of the second occurrence, and so on. The `occurrence` argument should be a positive non-zero integer. If no occurrence is found, 0 is returned. The `case_sensitivity` option specifies case-sensitive or case-insensitive matching. Enabling the `multiline` option will treat the input string as multiple lines. This makes the `^` and `$` characters match at the beginning and end of any line, instead of just the beginning and end of the input string. Enabling the `dotall` option makes the `.` character match line terminator characters in a string. Behavior is undefined if the regex fails to compile, the occurrence value is out of range, or the position value is out of range.
Repeat	Repeat a string `count` number of times.
Replace	Replace all occurrences of the substring with the replacement string. The `case_sensitivity` option applies to the `substring` argument.
Replace_slice	Replace a slice of the input string. A specified 'length' of characters will be deleted from the input string beginning at the 'start' position and will be replaced by a new string. A start value of 1 indicates the first character of the input string. If start is negative or zero, or greater than the length of the input string, a null string is returned. If 'length' is negative, a null string is returned. If 'length' is zero, inserting of the new string occurs at the specified 'start' position and no characters are deleted. If 'length' is greater than the input string, deletion will occur up to the last character of the input string.
Reverse	Returns the string in reverse order.
Right	Extract `count` characters starting from the right of the string.
Rpad	Right-pad the input string with the string of 'characters' until the specified length of the string has been reached. If the input string is longer than 'length', remove characters from the left-side to shorten it to 'length' characters. If the string of 'characters' is longer than the remaining 'length' needed to be filled, only pad until 'length' has been reached. If 'characters' is not specified, the default value is a single space.
Rtrim	Remove any occurrence of the characters from the right side of the string. If no characters are specified, spaces are removed.
Starts_with	Whether the `input` string starts with the `substring`. The `case_sensitivity` option applies to the `substring` argument.
String_agg	Concatenates a column of string values with a separator.
String_split	Split a string into a list of strings, based on a specified `separator` character.
Strpos	Return the position of the first occurrence of a string in another string. The first character of the string is at position 1. If no occurrence is found, 0 is returned. The `case_sensitivity` option applies to the `substring` argument.
Substring	Extract a substring of a specified `length` starting from position `start`. A `start` value of 1 refers to the first characters of the string.
Swapcase	Transform the string's lowercase characters to uppercase and uppercase characters to lowercase. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.
Title	Converts the input string into titlecase. Capitalize the first character of each word in the input string except for articles (a, an, the). Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.
Trim	Remove any occurrence of the characters from the left and right sides of the string. If no characters are specified, spaces are removed.
Upper	Transform the string to upper case characters. Implementation should follow the utf8_unicode_ci collations according to the Unicode Collation Algorithm described at http://www.unicode.org/reports/tr10/.

Approx_approx_count_distinct	Calculates the approximate number of rows that contain distinct values of the expression argument using HyperLogLog. This function provides an alternative to the COUNT (DISTINCT expression) function, which returns the exact number of rows that contain distinct values of an expression. APPROX_COUNT_DISTINCT processes large amounts of data significantly faster than COUNT, with negligible deviation from the exact result.
Generic_any_value	Selects an arbitrary value from a group of values. If the input is empty, the function returns null.
Generic_count	Count a set of records (not field referenced)

Ln	Natural logarithm of the value
Log10	Logarithm to base 10 of the value
Log1p	Natural logarithm (base e) of 1 + x log1p(x) => log(1+x)
Log2	Logarithm to base 2 of the value
Logb	Logarithm of the value with the given base logb(x, b) => log_{b} (x)

Ceil	Rounding to the ceiling of the value `x`.
Floor	Rounding to the floor of the value `x`.
Round	Rounding the value `x` to `s` decimal places.