// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Supported Operators and Functions

NOTE: For more information on functions supported by Apache Calcite, see the link:https://calcite.apache.org/docs/reference.html#operators-and-functions[product documentation,window=_blank].

== Aggregate Functions

=== AVG

[source,sql]
----
AVG( [ ALL | DISTINCT ] numeric)
----

Returns the average (arithmetic mean) of numeric across all input values. When used, the type of data will be changed in the following way:

[cols="1,1,1", opts="header"]
|===

|Input type| 	Result type| 	Minimum scale
|`DECIMAL`, `BIGINT`, `INTEGER`, `SMALLINT`, `TINYINT`| `DECIMAL`| 	16
|`DOUBLE`, `REAL`| `DOUBLE`|
|===

=== COUNT

[source,sql]
----
COUNT( [ ALL | DISTINCT ] value [, value ]*)
----

Returns the number of input rows for which value is not null (wholly not null if value is composite).

=== MAX

[source,sql]
----
MAX( [ ALL | DISTINCT ] value)
----

Returns the maximum value across all input values.

=== MIN

[source,sql]
----
MIN( [ ALL | DISTINCT ] value)
----

Returns the minimum value across all input values.

=== SUM

[source,sql]
----
SUM( [ ALL | DISTINCT ] numeric)
----

Returns the sum of numeric across all input values.

=== ANY_VALUE

[source,sql]
----
ANY_VALUE( [ ALL | DISTINCT ] value)
----

Returns one of the values of value across all input values; this is NOT specified in the SQL standard.

=== EVERY

[source,sql]
----
EVERY(condition)
----

Returns TRUE if all of the values of condition are TRUE.

=== SOME

[source,sql]
----
SOME(condition)
----

Returns TRUE if one or more of the values of condition is TRUE.

=== GROUPING

[source,sql]
----
GROUPING(column_reference [,column_reference])
----

Returns a bit vector of the given grouping expressions.

== JSON Functions

=== JSON_TYPE

[source,sql]
----
JSON_TYPE(jsonValue)
----

Returns a string value indicating the type of jsonValue.

=== FORMAT JSON

Indicates that the value is formatted as JSON.

=== JSON_VALUE

[source,sql]
----
JSON_VALUE(jsonValue, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] )
----

Extract an SQL scalar from a jsonValue using JSON path expression path.

=== JSON_QUERY

[source,sql]
----
JSON_QUERY(jsonValue, path [ RETURNING type ] [ { WITHOUT [ ARRAY ] | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] } WRAPPER ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ] )
----

Extract a JSON object or JSON array from jsonValue using the path JSON path expression.

=== JSON_TYPE

[source,sql]
----
JSON_TYPE(jsonValue)
----

Returns a string value indicating the type of `jsonValue`.

=== JSON_EXISTS

[source,sql]
----
JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] )
----

Whether a jsonValue satisfies a search criterion described using JSON path expression path.

=== JSON_DEPTH

[source,sql]
----
JSON_DEPTH(jsonValue)
----

Returns an integer value indicating the depth of jsonValue.

=== JSON_KEYS

[source,sql]
----
JSON_KEYS(jsonValue [, path ])
----

Returns a string indicating the keys of a JSON jsonValue.

=== JSON_PRETTY

[source,sql]
----
JSON_PRETTY(jsonValue)
----

Returns a pretty-printing of jsonValue.

=== JSON_LENGTH

[source,sql]
----
JSON_LENGTH(jsonValue [, path ])
----

Returns a integer indicating the length of jsonValue.

=== JSON_REMOVE

[source,sql]
----
JSON_REMOVE(jsonValue, path [, path ])
----

Removes data from jsonValue using a series of path expressions and returns the result.

=== JSON_STORAGE_SIZE

[source,sql]
----
JSON_STORAGE_SIZE(jsonValue)
----

Returns the number of bytes used to store the binary representation of jsonValue.

=== JSON_OBJECT

[source,sql]
----
JSON_OBJECT( jsonKeyVal [, jsonKeyVal ]* [ nullBehavior ] )
----

Construct JSON object using a series of key-value pairs.

=== JSON_ARRAY

[source,sql]
----
JSON_ARRAY( [ jsonVal [, jsonVal ]* ] [ nullBehavior ] )
----

Construct a JSON array using a series of values.

=== IS JSON VALUE

[source,sql]
----
jsonValue IS JSON [ VALUE ]
----

Whether jsonValue is a JSON value.

=== IS JSON OBJECT

[source,sql]
----
jsonValue IS JSON OBJECT
----

Whether jsonValue is a JSON object.

=== IS JSON ARRAY

[source,sql]
----
jsonValue IS JSON ARRAY
----

Whether jsonValue is a JSON array.

=== IS JSON SCALAR

[source,sql]
----
jsonValue IS JSON SCALAR
----

Whether jsonValue is a JSON scalar value.

== Regular Expression Functions

=== POSIX REGEX CASE INSENSITIVE

[source,sql]
----
value 1 POSIX REGEX CASE INSENSITIVE value 2
----

Case-sensitive POSIX regular expression.

=== POSIX REGEX CASE SENSITIVE

[source,sql]
----
value 1 POSIX REGEX CASE SENSITIVE value 2
----

Case-sensitive POSIX regular expression.


=== REGEXP_REPLACE

[source,sql]
----
REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [, matchType]]])
----

Replaces all substrings of string that match regexp with rep at the starting pos in expr (if omitted, the default is 1), occurrence specifies which occurrence of a match to search for (if omitted, the default is 1), matchType specifies how to perform matching

[source,sql]
----
REGEXP_REPLACE(string, regexp)
----

Replaces all substrings of value that match regexp with an empty string and returns modified value.

== Numeric Functions

=== MOD

[source,sql]
----
MOD(numeric1, numeric2)
----

Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative.

=== EXP

[source,sql]
----
EXP(numeric)
----

Returns e raised to the power of numeric.

=== POWER

[source,sql]
----
POWER(numeric1, numeric2)
----

Returns numeric1 raised to the power of numeric2.

=== LN

[source,sql]
----
LN(numeric)
----

Returns the natural logarithm (base e) of numeric.

=== LOG10

[source,sql]
----
LOG10(numeric)
----

Returns the base 10 logarithm of numeric.

=== ABS

[source,sql]
----
ABS(numeric)
----

Returns the absolute value of numeric.

=== RAND

[source,sql]
----
RAND([seed])
----

Generates a random double between 0 and 1 inclusive, optionally initializing the random number generator with seed.

=== RAND_INTEGER

[source,sql]
----
RAND_INTEGER([seed, ] numeric)
----

Generates a random integer between 0 and numeric - 1 inclusive, optionally initializing the random number generator with seed.

=== ACOS

[source,sql]
----
ACOS(numeric)
----

Returns the arc cosine of numeric.

=== ASIN

[source,sql]
----
ASIN(numeric)
----

Returns the arc sine of numeric.

=== ATAN

[source,sql]
----
ATAN(numeric)
----

Returns the arc tangent of numeric.

=== ATAN2

[source,sql]
----
ATAN2(numeric, numeric)
----

Returns the arc tangent of the numeric coordinates.

=== SQRT

[source,sql]
----
SQRT(numeric)
----

Returns the square root of numeric.

=== CBRT

[source,sql]
----
CBRT(numeric)
----

Returns the cube root of numeric.

=== COS

[source,sql]
----
COS(numeric)
----

Returns the cosine of numeric.

=== COSH

[source,sql]
----
COSH(numeric)
----

Returns the hyperbolic cosine of numeric.

=== COT

[source,sql]
----
COT(numeric)
----

Returns the cotangent of numeric.

=== DEGREES

[source,sql]
----
DEGREES(numeric)
----

Converts numeric from radians to degrees.

=== RADIANS

[source,sql]
----
RADIANS(numeric)
----

Converts numeric from degrees to radians.

=== ROUND

[source,sql]
----
ROUND(numeric1 [, integer2])
----

Rounds numeric1 to optionally integer2 (if not specified 0) places right to the decimal point.

=== SIGN

[source,sql]
----
SIGN(numeric)
----

Returns the signum of numeric.

=== SIN

[source,sql]
----
SIN(numeric)
----

Returns the sine of numeric.

=== SINH

[source,sql]
----
SINH(numeric)
----

Returns the hyperbolic sine of numeric.

=== TAN

[source,sql]
----
TAN(numeric)
----

Returns the tangent of numeric.

=== TANH

[source,sql]
----
TANH(numeric)
----

Returns the hyperbolic tangent of numeric.

=== TRUNCATE

[source,sql]
----
TRUNCATE(numeric1 [, integer2])
----

Truncates numeric1 to optionally integer2 (if not specified 0) places right to the decimal point.

=== PI

[source,sql]
----
PI()
----

Returns a value that is closer than any other value to Pi.

== String Functions

=== UPPER

[source,sql]
----
UPPER(string)
----

Returns a character string converted to upper case.

=== LOWER

[source,sql]
----
LOWER(string)
----

Returns a character string converted to lower case.

=== INITCAP

[source,sql]
----
INITCAP(string)
----

Returns string with the first letter of each word converter to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.

=== TO_BASE64

[source,sql]
----
TO_BASE64(string)
----
Converts the string to base-64 encoded form and returns an encoded string

=== FROM_BASE64

[source,sql]
----
FROM_BASE64(string)
----

Returns the decoded result of a base-64 string as a string.

=== MD5

[source,sql]
----
MD5(string)
----

Calculates an MD5 128-bit checksum of string and returns it as a hex string.

=== SHA1

[source,sql]
----
SHA1(string)
----

Calculates a SHA-1 hash value of string and returns it as a hex string.

=== SUBSTRING

[source,sql]
----
SUBSTRING(string FROM integer)
----

Returns a substring of a character string starting at a given point.

[source,sql]
----
SUBSTRING(string FROM integer FOR integer)
----

Returns a substring of a character string starting at a given point with a given length.

[source,sql]
----
SUBSTRING(binary FROM integer)
----

Returns a substring of binary starting at a given point.

[source,sql]
----
SUBSTRING(binary FROM integer FOR integer)

----

Returns a substring of binary starting at a given point with a given length.

=== LEFT

[source,sql]
----
LEFT(string, length)
----

Returns the leftmost length characters from the string.

=== RIGHT

[source,sql]
----
RIGHT(string, length)
----

Returns the rightmost length characters from the string.

=== REPLACE

[source,sql]
----
REPLACE(char, search_string [, replace_string])
----

Replaces search_string with replace_string.

=== TRANSLATE

[source,sql]
----
TRANSLATE(expr, fromString, toString)
----
Returns expr with all occurrences of each character in fromString replaced by its corresponding character in toString. Characters in expr that are not in fromString are not replaced.


=== CHR

[source,sql]
----
CHR(integer)
----

Returns the character whose UTF-8 code is integer.

=== CHAR_LENGTH

[source,sql]
----
CHAR_LENGTH(string)
----

Returns the number of characters in a character string.

=== CHARACTER_LENGTH

[source,sql]
----
CHARACTER_LENGTH(string)
----

Returns the number of characters in a character string.

=== ||

[source,sql]
----
string || string
----

Concatenates two character strings.

=== CONCAT

[source,sql]
----
CONCAT(string, string)
----

Concatenates two strings, returns null only when both string arguments are null, otherwise treats null as empty string.

[source,sql]
----
CONCAT(string [, string ]*)
----

Concatenates one or more strings, returns null if any of the arguments is null.

[source,sql]
----
CONCAT(string [, string ]*)
----

Concatenates one or more strings, null is treated as empty string.

=== OVERLAY

[source,sql]
----
OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])
----

Replaces a substring of string1 with string2.

[source,sql]
----
OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ])
----

Replaces a substring of binary1 with binary2.

=== POSITION

[source,sql]
----
POSITION(substring IN string)
----

Returns the position of the first occurrence of substring in string.

[source,sql]
----
POSITION(substring IN string FROM integer)
----

Returns the position of the first occurrence of substring in string starting at a given point (not standard SQL).

[source,sql]
----
POSITION(binary1 IN binary2)
----

Returns the position of the first occurrence of binary1 in binary2.

[source,sql]
----
POSITION(binary1 IN binary2 FROM integer)
----

Returns the position of the first occurrence of binary1 in binary2 starting at a given point (not standard SQL).

=== ASCII

[source,sql]
----
ASCII(string)
----

Returns the ASCII code of the first character of string; if the first character is a non-ASCII character, returns its Unicode code point; returns 0 if string is empty.

=== REPEAT

[source,sql]
----
REPEAT(string, integer)
----

Returns a string consisting of string repeated of integer times; returns an empty string if integer is less than 1.

=== SPACE

[source,sql]
----
SPACE(integer)
----
Returns a string with an integer number of spaces; returns an empty string if integer is less than 1.


=== STRCMP

[source,sql]
----
STRCMP(string, string)
----

Returns 0 if both of the strings are same and returns -1 when the first argument is smaller than the second and 1 when the second one is smaller than the first one.

=== SOUNDEX

[source,sql]
----
SOUNDEX(string)
----

* Returns the phonetic representation of string; throws if string is encoded with multi-byte encoding such as UTF-8; or
* Returns the phonetic representation of string; return original string if string is encoded with multi-byte encoding such as UTF-8

=== DIFFERENCE

[source,sql]
----
DIFFERENCE(string, string)
----

Returns a measure of the similarity of two strings, namely the number of character positions that their SOUNDEX values have in common: 4 if the SOUNDEX values are same and 0 if the SOUNDEX values are totally different.

=== REVERSE

[source,sql]
----
REVERSE(string)
----

Returns string with the order of the characters reversed.

=== TRIM

[source,sql]
----
TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)
----

Removes the longest string containing only the characters in string1 from the start/end/both ends of string1.

=== LTRIM

[source,sql]
----
LTRIM(string)
----

Returns string with all blanks removed from the start.

=== RTRIM

[source,sql]
----
RTRIM(string)
----

Returns string with all blanks removed from the end.

=== SUBSTR

[source,sql]
----
SUBSTR(string, position [, substringLength ])
----

Returns a portion of string, beginning at character position, substringLength characters long. SUBSTR calculates lengths using characters as defined by the input character set.

=== LENGTH

[source,sql]
----
LENGTH(string)
----

Equivalent to CHAR_LENGTH(string).

=== OCTET_LENGTH

[source,sql]
----
OCTET_LENGTH(binary)
----

Returns the number of bytes in binary.

=== LIKE

[source,sql]
----
string1 LIKE string2 [ ESCAPE string3 ]
----

Whether string1 matches pattern string2.

=== SIMILAR TO

[source,sql]
----
string1 SIMILAR TO string2 [ ESCAPE string3 ]
----

Whether string1 matches regular expression string2.

== Date/Time Functions

=== EXTRACT

[source,sql]
----
EXTRACT(timeUnit FROM datetime)
----

Extracts and returns the value of a specified datetime field from a datetime value expression.

=== FLOOR

[source,sql]
----
FLOOR(datetime TO timeUnit)
----

Rounds datetime down to timeUnit.

=== CEIL

[source,sql]
----
CEIL(datetime TO timeUnit)
----

Rounds datetime up to timeUnit.

=== TIMESTAMPDIFF

[source,sql]
----
TIMESTAMPDIFF(timeUnit, datetime, datetime2)
----

Returns the (signed) number of timeUnit intervals between datetime and datetime2. Equivalent to (datetime2 - datetime) timeUnit.

=== LAST_DAY

[source,sql]
----
LAST_DAY(date)
----

Returns the date of the last day of the month in a value of datatype DATE; For example, it returns DATE’2020-02-29’ for both DATE’2020-02-10’ and TIMESTAMP’2020-02-10 10:10:10’.

=== DAYNAME

[source,sql]
----
DAYNAME(datetime)
----

Returns the name of the day of the week based on the datetime value.

=== MONTHNAME

[source,sql]
----
MONTHNAME(date)
----

Returns the name, in the connection’s locale, of the month in datetime; for example, it returns ‘二月’ for both DATE ‘2020-02-10’ and TIMESTAMP ‘2020-02-10 10:10:10’.

=== DAYOFMONTH

[source,sql]
----
DAYOFMONTH(date)
----

Equivalent to EXTRACT(DAY FROM date). Returns an integer between 1 and 31.

=== DAYOFWEEK

[source,sql]
----
DAYOFWEEK(date)
----

Equivalent to EXTRACT(DOW FROM date). Returns an integer between 1 and 7.

=== DAYOFYEAR

[source,sql]
----
DAYOFYEAR(date)
----

Equivalent to EXTRACT(DOY FROM date). Returns an integer between 1 and 366.

=== YEAR

[source,sql]
----
YEAR(date)
----

Equivalent to EXTRACT(YEAR FROM date). Returns an integer.

=== QUARTER

[source,sql]
----
QUARTER(date)
----

Equivalent to EXTRACT(QUARTER FROM date). Returns an integer between 1 and 4.

=== MONTH

[source,sql]
----
MONTH(date)
----

Equivalent to EXTRACT(MONTH FROM date). Returns an integer between 1 and 12.

=== WEEK

[source,sql]
----
WEEK(date)
----

Equivalent to EXTRACT(WEEK FROM date). Returns an integer between 1 and 53.

=== HOUR

[source,sql]
----
HOUR(date)
----

Equivalent to EXTRACT(HOUR FROM date). Returns an integer between 0 and 23.

=== MINUTE

[source,sql]
----
MINUTE(date)
----

Equivalent to EXTRACT(MINUTE FROM date). Returns an integer between 0 and 59.

=== SECOND

[source,sql]
----
SECOND(date)
----

Equivalent to EXTRACT(SECOND FROM date). Returns an integer between 0 and 59.

=== TIMESTAMP_SECONDS

[source,sql]
----
TIMESTAMP_SECONDS(integer)
----

Returns the TIMESTAMP that is integer seconds after 1970-01-01 00:00:00.

=== TIMESTAMP_MILLIS

[source,sql]
----
TIMESTAMP_MILLIS(integer)
----

Returns the TIMESTAMP that is integer milliseconds after 1970-01-01 00:00:00.

=== TIMESTAMP_MICROS

[source,sql]
----
TIMESTAMP_MICROS(integer)
----

Returns the TIMESTAMP that is integer microseconds after 1970-01-01 00:00:00.

=== UNIX_SECONDS

[source,sql]
----
UNIX_SECONDS(timestamp)
----

Returns the number of seconds since 1970-01-01 00:00:00.

=== UNIX_MILLIS

[source,sql]
----
UNIX_MILLIS(timestamp)
----

Returns the number of milliseconds since 1970-01-01 00:00:00.

=== UNIX_MICROS

[source,sql]
----
UNIX_MICROS(timestamp)
----

Returns the number of microseconds since 1970-01-01 00:00:00.

=== UNIX_DATE

[source,sql]
----
UNIX_DATE(date)
----

Returns the number of days since 1970-01-01

=== DATE_FROM_UNIX_DATE

[source,sql]
----
DATE_FROM_UNIX_DATE(integer)
----

Returns the DATE that is integer days after 1970-01-01.

=== DATE

[source,sql]
----
DATE(timestamp)
----

Extracts the DATE from a timestamp.

[source,sql]
----
DATE(timestampLtz)
----

Extracts the DATE from timestampLtz (an instant; BigQuery’s TIMESTAMP type), assuming UTC.

[source,sql]
----
DATE(timestampLtz, timeZone)
----

Extracts the DATE from timestampLtz (an instant; BigQuery’s TIMESTAMP type) in timeZone.

[source,sql]
----
DATE(string)
----

Equivalent to CAST(string AS DATE).

[source,sql]
----
DATE(year, month, day)
----

Returns a DATE value for year, month, and day (all of type INTEGER).

=== CURRENT_TIMESTAMP

[source,sql]
----
CURRENT_TIMESTAMP
----

Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH LOCAL TIME ZONE.

=== CURRENT_DATE

[source,sql]
----
CURRENT_DATE
----

Returns the current date in the session time zone, in a value of datatype DATE.

=== LOCALTIME

[source,sql]
----
LOCALTIME
----

Returns the current date and time in the session time zone in a value of datatype TIME.

[source,sql]
----
LOCALTIME(precision)
----

Returns the current date and time in the session time zone in a value of datatype TIME, with precision digits of precision.

=== LOCALTIMESTAMP

[source,sql]
----
LOCALTIMESTAMP
----

Returns the current date and time in the session time zone in a value of datatype TIMESTAMP.

[source,sql]
----
LOCALTIMESTAMP(precision)
----

Returns the current date and time in the session time zone in a value of datatype TIMESTAMP, with precision digits of precision.

== Other Functions

=== CAST

[source,sql]
----
CAST(value AS type)
----

Converts a value to a given type. Casts between integer types truncate towards 0.

=== COALESCE

[source,sql]
----
COALESCE(value, value [, value ]*)
----

Provides a value if the first value is null. For example, COALESCE(NULL, 5) returns 5.

=== GREATEST

[source,sql]
----
GREATEST(expr [, expr ]*)
----

Returns the greatest of the expressions.

=== NULLIF

[source,sql]
----
NULLIF(value, value)
----

Returns NULL if the values are the same. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.

=== NVL

[source,sql]
----
NVL(value1, value2)
----

Returns value1 if value1 is not null, otherwise value2.

=== CASE

[source,sql]
----
CASE value
WHEN value1 [, value11 ]* THEN result1
[ WHEN valueN [, valueN1 ]* THEN resultN ]*
[ ELSE resultZ ]
END
----

Simple case.

[source,sql]
----
CASE
WHEN condition1 THEN result1
[ WHEN conditionN THEN resultN ]*
[ ELSE resultZ ]
END
----

Searched case.

=== DECODE

[source,sql]
----
DECODE(value, value1, result1 [, valueN, resultN ]* [, default ])
----

Compares value to each valueN value one by one; if value is equal to a valueN, returns the corresponding resultN, else returns default, or NULL if default is not specified.

=== LEAST

[source,sql]
----
LEAST(expr [, expr ]* )
----

Returns the least of the expressions.

=== COMPRESS

[source,sql]
----
COMPRESS(string)
----

Compresses a string using zlib compression and returns the result as a binary string.

=== TYPEOF

[source,sql]
----
TYPEOF value
----

Returns the type of the specified value.

=== RAND_UUID

[source,sql]
----
RAND_UUID
----

Generates a random UUID.

=== SYSTEM_RANGE

[source,sql]
----
SYSTEM_RANGE(start, end[, increment])
----

Returns a range from the table, with an optional increment.

== Security Functions

=== CURRENT_USER

[source,sql]
----
CURRENT_USER
----

Returns the name of the current database user. When security is disabled, returns the system user name instead.