10.7 feature preview: Miscellaneous features

Written by Written by Eric Herman

The “Misc features” preview includes all the other features that did not make it into a separate dedicated preview binary.

MariaDB Server 10.7 includes the JSON_EQUALS function, which compares inputs as JSON objects, regardless of whitespace, key order, or number format.

For instance, as you would expect, these two strings will evaluate as equal JSON objects:

> SELECT JSON_EQUALS(
'{"name":"alice","color":"blue"}'
,'
{
    "color": "blue",
    "name":  "alice"
}
');

While handy in rare situations, JSON_EQUALS alone may not be very interesting, as it may not be very often that our application wants to test for equality of two values using SQL inside the database.

However, we may wish our application to use the database to enforce a unique constraint on the JSON contents, and we can do so using the JSON_NORMALIZE function in combination with a unique key.

For example, if we have a table with a JSON column:

> CREATE TABLE t1 (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 val JSON,
 /* other columns here */
 PRIMARY KEY (id)
);

In MariaDB 10.7, we can add a unique constraint using JSON_NORMALIZE like this:

> ALTER TABLE t1
   ADD COLUMN jnorm JSON AS (JSON_NORMALIZE(val)) VIRTUAL,
   ADD UNIQUE KEY (jnorm);

We can test this by first inserting a row as normal:

> INSERT INTO t1 (val) VALUES ('{"name":"alice","color":"blue"}');

And then seeing what happens with a different string which would produce the same JSON object:

> INSERT INTO t1 (val) VALUES ('{ "color": "blue", "name": "alice" }');

ERROR 1062 (23000): Duplicate entry '{"color":"blue","name":"alice"}' for key 'jnorm'

The JSON_EQUALS and JSON_NORMALIZE functions of MariaDB 10.7 aim to make using JSON and an SQL database a little more natural for developers of modern web apps.

String comparison in 10.7 is up to three times faster. It is particularly important for the utf8 character set, used for various identifiers (e.g. table and column names), as they are compared many times during the course of execution of every single query. See the benchmark results in MDEV-26572.

If you use system versioned tables, you might be happy to know that in 10.7 you can create a table dump for a versioned table at any specific point in time:

mysqldump --as-of="2020-09-28 00:00" my_db my_versioned_table

In 10.7 we applied a contributed patch (thank you, woqutech) that implements MySQL-compatible CHANNEL syntax for our own multi-source replication feature.

You might have used GET DIAGNOSTICS before. After an error or a warning it makes it possible to retrieve programmatically the sqlstate, the error number and text. This is particularly useful in condition handlers in stored routines. For example,

GET DIAGNOSTICS CONDITION 2 @sqlstate = RETURNED_SQLSTATE,
  @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

SELECT @sqlstate, @errno, @text;
+-----------+--------+----------------------------------------+
| @sqlstate | @errno | @text                                  |
+-----------+--------+----------------------------------------+
| 01000     |   1265 | Data truncated for column 'c' at row 2 |
+-----------+--------+----------------------------------------+

But until now there was no way, short of parsing the error text, to know in what row the error has happened. In 10.7 one can use an ERROR_INDEX property to retrieve the row number too. Even if the error text does not mention it:

INSERT INTO t1 VALUES (4,'d',1.00104),(1,'a',1.00101),(2,'b',1.00102);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'

GET DIAGNOSTICS CONDITION 1 @row_num= ERROR_INDEX;

SELECT @row_num;
+----------+
| @row_num |
+----------+
|        2 |
+----------+

Try the new features out by downloading and installing the tarball.

Feedback welcome

If you come across any problems in this feature preview, with the design, or edge cases that don’t work as expected, please let us know with a JIRA bug/feature request on the MDEV project. You are welcome to chat about it on Zulip.