How UNCOMPRESS() Works in MariaDB

In MariaDB, UNCOMPRESS() is a built-in function that uncompresses a string that’s been compressed by the COMPRESS() function.

Syntax

The syntax goes like this:

UNCOMPRESS(string_to_uncompress)

Where string_to_uncompress is the string you want to uncompress.

Example

Here’s a basic example:

SELECT UNCOMPRESS(COMPRESS('Marching on'));

Result:

+-------------------------------------+
| UNCOMPRESS(COMPRESS('Marching on')) |
+-------------------------------------+
| Marching on                         |
+-------------------------------------+

Checking the Size

Here’s a basic example:

SELECT
    LENGTH(REPEAT('z', 50)) AS "Uncompressed",
    LENGTH(COMPRESS(REPEAT('z', 50))) AS "Compressed",
    LENGTH(UNCOMPRESS(COMPRESS(REPEAT('z', 50)))) AS "Uncompressed";

Result:

+--------------+------------+--------------+
| Uncompressed | Compressed | Uncompressed |
+--------------+------------+--------------+
|           50 |         16 |           50 |
+--------------+------------+--------------+

Here, I used the LENGTH() function to get the length in bytes of the uncompressed and compressed strings respectively. I used the REPEAT() function to repeat the same letter 50 times before compressing it.

Here’s another example that uses the copyright symbol (©), which is a two-byte character:

SELECT
    LENGTH(REPEAT('©', 50)) AS "Uncompressed",
    LENGTH(COMPRESS(REPEAT('©', 50))) AS "Compressed",
    LENGTH(UNCOMPRESS(COMPRESS(REPEAT('©', 50)))) AS "Uncompressed";

Result:

+--------------+------------+--------------+
| Uncompressed | Compressed | Uncompressed |
+--------------+------------+--------------+
|          100 |         17 |          100 |
+--------------+------------+--------------+

Here’s one that uses a three-byte character:

SELECT
    LENGTH(REPEAT('อ', 50)) AS "Uncompressed",
    LENGTH(COMPRESS(REPEAT('อ', 50))) AS "Compressed",
    LENGTH(UNCOMPRESS(COMPRESS(REPEAT('อ', 50)))) AS "Uncompressed";

Result:

+--------------+------------+--------------+
| Uncompressed | Compressed | Uncompressed |
+--------------+------------+--------------+
|          150 |         18 |          150 |
+--------------+------------+--------------+

Null Arguments

Passing null returns null:

SELECT UNCOMPRESS(null);

Result:

+------------------+
| UNCOMPRESS(null) |
+------------------+
| NULL             |
+------------------+

Missing Argument

Calling UNCOMPRESS() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT UNCOMPRESS();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'UNCOMPRESS'

Check for Compression

The UNCOMPRESS() function requires MariaDB to have been compiled with a compression library such as zlib. If MariaDB hasn’t been compiled with such a compression library, UNCOMPRESS() returns null.

You can check the have_compress system variable to see whether or not the zlib compression library is accessible to the server:

SELECT @@have_compress;

Result:

+-----------------+
| @@have_compress |
+-----------------+
| YES             |
+-----------------+

In this case, the zlib compression library is accessible to the server, and the result is YES.

If the zlib compression library is not accessible to the server, the result would be NO.