News About SQL

MariaDB has come closer to the standards for SQL commands, as well. Now, DELETE statements that reference themselves can delete rows (Listing 8) instead of provoking the error:

01 SQL> CREATE TABLE customer (
02 id INT UNSIGNED
03 , year SMALLINT UNSIGNED
04 , revenue DECIMAL(11, 2)
05 , customer_class CHAR(1)
06 );
07
08 SQL> INSERT INTO customer
09 VALUES (1, 2016, 100.0), (2, 2016, 0.0), (3, 2016, 999.99);
10 SQL> INSERT INTO customer
11 VALUES (1, 2017, 500.0), (2, 2017, 0.0), (3, 2017, 100.00);
12 SQL> INSERT INTO customer
13 VALUES (1, 2018, 400.0), (2, 2018, 0.0), (3, 2018, 0.0);
14
15 SQL> SELECT * FROM customer;
16 +---+------+---------+
17 | id| year | revenue |
18 +---+------+---------+
19 | 1 | 2016 | 100.00  |
20 | 2 | 2016 |   0.00  |
21 | 3 | 2016 | 999.99  |
22 | 1 | 2017 | 500.00  |
23 | 2 | 2017 |   0.00  |
24 | 3 | 2017 | 100.00  |
25 | 1 | 2018 | 400.00  |
26 | 2 | 2018 |   0.00  |
27 | 3 | 2018 |   0.00  |
28 +---+------+---------+
29
30 SQL> DELETE FROM customer
31 WHERE id IN (SELECT id FROM customer GROUP BY id HAVING SUM(revenue) = 0.0);

ERROR 1093 (HY000): You can't specify target table 'customer' for update in FROM clause

The same applies to UPDATE statements. Recently, ORDER BY and LIMIT clauses have been added to multitable updates. Besides the existing UNION and UNION ALL operators, MariaDB now also knows EXCEPT (MINUS) and INTERSECT (Listing 9).

01 SQL> CREATE TABLE customer (
02 first_name VARCHAR(33)
03 , last_name VARCHAR(55)
04 );
05
06 SQL> CREATE TABLE user (
07 first_name VARCHAR(33)
08 , last_name VARCHAR(55)
09 );
10
11 SQL> INSERT INTO customer VALUES ('Hans', 'Meier'), ('Sepp', 'Müller'),
12 ('Fritz', 'Huber');
13 SQL> INSERT INTO user VALUES ('Oman', 'Klept'), ('Hans', 'Meier'), ('Getfor'),
14 'Free');
15
16 # In the first case, we want to identify all users who are not customers:
17
18 SQL> SELECT first_name, last_name FROM user
19 EXCEPT
20 SELECT first_name, last_name FROM customer
21 ;
22 +------------+-----------+
23 | first_name | last_name |
24 +------------+-----------+
25 | Oman       | Klept     |
26 | Getfor     | Free      |
27 +------------+-----------+
28
29 # And in the second case we want to know who is user and customer:
30
31 SQL> SELECT first_name, last_name FROM user
32 INTERSECT
33 SELECT first_name, last_name FROM customer
34 ;
35 +------------+-----------+
36 | first_name | last_name |
37 +------------+-----------+
38 | Hans       | Meier     |
39 +------------+-----------+

Previously, these operations were managed with somewhat more complicated JOIN constructs, but thanks to the two new clauses, the code looks much more intuitive and simpler. The non-standard MINUS, which corresponds to EXCEPT and is familiar to Oracle developers, is unfortunately not yet supported by MariaDB.

System-Versioned Tables

Besides numerous other small improvements in the SQL layer, the new system-versioned tables feature stands out. Defined by the SQL:2011 standard, this feature stores the entire history of a row from creation to deletion, which means you can analyze data for a specific point in time or track changes during an audit. The command

SQL> CREATE TABLE accounting (
name VARCHAR(55),
amount DECIMAL(11, 2),
`date` DATE
) WITH SYSTEM VERSIONING;

creates a versioned table, to which you can now enter data:

SQL> UPDATE accounting SET amount = amount + 500.0, `date` = '2018-03-13' WHERE name = 'Offenders';

At a later date, it will be possible to see exactly what the account balance of Offenders was at a certain point in time (e.g., March 1) (Listing 10).

SQL> SELECT * FROM accounting FOR SYSTEM_TIME AS OF TIMESTAMP '2018-03-01 00:00:00';
+-----------+---------+------------+
| name      | amount  | date       |
+-----------+---------+------------+
| Employees |  600.00 | 2018-02-25 |
| Chief     | 1200.00 | 2018-02-25 |
| Offenders |  600.00 | 2018-02-25 |
+-------------+---------+----------+

Further options for limiting the time range include FROM … TO … or BETWEEN … AND … . MariaDB also offers the possibility of querying the rows in a transaction-specific way with the trxid marker. An existing table can be converted to system versioning with:

SQL> ALTER TABLE accounting ADD SYSTEM VERSIONING;

Be careful when backing up: If you save with mysqldump, the entire history is lost. Therefore, you should use a physical backup method such as the mariabackup tool.

Other New Features

Storage engine-independent column compression has also been added. This feature especially reduces the footprint of very large columns (e.g., BLOB, TEXT, VARCHAR, VARBINARY, JSON):

SQL> CREATE TABLE mail (
 subject VARCHAR(255)
, body VARCHAR(255)
, attachment LONGBLOB COMPRESSED
, metadata JSON COMPRESSED
);

Indexing compressed columns is not possible.