Edit

SQL Function Comparison

As Ignite 3 uses the Calcite engine, a number of functions have changed. The majority of functions can be migrated without additional actions, however for some functions additional actions are required.

Changed Functions

The following functions have been changed in Ignite 3. They can be used with minor changes.

Ignite 2	Ignite 3	Comment
DAY_OF_MONTH	DAYOFMONTH	This function was renamed.
DAY_OF_WEEK	DAYOFWEEK	This function was renamed.
DAY_OF_YEAR	DAYOFYEAR	This function was renamed.
IS_JSON value	IS JSON [VALUE]	Function syntax was changed, the behavior is unchanged.
ISNULL	NVL	This function was renamed.
INSTR(a, b)	POSITION (b IN a)	Function name and syntax was changed, the behavior is unchanged.
RANDOM_UUID	RAND_UUID	This function was renamed.

Ignite 2

Ignite 3

Comment

DAY_OF_MONTH

DAYOFMONTH

This function was renamed.

DAY_OF_WEEK

DAYOFWEEK

This function was renamed.

DAY_OF_YEAR

DAYOFYEAR

This function was renamed.

IS_JSON value

IS JSON [VALUE]

Function syntax was changed, the behavior is unchanged.

ISNULL

NVL

This function was renamed.

INSTR(a, b)

POSITION (b IN a)

Function name and syntax was changed, the behavior is unchanged.

RANDOM_UUID

RAND_UUID

This function was renamed.

Changed Data Types

Ignite 2 Ignite 3 Comment

Ignite 2	Ignite 3	Comment
BOOLEAN	BOOLEAN	Only `true` and `false` values are supported.

BOOLEAN

Only true and false values are supported.

Removed Functions

The following functions were removed in Ignite 3. Follow the recommendations below to adapt your code.

Ignite 2 Comment

Ignite 2	Comment
BITAND	No equivalent is currently available.
BITGET	No equivalent is currently available.
BITOR	No equivalent is currently available.
BITXOR	No equivalent is currently available.
BIT_AND	No equivalent is currently available.
BIT_OR	No equivalent is currently available.
COMPRESS	No equivalent is currently available.
CONVERT	No equivalent is currently available.
CURRENT_TIME	No equivalent is currently available.
DECRYPT	No equivalent is currently available.
ENCRYPT	No equivalent is currently available.
EXPAND	No equivalent is currently available.
FIRSTVALUE	No equivalent is currently available.
FORMATDATETIME	Use `CAST (datetimeval AS VARCHAR FORMAT 'format-string')` instead.
GROUP_CONCAT	No equivalent is currently available.
JSON_MODIFY	No equivalent is currently available.
LASTVALUE	No equivalent is currently available.
LOG	No equivalent is currently available.
PARSEDATETIME	Use `CAST (string AS datetime-type FORMAT 'format-string')`
ROUNDMAGIC	No equivalent is currently available.
SECURE_RAND	No equivalent is currently available.
STDDEV_POP	No equivalent is currently available.
STDDEV_SAMP	No equivalent is currently available.
VAR_POP	No equivalent is currently available.
VAR_SAMP	No equivalent is currently available.
ZERO	No equivalent is currently available.

BITAND