RANDOM_BYTES implementation for MariaDB < 10.10.0

mariadb_RANDOM_BYTES.sql

DROP FUNCTION RANDOM_BYTES;

DELIMITER $$

CREATE FUNCTION RANDOM_BYTES(length INT) RETURNS VARBINARY(1024)
NOT DETERMINISTIC NO SQL
BEGIN
    DECLARE output VARBINARY(1024);
    IF length < 1 OR length > 1024 THEN
        RETURN NULL;
    END IF;
    -- XXX: SELECT CONCAT(NULL, '123') -> RETURNS NULL
    SET output = '';
    WHILE LENGTH(output) < length DO
        -- Make sure 1024 / UNHEX(...) is dividable
        -- 32 random bytes at a time
        SET output = CONCAT(output, UNHEX(SHA2(RAND(), 256)));
    END WHILE;
    RETURN LEFT(output, length);
END$$

DELIMITER ;


-- length in range
SELECT RANDOM_BYTES(15) AS t;
SELECT RANDOM_BYTES(16) AS t;

-- length < 1, NULL
SELECT RANDOM_BYTES(0) AS t;
-- length > 1024, NULL
SELECT RANDOM_BYTES(1025) AS t;

-- length in range, corner cases
SELECT RANDOM_BYTES(1) AS t;
SELECT RANDOM_BYTES(1024) AS t;