Functions
Every PPL scalar function name. A name maps onto an IR registry function, is lowered onto IR operators, or is unsupported and fails with UNSUPPORTED_FUNCTION. NULL behavior and errors are those of the IR function.
| Function | Category | Support | Semantics |
|---|---|---|---|
abs | math | full | Absolute value. |
acos | math | full | Trigonometric or angle-conversion function acos in radians; NULL outside its domain. |
add | full | lowered to arithmetic operator + | |
adddate | full | lowered to date_add | |
addtime | datetime | full | t plus the time of day or duration delta. |
array | collection | full | Array of the arguments. |
array_length | collection | full | Number of elements of a; NULL for non-arrays. |
ascii | string | full | Code point of the first character of s, 0 for the empty string. |
asin | math | full | Trigonometric or angle-conversion function asin in radians; NULL outside its domain. |
atan | math | full | Arc tangent of x, or with two arguments of y/x using the signs of both to pick the quadrant. |
atan2 | math | full | Arc tangent of y/x using the signs of both to pick the quadrant. |
avg | math | full | Arithmetic mean of the arguments. |
cbrt | math | full | Cube root. |
ceil | math | full | Smallest integer not less than x. |
ceiling | math | full | Smallest integer not less than x. |
cidrmatch | ip | full | TRUE when the IPv4 or IPv6 address ip is inside the CIDR block cidr; NULL when either does not parse. |
coalesce | condition | full | The first argument that is not NULL, or NULL. |
concat | string | full | Concatenation of the arguments' string forms; NULL when any argument is NULL. |
concat_ws | string | full | Concatenation of the non-NULL arguments separated by sep. |
conv | math | full | x read as a number in from_base, written in to_base (2-36). |
convert_tz | datetime | full | t read as wall-clock time in from_tz, rewritten as wall-clock time in to_tz; NULL for unknown zones. |
cos | math | full | Trigonometric or angle-conversion function cos in radians; NULL outside its domain. |
cosh | math | full | Trigonometric or angle-conversion function cosh in radians; NULL outside its domain. |
cot | math | full | Trigonometric or angle-conversion function cot in radians; NULL outside its domain. |
crc32 | math | full | CRC-32 (IEEE) checksum of the UTF-8 bytes of s. |
curdate | datetime | full | The start (00:00 UTC) of the reference time's day. |
current_date | datetime | full | The start (00:00 UTC) of the reference time's day. |
current_time | datetime | full | The reference time's time of day as a duration since midnight UTC. |
current_timestamp | datetime | full | The query's reference time. |
curtime | datetime | full | The reference time's time of day as a duration since midnight UTC. |
date | datetime | full | The start (00:00 UTC) of t's day; t may be a timestamp or a date or timestamp string. |
date_add | full | lowered to date_add | |
date_format | datetime | full | t formatted with MySQL-style % specifiers. |
date_sub | full | lowered to date_add | |
datediff | datetime | full | Whole days from b's date to a's date. |
datetime | datetime | full | t as a timestamp, converted to time zone to_tz when given. |
day | datetime | full | Day of month (1-31) of t. |
day_of_month | datetime | full | Day of month (1-31) of t. |
day_of_week | datetime | full | Day of week of t, 1 = Sunday to 7 = Saturday. |
day_of_year | datetime | full | Day of year (1-366) of t. |
dayname | datetime | full | English name of t's weekday. |
dayofmonth | datetime | full | Day of month (1-31) of t. |
dayofweek | datetime | full | Day of week of t, 1 = Sunday to 7 = Saturday. |
dayofyear | datetime | full | Day of year (1-366) of t. |
degrees | math | full | Trigonometric or angle-conversion function degrees in radians; NULL outside its domain. |
divide | full | lowered to arithmetic operator / | |
e | math | full | The constant e. |
earliest | full | lowered to time comparison | |
exists | collection | full | TRUE when the predicate is TRUE for some element, NULL when none is TRUE but some is NULL. |
exp | math | full | e raised to the power x. |
expm1 | math | full | e raised to the power x, minus 1. |
filter | collection | full | Elements for which the predicate is TRUE. |
floor | math | full | Largest integer not greater than x. |
forall | collection | full | TRUE when the predicate is TRUE for every element (TRUE for an empty array), NULL when none is FALSE but some is NULL. |
from_days | datetime | full | The date n days after year 0. |
from_unixtime | datetime | full | The timestamp seconds after the Unix epoch; formatted with MySQL-style specifiers when format is given. |
geoip | unsupported | LynxDB has no GeoIP database | |
hour | datetime | full | Hour (0-23) of t. |
hour_of_day | datetime | full | Hour (0-23) of t. |
if | full | lowered to CASE | |
ifnull | full | lowered to coalesce | |
ilike | full | lowered to like | |
isblank | full | lowered to is_blank | |
isempty | full | lowered to is_empty | |
isnotnull | full | lowered to NOT is_null | |
isnull | full | lowered to is_null | |
ispresent | full | lowered to NOT is_null | |
json | json | full | s when it is valid JSON text, else NULL. |
json_append | json | full | s with each value appended to the array at its path. |
json_array | json | full | JSON array text of the arguments. |
json_array_length | json | full | Number of elements of the JSON array s; NULL when s is not a JSON array. |
json_delete | json | full | s without the members at the paths. |
json_extend | json | full | s with each array value's elements appended to the array at its path. |
json_extract | json | full | The value at a JSON path in s; with several paths a JSON array of the values. |
json_keys | json | full | JSON array text of the top-level keys of the JSON object s; NULL for non-objects. |
json_object | json | full | JSON object text from alternating keys and values. |
json_set | json | full | s with the value at each path replaced or created. |
json_valid | json | full | TRUE when s is valid JSON text; FALSE for NULL. |
last_day | datetime | full | The start of the last day of t's month. |
latest | full | lowered to time comparison | |
left | string | full | The first n characters of s. |
length | string | full | Number of characters (Unicode code points) in s. |
like | full | lowered to like | |
ln | math | full | Natural logarithm; NULL for x <= 0. |
localtime | datetime | full | The query's reference time. |
localtimestamp | datetime | full | The query's reference time. |
locate | string | full | 1-based position of the first occurrence of sub in s at or after start, or 0. |
log | math | full | Natural logarithm of x, or with two arguments the base-b logarithm of x; NULL for non-positive input. |
log10 | math | full | Base-10 logarithm; NULL for x <= 0. |
log2 | math | full | Base-2 logarithm; NULL for x <= 0. |
lower | string | full | s in lower case. |
ltrim | string | full | s without leading white space. |
makedate | datetime | full | The date for a year and day of year. |
maketime | datetime | full | A time of day from its parts. |
match | full | lowered to SEARCH_WORD | |
match_bool_prefix | unsupported | LynxDB has no relevance scoring; use match, match_phrase or match_phrase_prefix on one field, the search command, or where with like or regexp_match | |
match_phrase | full | lowered to SEARCH_PHRASE | |
match_phrase_prefix | full | lowered to SEARCH_PREFIX | |
max | math | full | Largest argument; NULL arguments are ignored. |
md5 | cryptographic | full | Hex MD5 digest of s. |
microsecond | datetime | full | Microseconds (0-999999) of t. |
min | math | full | Smallest argument; NULL arguments are ignored. |
minute | datetime | full | Minute (0-59) of t. |
minute_of_day | datetime | full | Minutes since midnight of t. |
minute_of_hour | datetime | full | Minute (0-59) of t. |
mod | math | full | Remainder of n divided by m with the sign of n; NULL when m is 0. |
modulus | math | full | Remainder of n divided by m with the sign of n; NULL when m is 0. |
month | datetime | full | Month (1-12) of t. |
month_of_year | datetime | full | Month (1-12) of t. |
monthname | datetime | full | English name of t's month. |
multi_match | unsupported | LynxDB has no relevance scoring; use match, match_phrase or match_phrase_prefix on one field, the search command, or where with like or regexp_match | |
multiply | full | lowered to arithmetic operator * | |
mvappend | collection | full | Array of the arguments with array arguments flattened one level; NULL arguments are skipped. |
mvdedup | collection | full | a with later duplicates removed. |
mvfind | collection | full | 0-based index of the first element matching regex, or NULL. |
mvindex | collection | full | The element at 0-based start, or with end the inclusive sub-array; negative indexes count from the end. |
mvjoin | collection | full | String forms of the non-NULL elements joined by sep. |
mvmap | full | lowered to transform | |
mvzip | collection | full | Pairwise string concatenation of a and b joined by sep (default comma), as long as the shorter. |
now | datetime | full | The query's reference time. |
nullif | full | lowered to CASE | |
period_add | datetime | full | Period YYMM or YYYYMM plus n months, as YYYYMM. |
period_diff | datetime | full | Months from period b to period a. |
pi | math | full | The constant pi. |
position | full | lowered to locate | |
pow | math | full | x raised to the power y. |
power | math | full | x raised to the power y. |
quarter | datetime | full | Quarter (1-4) of t. |
query_string | unsupported | LynxDB has no relevance scoring; use match, match_phrase or match_phrase_prefix on one field, the search command, or where with like or regexp_match | |
radians | math | full | Trigonometric or angle-conversion function radians in radians; NULL outside its domain. |
rand | math | full | Uniform random float in [0, 1); with a seed the sequence is reproducible within one query. |
reduce | collection | full | Left fold of the elements starting at init, finished by the optional one-parameter lambda. |
regexp_match | string | full | TRUE when the RE2 pattern matches anywhere in s. |
regexp_replace | string | full | s with every match of the RE2 pattern replaced; $1 or \1 in the replacement inserts a group. |
replace | full | lowered to regexp_replace | |
reverse | string | full | s with its characters in reverse order. |
right | string | full | The last n characters of s. |
rint | math | full | x rounded to the nearest integer, ties to even. |
round | math | full | x rounded half away from zero to d decimal places (default 0). |
rtrim | string | full | s without trailing white space. |
sec_to_time | datetime | full | seconds as a duration. |
second | datetime | full | Second (0-59) of t. |
second_of_minute | datetime | full | Second (0-59) of t. |
sha1 | cryptographic | full | Hex SHA-1 digest of s. |
sha2 | cryptographic | full | Hex SHA-2 digest of s with 224, 256, 384 or 512 bits. |
sign | math | full | -1, 0 or 1 according to the sign of x. |
signum | math | full | -1, 0 or 1 according to the sign of x. |
simple_query_string | unsupported | LynxDB has no relevance scoring; use match, match_phrase or match_phrase_prefix on one field, the search command, or where with like or regexp_match | |
sin | math | full | Trigonometric or angle-conversion function sin in radians; NULL outside its domain. |
sinh | math | full | Trigonometric or angle-conversion function sinh in radians; NULL outside its domain. |
split | collection | full | s split at every occurrence of sep; the empty separator splits into characters. |
sqrt | math | full | Square root; NULL for negative x. |
str_to_date | datetime | full | s parsed with MySQL-style % specifiers. |
strcmp | string | full | -1, 0 or 1 comparing a and b by code points. |
strftime | datetime | full | t (a timestamp or epoch seconds) formatted with POSIX strftime % specifiers. |
subdate | full | lowered to date_add | |
substr | string | full | Substring of s from 1-based position start (negative counts from the end), of at most length characters. |
substring | string | full | Substring of s from 1-based position start (negative counts from the end), of at most length characters. |
subtime | datetime | full | t minus the time of day or duration delta. |
subtract | full | lowered to arithmetic operator - | |
sum | math | full | Sum of the arguments. |
sysdate | datetime | full | The wall-clock time at evaluation. |
tan | math | full | Trigonometric or angle-conversion function tan in radians; NULL outside its domain. |
time | datetime | full | t's time of day as a duration since midnight; t may be a timestamp, a duration or a time string. |
time_format | datetime | full | The time-of-day part of t formatted with MySQL-style % specifiers. |
time_to_sec | datetime | full | Seconds of t's time of day. |
timediff | datetime | full | a minus b as a duration. |
timestamp | datetime | full | t as a timestamp, plus the time of day add when given. |
to_days | datetime | full | Days since year 0 of t's date. |
to_seconds | datetime | full | Seconds since year 0 of t. |
tonumber | conversion | full | s parsed as a number in base (default 10); an int when it has no fraction, else a float. |
tostring | conversion | full | String form of x; formats: hex, binary, commas, duration. |
transform | collection | full | Array of the lambda applied to each element (and its 0-based index for a two-parameter lambda). |
trim | string | full | s without leading and trailing white space. |
typeof | system | full | Name of the runtime type of x: null, bool, int, float, string, timestamp, duration, array, object. |
unix_timestamp | datetime | full | Seconds since the Unix epoch of t, or of the reference time. |
upper | string | full | s in upper case. |
utc_date | datetime | full | The start (00:00 UTC) of the reference time's day. |
utc_time | datetime | full | The reference time's time of day as a duration since midnight UTC. |
utc_timestamp | datetime | full | The query's reference time. |
week | datetime | full | Week number of t under MySQL week mode 0-7 (default 0). |
week_of_year | datetime | full | Week number of t under MySQL week mode 0-7 (default 0). |
weekday | datetime | full | Day of week of t, 0 = Monday to 6 = Sunday. |
year | datetime | full | Year of t. |
yearweek | datetime | full | Year and week of t as YYYYWW under MySQL week mode 0-7 (default 0). |