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. This function is only on Redshift, but we list it in PostgreSQL because Redshift does not have its own library.

    • 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).

    • 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.

    • 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.

    • IFNULL

      public static final SqlFunction IFNULL
      The "IFNULL(value, value)" 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.

    • ENDS_WITH

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

    • STARTS_WITH

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

    • 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".

    • SUBSTR_POSTGRESQL

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

    • GREATEST

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

    • LEAST

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

    • 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_REPLACE

      public static final SqlFunction REGEXP_REPLACE

    • COMPRESS

      public static final SqlFunction COMPRESS

    • 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.

    • 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.

    • 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.

    • 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.

    • 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

    • 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.

    • CONCAT_FUNCTION

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

    • CONCAT2

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

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

    • ARRAY_LENGTH

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

    • ARRAY_REVERSE

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

    • ARRAY_CONCAT

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

    • REVERSE

      public static final SqlFunction REVERSE

    • FROM_BASE64

      public static final SqlFunction FROM_BASE64

    • TO_BASE64

      public static final SqlFunction TO_BASE64

    • 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_TIMESTAMP

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

    • 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.

    • 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
      BigQuery's DATETIME_SUB(timestamp, interval) function is a synonym for TIMESTAMP_SUB because in Calcite, DATETIME is an alias for TIMESTAMP.

    • TIMESTAMP_TRUNC

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

    • 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).

    • 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.

    • TANH

      public static final SqlFunction TANH

    • COSH

      public static final SqlFunction COSH

    • SINH

      public static final SqlFunction SINH

    • MD5

      public static final SqlFunction MD5

    • SHA1

      public static final SqlFunction SHA1

    • POW

      public static final SqlFunction POW

    • TRUNC

      public static final SqlFunction TRUNC

    • INFIX_CAST

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

    • NULL_SAFE_EQUAL

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