A glance at a MariaDB release candidate
Today I looked at the MariaDB Release Candidate wondering how my earlier predictions came out.
I predicted, for “roles”:
For all of the bugs, and for some of the flaws, there’s no worry — they’ll probably disappear.
In fact all the bugs are gone, and I belatedly realized (after some gentle nudges from a MariaDB employee) that some of the flaws weren’t flaws.
The inability to grant to PUBLIC still troubles me, but it looks like roles are ready to roll.
I predicted for “mroonga”:
At the time I’m writing this, MariaDB 10.0.8 doesn’t have mroonga yet.
In other words, I thought it would be in 10.0.8. It was not to be, as another MariaDB employee told me:
Unfortunately, mroonga appeared to have some portability problems. Most likely it won’t be in 10.0.8. There is a good chance it will be 10.0.9 though.
While I was looking, I tried out a new clause for the DELETE statement:
DELETE FROM t RETURNING select-list;
which returns the rows that just got deleted. It’s a new non-standard clause which increases MariaDB’s divergence from MySQL, so decent people should demand an explanation.
Oracle 12c looks similar but does something different:
DELETE FROM t RETURN|RETURNING expr [,expr...] INTO data-item [,data-item...];
SQL Server 2012 has a clause which looks different but does something similar:
DELETE FROM t OUTPUT select-list;
PostgreSQL has a clause which looks similar and does something similar:
DELETE FROM t RETURNING select-list;
So we’re seeing, yet again, the belief that PostgreSQL is a better model for MariaDB than the big boys are. By the way, the big boys are a bit more orthogonal — I’ve seen extensions similar to this one in UPDATE or MERGE statements. But the bigger orthogonality woe is that here we have a statement that returns a result set, but can’t be used in all the places where a result-set-returning sub-statement could work, like DECLARE CURSOR FOR DELETE, or INSERT … DELETE. Of course, I’m glad they don’t work. My point is only that now-it-works-now-it-doesn’t additions make the product look haphazard.
Update: How it’s done in standard SQL
This section was added on 2014-03-01, one week after the original post.
SQL:2011 has a non-core (optional) feature T495 “Combined data change and retrieval”. Ignoring a lot, the syntax is SELECT … FROM NEW|OLD|FINAL TABLE (INSERT|UPDATE|DELETE …) …. For example
SELECT column1+5 FROM OLD TABLE (DELETE FROM t) AS t WHERE f = 77;
In this example table t is a “delta table”.
There’s an example with DECLARE CURSOR and FETCH in the DB2 10 for z/OS manual.
Apparently SELECT … FOR UPDATE is just too hard to use. Oh, yeah, people still care about MyISAM.
We will continue to see “MySQLisms” appear until non-transactional storage engines go the way of the dodo.
I would have much preferred Oracle RETURNING syntax be supported for DELETE and UPDATE and having it just for delete is rather useless, and the syntax which is supported is limiting IMHO.
> and the syntax which is supported is limiting IMHO.
The DML returning clause used by Postgres is much more powerful than the limited implementation that Oracle offers. Having to store the returned values into a variable, limits its usage to stored procedures and to single row deletes. If you delete multiple rows, you can’t use the returning clause in Oracle.
The “result set” nature of the Postgres implementation allows for the following though:
with deleted_rows as (
delete from current_table
where …
returning *
)
insert into archive_table
select *
from deleted_rows;
The above “moves” rows from the current_table to the archive_table in a single statement.
With Oracle’s implementation this would require a PL/SQL block using a cursor doing row-by-row deletes and inserts – much slower.
Btw: the returning clause is nothing new to Oracle 12c, it has been around at least since 10g (maybe even 9i)
One could revert the problem: if all DBMS’s support a variant of DELETE/UPDATE RETURNING, why is it not standard?
I probably agree that some of the syntaxes you mentioned don’t work. Except that… I don’t like inconsistencies, probably because I tend to forget them. I’d like to see any statement returning a resultset allowed with INSERT, CREATE TABLE, cursors…
Just an opinion, of course.