Built-in Functions for MariaDB Xpand

Supported Functions

Xpand supports the following functions:

• ABS

• ACOS

• ADDDATE

• ADDTIME

• AES_DECRYPT

• AES_ENCRYPT

• ASCII

• ASIN

• ATAN

• ATAN2

• AVG

• BIN

• 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_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 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

• 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

For a full list of built-in functions supported by Xpand, see "Functions for MariaDB Xpand".

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_IPV4(), IS_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()