Package org.apache.calcite.sql.fun

Class SqlLibraryOperators

java.lang.Object
org.apache.calcite.sql.fun.SqlLibraryOperators
public abstract class SqlLibraryOperators extends Object
Defines functions and operators that are not part of standard SQL but belong to one or more other dialects of SQL.

They are read by SqlLibraryOperatorTableFactory into instances of SqlOperatorTable that contain functions and operators for particular libraries.

  • Field Details

    • AGGREGATE

      public static final SqlFunction AGGREGATE
      The "AGGREGATE(m)" aggregate function; aggregates a measure column according to the measure's rollup strategy. This is a Calcite-specific extension.

      This operator is for SQL (and AST); for internal use (RexNode and Aggregate) use AGG_M2M.

    • CONVERT_TIMEZONE

      public static final SqlFunction CONVERT_TIMEZONE
      The "CONVERT_TIMEZONE(tz1, tz2, datetime)" function; converts the timezone of datetime from tz1 to tz2.

    • DATE_ADD

      public static final SqlFunction DATE_ADD
      THE "DATE_ADD(date, interval)" function (BigQuery) adds the interval to the date.

    • DATE_DIFF

      public static final SqlFunction DATE_DIFF
      THE "DATE_DIFF(date, date2, timeUnit)" function (BigQuery) returns the number of timeUnit in (date - date2).

    • DATEADD

      public static final SqlFunction DATEADD
      The "DATEADD(timeUnit, numeric, datetime)" function (Microsoft SQL Server, Redshift, Snowflake).

    • DATE_ADD_SPARK

      public static final SqlFunction DATE_ADD_SPARK
      The "DATE_ADD(date, numDays)" function (Spark) Returns the date that is num_days after start_date.

    • DATE_SUB_SPARK

      public static final SqlFunction DATE_SUB_SPARK
      The "DATE_SUB(date, numDays)" function (Spark) Returns the date that is num_days before start_date.

    • ADD_MONTHS

      public static final SqlFunction ADD_MONTHS
      The "ADD_MONTHS(start_date, num_months)" function (SPARK) Returns the date that is num_months after start_date.

    • DATEDIFF

      public static final SqlFunction DATEDIFF
      The "DATEDIFF(timeUnit, datetime, datetime2)" function (Microsoft SQL Server, Redshift, Snowflake).

      MySQL has "DATEDIFF(date, date2)" and "TIMEDIFF(time, time2)" functions but Calcite does not implement these because they have no "timeUnit" argument.

    • MSSQL_CONVERT

      public static final SqlFunction MSSQL_CONVERT
      The "CONVERT(type, expr [,style])" function (Microsoft SQL Server).

      Syntax:

      CONVERT( data_type [ ( length ) ], expression [, style ] )

      The optional "style" argument specifies how the value is going to be converted; this implementation ignores the style parameter.

      CONVERT(type, expr, style) is equivalent to CAST(expr AS type), and the implementation delegates most of its logic to actual CAST operator.

      Not to be confused with standard SqlStdOperatorTable.CONVERT, which converts a string from one character set to another.

    • DATE_PART

      public static final SqlFunction DATE_PART
      The "DATE_PART(timeUnit, datetime)" function (Databricks, Postgres, Redshift, Snowflake).

    • DATE_SUB

      public static final SqlFunction DATE_SUB
      The "DATE_SUB(date, interval)" function (BigQuery); subtracts interval from the date, independent of any time zone.

    • DATEPART

      public static final SqlFunction DATEPART
      The "DATEPART(timeUnit, datetime)" function (Microsoft SQL Server).

    • DECODE

      public static final SqlFunction DECODE
      The "DECODE(v, v1, result1, [v2, result2, ...], resultN)" function.

    • IF

      public static final SqlFunction IF
      The "IF(condition, thenValue, elseValue)" function.

    • NVL

      public static final SqlBasicFunction NVL
      The "NVL(value, value)" function.

    • NVL2

      public static final SqlBasicFunction NVL2
      The "NVL2(value, value, value)" function.

    • IFNULL

      public static final SqlFunction IFNULL
      The "IFNULL(value, value)" function.

    • LEN

      public static final SqlFunction LEN
      The "LEN(string)" function.

    • LENGTH

      public static final SqlFunction LENGTH
      The "LENGTH(string)" function.

    • LPAD

      public static final SqlFunction LPAD
      The "LPAD(original_value, return_length[, pattern])" function.

    • RPAD

      public static final SqlFunction RPAD
      The "RPAD(original_value, return_length[, pattern])" function.

    • LTRIM

      public static final SqlFunction LTRIM
      The "LTRIM(string)" function.

    • RTRIM

      public static final SqlFunction RTRIM
      The "RTRIM(string)" function.

    • SPLIT

      public static final SqlFunction SPLIT
      The "SPLIT(string [, delimiter])" function.

    • SPLIT_PART

      public static final SqlFunction SPLIT_PART
      The "SPLIT_PART(string, delimiter, n)" function.

    • STRPOS

      public static final SqlFunction STRPOS
      The "STRPOS(string, substring)" function.

    • INSTR

      public static final SqlFunction INSTR
      The "INSTR(string, substring [, position [, occurrence]])" function.

    • ENDS_WITH

      public static final SqlBasicFunction ENDS_WITH
      The "ENDS_WITH(value1, value2)" function (BigQuery).

    • ENDSWITH

      public static final SqlFunction ENDSWITH
      The "ENDSWITH(value1, value2)" function (Snowflake).

    • STARTS_WITH

      public static final SqlBasicFunction STARTS_WITH
      The "STARTS_WITH(value1, value2)" function (BigQuery, PostgreSQL).

    • STARTSWITH

      public static final SqlFunction STARTSWITH
      The "STARTSWITH(value1, value2)" function (Snowflake).

    • SUBSTR_BIG_QUERY

      public static final SqlFunction SUBSTR_BIG_QUERY
      BigQuery's "SUBSTR(string, position [, substringLength ])" function.

    • SUBSTR_MYSQL

      public static final SqlFunction SUBSTR_MYSQL
      MySQL's "SUBSTR(string, position [, substringLength ])" function.

    • SUBSTR_ORACLE

      public static final SqlFunction SUBSTR_ORACLE
      Oracle's "SUBSTR(string, position [, substringLength ])" function.

      It has different semantics to standard SQL's SqlStdOperatorTable.SUBSTRING function:

      • If substringLength ≤ 0, result is the empty string (Oracle would return null, because it treats the empty string as null, but Calcite does not have these semantics);
      • If position = 0, treat position as 1;
      • If position < 0, treat position as "length(string) + position + 1".

    • CONVERT_ORACLE

      public static final SqlFunction CONVERT_ORACLE

    • SUBSTR_POSTGRESQL

      public static final SqlFunction SUBSTR_POSTGRESQL
      PostgreSQL's "SUBSTR(string, position [, substringLength ])" function.

    • PARSE_URL

      public static final SqlFunction PARSE_URL
      The "PARSE_URL(urlString, partToExtract [, keyToExtract] )" function.

    • FIND_IN_SET

      public static final SqlFunction FIND_IN_SET
      The "FIND_IN_SET(matchStr, textStr)" function.

    • GREATEST

      public static final SqlFunction GREATEST
      The "GREATEST(value, value)" function.

    • GREATEST_PG

      public static final SqlFunction GREATEST_PG
      The "GREATEST(value, value)" function. Identical to the standard GREATEST function except it skips null values and only returns null if all parameters are nulls.

    • LEAST

      public static final SqlFunction LEAST
      The "LEAST(value, value)" function.

    • LEAST_PG

      public static final SqlFunction LEAST_PG
      The "LEAST(value, value)" function. Identical to the standard LEAST function except it skips null values and only returns null if all parameters are nulls.

    • CEIL_BIG_QUERY

      public static final SqlFunction CEIL_BIG_QUERY
      The "CEIL(value)" function. Identical to the standard CEIL function except the return type should be a double if the operand is an integer.

    • FLOOR_BIG_QUERY

      public static final SqlFunction FLOOR_BIG_QUERY
      The "FLOOR(value)" function. Identical to the standard FLOOR function except the return type should be a double if the operand is an integer.

    • TRANSLATE3

      public static final SqlFunction TRANSLATE3
      The TRANSLATE(string_expr, search_chars, replacement_chars) function returns string_expr with all occurrences of each character in search_chars replaced by its corresponding character in replacement_chars.

      It is not defined in the SQL standard, but occurs in Oracle and PostgreSQL.

    • JSON_TYPE

      public static final SqlFunction JSON_TYPE

    • JSON_DEPTH

      public static final SqlFunction JSON_DEPTH

    • JSON_LENGTH

      public static final SqlFunction JSON_LENGTH

    • JSON_KEYS

      public static final SqlFunction JSON_KEYS

    • JSON_PRETTY

      public static final SqlFunction JSON_PRETTY

    • JSON_REMOVE

      public static final SqlFunction JSON_REMOVE

    • JSON_STORAGE_SIZE

      public static final SqlFunction JSON_STORAGE_SIZE

    • JSON_INSERT

      public static final SqlFunction JSON_INSERT

    • JSON_REPLACE

      public static final SqlFunction JSON_REPLACE

    • JSON_SET

      public static final SqlFunction JSON_SET

    • REGEXP_CONTAINS

      public static final SqlFunction REGEXP_CONTAINS
      The "REGEXP_CONTAINS(value, regexp)" function. Returns TRUE if value is a partial match for the regular expression, regexp.

    • REGEXP_EXTRACT

      public static final SqlBasicFunction REGEXP_EXTRACT
      The "REGEXP_EXTRACT(value, regexp[, position[, occurrence]])" function. Returns the substring in value that matches the regexp. Returns NULL if there is no match.

    • REGEXP_EXTRACT_ALL

      public static final SqlBasicFunction REGEXP_EXTRACT_ALL
      The "REGEXP_EXTRACT_ALL(value, regexp)" function. Returns the substring in value that matches the regexp. Returns NULL if there is no match.

    • REGEXP_INSTR

      public static final SqlBasicFunction REGEXP_INSTR
      The "REGEXP_INSTR(value, regexp [, position[, occurrence, [occurrence_position]]])" function. Returns the lowest 1-based position of a regexp in value. Returns NULL if there is no match.

    • REGEXP_REPLACE_2

      public static final SqlFunction REGEXP_REPLACE_2
      The "REGEXP_REPLACE(value, regexp)" function. Replaces all substrings of value that match regexp with rep and returns modified value.

    • REGEXP_REPLACE_3

      public static final SqlFunction REGEXP_REPLACE_3
      The "REGEXP_REPLACE(value, regexp, rep)" function. Replaces all substrings of value that match regexp with rep and returns modified value.

    • REGEXP_REPLACE_4

      public static final SqlFunction REGEXP_REPLACE_4
      The "REGEXP_REPLACE(value, regexp, rep, pos)" function. Replaces all substrings of value that match regexp with rep and returns modified value. Start searching value from character position pos.

    • REGEXP_REPLACE_5

      public static final SqlFunction REGEXP_REPLACE_5
      The "REGEXP_REPLACE(value, regexp, rep, pos, [ occurrence | matchType ])" function. Replaces all substrings of value that match regexp with rep and returns modified value. Start searching value from character position pos. Replace only the occurrence match or all matches if occurrence is 0. matchType is a string of flags to apply to the search.

    • REGEXP_REPLACE_5_ORACLE

      public static final SqlFunction REGEXP_REPLACE_5_ORACLE
      The "REGEXP_REPLACE(value, regexp, rep, pos, matchType)" function. Replaces all substrings of value that match regexp with rep and returns modified value. Start searching value from character position pos. Replace only the occurrence match or all matches if occurrence is 0.

    • REGEXP_REPLACE_6

      public static final SqlFunction REGEXP_REPLACE_6
      The "REGEXP_REPLACE(value, regexp, rep, pos, occurrence, matchType)" function. Replaces all substrings of value that match regexp with rep and returns modified value. Start searching value from character position pos. Replace only the occurrence match or all matches if occurrence is 0. matchType is a string of flags to apply to the search.

    • REGEXP_REPLACE_BIG_QUERY_3

      public static final SqlFunction REGEXP_REPLACE_BIG_QUERY_3
      The "REGEXP_REPLACE(value, regexp, rep)" function. Replaces all substrings of value that match regexp with rep and returns modified value.

    • REGEXP_REPLACE_PG_3

      public static final SqlFunction REGEXP_REPLACE_PG_3
      The "REGEXP_REPLACE(value, regexp, rep)" function. Replaces all substrings of value that match regexp with rep and returns modified value.

    • REGEXP_REPLACE_PG_4

      public static final SqlFunction REGEXP_REPLACE_PG_4
      The "REGEXP_REPLACE(value, regexp, rep, flags)" function. Replaces all substrings of value that match regexp with rep and returns modified value. flags are applied to the search.

    • REGEXP_SUBSTR

      public static final SqlFunction REGEXP_SUBSTR
      The "REGEXP_SUBSTR(value, regexp[, position[, occurrence]])" function. Returns the substring in value that matches the regexp. Returns NULL if there is no match.

    • REGEXP

      public static final SqlFunction REGEXP
      The "REGEXP(value, regexp)" function, equivalent to RLIKE.

    • REGEXP_LIKE

      public static final SqlFunction REGEXP_LIKE
      The "REGEXP_LIKE(value, regexp)" function, equivalent to RLIKE.

    • COMPRESS

      public static final SqlFunction COMPRESS

    • URL_DECODE

      public static final SqlFunction URL_DECODE
      The "URL_DECODE(string)" function.

    • URL_ENCODE

      public static final SqlFunction URL_ENCODE
      The "URL_ENCODE(string)" function.

    • EXTRACT_VALUE

      public static final SqlFunction EXTRACT_VALUE

    • XML_TRANSFORM

      public static final SqlFunction XML_TRANSFORM

    • EXTRACT_XML

      public static final SqlFunction EXTRACT_XML

    • EXISTS_NODE

      public static final SqlFunction EXISTS_NODE

    • BOOL_AND

      public static final SqlAggFunction BOOL_AND
      The "BOOL_AND(condition)" aggregate function, PostgreSQL and Redshift's equivalent to SqlStdOperatorTable.EVERY.

    • BOOL_OR

      public static final SqlAggFunction BOOL_OR
      The "BOOL_OR(condition)" aggregate function, PostgreSQL and Redshift's equivalent to SqlStdOperatorTable.SOME.

    • BOOLAND_AGG

      public static final SqlAggFunction BOOLAND_AGG
      The "BOOLAND_AGG(condition)" aggregate function, Snowflake's equivalent to SqlStdOperatorTable.EVERY.

    • BOOLOR_AGG

      public static final SqlAggFunction BOOLOR_AGG
      The "BOOLOR_AGG(condition)" aggregate function, Snowflake's equivalent to SqlStdOperatorTable.SOME.

    • LOGICAL_AND

      public static final SqlAggFunction LOGICAL_AND
      The "LOGICAL_AND(condition)" aggregate function, BigQuery's equivalent to SqlStdOperatorTable.EVERY.

    • LOGICAL_OR

      public static final SqlAggFunction LOGICAL_OR
      The "LOGICAL_OR(condition)" aggregate function, BigQuery's equivalent to SqlStdOperatorTable.SOME.

    • COUNTIF

      public static final SqlAggFunction COUNTIF
      The "COUNTIF(condition) [OVER (...)]" function, in BigQuery, returns the count of TRUE values for expression.

      COUNTIF(b) is equivalent to COUNT(*) FILTER (WHERE b).

    • ARRAY_AGG

      public static final SqlAggFunction ARRAY_AGG
      The "ARRAY_AGG(value [ ORDER BY ...])" aggregate function, in BigQuery and PostgreSQL, gathers values into arrays.

    • ARRAY_CONCAT_AGG

      public static final SqlAggFunction ARRAY_CONCAT_AGG
      The "ARRAY_CONCAT_AGG(value [ ORDER BY ...])" aggregate function, in BigQuery and PostgreSQL, concatenates array values into arrays.

    • STRING_AGG

      public static final SqlAggFunction STRING_AGG
      The "STRING_AGG(value [, separator ] [ ORDER BY ...])" aggregate function, BigQuery and PostgreSQL's equivalent of SqlStdOperatorTable.LISTAGG.

      STRING_AGG(v, sep ORDER BY x, y) is implemented by rewriting to LISTAGG(v, sep) WITHIN GROUP (ORDER BY x, y).

    • GROUP_CONCAT

      public static final SqlAggFunction GROUP_CONCAT
      The "GROUP_CONCAT([DISTINCT] expr [, ...] [ORDER BY ...] [SEPARATOR sep])" aggregate function, MySQL's equivalent of SqlStdOperatorTable.LISTAGG.

      GROUP_CONCAT(v ORDER BY x, y SEPARATOR s) is implemented by rewriting to LISTAGG(v, s) WITHIN GROUP (ORDER BY x, y).

    • MAX_BY

      public static final SqlAggFunction MAX_BY
      The "MAX_BY(value, comp)" aggregate function, Spark's equivalent to SqlStdOperatorTable.ARG_MAX.

    • MIN_BY

      public static final SqlAggFunction MIN_BY
      The "MIN_BY(condition)" aggregate function, Spark's equivalent to SqlStdOperatorTable.ARG_MIN.

    • PERCENTILE_CONT2

      public static final SqlAggFunction PERCENTILE_CONT2
      The PERCENTILE_CONT function, BigQuery's equivalent to SqlStdOperatorTable.PERCENTILE_CONT, but uses an OVER clause rather than WITHIN GROUP.

    • PERCENTILE_DISC2

      public static final SqlAggFunction PERCENTILE_DISC2
      The PERCENTILE_DISC function, BigQuery's equivalent to SqlStdOperatorTable.PERCENTILE_DISC, but uses an OVER clause rather than WITHIN GROUP.

    • DATE

      public static final SqlFunction DATE
      The "DATE" function. It has the following overloads:
      • DATE(string) is syntactic sugar for CAST(string AS DATE)
      • DATE(year, month, day)
      • DATE(timestampLtz [, timeZone])
      • DATE(timestamp)

    • DATETIME

      public static final SqlFunction DATETIME
      The "DATETIME" function returns a Calcite TIMESTAMP (which BigQuery calls a DATETIME).

      It has the following overloads:

      • DATETIME(year, month, day, hour, minute, second)
      • DATETIME(date[, time])
      • DATETIME(timestampLtz[, timeZone])

    • TIME

      public static final SqlFunction TIME
      The "TIME" function. It has the following overloads:
      • TIME(hour, minute, second)
      • TIME(timestampLtz [, timeZone])
      • TIME(timestamp)

    • TIMESTAMP

      public static final SqlFunction TIMESTAMP
      The "TIMESTAMP" function returns a Calcite TIMESTAMP WITH LOCAL TIME ZONE (which BigQuery calls a TIMESTAMP). It has the following overloads:
      • TIMESTAMP(string[, timeZone])
      • TIMESTAMP(date[, timeZone])
      • TIMESTAMP(timestamp[, timeZone])

    • CURRENT_DATETIME

      public static final SqlFunction CURRENT_DATETIME
      The "CURRENT_DATETIME([timezone])" function.

    • SYSDATE

      public static final SqlFunction SYSDATE
      The "SYSDATE" function.

    • SYSTIMESTAMP

      public static final SqlFunction SYSTIMESTAMP
      The "SYSTIMESTAMP" function.

    • DATE_FROM_UNIX_DATE

      public static final SqlFunction DATE_FROM_UNIX_DATE
      The "DATE_FROM_UNIX_DATE(integer)" function; returns a DATE value a given number of seconds after 1970-01-01.

    • UNIX_DATE

      public static final SqlFunction UNIX_DATE
      The "UNIX_DATE(date)" function; returns the number of days since 1970-01-01.

    • CONTAINS_SUBSTR

      public static final SqlFunction CONTAINS_SUBSTR
      "CONTAINS_SUBSTR(expression, string[, json_scope => json_scope_value ])" function; returns whether string exists as substring in expression, with optional json_scope argument.

    • MONTHNAME

      public static final SqlFunction MONTHNAME
      The "MONTHNAME(datetime)" function; returns the name of the month, in the current locale, of a TIMESTAMP or DATE argument.

    • DAYNAME

      public static final SqlFunction DAYNAME
      The "DAYNAME(datetime)" function; returns the name of the day of the week, in the current locale, of a TIMESTAMP or DATE argument.

    • LEFT

      public static final SqlFunction LEFT

    • REPEAT

      public static final SqlFunction REPEAT

    • SPACE

      public static final SqlFunction SPACE

    • STRCMP

      public static final SqlFunction STRCMP

    • SOUNDEX

      public static final SqlFunction SOUNDEX

    • SOUNDEX_SPARK

      public static final SqlFunction SOUNDEX_SPARK
      The variant of the SOUNDEX operator.

    • DIFFERENCE

      public static final SqlFunction DIFFERENCE

    • ILIKE

      public static final SqlSpecialOperator ILIKE
      The case-insensitive variant of the LIKE operator.

    • NOT_ILIKE

      public static final SqlSpecialOperator NOT_ILIKE
      The case-insensitive variant of the NOT LIKE operator.

    • RLIKE

      public static final SqlSpecialOperator RLIKE
      The regex variant of the LIKE operator.

    • NOT_RLIKE

      public static final SqlSpecialOperator NOT_RLIKE
      The regex variant of the NOT LIKE operator.

    • BIT_COUNT_BIG_QUERY

      public static final SqlFunction BIT_COUNT_BIG_QUERY
      Alias for SqlStdOperatorTable.BITCOUNT.

    • BIT_COUNT_MYSQL

      public static final SqlFunction BIT_COUNT_MYSQL

    • CONCAT_FUNCTION

      public static final SqlFunction CONCAT_FUNCTION
      The "CONCAT(arg, ...)" function that concatenates strings. For example, "CONCAT('a', 'bc', 'd')" returns "abcd".

      It accepts at least 1 argument and returns null if any of the arguments is null.

    • CONCAT_FUNCTION_WITH_NULL

      public static final SqlFunction CONCAT_FUNCTION_WITH_NULL
      The "CONCAT(arg, ...)" function that concatenates strings, which never returns null. For example, "CONCAT('a', 'bc', 'd')" returns "abcd".

      If one of the arguments is null, it will be treated as empty string. "CONCAT('a', null)" returns "a". "CONCAT('a', null, 'b')" returns "ab".

      Returns empty string only when all arguments are null or the empty string. "CONCAT(null)" returns "". "CONCAT(null, '')" returns "". "CONCAT(null, null, null)" returns "".

      It differs from CONCAT_FUNCTION when processing null values.

    • CONCAT2

      public static final SqlFunction CONCAT2
      The "CONCAT(arg0, arg1)" function that concatenates strings. For example, "CONCAT('a', 'bc')" returns "abc".

      If one of the arguments is null, it will be treated as empty string. "CONCAT('a', null)" returns "a".

      Returns null only when both arguments are null. "CONCAT(null, null)" returns null.

      It is assigned SqlKind.CONCAT2 to make it not equal to CONCAT_FUNCTION.

    • CONCAT_WS

      public static final SqlFunction CONCAT_WS
      The "CONCAT_WS(separator, arg1, ...)" function (MySQL); concatenates strings with separator, and treats null arguments as empty strings. For example:
      • CONCAT_WS(',', 'a') returns "a";
      • CONCAT_WS(',', 'a', 'b') returns "a,b".

      Returns null if the separator arg is null. For example, CONCAT_WS(null, 'a', 'b') returns null.

      If all the arguments except the separator are null, it also returns the empty string. For example, CONCAT_WS(',', null, null) returns "".

    • CONCAT_WS_POSTGRESQL

      public static final SqlFunction CONCAT_WS_POSTGRESQL
      The "CONCAT_WS(separator, arg1, ...)" function (Postgres).

      Differs from CONCAT_WS (MySQL) in that its arg1 can be of any type, not limited to string. For example:

      • CONCAT_WS(',', 'a') returns "a";
      • CONCAT_WS(',', 'a', DATE '1945-02-24') returns "a,1945-02-24";
      • CONCAT_WS(',', 'a', ARRAY['b', 'c']) returns "a,[b, c]".

    • CONCAT_WS_MSSQL

      public static final SqlFunction CONCAT_WS_MSSQL
      The "CONCAT_WS(separator, arg1, arg2, ...)" function in (MSSQL).

      Differs from CONCAT_WS (MySQL, Postgres) in that it accepts between 3 and 254 arguments, and never returns null (even if the separator is null). For example:

      • CONCAT_WS(',', 'a', 'b') returns "a,b";
      • CONCAT_WS(null, 'a', 'b') returns "ab";
      • CONCAT_WS(',', null, null) returns "";
      • CONCAT_WS(null, null, null) returns "".

    • CONCAT_WS_SPARK

      public static final SqlFunction CONCAT_WS_SPARK
      The "CONCAT_WS(separator[, str | array(str)]+)" function in (SPARK).

      For example:

      • CONCAT_WS(',', 'a', 'b') returns "a,b";
      • CONCAT_WS(null, 'a', 'b') returns NULL";
      • CONCAT_WS('s') returns "";
      • CONCAT_WS('/', 'a', null, 'b') returns "a/b";
      • CONCAT_WS('/', array('a', 'b')) returns "a/b";
      • CONCAT_WS('/', 'c', array('a', 'b')) returns "c/a/b".

    • ARRAY

      public static final SqlFunction ARRAY
      The "ARRAY(exp, ...)" function (Spark); compare with the standard array value constructor, "ARRAY [exp, ...]".

    • MAP

      public static final SqlFunction MAP
      The "MAP(key, value, ...)" function (Spark); compare with the standard map value constructor, "MAP[key, value, ...]".

    • ARRAY_APPEND

      public static final SqlFunction ARRAY_APPEND
      The "ARRAY_APPEND(array, element)" function.

    • EXISTS

      public static final SqlFunction EXISTS
      The "EXISTS(array, lambda)" function (Spark); returns whether a predicate holds for one or more elements in the array.

    • ARRAY_COMPACT

      public static final SqlFunction ARRAY_COMPACT
      The "ARRAY_COMPACT(array)" function.

    • ARRAY_CONCAT

      public static final SqlFunction ARRAY_CONCAT
      The "ARRAY_CONCAT(array [, array]*)" function.

    • ARRAY_CONTAINS

      public static final SqlFunction ARRAY_CONTAINS
      The "ARRAY_CONTAINS(array, element)" function.

    • ARRAY_DISTINCT

      public static final SqlFunction ARRAY_DISTINCT
      The "ARRAY_DISTINCT(array)" function.

    • ARRAY_EXCEPT

      public static final SqlFunction ARRAY_EXCEPT
      The "ARRAY_EXCEPT(array1, array2)" function.

    • ARRAY_INSERT

      public static final SqlFunction ARRAY_INSERT
      The "ARRAY_INSERT(array, pos, val)" function (Spark).

    • ARRAY_INTERSECT

      public static final SqlFunction ARRAY_INTERSECT
      The "ARRAY_INTERSECT(array1, array2)" function.

    • ARRAY_JOIN

      public static final SqlFunction ARRAY_JOIN
      The "ARRAY_JOIN(array, delimiter [, nullText ])" function.

    • ARRAY_LENGTH

      public static final SqlFunction ARRAY_LENGTH
      The "ARRAY_LENGTH(array)" function.

    • ARRAY_MAX

      public static final SqlFunction ARRAY_MAX
      The "ARRAY_MAX(array)" function.

    • ARRAY_MIN

      public static final SqlFunction ARRAY_MIN
      The "ARRAY_MIN(array)" function.

    • ARRAY_POSITION

      public static final SqlFunction ARRAY_POSITION
      The "ARRAY_POSITION(array, element)" function.

    • ARRAY_PREPEND

      public static final SqlFunction ARRAY_PREPEND
      The "ARRAY_PREPEND(array, element)" function.

    • ARRAY_REMOVE

      public static final SqlFunction ARRAY_REMOVE
      The "ARRAY_REMOVE(array, element)" function.

    • ARRAY_REPEAT

      public static final SqlFunction ARRAY_REPEAT
      The "ARRAY_REPEAT(element, count)" function.

    • ARRAY_REVERSE

      public static final SqlFunction ARRAY_REVERSE
      The "ARRAY_REVERSE(array)" function.

    • ARRAY_SIZE

      public static final SqlFunction ARRAY_SIZE
      The "ARRAY_SIZE(array)" function.

    • ARRAY_SLICE

      public static final SqlFunction ARRAY_SLICE
      The "ARRAY_SLICE(array, start, length)" function.

    • ARRAY_UNION

      public static final SqlFunction ARRAY_UNION
      The "ARRAY_UNION(array1, array2)" function.

    • ARRAY_TO_STRING

      public static final SqlFunction ARRAY_TO_STRING
      The "ARRAY_TO_STRING(array, delimiter [, nullText ])" function.

    • ARRAYS_OVERLAP

      public static final SqlFunction ARRAYS_OVERLAP
      The "ARRAYS_OVERLAP(array1, array2)" function (Spark).

    • ARRAYS_ZIP

      public static final SqlFunction ARRAYS_ZIP
      The "ARRAYS_ZIP(array, ...)" function (Spark).

    • SORT_ARRAY

      public static final SqlFunction SORT_ARRAY
      The "SORT_ARRAY(array)" function (Spark).

    • MAP_CONCAT

      public static final SqlFunction MAP_CONCAT
      The "MAP_CONCAT(map [, map]*)" function.

    • MAP_ENTRIES

      public static final SqlFunction MAP_ENTRIES
      The "MAP_ENTRIES(map)" function.

    • MAP_KEYS

      public static final SqlFunction MAP_KEYS
      The "MAP_KEYS(map)" function.

    • MAP_VALUES

      public static final SqlFunction MAP_VALUES
      The "MAP_VALUES(map)" function.

    • MAP_CONTAINS_KEY

      public static final SqlFunction MAP_CONTAINS_KEY
      The "MAP_CONTAINS_KEY(map, key)" function.

    • MAP_FROM_ARRAYS

      public static final SqlFunction MAP_FROM_ARRAYS
      The "MAP_FROM_ARRAYS(keysArray, valuesArray)" function.

    • MAP_FROM_ENTRIES

      public static final SqlFunction MAP_FROM_ENTRIES
      The "MAP_FROM_ENTRIES(arrayOfEntries)" function.

    • STR_TO_MAP

      public static final SqlFunction STR_TO_MAP
      The "STR_TO_MAP(string[, stringDelimiter[, keyValueDelimiter]])" function.

    • SUBSTRING_INDEX

      public static final SqlFunction SUBSTRING_INDEX
      The "SUBSTRING_INDEX(string, delimiter, count)" function.

    • REVERSE

      public static final SqlFunction REVERSE

    • REVERSE_SPARK

      public static final SqlFunction REVERSE_SPARK
      The "REVERSE(string|array)" function.

    • LEVENSHTEIN

      public static final SqlFunction LEVENSHTEIN
      The "LEVENSHTEIN(string1, string2)" function.

    • FROM_BASE64

      public static final SqlFunction FROM_BASE64

    • TO_BASE64

      public static final SqlFunction TO_BASE64

    • BASE64

      public static final SqlFunction BASE64

    • UN_BASE64

      public static final SqlFunction UN_BASE64

    • FROM_BASE32

      public static final SqlFunction FROM_BASE32

    • TO_BASE32

      public static final SqlFunction TO_BASE32

    • FROM_HEX

      public static final SqlFunction FROM_HEX
      The "FROM_HEX(varchar)" function; converts a hexadecimal-encoded varchar into bytes.

    • TO_HEX

      public static final SqlFunction TO_HEX
      The "TO_HEX(binary)" function; converts binary into a hexadecimal varchar.

    • HEX

      public static final SqlFunction HEX
      The "HEX(string)" function; converts string into a hexadecimal varchar.

    • FORMAT_NUMBER

      public static final SqlFunction FORMAT_NUMBER
      The "FORMAT_NUMBER(value, decimalOrFormat)" function.

    • TO_CHAR

      public static final SqlFunction TO_CHAR
      The "TO_CHAR(timestamp, format)" function; converts timestamp to string according to the given format.

      (TO_CHAR is not supported in MySQL, but it is supported in MariaDB, a variant of MySQL covered by SqlLibrary.MYSQL.)

    • TO_CHAR_PG

      public static final SqlFunction TO_CHAR_PG
      The "TO_CHAR(timestamp, format)" function; converts timestamp to string according to the given format.

    • TO_DATE

      public static final SqlFunction TO_DATE
      The "TO_DATE(string1, string2)" function; casts string1 to a DATE using the format specified in string2.

    • TO_DATE_PG

      public static final SqlFunction TO_DATE_PG
      The "TO_DATE(string1, string2)" function for PostgreSQL; casts string1 to a DATE using the format specified in string2.

    • TO_TIMESTAMP

      public static final SqlFunction TO_TIMESTAMP
      The "TO_TIMESTAMP(string1, string2)" function; casts string1 to a TIMESTAMP using the format specified in string2.

    • TO_TIMESTAMP_PG

      public static final SqlFunction TO_TIMESTAMP_PG
      The "TO_TIMESTAMP(string1, string2)" function for PostgreSQL; casts string1 to a TIMESTAMP using the format specified in string2.

    • PARSE_TIME

      public static final SqlFunction PARSE_TIME
      The "PARSE_TIME(string, string)" function (BigQuery); converts a string representation of time to a TIME value.

    • PARSE_DATE

      public static final SqlFunction PARSE_DATE
      The "PARSE_DATE(string, string)" function (BigQuery); Converts a string representation of date to a DATE object.

    • PARSE_TIMESTAMP

      public static final SqlFunction PARSE_TIMESTAMP
      The "PARSE_TIMESTAMP(string, string [, timezone])" function (BigQuery); Formats a timestamp object according to the specified string.

      In BigQuery, the "TIMESTAMP" datatype maps to Calcite's TIMESTAMP_WITH_LOCAL_TIME_ZONE

    • PARSE_DATETIME

      public static final SqlFunction PARSE_DATETIME
      The "PARSE_DATETIME(string, string [, timezone])" function (BigQuery); Formats a timestamp object according to the specified string.

      Note that the TIMESTAMP type of Calcite and Standard SQL is called DATETIME in BigQuery.

    • FORMAT_TIME

      public static final SqlFunction FORMAT_TIME
      The "FORMAT_TIME(string, time)" function (BigQuery); Formats a time object according to the specified string.

    • FORMAT_DATE

      public static final SqlFunction FORMAT_DATE
      The "FORMAT_DATE(string, date)" function (BigQuery); Formats a date object according to the specified string.

    • FORMAT_TIMESTAMP

      public static final SqlFunction FORMAT_TIMESTAMP
      The "FORMAT_TIMESTAMP(string, timestamp)" function (BigQuery); Formats a timestamp object according to the specified string.

      In BigQuery, the "TIMESTAMP" datatype maps to Calcite's TIMESTAMP_WITH_LOCAL_TIME_ZONE

    • FORMAT_DATETIME

      public static final SqlFunction FORMAT_DATETIME
      The "FORMAT_DATETIME(string, timestamp)" function (BigQuery); formats a timestamp object according to the specified string.

      Note that the TIMESTAMP type of Calcite and Standard SQL is called DATETIME in BigQuery.

    • TIMESTAMP_ADD2

      public static final SqlBasicFunction TIMESTAMP_ADD2
      The "TIMESTAMP_ADD(timestamp, interval)" function (BigQuery), the two-argument variant of the built-in TIMESTAMPADD function, which has three arguments.

      In BigQuery, the syntax is "TIMESTAMP_ADD(timestamp, INTERVAL int64_expression date_part)" but in Calcite the second argument can be any interval expression, not just an interval literal.

    • TIMESTAMP_DIFF3

      public static final SqlFunction TIMESTAMP_DIFF3
      The "TIMESTAMP_DIFF(timestamp, timestamp, timeUnit)" function (BigQuery); returns the number of timeUnit between the two timestamp expressions.

      TIMESTAMP_DIFF(t1, t2, unit) is equivalent to TIMESTAMPDIFF(unit, t2, t1) and (t1 - t2) unit.

    • TIME_ADD

      public static final SqlFunction TIME_ADD
      The "TIME_ADD(time, interval)" function (BigQuery); adds interval expression to the specified time expression.

    • TIME_DIFF

      public static final SqlFunction TIME_DIFF
      The "TIME_DIFF(time, time, timeUnit)" function (BigQuery); returns the number of timeUnit between the two time expressions.

    • DATE_TRUNC

      public static final SqlFunction DATE_TRUNC
      The "DATE_TRUNC(date, timeUnit)" function (BigQuery); truncates a DATE value to the beginning of a timeUnit.

    • TIME_SUB

      public static final SqlFunction TIME_SUB
      The "TIME_SUB(time, interval)" function (BigQuery); subtracts an interval from a time, independent of any time zone.

      In BigQuery, the syntax is "TIME_SUB(time, INTERVAL int64 date_part)" but in Calcite the second argument can be any interval expression, not just an interval literal.

    • TIME_TRUNC

      public static final SqlFunction TIME_TRUNC
      The "TIME_TRUNC(time, timeUnit)" function (BigQuery); truncates a TIME value to the beginning of a timeUnit.

    • TIMESTAMP_SUB

      public static final SqlBasicFunction TIMESTAMP_SUB
      The "TIMESTAMP_SUB(timestamp, interval)" function (BigQuery); subtracts an interval from a timestamp, independent of any time zone.

      In BigQuery, the syntax is "TIMESTAMP_SUB(timestamp, INTERVAL int64 date_part)" but in Calcite the second argument can be any interval expression, not just an interval literal.

    • DATETIME_SUB

      public static final SqlFunction DATETIME_SUB
      The "DATETIME_SUB(timestamp, interval)" function (BigQuery).

      Note that the TIMESTAMP type of Calcite and Standard SQL is called DATETIME in BigQuery.

      A synonym for TIMESTAMP_SUB, which supports both TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE operands.

    • TIMESTAMP_TRUNC

      public static final SqlFunction TIMESTAMP_TRUNC
      The "TIMESTAMP_TRUNC(timestamp, timeUnit[, timeZone])" function (BigQuery); truncates a TIMESTAMP WITH LOCAL TIME ZONE value to the beginning of a timeUnit.

      Note that the TIMESTAMP WITH LOCAL TIME ZONE type of Calcite is called TIMESTAMP in BigQuery.

    • DATETIME_TRUNC

      public static final SqlFunction DATETIME_TRUNC
      The "DATETIME_TRUNC(timestamp, timeUnit)" function (BigQuery); truncates a TIMESTAMP value to the beginning of a timeUnit.

      Note that the TIMESTAMP type of Calcite and Standard SQL is called DATETIME in BigQuery.

    • TIMESTAMP_SECONDS

      public static final SqlFunction TIMESTAMP_SECONDS
      The "TIMESTAMP_SECONDS(bigint)" function; returns a TIMESTAMP value a given number of seconds after 1970-01-01 00:00:00.

    • TIMESTAMP_MILLIS

      public static final SqlFunction TIMESTAMP_MILLIS
      The "TIMESTAMP_MILLIS(bigint)" function; returns a TIMESTAMP value a given number of milliseconds after 1970-01-01 00:00:00.

    • TIMESTAMP_MICROS

      public static final SqlFunction TIMESTAMP_MICROS
      The "TIMESTAMP_MICROS(bigint)" function; returns a TIMESTAMP value a given number of micro-seconds after 1970-01-01 00:00:00.

    • UNIX_SECONDS

      public static final SqlFunction UNIX_SECONDS
      The "UNIX_SECONDS(bigint)" function; returns the number of seconds since 1970-01-01 00:00:00.

    • UNIX_MILLIS

      public static final SqlFunction UNIX_MILLIS
      The "UNIX_MILLIS(bigint)" function; returns the number of milliseconds since 1970-01-01 00:00:00.

    • UNIX_MICROS

      public static final SqlFunction UNIX_MICROS
      The "UNIX_MICROS(bigint)" function; returns the number of microseconds since 1970-01-01 00:00:00.

    • DATETIME_ADD

      public static final SqlFunction DATETIME_ADD
      The "DATETIME_ADD(timestamp, interval)" function (BigQuery). As TIMESTAMP_ADD, returns a Calcite TIMESTAMP (which BigQuery calls a DATETIME).

    • DATETIME_DIFF

      public static final SqlFunction DATETIME_DIFF
      The "DATETIME_DIFF(timestamp, timestamp2, timeUnit)" function (BigQuery).

      Note that the TIMESTAMP type of Calcite and Standard SQL is called DATETIME in BigQuery.

    • SAFE_ADD

      public static final SqlFunction SAFE_ADD
      The "SAFE_ADD(numeric1, numeric2)" function; equivalent to the + operator but returns null if overflow occurs.

    • SAFE_DIVIDE

      public static final SqlFunction SAFE_DIVIDE
      The "SAFE_DIVIDE(numeric1, numeric2)" function; equivalent to the / operator but returns null if an error occurs, such as overflow or division by zero.

    • SAFE_MULTIPLY

      public static final SqlFunction SAFE_MULTIPLY
      The "SAFE_MULTIPLY(numeric1, numeric2)" function; equivalent to the * operator but returns null if overflow occurs.

    • SAFE_NEGATE

      public static final SqlFunction SAFE_NEGATE
      The "SAFE_NEGATE(numeric)" function; negates numeric and returns null if overflow occurs.

    • SAFE_SUBTRACT

      public static final SqlFunction SAFE_SUBTRACT
      The "SAFE_SUBTRACT(numeric1, numeric2)" function; equivalent to the - operator but returns null if overflow occurs.

    • CHAR

      public static final SqlFunction CHAR
      The "CHAR(n)" function; returns the character whose ASCII code is n % 256, or null if n < 0.

    • CHR

      public static final SqlFunction CHR
      The "CHR(n)" function; returns the character whose UTF-8 code is n.

    • CODE_POINTS_TO_BYTES

      public static final SqlFunction CODE_POINTS_TO_BYTES
      The "CODE_POINTS_TO_BYTES(integers)" function (BigQuery); Converts an array of extended ASCII code points to bytes.

    • CODE_POINTS_TO_STRING

      public static final SqlFunction CODE_POINTS_TO_STRING
      The "CODE_POINTS_TO_STRING(integers)" function (BigQuery); Converts an array of Unicode code points to string.

    • TO_CODE_POINTS

      public static final SqlFunction TO_CODE_POINTS
      The "TO_CODE_POINTS(string or binary)" function (BigQuery); Converts a string or binary value to an array of integers that represent code points or extended ASCII character values.

    • TANH

      public static final SqlFunction TANH

    • COTH

      public static final SqlFunction COTH
      The "COTH(value)" function; returns the hyperbolic cotangent of value.

    • COSH

      public static final SqlFunction COSH

    • ACOSH

      public static final SqlFunction ACOSH
      The ACOSH(numeric) function; returns the inverse hyperbolic cosine of value.

    • ASINH

      public static final SqlFunction ASINH
      The ASINH(numeric) function; returns the inverse hyperbolic sine of value.

    • ATANH

      public static final SqlFunction ATANH
      The ATANH(numeric) function; returns the inverse hyperbolic tangent of value.

    • COSD

      public static final SqlFunction COSD
      The COSD(numeric) function; returns the cosine of value. value is treated as degrees.

    • SIND

      public static final SqlFunction SIND
      The SIND(numeric) function; returns the sine of value. value is treated as degrees.

    • TAND

      public static final SqlFunction TAND
      The TAND(numeric) function; returns the tangent of value. value is treated as degrees.

    • ACOSD

      public static final SqlFunction ACOSD
      The ACOSD(numeric) function; returns the inverse cosine of value in degrees.

    • ASIND

      public static final SqlFunction ASIND
      The ACOSD(numeric) function; returns the inverse sine of value in degrees.

    • ATAND

      public static final SqlFunction ATAND
      The ACOSD(numeric) function; returns the inverse tangent of value in degrees.

    • SECH

      public static final SqlFunction SECH
      The "COTH(value)" function; returns the hyperbolic secant of value.

    • CSCH

      public static final SqlFunction CSCH
      The "COTH(value)" function; returns the hyperbolic cosecant of value.

    • SINH

      public static final SqlFunction SINH

    • CSC

      public static final SqlFunction CSC

    • SEC

      public static final SqlFunction SEC

    • FACTORIAL

      public static final SqlFunction FACTORIAL
      The FACTORIAL(integer) function. Returns the factorial of integer, the range of integer is [0, 20]. Otherwise, returns NULL.

    • BIN

      public static final SqlFunction BIN

    • MD5

      public static final SqlFunction MD5

    • CRC32

      public static final SqlFunction CRC32

    • SHA1

      public static final SqlFunction SHA1

    • SHA256

      public static final SqlFunction SHA256

    • SHA512

      public static final SqlFunction SHA512

    • IS_INF

      public static final SqlFunction IS_INF
      The "IS_INF(value)" function. Returns whether value is infinite.

    • IS_NAN

      public static final SqlFunction IS_NAN
      The "IS_NAN(value)" function. Returns whether value is NaN.

    • LOG

      public static final SqlFunction LOG
      The "LOG(value [, value2])" function.
      See Also:

    • LOG_MYSQL

      public static final SqlFunction LOG_MYSQL
      The "LOG(numeric1 [, numeric2 ]) " function. Returns the logarithm of numeric2 to base numeric1.

    • LOG_POSTGRES

      public static final SqlFunction LOG_POSTGRES
      The "LOG(numeric1 [, numeric2 ]) " function. Returns the logarithm of numeric2 to base numeric1.

    • LOG2

      public static final SqlFunction LOG2
      The "LOG2(numeric)" function. Returns the base 2 logarithm of numeric.

    • LOG1P

      public static final SqlFunction LOG1P
      The "LOG1p(numeric)" function. Returns log(1 + numeric).

    • POW

      public static final SqlFunction POW

    • POWER_PG

      public static final SqlFunction POWER_PG
      The POWER(numeric, numeric) function.

      The return type is DECIMAL if either argument is a DECIMAL. In all other cases, the return type is a double.

    • TRUNC_BIG_QUERY

      public static final SqlFunction TRUNC_BIG_QUERY
      The "TRUNC(numeric1 [, integer2])" function. Identical to the standard TRUNCATE function except the return type should be a double if numeric1 is an integer.

    • INFIX_CAST

      public static final SqlOperator INFIX_CAST
      Infix "::" cast operator used by PostgreSQL, for example '100'::INTEGER.

    • SAFE_CAST

      public static final SqlFunction SAFE_CAST
      The "SAFE_CAST(expr AS type)" function; identical to CAST(), except that if conversion fails, it returns NULL instead of raising an error.

    • TRY_CAST

      public static final SqlFunction TRY_CAST
      The "TRY_CAST(expr AS type)" function, equivalent to SAFE_CAST.

    • OFFSET

      public static final SqlOperator OFFSET
      The "OFFSET(index)" array subscript operator used by BigQuery. The index starts at 0 and produces an error if the index is out of range.

    • ORDINAL

      public static final SqlOperator ORDINAL
      The "ORDINAL(index)" array subscript operator used by BigQuery. The index starts at 1 and produces an error if the index is out of range.

    • SAFE_OFFSET

      public static final SqlOperator SAFE_OFFSET
      The "SAFE_OFFSET(index)" array subscript operator used by BigQuery. The index starts at 0 and returns null if the index is out of range.

    • SAFE_ORDINAL

      public static final SqlOperator SAFE_ORDINAL
      The "SAFE_ORDINAL(index)" array subscript operator used by BigQuery. The index starts at 1 and returns null if the index is out of range.

    • NULL_SAFE_EQUAL

      public static final SqlOperator NULL_SAFE_EQUAL
      NULL-safe "<=>" equal operator used by MySQL, for example 1<=>NULL.

    • BITAND_AGG

      public static final SqlAggFunction BITAND_AGG
      The "BITAND_AGG(expression)" function. Equivalent to the standard "BIT_AND(expression)".

    • BITOR_AGG

      public static final SqlAggFunction BITOR_AGG
      The "BITOR_AGG(expression)" function. Equivalent to the standard "BIT_OR(expression)".

    • BIT_LENGTH

      public static final SqlFunction BIT_LENGTH
      The "BIT_LENGTH(string or binary)" function.

    • BIT_GET

      public static final SqlBasicFunction BIT_GET
      The "BIT_GET(value, position)" function.

    • GETBIT

      public static final SqlFunction GETBIT
      Alias for BIT_GET.

    • RANDOM

      public static final SqlFunction RANDOM
      The RANDOM() function. Equivalent to RAND().