Xpand (fomerly clustrixdb) supported functions

Supported Functions and Operators

ClustrixDB supports the following functions and operators:

• :=
• !
• !=
• *
• /
• &
• &&
• %
• ^
• +
• <
• <<
• <=
• <=>
• =
• =
• >
• >=
• >>
• |
• ||
• ~
• ABS
• ACOS
• ADDDATE
• ADDTIME
• AES_DECRYPT
• AES_ENCRYPT
• ASCII
• ASIN
• ATAN
• ATAN2
• AVG
• BETWEEN AND
  • If one of the values being compared for BETWEEN is NULL, ClustrixDB will return NULL 
• BIN
• BINARY OPERATOR
• BIT_AND
• BIT_COUNT
• BIT_LENGTH
• BIT_OR
• BIT_XOR
• CASE
• CAST
• CEIL
• CEILING
• CHAR Function
• CHAR_LENGTH
• CHARACTER_LENGTH
• CHARSET
• COALESCE
• COMPRESS
• CONCAT
• CONCAT_WS
• CONNECTION_ID
• CONTAINS
• CONV
• CONVERT
• CONVERT_TZ
• COS
• COT
• COUNT
  • ClustrixDB allows multiple inputs to the COUNT() function, where some versions of MySQL and MariaDB do not 
  • ClustrixDB will include trailing NULL values in the results of COUNT(), while MySQL does not
    
• COUNT DISTINCT
• CRC32
• CURDATE
• CURRENT_DATE
• CURRENT_ROLE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• CURRENT_USER
• CURTIME
• DATABASE
• DATE FUNCTION
• DATE_ADD
• DATE_FORMAT
• DATE_SUB
• DATEDIFF
• DAY
• DAYNAME
• DAYOFMONTH
• DAYOFWEEK
• DAYOFYEAR
• DECODE
• DIV
• ELT
• ENCODE
• ENCRYPT
• EQUALS
• EXP
• EXPORT_SET
• FIELD
• FIND_IN_SET
• FLOOR
• FORMAT
• FOUND_ROWS
• FROM_BASE64
• FROM_DAYS
• FROM_UNIXTIME
• GET_FORMAT
• GET_LOCK
  • Use of GET_LOCK with Statement-based Replication is considered unsafe, though ClustrixDB does not raise an error. MySQL raises an error. 
• GREATEST
• GROUP_CONCAT
  • Multiple GROUP_CONCAT statements are supported, but only one ORDER BY is allowed per select statement
• HEX
• HOUR
• IF
• IFNULL
• IN
• INSERT Function
• INSTR
• INTERVAL
• IS
• IS NOT
• IS NOT NULL
• IS NULL
• IS_FREE_LOCK
• IS_USED_LOCK
• ISNULL
• JSON_ARRAY - See the section on JSON for more information on this and other JSON functions 
• JSON_CONTAINS_PATH
• JSON_DEPTH
• JSON_EXTRACT
• JSON_KEYS
• JSON_LENGTH
• JSON_OBJECT
• JSON_QUOTE
• JSON_SEARCH
• JSON_TYPE
• JSON_UNQUOTE
• JSON_VALID
• LAST_DAY
• LAST_INSERT_ID
• LAST_VALUE
• LCASE
• LEAST
• LEFT
• LENGTH
• LIKE
• LINESTRING
• LN
• LOCALTIME
• LOCALTIMESTAMP
• LOCATE
  • In ClustrixDB, LOCATE() returns NULL when position is provided as NULL. MySQL returns 0 in such instances.
• LOG
• LOG10
• LOG2
• LOWER
• LPAD
• LTRIM
• MAKE_SET
• MAKEDATE
• MAKETIME
• MAX
• MD5
• MICROSECOND
• MID
• MIN
• MINUTE
• MOD
• MONTH
• MONTHNAME
• NAME_CONST
• NEXTVAL
• NOT BETWEEN
• NOT IN
• NOT LIKE
• NOT REGEXP
• NOW
• NULLIF
• OCT
• ORD
• PASSWORD
• PERIOD_ADD
  • The results of this function do not match MySQL when year boundaries are being crossed or approached.
• PERIOD_DIFF
• PI
• POW
• POWER
• QUARTER
• QUOTE
• RADIANS
• RAND
  • Does not accept an argument as seed.
• REGEXP
• RELEASE_LOCK
• REPEAT Function
• REPLACE Function
• REVERSE
• RIGHT
• RLIKE
• ROUND
• ROW_COUNT
  • When updating a row, if the values are not being changed (e.g. (1,1) → (1,1)) mysql reports 0, ClustrixDB returns 1
  • When replacing a row with an identical row, mysql reports 1, ClustrixDB returns 2
  • On ClustrixDB, ROW_COUNT does not reflect the number of rows affected by a stored procedure 
  • ROW_COUNT does not match mysql behavior for DDL (e.g. CREATE TABLE then  SELECT ROW_COUNT)
  • When committing an explicit transaction, ClustrixDB reports the ROW_COUNT() value for the most recent statement before the COMMIT. In other words, row_count behaves as if there was no COMMIT.
    
• RPAD
• RTRIM
• SCHEMA
• SEC_TO_TIME
• SECOND
• SESSION_USER
• SHA
• SHA1
• SHA2
• SIGN
• SIN
• SLEEP
• SQRT
• STD
• STDDEV
• STDDEV_POP
• STDDEV_SAMP
• STR_TO_DATE
• STRCMP
• SUBDATE
• SUBSTR
• SUBSTRING
• SUBSTRING_INDEX
• SUBTIME
• SUM
• SYSDATE
  • Does not reflect time zone settings; always returns UTC.
• SYSTEM_USER
• TAN
• TIME function
• TIME_FORMAT
• TIME_TO_SEC
• TIMEDIFF
• TIMESTAMP FUNCTION
• TIMESTAMPADD
• TIMESTAMPDIFF
• TO_BASE64
• TO_DAYS
• TO_SECONDS
• TRIM
• TRUNCATE
• UCASE
• UNCOMPRESS
• UNCOMPRESSED_LENGTH
• UNHEX
• UNIX_TIMESTAMP
• UPPER
• USER
• UTC_DATE
• UTC_TIME
• UTC_TIMESTAMP
• UUID
• UUID_SHORT
• VALUES or VALUE
• VAR_POP
• VAR_SAMP
• VARIANCE
• VERSION
• WEEK
• WEEKDAY
• WEEKOFYEAR
• XOR
• YEAR
• YEARWEEK