8. SQL Functions

This chapter describes all of MIRAJ's built-in SQL functions: their signature, their precise behavior (derived from the engine code), their return type, their behavior with NULL, and a sample call.

8.0 General notes#

• Case-insensitive names. CONCAT, Concat and concat designate the same function. This chapter writes them in uppercase by convention.
• NULL propagation. Unless explicitly stated otherwise in a function's description, any NULL argument makes the result NULL. The notable exceptions are COALESCE, IFNULL/NVL, CONCAT_WS, IF, ISNULL, ELT, FIELD, MAKE_SET, QUOTE, SFORMAT (for the arguments other than the format) and aggregates (which ignore the NULLs of their argument rather than propagating NULL to the whole row).
• String positions and lengths are counted in Unicode characters (not bytes), except LENGTH and BIT_LENGTH, which count UTF-8 bytes. ASCII returns the leading byte of the UTF-8 encoding of the first character; ORD combines the UTF-8 bytes of the first character in big-endian order (ORD('é') = 50089, whereas é has Unicode code point 233).
• CASE WHEN is not a function but a construct of the SQL language (see chapter 7. SELECT Queries); its result typing behavior follows the same rules as COALESCE/IF described in 8.4.
• The exact number of functions described in this chapter far exceeds the number announced elsewhere in the product documentation (152): that historical count appears to cover only a subset of the families below (for example, without the network, vector or named-lock functions). This chapter documents the entire scalar function registry of the engine, plus the aggregate functions.

Table of contents#

• 8.1 String functions
  • CONCAT · CONCAT_WS · UPPER / UCASE · LOWER / LCASE · LENGTH / OCTET_LENGTH · BIT_LENGTH · CHAR_LENGTH / CHARACTER_LENGTH · TRIM · LTRIM · RTRIM · SUBSTRING / SUBSTR / MID · LEFT · RIGHT · REPLACE · LOCATE · INSTR · LPAD · RPAD · REVERSE · REPEAT · SPACE · ASCII · ORD · CHAR · STRCMP · FORMAT · INSERT · ELT · FIELD · FIND_IN_SET · SUBSTRING_INDEX · QUOTE · BIN · OCT · CONV · MAKE_SET · EXPORT_SET · SOUNDEX · NATURAL_SORT_KEY · WEIGHT_STRING · LOAD_FILE · CONVERT (character set)
• 8.2 Numeric functions
  • ABS · SIGN · ROUND · TRUNCATE · FLOOR · CEILING / CEIL · MOD · POWER / POW · SQRT · EXP · LN · LOG · LOG10 · LOG2 · PI · RAND · SIN, COS, TAN · COT · ASIN · ACOS · ATAN / ATAN2 · DEGREES · RADIANS · GREATEST · LEAST · CRC32 / CRC32C
• 8.3 Date and time functions
  • NOW / CURRENT_TIMESTAMP / LOCALTIME / LOCALTIMESTAMP · SYSDATE · CURDATE / CURRENT_DATE · CURTIME / CURRENT_TIME · UTC_TIMESTAMP, UTC_DATE, UTC_TIME · DATE · TIME · TIMESTAMP · YEAR · MONTH · DAY / DAYOFMONTH · HOUR · MINUTE · SECOND · MICROSECOND · DAYOFWEEK · WEEKDAY · DAYOFYEAR · QUARTER · WEEK · WEEKOFYEAR · YEARWEEK · DAYNAME · MONTHNAME · LAST_DAY · DATE_FORMAT / TIME_FORMAT · STR_TO_DATE · DATE_ADD / ADDDATE · DATE_SUB / SUBDATE · TIMESTAMPADD · DATEDIFF · TIMEDIFF · TIMESTAMPDIFF · ADDTIME · SUBTIME · UNIX_TIMESTAMP · FROM_UNIXTIME · TO_DAYS · FROM_DAYS · MAKEDATE · MAKETIME · TIME_TO_SEC · SEC_TO_TIME · EXTRACT · TO_SECONDS · PERIOD_ADD · PERIOD_DIFF · GET_FORMAT · CONVERT_TZ
• 8.4 Control and logic functions
  • IF · IFNULL / NVL · COALESCE · NULLIF · ISNULL
• 8.5 Aggregate functions
  • COUNT · SUM · AVG · MIN / MAX · GROUP_CONCAT · STD / STDDEV / STDDEV_POP · STDDEV_SAMP · VARIANCE / VAR_POP · VAR_SAMP · BIT_AND, BIT_OR, BIT_XOR · JSON_ARRAYAGG
• 8.6 Regular expressions
  • REGEXP_LIKE · REGEXP_INSTR · REGEXP_SUBSTR · REGEXP_REPLACE
• 8.7 Hash, encryption and encoding functions
  • MD5 · SHA1 / SHA · SHA2 · PASSWORD · HEX · UNHEX · TO_BASE64 · FROM_BASE64 · AES_ENCRYPT · AES_DECRYPT
• 8.8 Network (IP) functions
  • INET_ATON · INET_NTOA · INET6_ATON · INET6_NTOA · IS_IPV4 · IS_IPV6 · IS_IPV4_COMPAT · IS_IPV4_MAPPED
• 8.9 System, session and lock functions
  • DATABASE / SCHEMA · VERSION · LAST_INSERT_ID · ROW_COUNT · FOUND_ROWS · CONNECTION_ID · USER / SESSION_USER / SYSTEM_USER · CURRENT_USER · CURRENT_ROLE · SLEEP · UUID · UUID_SHORT · GET_LOCK · RELEASE_LOCK · IS_FREE_LOCK · IS_USED_LOCK · RELEASE_ALL_LOCKS · System variables @@name
• 8.10 JSON functions
  • JSON_OBJECT · JSON_ARRAY · JSON_MERGE_PRESERVE / JSON_MERGE · JSON_MERGE_PATCH · JSON_QUOTE · JSON_VALID
• 8.11 Vector functions (VECTOR(n))
  • VEC_FROMTEXT · VEC_TOTEXT · VEC_DISTANCE_EUCLIDEAN · VEC_DISTANCE_COSINE · VEC_DISTANCE · STRING_TO_VECTOR / TO_VECTOR · VECTOR_TO_STRING / FROM_VECTOR · VECTOR_DIM · DISTANCE · L2_DISTANCE · COSINE_DISTANCE · INNER_PRODUCT · VECTOR_NEGATIVE_INNER_PRODUCT · L1_DISTANCE · VECTOR_DIMS · VECTOR_NORM · L2_NORMALIZE · SUBVECTOR · VECTOR_ADD · VECTOR_SUB · VECTOR_MUL
• 8.12 Advanced formatting
  • SFORMAT
• 8.13 Window functions
  • ROW_NUMBER · RANK · DENSE_RANK · PERCENT_RANK · CUME_DIST · NTILE · LAG / LEAD · FIRST_VALUE / LAST_VALUE · NTH_VALUE · Window aggregates

8.1 String functions#

Summary:

CONCAT#

CONCAT(string1, string2, ...)

Concatenates from one to an unlimited number of arguments, converted to text if needed. NULL propagates: if a single argument is NULL, the entire result is NULL (unlike CONCAT_WS).

Returns: VARCHAR/text.

SELECT CONCAT('MIRAJ', ' ', 'DB');   -- 'MIRAJ DB'
SELECT CONCAT('a', NULL, 'b');       -- NULL

CONCAT_WS#

CONCAT_WS(separator, string1, string2, ...)

"Concat With Separator": concatenates string1, string2, ... inserting separator between each pair of non-NULL arguments; a NULL argument is simply skipped (no empty insertion). If separator itself is NULL, the result is NULL.

Returns: text.

SELECT CONCAT_WS(',', 'a', NULL, 'b');  -- 'a,b'
SELECT CONCAT_WS(NULL, 'a', 'b');       -- NULL

UPPER / UCASE#

UPPER(string)

Converts string to uppercase (Unicode rules, not just ASCII). UCASE is a strict alias.

Returns: text. NULL → NULL.

SELECT UPPER('café');  -- 'CAFÉ'

LOWER / LCASE#

LOWER(string)

Converts string to lowercase (Unicode rules). LCASE is a strict alias.

SELECT LOWER('CAFÉ');  -- 'café'

LENGTH / OCTET_LENGTH#

LENGTH(string)

Length of string in bytes of its UTF-8 encoding (not in characters); for a binary value (VARBINARY/BLOB), length in raw bytes, without any UTF-8 decoding. OCTET_LENGTH is a strict alias.

Returns: BIGINT.

SELECT LENGTH('café');  -- 5 (the é takes two bytes in UTF-8)

BIT_LENGTH#

BIT_LENGTH(string)

Length in bits, that is, eight times LENGTH(string).

SELECT BIT_LENGTH('a');  -- 8

CHAR_LENGTH / CHARACTER_LENGTH#

CHAR_LENGTH(string)

Length of string in Unicode characters (not in bytes); for a binary value, one byte counts as one character. CHARACTER_LENGTH is a strict alias.

SELECT CHAR_LENGTH('café');  -- 4

TRIM#

TRIM([BOTH | LEADING | TRAILING] [characters] FROM string)
TRIM(string)
TRIM(characters, string)

Removes repeated occurrences of characters (the space ' ' by default, with the one-argument form) at the start, at the end, or on both sides (BOTH, the default) of string. If characters is an empty string, string is returned unchanged.

Returns: text.

SELECT TRIM('  x  ');                 -- 'x'
SELECT TRIM(LEADING '0' FROM '007');  -- '7'
SELECT TRIM(BOTH 'xy' FROM 'xyzxy');  -- 'z'

LTRIM#

LTRIM(string)

Removes only the leading spaces (' ') of string.

SELECT LTRIM('  x  ');  -- 'x  '

RTRIM#

RTRIM(string)

Removes only the trailing spaces (' ') of string.

SELECT RTRIM('  x  ');  -- '  x'

SUBSTRING / SUBSTR / MID#

SUBSTRING(string, position [, length])

Substring of string starting at character position (1 = first character). A negative position counts from the end (-1 = last character). Without length, goes to the end; with length < 1, or position = 0, or position outside the string, returns an empty string (not NULL). SUBSTR and MID are strict aliases.

Returns: text.

SELECT SUBSTRING('MIRAJ DB', 7);      -- 'DB'
SELECT SUBSTRING('MIRAJ DB', 1, 5);   -- 'MIRAJ'
SELECT SUBSTRING('MIRAJ DB', -2);     -- 'DB'

LEFT#

LEFT(string, n)

The first n characters of string (negative n is treated as 0; n beyond the length returns the whole string).

SELECT LEFT('MIRAJ', 3);  -- 'MIR'

RIGHT#

RIGHT(string, n)

The last n characters of string.

SELECT RIGHT('MIRAJ', 3);  -- 'RAJ'

REPLACE#

REPLACE(string, search, replacement)

Replaces all non-overlapping occurrences of search with replacement in string, case-sensitively. If search is an empty string, string is returned unchanged (no infinite loop).

SELECT REPLACE('a-b-c', '-', '/');  -- 'a/b/c'

LOCATE#

LOCATE(substring, string [, start_position])

Position (in characters, 1-based) of the first occurrence of substring in string, case-insensitive search, starting from start_position (1 by default). Returns 0 if substring is not found or if start_position < 1.

SELECT LOCATE('DB', 'MIRAJ DB');     -- 7
SELECT LOCATE('xyz', 'MIRAJ DB');    -- 0

INSTR#

INSTR(string, substring)

Equivalent to LOCATE(substring, string) (arguments in reverse order), searching from the start, case-insensitive.

SELECT INSTR('MIRAJ DB', 'DB');  -- 7

LPAD#

LPAD(string, length, padding)

Pads string on the left with repetitions of padding up to length characters; if string already has length characters or more, it is truncated to length characters. Returns NULL if length < 0 or exceeds one million characters; returns an empty string if padding is empty and padding would be needed.

SELECT LPAD('7', 3, '0');   -- '007'
SELECT LPAD('12345', 3, '0');  -- '123' (truncated)

RPAD#

RPAD(string, length, padding)

Like LPAD but pads on the right.

SELECT RPAD('7', 3, '0');  -- '700'

REVERSE#

REVERSE(string)

Reverses the order of the (Unicode) characters of string.

SELECT REVERSE('MIRAJ');  -- 'JARIM'

REPEAT#

REPEAT(string, n)

Repeats string n times end to end (negative n is treated as 0). Returns NULL if n exceeds one million.

SELECT REPEAT('ab', 3);  -- 'ababab'

SPACE#

SPACE(n)

A string of n spaces (negative n is treated as 0). Returns NULL if n exceeds one million.

SELECT SPACE(3);  -- '   '

ASCII#

ASCII(string)

Leading byte of the UTF-8 encoding of the first character of string (so the ASCII code for an ASCII character; for a multi-byte character, only the leading byte is returned, not the Unicode code point). 0 if string is empty.

Returns: BIGINT.

SELECT ASCII('A');  -- 65

ORD#

ORD(string)

Numeric code of the first character of string, computed by combining its UTF-8 bytes in big-endian order (compatible with the reference's historical implementation). For an ASCII character, identical to ASCII. 0 if string is empty.

SELECT ORD('A');    -- 65
SELECT ORD('é');    -- 50089 (0xC3 0xA9 combined)

CHAR#

CHAR(code1 [, code2, ...])

Builds a string from code points (each argument becomes one character). A NULL argument is skipped (it does not break the concatenation, unlike most string functions). Each code is first interpreted as the significant bytes of a multi-byte character; if the result is not valid UTF-8, each code is re-read as a direct Unicode code point.

SELECT CHAR(77, 73, 82, 65, 74);  -- 'MIRAJ'

STRCMP#

STRCMP(string1, string2)

Compares two strings according to the comparison collation: 0 if equal, a negative number if string1 < string2, positive otherwise.

Returns: BIGINT.

SELECT STRCMP('a', 'b');  -- negative value
SELECT STRCMP('a', 'a');  -- 0

FORMAT#

FORMAT(number, decimals [, locale])

Formats number with decimals digits after the decimal point (rounded to the nearest value, halves away from zero; decimals clamped to 0–30) and a thousands separator. Without locale, uses Anglo-American conventions (, for thousands, . for the decimal point). With a recognized locale (e.g. 'fr_FR', 'de_DE'), the separators and digit grouping follow that locale; an unknown or NULL locale is equivalent to 'en_US'. NULL if number or decimals is NULL.

Returns: text.

SELECT FORMAT(1234567.891, 2);              -- '1,234,567.89'
SELECT FORMAT(1234567.891, 2, 'fr_FR');     -- '1234567,89' (fr_FR does not group thousands)
SELECT FORMAT(1234567.891, 2, 'de_DE');     -- '1.234.567,89'

INSERT#

INSERT(string, position, length, replacement)

Replaces length characters of string starting at position (1-based) with replacement. If position is outside string (< 1 or > length(string)), string is returned unchanged. A negative length goes to the end of string.

SELECT INSERT('MIRAJ DB', 7, 2, 'Server');  -- 'MIRAJ Server'

ELT#

ELT(n, string1, string2, ...)

Returns the n-th string (1-based) among string1, string2, .... Returns NULL if n is NULL, out of range (< 1 or > the number of strings), or if the chosen string is NULL — but other NULLs in the list do not affect the result if they are not chosen.

SELECT ELT(2, 'a', 'b', 'c');  -- 'b'
SELECT ELT(5, 'a', 'b', 'c');  -- NULL

FIELD#

FIELD(string, value1, value2, ...)

Position (1-based) of string in the list value1, value2, ... (case-insensitive comparison), 0 if not found. Returns 0 (not NULL) if string is NULL; a NULL valueN is skipped and never matches.

SELECT FIELD('b', 'a', 'b', 'c');  -- 2
SELECT FIELD('x', 'a', 'b', 'c');  -- 0

FIND_IN_SET#

FIND_IN_SET(string, list)

Position (1-based) of string in list, a string of comma-separated values; 0 if not found or if list is empty.

SELECT FIND_IN_SET('b', 'a,b,c');  -- 2

SUBSTRING_INDEX#

SUBSTRING_INDEX(string, delimiter, n)

If n > 0, everything before the n-th occurrence of delimiter counting from the start; if n < 0, everything after the |n|-th occurrence counting from the end. Returns the whole string if delimiter does not appear enough times. Returns an empty string if delimiter is empty or n is 0.

SELECT SUBSTRING_INDEX('a.b.c.d', '.', 2);   -- 'a.b'
SELECT SUBSTRING_INDEX('a.b.c.d', '.', -2);  -- 'c.d'

QUOTE#

QUOTE(string)

Surrounds string with apostrophes and escapes the apostrophes and backslashes it contains, to produce a SQL literal directly reusable in a statement. Does not propagate NULL: QUOTE(NULL) returns the literal string 'NULL' (four letters, without quotes), the SQL keyword as it would appear in a statement.

SELECT QUOTE("l'ami");  -- '''l\'ami'''  (literal: 'l\'ami')
SELECT QUOTE(NULL);     -- NULL  (the text, not the SQL NULL value)

BIN#

BIN(n)

Binary representation of n (signed 64-bit integer, read as two's complement if negative).

SELECT BIN(5);   -- '101'
SELECT BIN(-1);  -- '1111111111111111111111111111111111111111111111111111111111111111' (64 ones)

OCT#

OCT(n)

Octal representation of n, equivalent to CONV(n, 10, 8). A text argument is read as a decimal integer prefix (reading stops at the first non-numeric character, overflow saturates); an empty string returns NULL.

SELECT OCT(8);  -- '10'

CONV#

CONV(number, from_base, to_base)

Converts the text representation of number (read in from_base) to to_base (bases from 2 to 36, absolute value). Reading stops at the first digit invalid for the source base; leading spaces are ignored. NULL if either base is outside 2–36.

Returns: text.

SELECT CONV('FF', 16, 10);  -- '255'
SELECT CONV(255, 10, 16);   -- 'FF'

MAKE_SET#

MAKE_SET(mask, string1, string2, ...)

Comma-separated list of the stringN whose bit N-1 of mask (integer) is 1; bits beyond the number of strings supplied are ignored. A NULL stringN is skipped even if its bit is 1. NULL if mask is NULL.

SELECT MAKE_SET(5, 'a', 'b', 'c');  -- 'a,c' (bits 0 and 2 set: 5 = 0b101)

EXPORT_SET#

EXPORT_SET(mask, on, off [, separator [, num_bits]])

For each of the num_bits bits of mask (64 by default, clamped to 0–64), returns on if the bit is 1, off otherwise, from the least significant bit upward, joined by separator (, by default).

SELECT EXPORT_SET(5, 'Y', 'N', ',', 4);  -- 'Y,N,Y,N'

SOUNDEX#

SOUNDEX(string)

Soundex phonetic code of string: the first letter is copied (uppercase), then one digit per following consonant according to the classic Soundex table (vowels, H, W, Y ignored without separating two identical digits), padded with zeros to 4 characters. Empty string if string contains no letters.

SELECT SOUNDEX('Robert');  -- 'R163'
SELECT SOUNDEX('Rupert');  -- 'R163'

NATURAL_SORT_KEY#

NATURAL_SORT_KEY(string)

Produces a text key such that a lexicographic sort of these keys is equivalent to a "natural sort" of string (numbers embedded in the text are sorted by value, not character by character: 'a2' before 'a10'). Useful in ORDER BY NATURAL_SORT_KEY(column).

SELECT NATURAL_SORT_KEY('a2'), NATURAL_SORT_KEY('a10');
-- 'a02', 'a110' (the second sorts after the first)

WEIGHT_STRING#

WEIGHT_STRING(string)
WEIGHT_STRING(string AS CHAR(n))
WEIGHT_STRING(string AS BINARY(n))

Returns the sort weight bytes of string according to the comparison collation used by the engine; two strings equal under = have the same result. AS CHAR(n) first reduces the text to n characters (truncated or padded with spaces); AS BINARY(n) reduces the raw bytes to n (truncated or padded with zeros). A numeric value returns NULL (this function defines no sort weight for a number); a date or time uses the bytes of its text.

Returns: binary (VARBINARY).

LOAD_FILE#

LOAD_FILE(path)

Contents of a file on the server, as binary. The file must be located in the directory allowed by the server variable secure_file_priv, and the user must have the FILE privilege; a relative path starts from that directory, and a path that would leave it (via ..) is rejected. Returns NULL, without error, if no directory is allowed for the session, or if the file is missing, unreadable, is not a regular file, or exceeds max_allowed_packet.

Returns: binary.

SELECT LOAD_FILE('data/import.csv');

CONVERT (character set)#

CONVERT(expr USING character_set)
CAST(expr AS CHAR CHARACTER SET character_set)

Converts expr to the named character set. To binary, returns the raw UTF-8 bytes of the text (binary type); to another set, returns a text in which characters not representable in that set are replaced by ?. This form of CONVERT (with a character set) is distinct from CONVERT(expr, type), which performs a SQL type change, described in chapter 4. Data Types.

SELECT HEX(CONVERT('é' USING latin1));  -- 'E9'

8.2 Numeric functions#

Calculations on DECIMAL remain exact (scaled integer arithmetic); a division or modulo by zero, or an invalid domain (logarithm of a negative number, square root of a negative...) returns NULL rather than raising an error.

Summary:

ABS#

ABS(number)

Absolute value. An integer stays an integer, a DECIMAL stays a DECIMAL (same scale), others become DOUBLE. NULL on overflow (the minimum value of a signed BIGINT, whose opposite does not fit in a BIGINT).

SELECT ABS(-5);    -- 5
SELECT ABS(-5.25); -- 5.25

SIGN#

SIGN(number)

1 if number > 0, -1 if number < 0, 0 otherwise.

Returns: BIGINT.

ROUND#

ROUND(number [, decimals])

Rounds number to decimals decimal places (0 by default; negative to round to tens, hundreds...). An integer stays an integer (rounded to tens if decimals < 0); a DECIMAL is rounded away from zero on a half (ROUND(0.5, 0) = 1) and the result keeps the requested number of decimals, padded with zeros if needed (ROUND(2.567, 5) = 2.56700); a floating-point value (FLOAT/DOUBLE) is rounded to the nearest even (default IEEE 754 mode: ROUND(2.5e0) = 2, ROUND(1.5e0) = 2, ROUND(0.5e0) = 0), consistent with the observed behavior of the reference server for this type.

SELECT ROUND(1250, -2);   -- 1300 (integer)
SELECT ROUND(2.5);        -- 3   (DECIMAL literal: rounded away from zero)
SELECT ROUND(1.005, 2);   -- 1.01 (exact DECIMAL)

TRUNCATE#

TRUNCATE(number, decimals)

Truncates number to decimals decimal places without rounding (truncation toward zero). decimals is mandatory, unlike ROUND.

SELECT TRUNCATE(1.999, 2);  -- 1.99
SELECT TRUNCATE(1999, -2);  -- 1900

FLOOR#

FLOOR(number)

Largest integer less than or equal to number. NULL if outside the range of a signed BIGINT.

Returns: BIGINT.

SELECT FLOOR(1.9);   -- 1
SELECT FLOOR(-1.1);  -- -2

CEILING / CEIL#

CEILING(number)

Smallest integer greater than or equal to number. CEIL is a strict alias.

SELECT CEILING(1.1);  -- 2
SELECT CEILING(-1.9); -- -1

MOD#

MOD(a, b)

Remainder of the integer division of a by b (same sign as a, like the % operator). NULL if b is 0. An integer divided by an integer returns an integer; otherwise the calculation goes through a floating-point division but the result keeps the numeric representation of the first argument (integer, DECIMAL or DOUBLE).

SELECT MOD(10, 3);   -- 1
SELECT MOD(-10, 3);  -- -1
SELECT MOD(10, 0);   -- NULL

POWER / POW#

POWER(base, exponent)

base raised to the power exponent. POW is a strict alias.

Returns: DOUBLE.

SELECT POWER(2, 10);  -- 1024

SQRT#

SQRT(number)

Square root. NULL if number < 0.

SELECT SQRT(16);  -- 4
SELECT SQRT(-1);  -- NULL

EXP#

EXP(number)

e raised to the power number.

SELECT EXP(1);  -- 2.718281828459045

LN#

LN(number)

Natural logarithm. NULL if number <= 0.

LOG#

LOG(number)
LOG(base, number)

Without base, natural logarithm (identical to LN). With base, logarithm of number in base base. NULL if number <= 0, base <= 0 or base = 1.

SELECT LOG(2, 8);  -- 3

LOG10#

LOG10(number)

Base-10 logarithm. NULL if number <= 0.

LOG2#

LOG2(number)

Base-2 logarithm. NULL if number <= 0.

SELECT LOG2(8);  -- 3

PI#

PI()

The constant π (3.141592653589793), with no argument.

Returns: DOUBLE. Constant function (evaluated once per statement).

RAND#

RAND([seed])

Pseudo-random floating-point number in [0, 1). Without an argument, a new value on each call. With seed (a constant integer or an expression), the generator is reseeded with that seed: two calls with the same seed return the same value. A seed equal to NULL is equivalent to 0.

SELECT RAND();      -- e.g. 0.7364...
SELECT RAND(42);    -- always the same value for seed 42

SIN, COS, TAN#

SIN(angle_radians)
COS(angle_radians)
TAN(angle_radians)

Usual trigonometric functions, argument in radians.

COT#

COT(angle_radians)

Cotangent (1 / TAN(angle_radians)). NULL if TAN(angle_radians) = 0.

ASIN#

ASIN(number)

Arc sine, in radians. NULL if number is outside [-1, 1].

ACOS#

ACOS(number)

Arc cosine, in radians. NULL if number is outside [-1, 1].

ATAN / ATAN2#

ATAN(number)
ATAN(y, x)
ATAN2(y, x)

ATAN(number): arc tangent in radians. ATAN(y, x) (or ATAN2(y, x)): angle of the point (x, y), taking the quadrant into account.

DEGREES#

DEGREES(angle_radians)

Converts radians to degrees.

RADIANS#

RADIANS(angle_degrees)

Converts degrees to radians.

GREATEST#

GREATEST(expr1, expr2, ...)

The greatest of the expressions, compared according to a common ordering derived from their types (see the COALESCE typing table in 8.4). NULL if any of the expressions is NULL.

SELECT GREATEST(3, 7, 2);  -- 7

LEAST#

LEAST(expr1, expr2, ...)

The smallest of the expressions, same rules as GREATEST.

SELECT LEAST(3, 7, 2);  -- 2

CRC32 / CRC32C#

CRC32(expr)
CRC32([seed,] expr)
CRC32C(expr)
CRC32C([seed,] expr)

CRC-32 checksum (standard polynomial for CRC32, Castagnoli for CRC32C) of the bytes of expr (raw bytes for a binary value, UTF-8 text otherwise). With a seed (result of a previous call), chains the computation: CRC32C(CRC32C('a'), 'b') is equivalent to CRC32C('ab').

Returns: BIGINT (unsigned 32-bit value, represented as a positive integer).

SELECT CRC32('MIRAJ');  -- a 32-bit integer

8.3 Date and time functions#

MIRAJ represents dates as a floating-point number (integer part = number of days since 12/30/1899, fractional part = fraction of a day), in the manner of TDateTime; the functions below hide the internal representation.

Summary:

NOW / CURRENT_TIMESTAMP / LOCALTIME / LOCALTIMESTAMP#

NOW()

Current local date and time, frozen at the start of the statement: all calls to NOW() within a single statement (including across multiple rows) return the same value. CURRENT_TIMESTAMP, LOCALTIME and LOCALTIMESTAMP are strict aliases.

Returns: DATETIME.

SELECT NOW();  -- '2026-09-23 14:05:12'

SYSDATE#

SYSDATE()

Like NOW(), but reads the clock on each call (not frozen for the statement): two calls to SYSDATE() in the same statement may differ.

CURDATE / CURRENT_DATE#

CURDATE()

Current date (without time), frozen for the statement. CURRENT_DATE is a strict alias.

Returns: DATE.

CURTIME / CURRENT_TIME#

CURTIME()

Current time (without date), frozen for the statement. CURRENT_TIME is a strict alias.

Returns: TIME.

UTC_TIMESTAMP, UTC_DATE, UTC_TIME#

UTC_TIMESTAMP()
UTC_DATE()
UTC_TIME()

Equivalents of NOW(), CURDATE() and CURTIME() in UTC time rather than local time, all frozen for the statement.

DATE#

DATE(expr)

Date part of a date/time expression (time set to zero).

Returns: DATE.

SELECT DATE('2026-09-13 08:09:10');  -- '2026-09-13'

TIME#

TIME(expr)

Time part of a date/time expression.

Returns: TIME.

SELECT TIME('2026-09-13 08:09:10');  -- '08:09:10'

TIMESTAMP#

TIMESTAMP(expr)
TIMESTAMP(date_expr, time_expr)

With one argument, converts expr to DATETIME. With two arguments, combines the date of date_expr and the time (fractional part) of time_expr.

Returns: DATETIME.

YEAR#

YEAR(expr)

Year (BIGINT).

MONTH#

MONTH(expr)

Month, from 1 to 12 (BIGINT).

DAY / DAYOFMONTH#

DAY(expr)

Day of the month, from 1 to 31 (BIGINT). DAYOFMONTH is a strict alias.

HOUR#

HOUR(expr)

Hour, from 0 to 23 for a date/time; for a TIME value, may exceed 23 (a TIME duration can represent more than 24 hours).

MINUTE#

MINUTE(expr)

Minute, from 0 to 59.

SECOND#

SECOND(expr)

Second, from 0 to 59.

MICROSECOND#

MICROSECOND(expr)

Microseconds of the fractional part. MIRAJ only retains millisecond precision; the result is therefore always a multiple of 1000.

DAYOFWEEK#

DAYOFWEEK(expr)

Day of the week according to the usual convention: 1 = Sunday, ..., 7 = Saturday.

WEEKDAY#

WEEKDAY(expr)

Day of the week according to the ISO convention: 0 = Monday, ..., 6 = Sunday.

SELECT DAYOFWEEK('2026-09-13'), WEEKDAY('2026-09-13');  -- 1, 6 (Sunday)

DAYOFYEAR#

DAYOFYEAR(expr)

Rank of the day in the year (1 to 366).

QUARTER#

QUARTER(expr)

Quarter of the year, from 1 to 4.

WEEK#

WEEK(expr [, mode])

Week number in the year (0 by default without mode). mode (0 to 7) combines three independent choices through its bits:

SELECT WEEK('2026-01-01', 0);  -- 0
SELECT WEEK('2026-01-01', 1);  -- depends on the day of the week of January 1st

WEEKOFYEAR#

WEEKOFYEAR(expr)

Equivalent to WEEK(expr, 3) (ISO 8601 weeks: Monday, numbered 1 to 53, week 1 = the one containing the first Thursday of the year).

YEARWEEK#

YEARWEEK(expr [, mode])

Year and week combined into a single integer YYYYWW (mode defaults to 0, same bits as WEEK, with the "weeks 1–53" bit always active).

SELECT YEARWEEK('2026-09-13');  -- 202637

DAYNAME#

DAYNAME(expr)

Full name of the day of the week, in English ('Sunday', 'Monday', ...).

MONTHNAME#

MONTHNAME(expr)

Full name of the month, in English ('January', ...).

LAST_DAY#

LAST_DAY(expr)

Date of the last day of the month of expr.

Returns: DATE.

SELECT LAST_DAY('2026-02-10');  -- '2026-02-28'

DATE_FORMAT / TIME_FORMAT#

DATE_FORMAT(expr, format)

Formats expr according to format, a string with %X specifiers:

TIME_FORMAT is a strict alias (useful in documentation with a format that has no date part).

Returns: text.

SELECT DATE_FORMAT('2026-09-13 08:09:10', '%W %d %M %Y à %H:%i');
-- 'Sunday 13 September 2026 à 08:09'

STR_TO_DATE#

STR_TO_DATE(string, format)

Parses string according to format (same specifiers as DATE_FORMAT, plus %p and %T when reading) and returns the corresponding date/time. NULL if string does not match the format exactly or describes an invalid date (e.g. '2026-02-30'). The return type (DATE, TIME or DATETIME) is inferred from the specifiers present in format when it is a constant.

SELECT STR_TO_DATE('13/09/2026', '%d/%m/%Y');  -- DATE '2026-09-13'
SELECT STR_TO_DATE('31/02/2026', '%d/%m/%Y');  -- NULL (invalid day)

DATE_ADD / ADDDATE#

DATE_ADD(expr, INTERVAL n unit)

Adds n units unit to expr. Simple units: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND. Composite units accepted with a textual interval (INTERVAL '1:30' HOUR_MINUTE, INTERVAL '2-3' YEAR_MONTH, etc.): YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND. A month addition that goes past the last day of the target month brings the result back to that last day ('2026-01-31' + INTERVAL 1 MONTH = '2026-02-28'). NULL if the result falls outside the range 0001-01-01 to 9999-12-31. ADDDATE is a strict alias, also accepting the three-argument form ADDDATE(expr, n, 'unit').

Returns: DATE if expr is a date and the unit is one day or more, TIME if expr is a time, DATETIME otherwise.

SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH);        -- '2026-02-28'
SELECT DATE_ADD('2026-01-01 10:00', INTERVAL '1:30' HOUR_MINUTE);  -- '2026-01-01 11:30:00'

DATE_SUB / SUBDATE#

DATE_SUB(expr, INTERVAL n unit)

Like DATE_ADD, but subtracts. SUBDATE is a strict alias.

SELECT DATE_SUB('2026-03-01', INTERVAL 1 DAY);  -- '2026-02-28'

TIMESTAMPADD#

TIMESTAMPADD(unit, n, expr)

Adds n units unit (same simple units as DATE_ADD) to expr; argument order differs from DATE_ADD.

Returns: DATETIME.

SELECT TIMESTAMPADD(DAY, 7, '2026-09-13');  -- '2026-09-20 00:00:00'

DATEDIFF#

DATEDIFF(expr1, expr2)

Difference as a whole number of days between the date parts of expr1 and expr2 (expr1 - expr2), ignoring the time.

Returns: BIGINT.

SELECT DATEDIFF('2026-09-20', '2026-09-13');  -- 7

TIMEDIFF#

TIMEDIFF(expr1, expr2)

Difference expr1 - expr2 as a duration.

Returns: TIME.

TIMESTAMPDIFF#

TIMESTAMPDIFF(unit, expr1, expr2)

Whole difference expr2 - expr1 expressed in unit (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND). For YEAR, QUARTER and MONTH, the calculation is exact calendar-based: it counts the full months actually elapsed (taking day and time into account), not an approximation by dividing by an average number of days. For example, between two consecutive January 15ths, the difference in years is always exactly 1, whether or not a leap year lies between them — a 365.25 days calculation would sometimes wrongly give 0.

Returns: BIGINT.

SELECT TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-04-10');  -- 2 (not yet 3 full months)
SELECT TIMESTAMPDIFF(YEAR, '2001-01-01', '2002-01-01');   -- 1 (exact, calendar-based)

ADDTIME#

ADDTIME(expr, duration)

Adds duration (a time, or a time interval text) to expr.

Returns: DATETIME if expr carries a date, TIME otherwise.

SUBTIME#

SUBTIME(expr, duration)

Like ADDTIME, but subtracts.

UNIX_TIMESTAMP#

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(expr)

Without an argument, seconds elapsed since the Unix epoch (January 1st, 1970 UTC) up to the start instant of the statement. With expr (local time), the corresponding Unix seconds.

Returns: BIGINT.

FROM_UNIXTIME#

FROM_UNIXTIME(seconds [, format])

Local date-time corresponding to seconds (Unix seconds, fractions accepted to the nearest millisecond). With format, directly returns the formatted text (like DATE_FORMAT).

Returns: DATETIME, or text if format is supplied.

SELECT FROM_UNIXTIME(1767323045);

TO_DAYS#

TO_DAYS(expr)

Number of days elapsed since day 0 of the proleptic Gregorian calendar, for the date part of expr.

Returns: BIGINT.

FROM_DAYS#

FROM_DAYS(n)

Date corresponding to the number of days n since day 0 (inverse of TO_DAYS). Does not account for the Gregorian calendar before its adoption; to be reserved for computing differences between dates rather than for actual historical dates.

Returns: DATE.

MAKEDATE#

MAKEDATE(year, day_of_year)

Date of the day_of_year-th day of year (January 1st = day 1). NULL if day_of_year < 1.

SELECT MAKEDATE(2026, 1);   -- '2026-01-01'
SELECT MAKEDATE(2026, 60);  -- '2026-03-01'

MAKETIME#

MAKETIME(hour, minute, second)

Builds a time from its components (second accepts a fraction).

Returns: TIME.

SELECT MAKETIME(8, 30, 15);  -- '08:30:15'

TIME_TO_SEC#

TIME_TO_SEC(expr)

Whole number of seconds corresponding to the time part of expr (or to expr directly if it is already a time).

Returns: BIGINT.

SEC_TO_TIME#

SEC_TO_TIME(seconds)

Time corresponding to seconds (fractions of a second accepted).

Returns: TIME.

SELECT SEC_TO_TIME(3661);  -- '01:01:01'

EXTRACT#

EXTRACT(unit FROM expr)

Extracts a field from expr: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, or a composite unit (YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND) that combines several fields into a single integer (e.g. YEAR_MONTH gives YYYYMM).

Returns: BIGINT.

SELECT EXTRACT(YEAR_MONTH FROM '2026-09-13');  -- 202609

TO_SECONDS#

TO_SECONDS(expr)

Number of seconds elapsed from day 0 of year 0 up to expr (combines TO_DAYS and the time of day).

Returns: BIGINT.

PERIOD_ADD#

PERIOD_ADD(period, n)

Adds n months to period, a period in YYMM or YYYYMM format (two-digit years below 70 are read as 20xx, otherwise 19xx). 0 stays 0 regardless of n.

Returns: BIGINT in YYYYMM format.

SELECT PERIOD_ADD(202609, 3);  -- 202612

PERIOD_DIFF#

PERIOD_DIFF(period1, period2)

Difference in months between two YYMM/YYYYMM periods.

SELECT PERIOD_DIFF(202612, 202609);  -- 3

GET_FORMAT#

GET_FORMAT({DATE | TIME | DATETIME | TIMESTAMP}, {'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL'})

Returns the format string (DATE_FORMAT-compatible) corresponding to a named regional convention, for example to then use it with STR_TO_DATE or DATE_FORMAT. NULL if the combination is not defined.

SELECT GET_FORMAT(DATE, 'EUR');  -- '%d.%m.%Y'
SELECT STR_TO_DATE('13.09.2026', GET_FORMAT(DATE, 'EUR'));  -- '2026-09-13'

CONVERT_TZ#

CONVERT_TZ(expr, from_tz, to_tz)

Converts expr from time zone from_tz to to_tz. Each time zone is either 'SYSTEM' (the server's local time), a fixed offset ('+01:00', from -13:59 to +14:00), or an IANA time zone name ('Europe/Paris', 'UTC', ...). NULL if a time zone is invalid or if the result falls outside the range of years 1 to 9999.

Returns: DATETIME.

SELECT CONVERT_TZ('2026-09-13 12:00:00', 'UTC', 'Europe/Paris');  -- '2026-09-13 14:00:00'

8.4 Control and logic functions#

Summary:

IF#

IF(condition, value_if_true, value_if_false)

Returns value_if_true if condition is true (non-null and different from zero), otherwise value_if_false. A NULL condition counts as false. The return type is the common type of value_if_true and value_if_false (see the COALESCE table below); the branch not taken is not evaluated for its error type but counts toward the result typing.

SELECT IF(1 > 0, 'yes', 'no');  -- 'yes'
SELECT IF(NULL, 'yes', 'no');   -- 'no'

IFNULL / NVL#

IFNULL(expr1, expr2)

Returns expr1 if it is not NULL, otherwise expr2. NVL is a strict alias.

SELECT IFNULL(NULL, 'default value');  -- 'default value'
SELECT IFNULL('x', 'y');               -- 'x'

COALESCE#

COALESCE(expr1, expr2, ...)

Returns the first non-NULL expression in the list, or NULL if all are NULL.

Return type: derived from all arguments — all integers → BIGINT; all numeric with a floating-point → DOUBLE; all numeric without a floating-point → DECIMAL (at the widest scale); all temporal with a DATETIME → DATETIME, otherwise the type of the first argument; all VECTOR → VECTOR; otherwise text. These same rules apply to IF, IFNULL, GREATEST, LEAST and CASE WHEN.

SELECT COALESCE(NULL, NULL, 'found', 'ignored');  -- 'found'

NULLIF#

NULLIF(expr1, expr2)

Returns NULL if expr1 = expr2, otherwise expr1 (with its original type and scale, including DECIMAL).

SELECT NULLIF(5, 5);  -- NULL
SELECT NULLIF(5, 6);  -- 5

ISNULL#

ISNULL(expr)

1 if expr is NULL, 0 otherwise. Unlike most functions, never returns NULL itself.

Returns: BIGINT.

SELECT ISNULL(NULL);  -- 1
SELECT ISNULL(0);     -- 0

8.5 Aggregate functions#

Aggregate functions summarize the values of a group of rows (GROUP BY, or the whole table without GROUP BY) into a single value. Unlike scalar functions, they silently ignore NULLs in their argument rather than making the whole computation fail.

These functions, except GROUP_CONCAT, JSON_ARRAYAGG, BIT_*, STDDEV_SAMP, VAR_POP and VAR_SAMP, can also be used as window functions with OVER (...): see 8.13.

Summary:

COUNT#

COUNT(*)
COUNT([DISTINCT] expr)

COUNT(*) counts all rows of the group. COUNT(expr) counts the rows where expr is not NULL. COUNT(DISTINCT expr) counts the distinct non-NULL values of expr.

Returns: BIGINT.

SELECT COUNT(*) FROM commandes;
SELECT COUNT(DISTINCT client_id) FROM commandes;

SUM#

SUM([DISTINCT] expr)

Sum of the non-NULL values of expr. NULL if the group contains no non-NULL value. On a VECTOR(n) column, performs an element-wise sum (VECTOR(n) result).

Returns: BIGINT if expr is an integer, DECIMAL (same scale) if expr is DECIMAL, DOUBLE otherwise.

AVG#

AVG([DISTINCT] expr)

Average of the non-NULL values of expr. On VECTOR(n), element-wise average.

Returns: DECIMAL with 4 more decimals than expr if expr is an integer or DECIMAL, DOUBLE otherwise.

MIN / MAX#

MIN(expr)
MAX(expr)

Smallest / largest non-NULL value of expr according to the natural ordering of the type. NULL if the group contains no non-NULL value.

Returns: the type of expr.

GROUP_CONCAT#

GROUP_CONCAT([DISTINCT] expr [, expr2, ...]
             [ORDER BY key1 [ASC|DESC], ...]
             [SEPARATOR string])

Concatenates the non-NULL values of expr of the group (converted to text) into a single string. With several expressions (expr, expr2, ...), each is concatenated directly (with no separator between them) for each row, and the entire row is skipped if any of these expressions is NULL. DISTINCT removes duplicates before concatenation. ORDER BY (with a position number or an expression) sets the order of the values in the result — the sort key can be an expression that is not itself concatenated. SEPARATOR sets the separator between values (',' by default); SEPARATOR '' uses no separator.

Returns: text.

SELECT GROUP_CONCAT(nom ORDER BY nom SEPARATOR ' ; ') FROM clients;
-- 'Ali ; Bob ; Chloé'
SELECT GROUP_CONCAT(DISTINCT ville) FROM clients;

STD / STDDEV / STDDEV_POP#

STD(expr)
STDDEV(expr)
STDDEV_POP(expr)

Population standard deviation of the non-NULL values of expr (divisor = number of values). The three names are strictly equivalent. NULL if the group is empty.

Returns: DOUBLE.

STDDEV_SAMP#

STDDEV_SAMP(expr)

Corrected (sample) standard deviation, divisor = number of values minus one. NULL if the group has fewer than two values.

VARIANCE / VAR_POP#

VARIANCE(expr)
VAR_POP(expr)

Population variance (square of STD). Both names are equivalent.

VAR_SAMP#

VAR_SAMP(expr)

Corrected variance (square of STDDEV_SAMP). NULL if the group has fewer than two values.

BIT_AND, BIT_OR, BIT_XOR#

BIT_AND(expr)
BIT_OR(expr)
BIT_XOR(expr)

Bitwise AND, OR and XOR of all non-NULL integer values of expr (BIT_AND of an empty group is all bits set to 1, BIT_OR and BIT_XOR of an empty group are 0, like the reference).

Returns: unsigned 64-bit integer.

JSON_ARRAYAGG#

JSON_ARRAYAGG(expr)

Builds a JSON array ([...]) from the values of expr of the group (in reading order), each value being converted to a scalar JSON document (number as is, text in quotes, NULL as null) unless expr already produces a JSON document.

Returns: JSON text.

SELECT JSON_ARRAYAGG(nom) FROM clients;  -- '["Ali", "Bob", "Chloé"]'

8.6 Regular expressions#

MIRAJ implements PCRE-compatible regular expressions (linear-time engine, without backreferences or zero-width assertions); it is the same engine as the REGEXP/RLIKE operator. By default, the search is case-insensitive. The optional type parameter (last argument of each function) accepts a combination of the letters:

An invalid pattern raises an error (some widespread SQL servers in practice accept PCRE constructs not supported here); a quantifier applied directly to ^ or $ is rejected.

Summary:

REGEXP_LIKE#

REGEXP_LIKE(expr, pattern [, type])

1 if pattern matches somewhere in expr, 0 otherwise.

Returns: BIGINT.

SELECT REGEXP_LIKE('MIRAJ-1.0', '^[A-Z]+-[0-9.]+$');  -- 1

REGEXP_INSTR#

REGEXP_INSTR(expr, pattern [, position [, occurrence [, option [, type]]]])

Position (in characters, 1-based) of the start of the occurrence-th match (1 by default) starting from position (1 by default); 0 if it does not exist. If option is 1, returns the position after the end of the match rather than its start (0 by default).

Returns: BIGINT.

SELECT REGEXP_INSTR('abc123def456', '[0-9]+', 1, 2);  -- position of the second group of digits

REGEXP_SUBSTR#

REGEXP_SUBSTR(expr, pattern [, position [, occurrence [, type]]])

Text of the occurrence-th match of pattern in expr starting from position. Returns an empty string (not NULL) if no match is found.

SELECT REGEXP_SUBSTR('prix: 42.50 EUR', '[0-9]+\\.[0-9]+');  -- '42.50'
SELECT REGEXP_SUBSTR('abc', '[0-9]+');                        -- '' (empty string)

REGEXP_REPLACE#

REGEXP_REPLACE(expr, pattern, replacement [, position [, occurrence [, type]]])

Replaces with replacement all matches of pattern starting from position (all of them, by default), or only the occurrence-th one if specified (> 0). In replacement, \N (N from 0 to 9) inserts the text of the N-th captured group (\0 = entire match); a \N without a corresponding group inserts an empty string.

SELECT REGEXP_REPLACE('2026-09-13', '([0-9]+)-([0-9]+)-([0-9]+)', '\\3/\\2/\\1');
-- '13/09/2026'

8.7 Hash, encryption and encoding functions#

Summary:

MD5#

MD5(expr)

MD5 digest (RFC 1321) of expr, in lowercase hexadecimal (32 characters).

Returns: text.

SELECT MD5('MIRAJ');  -- a 32-character hexadecimal string

SHA1 / SHA#

SHA1(expr)

SHA-1 digest (FIPS 180-4), in lowercase hexadecimal (40 characters). SHA is a strict alias.

SHA2#

SHA2(expr, bit_length)

Digest from the SHA-2 family, in lowercase hexadecimal. bit_length must be 224, 256, 384, 512, or 0 (equivalent to 256); any other value returns NULL.

SELECT SHA2('MIRAJ', 256);  -- 64 hexadecimal characters

PASSWORD#

PASSWORD(password)

Digest used internally for the authentication_string of an account (see chapter 10. Accounts and Privileges). Not normally needed in application queries.

HEX#

HEX(expr)

Hexadecimal representation (uppercase) of expr: for a number, its 64-bit integer value; for a binary value, its raw bytes; for a text, its UTF-8 bytes.

SELECT HEX(255);      -- 'FF'
SELECT HEX('AB');     -- '4142'

UNHEX#

UNHEX(hex_string)

Decodes hex_string (an implicit leading digit 0 is added if its length is odd) into raw bytes. NULL if hex_string contains a character that is not a hexadecimal digit.

Returns: binary (VARBINARY).

SELECT UNHEX('4142');  -- the bytes of 'AB'

TO_BASE64#

TO_BASE64(expr)

Encodes expr in base64 (standard alphabet, with = padding), inserting a line break every 76 characters like the reference.

Returns: text.

FROM_BASE64#

FROM_BASE64(string)

Decodes string (standard base64) into raw bytes; spaces, tabs and line breaks are ignored; NULL if the text is not valid base64.

Returns: binary.

SELECT FROM_BASE64(TO_BASE64('MIRAJ'));  -- the bytes of 'MIRAJ'

AES_ENCRYPT#

AES_ENCRYPT(data, key)

Encrypts data with AES-128-ECB (with PKCS7 padding), key being folded by XOR to 16 bytes regardless of its length (default mode, corresponding to block_encryption_mode = aes-128-ecb).

Returns: binary.

AES_DECRYPT#

AES_DECRYPT(encrypted_data, key)

Inverse of AES_ENCRYPT with the same key derivation. NULL if decryption fails (wrong key, corrupted data).

SELECT AES_DECRYPT(AES_ENCRYPT('secret', 'ma_cle'), 'ma_cle');  -- 'secret'

8.8 Network (IP) functions#

Summary:

INET_ATON#

INET_ATON(ipv4_address)

Converts an IPv4 address in strict dotted-decimal notation (four parts, no abbreviated form) to its 32-bit integer. NULL if ipv4_address is not a valid IPv4 address in this notation.

Returns: BIGINT.

SELECT INET_ATON('192.168.1.1');  -- 3232235777

INET_NTOA#

INET_NTOA(integer)

Inverse of INET_ATON. NULL if integer is not in [0, 4294967295].

SELECT INET_NTOA(3232235777);  -- '192.168.1.1'

INET6_ATON#

INET6_ATON(address)

Converts an IPv4 or IPv6 address (text notation) to its binary form (4 bytes for IPv4, 16 for IPv6). NULL if address is neither.

Returns: binary.

INET6_NTOA#

INET6_NTOA(binary)

Inverse of INET6_ATON: text form of a binary address of 4 or 16 bytes. NULL for any other length.

IS_IPV4#

IS_IPV4(string)

1 if string is a valid IPv4 address, 0 otherwise (never NULL for a non-NULL input).

Returns: BIGINT.

IS_IPV6#

IS_IPV6(string)

1 if string is a valid IPv6 address, 0 otherwise.

IS_IPV4_COMPAT#

IS_IPV4_COMPAT(binary)

1 if binary (16 bytes, as produced by INET6_ATON) is an obsolete IPv4-compatible address (::a.b.c.d: first twelve bytes zero), 0 otherwise.

IS_IPV4_MAPPED#

IS_IPV4_MAPPED(binary)

1 if binary is an IPv4-mapped address (::ffff:a.b.c.d), 0 otherwise.

8.9 System, session and lock functions#

Summary:

DATABASE / SCHEMA#

DATABASE()

Name of the session's current database, NULL if no database is selected. SCHEMA is a strict alias. Constant function (a single evaluation per statement).

VERSION#

VERSION()

Version number of the MIRAJ server, in a format compatible with existing connectors (see chapter 1. Introduction for the variables that distinguish editions).

Returns: text.

LAST_INSERT_ID#

LAST_INSERT_ID()

Last auto-increment column value generated by an INSERT statement in the session.

Returns: BIGINT.

ROW_COUNT#

ROW_COUNT()

Number of rows affected by the last INSERT, UPDATE or DELETE statement of the session.

FOUND_ROWS#

FOUND_ROWS()

Number of rows of the last result read (historical usage, specific to the connection).

CONNECTION_ID#

CONNECTION_ID()

Connection identifier of the current session, as displayed by SHOW PROCESSLIST.

USER / SESSION_USER / SYSTEM_USER#

USER()

Identity of the client as supplied at connection, in the format 'user@host' (the client's host, not the account's). SESSION_USER and SYSTEM_USER are strict aliases.

CURRENT_USER#

CURRENT_USER()

Account actually used for privilege checks, in the format 'user@host' (the host defined for the account, which may differ from that of USER() if the account has a wildcard entry such as '%').

CURRENT_ROLE#

CURRENT_ROLE()

Active roles of the session, or the string 'NONE' if no role is active (see chapter 10. Accounts and Privileges).

SLEEP#

SLEEP(seconds)

Suspends execution for seconds (fractions accepted), then returns 0. A NULL or negative argument does not wait (returns 0 immediately). The wait is interruptible: KILL QUERY on the session cuts it short and SLEEP returns 1; KILL CONNECTION or exceeding max_statement_time raise an error rather than returning a value.

Returns: BIGINT.

SELECT SLEEP(0.5);  -- waits half a second, returns 0

UUID#

UUID()

Random version 4 UUID identifier, in lowercase (xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx).

Returns: text.

UUID_SHORT#

UUID_SHORT()

Increasing integer identifier, unique over time, in a format compatible with the reference.

Returns: BIGINT.

GET_LOCK#

GET_LOCK(name, timeout)

Attempts to acquire a named lock name (64 characters at most) for the session. Returns 1 if the lock is acquired (repeated acquisitions by the same session accumulate), 0 if timeout seconds (fraction allowed; negative = unlimited wait; NULL = 0) elapse without success, NULL if the wait is interrupted by KILL QUERY or outside any session. Raises an error if KILL CONNECTION occurs, if max_statement_time is exceeded, or if waiting would close a cycle between named locks (deadlock detected).

Returns: BIGINT.

SELECT GET_LOCK('verrou_import', 10);
-- ... protected processing ...
SELECT RELEASE_LOCK('verrou_import');

RELEASE_LOCK#

RELEASE_LOCK(name)

Releases one acquisition of lock name held by the session. 1 if an acquisition was released, 0 if the lock is held by another session, NULL if it is not held at all.

IS_FREE_LOCK#

IS_FREE_LOCK(name)

1 if nobody holds lock name, 0 otherwise.

IS_USED_LOCK#

IS_USED_LOCK(name)

Connection identifier of the session that holds lock name, NULL if it is free.

RELEASE_ALL_LOCKS#

RELEASE_ALL_LOCKS()

Releases all named-lock acquisitions of the current session; returns their number.

Returns: BIGINT.

System variables @@name#

In addition to the functions above, an expression can read a session or global system variable through @@name or @@session.name / @@global.name (for example @@version, @@miraj_edition, @@max_allowed_packet). These variables are described in chapter 11. Server Administration; they are manipulated with SET and can also be inspected with SHOW VARIABLES.

8.10 JSON functions#

MIRAJ carries a JSON document as a simple text value, written in the same format as the reference ({"a": 1, "b": [1, 2]}, a space after : and after each comma). The order of an object's members is preserved as written.

Summary:

JSON_OBJECT#

JSON_OBJECT([key1, value1, key2, value2, ...])

Builds a JSON object from key/value pairs (an even number of arguments is required). Each key is converted to JSON text; each value is converted to a JSON scalar (number as is, text in escaped quotes, NULL as null) unless it is already a JSON document (produced by another JSON function or a CAST(... AS JSON)). A NULL key raises an error.

Returns: JSON text.

SELECT JSON_OBJECT('nom', 'MIRAJ', 'version', 1.0);
-- '{"nom": "MIRAJ", "version": 1.0}'

JSON_ARRAY#

JSON_ARRAY([value1, value2, ...])

Builds a JSON array from its arguments (same conversion rules as JSON_OBJECT).

SELECT JSON_ARRAY(1, 'deux', NULL);  -- '[1, "deux", null]'

JSON_MERGE_PRESERVE / JSON_MERGE#

JSON_MERGE_PRESERVE(document1, document2, ...)

Merges at least two JSON documents: two arrays are concatenated; two objects are combined (the values of a key present in both are themselves merged recursively); any other combination (e.g. an object and an array) treats each isolated value as a one-element array and then concatenates them. JSON_MERGE is a strict alias.

SELECT JSON_MERGE_PRESERVE('{"a": 1}', '{"a": 2, "b": 3}');  -- '{"a": [1, 2], "b": 3}'
SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');       -- '[1, 2, true, false]'

JSON_MERGE_PATCH#

JSON_MERGE_PATCH(document1, document2, ...)

Merge according to RFC 7396: the members of the next document replace those of the previous one, a member equal to null removes the corresponding key; a document that is not an object entirely replaces the previous one. NULL if a document is NULL (unless it is followed by a document that is not an object, which prevails anyway).

SELECT JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": null, "c": 3}');  -- '{"b": 2, "c": 3}'

JSON_QUOTE#

JSON_QUOTE(string)

Surrounds string with double quotes and escapes JSON special characters, to produce a valid JSON string from a raw SQL text.

SELECT JSON_QUOTE('a "b" c');  -- '"a \"b\" c"'

JSON_VALID#

JSON_VALID(string)

1 if string is a syntactically valid JSON document, 0 otherwise.

Returns: BIGINT.

8.11 Vector functions (VECTOR(n))#

These functions operate on the VECTOR(n) type (n 32-bit floats), used for similarity search (embeddings). They fall into three families with different error conventions, inherited from distinct compatibilities:

• VEC_* family: an incorrect argument (wrong format, different dimensions) returns NULL rather than failing.
• STRING_TO_VECTOR / VECTOR_TO_STRING / VECTOR_DIM / DISTANCE family: an incorrect argument raises an error.
• L2_DISTANCE, COSINE_DISTANCE, VECTOR_ADD, etc. family (and the operators <->, <=>, <#>, <+>, +, -, * between vectors, see chapter 4. Data Types): an incorrect argument raises an error; computations are done in single precision (32 bits).

Everywhere, a vector argument also accepts its text form '[1, 2, 3]'.

Summary:

VEC_FROMTEXT#

VEC_FROMTEXT(string)

Converts the text form '[1, 2, 3]' to a binary vector. NULL if string is not a valid text vector.

Returns: VECTOR(n).

VEC_TOTEXT#

VEC_TOTEXT(vector)

Text form of a vector, with six significant digits per component.

Returns: text.

VEC_DISTANCE_EUCLIDEAN#

VEC_DISTANCE_EUCLIDEAN(vector1, vector2)

Euclidean (L2) distance between two vectors, computed in double precision. NULL if an argument is invalid or if the dimensions differ.

Returns: DOUBLE.

VEC_DISTANCE_COSINE#

VEC_DISTANCE_COSINE(vector1, vector2)

Cosine distance (1 - cosine similarity) between two vectors, in double precision.

VEC_DISTANCE#

VEC_DISTANCE(vector1, vector2)

Distance of the metric of the vector index carried by one of the two columns passed as arguments (Euclidean, cosine or negated inner product, see chapter 19). Rejected at compile time (error 4206) outside a context with a vector index, the distance then never being defined without a metric indication.

STRING_TO_VECTOR / TO_VECTOR#

STRING_TO_VECTOR(string)

Converts string (text form '[1, 2, 3]') to a binary vector; raises an error if string is not valid (instead of returning NULL, unlike VEC_FROMTEXT). TO_VECTOR is a strict alias.

VECTOR_TO_STRING / FROM_VECTOR#

VECTOR_TO_STRING(vector)

Text form of a vector (exponential notation, five significant digits). FROM_VECTOR is a strict alias.

VECTOR_DIM#

VECTOR_DIM(vector)

Number of components of the vector.

Returns: BIGINT.

DISTANCE#

DISTANCE(vector1, vector2, metric)

Distance between two vectors according to metric: 'EUCLIDEAN', 'COSINE' or 'DOT' (dot product). Error if metric is not recognized.

Returns: DOUBLE.

L2_DISTANCE#

L2_DISTANCE(vector1, vector2)

Euclidean distance, computed in single precision (32 bits). Error if the dimensions differ (message giving both dimensions).

COSINE_DISTANCE#

COSINE_DISTANCE(vector1, vector2)

Cosine distance, computed in single precision.

INNER_PRODUCT#

INNER_PRODUCT(vector1, vector2)

Dot product of the two vectors.

VECTOR_NEGATIVE_INNER_PRODUCT#

VECTOR_NEGATIVE_INNER_PRODUCT(vector1, vector2)

Negative of the dot product (useful for sorting by decreasing similarity with an index that minimizes).

L1_DISTANCE#

L1_DISTANCE(vector1, vector2)

Manhattan distance (sum of the absolute values of the component-wise differences).

VECTOR_DIMS#

VECTOR_DIMS(vector)

Number of components (variant of VECTOR_DIM from the "operator" family, which raises an error rather than returning NULL on an incorrect argument).

VECTOR_NORM#

VECTOR_NORM(vector)

Euclidean norm (length) of the vector.

Returns: DOUBLE.

L2_NORMALIZE#

L2_NORMALIZE(vector)

Vector scaled to a norm of 1 (each component divided by the norm); a zero vector is returned unchanged. Error on overflow of a 32-bit float.

Returns: VECTOR(n).

SUBVECTOR#

SUBVECTOR(vector, position, count)

Subvector of count components starting at position (1-based, like SUBSTRING). Error if count < 1 or if the requested range is empty.

VECTOR_ADD#

VECTOR_ADD(vector1, vector2)

Element-wise addition. Error on overflow.

VECTOR_SUB#

VECTOR_SUB(vector1, vector2)

Element-wise subtraction.

VECTOR_MUL#

VECTOR_MUL(vector1, vector2)

Element-wise multiplication. Also an error on loss of precision through underflow (a result of zero although neither factor was zero).

8.12 Advanced formatting#

Summary:

SFORMAT#

SFORMAT(format [, arg1, arg2, ...])

Formats arg1, arg2, ... according to format, in the manner of the C++ {fmt} library (syntax close to Python's str.format()). {} fields take the arguments in order; {n} takes the argument at index n (0-based) — the two numbering styles cannot be mixed in a single call. {{ and }} produce a literal brace.

Specification after : (optional): [[fill]alignment][sign][#][0][width][.precision][L][type], where width and precision can themselves come from an integer argument ({:{}}, {:.{2}}). Accepted types depending on the nature of the argument: d x X o b B c for an integer; e E f F g G a A for a floating-point number; s ? for a text; with no type, the shortest form that yields the same number when read back. L groups thousands digits with ,.

Behavior with NULL: a NULL numeric argument is treated as 0; a NULL text argument, or a NULL format itself, returns NULL for the whole call. Any format error (invalid specification, missing argument, unmatched brace, switching numbering style mid-format) also returns NULL, rather than raising an error — as does a result that would exceed max_allowed_packet.

Returns: text.

SELECT SFORMAT('{} is {:.2f} ({:#x})', 'Pi', 3.14159, 255);
-- 'Pi is 3.14 (0xff)'

SELECT SFORMAT('{0} then {1} then {0}', 'A', 'B');
-- 'A then B then A'

SELECT SFORMAT('{:L}', 1234567);
-- '1,234,567' (the 'L' type groups thousands)

8.13 Window functions#

A window function computes a value for each row by looking at the other rows of its partition, without grouping them (unlike GROUP BY). It is written:

function(...) OVER ([PARTITION BY expr, ...] [ORDER BY expr [ASC|DESC], ...] [frame])

• PARTITION BY splits the rows into independent partitions; without it, all rows form a single partition.
• ORDER BY sets the order of the rows within the partition. Rows that have the same key value are ties ("peers").
• Frame: ROWS or RANGE, in the form ROWS start or ROWS BETWEEN start AND end, with the bounds UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING. ROWS accepts all bounds; RANGE accepts only UNBOUNDED and CURRENT ROW (the ties of the current row are then part of the frame); RANGE with an n PRECEDING/n FOLLOWING offset returns the "not supported" error (1235). A start of UNBOUNDED FOLLOWING is a syntax error (1064).
• Default frame (no frame written): with ORDER BY, from the start of the partition to the current row and its ties (RANGE UNBOUNDED PRECEDING); without ORDER BY, the entire partition.
• The window is evaluated after WHERE, GROUP BY and HAVING (so it can operate on aggregates: SUM(SUM(x)) OVER (ORDER BY g)) and before the query's ORDER BY/LIMIT; filtering on its result is done in an enclosing query (derived table). It can also appear in an ORDER BY.
• Common errors: window function in a WHERE or nested inside another window function/aggregate (1111); wrong number of arguments (1582); ordinary scalar function followed by OVER (for example UPPER(x) OVER (...)) or DISTINCT inside a window function (1235, "not supported").

ROW_NUMBER#

ROW_NUMBER() OVER (...)

Number of the row within its partition, starting at 1, according to the window's ORDER BY. Ties receive distinct numbers (in their read order, which is not guaranteed). No arguments (otherwise error 1582).

SELECT id, ROW_NUMBER() OVER (PARTITION BY g ORDER BY d) FROM T;

RANK#

RANK() OVER (...)

Rank of the row: 1 + number of strictly preceding rows. Ties share the same rank and the next rank is skipped (1, 2, 2, 4). Without ORDER BY, all rows are 1.

DENSE_RANK#

DENSE_RANK() OVER (...)

Like RANK but without gaps: 1, 2, 2, 3.

SELECT id, RANK() OVER (ORDER BY d), DENSE_RANK() OVER (ORDER BY d) FROM T;
-- d = 1, 2, 2, 3  ->  RANK = 1, 2, 2, 4 ; DENSE_RANK = 1, 2, 2, 3

PERCENT_RANK#

PERCENT_RANK() OVER (...)

(rank - 1) / (number of rows in the partition - 1), between 0 and 1. Equals 0 for a single-row partition. Returns: DOUBLE.

CUME_DIST#

CUME_DIST() OVER (...)

Cumulative distribution: number of rows up to the last tie of the current row, divided by the size of the partition (in ]0, 1]). Returns: DOUBLE.

NTILE#

NTILE(n) OVER (...)

Splits the ordered partition into n buckets of as equal a size as possible and returns the bucket number (starting at 1) of the row; when the division is not exact, the first buckets receive one more row. n must be a constant integer greater than or equal to 1: NTILE(0), NTILE(NULL) or a non-constant value (column) return error 1210.

SELECT id, NTILE(3) OVER (ORDER BY id) FROM T;   -- 7 rows: 1,1,1,2,2,3,3

LAG / LEAD#

LAG(expr [, offset [, default]]) OVER (...)
LEAD(expr [, offset [, default]]) OVER (...)

Value of expr in the row located offset rows before (LAG) or after (LEAD) the current row in the ordered partition. offset is 1 by default; it is a constant integer greater than or equal to 0 (0 designates the current row; negative, NULL or non-constant value: error 1210). Outside the partition, the result is default (converted to the result type), or NULL if omitted. The window frame has no effect on these functions. A NULL value of expr in the targeted row is returned as is. At most three arguments (otherwise 1582).

SELECT id, LAG(v) OVER (ORDER BY id), LEAD(v, 1, -1) OVER (ORDER BY id) FROM T;

FIRST_VALUE / LAST_VALUE#

FIRST_VALUE(expr) OVER (...)
LAST_VALUE(expr) OVER (...)

Value of expr in the first, respectively the last, row of the frame. Note: with the default frame (ending at the current row and its ties), LAST_VALUE returns the value of the last tie of the current row; to get the last row of the partition, write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. NULL if the frame is empty. A NULL value is not skipped.

SELECT id, FIRST_VALUE(id) OVER (PARTITION BY g ORDER BY d DESC, id) FROM T;

NTH_VALUE#

NTH_VALUE(expr, n) OVER (...)

Value of expr in the n-th row (starting at 1) of the frame; NULL if the frame has fewer than n rows. n is a constant integer greater than or equal to 1 (otherwise 1210). Exactly two arguments (otherwise 1582).

SELECT id, NTH_VALUE(id, 2) OVER (PARTITION BY g ORDER BY d DESC, id
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM T;

Window aggregates#

COUNT, SUM, AVG, MIN, MAX, STD, STDDEV, STDDEV_POP and VARIANCE accept an OVER clause and are then computed over each row's frame, with the same type and NULL rules as in 8.5 (NULLs are ignored; SUM, AVG, MIN, MAX, STD, VARIANCE return NULL on a frame with no non-NULL value, COUNT returns 0). COUNT(*) OVER (...) is accepted.

Not supported with OVER (error 1235): GROUP_CONCAT, JSON_ARRAYAGG, BIT_AND/BIT_OR/BIT_XOR, STDDEV_SAMP, VAR_POP, VAR_SAMP, DISTINCT in the argument, and SUM/AVG/MIN/MAX on VECTOR(n) vectors.

SELECT id, SUM(v) OVER (PARTITION BY g ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T;  -- running total
SELECT id, SUM(d) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM T;                        -- moving average