Built-in Functions for MariaDB Xpand

Overview

This page describes Xpand's support for SQL functions.

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

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()