Table of contents
- Introduction
- ABS
- ACOS
- ADD
- ASCII
- ASIN
- ATAN
- ATAN2
- CAST
- CBRT
- CEIL
- CONCAT
- CONCAT_WS
- CONV
- COS
- COSH
- COT
- CRC32
- CURDATE
- DATE
- DATE_FORMAT
- DAYOFMONTH
- DEGREES
- DIVIDE
- E
- EXP
- EXPM1
- FLOOR
- IF
- IFNULL
- ISNULL
- LEFT
- LENGTH
- LN
- LOCATE
- LOG
- LOG2
- LOG10
- LOWER
- LTRIM
- MAKETIME
- MOD
- MONTH
- MONTHNAME
- MULTIPLY
- NOW
- PI
- POW
- POWER
- RADIANS
- RAND
- REPLACE
- RIGHT
- RINT
- ROUND
- RTRIM
- SIGN
- SIGNUM
- SIN
- SINH
- SQRT
- SUBSTRING
- SUBTRACT
- TAN
- TIMESTAMP
- TRIM
- TRUNCATE
- UPPER
- YEAR
There is support for a wide variety of SQL functions. We are intend to generate this part of documentation automatically from our type system. However, the type system is missing descriptive information for now. So only formal specifications of all SQL functions supported are listed at the moment. More details will be added in future.
Most of the specifications can be self explained just as a regular function with data type as argument. The only notation that needs elaboration is generic type T which binds to an actual type and can be used as return type. For example, ABS(NUMBER T) -> T means function ABS accepts an numerical argument of type T which could be any sub-type of NUMBER type and returns the actual type of T as return type. The actual type binds to generic type at runtime dynamically.
Specifications:
- ABS(NUMBER T) -> T
Usage: acos(x) calculate the arc cosine of x. Returns NULL if x is not in the range -1 to 1.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT ACOS(0) fetched rows / total rows = 1/1 +--------------------+ | ACOS(0) | |--------------------| | 1.5707963267948966 | +--------------------+
Specifications:
- ADD(NUMBER T, NUMBER) -> T
Specifications:
- ASCII(STRING T) -> INTEGER
Usage: asin(x) calculate the arc sine of x. Returns NULL if x is not in the range -1 to 1.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT ASIN(0) fetched rows / total rows = 1/1 +-----------+ | ASIN(0) | |-----------| | 0.0 | +-----------+
Usage: atan(x) calculates the arc tangent of x. atan(y, x) calculates the arc tangent of y / x, except that the signs of both arguments are used to determine the quadrant of the result.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT ATAN(2), ATAN(2, 3) fetched rows / total rows = 1/1 +--------------------+--------------------+ | ATAN(2) | ATAN(2, 3) | |--------------------+--------------------| | 1.1071487177940904 | 0.5880026035475675 | +--------------------+--------------------+
Usage: atan2(y, x) calculates the arc tangent of y / x, except that the signs of both arguments are used to determine the quadrant of the result.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT ATAN2(2, 3) fetched rows / total rows = 1/1 +--------------------+ | ATAN2(2, 3) | |--------------------| | 0.5880026035475675 | +--------------------+
Specification is undefined and type check is skipped for now
Specifications:
- CBRT(NUMBER T) -> T
Specifications:
- CEIL(NUMBER T) -> T
Specification is undefined and type check is skipped for now
Specification is undefined and type check is skipped for now
Usage: CONV(x, a, b) converts the number x from a base to b base.
Argument type: x: STRING, a: INTEGER, b: INTEGER
Return type: STRING
Example:
od> SELECT CONV('12', 10, 16), CONV('2C', 16, 10), CONV(12, 10, 2), CONV(1111, 2, 10)
fetched rows / total rows = 1/1
+----------------------+----------------------+-------------------+---------------------+
| CONV('12', 10, 16) | CONV('2C', 16, 10) | CONV(12, 10, 2) | CONV(1111, 2, 10) |
|----------------------+----------------------+-------------------+---------------------|
| c | 44 | 1100 | 15 |
+----------------------+----------------------+-------------------+---------------------+
Usage: cos(x) calculate the cosine of x, where x is given in radians.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT COS(0) fetched rows / total rows = 1/1 +----------+ | COS(0) | |----------| | 1.0 | +----------+
Specifications:
- COSH(NUMBER T) -> DOUBLE
Usage: cot(x) calculate the cotangent of x. Returns out-of-range error if x equals to 0.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT COT(1) fetched rows / total rows = 1/1 +--------------------+ | COT(1) | |--------------------| | 0.6420926159343306 | +--------------------+
Usage: Calculates a cyclic redundancy check value and returns a 32-bit unsigned value.
Argument type: STRING
Return type: LONG
Example:
od> SELECT CRC32('MySQL')
fetched rows / total rows = 1/1
+------------------+
| CRC32('MySQL') |
|------------------|
| 3259397556 |
+------------------+
Specifications:
- CURDATE() -> DATE
Specifications:
- DATE(DATE) -> DATE
Specifications:
- DATE_FORMAT(DATE, STRING) -> STRING
- DATE_FORMAT(DATE, STRING, STRING) -> STRING
Specifications:
- DAYOFMONTH(DATE) -> INTEGER
Usage: degrees(x) converts x from radians to degrees.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT DEGREES(1.57) fetched rows / total rows = 1/1 +-------------------+ | DEGREES(1.57) | |-------------------| | 89.95437383553924 | +-------------------+
Specifications:
- DIVIDE(NUMBER T, NUMBER) -> T
Usage: E() returns the Euler's number
Return type: DOUBLE
Example:
od> SELECT E() fetched rows / total rows = 1/1 +-------------------+ | E() | |-------------------| | 2.718281828459045 | +-------------------+
Specifications:
- EXP(NUMBER T) -> T
Specifications:
- EXPM1(NUMBER T) -> T
Specifications:
- FLOOR(NUMBER T) -> T
Specifications:
- IF(BOOLEAN, ES_TYPE, ES_TYPE) -> ES_TYPE
Specifications:
- IFNULL(ES_TYPE, ES_TYPE) -> ES_TYPE
Specifications:
- ISNULL(ES_TYPE) -> INTEGER
Specifications:
- LEFT(STRING T, INTEGER) -> T
Specifications:
- LENGTH(STRING) -> INTEGER
Specifications:
- LN(NUMBER T) -> DOUBLE
Specifications:
- LOCATE(STRING, STRING, INTEGER) -> INTEGER
- LOCATE(STRING, STRING) -> INTEGER
Specifications:
- LOG(NUMBER T) -> DOUBLE
- LOG(NUMBER T, NUMBER) -> DOUBLE
Specifications:
- LOG2(NUMBER T) -> DOUBLE
Specifications:
- LOG10(NUMBER T) -> DOUBLE
Specifications:
- LOWER(STRING T) -> T
- LOWER(STRING T, STRING) -> T
Specifications:
- LTRIM(STRING T) -> T
Specifications:
- MAKETIME(INTEGER, INTEGER, INTEGER) -> DATE
Usage: MOD(n, m) calculates the remainder of the number n divided by m.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: Wider type between types of n and m if m is nonzero value. If m equals to 0, then returns NULL.
Example:
od> SELECT MOD(3, 2), MOD(3.1, 2) fetched rows / total rows = 1/1 +-------------+---------------+ | MOD(3, 2) | MOD(3.1, 2) | |-------------+---------------| | 1 | 1.1 | +-------------+---------------+
Specifications:
- MONTH(DATE) -> INTEGER
Specifications:
- MONTHNAME(DATE) -> STRING
Specifications:
- MULTIPLY(NUMBER T, NUMBER) -> NUMBER
Specifications:
- NOW() -> DATE
Usage: PI() returns the constant pi
Return type: DOUBLE
Example:
od> SELECT PI() fetched rows / total rows = 1/1 +-------------------+ | PI() | |-------------------| | 3.141592653589793 | +-------------------+
Usage: POW(x, y) calculates the value of x raised to the power of y. Bad inputs return NULL result.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT POW(3, 2), POW(-3, 2), POW(3, -2) fetched rows / total rows = 1/1 +-------------+--------------+--------------------+ | POW(3, 2) | POW(-3, 2) | POW(3, -2) | |-------------+--------------+--------------------| | 9.0 | 9.0 | 0.1111111111111111 | +-------------+--------------+--------------------+
Usage: POWER(x, y) calculates the value of x raised to the power of y. Bad inputs return NULL result.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT POWER(3, 2), POWER(-3, 2), POWER(3, -2) fetched rows / total rows = 1/1 +---------------+----------------+--------------------+ | POWER(3, 2) | POWER(-3, 2) | POWER(3, -2) | |---------------+----------------+--------------------| | 9.0 | 9.0 | 0.1111111111111111 | +---------------+----------------+--------------------+
Usage: radians(x) converts x from degrees to radians.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT RADIANS(90) fetched rows / total rows = 1/1 +--------------------+ | RADIANS(90) | |--------------------| | 1.5707963267948966 | +--------------------+
Usage: RAND()/RAND(N) returns a random floating-point value in the range 0 <= value < 1.0. If integer N is specified, the seed is initialized prior to execution. One implication of this behavior is with identical argument N, rand(N) returns the same value each time, and thus produces a repeatable sequence of column values.
Argument type: INTEGER
Return type: FLOAT
Example:
od> SELECT RAND(3) fetched rows / total rows = 1/1 +------------+ | RAND(3) | |------------| | 0.73105735 | +------------+
Specifications:
- REPLACE(STRING T, STRING, STRING) -> T
Specifications:
- RIGHT(STRING T, INTEGER) -> T
Specifications:
- RINT(NUMBER T) -> T
Usage: ROUND(x, d) rounds the argument x to d decimal places, d defaults to 0 if not specified
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type map:
(INTEGER/LONG [,INTEGER]) -> LONG (FLOAT/DOUBLE [,INTEGER]) -> LONG
Example:
od> SELECT ROUND(12.34), ROUND(12.34, 1), ROUND(12.34, -1), ROUND(12, 1) fetched rows / total rows = 1/1 +----------------+-------------------+--------------------+----------------+ | ROUND(12.34) | ROUND(12.34, 1) | ROUND(12.34, -1) | ROUND(12, 1) | |----------------+-------------------+--------------------+----------------| | 12.0 | 12.3 | 10.0 | 12 | +----------------+-------------------+--------------------+----------------+
Specifications:
- RTRIM(STRING T) -> T
Usage: Returns the sign of the argument as -1, 0, or 1, depending on whether the number is negative, zero, or positive
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: INTEGER
Example:
od> SELECT SIGN(1), SIGN(0), SIGN(-1.1) fetched rows / total rows = 1/1 +-----------+-----------+--------------+ | SIGN(1) | SIGN(0) | SIGN(-1.1) | |-----------+-----------+--------------| | 1 | 0 | -1 | +-----------+-----------+--------------+
Specifications:
- SIGNUM(NUMBER T) -> T
Usage: sin(x) calculate the sine of x, where x is given in radians.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT SIN(0) fetched rows / total rows = 1/1 +----------+ | SIN(0) | |----------| | 0.0 | +----------+
Specifications:
- SINH(NUMBER T) -> DOUBLE
Usage: Calculates the square root of a non-negative number
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type map:
(Non-negative) INTEGER/LONG/FLOAT/DOUBLE -> DOUBLE (Negative) INTEGER/LONG/FLOAT/DOUBLE -> NULL
Example:
od> SELECT SQRT(4), SQRT(4.41) fetched rows / total rows = 1/1 +-----------+--------------+ | SQRT(4) | SQRT(4.41) | |-----------+--------------| | 2.0 | 2.1 | +-----------+--------------+
Specifications:
- SUBSTRING(STRING T, INTEGER, INTEGER) -> T
Specifications:
- SUBTRACT(NUMBER T, NUMBER) -> T
Usage: tan(x) calculate the tangent of x, where x is given in radians.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
od> SELECT TAN(0) fetched rows / total rows = 1/1 +----------+ | TAN(0) | |----------| | 0.0 | +----------+
Specifications:
- TIMESTAMP(DATE) -> DATE
Specifications:
- TRIM(STRING T) -> T
Usage: TRUNCATE(x, d) returns the number x, truncated to d decimal place
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type map:
INTEGER/LONG -> LONG FLOAT/DOUBLE -> DOUBLE
Example:
fetched rows / total rows = 1/1 +----------------------+-----------------------+-------------------+ | TRUNCATE(56.78, 1) | TRUNCATE(56.78, -1) | TRUNCATE(56, 1) | |----------------------+-----------------------+-------------------| | 56.7 | 50 | 56 | +----------------------+-----------------------+-------------------+
Specifications:
- UPPER(STRING T) -> T
- UPPER(STRING T, STRING) -> T
Specifications:
- YEAR(DATE) -> INTEGER