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

Supported Functions and Operators

Xpand 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, Xpand 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
    • Xpand allows multiple inputs to the COUNT() function, where some versions of MySQL and MariaDB do not 

    • Xpand 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 Xpand 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 Xpand, 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, Xpand returns 1
    • When replacing a row with an identical row, mysql reports 1, Xpand returns 2
    • On Xpand, 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, Xpand 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

Unsupported Functions

Xpand does not support the following functions:

  • ANALYSE()
  • AREA()
  • AsBinary()
  • AsText()
  • AsWKB()
  • AsWKT()
  • BENCHMARK() 

  • BINLOG_GTID_POS()

  • BOUNDARY()

  • BUFFER()

  • CENTROID()

  • CHARSET() 

  • CHR()
  • COERCIBILITY() 

  • COLLATION() 

  • COLUMN_ADD()

  • COLUMN_CHECK()

  • COLUMN_CREATE()

  • COLUMN_DELETE()

  • COLUMN_EXISTS()

  • COLUMN_GET()

  • COLUMN_JSON()

  • COLUMN_LIST()

  • CONVEXHULL()

  • CROSSES()

  • CUME_DIST()

  • CURRENT_USER() 

  • DECODE_HISTOGRAM()

  • DEFAULT()

  • DEGREES()

  • DENSE_RANK()

  • DES_DECRYPT()

  • DES_ENCRYPT()

  • DIMENSION()

  • DISJOINT() 

  • ENDPOINT()

  • ENVELOPE()

  • ExteriorRing()

  • EXTRACT() 

  • EXTRACTVALUE()

  • GeomCollFromText()

  • GeomCollFromWKB()

  • GEOMETRYCOLLECTION()

  • GeometryCollectionFromText()

  • GeometryCollectionFromWKB()

  • GeometryFromText()

  • GeometryFromWKB()

  • GeometryN()

  • GeometryType()

  • GeomFromText()

  • GeomFromWKB()

  • GLENGTH()

  • GET_FORMAT() 

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

  • InteriorRingN() 

  • INTERSECTS()

  • IS_IPV4IS_IPV4_COMPAT ()IS_IPV4_MAPPED ()IS_IPV6 ()

  • IsClosed()

  • IsEmpty()

  • IsRing()

  • IsSimple() 

  • JSON_ARRAY_APPEND()

  • JSON_ARRAY_INSERT()

  • JSON_COMPACT()

  • JSON_CONTAINS()

  • JSON_DETAILED()

  • JSON_EXISTS()

  • JSON_INSERT()

  • JSON_LOOSE()

  • JSON_MERGE()

  • JSON_QUERY()

  • JSON_REMOVE()

  • JSON_REPLACE()

  • JSON_SET()

  • JSON_VALUE()

  • LAST_VAL() 
  • LineFromText()
  • LineFromWKB()
  • LineStringFromText()
  • LineStringFromWKB() 
  • LOAD_FILE()
  • LOCK TABLES()

  • LOAD_FILE() 
  • MAKEDATE() 

  • MASTER_GTID_WAIT()

  • MASTER_POS_WAIT()

  • MATCH AGAINST()

  • MBRContains()

  • MBRDisjoint()

  • MBREqual()

  • MBRIntersects()

  • MBROverlaps()

  • MBRTouches()

  • MBRWithin()

  • MEDIAN()

  • MLineFromText()

  • MLineFromWKB()

  • MPointFromText()

  • MPointFromWKB()

  • MPolyFromText()

  • MPolyFromWKB()

  • MULTILINESTRING()

  • MultiLineStringFromText()

  • MultiLineStringFromWKB()

  • MULTIPOINT()

  • MultiPointFromText()

  • MultiPointFromWKB()

  • MULTIPOLYGON()

  • MultiPolygonFromText()

  • MultiPolygonFromWKB()

  • NTILE()

  • NumGeometries()

  • NumInteriorRings()

  • NumPoints()

  • OCTET_LENGTH()

  • OLD_PASSWORD()

  • OVERLAPS()

  • PERCENT_RANK 
  • PERCENTILE_CONT()
  • PERCENTILE_DISC()
  • POINT()
  • PointFromText()
  • PointFromWKB()
  • PointN()
  • PointOnSurface()
  • PolyFromText()
  • PolyFromWKB()
  • POLYGON()
  • PolygonFromText()
  • PolygonFromWKB()
  • POSITION()
  • RANK() 
  • REGEXP_INSTR()
  • REGEXP_REPLACE()
  • REGEXP_SUBSTR()
  • ROW_NUMBER()
  • SESSION_USER()
  • SETVAL()
  • SOUNDEX()
  • SOUNDS LIKE()
  • SPACE()
  • SPIDER_BG_DIRECT_SQL()
  • SPIDER_COPY_TABLES()
  • SPIDER_DIRECT_SQL()
  • SPIDER_FLUSH_TABLE_MON_CACHE()
  • SRID()
  • ST_AREA()
  • ST_AsBinary()
  • ST_AsText()
  • ST_AsWKB()
  • ST_ASWKT()
  • ST_BOUNDARY()
  • ST_BUFFER()
  • ST_CENTROID()
  • ST_CONTAINS()
  • ST_CONVEXHULL()
  • ST_CROSSES()
  • ST_DIFFERENCE()
  • ST_DIMENSION()
  • ST_DISJOINT()
  • ST_DISTANCE()
  • ST_ENDPOINT()
  • ST_ENVELOPE()
  • ST_EQUALS()
  • ST_ExteriorRing()
  • ST_GeomCollFromText()
  • ST_GeomCollFromWKB()
  • ST_GeometryCollectionFromText()
  • ST_GeometryCollectionFromWKB()
  • ST_GeometryFromText()
  • ST_GeometryFromWKB()
  • ST_GEOMETRYN()
  • ST_GEOMETRYTYPE()
  • ST_GeomFromText()
  • ST_GeomFromWKB()
  • ST_InteriorRingN()
  • ST_INTERSECTION()
  • ST_INTERSECTS()
  • ST_ISCLOSED()
  • ST_ISEMPTY()
  • ST_IsRing()
  • ST_IsSimple()
  • ST_LENGTH()
  • ST_LineFromText()
  • ST_LineFromWKB()
  • ST_LineStringFromText()
  • ST_LineStringFromWKB()
  • ST_NUMGEOMETRIES()
  • ST_NumInteriorRings()
  • ST_NUMPOINTS()
  • ST_OVERLAPS()
  • ST_PointFromText()
  • ST_PointFromWKB()
  • ST_POINTN()
  • ST_POINTONSURFACE()
  • ST_PolyFromText()
  • ST_PolyFromWKB()
  • ST_PolygonFromText()
  • ST_PolygonFromWKB()
  • ST_RELATE()
  • ST_SRID()
  • ST_STARTPOINT()
  • ST_SYMDIFFERENCE()
  • ST_TOUCHES()
  • ST_UNION()
  • ST_WITHIN()
  • ST_X()
  • ST_Y()
  • STARTPOINT()
  • TO_SECONDS() 
  • TOUCHES()
  • UPDATEXML()
  • WEIGHT_STRING()
  • WITHIN()
  • X()
  • Y()
  • No labels