SQL language

The page describes the SQL dialect recognized by Calcite’s default SQL parser.

Grammar

SQL grammar in BNF-likeform.

statement:
      setStatement
  |   resetStatement
  |   explain
  |   describe
  |   insert
  |   update
  |   merge
  |   delete
  |   query
statementList:
      statement [ ';' statement ]* [ ';' ]
setStatement:
      [ ALTER ( SYSTEM | SESSION ) ] SET identifier '=' expression
resetStatement:
      [ ALTER ( SYSTEM | SESSION ) ] RESET identifier
  |   [ ALTER ( SYSTEM | SESSION ) ] RESET ALL
explain:
      EXPLAIN PLAN
      [ WITH TYPE | WITH IMPLEMENTATION | WITHOUT IMPLEMENTATION ]
      [ EXCLUDING ATTRIBUTES | INCLUDING [ ALL ] ATTRIBUTES ]
      [ AS JSON | AS XML ]
      FOR ( query | insert | update | merge | delete )
describe:
      DESCRIBE DATABASE databaseName
   |  DESCRIBE CATALOG [ databaseName . ] catalogName
   |  DESCRIBE SCHEMA [ [ databaseName . ] catalogName ] . schemaName
   |  DESCRIBE [ TABLE ] [ [ [ databaseName . ] catalogName . ] schemaName . ] tableName [ columnName ]
   |  DESCRIBE [ STATEMENT ] ( query | insert | update | merge | delete )
insert:
      ( INSERT | UPSERT ) INTO tablePrimary
      [ '(' column [, column ]* ')' ]
      query
update:
      UPDATE tablePrimary
      SET assign [, assign ]*
      [ WHERE booleanExpression ]
assign:
      identifier '=' expression
merge:
      MERGE INTO tablePrimary [ [ AS ] alias ]
      USING tablePrimary
      ON booleanExpression
      [ WHEN MATCHED THEN UPDATE SET assign [, assign ]* ]
      [ WHEN NOT MATCHED THEN INSERT VALUES '(' value [ , value ]* ')' ]
delete:
      DELETE FROM tablePrimary [ [ AS ] alias ]
      [ WHERE booleanExpression ]
query:
      values
  |   WITH withItem [ , withItem ]* query
  |   {
          select
      |   selectWithoutFrom
      |   query UNION [ ALL | DISTINCT ] query
      |   query EXCEPT [ ALL | DISTINCT ] query
      |   query MINUS [ ALL | DISTINCT ] query
      |   query INTERSECT [ ALL | DISTINCT ] query
      }
      [ ORDER BY orderItem [, orderItem ]* ]
      [ LIMIT [ start, ] { count | ALL } ]
      [ OFFSET start { ROW | ROWS } ]
      [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
withItem:
      name
      [ '(' column [, column ]* ')' ]
      AS '(' query ')'
orderItem:
      expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
select:
      SELECT [ STREAM ] [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]
      [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
selectWithoutFrom:
      SELECT [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }
projectItem:
      expression [ [ AS ] columnAlias ]
  |   tableAlias . *
tableExpression:
      tableReference [, tableReference ]*
  |   tableExpression [ NATURAL ] [ ( LEFT | RIGHT | FULL ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
  |   tableExpression CROSS JOIN tableExpression
  |   tableExpression [ CROSS | OUTER ] APPLY tableExpression
joinCondition:
      ON booleanExpression
  |   USING '(' column [, column ]* ')'
tableReference:
      tablePrimary
      [ FOR SYSTEM_TIME AS OF expression ]
      [ matchRecognize ]
      [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
tablePrimary:
      [ [ catalogName . ] schemaName . ] tableName
      '(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'
  |   tablePrimary [ EXTEND ] '(' columnDecl [, columnDecl ]* ')'
  |   [ LATERAL ] '(' query ')'
  |   UNNEST '(' expression ')' [ WITH ORDINALITY ]
  |   [ LATERAL ] TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]* ')' ')'
columnDecl:
      column type [ NOT NULL ]
values:
      VALUES expression [, expression ]*
groupItem:
      expression
  |   '(' ')'
  |   '(' expression [, expression ]* ')'
  |   CUBE '(' expression [, expression ]* ')'
  |   ROLLUP '(' expression [, expression ]* ')'
  |   GROUPING SETS '(' groupItem [, groupItem ]* ')'
window:
      windowName
  |   windowSpec
windowSpec:
      '('
      [ windowName ]
      [ ORDER BY orderItem [, orderItem ]* ]
      [ PARTITION BY expression [, expression ]* ]
      [
          RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING }
      |   ROWS numericExpression { PRECEDING | FOLLOWING }
      ]
      ')'

In insert, if the INSERT or UPSERT statement does not specify alist of target columns, the query must have the same number ofcolumns as the target table, except in certainconformance levels.

In merge, at least one of the WHEN MATCHED and WHEN NOT MATCHED clauses mustbe present.

tablePrimary may only contain an EXTEND clause in certainconformance levels;in those same conformance levels, any column in insert may be replaced bycolumnDecl, which has a similar effect to including it in an EXTEND clause.

In orderItem, if expression is a positive integer n, it denotesthe _n_th item in the SELECT clause.

In query, count and start may each be either an unsigned integer literalor a dynamic parameter whose value is an integer.

An aggregate query is a query that contains a GROUP BY or a HAVINGclause, or aggregate functions in the SELECT clause. In the SELECT,HAVING and ORDER BY clauses of an aggregate query, all expressionsmust be constant within the current group (that is, grouping constantsas defined by the GROUP BY clause, or constants), or aggregatefunctions, or a combination of constants and aggregatefunctions. Aggregate and grouping functions may only appear in anaggregate query, and only in a SELECT, HAVING or ORDER BY clause.

A scalar sub-query is a sub-query used as an expression.If the sub-query returns no rows, the value is NULL; if itreturns more than one row, it is an error.

IN, EXISTS and scalar sub-queries can occurin any place where an expression can occur (such as the SELECT clause,WHERE clause, ON clause of a JOIN, or as an argument to an aggregatefunction).

An IN, EXISTS or scalar sub-query may be correlated; that is, itmay refer to tables in the FROM clause of an enclosing query.

selectWithoutFrom is equivalent to VALUES,but is not standard SQL and is only allowed in certainconformance levels.

MINUS is equivalent to EXCEPT,but is not standard SQL and is only allowed in certainconformance levels.

CROSS APPLY and OUTER APPLY are only allowed in certainconformance levels.

“LIMIT start, count” is equivalent to “LIMIT count OFFSET start”but is only allowed in certainconformance levels.

Keywords

The following is a list of SQL keywords.Reserved keywords are bold.

A,ABS,ABSENT,ABSOLUTE,ACTION,ADA,ADD,ADMIN,AFTER,ALL,ALLOCATE,ALLOW,ALTER,ALWAYS,AND,ANY,APPLY,ARE,ARRAY,ARRAY_MAX_CARDINALITY,AS,ASC,ASENSITIVE,ASSERTION,ASSIGNMENT,ASYMMETRIC,AT,ATOMIC,ATTRIBUTE,ATTRIBUTES,AUTHORIZATION,AVG,BEFORE,BEGIN,BEGIN_FRAME,BEGIN_PARTITION,BERNOULLI,BETWEEN,BIGINT,BINARY,BIT,BLOB,BOOLEAN,BOTH,BREADTH,BY,C,CALL,CALLED,CARDINALITY,CASCADE,CASCADED,CASE,CAST,CATALOG,CATALOG_NAME,CEIL,CEILING,CENTURY,CHAIN,CHAR,CHARACTER,CHARACTERISTICS,CHARACTERS,CHARACTER_LENGTH,CHARACTER_SET_CATALOG,CHARACTER_SET_NAME,CHARACTER_SET_SCHEMA,CHAR_LENGTH,CHECK,CLASSIFIER,CLASS_ORIGIN,CLOB,CLOSE,COALESCE,COBOL,COLLATE,COLLATION,COLLATION_CATALOG,COLLATION_NAME,COLLATION_SCHEMA,COLLECT,COLUMN,COLUMN_NAME,COMMAND_FUNCTION,COMMAND_FUNCTION_CODE,COMMIT,COMMITTED,CONDITION,CONDITIONAL,CONDITION_NUMBER,CONNECT,CONNECTION,CONNECTION_NAME,CONSTRAINT,CONSTRAINTS,CONSTRAINT_CATALOG,CONSTRAINT_NAME,CONSTRAINT_SCHEMA,CONSTRUCTOR,CONTAINS,CONTINUE,CONVERT,CORR,CORRESPONDING,COUNT,COVAR_POP,COVAR_SAMP,CREATE,CROSS,CUBE,CUME_DIST,CURRENT,CURRENT_CATALOG,CURRENT_DATE,CURRENT_DEFAULT_TRANSFORM_GROUP,CURRENT_PATH,CURRENT_ROLE,CURRENT_ROW,CURRENT_SCHEMA,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_TRANSFORM_GROUP_FOR_TYPE,CURRENT_USER,CURSOR,CURSOR_NAME,CYCLE,DATA,DATABASE,DATE,DATETIME_INTERVAL_CODE,DATETIME_INTERVAL_PRECISION,DAY,DEALLOCATE,DEC,DECADE,DECIMAL,DECLARE,DEFAULT,DEFAULTS,DEFERRABLE,DEFERRED,DEFINE,DEFINED,DEFINER,DEGREE,DELETE,DENSE_RANK,DEPTH,DEREF,DERIVED,DESC,DESCRIBE,DESCRIPTION,DESCRIPTOR,DETERMINISTIC,DIAGNOSTICS,DISALLOW,DISCONNECT,DISPATCH,DISTINCT,DOMAIN,DOUBLE,DOW,DOY,DROP,DYNAMIC,DYNAMIC_FUNCTION,DYNAMIC_FUNCTION_CODE,EACH,ELEMENT,ELSE,EMPTY,ENCODING,END,END-EXEC,END_FRAME,END_PARTITION,EPOCH,EQUALS,ERROR,ESCAPE,EVERY,EXCEPT,EXCEPTION,EXCLUDE,EXCLUDING,EXEC,EXECUTE,EXISTS,EXP,EXPLAIN,EXTEND,EXTERNAL,EXTRACT,FALSE,FETCH,FILTER,FINAL,FIRST,FIRST_VALUE,FLOAT,FLOOR,FOLLOWING,FOR,FOREIGN,FORMAT,FORTRAN,FOUND,FRAC_SECOND,FRAME_ROW,FREE,FROM,FULL,FUNCTION,FUSION,G,GENERAL,GENERATED,GEOMETRY,GET,GLOBAL,GO,GOTO,GRANT,GRANTED,GROUP,GROUPING,GROUPS,HAVING,HIERARCHY,HOLD,HOUR,IDENTITY,IGNORE,IMMEDIATE,IMMEDIATELY,IMPLEMENTATION,IMPORT,IN,INCLUDING,INCREMENT,INDICATOR,INITIAL,INITIALLY,INNER,INOUT,INPUT,INSENSITIVE,INSERT,INSTANCE,INSTANTIABLE,INT,INTEGER,INTERSECT,INTERSECTION,INTERVAL,INTO,INVOKER,IS,ISODOW,ISOLATION,ISOYEAR,JAVA,JOIN,JSON,JSON_ARRAY,JSON_ARRAYAGG,JSON_EXISTS,JSON_OBJECT,JSON_OBJECTAGG,JSON_QUERY,JSON_VALUE,K,KEY,KEY_MEMBER,KEY_TYPE,LABEL,LAG,LANGUAGE,LARGE,LAST,LAST_VALUE,LATERAL,LEAD,LEADING,LEFT,LENGTH,LEVEL,LIBRARY,LIKE,LIKE_REGEX,LIMIT,LN,LOCAL,LOCALTIME,LOCALTIMESTAMP,LOCATOR,LOWER,M,MAP,MATCH,MATCHED,MATCHES,MATCH_NUMBER,MATCH_RECOGNIZE,MAX,MAXVALUE,MEASURES,MEMBER,MERGE,MESSAGE_LENGTH,MESSAGE_OCTET_LENGTH,MESSAGE_TEXT,METHOD,MICROSECOND,MILLENNIUM,MILLISECOND,MIN,MINUS,MINUTE,MINVALUE,MOD,MODIFIES,MODULE,MONTH,MORE,MULTISET,MUMPS,NAME,NAMES,NANOSECOND,NATIONAL,NATURAL,NCHAR,NCLOB,NESTING,NEW,NEXT,NO,NONE,NORMALIZE,NORMALIZED,NOT,NTH_VALUE,NTILE,NULL,NULLABLE,NULLIF,NULLS,NUMBER,NUMERIC,OBJECT,OCCURRENCES_REGEX,OCTETS,OCTET_LENGTH,OF,OFFSET,OLD,OMIT,ON,ONE,ONLY,OPEN,OPTION,OPTIONS,OR,ORDER,ORDERING,ORDINALITY,OTHERS,OUT,OUTER,OUTPUT,OVER,OVERLAPS,OVERLAY,OVERRIDING,PAD,PARAMETER,PARAMETER_MODE,PARAMETER_NAME,PARAMETER_ORDINAL_POSITION,PARAMETER_SPECIFIC_CATALOG,PARAMETER_SPECIFIC_NAME,PARAMETER_SPECIFIC_SCHEMA,PARTIAL,PARTITION,PASCAL,PASSING,PASSTHROUGH,PAST,PATH,PATTERN,PER,PERCENT,PERCENTILE_CONT,PERCENTILE_DISC,PERCENT_RANK,PERIOD,PERMUTE,PLACING,PLAN,PLI,PORTION,POSITION,POSITION_REGEX,POWER,PRECEDES,PRECEDING,PRECISION,PREPARE,PRESERVE,PREV,PRIMARY,PRIOR,PRIVILEGES,PROCEDURE,PUBLIC,QUARTER,RANGE,RANK,READ,READS,REAL,RECURSIVE,REF,REFERENCES,REFERENCING,REGR_AVGX,REGR_AVGY,REGR_COUNT,REGR_INTERCEPT,REGR_R2,REGR_SLOPE,REGR_SXX,REGR_SXY,REGR_SYY,RELATIVE,RELEASE,REPEATABLE,REPLACE,RESET,RESPECT,RESTART,RESTRICT,RESULT,RETURN,RETURNED_CARDINALITY,RETURNED_LENGTH,RETURNED_OCTET_LENGTH,RETURNED_SQLSTATE,RETURNING,RETURNS,REVOKE,RIGHT,ROLE,ROLLBACK,ROLLUP,ROUTINE,ROUTINE_CATALOG,ROUTINE_NAME,ROUTINE_SCHEMA,ROW,ROWS,ROW_COUNT,ROW_NUMBER,RUNNING,SAVEPOINT,SCALAR,SCALE,SCHEMA,SCHEMA_NAME,SCOPE,SCOPE_CATALOGS,SCOPE_NAME,SCOPE_SCHEMA,SCROLL,SEARCH,SECOND,SECTION,SECURITY,SEEK,SELECT,SELF,SENSITIVE,SEQUENCE,SERIALIZABLE,SERVER,SERVER_NAME,SESSION,SESSION_USER,SET,SETS,SHOW,SIMILAR,SIMPLE,SIZE,SKIP,SMALLINT,SOME,SOURCE,SPACE,SPECIFIC,SPECIFICTYPE,SPECIFIC_NAME,SQL,SQLEXCEPTION,SQLSTATE,SQLWARNING,SQL_BIGINT,SQL_BINARY,SQL_BIT,SQL_BLOB,SQL_BOOLEAN,SQL_CHAR,SQL_CLOB,SQL_DATE,SQL_DECIMAL,SQL_DOUBLE,SQL_FLOAT,SQL_INTEGER,SQL_INTERVAL_DAY,SQL_INTERVAL_DAY_TO_HOUR,SQL_INTERVAL_DAY_TO_MINUTE,SQL_INTERVAL_DAY_TO_SECOND,SQL_INTERVAL_HOUR,SQL_INTERVAL_HOUR_TO_MINUTE,SQL_INTERVAL_HOUR_TO_SECOND,SQL_INTERVAL_MINUTE,SQL_INTERVAL_MINUTE_TO_SECOND,SQL_INTERVAL_MONTH,SQL_INTERVAL_SECOND,SQL_INTERVAL_YEAR,SQL_INTERVAL_YEAR_TO_MONTH,SQL_LONGVARBINARY,SQL_LONGVARCHAR,SQL_LONGVARNCHAR,SQL_NCHAR,SQL_NCLOB,SQL_NUMERIC,SQL_NVARCHAR,SQL_REAL,SQL_SMALLINT,SQL_TIME,SQL_TIMESTAMP,SQL_TINYINT,SQL_TSI_DAY,SQL_TSI_FRAC_SECOND,SQL_TSI_HOUR,SQL_TSI_MICROSECOND,SQL_TSI_MINUTE,SQL_TSI_MONTH,SQL_TSI_QUARTER,SQL_TSI_SECOND,SQL_TSI_WEEK,SQL_TSI_YEAR,SQL_VARBINARY,SQL_VARCHAR,SQRT,START,STATE,STATEMENT,STATIC,STDDEV_POP,STDDEV_SAMP,STREAM,STRUCTURE,STYLE,SUBCLASS_ORIGIN,SUBMULTISET,SUBSET,SUBSTITUTE,SUBSTRING,SUBSTRING_REGEX,SUCCEEDS,SUM,SYMMETRIC,SYSTEM,SYSTEM_TIME,SYSTEM_USER,TABLE,TABLESAMPLE,TABLE_NAME,TEMPORARY,THEN,TIES,TIME,TIMESTAMP,TIMESTAMPADD,TIMESTAMPDIFF,TIMEZONE_HOUR,TIMEZONE_MINUTE,TINYINT,TO,TOP_LEVEL_COUNT,TRAILING,TRANSACTION,TRANSACTIONS_ACTIVE,TRANSACTIONS_COMMITTED,TRANSACTIONS_ROLLED_BACK,TRANSFORM,TRANSFORMS,TRANSLATE,TRANSLATE_REGEX,TRANSLATION,TREAT,TRIGGER,TRIGGER_CATALOG,TRIGGER_NAME,TRIGGER_SCHEMA,TRIM,TRIM_ARRAY,TRUE,TRUNCATE,TYPE,UESCAPE,UNBOUNDED,UNCOMMITTED,UNCONDITIONAL,UNDER,UNION,UNIQUE,UNKNOWN,UNNAMED,UNNEST,UPDATE,UPPER,UPSERT,USAGE,USER,USER_DEFINED_TYPE_CATALOG,USER_DEFINED_TYPE_CODE,USER_DEFINED_TYPE_NAME,USER_DEFINED_TYPE_SCHEMA,USING,UTF16,UTF32,UTF8,VALUE,VALUES,VALUE_OF,VARBINARY,VARCHAR,VARYING,VAR_POP,VAR_SAMP,VERSION,VERSIONING,VIEW,WEEK,WHEN,WHENEVER,WHERE,WIDTH_BUCKET,WINDOW,WITH,WITHIN,WITHOUT,WORK,WRAPPER,WRITE,XML,YEAR,ZONE.

Identifiers

Identifiers are the names of tables, columns and other metadataelements used in a SQL query.

Unquoted identifiers, such as emp, must start with a letter and canonly contain letters, digits, and underscores. They are implicitlyconverted to upper case.

Quoted identifiers, such as "Employee Name", start and end withdouble quotes. They may contain virtually any character, includingspaces and other punctuation. If you wish to include a double quotein an identifier, use another double quote to escape it, like this:"An employee called ""Fred"".".

In Calcite, matching identifiers to the name of the referenced object iscase-sensitive. But remember that unquoted identifiers are implicitlyconverted to upper case before matching, and if the object it refersto was created using an unquoted identifier for its name, then itsname will have been converted to upper case also.

Data types

Scalar types

Where:

timeUnit:
  MILLENNIUM | CENTURY | DECADE | YEAR | QUARTER | MONTH | WEEK | DOY | DOW | DAY | HOUR | MINUTE | SECOND | EPOCH

Note:

• DATE, TIME and TIMESTAMP have no time zone. For those types, there is noteven an implicit time zone, such as UTC (as in Java) or the local time zone.It is left to the user or application to supply a time zone. In turn,TIMESTAMP WITH LOCAL TIME ZONE does not store the time zone internally, butit will rely on the supplied time zone to provide correct semantics.
• GEOMETRY is allowed only in certainconformance levels.

Non-scalar types

Note:

• Every ROW column type can have an optional [ NULL | NOT NULL ] suffixto indicate if this column type is nullable, default is not nullable.

Spatial types

Spatial data is represented as character strings encoded aswell-known text (WKT)or binary strings encoded aswell-known binary (WKB).

Where you would use a literal, apply the ST_GeomFromText function,for example ST_GeomFromText('POINT (30 10)').

Operators and functions

Operator precedence

The operator precedence and associativity, highest to lowest.

Note that :: is dialect-specific, but is shown in this table forcompleteness.

Comparison operators

comp:
      =
  |   <>
  |   >
  |   >=
  |   <
  |   <=

Logical operators

Arithmetic operators and functions

Character string operators and functions

Not implemented:

• SUBSTRING(string FROM regexp FOR regexp)

Binary string operators and functions

Date/time functions

Calls to niladic functions such as CURRENT_DATE do not accept parentheses instandard SQL. Calls with parentheses, such as CURRENT_DATE() are accepted in certainconformance levels.

Not implemented:

• CEIL(interval)
• FLOOR(interval)
• • interval
• • interval
• interval + interval
• interval - interval
• interval / interval

System functions

Conditional functions and operators

Type conversion

Generally an expression cannot contain values of different datatypes. For example, an expression cannot multiply 5 by 10 and then add ‘JULIAN’.However, Calcite supports both implicit and explicit conversion of values from one datatype to another.

Implicit and Explicit Type Conversion

Calcite recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

• SQL statements are easier to understand when you use explicit datatype conversion functions.
• Implicit datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.
• Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR value may return an unexpected format.

Algorithms for implicit conversion are subject to change across Calcite releases. Behavior of explicit conversions is more predictable.

Explicit Type Conversion

Supported data types syntax:

type:
      typeName
      [ collectionsTypeName ]*
typeName:
      sqlTypeName
  |   rowTypeName
  |   compoundIdentifier
sqlTypeName:
      char [ precision ] [ charSet ]
  |   varchar [ precision ] [ charSet ]
  |   DATE
  |   time
  |   timestamp
  |   GEOMETRY
  |   decimal [ precision [, scale] ]
  |   BOOLEAN
  |   integer
  |   BINARY [ precision ]
  |   varbinary [ precision ]
  |   TINYINT
  |   SMALLINT
  |   BIGINT
  |   REAL
  |   double
  |   FLOAT
  |   ANY [ precision [, scale] ]
collectionsTypeName:
      ARRAY | MULTISET
rowTypeName:
      ROW '('
      fieldName1 fieldType1 [ NULL | NOT NULL ]
      [ , fieldName2 fieldType2 [ NULL | NOT NULL ] ]*
      ')'
char:
      CHARACTER | CHAR
varchar:
      char VARYING | VARCHAR
decimal:
      DECIMAL | DEC | NUMERIC
integer:
      INTEGER | INT
varbinary:
      BINARY VARYING | VARBINARY
double:
      DOUBLE [ PRECISION ]
time:
      TIME [ precision ] [ timeZone ]
timestamp:
      TIMESTAMP [ precision ] [ timeZone ]
charSet:
      CHARACTER SET charSetName
timeZone:
      WITHOUT TIME ZONE
  |   WITH LOCAL TIME ZONE

Implicit Type Conversion

Calcite automatically converts a value from one datatype to anotherwhen such a conversion makes sense. The table below is a matrix ofCalcite type conversions. The table shows all possible conversions,without regard to the context in which it is made. The rules governingthese details follow the table.

i: implicit cast / e: explicit cast / x: not allowed

Conversion Contexts and Strategies

• Set operation (UNION, EXCEPT, INTERSECT): Compare every branchrow data type and find the common type of each fields pair;
• Binary arithmetic expression (+, -, &, ^, /, %): promotestring operand to data type of the other numeric operand;
• Binary comparison (=, <, <=, <>, >, >=):if operands are STRING and TIMESTAMP, promote to TIMESTAMP;make 1 = true and 0 = false always evaluate to TRUE;if there is numeric type operand, find common type for both operands.
• IN sub-query: compare type of LHS and RHS, and find the common type;if it is struct type, find wider type for every field;
• IN expression list: compare every expression to find the common type;
• CASE WHEN expression or COALESCE: find the common wider type of the THENand ELSE operands;
• Character + INTERVAL or character - INTERVAL: Promote character toTIMESTAMP;
• Built-in function: Look up the type families registered in the checker,find the family default type if checker rules allow it;
• User-defined function (UDF): Coerce based on the declared argument typesof the eval() method.

Strategies for Finding Common Type

• If the operator has expected data types, just take them as thedesired one. (e.g. the UDF would have eval() method which hasreflection argument types);
• If there is no expected data type but the data type families areregistered, try to coerce the arguments to the family’s default datatype, i.e. the String family will have a VARCHAR type;
• If neither expected data type nor families are specified, try tofind the tightest common type of the node types, i.e. INTEGER andDOUBLE will return DOUBLE, the numeric precision does not losefor this case;
• If no tightest common type is found, try to find a wider type,i.e. VARCHAR and INTEGER will return INTEGER,we allow some precision loss when widening decimal to fractional,or promote to VARCHAR type.

Value constructors

Collection functions

See also: the UNNEST relational operator converts a collection to a relation.

Period predicates

Where period1 and period2 are period expressions:

period:
      (datetime, datetime)
  |   (datetime, interval)
  |   PERIOD (datetime, datetime)
  |   PERIOD (datetime, interval)

JDBC function escape

Numeric

String

Not implemented:

• {fn CHAR(string)}

Date/time

System

Conversion

Aggregate functions

Syntax:

aggregateCall:
        agg( [ ALL | DISTINCT ] value [, value ]*)
        [ WITHIN GROUP (ORDER BY orderItem [, orderItem ]*) ]
        [ FILTER (WHERE condition) ]
    |   agg(*) [ FILTER (WHERE condition) ]

where agg is one of the operators in the following table, or a user-definedaggregate function.

If FILTER is present, the aggregate function only considers rows for whichcondition evaluates to TRUE.

If DISTINCT is present, duplicate argument values are eliminated before beingpassed to the aggregate function.

If WITHIN GROUP is present, the aggregate function sorts the input rowsaccording to the ORDER BY clause inside WITHIN GROUP before aggregatingvalues. WITHIN GROUP is only allowed for hypothetical set functions (RANK,DENSE_RANK, PERCENT_RANK and CUME_DIST), inverse distribution functions(PERCENTILE_CONT and PERCENTILE_DISC) and collection functions (COLLECTand LISTAGG).

Not implemented:

• REGR_AVGX(numeric1, numeric2)
• REGR_AVGY(numeric1, numeric2)
• REGR_INTERCEPT(numeric1, numeric2)
• REGR_R2(numeric1, numeric2)
• REGR_SLOPE(numeric1, numeric2)
• REGR_SXY(numeric1, numeric2)

Window functions

Syntax:

windowedAggregateCall:
        agg( [ ALL | DISTINCT ] value [, value ]*)
        [ RESPECT NULLS | IGNORE NULLS ]
        [ WITHIN GROUP (ORDER BY orderItem [, orderItem ]*) ]
        [ FILTER (WHERE condition) ]
        OVER window
    |   agg(*)
        [ FILTER (WHERE condition) ]
        OVER window

where agg is one of the operators in the following table, or a user-definedaggregate function.

DISTINCT, FILTER and WITHIN GROUP are as described for aggregatefunctions.

Note:

• You may specify null treatment (IGNORE NULLS, RESPECT NULLS) forFIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD and LAG functions. Thesyntax handled by the parser, but only RESPECT NULLS is implemented atruntime.

Not implemented:

• COUNT(DISTINCT value [, value ]*) OVER window
• APPROX_COUNT_DISTINCT(value [, value ]*) OVER window
• PERCENT_RANK(value) OVER window
• CUME_DIST(value) OVER window

Grouping functions

Grouped window functions

Grouped window functions occur in the GROUP BY clause and define a key valuethat represents a window containing several rows.

In some window functions, a row may belong to more than one window.For example, if a query is grouped usingHOP(t, INTERVAL '2' HOUR, INTERVAL '1' HOUR), a row with timestamp ‘10:15:00’ will occur in both the 10:00 - 11:00 and 11:00 - 12:00 totals.

Grouped auxiliary functions

Grouped auxiliary functions allow you to access properties of a window definedby a grouped window function.

Spatial functions

In the following:

• geom is a GEOMETRY;
• geomCollection is a GEOMETRYCOLLECTION;
• point is a POINT;
• lineString is a LINESTRING;
• iMatrix is a DE-9IM intersection matrix;
• distance, tolerance, segmentLengthFraction, offsetDistance are of type double;
• dimension, quadSegs, srid, zoom are of type integer;
• layerType is a character string;
• gml is a character string containing Geography Markup Language (GML);
• wkt is a character string containing well-known text (WKT);
• wkb is a binary string containing well-known binary (WKB).

In the “C” (for “compatibility”) column, “o” indicates that the functionimplements the OpenGIS Simple Features Implementation Specification for SQL,version 1.2.1;“p” indicates that the function is aPostGIS extension to OpenGIS.

Geometry conversion functions (2D)

Not implemented:

• ST_AsBinary(geom) GEOMETRY → WKB
• ST_AsGML(geom) GEOMETRY → GML
• ST_Force2D(geom) 3D GEOMETRY → 2D GEOMETRY
• ST_GeomFromGML(gml [, srid ]) GML → GEOMETRY
• ST_GeomFromWKB(wkb [, srid ]) WKB → GEOMETRY
• ST_GoogleMapLink(geom [, layerType [, zoom ]]) GEOMETRY → Google map link
• ST_LineFromWKB(wkb [, srid ]) WKB → LINESTRING
• ST_OSMMapLink(geom [, marker ]) GEOMETRY → OSM map link
• ST_PointFromWKB(wkb [, srid ]) WKB → POINT
• ST_PolyFromWKB(wkb [, srid ]) WKB → POLYGON
• STToMultiLine(geom) Converts the coordinates of _geom (which may be a GEOMETRYCOLLECTION) into a MULTILINESTRING
• STToMultiPoint(geom)) Converts the coordinates of _geom (which may be a GEOMETRYCOLLECTION) into a MULTIPOINT
• STToMultiSegments(geom) Converts _geom (which may be a GEOMETRYCOLLECTION) into a set of distinct segments stored in a MULTILINESTRING

Geometry conversion functions (3D)

Not implemented:

• ST_Force3D(geom) 2D GEOMETRY → 3D GEOMETRY

Geometry creation functions (2D)

Not implemented:

• STBoundingCircle(geom) Returns the minimum bounding circle of _geom
• STExpand(geom, distance) Expands _geom’s envelope
• STExpand(geom, deltaX, deltaY) Expands _geom’s envelope
• ST_MakeEllipse(point, width, height) Constructs an ellipse
• ST_MakeEnvelope(xMin, yMin, xMax, yMax [, srid ]) Creates a rectangular POLYGON
• STMakeGrid(geom, deltaX, deltaY) Calculates a regular grid of POLYGONs based on _geom
• STMakeGridPoints(geom, deltaX, deltaY) Calculates a regular grid of points based on _geom
• STMakePolygon(lineString [, hole ]) Creates a POLYGON from *lineString_ with the given holes (which are required to be closed LINESTRINGs)
• STMinimumDiameter(geom) Returns the minimum diameter of _geom
• STMinimumRectangle(geom) Returns the minimum rectangle enclosing _geom
• STOctogonalEnvelope(geom) Returns the octogonal envelope of _geom
• STRingBuffer(geom, distance, bufferCount [, endCapStyle [, doDifference]]) Returns a MULTIPOLYGON of buffers centered at _geom and of increasing buffer size

Geometry creation functions (3D)

Not implemented:

• ST_Extrude(geom, height [, flag]) Extrudes a GEOMETRY
• STGeometryShadow(geom, point, height) Computes the shadow footprint of _geom
• STGeometryShadow(geom, azimuth, altitude, height [, unify ]) Computes the shadow footprint of _geom

Geometry properties (2D)

Not implemented:

• STCentroid(geom) Returns the centroid of _geom (which may be a GEOMETRYCOLLECTION)
• STCompactnessRatio(polygon) Returns the square root of _polygon’s area divided by the area of the circle with circumference equal to its perimeter
• STCoordDim(geom) Returns the dimension of the coordinates of _geom
• STDimension(geom) Returns the dimension of _geom
• STEndPoint(lineString) Returns the last coordinate of _lineString
• STEnvelope(geom [, srid ]) Returns the envelope of _geom (which may be a GEOMETRYCOLLECTION) as a GEOMETRY
• STExplode(query [, fieldName]) Explodes the GEOMETRYCOLLECTIONs in the _fieldName column of a query into multiple geometries
• STExtent(geom) Returns the minimum bounding box of _geom (which may be a GEOMETRYCOLLECTION)
• STExteriorRing(polygon) Returns the exterior ring of _polygon as a linear-ring
• STGeometryN(geomCollection, n) Returns the _n_th GEOMETRY of _geomCollection
• STInteriorRingN(polygon, n) Returns the _n_th interior ring of _polygon
• STIsClosed(geom) Returns whether _geom is a closed LINESTRING or MULTILINESTRING
• STIsEmpty(geom) Returns whether _geom is empty
• STIsRectangle(geom) Returns whether _geom is a rectangle
• STIsRing(geom) Returns whether _geom is a closed and simple line-string or MULTILINESTRING
• STIsSimple(geom) Returns whether _geom is simple
• STIsValid(geom) Returns whether _geom is valid
• ST_IsValidDetail(geom [, selfTouchValid ]) Returns a valid detail as an array of objects
• STIsValidReason(geom [, selfTouchValid ]) Returns text stating whether _geom is valid, and if not valid, a reason why
• STNPoints(geom) Returns the number of points in _geom
• STNumGeometries(geom) Returns the number of geometries in _geom (1 if it is not a GEOMETRYCOLLECTION)
• ST_NumInteriorRing(geom) Alias for ST_NumInteriorRings
• STNumInteriorRings(geom) Returns the number of interior rings of _geom
• STNumPoints(lineString) Returns the number of points in _lineString
• STPointN(geom, n) Returns the _n_th point of a _lineString
• STPointOnSurface(geom) Returns an interior or boundary point of _geom
• STSRID(geom) Returns SRID value of _geom or 0 if it does not have one
• STStartPoint(lineString) Returns the first coordinate of _lineString
• STXMax(geom) Returns the maximum x-value of _geom
• STXMin(geom) Returns the minimum x-value of _geom
• STYMax(geom) Returns the maximum y-value of _geom
• STYMin(geom) Returns the minimum y-value of _geom

Geometry properties (3D)

Not implemented:

• STZMax(geom) Returns the maximum z-value of _geom
• STZMin(geom) Returns the minimum z-value of _geom

Geometry predicates

Not implemented:

• STCovers(geom1, geom2) Returns whether no point in _geom2 is outside geom1
• STOrderingEquals(geom1, geom2) Returns whether _geom1 equals geom2 and their coordinates and component Geometries are listed in the same order
• STRelate(geom1, geom2) Returns the DE-9IM intersection matrix of _geom1 and geom2
• STRelate(geom1, geom2, iMatrix) Returns whether _geom1 and geom2 are related by the given intersection matrix iMatrix

Geometry operators (2D)

The following functions combine 2D geometries.

See also: the ST_Union aggregate function.

Not implemented:

• STConvexHull(geom) Computes the smallest convex polygon that contains all the points in _geom
• ST_Difference(geom1, geom2) Computes the difference between two geometries
• ST_Intersection(geom1, geom2) Computes the intersection of two geometries
• ST_SymDifference(geom1, geom2) Computes the symmetric difference between two geometries

Affine transformation functions (3D and 2D)

Not implemented:

• STRotate(geom, angle [, origin | x, y]) Rotates a _geom counter-clockwise by angle (in radians) about origin (or the point (x, y))
• STScale(geom, xFactor, yFactor [, zFactor ]) Scales _geom by multiplying the ordinates by the indicated scale factors
• STTranslate(geom, x, y, [, z]) Translates _geom

Geometry editing functions (2D)

The following functions modify 2D geometries.

Not implemented:

• STAddPoint(geom, point [, tolerance ]) Adds _point to geom with a given tolerance (default 0)
• STCollectionExtract(geom, dimension) Filters _geom, returning a multi-geometry of those members with a given dimension (1 = point, 2 = line-string, 3 = polygon)
• STDensify(geom, tolerance) Inserts extra vertices every _tolerance along the line segments of geom
• STFlipCoordinates(geom) Flips the X and Y coordinates of _geom
• STHoles(geom) Returns the holes in _geom (which may be a GEOMETRYCOLLECTION)
• STNormalize(geom) Converts _geom to normal form
• STRemoveDuplicatedCoordinates(geom) Removes duplicated coordinates from _geom
• STRemoveHoles(geom) Removes a _geom’s holes
• STRemovePoints(geom, poly) Removes all coordinates of _geom located within poly; null if all coordinates are removed
• STRemoveRepeatedPoints(geom, tolerance) Removes from _geom all repeated points (or points within tolerance of another point)
• STReverse(geom) Reverses the vertex order of _geom

Geometry editing functions (3D)

The following functions modify 3D geometries.

Not implemented:

• STAddZ(geom, zToAdd) Adds _zToAdd to the z-coordinate of geom
• STInterpolate3DLine(geom) Returns _geom with an interpolation of z values, or null if it is not a line-string or MULTILINESTRING
• STMultiplyZ(geom, zFactor) Returns _geom with its z-values multiplied by zFactor
• STReverse3DLine(geom [, sortOrder ]) Potentially reverses _geom according to the z-values of its first and last coordinates
• STUpdateZ(geom, newZ [, updateCondition ]) Updates the z-values of _geom
• STZUpdateLineExtremities(geom, startZ, endZ [, interpolate ]) Updates the start and end z-values of _geom

Geometry measurement functions (2D)

Not implemented:

• STArea(geom) Returns the area of _geom (which may be a GEOMETRYCOLLECTION)
• STClosestCoordinate(geom, point) Returns the coordinate(s) of _geom closest to point
• STClosestPoint(geom1, geom2) Returns the point of _geom1 closest to geom2
• STFurthestCoordinate(geom, point) Returns the coordinate(s) of _geom that are furthest from point
• STLength(lineString) Returns the length of _lineString
• STLocateAlong(geom, segmentLengthFraction, offsetDistance) Returns a MULTIPOINT containing points along the line segments of _geom at segmentLengthFraction and offsetDistance
• STLongestLine(geom1, geom2) Returns the 2-dimensional longest line-string between the points of _geom1 and geom2
• STMaxDistance(geom1, geom2) Computes the maximum distance between _geom1 and geom2
• STPerimeter(polygon) Returns the length of the perimeter of _polygon (which may be a MULTIPOLYGON)
• STProjectPoint(point, lineString) Projects _point onto a lineString (which may be a MULTILINESTRING)

Geometry measurement functions (3D)

Not implemented:

• ST_3DArea(geom) Return a polygon’s 3D area
• ST_3DLength(geom) Returns the 3D length of a line-string
• ST_3DPerimeter(geom) Returns the 3D perimeter of a polygon or MULTIPOLYGON
• STSunPosition(point [, timestamp ]) Computes the sun position at _point and timestamp (now by default)

Geometry processing functions (2D)

The following functions process geometries.

Not implemented:

• STLineIntersector(geom1, geom2) Splits _geom1 (a line-string) with geom2
• ST_LineMerge(geom) Merges a collection of linear components to form a line-string of maximal length
• STMakeValid(geom [, preserveGeomDim [, preserveDuplicateCoord [, preserveCoordDim]]]) Makes _geom valid
• STPolygonize(geom) Creates a MULTIPOLYGON from edges of _geom
• STPrecisionReducer(geom, n) Reduces _geom’s precision to n decimal places
• ST_RingSideBuffer(geom, distance, bufferCount [, endCapStyle [, doDifference]]) Computes a ring buffer on one side
• ST_SideBuffer(geom, distance [, bufferStyle ]) Compute a single buffer on one side
• STSimplify(geom, distance) Simplifies _geom using the Douglas-Peuker algorithm with a distance tolerance
• STSimplifyPreserveTopology(geom) Simplifies _geom, preserving its topology
• STSnap(geom1, geom2, tolerance) Snaps _geom1 and geom2 together
• STSplit(geom1, geom2 [, tolerance]) Splits _geom1 by geom2 using tolerance (default 1E-6) to determine where the point splits the line

Geometry projection functions

Trigonometry functions

Not implemented:

• STAzimuth(point1, point2) Return the azimuth of the segment from _point1 to point2

Topography functions

Not implemented:

• ST_TriangleAspect(geom) Returns the aspect of a triangle
• ST_TriangleContouring(query [, z1, z2, z3 ][, varArgs]*) Splits triangles into smaller triangles according to classes
• ST_TriangleDirection(geom) Computes the direction of steepest ascent of a triangle and returns it as a line-string
• ST_TriangleSlope(geom) Computes the slope of a triangle as a percentage
• ST_Voronoi(geom [, outDimension [, envelopePolygon ]]) Creates a Voronoi diagram

Triangulation functions

Not implemented:

• STConstrainedDelaunay(geom [, flag [, quality ]]) Computes a constrained Delaunay triangulation based on _geom
• ST_Delaunay(geom [, flag [, quality ]]) Computes a Delaunay triangulation based on points
• STTessellate(polygon) Tessellates _polygon (may be MULTIPOLYGON) with adaptive triangles

Geometry aggregate functions

Not implemented:

• STAccum(geom) Accumulates _geom into a GEOMETRYCOLLECTION (or MULTIPOINT, MULTILINESTRING or MULTIPOLYGON if possible)
• ST_Collect(geom) Alias for ST_Accum
• ST_Union(geom) Computes the union of geometries

JSON Functions

In the following:

• jsonValue is a character string containing a JSON value;
• path is a character string containing a JSON path expression; mode flag strict or lax should be specified in the beginning of path.

Query Functions

Note:

• The ON ERROR and ON EMPTY clauses define the fallbackbehavior of the function when an error is thrown or a null valueis about to be returned.
• The ARRAY WRAPPER clause defines how to represent a JSON array resultin JSON_QUERY function. The following examples compare the wrapperbehaviors.

Example Data:

{"a": "[1,2]", "b": [1,2], "c": "hi"}

Comparison:

Not implemented:

• JSON_TABLE

Constructor Functions

Note:

• The flag FORMAT JSON indicates the value is formatted as JSONcharacter string. When FORMAT JSON is used, the value should bede-parse from JSON character string to a SQL structured value.
• ON NULL clause defines how the JSON output represents nullvalues. The default null behavior of JSON_OBJECT andJSON_OBJECTAGG is NULL ON NULL, and for JSON_ARRAY andJSON_ARRAYAGG it is ABSENT ON NULL.
• If ORDER BY clause is provided, JSON_ARRAYAGG sorts theinput rows into the specified order before performing aggregation.

Comparison Operators

Dialect-specific Operators

The following operators are not in the SQL standard, and are not enabled inCalcite’s default operator table. They are only available for use in queriesif your session has enabled an extra operator table.

To enable an operator table, set thefunconnect string parameter.

The ‘C’ (compatibility) column contains value‘m’ for MySQL (‘fun=mysql’ in the connect string),‘o’ for Oracle (‘fun=oracle’ in the connect string),‘p’ for PostgreSQL (‘fun=postgresql’ in the connect string).

One operator name may correspond to multiple SQL dialects, but with differentsemantics.

Note:

• JSON_TYPE / JSON_DEPTH / JSON_PRETTY / JSON_STORAGE_SIZE return null if the argument is null
• JSON_LENGTH / JSON_KEYS / JSON_REMOVE return null if the first argument is null
• JSON_TYPE generally returns an upper-case string flag indicating the type of the JSON input. Currently supported supported type flags are:
  • INTEGER
  • STRING
  • FLOAT
  • DOUBLE
  • LONG
  • BOOLEAN
  • DATE
  • OBJECT
  • ARRAY
  • NULL
• JSON_DEPTH defines a JSON value’s depth as follows:
  • An empty array, empty object, or scalar value has depth 1;
  • A non-empty array containing only elements of depth 1 or non-empty object containing only member values of depth 1 has depth 2;
  • Otherwise, a JSON document has depth greater than 2.
• JSON_LENGTH defines a JSON value’s length as follows:
  • A scalar value has length 1;
  • The length of array or object is the number of elements is contains.

Usage Examples:

JSON_TYPE example

SQL

SELECT JSON_TYPE(v) AS c1,
  JSON_TYPE(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2,
  JSON_TYPE(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3,
  JSON_TYPE(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4
FROM (VALUES ('{"a": [10, true],"b": "[10, true]"}')) AS t(v)
LIMIT 10;

Result

JSON_DEPTH example

SQL

SELECT JSON_DEPTH(v) AS c1,
  JSON_DEPTH(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2,
  JSON_DEPTH(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3,
  JSON_DEPTH(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4
FROM (VALUES ('{"a": [10, true],"b": "[10, true]"}')) AS t(v)
LIMIT 10;

Result

JSON_LENGTH example

SQL

SELECT JSON_LENGTH(v) AS c1,
  JSON_LENGTH(v, 'lax $.a') AS c2,
  JSON_LENGTH(v, 'strict $.a[0]') AS c3,
  JSON_LENGTH(v, 'strict $.a[1]') AS c4
FROM (VALUES ('{"a": [10, true]}')) AS t(v)
LIMIT 10;

Result

JSON_KEYS example

SQL

SELECT JSON_KEYS(v) AS c1,
  JSON_KEYS(v, 'lax $.a') AS c2,
  JSON_KEYS(v, 'lax $.b') AS c2,
  JSON_KEYS(v, 'strict $.a[0]') AS c3,
  JSON_KEYS(v, 'strict $.a[1]') AS c4
FROM (VALUES ('{"a": [10, true],"b": {"c": 30}}')) AS t(v)
LIMIT 10;

Result

JSON_REMOVE example

SQL

SELECT JSON_REMOVE(v, '$[1]') AS c1
FROM (VALUES ('["a", ["b", "c"], "d"]')) AS t(v)
LIMIT 10;

Result

JSON_STORAGE_SIZE example

SQL

SELECT
JSON_STORAGE_SIZE('[100, \"sakila\", [1, 3, 5], 425.05]') AS c1,
JSON_STORAGE_SIZE('{\"a\": 10, \"b\": \"a\", \"c\": \"[1, 3, 5, 7]\"}') AS c2,
JSON_STORAGE_SIZE('{\"a\": 10, \"b\": \"xyz\", \"c\": \"[1, 3, 5, 7]\"}') AS c3,
JSON_STORAGE_SIZE('[100, \"json\", [[10, 20, 30], 3, 5], 425.05]') AS c4
limit 10;

Result

DECODE example

SQL

SELECT DECODE(f1, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c1,
  DECODE(f2, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c2,
  DECODE(f3, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c3,
  DECODE(f4, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c4,
  DECODE(f5, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c5
FROM (VALUES (1, 2, 3, 4, 5)) AS t(f1, f2, f3, f4, f5);

Result

TRANSLATE example

SQL

SELECT TRANSLATE('Aa*Bb*Cc''D*d', ' */''%', '_') as c1,
  TRANSLATE('Aa/Bb/Cc''D/d', ' */''%', '_') as c2,
  TRANSLATE('Aa Bb Cc''D d', ' */''%', '_') as c3,
  TRANSLATE('Aa%Bb%Cc''D%d', ' */''%', '_') as c4
FROM (VALUES (true)) AS t(f0);

Result

Not implemented:

• JSON_INSERT
• JSON_SET
• JSON_REPLACE

User-defined functions

Calcite is extensible. You can define each kind of function using user code.For each kind of function there are often several ways to define a function,varying from convenient to efficient.

To implement a scalar function, there are 3 options:

• Create a class with a public static eval method,and register the class;
• Create a class with a public non-static eval method,and a public constructor with no arguments,and register the class;
• Create a class with one or more public static methods,and register each class/method combination.

To implement an aggregate function, there are 2 options:

• Create a class with public static init, add and result methods,and register the class;
• Create a class with public non-static init, add and result methods,and a public constructor with no arguments,and register the class.

Optionally, add a public merge method to the class; this allows Calcite togenerate code that merges sub-totals.

Optionally, make your class implement theSqlSplittableAggFunctioninterface; this allows Calcite to decompose the function across several stagesof aggregation, roll up from summary tables, and push it through joins.

To implement a table function, there are 3 options:

• Create a class with a static eval method that returnsScannableTableorQueryableTable,and register the class;
• Create a class with a non-static eval method that returnsScannableTableorQueryableTable,and register the class;
• Create a class with one or more public static methods that returnScannableTableorQueryableTable,and register each class/method combination.

To implement a table macro, there are 3 options:

• Create a class with a static eval method that returnsTranslatableTable,and register the class;
• Create a class with a non-static eval method that returnsTranslatableTable,and register the class;
• Create a class with one or more public static methods that returnTranslatableTable,and register each class/method combination.

Calcite deduces the parameter types and result type of a function from theparameter and return types of the Java method that implements it. Further, youcan specify the name and optionality of each parameter using theParameterannotation.

Calling functions with named and optional parameters

Usually when you call a function, you need to specify all of its parameters,in order. But that can be a problem if a function has a lot of parameters,and especially if you want to add more parameters over time.

To solve this problem, the SQL standard allows you to pass parameters by name,and to define parameters which are optional (that is, have a default valuethat is used if they are not specified).

Suppose you have a function f, declared as in the following pseudo syntax:

FUNCTION f(
  INTEGER a,
  INTEGER b DEFAULT NULL,
  INTEGER c,
  INTEGER d DEFAULT NULL,
  INTEGER e DEFAULT NULL) RETURNS INTEGER

All of the function’s parameters have names, and parameters b, d and ehave a default value of NULL and are therefore optional.(In Calcite, NULL is the only allowable default value for optional parameters;this may changein future.)

When calling a function with optional parameters,you can omit optional arguments at the end of the list, or use the DEFAULTkeyword for any optional arguments.Here are some examples:

• f(1, 2, 3, 4, 5) provides a value to each parameter, in order;
• f(1, 2, 3, 4) omits e, which gets its default value, NULL;
• f(1, DEFAULT, 3) omits d and e,and specifies to use the default value of b;
• f(1, DEFAULT, 3, DEFAULT, DEFAULT) has the same effect as the previousexample;
• f(1, 2) is not legal, because c is not optional;
• f(1, 2, DEFAULT, 4) is not legal, because c is not optional.

You can specify arguments by name using the => syntax.If one argument is named, they all must be.Arguments may be in any other, but must not specify any argument more than once,and you need to provide a value for every parameter which is not optional.Here are some examples:

• f(c => 3, d => 1, a => 0) is equivalent to f(0, NULL, 3, 1, NULL);
• f(c => 3, d => 1) is not legal, because you have not specified a value fora and a is not optional.

MATCH_RECOGNIZE

MATCH_RECOGNIZE is a SQL extension for recognizing sequences ofevents in complex event processing (CEP).

It is experimental in Calcite, and yet not fully implemented.

Syntax

matchRecognize:
      MATCH_RECOGNIZE '('
      [ PARTITION BY expression [, expression ]* ]
      [ ORDER BY orderItem [, orderItem ]* ]
      [ MEASURES measureColumn [, measureColumn ]* ]
      [ ONE ROW PER MATCH | ALL ROWS PER MATCH ]
      [ AFTER MATCH
            ( SKIP TO NEXT ROW
            | SKIP PAST LAST ROW
            | SKIP TO FIRST variable
            | SKIP TO LAST variable
            | SKIP TO variable )
      ]
      PATTERN '(' pattern ')'
      [ WITHIN intervalLiteral ]
      [ SUBSET subsetItem [, subsetItem ]* ]
      DEFINE variable AS condition [, variable AS condition ]*
      ')'
subsetItem:
      variable = '(' variable [, variable ]* ')'
measureColumn:
      expression AS alias
pattern:
      patternTerm [ '|' patternTerm ]*
patternTerm:
      patternFactor [ patternFactor ]*
patternFactor:
      patternPrimary [ patternQuantifier ]
patternPrimary:
      variable
  |   '$'
  |   '^'
  |   '(' [ pattern ] ')'
  |   '{-' pattern '-}'
  |   PERMUTE '(' pattern [, pattern ]* ')'
patternQuantifier:
      '*'
  |   '*?'
  |   '+'
  |   '+?'
  |   '?'
  |   '??'
  |   '{' { [ minRepeat ], [ maxRepeat ] } '}' ['?']
  |   '{' repeat '}'
intervalLiteral:
      INTERVAL 'string' timeUnit [ TO timeUnit ]

In patternQuantifier, repeat is a positive integer,and minRepeat and maxRepeat are non-negative integers.

DDL Extensions

DDL extensions are only available in the calcite-server module.To enable, include calcite-server.jar in your class path, and addparserFactory=org.apache.calcite.sql.parser.ddl.SqlDdlParserImpl#FACTORYto the JDBC connect string (see connect string propertyparserFactory).

ddlStatement:
      createSchemaStatement
  |   createForeignSchemaStatement
  |   createTableStatement
  |   createViewStatement
  |   createMaterializedViewStatement
  |   createTypeStatement
  |   createFunctionStatement
  |   dropSchemaStatement
  |   dropForeignSchemaStatement
  |   dropTableStatement
  |   dropViewStatement
  |   dropMaterializedViewStatement
  |   dropTypeStatement
  |   dropFunctionStatement
createSchemaStatement:
      CREATE [ OR REPLACE ] SCHEMA [ IF NOT EXISTS ] name
createForeignSchemaStatement:
      CREATE [ OR REPLACE ] FOREIGN SCHEMA [ IF NOT EXISTS ] name
      (
          TYPE 'type'
      |   LIBRARY 'com.example.calcite.ExampleSchemaFactory'
      )
      [ OPTIONS '(' option [, option ]* ')' ]
option:
      name literal
createTableStatement:
      CREATE TABLE [ IF NOT EXISTS ] name
      [ '(' tableElement [, tableElement ]* ')' ]
      [ AS query ]
createTypeStatement:
      CREATE [ OR REPLACE ] TYPE name AS
      {
          baseType
      |   '(' attributeDef [, attributeDef ]* ')'
      }
attributeDef:
      attributeName type
      [ COLLATE collation ]
      [ NULL | NOT NULL ]
      [ DEFAULT expression ]
tableElement:
      columnName type [ columnGenerator ] [ columnConstraint ]
  |   columnName
  |   tableConstraint
columnGenerator:
      DEFAULT expression
  |   [ GENERATED ALWAYS ] AS '(' expression ')'
      { VIRTUAL | STORED }
columnConstraint:
      [ CONSTRAINT name ]
      [ NOT ] NULL
tableConstraint:
      [ CONSTRAINT name ]
      {
          CHECK '(' expression ')'
      |   PRIMARY KEY '(' columnName [, columnName ]* ')'
      |   UNIQUE '(' columnName [, columnName ]* ')'
      }
createViewStatement:
      CREATE [ OR REPLACE ] VIEW name
      [ '(' columnName [, columnName ]* ')' ]
      AS query
createMaterializedViewStatement:
      CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] name
      [ '(' columnName [, columnName ]* ')' ]
      AS query
createFunctionStatement:
      CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] name
      AS classNameLiteral
      [ USING  usingFile [, usingFile ]* ]
usingFile:
      ( JAR | FILE | ARCHIVE ) filePathLiteral
dropSchemaStatement:
      DROP SCHEMA [ IF EXISTS ] name
dropForeignSchemaStatement:
      DROP FOREIGN SCHEMA [ IF EXISTS ] name
dropTableStatement:
      DROP TABLE [ IF EXISTS ] name
dropViewStatement:
      DROP VIEW [ IF EXISTS ] name
dropMaterializedViewStatement:
      DROP MATERIALIZED VIEW [ IF EXISTS ] name
dropTypeStatement:
      DROP TYPE [ IF EXISTS ] name
dropFunctionStatement:
      DROP FUNCTION [ IF EXISTS ] name

In createTableStatement, if you specify AS query, you may omit the list oftableElement_s, or you can omit the data type of any _tableElement, in whichcase it just renames the underlying column.

In columnGenerator, if you do not specify VIRTUAL or STORED for agenerated column, VIRTUAL is the default.

In createFunctionStatement and usingFile, classNameLiteral_and _filePathLiteral are character literals.