New Features In MySQL 6.x

The two big developments in the SQL world this summer are the pending arrival of the SQL:2008 ANSI standard, and my (Peter Gulutzan’s) attendance on Sun’s behalf at an ANSI meeting. Okay, I concede that the events have unequal importance, but sometimes I slip in a bit about personal involvements.

What’s a standard?

Review: ANSI (which stands for “INCITS Technical Committee H2 on Database”), and its counterparts in many other countries, produce official standards for many things, including SQL. The international co-ordinating body is ISO (International Organization for Standards), so there’s an ANSI standard and an ISO standard, and they’re practically the same. There’s a partially-restricted site containing ISO papers here.

The current edition is ANSI/ISO SQL:2003. But SQL:2008 is only a few days or weeks away. I’m taking a risk here by jumping the gun and saying something about it this early, but I must take the risk, there’s a time constraint that forces me to state this week what we Sun folk know about SQL:2008 and what we’re doing about it.

Differences between SQL:2003 and SQL:2008

SQL is stable. We can expect new editions to have only small changes. As with any product, the changes are of two types: fixes and new features.

FIXES. Quite a few bugs are now corrected or at least some clarification has happened. They’re of no great importance now that they’re dead, but I’d like to (ahem) just observe that a small number of them were found by MySQL AB personnel. I’m proud that we played a part, however trivial, and glad that the standard editor took an “open source” attitude when deciding whether to incorporate the things I sent him.

NEW FEATURES. There’s a section at the end of Part II called Appendix F SQL feature taxonomy. By comparing the SQL:2003 and SQL:2008 files I was able to see that around 60 small non-core optional features are new. Everybody will have a different idea which ones matter. My own choice is the ones that look relevant to MySQL.

F123 all diagnostics. Allows “GET DIAGNOSTICS target = ALL”. Support for GET DIAGNOSTICS isn’t in our plans for real soon now, but it’s inevitable. The mysqlforge task description is WL#2111 Stored Procedures: Implement GET DIAGNOSTICS.
F200 TRUNCATE TABLE statement. See the earlier article “TRUNCATE now and unto ages of ages”.
F382 alter column data type. Allows “ALTER … SET DATA TYPE data type”. MySQL can already alter data type, but with non-standard syntax “ALTER … MODIFY …”.
F763 CURRENT_SCHEMA. Allows “SELECT CURRENT_SCHEMA …”. MySQL can already select this, but with non-standard syntax “SELECT DATABASE() …”. See the feature request in MySQL’s bugs database, “Please implement a CURRENT_DATABASE()/CURRENT_SCHEMA() function”.
F852 Top-level order by clause in views. Allows “CREATE VIEW … ORDER BY …”. MySQL can already do this.
F857 Top-level fetch first clause in query expression; F865 offset row count in result offset clause. Allows picking how many rows to select, and where to start, with “SELECT … OFFSET … FIRST | NEXT”. MySQL can already pick, but with non-standard syntax “SELECT … LIMIT …”.
T021 BINARY and VARBINARY data types. See the earlier article: “Tiny Tweak: BINARY and VARBINARY”.
T023 Compound binary literals. Allows concatenation of hexits in binary string literals. This isn’t in MySQL’s plans, though we’ve talked about it.
T213 INSTEAD OF triggers. If you design an INSTEAD OF trigger on a view, then delete from the view, the rows aren’t deleted but the trigger is activated — and the trigger might delete the rows that you want. We’ve had a customer request for this.
T285 Enhanced derived column names. This clears up some rules for automatic assignment of column names in a select list, when there’s no AS clause.

I’ll add as well that there’s a reference to the “current” Unicode standard. That’s not a change in the wording, but the meaning of “current” is now Unicode 5.1. Well, we’ve just added support for Unicode 5.0 in MySQL 6.0. Keeping up with other standards can muss our scheduling, but we’ve added a worklog task for Unicode 5.1 and we’ll see what happens to it. See the earlier article “MySQL 6.0 Feature #1: Supplementary Characters”.

Joining the committee

The work on the standard will continue, so that there will be fixes and feature changes for a new standard in a few years. The interesting question now facing us is: Will Sun Microsystems join ANSI?

A few weeks ago some committee members were kind enough to invite me to sit in as an observer for one of the regular ANSI meetings. I learned a lot about the mechanics and interrelationships (though I have no scoops about new happenings that aren’t public). It was gently noted a few times that here was a gathering of all the behemoths of the database industry, except one: Sun.

Well, it was natural for MySQL to stay out. (Time to confess now: we weren’t a behemoth, more a micro-behemoth.) Before the acquisition, we were a relatively small outfit and relatively cloutless. Sun is a different sort of organization, though. And Sun does join other committees, for example it plays a big role with Unicode and (duh!) Java standards. Equally importantly, Sun is serious about standards to an extent that some MySQL old-timers weren’t. Probably this has a lot to do with the influence of big enterprise customers in the new field that we’re playing in.

This attitude will I hope be useful in other points of MySQL 6.x development, and I’ll keep you posted about “MySQL and progress toward the SQL standard” in this blog. But I’m going to emphasize that the attitude has an effect on a lot more than MySQL. Sun does JavaDB. Sun even does PostgreSQL … it would be interesting to join the committee and act as a conduit for another open-source DBMS’s suggestions under Sun’s aegis. But any of Sun’s many PostgreSQL experts could do that better than a MySQL expert could.

I’m mildly warm to the concept of Sun joining. But now it’s time for a boilerplate disclaimer: “Any opinions in this blog do not represent the opinions of anyone official at Sun Microsystems.” The only thing I’m saying is that we’re thinking about it, and gathering opinions.

As of July 1 all Canadian MySQLers are employees of Sun Microsystems. I was expecting that we would be the rear guard, I was going to greet the great day singing “a broken man on an Edmonton pier / the last of Monty’s engineers” (with thanks to the writer of “Barrett’s privateers”), or perhaps “And I, the last, go forth companionless … Among new men, strange faces, other minds.” (with thanks to Mr Tennyson). But as things turned out, the Ukrainians and the French were delayed. So Trudy Pelzer and I were the last Canadian engineers to come on board, but not quite last of all.

And it’s been wonderful. A free lunch, a tour of Sun’s Edmonton office, a SafeWord security doohickey, a carrying case, and an alternative address = peter.gulutzan@sun.com. More seriously, I’ve gained several thousand new co-workers while keeping the old ones (almost none of the MySQL engineers didn’t join Sun, and I know of none that intend to leave now that they’ve joined Sun). This is one of those friendly acquisitions where everything has just worked, and my hat is off to the integration teams who’ve kept us all in a high-morale gung-ho cloud. The new company is an even better friend of open source than the old, the number of intelligent people I’ve encountered is even greater, and the European level of politeness — always one of MySQL AB’s best points — hasn’t been polluted at all, touch wood. I highly recommend a Sun job for connoisseurs of tasteful engineering positions.