Table of contents
There is support for a wide variety of functions shared by SQL/PPL. 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 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.
Usage: cast(expr as dateType) cast the expr to dataType. return the value of dataType. The following conversion rules are used:
| Src/Target | STRING | NUMBER | BOOLEAN | TIMESTAMP | DATE | TIME |
| STRING | Note1 | Note1 | TIMESTAMP() | DATE() | TIME() | |
| NUMBER | Note1 | v!=0 | N/A | N/A | N/A | |
| BOOLEAN | Note1 | v?1:0 | N/A | N/A | N/A | |
| TIMESTAMP | Note1 | N/A | N/A | DATE() | TIME() | |
| DATE | Note1 | N/A | N/A | N/A | N/A | |
| TIME | Note1 | N/A | N/A | N/A | N/A |
Note1: the conversion follow the JDK specification.
Cast to string example:
os> SELECT cast(true as string) as cbool, cast(1 as string) as cint, cast(DATE '2012-08-07' as string) as cdate fetched rows / total rows = 1/1 +---------+--------+------------+ | cbool | cint | cdate | |---------+--------+------------| | true | 1 | 2012-08-07 | +---------+--------+------------+
Cast to number example:
os> SELECT cast(true as int) as cbool, cast('1' as integer) as cstring
fetched rows / total rows = 1/1
+---------+-----------+
| cbool | cstring |
|---------+-----------|
| 1 | 1 |
+---------+-----------+
Cast to date example:
os> SELECT cast('2012-08-07' as date) as cdate, cast('01:01:01' as time) as ctime, cast('2012-08-07 01:01:01' as timestamp) as ctimestamp
fetched rows / total rows = 1/1
+------------+----------+---------------------+
| cdate | ctime | ctimestamp |
|------------+----------+---------------------|
| 2012-08-07 | 01:01:01 | 2012-08-07 01:01:01 |
+------------+----------+---------------------+
Cast function can be chained:
os> SELECT cast(cast(true as string) as boolean) as cbool fetched rows / total rows = 1/1 +---------+ | cbool | |---------| | True | +---------+
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:
os> SELECT ACOS(0) fetched rows / total rows = 1/1 +--------------------+ | ACOS(0) | |--------------------| | 1.5707963267948966 | +--------------------+
Specifications:
- ADD(NUMBER T, NUMBER) -> T
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:
os> 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:
os> 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:
os> SELECT ATAN2(2, 3) fetched rows / total rows = 1/1 +--------------------+ | ATAN2(2, 3) | |--------------------| | 0.5880026035475675 | +--------------------+
Specifications:
- CBRT(NUMBER T) -> T
Specifications:
- CEIL(NUMBER T) -> T
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:
os> 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:
os> 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:
os> 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:
os> SELECT CRC32('MySQL')
fetched rows / total rows = 1/1
+------------------+
| CRC32('MySQL') |
|------------------|
| 3259397556 |
+------------------+
Usage: degrees(x) converts x from radians to degrees.
Argument type: INTEGER/LONG/FLOAT/DOUBLE
Return type: DOUBLE
Example:
os> 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:
os> 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:
- LN(NUMBER T) -> DOUBLE
Specifications:
- LOG(NUMBER T) -> DOUBLE
- LOG(NUMBER T, NUMBER) -> DOUBLE
Specifications:
- LOG2(NUMBER T) -> DOUBLE
Specifications:
- LOG10(NUMBER T) -> DOUBLE
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:
os> 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:
- MULTIPLY(NUMBER T, NUMBER) -> NUMBER
Usage: PI() returns the constant pi
Return type: DOUBLE
Example:
os> 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
Synonyms: POWER
Example:
os> 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
Synonyms: POW
Example:
os> 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:
os> 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:
os> SELECT RAND(3) fetched rows / total rows = 1/1 +------------+ | RAND(3) | |------------| | 0.73105735 | +------------+
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:
os> 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 | +----------------+-------------------+--------------------+----------------+
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:
os> 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:
os> 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:
os> SELECT SQRT(4), SQRT(4.41) fetched rows / total rows = 1/1 +-----------+--------------+ | SQRT(4) | SQRT(4.41) | |-----------+--------------| | 2.0 | 2.1 | +-----------+--------------+
Usage: strcmp(str1, str2) returns 0 if strings are same, -1 if first arg < second arg according to current sort order, and 1 otherwise.
Argument type: STRING, STRING
Return type: INTEGER
Example:
os> SELECT STRCMP('hello', 'world'), STRCMP('hello', 'hello')
fetched rows / total rows = 1/1
+----------------------------+----------------------------+
| STRCMP('hello', 'world') | STRCMP('hello', 'hello') |
|----------------------------+----------------------------|
| -1 | 0 |
+----------------------------+----------------------------+
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:
os> SELECT TAN(0) fetched rows / total rows = 1/1 +----------+ | TAN(0) | |----------| | 0.0 | +----------+
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 | +----------------------+-----------------------+-------------------+
Usage: adddate(date, INTERVAL expr unit)/ adddate(date, expr) adds the time interval of second argument to date; adddate(date, days) adds the second argument as integer number of days to date.
Argument type: DATE/DATETIME/TIMESTAMP/STRING, INTERVAL/LONG
Return type map:
(DATE/DATETIME/TIMESTAMP/STRING, INTERVAL) -> DATETIME
(DATE, LONG) -> DATE
(DATETIME/TIMESTAMP/STRING, LONG) -> DATETIME
Synonyms: DATE_ADD
Example:
os> SELECT ADDDATE(DATE('2020-08-26'), INTERVAL 1 HOUR), ADDDATE(DATE('2020-08-26'), 1), ADDDATE(TIMESTAMP('2020-08-26 01:01:01'), 1)
fetched rows / total rows = 1/1
+------------------------------------------------+----------------------------------+------------------------------------------------+
| ADDDATE(DATE('2020-08-26'), INTERVAL 1 HOUR) | ADDDATE(DATE('2020-08-26'), 1) | ADDDATE(TIMESTAMP('2020-08-26 01:01:01'), 1) |
|------------------------------------------------+----------------------------------+------------------------------------------------|
| 2020-08-26 01:00:00 | 2020-08-27 | 2020-08-27 01:01:01 |
+------------------------------------------------+----------------------------------+------------------------------------------------+
Specifications:
- CURDATE() -> DATE
Usage: date(expr) constructs a date type with the input string expr as a date. If the argument is of date/datetime/timestamp, it extracts the date value part from the expression.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: DATE
Example:
>od SELECT DATE('2020-08-26'), DATE(TIMESTAMP('2020-08-26 13:49:00'))
fetched rows / total rows = 1/1
+----------------------+------------------------------------------+
| DATE('2020-08-26') | DATE(TIMESTAMP('2020-08-26 13:49:00')) |
|----------------------+------------------------------------------|
| DATE '2020-08-26' | DATE '2020-08-26' |
+----------------------+------------------------------------------+
Usage: date_add(date, INTERVAL expr unit)/ date_add(date, expr) adds the time interval expr to date
Argument type: DATE/DATETIME/TIMESTAMP/STRING, INTERVAL/LONG
Return type map:
DATE/DATETIME/TIMESTAMP/STRING, INTERVAL -> DATETIME
DATE, LONG -> DATE
DATETIME/TIMESTAMP/STRING, LONG -> DATETIME
Synonyms: ADDDATE
Example:
os> SELECT DATE_ADD(DATE('2020-08-26'), INTERVAL 1 HOUR), DATE_ADD(DATE('2020-08-26'), 1), DATE_ADD(TIMESTAMP('2020-08-26 01:01:01'), 1)
fetched rows / total rows = 1/1
+-------------------------------------------------+-----------------------------------+-------------------------------------------------+
| DATE_ADD(DATE('2020-08-26'), INTERVAL 1 HOUR) | DATE_ADD(DATE('2020-08-26'), 1) | DATE_ADD(TIMESTAMP('2020-08-26 01:01:01'), 1) |
|-------------------------------------------------+-----------------------------------+-------------------------------------------------|
| 2020-08-26 01:00:00 | 2020-08-27 | 2020-08-27 01:01:01 |
+-------------------------------------------------+-----------------------------------+-------------------------------------------------+
Usage: date_format(date, format) formats the date argument using the specifiers in the format argument.
| Specifier | Description |
|---|---|
| %a | Abbreviated weekday name (Sun..Sat) |
| %b | Abbreviated month name (Jan..Dec) |
| %c | Month, numeric (0..12) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %f | Microseconds (000000..999999) |
| %H | Hour (00..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %i | Minutes, numeric (00..59) |
| %j | Day of year (001..366) |
| %k | Hour (0..23) |
| %l | Hour (1..12) |
| %M | Month name (January..December) |
| %m | Month, numeric (00..12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %U | Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 |
| %u | Week (00..53), where Monday is the first day of the week; WEEK() mode 1 |
| %V | Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X |
| %v | Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x |
| %W | Weekday name (Sunday..Saturday) |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
| %x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
| %Y | Year, numeric, four digits |
| %y | Year, numeric (two digits) |
| %% | A literal % character |
| %x | x, for any “x” not listed above |
| x | x, for any smallcase/uppercase alphabet except [aydmshiHIMYDSEL] |
Argument type: STRING/DATE/DATETIME/TIMESTAMP, STRING
Return type: STRING
Example:
>od SELECT DATE_FORMAT('1998-01-31 13:14:15.012345', '%T.%f'), DATE_FORMAT(TIMESTAMP('1998-01-31 13:14:15.012345'), '%Y-%b-%D %r')
fetched rows / total rows = 1/1
+-----------------------------------------------+----------------------------------------------------------------+
| DATE('1998-01-31 13:14:15.012345', '%T.%f') | DATE(TIMESTAMP('1998-01-31 13:14:15.012345'), '%Y-%b-%D %r') |
|-----------------------------------------------+----------------------------------------------------------------|
| '13:14:15.012345' | '1998-Jan-31st 01:14:15 PM' |
+-----------------------------------------------+----------------------------------------------------------------+
Usage: date_sub(date, INTERVAL expr unit)/ date_sub(date, expr) subtracts the time interval expr from date
Argument type: DATE/DATETIME/TIMESTAMP/STRING, INTERVAL/LONG
Return type map:
DATE/DATETIME/TIMESTAMP/STRING, INTERVAL -> DATETIME
DATE, LONG -> DATE
DATETIME/TIMESTAMP/STRING, LONG -> DATETIME
Synonyms: SUBDATE
Example:
os> SELECT DATE_SUB(DATE('2008-01-02'), INTERVAL 31 DAY), DATE_SUB(DATE('2020-08-26'), 1), DATE_SUB(TIMESTAMP('2020-08-26 01:01:01'), 1)
fetched rows / total rows = 1/1
+-------------------------------------------------+-----------------------------------+-------------------------------------------------+
| DATE_SUB(DATE('2008-01-02'), INTERVAL 31 DAY) | DATE_SUB(DATE('2020-08-26'), 1) | DATE_SUB(TIMESTAMP('2020-08-26 01:01:01'), 1) |
|-------------------------------------------------+-----------------------------------+-------------------------------------------------|
| 2007-12-02 | 2020-08-25 | 2020-08-25 01:01:01 |
+-------------------------------------------------+-----------------------------------+-------------------------------------------------+
Usage: day(date) extracts the day of the month for date, in the range 1 to 31. The dates with value 0 such as '0000-00-00' or '2008-00-00' are invalid.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: INTEGER
Synonyms: DAYOFMONTH
Example:
os> SELECT DAY(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+---------------------------+
| DAY(DATE('2020-08-26')) |
|---------------------------|
| 26 |
+---------------------------+
Usage: dayname(date) returns the name of the weekday for date, including Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: STRING
Example:
os> SELECT DAYNAME(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+-------------------------------+
| DAYNAME(DATE('2020-08-26')) |
|-------------------------------|
| Wednesday |
+-------------------------------+
Usage: dayofmonth(date) extracts the day of the month for date, in the range 1 to 31. The dates with value 0 such as '0000-00-00' or '2008-00-00' are invalid.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: INTEGER
Synonyms: DAY
Example:
os> SELECT DAYOFMONTH(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+----------------------------------+
| DAYOFMONTH(DATE('2020-08-26')) |
|----------------------------------|
| 26 |
+----------------------------------+
Usage: dayofweek(date) returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday).
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT DAYOFWEEK(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+---------------------------------+
| DAYOFWEEK(DATE('2020-08-26')) |
|---------------------------------|
| 4 |
+---------------------------------+
Usage: dayofyear(date) returns the day of the year for date, in the range 1 to 366.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT DAYOFYEAR(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+---------------------------------+
| DAYOFYEAR(DATE('2020-08-26')) |
|---------------------------------|
| 239 |
+---------------------------------+
Usage: from_days(N) returns the date value given the day number N.
Argument type: INTEGER/LONG
Return type: DATE
Example:
os> SELECT FROM_DAYS(733687) fetched rows / total rows = 1/1 +---------------------+ | FROM_DAYS(733687) | |---------------------| | 2008-10-07 | +---------------------+
Usage: hour(time) extracts the hour value for time. Different from the time of day value, the time value has a large range and can be greater than 23, so the return value of hour(time) can be also greater than 23.
Argument type: STRING/TIME/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT HOUR((TIME '01:02:03')) fetched rows / total rows = 1/1 +---------------------------+ | HOUR((TIME '01:02:03')) | |---------------------------| | 1 | +---------------------------+
Specifications:
- MAKETIME(INTEGER, INTEGER, INTEGER) -> DATE
Usage: microsecond(expr) returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.
Argument type: STRING/TIME/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT MICROSECOND((TIME '01:02:03.123456')) fetched rows / total rows = 1/1 +-----------------------------------------+ | MICROSECOND((TIME '01:02:03.123456')) | |-----------------------------------------| | 123456 | +-----------------------------------------+
Usage: minute(time) returns the minute for time, in the range 0 to 59.
Argument type: STRING/TIME/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT MINUTE((TIME '01:02:03')) fetched rows / total rows = 1/1 +-----------------------------+ | MINUTE((TIME '01:02:03')) | |-----------------------------| | 2 | +-----------------------------+
Usage: month(date) returns the month for date, in the range 1 to 12 for January to December. The dates with value 0 such as '0000-00-00' or '2008-00-00' are invalid.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT MONTH(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+-----------------------------+
| MONTH(DATE('2020-08-26')) |
|-----------------------------|
| 8 |
+-----------------------------+
Usage: monthname(date) returns the full name of the month for date.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: STRING
Example:
os> SELECT MONTHNAME(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+---------------------------------+
| MONTHNAME(DATE('2020-08-26')) |
|---------------------------------|
| August |
+---------------------------------+
Specifications:
- NOW() -> DATE
Usage: quarter(date) returns the quarter of the year for date, in the range 1 to 4.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT QUARTER(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+-------------------------------+
| QUARTER(DATE('2020-08-26')) |
|-------------------------------|
| 3 |
+-------------------------------+
Usage: second(time) returns the second for time, in the range 0 to 59.
Argument type: STRING/TIME/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT SECOND((TIME '01:02:03')) fetched rows / total rows = 1/1 +-----------------------------+ | SECOND((TIME '01:02:03')) | |-----------------------------| | 3 | +-----------------------------+
Usage: subdate(date, INTERVAL expr unit)/ subdate(date, expr) subtracts the time interval expr from date
Argument type: DATE/DATETIME/TIMESTAMP/STRING, INTERVAL/LONG
Return type map:
DATE/DATETIME/TIMESTAMP/STRING, INTERVAL -> DATETIME
DATE, LONG -> DATE
DATETIME/TIMESTAMP/STRING, LONG -> DATETIME
Synonyms: DATE_SUB
Example:
os> SELECT SUBDATE(DATE('2008-01-02'), INTERVAL 31 DAY), SUBDATE(DATE('2020-08-26'), 1), SUBDATE(TIMESTAMP('2020-08-26 01:01:01'), 1)
fetched rows / total rows = 1/1
+------------------------------------------------+----------------------------------+------------------------------------------------+
| SUBDATE(DATE('2008-01-02'), INTERVAL 31 DAY) | SUBDATE(DATE('2020-08-26'), 1) | SUBDATE(TIMESTAMP('2020-08-26 01:01:01'), 1) |
|------------------------------------------------+----------------------------------+------------------------------------------------|
| 2007-12-02 | 2020-08-25 | 2020-08-25 01:01:01 |
+------------------------------------------------+----------------------------------+------------------------------------------------+
Usage: time(expr) constructs a time type with the input string expr as a time. If the argument is of date/datetime/time/timestamp, it extracts the time value part from the expression.
Argument type: STRING/DATE/DATETIME/TIME/TIMESTAMP
Return type: TIME
Example:
>od SELECT TIME('13:49:00'), TIME(TIMESTAMP('2020-08-26 13:49:00'))
fetched rows / total rows = 1/1
+--------------------+------------------------------------------+
| TIME('13:49:00') | TIME(TIMESTAMP('2020-08-26 13:49:00')) |
|--------------------+------------------------------------------|
| TIME '13:49:00' | TIME '13:49:00' |
+--------------------+------------------------------------------+
Usage: time_to_sec(time) returns the time argument, converted to seconds.
Argument type: STRING/TIME/DATETIME/TIMESTAMP
Return type: LONG
Example:
os> SELECT TIME_TO_SEC(TIME '22:23:00') fetched rows / total rows = 1/1 +--------------------------------+ | TIME_TO_SEC(TIME '22:23:00') | |--------------------------------| | 80580 | +--------------------------------+
Usage: timestamp(expr) construct a timestamp type with the input string expr as an timestamp. If the argument is of date/datetime/timestamp type, cast expr to timestamp type with default timezone UTC.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: TIMESTAMP
Example:
>od SELECT TIMESTAMP('2020-08-26 13:49:00')
fetched rows / total rows = 1/1
+------------------------------------+
| TIMESTAMP('2020-08-26 13:49:00') |
|------------------------------------|
| TIMESTAMP '2020-08-26 13:49:00 |
+------------------------------------+
Usage: to_days(date) returns the day number (the number of days since year 0) of the given date. Returns NULL if date is invalid.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: LONG
Example:
os> SELECT TO_DAYS(DATE '2008-10-07') fetched rows / total rows = 1/1 +------------------------------+ | TO_DAYS(DATE '2008-10-07') | |------------------------------| | 733687 | +------------------------------+
Usage: week(date[, mode]) returns the week number for date. If the mode argument is omitted, the default mode 0 is used.
| Mode | First day of week | Range | Week 1 is the first week … |
|---|---|---|---|
| 0 | Sunday | 0-53 | with a Sunday in this year |
| 1 | Monday | 0-53 | with 4 or more days this year |
| 2 | Sunday | 1-53 | with a Sunday in this year |
| 3 | Monday | 1-53 | with 4 or more days this year |
| 4 | Sunday | 0-53 | with 4 or more days this year |
| 5 | Monday | 0-53 | with a Monday in this year |
| 6 | Sunday | 1-53 | with 4 or more days this year |
| 7 | Monday | 1-53 | with a Monday in this year |
Argument type: DATE/DATETIME/TIMESTAMP/STRING
Return type: INTEGER
Example:
>od SELECT WEEK(DATE('2008-02-20')), WEEK(DATE('2008-02-20'), 1)
fetched rows / total rows = 1/1
+----------------------------+-------------------------------+
| WEEK(DATE('2008-02-20')) | WEEK(DATE('2008-02-20'), 1) |
|----------------------------|-------------------------------|
| 7 | 8 |
+----------------------------+-------------------------------+
Usage: year(date) returns the year for date, in the range 1000 to 9999, or 0 for the “zero” date.
Argument type: STRING/DATE/DATETIME/TIMESTAMP
Return type: INTEGER
Example:
os> SELECT YEAR(DATE('2020-08-26'))
fetched rows / total rows = 1/1
+----------------------------+
| YEAR(DATE('2020-08-26')) |
|----------------------------|
| 2020 |
+----------------------------+
Usage: ASCII(expr) returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for 8-bit characters.
Argument type: STRING
Return type: INTEGER
Example:
os> SELECT ASCII('hello')
fetched rows / total rows = 1/1
+------------------+
| ASCII('hello') |
|------------------|
| 104 |
+------------------+
Usage: CONCAT(str1, str2) returns str1 and str strings concatenated together.
Argument type: STRING, STRING
Return type: STRING
Example:
os> SELECT CONCAT('hello', 'world')
fetched rows / total rows = 1/1
+----------------------------+
| CONCAT('hello', 'world') |
|----------------------------|
| helloworld |
+----------------------------+
Usage: CONCAT_WS(sep, str1, str2) returns str1 concatenated with str2 using sep as a separator between them.
Argument type: STRING, STRING, STRING
Return type: STRING
Example:
os> SELECT CONCAT_WS(',', 'hello', 'world')
fetched rows / total rows = 1/1
+------------------------------------+
| CONCAT_WS(',', 'hello', 'world') |
|------------------------------------|
| hello,world |
+------------------------------------+
Usage: left(str, len) returns the leftmost len characters from the string str, or NULL if any argument is NULL.
Argument type: STRING, INTEGER
Return type: STRING
Example:
os> SELECT LEFT('helloworld', 5), LEFT('HELLOWORLD', 0)
fetched rows / total rows = 1/1
+-------------------------+-------------------------+
| LEFT('helloworld', 5) | LEFT('HELLOWORLD', 0) |
|-------------------------+-------------------------|
| hello | |
+-------------------------+-------------------------+
Specifications:
- LENGTH(STRING) -> INTEGER
Usage: length(str) returns length of string measured in bytes.
Argument type: STRING
Return type: INTEGER
Example:
os> SELECT LENGTH('helloworld')
fetched rows / total rows = 1/1
+------------------------+
| LENGTH('helloworld') |
|------------------------|
| 10 |
+------------------------+
Usage: The first syntax LOCATE(substr, str) returns the position of the first occurrence of substring substr in string str. The second syntax LOCATE(substr, str, pos) returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if any argument is NULL.
Argument type: STRING, STRING, INTEGER
Return type map:
(STRING, STRING) -> INTEGER (STRING, STRING, INTEGER) -> INTEGER
Example:
os> SELECT LOCATE('world', 'helloworld'), LOCATE('world', 'helloworldworld', 7)
fetched rows / total rows = 1/1
+---------------------------------+-----------------------------------------+
| LOCATE('world', 'helloworld') | LOCATE('world', 'helloworldworld', 7) |
|---------------------------------+-----------------------------------------|
| 6 | 11 |
+---------------------------------+-----------------------------------------+
Usage: lower(string) converts the string to lowercase.
Argument type: STRING
Return type: STRING
Example:
os> SELECT LOWER('helloworld'), LOWER('HELLOWORLD')
fetched rows / total rows = 1/1
+-----------------------+-----------------------+
| LOWER('helloworld') | LOWER('HELLOWORLD') |
|-----------------------+-----------------------|
| helloworld | helloworld |
+-----------------------+-----------------------+
Usage: ltrim(str) trims leading space characters from the string.
Argument type: STRING
Return type: STRING
Example:
os> SELECT LTRIM(' hello'), LTRIM('hello ')
fetched rows / total rows = 1/1
+---------------------+---------------------+
| LTRIM(' hello') | LTRIM('hello ') |
|---------------------+---------------------|
| hello | hello |
+---------------------+---------------------+
Usage: REPLACE(str, from_str, to_str) returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.
Argument type: STRING, STRING, STRING
Return type: STRING
Example:
os> SELECT REPLACE('Hello World!', 'World', 'OpenSearch')
fetched rows / total rows = 1/1
+--------------------------------------------------+
| REPLACE('Hello World!', 'World', 'OpenSearch') |
|--------------------------------------------------|
| Hello OpenSearch! |
+--------------------------------------------------+
Usage: right(str, len) returns the rightmost len characters from the string str, or NULL if any argument is NULL.
Argument type: STRING, INTEGER
Return type: STRING
Example:
os> SELECT RIGHT('helloworld', 5), RIGHT('HELLOWORLD', 0)
fetched rows / total rows = 1/1
+--------------------------+--------------------------+
| RIGHT('helloworld', 5) | RIGHT('HELLOWORLD', 0) |
|--------------------------+--------------------------|
| world | |
+--------------------------+--------------------------+
Usage: rtrim(str) trims trailing space characters from the string.
Argument type: STRING
Return type: STRING
Example:
os> SELECT RTRIM(' hello'), RTRIM('hello ')
fetched rows / total rows = 1/1
+---------------------+---------------------+
| RTRIM(' hello') | RTRIM('hello ') |
|---------------------+---------------------|
| hello | hello |
+---------------------+---------------------+
Usage: substring(str, start) or substring(str, start, length) returns substring using start and length. With no length, entire string from start is returned.
Argument type: STRING, INTEGER, INTEGER
Return type: STRING
Synonyms: SUBSTR
Example:
os> SELECT SUBSTRING('helloworld', 5), SUBSTRING('helloworld', 5, 3)
fetched rows / total rows = 1/1
+------------------------------+---------------------------------+
| SUBSTRING('helloworld', 5) | SUBSTRING('helloworld', 5, 3) |
|------------------------------+---------------------------------|
| oworld | owo |
+------------------------------+---------------------------------+
Argument Type: STRING
Return type: STRING
Example:
os> SELECT TRIM(' hello'), TRIM('hello ')
fetched rows / total rows = 1/1
+--------------------+--------------------+
| TRIM(' hello') | TRIM('hello ') |
|--------------------+--------------------|
| hello | hello |
+--------------------+--------------------+
Usage: upper(string) converts the string to uppercase.
Argument type: STRING
Return type: STRING
Example:
os> SELECT UPPER('helloworld'), UPPER('HELLOWORLD')
fetched rows / total rows = 1/1
+-----------------------+-----------------------+
| UPPER('helloworld') | UPPER('HELLOWORLD') |
|-----------------------+-----------------------|
| HELLOWORLD | HELLOWORLD |
+-----------------------+-----------------------+
Specifications:
- IF(BOOLEAN, ES_TYPE, ES_TYPE) -> ES_TYPE
Specifications:
- IFNULL(ES_TYPE, ES_TYPE) -> ES_TYPE
Usage: return parameter2 if parameter1 is null, otherwise return parameter1
Argument type: Any
Return type: Any (NOTE : if two parameters has different type, you will fail semantic check"
Example One:
os> SELECT IFNULL(123, 321), IFNULL(321, 123) fetched rows / total rows = 1/1 +--------------------+--------------------+ | IFNULL(123, 321) | IFNULL(321, 123) | |--------------------+--------------------| | 123 | 321 | +--------------------+--------------------+
Example Two:
os> SELECT IFNULL(321, 1/0), IFNULL(1/0, 123) fetched rows / total rows = 1/1 +--------------------+--------------------+ | IFNULL(321, 1/0) | IFNULL(1/0, 123) | |--------------------+--------------------| | 321 | 123 | +--------------------+--------------------+
Example Three:
os> SELECT IFNULL(1/0, 1/0) fetched rows / total rows = 1/1 +--------------------+ | IFNULL(1/0, 1/0) | |--------------------| | null | +--------------------+
Specifications:
- NULLIF(ES_TYPE, ES_TYPE) -> ES_TYPE
Usage: return null if two parameters are same, otherwise return parameer1
Argument type: Any
Return type: Any (NOTE : if two parametershas different type, you will fail semantic check")
Example:
os> SELECT NULLIF(123, 123), NULLIF(321, 123), NULLIF(1/0, 321), NULLIF(321, 1/0), NULLIF(1/0, 1/0) fetched rows / total rows = 1/1 +--------------------+--------------------+--------------------+--------------------+--------------------+ | NULLIF(123, 123) | NULLIF(321, 123) | NULLIF(1/0, 321) | NULLIF(321, 1/0) | NULLIF(1/0, 1/0) | |--------------------+--------------------+--------------------+--------------------+--------------------| | null | 321 | null | 321 | null | +--------------------+--------------------+--------------------+--------------------+--------------------+
Specifications:
- ISNULL(ES_TYPE) -> INTEGER
Usage: return true if parameter is null, otherwise return false
Argument type: Any
Return type: boolean
Example:
os> SELECT ISNULL(1/0), ISNULL(123) fetched rows / total rows = 1/1 +---------------+---------------+ | ISNULL(1/0) | ISNULL(123) | |---------------+---------------| | True | False | +---------------+---------------+
Specifications:
- IF(condition, ES_TYPE1, ES_TYPE2) -> ES_TYPE1 or ES_TYPE2
Usage: if first parameter is true, return second parameter, otherwise return third one.
Argument type: condition as BOOLEAN, second and third can by any type
Return type: Any (NOTE : if parameters #2 and #3 has different type, you will fail semantic check"
Example:
os> SELECT IF(100 > 200, '100', '200') fetched rows / total rows = 1/1 +-------------------------------+ | IF(100 > 200, '100', '200') | |-------------------------------| | 200 | +-------------------------------+ os> SELECT IF(200 > 100, '100', '200') fetched rows / total rows = 1/1 +-------------------------------+ | IF(200 > 100, '100', '200') | |-------------------------------| | 100 | +-------------------------------+
CASE statement has two forms with slightly different syntax: Simple Case and Searched Case.
Simple case syntax compares a case value expression with each compare expression in WHEN clause and return its result if matched. Otherwise, result expression's value in ELSE clause is returned (or NULL if absent):
CASE case_value_expression WHEN compare_expression THEN result_expression [WHEN compare_expression THEN result_expression] ... [ELSE result_expression] END
Similarly, searched case syntax evaluates each search condition and return result if true. A search condition must be a predicate that returns a bool when evaluated:
CASE WHEN search_condition THEN result_expression [WHEN search_condition THEN result_expression] ... [ELSE result_expression] END
All result types in WHEN and ELSE clause are required to be exactly the same. Otherwise, take the following query for example, you'll see an semantic analysis exception thrown:
CASE age WHEN 30 THEN 'Thirty' WHEN 50 THEN true END
Here are examples for simple case syntax:
os> SELECT
... CASE 1
... WHEN 1 THEN 'One'
... END AS simple_case,
... CASE ABS(-2)
... WHEN 1 THEN 'One'
... WHEN 2 THEN 'Absolute two'
... END AS func_case_value,
... CASE ABS(-3)
... WHEN 1 THEN 'One'
... ELSE TRIM(' Absolute three ')
... END AS func_result;
fetched rows / total rows = 1/1
+---------------+-------------------+----------------+
| simple_case | func_case_value | func_result |
|---------------+-------------------+----------------|
| One | Absolute two | Absolute three |
+---------------+-------------------+----------------+
Here are examples for searched case syntax:
os> SELECT ... CASE ... WHEN 1 = 1 THEN 'One' ... END AS single_search, ... CASE ... WHEN 2 = 1 THEN 'One' ... WHEN 'hello' = 'hello' THEN 'Hello' END AS multi_searches, ... CASE ... WHEN 2 = 1 THEN 'One' ... WHEN 'hello' = 'world' THEN 'Hello' ... END AS no_else; fetched rows / total rows = 1/1 +-----------------+------------------+-----------+ | single_search | multi_searches | no_else | |-----------------+------------------+-----------| | One | Hello | null | +-----------------+------------------+-----------+
The relevance based functions enable users to search the index for documents by the relevance of the input query. The functions are built on the top of the search queries of the OpenSearch engine, but in memory execution within the plugin is not supported. These functions are able to perform the global filter of a query, for example the condition expression in a WHERE clause or in a HAVING clause. For more details of the relevance based search, check out the design here: Relevance Based Search With SQL/PPL Query Engine
match(field_expression, query_expression[, option=<option_value>]*)
The match function maps to the match query used in search engine, to return the documents that match a provided text, number, date or boolean value with a given field. Available parameters include:
- analyzer
- auto_generate_synonyms_phrase
- fuzziness
- max_expansions
- prefix_length
- fuzzy_transpositions
- fuzzy_rewrite
- lenient
- operator
- minimum_should_match
- zero_terms_query
- boost
Example with only field and query expressions, and all other parameters are set default values:
os> SELECT lastname, address FROM accounts WHERE match(address, 'Street'); fetched rows / total rows = 2/2 +------------+--------------------+ | lastname | address | |------------+--------------------| | Bond | 671 Bristol Street | | Bates | 789 Madison Street | +------------+--------------------+
Another example to show how to set custom values for the optional parameters:
os> SELECT lastname FROM accounts WHERE match(firstname, 'Hattie', operator='AND', boost=2.0); fetched rows / total rows = 1/1 +------------+ | lastname | |------------| | Bond | +------------+
match_phrase(field_expression, query_expression[, option=<option_value>]*)
The match_phrase function maps to the match_phrase query used in search engine, to return the documents that match a provided text with a given field. Available parameters include:
- analyzer
- slop
- zero_terms_query
For backward compatibility, matchphrase is also supported and mapped to match_phrase query as well.
Example with only field and query expressions, and all other parameters are set default values:
os> SELECT author, title FROM books WHERE match_phrase(author, 'Alexander Milne'); fetched rows / total rows = 2/2 +----------------------+--------------------------+ | author | title | |----------------------+--------------------------| | Alan Alexander Milne | The House at Pooh Corner | | Alan Alexander Milne | Winnie-the-Pooh | +----------------------+--------------------------+
Another example to show how to set custom values for the optional parameters:
os> SELECT author, title FROM books WHERE match_phrase(author, 'Alan Milne', slop = 2); fetched rows / total rows = 2/2 +----------------------+--------------------------+ | author | title | |----------------------+--------------------------| | Alan Alexander Milne | The House at Pooh Corner | | Alan Alexander Milne | Winnie-the-Pooh | +----------------------+--------------------------+
match_bool_prefix(field_expression, query_expression)
The match_bool_prefix function maps to the match_bool_prefix query in the search engine. match_bool_prefix creates a match query from all but the last term in the query string. The last term is used to create a prefix query.
- fuzziness
- max_expansions
- prefix_length
- fuzzy_transpositions
- fuzzy_rewrite
- minimum_should_match
- boost
- operator
- analyzer
Example with only field and query expressions, and all other parameters are set default values:
os> SELECT firstname, address FROM accounts WHERE match_bool_prefix(address, 'Bristol Stre'); fetched rows / total rows = 2/2 +-------------+--------------------+ | firstname | address | |-------------+--------------------| | Hattie | 671 Bristol Street | | Nanette | 789 Madison Street | +-------------+--------------------+
Another example to show how to set custom values for the optional parameters:
os> SELECT firstname, address FROM accounts WHERE match_bool_prefix(address, 'Bristol Street', minimum_should_match=2); fetched rows / total rows = 1/1 +-------------+--------------------+ | firstname | address | |-------------+--------------------| | Hattie | 671 Bristol Street | +-------------+--------------------+
match_phrase_prefix(field_expression, query_expression[, option=<option_value>]*)
The match_phrase_prefix function maps to the match_phrase_prefix query used in search engine, to return the documents that match a provided text with a given field. Available parameters include:
- analyzer
- slop
- zero_terms_query
- max_expansions
- boost
Example with only field and query expressions, and all other parameters are set default values:
os> SELECT author, title FROM books WHERE match_phrase_prefix(author, 'Alexander Mil'); fetched rows / total rows = 2/2 +----------------------+--------------------------+ | author | title | |----------------------+--------------------------| | Alan Alexander Milne | The House at Pooh Corner | | Alan Alexander Milne | Winnie-the-Pooh | +----------------------+--------------------------+
Another example to show how to set custom values for the optional parameters:
os> SELECT author, title FROM books WHERE match_phrase_prefix(author, 'Alan Mil', slop = 2); fetched rows / total rows = 2/2 +----------------------+--------------------------+ | author | title | |----------------------+--------------------------| | Alan Alexander Milne | The House at Pooh Corner | | Alan Alexander Milne | Winnie-the-Pooh | +----------------------+--------------------------+
multi_match([field_expression+], query_expression[, option=<option_value>]*)
The multi_match function maps to the multi_match query used in search engine, to return the documents that match a provided text, number, date or boolean value with a given field or fields.
The ^ lets you boost certain fields. Boosts are multipliers that weigh matches in one field more heavily than matches in other fields. The syntax allows to specify the fields in double quotes, single quotes, in backtick or even without any wrap. All fields search using star "*" is also available (star symbol should be wrapped). The weight is optional and should be specified using after the field name, it could be delimeted by the caret character or by whitespace. Please, refer to examples below:
multi_match(["Tags" ^ 2, 'Title' 3.4, `Body`, Comments ^ 0.3], ...)multi_match(["*"], ...)Available parameters include:
- analyzer
- auto_generate_synonyms_phrase
- cutoff_frequency
- fuzziness
- fuzzy_transpositions
- lenient
- max_expansions
- minimum_should_match
- operator
- prefix_length
- tie_breaker
- type
- slop
- boost
Example with only fields and query expressions, and all other parameters are set default values:
os> select * from books where multi_match(['title'], 'Pooh House'); fetched rows / total rows = 2/2 +------+--------------------------+----------------------+ | id | title | author | |------+--------------------------+----------------------| | 1 | The House at Pooh Corner | Alan Alexander Milne | | 2 | Winnie-the-Pooh | Alan Alexander Milne | +------+--------------------------+----------------------+
Another example to show how to set custom values for the optional parameters:
os> select * from books where multi_match(['title'], 'Pooh House', operator='AND', analyzer=default); fetched rows / total rows = 1/1 +------+--------------------------+----------------------+ | id | title | author | |------+--------------------------+----------------------| | 1 | The House at Pooh Corner | Alan Alexander Milne | +------+--------------------------+----------------------+
simple_query_string([field_expression+], query_expression[, option=<option_value>]*)
The simple_query_string function maps to the simple_query_string query used in search engine, to return the documents that match a provided text, number, date or boolean value with a given field or fields.
The ^ lets you boost certain fields. Boosts are multipliers that weigh matches in one field more heavily than matches in other fields. The syntax allows to specify the fields in double quotes, single quotes, in backtick or even without any wrap. All fields search using star "*" is also available (star symbol should be wrapped). The weight is optional and should be specified using after the field name, it could be delimeted by the caret character or by whitespace. Please, refer to examples below:
simple_query_string(["Tags" ^ 2, 'Title' 3.4, `Body`, Comments ^ 0.3], ...)simple_query_string(["*"], ...)Available parameters include:
- analyze_wildcard
- analyzer
- auto_generate_synonyms_phrase
- flags
- fuzziness
- fuzzy_max_expansions
- fuzzy_prefix_length
- fuzzy_transpositions
- lenient
- default_operator
- minimum_should_match
- quote_field_suffix
- boost
Example with only fields and query expressions, and all other parameters are set default values:
os> select * from books where simple_query_string(['title'], 'Pooh House'); fetched rows / total rows = 2/2 +------+--------------------------+----------------------+ | id | title | author | |------+--------------------------+----------------------| | 1 | The House at Pooh Corner | Alan Alexander Milne | | 2 | Winnie-the-Pooh | Alan Alexander Milne | +------+--------------------------+----------------------+
Another example to show how to set custom values for the optional parameters:
os> select * from books where simple_query_string(['title'], 'Pooh House', flags='ALL', default_operator='AND'); fetched rows / total rows = 1/1 +------+--------------------------+----------------------+ | id | title | author | |------+--------------------------+----------------------| | 1 | The House at Pooh Corner | Alan Alexander Milne | +------+--------------------------+----------------------+
query_string([field_expression+], query_expression[, option=<option_value>]*)
The query_string function maps to the query_string query used in search engine, to return the documents that match a provided text, number, date or boolean value with a given field or fields.
The ^ lets you boost certain fields. Boosts are multipliers that weigh matches in one field more heavily than matches in other fields. The syntax allows to specify the fields in double quotes, single quotes, backticks or without any wrap. All fields search using star "*" is also available (star symbol should be wrapped). The weight is optional and should be specified after the field name, it could be delimeted by the caret character or by whitespace. Please refer to examples below:
query_string(["Tags" ^ 2, 'Title' 3.4, `Body`, Comments ^ 0.3], ...)query_string(["*"], ...)Available parameters include:
- analyzer
- escape
- allow_leading_wildcard
- analyze_wildcard
- auto_generate_synonyms_phrase_query
- boost
- default_operator
- enable_position_increments
- fuzziness
- fuzzy_max_expansions
- fuzzy_prefix_length
- fuzzy_transpositions
- fuzzy_rewrite
- tie_breaker
- lenient
- type
- max_determinized_states
- minimum_should_match
- quote_analyzer
- phrase_slop
- quote_field_suffix
- rewrite
- time_zone
Example with only fields and query expressions, and all other parameters are set default values:
os> select * from books where query_string(['title'], 'Pooh House'); fetched rows / total rows = 2/2 +------+--------------------------+----------------------+ | id | title | author | |------+--------------------------+----------------------| | 1 | The House at Pooh Corner | Alan Alexander Milne | | 2 | Winnie-the-Pooh | Alan Alexander Milne | +------+--------------------------+----------------------+
Another example to show how to set custom values for the optional parameters:
os> select * from books where query_string(['title'], 'Pooh House', default_operator='AND'); fetched rows / total rows = 1/1 +------+--------------------------+----------------------+ | id | title | author | |------+--------------------------+----------------------| | 1 | The House at Pooh Corner | Alan Alexander Milne | +------+--------------------------+----------------------+