Skip to end of metadata
Go to start of metadata

This page describes the list of ClustrixDB support for built in SQL functions and operators.

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
  • 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
  • PERCENT_RANK
  • 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.
  • RANK
  • 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.
  • ROW_NUMBER
  • 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

Unsupported Functions

ClustrixDB does not support the following functions:

  • ANALYSE()
  • BENCHMARK() 

  • CHARSET() 

  • COERCIBILITY() 

  • COLLATION() 

  • CURRENT_USER() 

  • EXTRACT() 

  • GET_FORMAT() 

  • INET_ATON(), INET_NTOA(), INET6_ATON(), INET6_NTOA()

  • IS_IPV4IS_IPV4_COMPAT ()IS_IPV4_MAPPED ()IS_IPV6 ()

  • LOCK TABLES()

  • LOAD_FILE() 
  • MAKEDATE() 

  • SESSION_USER()
  • TO_SECONDS()
  • No labels