ESQL: Functions!

[nik9000]

Description

Description

This is a list of "shovel ready" functions. It's functions we are fairly sure we want and we should be able to start working on them now. This list is not sorted at all, partly because making any one of these functions shouldn't be a huge effort so the cost of having a sorted list is comparatively high.

This list is not sacred. If you need a function to do something, stick it on the list. Maybe even build it yourself, it's fun!

Weird

• TYPEOF
  • A constant string returning the type of the column?

IP

• AUTO_BUCKET for IPs
• MASK (called IP_PREFIX)

Math

• COPY_SIGN Add ES|QL copy_sign function #126486 Implementing copy_sign function for ESQL #128281
• SCALB ESQL: add scalb function #127696
• PI
• TAU
• ACOS
• ASIN
• ATAN
• ATAN2
• AUTO_BUCKET for numbers
• cube root - java calls this CBRT @ivancea
• CEIL @alex-spies
• COS
• COSH
• EXP
• FLOOR
• GREATEST(a, b, c) - max of columns, not an agg ESQL: LEAST and GREATEST functions #98630
• HYPOT
• LEAST(a, b, c) - min of columns, not an agg ESQL: LEAST and GREATEST functions #98630
• LOG - base e
• LOG - base 10
• LOG - any base
• RANDOM - harder than it looks
• SIGNUM - https://en.wikipedia.org/wiki/Sign_function @ioanatia Add ES|QL signum function #106866
• SIN
• SINH
• square root - java calls this SQRT Sqrt function for ESQL #98449
• SUM(a, b, c) - sum of columns, not an agg Let's just use a + b + c or MV_SUM(a) + MV_SUM(b) + MV_SUM(c)
• TAN
• TANH
• RADIANS_TO_DEGREES (to_degrees)
• DEGREES_TO_RADIANS (to_radians)

String

• ASCII
• CHAR - presumably we'd want to emit unicode code points. Not sure how we'd validate or if we have to. It'd just utf-8 encode the code point and stick it a BytesRef
• INSERT (@timgrein)
• BIT_LENGTH Add ES|QL bit_length function #115792 (@timgrein)
• TO_LOWERCASE - SQL calls this LCASE. There are locale issues with this, but I presume we'd use the ROOT locale. ESQL: Add TO_UPPER and TO_LOWER functions #104309
• LEFT(foo, len) - basically an alias for SUBSTRING(foo, 0, len)
• ES|QL LOCATE function #106818 @tteofili
• LTRIM ESQL: LTRIM, RTRIM and fix unicode whitespace #98590
• BYTES_LENGTH - Number of bytes in the string as encoded in utf-8. SQL calls this OCTET_LENGTH. Not sure this is useful for people though. [ES|QL] Add support for byte_length scalar function #116591
• POSITION Add ES|QL Locate function #106899
• REPEAT
• REPLACE Eval REPLACE function #98909
• REVERSE [ES|QL] add reverse function #113297
• RIGHT - See LEFT
• RTRIM ESQL: LTRIM, RTRIM and fix unicode whitespace #98590
• SPACE [ESQL] Add SPACE function #112350
• TRIM
• UCASE - Locales will be fun here. ESQL: Add TO_UPPER and TO_LOWER functions #104309
• NUMBER or TO_NUMBER ESQL: convert a string to a number using a base #133728

Date

• DAY_NAME/DAYNAME Add ESQL DAY_NAME function #132535
• MONTH_NAME/MONTHNAME
• DATE_ADD/DATEADD/TIMESTAMP_ADD/TIMESTAMPADD (surogate: binary operators with time periods)
• DATE_DIFF/DATEDIFF/TIMESTAMP_DIFF/TIMESTAMPDIFF
• DATE_PART/DATEPART for now folks can use EXTRACT
• DAY_OF_MONTH/DOM/DAY for now folks can use EXTRACT
• DAY_OF_WEEK/DAYOFWEEK/DOW for now folks can use EXTRACT
• DAY_OF_YEAR/DOY for now folks can use EXTRACT
• EXTRACT (DATE_EXTRACT)
• HOUR_OF_DAY/HOUR for now folks can use EXTRACT
• ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW for now folks can use EXTRACT
• ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW for now folks can use EXTRACT
• MINUTE_OF_DAY for now folks can use EXTRACT
• MINUTE_OF_HOUR/MINUTE for now folks can use EXTRACT
• MONTH_OF_YEAR/MONTH for now folks can use EXTRACT
• SECOND_OF_MINUTE/SECOND for now folks can use EXTRACT
• QUARTER for now folks can use EXTRACT
• WEEK_OF_YEAR/WEEK for now folks can use EXTRACT
• YEAR for now folks can use EXTRACT

Multi-valued

• MV_INTERSECT
• MV_UNION
• MV_CONTAINS Add MV_CONTAINS function #133099
• (Other interesting set operators?)

Encode/Decode

• CHARSET
  • Converts binary data form one charset to another. CHARSET(bytes field, source charset, target charset (optional - default could be UTF-8))
  • UTF-8 bytes fields have the same data as keyword fields. We should make there be a nice way to convert. Shouldn't need copies.
  • We don't have a bytes data type in ESQL yet.
• FROM_/TO_BASE64#107135
• URL_ENCODE/URL_DECODE

Secure Hashing

• , SHAKE_128, SHAKE_256 (they are not available using MessageDigest)
• MD5, SHA1, SHA256
• implement more hash function aliases (if needed), such as: SHA_0, SHA_1, SHA_256, SHA_512. Currently they are available using hash(.., ..)
• GENERIC SECURE_HASH() by relying on the underlying MessageDigest.getInstance

Aggregate (STATS ... BY)

• CORR(x,y) - correlation coefficient
• COVAR_POP(x,y) - population covariance (without bias correction)
• COVAR_SAMPL(x,y) - sample covariance (with Bessel's bias correction)
• ENTROPY(x) - Shannon/log-2 entropy
• KURTOSIS(x) - excess kurtosis (Fisher's definition?) with bias from on sample size
• KURTOSIS_POP(x) - excess kurtosis (Fisher's definition?) population without bias
• PERCENTILE_RANK(x) - ranking variant for percentile
• SKEWNESS(x)
• STDEV [ES|QL] Add a standard deviation function #116531 (uses Welford's algorithm - population? )
• STDDEV_POP - population variation for stddev @limotova
• STDDEV_SAMP- sampling variation for stddev @limotova
• VAR_POP(x) - population variance @limotova
• VAR_SAMP(X) - sample variance @limotova

NULL

• COALESCE ESQL: COALESCE function #98542

Score functions

• ESQL: Add decay function #121526
• sigmoid (similar to the painless version)
• saturation (similar to the painless version)

Miscellaneous functions

• FORMAT / PRINT output

[elasticsearchmachine]

Pinging @elastic/es-ql (Team:QL)

[elasticsearchmachine]

Pinging @elastic/elasticsearch-esql (:Query Languages/ES|QL)

[nik9000]

I wrote up a guide to making new functions about six months ago that's gone stale. I'll try and build another guide for it on Monday.

[dreamquster]

@nik9000 I had wrote partial logic of 'left' function. Now I wonder how do i write the manual document about it.
Furthermore, the variable named 'length' should be optional or mandatory
This is my pull request: #98660

[nik9000]

Here are some instructions for adding functions.

[elasticsearchmachine]

Pinging @elastic/es-analytics-geo (Team:Analytics)

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)