The First Development Milestone for MySQL 8.0

MySQL 8.0.0 exists.

For general impressions we already have comments by Giuseppe Maxia and Stewart Smith and Serdar Yegulalp.

Two new features looked important to me: modern UCA collations, and roles. So I downloaded and tried them out.

Modern UCA collations

MySQL is going to switch to utf8mb4 for the default character set and add collations based on the the latest version of the Unicode Collation Algorithm (UCA 9.0.0). I still see messages indicating the default is still latin1, but they're incorrect, I can put 4-byte UTF-8 characters in columns that I created without explicitly saying utf8mb4.

The new collations are only for utf8mb4. That's suboptimal. People still have good reasons to use other character sets (I discussed some of them in an earlier blog post). And in any case, a collation that works for utf8mb4's repertoire will work for every character set that has a pure subset of that repertoire, which is to say, every character set.

The new collations are only for the generic "Default Unicode Collation Element Table" (DUCET), and for Latin-based alphabets. So there are no updates for "persian" or "sinhala".

For an example, the following table shows changes between the old Swedish collation (utf8mb4_swedish_ci) and the new one (utf8mb4_sv_0900_ai_ci). The "Rule" column has what Unicode says about certain Swedish primary (level-1) comparisons, the "Example" column has what an SQL comparison would look like, the "Old" column has the results I got with utf8mb4_swedish_ci, the "New" column has the results I got with utf8mb4_sv_0900_ai_ci.

Rule Example Old New
---------------------------- --------- ----- ----

Most Swedes don't know about these rules, they apply to medieval texts or foreign names. But most Swedes do know that rules should cover the edge cases, not just the Stockholm phone book. Because it follows the Unicode rules, the new collation is better.

But the new collation's name is worse, for two reasons.

(1) The "_ai" suffix, meaning "accent insensitive", is Microsoftish. There is such a thing, but the definition of "accent" varies between languages and the factors that influence collations can be other things besides accents. Clearer suffixes for extra-sensitive collation names would be "w2" or "l2" (for weight=2 or level=2), and they're for sorting rather than searching unless you're Japanese, but a default = no-suffix-for-accents would have been okay.

(2) The "_sv" suffix, meaning "Swedish", is an unnecessary change. Compatibility with the previous suffix -- "swedish" -- would not have violated UCA specifications and would have been clearer for people who have used MySQL before.

For a second example, I looked at the new "Latin" collation, utf8mb4_la_0900_ai_ci. This time I couldn't find any rules file in the Unicode standard directory. There is a UCA chart for Latin but utf8mb4_la_0900_ai_ci obviously isn't following it at all. Instead it's like MySQL's old and silly "Roman" collation, where i=j and u=v. This is not an important collation. But MySQL claims the new collations follow UCA rules, and here is one that doesn't, so I worry about the others.

This has to be taken in context -- MySQL has far better support for character sets and collations than any other open-source DBMS, except sometimes MariaDB. And now it's a weenie bit more far better. Observations about paucity of new UCA collations, bad names, or standard non-compliance won't change that fact.


I discussed MariaDB's roles in 2014. MySQL's roles are already in the 8.0 documentation. Is MySQL making an improvement?

The first thing I noticed is that the syntax rules for roles are, too often, the same as the syntax rules for users. This is especially obvious when I ask for things that make no sense for roles, for example:

mysql>CREATE ROLE 'r'@'host';
OK 0 rows affected (0.1 seconds)

mysql>CREATE ROLE '';
Error 1396 (HY000) Operation CREATE USER failed for anonymous user

mysql>GRANT 'root'@'localhost' TO role_name;
OK 0 rows affected (0.1 seconds)

mysql>DROP USER role_name;
OK 0 rows affected (0.1 seconds)

Because of this, some non-standard limitations exist: maximum name length is 32, names are case sensitive, role names cannot be the same as user names, and there is no separate information_schema table

However, the DML statements that I tested for MariaDB do work with MySQL as well, and are often exactly the same:

MySQL:   CREATE ROLE [IF NOT EXISTS] role_name [,role_name...];

MariaDB: DROP ROLE [IF EXISTS] role_name [,role_name...];
MySQL:   DROP ROLE [IF EXISTS] role_name [,role_name...];

MariaDB: SET DEFAULT ROLE {role_name|NONE} [FOR user_name];
MySQL:   SET DEFAULT ROLE ALL TO user_name [,user_name...];

MariaDB: SET ROLE {role_name|NONE};
MySQL:   SET ROLE {role_name|NONE};


MariaDB: [no exact equivalent]

MariaDB: SHOW GRANTS [FOR role_name];
MySQL:   SHOW GRANTS [FOR role_name];
MySQL:   SHOW GRANTS [FOR user_name USING role_name[,role_name...]];

MariaDB: GRANT role_name TO grantee [,grantee...] [WITH ADMIN OPTION];
MySQL:   GRANT role_name[,role_name...] TO grantee [,grantee...];

(The last GRANT example surprised me. MariaDB has trouble granting multiple roles in one statement, it's Bug#5772. MySQL appears to be "solving" it by making certain role names illegal unless they're delimited; I'm not sure that's the right way to solve it.)

Circular roles (GRANT r1 TO r2; GRANT r2 TO r1;) are allowed but I expect they'll be disallowed in a later version.


/* as a user with lots of privileges */
CREATE USER 'u'@'localhost';
GRANT r TO 'u'@'localhost';
/* as user 'u'@'localhost' */
/* The first SELECT succeeds, the second SELECT fails. */

To generalize: so far MySQL 8.0.0 allows creation of roles but they have to look like users. So the syntax is undesirable, but they work properly.

Again, remember the context. There's nothing wrong with a feature that's not ready, until MySQL declares that it's ready.


MySQL's announcement, buried in a section about minor fixes, says "Foreign key names as stored in the foreign_keys and foreign_key_column_usage tables are a maximum of 64 characters, per the SQL standard". Er, up to a point. The SQL standard says "In a regular identifier, the number of identifier parts shall be less than 128."

Us Too

We have a new-version announcement too. Version 1.0.3 of the Ocelot Graphical User Interface (ocelotgui) for MySQL and MariaDB came out on Tuesday September 27 2016. Some new items are ...

As well as getting result sets in the result-set widget, one can get them added to the history widget, with the same format as what the mysql client outputs.

As well as predicting what the next word should be, Ocelot's syntax recognizer makes it possible to show hints if the user hovers over a word.

Finally, there is a rudimentary formatter. Clicking the edit menu item Edit|Format will change indentation, make keywords upper case, etc. I say "rudimentary" because, without a standard to follow, one must depend on taste, and nobody shares the taste that's on display here.

Documentation is now on C++ source and Linux-ready packages are on

Binary Serializers

DBMS client applications need to store SQL query results in local memory or local files. The format is flat and the fields are ordered -- that's "serialization". The most important serializer format uses human-readable markup, like
[start of field] [value] [end of field]
and the important ones in the MySQL/MariaDB world are CSV (what you get with SELECT ... INTO OUTFILE or LOAD INFILE), XML (what you get with --xml or LOAD XML), and JSON (for which there are various solutions if you don't use MySQL 5.7).

The less important serializer format uses length, like
[length of value] [value]
and this, although it has the silly name "binary serialization", is what I want to talk about.

The length alone isn't enough, we also need to know the type, so we can decode it correctly. With CSV there are hints such as "is the value enclosed in quotes", but with binary serializers the value contains no hints. There has to be an indicator that says what the type is. There might be a single list of types for all of the records, in which case the format is said to "have a schema". Or there might be a type attached to each record, like
[type] [length of value] [value]
in which case the format is often called "TLV" (type-length-value).

Binary serializers are better than markup serializers if you need "traversability" -- the ability to skip to field number 2 without having to read every byte in field number 1. Binary TLV serializers are better than binary with-schema serializers if you ned "flexibility" -- when not every record has the same number of fields and not every field has the same type. But of course TLV serializers might require slightly more space.

A "good" binary serializer will have two Characteristics:
#1 It is well known, preferably a standard with a clear specification, but otherwise a commonly-used format with a big sponsor. Otherwise you have to write your own library and you will find out all the gotchas by re-inventing a wheel. Also, if you want to ship your file for import by another application, it would be nice if the other application knew how to import it.
#2 It can store anything that comes out of MySQL or MariaDB.

Unfortunately, as we'll see, Characteristic #1 and Characteristic #2 are contradictory. The well-known serializers usually were made with the objective of storing anything that comes out of XML or JSON, or that handled quirky situations when shipping over a wire. So they're ready for things that MySQL and MariaDB don't generate (such as structured arrays) but not ready for things that MySQL and MariaDB might generate (such as ... well, we'll see as I look at each serializer).

To decide "what is well known" I used the Wikipedia article Comparison of data serialization formats. It's missing some formats (for example sereal) but it's the biggest list I know of, from a source that's sometimes neutral. I selected the binary serializers that fit Characteristic #1. I evaluated them according to Characteristic #2.

I'll look at each serializer. Then I'll show a chart. Then you'll draw a conclusion.


Has schemas. Not standard but sponsored by Apache.

I have a gripe. Look at these two logos. The first one is for the defunct British/Canadian airplane maker A.V.Roe (from Wikipedia). The second one is for the binary serializer format Apache Avro (from their site).

Although I guess that the Apache folks somehow have avoided breaking laws, I think that taking A.V.Roe's trademark is like wearing medals that somebody else won. But putting my gripe aside, let's look at a technical matter.

The set of primitive type names is:
null: no value

Well, of course, in SQL a NULL is not a type and it is a value. This is not a showstopper, because I can declare a union of a null type and a string type if I want to allow nulls and strings in the same field. Um, okay. But then comes the encoding rule:

null is written as zero bytes.

I can't read that except as "we're like Oracle 12c, we think empty strings are NULLs".


TLV. Standard.

ASN means "abstract syntax notation" but there are rules for encoding too, and ASN.1 has a huge advantage: it's been around for over twenty years. So whenever any "why re-invent the wheel?" argument starts up on any forum, somebody is bound to ask why all these whippersnapper TLVs are proposed considering ASN.1 was good enough for grand-pappy, eh?

Kidding aside, it's a spec that's been updated as recently as 2015. As usual with official standards, it's hard to find a free-and-legitimate copy, but here it is: the link to a download of "X.690 (08/2015) ITU-T X.690 | ISO/IEC 8825-1 ISO/IEC 8825-1:2015 Information technology -- ASN.1 encoding rules: Specification of Basic Encoding Rules (BER), Canonical Encoding Rules (CER) and Distinguished Encoding Rules (DER)" from the International Telecommunication Union site:

It actually specifies how to handle exotic situations, such as
** If it is a "raw" string of bits, are there unused bits in the final byte?
** If the string length is greater than 2**32, is there a way to store it?
** Can I have a choice between BMP (like MySQL UCS2) and UTF-8 and other character sets?
** Can an integer value be greater than 2**63?
... You don't always see all these things specified except in ASN.1.

Unfortunately, if you try to think of everything, your spec will be large and your overhead will be large, so competitors will appear saying they have something "simpler" and "more compact". Have a look at to see how ASN.1 once did bestride the narrow world like a colossus, but nowadays is not more popular than all the others.


TLV. Sponsored by MongoDB.

Although BSON is "used mainly as a data storage and network transfer format in the MongoDB [DBMS]", anybody can use it. There's a non-Mongo site which refers to independent libraries and discussion groups.

BSON is supposed to make you think "binary JSON" but in fact all the binary serializers that I'm discussing (and I few that I'm not discussing such as UBJSON) can do a fair job of representing JSON-marked-up-text in binary format. Some people even claim that MessagePack does a better job of that than BSON does.

There is a "date" but it is milliseconds since the epoch, so it might be an okay analogue for MySQL/MariaDB TIMESTAMP but not for DATETIME.


TLV. Proposed standard.

CBOR is not well known but there's an IETF Internet Standards Document for it (RFC 7049 Concise Binary Object Representation), so I reckoned it's worth looking at. I don't give that document much weight, though -- it has been in the proposal phase since 2013.

The project site page mentions JSON data model, schemalessness, raw binary strings, and concise encoding -- but I wanted to see distinguishing features. There are a few.

I was kind of surprised that there are two "integer" types: one type is positive integers, the other type is negative integers.
In other words -5 is
[type = negative number] [length] [value = 5]
rather than the Two's Complement style
[type = signed number] [length] [value = -5]
but that's just an oddness rather than a problem.

There was an acknowledgment in the IETF document that "CBOR is inspired by MessagePack". But one of MessagePack's defects (the lack of a raw string type) has been fixed now. That takes away one of the reasons that I'd have for regarding CBOR as a successor to MessagePack.

Fast Infoset

TLV. Uses a standard.

After seeing so much JSON, it's nice to run into an international standard that specifies a binary encoding format for the XML Information Set (XML Infoset) as an alternative to the XML document format". Okay, they get points for variety.

However, it's using ASN.1's underlying encoding methods, so I won't count it as a separate product.


TLV. Not standard but widely used.

MessagePack, also called MsgPack, is popular and is actually used as a data storage format for Pinterest and Tarantool.

It's got a following among people who care a lot about saving bytes; for example see this Uber survey where MessagePack beat out some of the other formats that I'm looking at here.

One of the flaws of MessagePack, from my point of view, is its poor handling for character sets other than UTF-8. But I'll admit: when MessagePack's original author is named Sadayuki Furuhashi, I'm wary about arguing that back in Japan UTF-8 is not enough. For some of the arguing that happened about supporting other character sets with MessagePack, see this thread. Still, I think my "The UTF-8 world is not enough" post is valid for the purposes I'm discussing.

And the maximum length of a string is 2**32-1 bytes, so you can forget about dumping a LONGBLOB. I'd have the same trouble with BSON but BSON allows null-terminated strings.


TLV. Sort of a standard for a particular industry group.

Open Platform Communications - Unified Architecture has a Binary Encoding format.

Most of the expected types are there: boolean, integer, float, double, string, raw string, and datetime. The datetime description is a bit weird though: number of 100 nanosecond intervals since January 1, 1601 (UTC). I've seen strange cutover dates in my time, but this is a new one for me.

For strings, there's a way to indicate NULLs (hurrah).

I have the impression that OPC is an organization for special purposes (field devices, control systems, etc.) and I'm interested in general-purpose formats, so didn't look hard at this.

Protocol Buffers

Has schemas. Not standard but sponsored by Google.

Like Avro, Google's Protocol Buffers have a schema for the type and so they are schema + LV rather than TLV. But MariaDB uses them for its Dynamic Columns feature, so everybody should know about them.

Numbers and strings can be long, but there's very little differentiation -- essentially you have integers, double-precision floating point numbers, and strings. So, since I was objecting earlier when I saw that other serialization formats didn't distinguish (say) character sets, I have to be fair and say: this is worse. When the same "type" tag can be used for multiple different types, it's not specific enough.

Supposedly the makers of Protocol Buffers were asked why they didn't use ASN.1 and they answered "We never heard of it before". That's from a totally unreliable biased source but I did stop and ask myself: is that really so unbelievable? In this benighted age?


Can be TLV but depends on protocol. Not standard but sponsored by Apache, used a lot by Facebook.

I looked in vain for what one might call a "specification" of Thrift's binary serialization, and finally found an old stackoverflow discussion that said: er, there isn't any. There's a "Thrift Missing Guide" that tells me the base types, and a Java class describer for one of the protocols to help me guess the size limits.

Thrift's big advantage is that it's language neutral, which is why it's popular and there are many libraries and high-level tutorials. That makes it great as a communication format, which is what it's supposed to be. However, the number of options is small and the specification is so vague that I can't call it "good" according to the criteria I stated earlier.

The Chart

I depend on each serializer's specification, I didn't try anything out, I could easily have made some mistakes.

For the "NULL is a value" row, I say No (and could have added "Alackaday!") for all the formats that say NULL is a data type. Really the only way to handle NULL is with a flag so this would be best:
[type] [length] [flag] [value]
and in fact, if I was worried about dynamic schemas, I'd be partial to Codd's "two kinds of NULLs" arguments, in case some application wanted to make a distinction between not-applicable-value and missing-value.

For most of the data-type rows, I say Yes for all the formats that have explicit defined support. This does not mean that it's impossible to store the value -- for example it's easy to store a BOOLEAN with an integer or with a user-defined extension -- but then you're not using the format specification so some of its advantages are lost.

For dates (including DATETIME TIMESTAMP DATE etc.), I did not worry if the precision and range were less than what MySQL or MariaDB can handle. But for DECIMAL, i say No if the maximum number of digits is 18 or if there are no post-decimal digits.

For LONGBLOB, I say No if the maximum number of bytes is 2**32.

For VARCHAR, I say Yes if there's any way to store any encoded characters (rather than just bytes, which is what BINARY and BLOB are). In the "VARCHAR+" row I say Yes if there is more than one character set, although this doesn't mean much -- the extra character sets don't match with MySQL/MariaDB's variety.

I'll say again that specifications allow for "extensions", for example with ASN.1 you can define your own tags, but I'm only looking at what's specific in the specification.

Avro ASN.1 BSON CBOR Message Pack OPC UA Protocol Buffers Thrift
NULL is a value no no no no no YES no no







Dates no YES YES YES no YES no no


DECIMAL no YES no YES no no no no

VARCHAR+ no YES no no no YES no no

BIT no YES no no no no no no

Your Conclusion

You have multiple choice:

(1) Peter Gulutzan is obsessed with standards and exactness,
(2) Well, might as well use one of these despite its defects
(3) We really need yet another binary serializer format.

ocelotgui news

Recently there were some changes to the site to give more prominence to the ocelotgui manual, and a minor release -- ocelotgui version 1.02 -- happened on August 15.

What's in the SQL of NoSQL

In a previous post I said it's bogus that NoSQL stands for Not Only SQL, but NoSQL products can have "some" SQL. How much?

To get past the SQL-for-Hadoop stuff I'll just mine a few quotes: "Hive was the first SQL on Hadoop engine and is still the most mature engine." "Apache Phoenix is a project which aims to provide OLTP style SQL on top of Apache HBase." "Cloudera Impala and Apache Drill are the two most prominent Dremel clones." "Oracle, IBM, and Greenplum have all retrofit their database engines to integrate with Hadoop in various ways." There, that's the history out of the way. With thanks to a comprehensive article series: The Truth about SQL on Hadoop".

Of course I could add that it's possible to take an SQL front and add a NoSQL back as an "engine", as MariaDB did with Cassandra plus LevelDB, as PostgreSQL did with MongoDB.

Here's what I see as the documented capabilities provided by NoSQL vendors themselves, or via the Dremel clones.

thing CQL Drill Impala OrientDB
SELECT + WHERE 1/2 yes yes yes
SELECT + GROUP BY no yes yes yes
SELECT + functions 1/2 yes 1/2 yes
SELECT + IS NULL no yes yes yes
Subqueries or Joins no 1/2 yes no
GRANT + REVOKE yes no yes yes
Stored SQL Routines no no no no
Collations no no 1/10

CQL (Cassandra Query Language) does what I expect: I can do the regular DML statements, but UPDATE can only handle one row at a time. There are no subqueries or joins or views or SQL functions, and the WHERE is restricted because I have to use a query that includes information that tells Cassandra what cluster to go to. All strings are ASCII or UTF-8.

Apache Drill has all the options I respect for SELECT, even windowing. But ithe updating is done outside SQL. There are lots of built-in functions that CQL lacks.

Impala, from Cloudera, is regarded as a good option for analytic queries.

OrientDB handles graph databases, which the OrientDB founder says is defined as "index-free adjacency" (that's my first joke, I'm making sure you don't miss them).

For my chart, I deliberately added criteria that I thought might make the DBMSs choke. Specifically I thought IS [NOT] NULL would cause trouble because in a flexible schema the data might be either "missing" = not stored at all or "unknown" = stored explicitly as null value ... but usually this caused no problem. Specifically I thought collations would cause trouble because they must affect either storage or performance ... and they certainly did, CQL and Impala are byte-value-comparison-only and OrientDB has a grand total of two collations (case sensitive or insensitive). Most of the limitations are understandable for a DBMS that does clustering and searching quickly, but that's not mom-and-pop stuff. I will end this paragraph with my second joke. NoSQL doesn't scale ... down.

Sometimes the vendors get it that when you use SQL you should use ANSI SQL, for example it's typical that identifiers can be enclosed within double quotes -- far better than MySQL/MariaDB's ANSI_QUOTES requirement. On the other hand I see that Impala has SHOW statements -- just as bad as MySQL/MariaD. And I've no idea where OrientDB came up with the syntax "ORDER BY ... SKIP".

On the other hand, NoSQL can be expected to be better at flexible schemas. And it is, because some of the SQL vendors (I'm especially thinking of the work-in-progress "dynamic column" stuff in MariaDB and the JSON functions in MySQL) are still catching up. But they will catch up eventually. That's why I like the Gartner Report on DBMSs predicting

By 2017, the "NoSQL" label will cease to distinguish DBMSs, which will result in it falling out of use.

Only 5 months left to go, eh?

Another NoSQL DBMS, Tarantool, is also going to have SQL real soon now. Anti-disclosure: I do some work for the vendor but I had nothing to do with the SQL planning.

The idea behind Tarantool/SQL is: instead of struggling to add a piece at a time to a home-grown parser, just fork the one in SQLite. Poof, there's SQL syntax which, as I discussed in an earlier post, is more than an insignificant subset of the standard core features. The actual data maintenance job is done by Tarantool's multi-user server.

Here is an ocelotgui screenshot of a Tarantool/SQL query showing the usual advantages that one gets when the language is SQL: syntax highlighting, predicting, and columnar output.

SQLite and Standard SQL

I'm going to need to use SQLite syntax for a project that I'm involved with, and predictably I wonder: how standard is it? The SQLite folks themselves make modest claims to support most of the features with a special focus on SQL-92, but (a) I like to do my own counting (b) there's no official standard named SQL-92 because it was superseded 17 years ago.

By ignoring SQL-92 claims I eschew use of the NIST test suite. I'll be far less strict and more arbitrary: I'll go through SQL:2011's "Feature taxonomy and definition for mandatory features". For each feature in that list, I'll come up with a simple example SQL statement. If SQLite appears to handle the example, I'll mark it "Okay", else I'll mark it "Fail". I'm hoping that arbitrariness equals objectivity, because the unfair pluses should balance the unfair minuses.

Skip to the end of this blog post if you just want to see the final score.

Standard SQL Core Features, Examples, and Okay/Fail Results

E-011 Numeric data types
Example: create table t (s1 int);
Fail. A numeric column can contain non-numeric strings. There is a similar flaw for all data types, but let's count them all as only one fail.
E-011-02 REAL, DOUBLE PRECISION,and FLOAT data types
Example: create table tr (s1 float);
E-011-03 DECIMAL and NUMERIC data types
Example: create table td (s1 numeric);
Okay, although: when there are many post-decimal digits there is a switch to exponential notation, for example after "insert into t3 values (0.0000000000000001);" and "select *from t3" I get "1.0e-16". I regard this as a display flaw rather than a fail.
E-011-04 Arithmetic operators
Example: select 10+1,9-2,8*3,7/2 from t;
Okay. SQLite is wrong to calculate that 7/0 is NULL, though.
E-011-05 Numeric comparison
Example: select * from t where 1 < 2;
E-011-06 Implicit casting among the numeric data types
Example: select * from t where s1 = 1.00;
Okay, but only because SQLite doesn't distinguish etween numeric data types.
E021 Character string types
E-021-01 Character data type (including all its spellings)
Example: create table t44 (s1 char);
Okay, but only because SQLite accepts any definition that includes the word 'CHAR', for example CREATE TABLE t (s1 BIGCHAR) is okay although there's no such data type. There are no checks on maximum length, and no padding for insertions with less than the maximum length.
E021-02 CHARACTER VARYING data type (including all its spellings)
Example: create table t45 (s1 varchar);
Okay, but the behaviour is exactly the same as for CHARACTER.
E021-03 Character literals
Example: insert into t45 values ('');
Okay, and the bad practice of accepting ""s for character literals is avoided.Even hex notation, for example X'41', is okay.
E021-04 CHARACTER_LENGTH function
Example: select character_length(s1) from t;
Fail. There is no such function. There is a function LENGTH(), which is okay.
Example: select octet_length(s1) from t;
Fail. There is no such function.
E021-06 SUBSTRING function.
Example: select substring(s1 from 1 for 1) from t;
Fail. There is no such function. There is a function SUBSTR(x,n,n) which is okay.
E021-07 Character concatenation
Example: select 'a' || 'b' from t;
E021-08 UPPER and LOWER functions
Example: select upper('a'),lower('B') from t;
Okay. It does not work well out of the box, but I loaded the ICU extension.
E021-09 TRIM function
Example: select trim('a ') from t;
E021-10 Implicit casting among the fixed-length and variable-length character string types
Example: select * from tm where char_column > varchar_column;
Okay, but only because SQLite doesn't distinguish between character data types.
E021-11 POSITION function
Example; select position(x in y) from z;
Fail. There is no such function.
E021-02 Character comparison
Example: select * from t where s1 > 'a';
Okay. I should note here that comparisons are case sensitive, and it is devilishly hard to change this except with ASCII,but case insensitivity is not a requirement for this feature.
E031 Identifiers
E031-01 Delimited
Example: create table "t47" (s1 int);
Fail. Although I can enclose identifiers inside double quotes, that doesn't make them case sensitive.
E031-02 Lower case identifiers
Example: create table t48 (s1 int);
E031-03 Trailing underscore
Example: create table t49_ (s1 int);
E051 Basic query specification
Example: select distinct s1 from t;
E051-02 GROUP BY clause
Example: select distinct s1 from t group by s1;
E051-04 GROUP BY can contain columns not in select list
Example: select s1 from t group by lower(s1);
E051-05 select list items can be renamed
Example: select s1 as K from t order by K;
E051-06 HAVING clause
Example: select s1,count(*) from t having s1 < 'b';
Fail. GROUP BY is mandatory before HAVING.
If I hadn't happened to omit GROUP BY, it would have been okay.
E051-07 Qualifie d * in select list
Example: select t.* from t;
E051-08 Correlation names in the FROM clause
Example: select * from t as K;
E051-09 Rename columns in the FROM clause
Example: select * from t as x(q,c);
E061 Basic predicates and search conditions
E061-01 Comparison predicate
Example: select * from t where 0 = 0;
Okay. But less correct syntax would work too, for example "where 0 is 0".
E061-02 BETWEEN predicate
Example: select * from t where ' ' between '' and ' ';
E061-03 IN predicate with list of values
Example: select * from t where s1 in ('a',upper('a'));
E061-04 LIKE predicate
Example: select * from t where s1 like '_';
E061-05 LIKE predicate: ESCAPE clause
Example: select * from t where s1 like '_' escape '_';
E061-06 NULL predicate
Example: select * from t where s1 is not null;
E061-07 Quantified comparison predicate
Example: select * from t where s1 = any (select s1 from t);
Fail. Syntax error.
E061-08 EXISTS predicate
Example: select * from t where not exists (select * from t);
E061-09 Subqueries in comparison predicate
Example: select * from t where s1 > (select s1 from t);
Fail. There was more than one row in the subquery result set, but SQLite didn't return an error.
E061-11 Subqueries in IN predicate
Example: select * from t where s1 in (select s1 from t);
E061-12 Subqueries in quantified comparison predicate
Example: select * from t where s1 >= all (select s1 from t);
Fail. Syntax error.
E061-13 Correlated subqueries
Example: select * from t where s1 = (select s1 from t2 where t2.s2 = t.s1);
E061-14 Search condition
Example: select * from t where 0 <> 0 or 'a' < 'b' and s1 is null;
E071 Basic query expressions
E071-01 UNION DISTINCT table operator
Example: select * from t union distinct select * from t;
Fail. However, "select * from t union select * from t;" is okay.
E071-01 UNION ALL table operator
Example: select * from t union all select * from t;
E071-03 EXCEPT DISTINCT table operator
Example: select * from t except distinct select * from t;
Fail. However, "select * from t except select * from t;" is okay.
E071-05 Columns combined via table operators need not have exactly the same data type.
Example: select s1 from t union select 5 from t;
Okay, but only because SQLite doesn't distinguish data types very well.
E071-06 Table operators in subqueries
Example: select * from t where 'a' in (select * from t union select * from t);
E081 Basic privileges
E081-01 Select privilege at the table level
Fail. Syntax error. (SQLite doesn't support privileges.)
E081-02 DELETE privilege
Fail. (SQLite doesn't support privileges.)
E081-03 INSERT privilege at the table level
Fail. (SQLite doesn't support privileges.)
E081-04 UPDATE privilege at the table level
Fail. (SQLite doesn't support privileges.)
E081-05 UPDATE privilege at column level
Fail. (SQLite doesn't support privileges.)
E081-06 REFERENCES privilege at the table level
Fail. (SQLite doesn't support privileges.)
E081-07 REFERENCES privilege at column level
Fail. (SQLite doesn't support privileges.)
Fail. (SQLite doesn't support privileges.)
E081-09 USAGE privilege
Fail. (SQLite doesn't support privileges.)
E081-10 EXECUTE privilege
Fail. (SQLite doesn't support privileges.)
E091 Set functions
E091-01 AVG
Example: select avg(s1) from t7;
Fail. No warning that nulls were eliminated.
E091-02 COUNT
Example: select count(*) from t7 where s1 > 0;
E091-03 MAX
Example: select max(s1) from t7 where s1 > 0;
E091-04 MIN
Example: select min(s1) from t7 where s1 > 0;
E091-05 SUM
Example: select sum(1) from t7 where s1 > 0;
E091-06 ALL quantifier
Example: select sum(all s1) from t7 where s1 > 0;
E091-07 DISTINCT quantifier
Example: select sum(distinct s1) from t7 where s1 > 0;
E101 Basic data manipulation
E101-01 INSERT statement
Example: insert into t (s1) values (''),(null),(55);
E101-03 Searched UPDATE statement
Example: update t set s1 = null where s1 in (select s1 from t2);
E01-04 Searched DELETE statement
Example: delete from t where s1 in (select s1 from t);
E111 Single row SELECT statement
Example: select count(*) from t;
E121 Basic cursor support
Fail. SQLite doesn't support cursors.
E121-02 ORDER BY columns need not be in select list
Example: select s1 from t order by s2;
Okay. Update on 2016-06-27: Originally I wrongly said "Fail", see the comments.
E121-03 Value expressions in select list
Example: select s1 from t7 order by -s1;
E121-04 OPEN statement
Fail. SQLite doesn't support cursors.
E121-06 Positioned UPDATE statement
Fail. SQLite doesn't support cursors.
E121-07 Positioned DELETE statement
Fail. SQLite doesn't support cursors.
E121-08 CLOSE statement
Fail. SQLite doesn't support cursors.
E121-10 FETCH statement implicit next
Fail. SQLite doesn't support cursors.
E121-17 WITH HOLD cursors
Fail. SQLite doesn't support cursors.
E131 Null value support (nulls in lieu of values)
Example: select s1 from t7 where s1 is null;
E141 Basic integrity constraints
E141-01 NOT NULL constraints
Example: create table t8 (s1 int not null);
E141-02 UNIQUE constraints of NOT NULL columns
Example: create table t9 (s1 int not null unique);
E141-03 PRIMARY KEY constraints
Example: create table t10 (s1 int primary key);
Okay, although SQLite wrongly assumes s1 is auto-increment.
E141-04 Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action.
Example: create table t11 (s1 int references t10);
Fail. The foreign-key check will only be checked when I have said "pragma foreign_keys = on;".
E141-06 CHECK constraints
Example: create table t12 (s1 int, s2 int, check (s1 = s2));
E141-07 Column defaults
Example: create table t13 (s1 int, s2 int default -1);
E141-08 NOT NULL inferred on primary key
Example: create table t14 (s1 int primary key);
Fail. I am able to insert NULL if I don't explicitly say the column is NOT NULL.
E141-10 Names in a foreign key can be specified in any order
Example: create table t15 (s1 int, s2 int, primary key (s1,s2));
create table t16 (s1 int, s2 int, foreign key (s2,s1) references t15 (s1,s2));
E151 Transaction support
E151-01 COMMIT statement
Example: commit;
Fail. I have to say BEGIN TRANSACTION first.
E151-02 ROLLBACK statement
Example: rollback;
E152 Basic SET TRANSACTION statement
Example: set transaction isolation level serializable;
Fail. Syntax error.
E152-02 SET TRANSACTION statement READ ONLY and READ WRITE clauses
Example: set transaction read only;
Fail. Syntax error.
E153 Updatable queries with subqueries
E161 SQL comments using leading double minus
Example: --comment;
E171 SQLSTATE suport
Example: drop table no_such_table;
Fail. At least, the error message doesn't hint that SQLSTATE exists.
E182 Host language binding
Okay. The existence of shell executable proves there is a C binding.
F031 Basic schema manipulation
F031-01 CREATE TABLE statement to create persistent base tables
Example: create table t20 (t20_1 int not null);
F031-02 CREATE VIEW statement
Example: create view t21 as select * from t20;
F031-03 GRANT statement
Fail. SQLite doesn't support privileges.
F031-04 ALTER TABLE statement: add column
Example: alter table t7 add column t7_2 varchar default 'q';
F031-14 DROP TABLE statement: RESTRICT clause
Example: drop table t20 restrict;
Fail. Syntax error, and RESTRICT is not assumed.
F031-14 DROP VIEW statement: RESTRICT clause
Example: drop view v2 restrict;
Fail. Syntax error, and RESTRICT is not assumed.
F031-10 REVOKE statement: RESTRICT clause
Fail. SQLite does not support privileges.
F041 Basic joined table
F041-01 Inner join but not necessarily the INNER keyword
Example: select a.s1 from t7 a join t7 b;
F041-02 INNER keyword
Example: select a.s1 from t7 a inner join t7 b;
Example: select t7.*,t22.* from t22 left outer join t7 on (t22_1=s1);
Example: select t7.*,t22.* from t22 right outer join t7 on (t22_1=s1);
Fail. Syntax error.
F041-05 Outer joins can be nested
Example: select t7.*,t22.* from t22 left outer join t7 on (t22_1=s1) left outer join t23;
F041-07 The inner table in a left or right outer join can also be used in an inner join
Example: select t7.* from t22 left outer join t7 on (t22_1=s1) inner join t22 on (t22_4=t22_5);
Okay. The query fails due to a syntax error but that's expectable.
F041-08 All comparison operators are supported (rather than just =)
Example: select * from t where 0=1 or 0>1 or 0<1 or 0<>1;
F051 Basic date and time
F051-01 DATE data type (including support of DATE literal)
Example: create table dates (s1 date);
Okay. (SQLite doesn't enforce valid dates or times, but we've already noted that.)
F051-02 TIME data type (including support of TIME literal)
Example: create table times (s1 time default time '1:2:3');
Fail. Syntax error.
F051-03 TIMESTAMP data type (including support of TIMESTAMP literal)
Example: create table timestamps (s1 timestamp);
F051-04 Comparison predicate on DATE, TIME and TIMESTAMP data types
Example: select * from dates where s1 = s1;
F051-05 Explicit CAST between date-time types and character string types
Example: select cast(s1 as varchar) from dates;
Example: select current_date from t;
Example: select * from t where current_time < '23:23:23';
Example: select localtime from t;
Fail. Syntax error.
Example: select localtimestamp from t;
Fail. Syntax error.
F081 UNION and EXCEPT in views
Example: create view vv as select * from t7 except select * from t15;
F131 Grouped operations
F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views
Example: create view vv2 as select * from vv group by s1;
F131-02 Multiple tables supported in queries with grouped views
Example: create view vv3 as select * from vv2,t30;
F131-03 Set functions supported in queries with grouped views
Example: create view vv4 as select count(*) from vv2;
F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views
Example: create view vv5 as select count(*) from vv2 group by s1 having count(*) > 0;
F181 Multiple module support
Fail. SQLite doesn't have modules.
F201 CAST function
Example: select cast(s1 as int) from t;
F221 Explicit defaults
Example: update t set s1 = default;
Fail. Syntax error.
F261 CASE expression
F261-01 Simple CASE
Example: select case when 1 = 0 then 5 else 7 end from t;
F261-02 Searched CASE
Example: select case 1 when 0 then 5 else 7 end from t;
F261-03 NULLIF
Example: select nullif(s1,7) from t;
Example: select coalesce(s1,7) from t;
F311 Schema definition statement
Fail. SQLite doesn't have schemas or databases.
F311-02 CREATE TABLE for persistent base tables
Fail. SQLite doesn't have CREATE TABLE inside CREATE SCHEMA.
Fail. SQLite doesn't have CREATE VIEW inside CREATE SCHEMA.
Fail. SQLite doesn't have CREATE VIEW inside CREATE SCHEMA.
F311-05 GRANT statement
Fail. SQLite doesn't have GRANT inside CREATE SCHEMA.
F471 Scalar subquery values
Example: select s1 from t where s1 = (select count(*) from t);
F481 Expanded NULL Predicate
Example: select * from t where row(s1,s1) is not null;
Fail. Syntax error.
F812 Basic flagging
Fail. SQLite doesn't support any flagging
S011 Distinct types
Example: create type x as float;
Fail. SQLite doesn't support distinct types.
T321 Basic SQL-invoked routines
T321-01 User-defined functions with no overloading
Example: create function f () returns int return 5;
Fail. SQLite doesn't support user-defined functions.
T321-02 User-defined procedures with no overloading
Example: create procedure p () begin end;
Fail. SQLite doesn't support user-defined procedures.
T321-03 Function invocation
Example: select f(1) from t;
Fail. SQLite doesn't support user-defined functions.
T321-04 CALL statement.
Example: call p();
Fail. SQLite doesn't support user-defined procedures.
T321-05 RETURN statement.
Example: create function f() returns int return 5;
Fail. SQLite doesn't support user-defined functions.
T631 IN predicate with one list element
Example: select * from t where 1 in (1);
F031 Basic information schema.
Example: select * from information_schema.tables;
Fail. There is no schema with that name (not counted in the final score).

The Final Score

Fail: 59

Okay: 75

Update 2016-06-26: Originally I counted 60 to 74, that was an error.

So SQLite could claim to support most of the core features of the current standard, according to this counting method, after taking into account all the caveats and disclaimers embedded in the description above.

I anticipate the question, "Will ocelotgui (the Ocelot Graphical User Interface for MySQL and MariaDB) support SQLite too?" and the answer is "I don't know." The project would only take two weeks, but I have no idea whether it's worth that much effort.

In the last while, I've concentrated on some ocelotgui bug fixes and on checking whether it runs on Windows as well as on Linux. It does, but only from source -- see the instructions at

Releasing ocelotgui 1.0.0

Today ocelotgui, the Ocelot Graphical User Interface for MySQL and MariaDB, version 1.0.0, is generally available for Linux. Read the manual, see the screenshots, and download binary (.deb or .rpm) packages and GPL-licensed C++ source here.

MariaDB 10.2 Window Functions

Today the first MariaDB 10.2 alpha popped up and for the first time there is support for window functions.

I'll describe what's been announced, what's been expected, comparisons to other DBMSs, problems (including crashes and wrong answers), how to prepare, what you can use as a substitute while you wait.

I assume some knowledge of what window functions are. If you'd prefer an introductory tutorial, I'd suggest reading articles like this one by Joe Celko before you continue reading this post.

What's been announced

The MariaDB sources are:

The release notes

The source code trees -- the feature tree up till now has been but the version-10.2 download page has more choices and is probably more stable.

Sergei Petrunia and Vicentiu Ciorbaru, two developers who I think deserve the credit, made a set of slides for a conference in Berlin earlier this month. It seems to have some typos but is the best description I've seen so far.

On Wednesday April 20 Mr Petrunia will give a talk at the Percona conference. Alas, it coincides with Konstantin Osipov's talk about Tarantool -- which I've done some work for -- which, if you somehow haven't heard, is a NoSQL DBMS that's stable and faster than others according to independent benchmarks like the one from Coimbra. What a shame that two such important talks are scheduled for the same time.

Anyway, it's clear that I'll have to update this post as more things happen.

What's been expected

There have been several wishes / feature requests for window functions over the years.

Typical feature requests or forum queries are "Oracle-like Analytic Function RANK() / DENSE_RANK() [in 2004]", "analytical function like RANK etc to be implemented [in 2008]", "Is MySQL planning to implement CTE and Window functions? [in 2010]".

Typical blog posts are Shlomi Noach's "Three wishes for a new year [in 2012]" and Baron Schwartz's "Features I'd like in MySQL: windowing functions [in 2013]"..

Typical articles mentioning the MySQL/MariaDB lack of window functions are "What PostgreSQL has over other open source SQL databases" and "Window Functions Comparison ...".

So it's clear that there has been steady demand, or reason for demand, over the years.

My first applause moment is: Mr Petrunia and Mr Ciorbaru have addressed something that's been asked for, rather than what they wished had been asked for.

Comparisons to other DBMSs

I know twelve DBMSs that support window functions. No screen is wide enough for a chart showing them all, so I'll just list their windows-function documents here so that you can click on the names to get the details:
DB2 z/OS 10,
TERADATA. I'll show MariaDB against The Big Three.

These functions are mentioned in the standard document as required by optional feature T611 ELementary OLAP operations:

Function MariaDB Oracle DB2 SQL Server
DENSE_RANK yes yes yes yes
RANK yes yes yes yes
ROW_NUMBER yes yes yes yes

These functions are mentioned in the standard document as required by optional feature T612 Advanced OLAP operations:

Function MariaDB Oracle DB2 SQL Server
CUME_DIST yes yes no yes
PERCENT_RANK yes yes no yes

These functions are mentioned in the standard document as required by optional features T614 through T617:

Function MariaDB Oracle DB2 SQL Server
FIRST_VALUE no yes yes yes
LAG no yes yes yes
LAST_VALUE no yes yes yes
LEAD no yes yes yes
NTH_VALUE no yes no no
NTILE yes yes no yes

These are common functions which are in the standard and which can be window functions:

Function MariaDB Oracle DB2 SQL Server
AVG yes yes yes yes
COUNT yes yes yes yes
COVAR_POP/SAMP no yes yes yes
MAX no yes yes yes
MIN no yes yes no
SUM yes yes yes yes
VAR_POP/SAMP no yes yes yes

Yes MariaDB also supports non-standard functions like BIT_XOR, but they're worthless for comparison purposes. What's more important is that the MariaDB functions cannot be DISTINCT.

As for the options in OVER clause ... just the important ones ...

Function MariaDB Oracle DB2 SQL Server
ORDER BY yes yes yes yes
NULLS FIRST|LAST no "yes" yes no
PARTITION BY yes yes yes yes
PRECEDING|FOLLOWING sometimes yes yes yes

Those are the options that matter. The NULLS clause is important only because it shows how far an implementor will go to support the standard, rather than because most people care. MariaDB in effect supports NULLS HIGH|LOW, which is as good as Oracle -- The Oracle manual puts it this way: "NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order." People who think that's not cheating can add a comment at the end of this post.

From the above I suppose this second applause moment is justifiable: MariaDB has all the basics, and half of the advanced features that other DBMSs have.

Problems (including crashes and wrong answers)

The MariaDB announcement says:

"Do not use alpha releases on production systems! ... Thanks, and enjoy MariaDB!"

Indeed anyone who used 10.2.0 in production would discover that enjoyable things can be bad for you.

I started with this database: ...

create table t1 (s1 int, s2 char(5));
insert into t1 values (1,'a');
insert into t1 values (null,null);
insert into t1 values (1,null);
insert into t1 values (null,'a');
insert into t1 values (2,'b');
insert into t1 values (-1,'');

The following statements all cause the server to crash:

select row_number() over ();
select 1 as a, row_number() over (order by a) from dual;
select *, abs(row_number() over (order by s1))
 - row_number() over (order by s1) as X from t1;
select rank() over (order by avg(s1)) from t1;

The following statements all give the wrong answers:

select count(*) over (order by s2) from t1 where s2 is null;
select *,dense_rank() over (order by s2 desc),
 dense_rank() over (order by s2) from t1;
select *, sum(s1) over (order by s1) from t1 order by s1;
select avg(s1), rank() over (order by s1) from t1;

The following statement causes the client to hang (it loops in mysql_store_result, I think this is the first time I've seen this type of error)

select *, avg(s1) over () from t1;

And now for the third applause line ... to which you might be saying: huh? Aren't those, er, less-than-desirable results? To which I would reply: yes, but two weeks ago there were far more and far bigger problems. We should be clapping for how quickly progress has been made, and guessing that this section of my post will be obsolete soon.

How to prepare

You have lots of time to get ready for 10.2, but may as well start now by getting rid of words that have special meaning for window functions.

The word OVER is reserved.

The newly supported function names -- DENSE_RANK RANK ROW_NUMBER CUME_DIST PERCENT_RANK NTILE -- are not reserved, and the names of functions which will probably be supported soon -- FIRST_VALUE LAG LEAD LAST_VALUE NTH_VALUE -- will probably not be reserved. But they might as well be, because you won't be able to use those names for your own functions. Besides, they're reserved in standard SQL.

What you can use as a substitute

Suppose you don't want to wait till MariaDB is perfect, or you'd like to stay with MySQL (which as far as I know has made less progress than MariaDB toward this feature). Well, in short: gee that's too bad. But I have seen three claims about getting a slight subset.

One: Shlomi Noach claimss you can use a trick with GROUP_CONCAT:

Two: Adrian Corston claims you can make delta functions with assignments.

Three: I claim that in ocelotgui you can put 'row_number() over ()' in a SELECT and get a row-number column even with older versions of MySQL or MariaDB (this is a recent change, it's in the source not the binary).

In fact all the "window_function_name() OVER ()" functions could be done easily in the client, if they're in the select list and not part of an expression,
and the result set is ordered. But I'm not sure whether that's something to excite the populace.

There might be a "Four:". I have not surveyed the various applications that can do cumulations. I suspect that mondrian is one, and open OLAP might be another, but haven't looked at them.

Our own progress

For ocelotgui (Ocelot's GUI client for MySQL and MariaDB) we had to adjust the syntax checker to highlight the new syntax in 10.2, as this screenshot shows window_function
So we now can claim to have "the only native-Linux GUI that correctly recognizes MariaDB 10.2 window functions". Catchy slogan, eh? The beta download is at I still expect that it will be out of beta in a few weeks.

Client-Side Predictive Parsing of MySQL/MariaDB Grammar

The Ocelot GUI client for MySQL/MariaDB is now beta. The final feature is client-side predictive parsing of every SQL clause and statement. Readers who only care how we did it can skip to the section Recursive Descent Parsers". I'll start by illustrating why this is a good feature.

Error Checks

Compare this snapshot from mysql client:
with this from ocelotgui:

The GUI advantage is that the error message is more clear and the error location is more definite. This is not always true. However, anybody who dislikes the famous message "You have an error ..." should like that there is another way to hear the bad news. It's like getting an extra opinion.

In theory the further advantage is: this saves time on the server, because the client catches the syntax errors. In practice that should not be important, because: (a) ocelotgui is lax and can be erroneous; (b) there are better ways to catch some syntax errors before sending them to the server, for example setting up a test machine.

Finding the end

All respectable clients can scan an SQL statement to find its tokens. For example the mysql client knows that anything enclosed in quote marks, except a pair of quote marks, is a single token. This is pretty well essential, because it has to know whether a semicolon is just part of a string, or is the end of a statement.

Unfortunately a semicolon might not be the end of a compound statement, and that's where simple "tokenizers" like mysql's can't cope. So in my MySQL days we had to come up with the concept of delimiters (I say "we" because I think the hassle of delimiters might have been my idea, but others are welcome to take the credit). It should be clear that a statement like


is not complete and should not go to the server, so it's nice that we can say we don't need delimiters.

But they still can have their uses. The scenario I dread is that a user has a spelling error, causing the client to think that input is complete, and shipping it off to the server prematurely. If there was some symbol that always meant "ship it!" and no other symbol ever meant "ship it!", that would become unlikely. That's what delimiters are. But we should have decided on a fixed unchangeable symbol instead of a user-defined one.


All respectable GUIs have highlighting, that is, they can display different colours for literals, operators, or keywords. But most GUIs cannot figure out what is a keyword, unless they ask the MySQL-server's parser. The SQL problem is that there are two kinds of keywords: reserved words (which are guaranteed to have syntactic meaning if they're not errors), and ordinary keywords (which might have syntactic meaning but might be identifiers, depending where they appear). An example statement is


where "end" and "begin" are identifiers, but BEGIN and END are not.

(A much easier problem is that you'd need two keyword lists, because in MySQL 5.7 GENERATED and GET and IO_AFTER_GTIDS and IO_BEFORE_GTIDS and MASTER_BIND and STORED and VIRTUAL are reserved words, but in MariaDB they're not. But, since we don't think the MariaDB manual's reserved-word list is correct, we think those are the only reserved-word differences.)

So we have to beware of anything that depends on a list of keywords. For example my favoured editor is a variant of Kate, which recognizes a hodgepodge of symbols in its "SQL Keyword" list, and displays utterly useless highlighting when my file's extension is .sql. Apparently Vim would be similar. Most clients that claim to work with MySQL or MariaDB are better than editors -- they at least use a DBMS-specific keyword list rather than a generic one -- and they're generally good because in a sense they train users not to use keywords as identifiers. For example, if users see that "begin" has a non-identifier colour while they're typing, they'll avoid creating objects named "begin" even though it's not a reserved word.

I'm not sure whether, in the wider SQL world, well known GUI clients have advanced past the keyword-list stage. I see hints here and there that SQL Server Management Studio has not and Toad has not, and Oracle SQL Developer has, but nothing explicit, and I repeat I'm not sure.
(UPDATE 2016-03-28: Mike Lischke's description of Oracle's new approach is here.)

In the end, then, a GUI with keyword-list highlighting will be right 90% of the time (I'm just guessing here, but I think it will sound right to experienced readers). On the other hand, a GUI that recognizes grammar should be right 97% of the time, and -- just guessing again -- I expect that's preferable.


The next step upwards in intelligence is knowing what the next token might be before the user starts typing it, or knowing how the current token ends before the user finishes typing it. And here, if you contemplate the Error Message example that we started with, you might realize: the MySQL/MariaDB server parser can't do this. Which is why I emphasized right in the title: this is "predictive" parsing.

Example 1: suppose you've typed SELECT * FROM T ORDER. The GUI will show what the next word must be ...

Example 2: suppose you've typed CREATE T. The GUI will show what possible words start with T ...

And in either case, hitting the Tab key will save a bit of typing time. I should note that "hitting the Tab key to complete" is something many can do, even the mysql client -- but for identifiers not keywords. Technically we can do both, though we prefer to avoid discussing identifiers.

Initial Summary

For error checking, finding the end, highlighting, and predicting: whoopie for predictive parsing of the whole MySQL / MariaDB grammar.

As an additional point, I suppose it's obvious that we wouldn't have been able to incorporate a stored-procedure debugger in ocelotgui without parsing. Admittedly it is not using the new parsing code, but it is necessary for it to do a lot more than looking at keywords. So I class debugger capability as the fifth advantage of having client-side parsing.

Recursive Descent Parsers

The algorithms for recursive descent parsers are in most textbooks for compilers, and even in Wikipedia. The "recursive" means that the process can call itself; the rest of the algorithm looks like this:
If (next thing is X) accept it and get the next thing
if (next thing is Y) accept it and get the next thing
if (next thing is Z) accept it and get the next thing
else give up and say there's an error.
Simple, eh?

Now for some quotes from those textbooks for compilers:

"The advantages of recursive descent parsers are that it's easy to write, and once written, it's easy to read and understand. The main disadvantage is that it tends to be large and slow." -- Ronald Mak, Writing Compilers And Interpreters, page 810

"The parser text shows an astonishingly direct relationship to the grammar for which it was written. This similarity is one of the great attractions of recursive descent parsing. ... In spite of their initial good looks, recursive descent parsers have a number of drawbacks ... repeated backtracking ... often fails to produce a correct parser [text] ... error handling leaves much to be desired." -- Dick Grune, Henri Bal, Ceriel Jacobs, Koen Langendoen, pages 117-119

And they're right. Have a look at the parsing code of ocelotgui, which can be done by opening a source file here and searching for the first line that begins with "void MainWindow::hparse_f_" (line 9565 in today's beta). Then scroll downwards till there are no more routines that begin with hparse_f_ -- 5000 lines later. Readable, sure, because it's the simplest of C code. But also tedious, repetitive, and yes, "large and slow". And this is without knowing anything about object names, since it's a purely syntactic syntax checker and won't look at metadata.

On a server the disadvantages could be crippling, but on a client they don't matter -- the meanest laptop has megabytes to spare and the response time is still way faster than a user can blink. And SQL doesn't require repeated backtracking because it's rarely necessary to look ahead to the next tokens in order to figure out what the current token means. Here is the worst example that we ran into (I'm quoting the MariaDB 10.1 manual

GRANT role [, role2 ... ] TO grantee [, grantee2 ... ]

which can be instantiated as


See the problem? It's perfectly okay for EXECUTE to be a role name -- it's not reserved -- but typically it's in statements like GRANT EXECUTE ... ON PROCEDURE. So we have to look ahead to find whether ON follows, or whether TO follows. Which we did ... and then found out that MariaDB couldn't. I'd mentioned that this looked tough when I wrote about MariaDB roles two years ago. But for some reason it was attempted anyway and the inevitable bug report has been labelled "stalled" for a long time.

Oh, and one more detail that you'll find in those compiler textbooks: correctly speaking, ocelotgui has a "recognizer" not a "parser" because it doesn't generate a tree. That's why I've carefully said it "does parsing" but not said it contains a parser.

Beta Status

The above represents the last feature that we intend to add. It's at last "ocelotgui beta" rather than "ocelotgui alpha". The C source and Linux executables, as usual, are at

Comments in SQL Statements

First I will say what the comment syntax is for various vendors' dialects. Then I will get specific about some matters that specifically affect MySQL or MariaDB.

Syntax Table

DBMS --... /*...*/ #... Nesting Hints
Oracle 12c YES YES NO NO YES




The first column is for the type of DBMS. "Standard" is the ISO/IEC SQL standard document. For the others, just click on the DBMS name to see the relevant documentation. The standard, incidentally, clarifies that strings of comments are to be treated as a newline, so if you hear somebody say "comments are ignored", that's slightly wrong.

The first column is for comments that begin with "--" (two hyphen-minus signs), what the standard document calls "simple comments", the ones that look like this:


Everyone supports simple comments, the only problem with MySQL/MariaDB is their insistence that the -- must be followed by a space. I've had it explained to me that otherwise the parser had problems.

The second column is for comments enclosed by /* and */, what the standard document calls "bracketed comments", the ones that look like this:


According to the standard document, bracketed comments are not mandatory, they are optional feature T351. However, it would be surprising to find a modern SQL implementation that doesn't support them.

The third column is for comments that begin with "#" (what Unicode calls Number Sign but an American dictionary allows for the word Octothorpe ), the ones that look like this:


Notice how, in every row but the MySQL/MariaDB row, the key word is NO? In fact I've only encountered one other SQL DBMS that is octothorpophiliac: mSQL. Old-timers may recall that mSQL from Hughes Technologies was, for a while, an inspiration for one of MySQL's founders. Anyway, it's unnecessary because simple comments do the job just as well.

The fourth column is for nesting, that is, putting bracketed comments within bracketed comments, that look like this:


I've often been irritated that I can't nest in C, so I approve of the DBMSs that support this standard requirement. But I never saw it as important in my MySQL-architect days. There were a few what I guess could be categorized
as "feature requests" (here and here and here) and I approved of my colleagues' clear responses, it's low priority.

The final column is for hints. A hint is a bit of syntax that the server might ignore, signalled by an extra character or two in a bracketed comment, like this:


Typically a hint is a suggestion for an optimizer, like "use index X instead of the default". It's found in Oracle; it's not found in PostgreSQL and some PostgreSQL folks don't like it; but it's found in EnterpriseDB's "advanced PostgreSQL"; and of course it's found in MySQL and MariaDB. A newish point is that MariaDB has an extra signal "/*M!###### MariaDB-specific code */" that MySQL won't recognize, which is a good thing since the optimizers have diverged somewhat.

Passing comments to the server

In the MySQL 5.7 manual we see the client has an interesting option:

--comments, -c
Whether to preserve comments in statements sent to the server. The default is --skip-comments (discard comments), enable with --comments (preserve comments).

and a good question is: huh? Surely we should preserve comments, especially in stored procedures, no? Well, the obvious answer is that the parser has to spend time skipping over them, but I doubt that the effect is significant nowadays. The better answer is merely that behaviour changes are serious so let's leave this up to the users. Our GUI client supports --comments too, which is no surprise since we support all mysql-client options that make sense in a GUI.

But what happens if it's hard to tell where comments belong? Buried in the source download is a file named mysql-test/t/mysql_comments.sql which is checking these questions:
* Ignore comments outside statements, on separate lines?
* Ignore comments at the end of statements, on the same line but after the semicolon?
* Ignore comments inside CREATE PROCEDURE/FUNCTION/TRIGGER/EVENT, but not in the body?
The test should be updated now that compound statements in MariaDB don't have to be inside CREATE PROCEDURE/FUNCTION/TRIGGER/EVENT.


Steve McConnell's "Code Complete" book advises: "A common guideline for Java and C++ that arises from a similar motivation is to use // synax for single-line comments and /* ... */ syntax for larger comments."

I guess that the equivalent for SQL purposes would be to say: use -- for single-line comments and /* ... */ for longer ones. But don't use #, and be wary with standalone or endline comments, and turn --comments on.


In an earlier blog post I predicted that ocelotgui, our GUI client for MySQL and MariaDB, would be beta in February. Now it's February 29, so I have to modify that to: "any day now (watch this blog for updates or click Watch on the github project page)". The latest feature additions are in the downloadable source code, by the way, but not in the binary release.

Privileges in MySQL and MariaDB: The Weed Of Crime Bears Bitter Fruit

Let's look at how well MySQL and MariaDB support privileges (part of "access control" in standard terms), compared to other DBMSs, and consequences thereof.

Count the Privileges

I go to the DBMS manuals (here and starting here and here) and I count the privileges. This is like judging a town by the number of traffic lights it claims to have, but I'm trying to get an indicator for how granular the DBMS's "authorization" is.

Number of privileges listed in the manuals

MySQL/MariaDB  Oracle 12c     DB2 9.7   SQL Server 2014
31             240            52        124

Pretty small number in the first column, eh? There are historical reasons that MySQL was reluctant to add new privileges, illustrated by Bug#43730.

What is the effect of having a limited number of privileges? Sometimes the same privilege has to be used for two different things. For example, the SUPER privilege is good for "CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command", while the PROCESS privilege is what you need for SHOW PROCESSLIST -- but also for selecting from information_schema.innodb_sys_tables.

Why is this a flaw? If administrators want to allow access to a goose, they are forced to allow access to a gander as well -- even when the gander is none of the grantee's business. As an example that affected us: to make Ocelot's stored-procedure debugger work, we have to be able to set values in a single global variable, which is impossible without the SUPER privilege, therefore to allow people to use the debugger you have to allow them to purge binary logs too.

Standard Requirements

The SQL standard mentions 9 privileges: INSERT UPDATE DELETE SELECT REFERENCES USAGE UNDER TRIGGER EXECUTE. MySQL and MariaDB do a fair job of handling them:

INSERT: perfect support, including column-level grants.

UPDATE: perfect support, including column-level grants.

DELETE: perfect support.

SELECT: perfect support, including column-level grants.

REFERENCES: perfect support, not including column-level grants. I think this is not well known yet. Deep in the caverns of the manual are the words: "The REFERENCES privilege is unused before MySQL 5.7.6. As of 5.7.6, creation of a foreign key constraint requires the REFERENCES privilege for the parent table." Kudos to MySQL. The MariaDB manual, on the other hand, still says the REFERENCES privilege is "unused". For some background about this new feature, click the high-level architecture tab in the old worklog task Foreign keys: reference privilege.

USAGE: no support. In standard SQL, USAGE is for access to domains or UDTs or sequence generators or transliterations or character sets or collations. In MySQL/MariaDB, USAGE is the minimum privilege -- you can log in, that's all. So USAGE is unsupported, but unimportant.

UNDER: no relevance. This is for optional UDT features.

TRIGGER: perfect support.

EXECUTE: perfect support.

Looking at the down side, MySQL and MariaDB don't allow for the standard GRANT OPTION. Yes, they have a GRANT OPTION privilege, but that's not standard -- what's needed (and what's supported by the other serious DBMSs) is an option to grant a particular privilege, not a privilege to grant any privileges.


The objection about having hundreds of possible privileges is: it's hard to keep track of them, or even remember what they are. This should be a solved problem: allow a package of privileges, in other words support CREATE ROLE. This time the kudos go to MariaDB which has allowed roles for over two years. But what if you have MySQL and it's tedious to grant multiple times?

It's still simple. You either make a script which contains a bunch of GRANT statements, or you create a stored procedure. Certainly I'd recommend a stored procedure, because it will be "inside the database", and therefore subject to tracking. Scripts are a tad more dicey security-wise, since changing or deleting files is a process outside the DBMS's control.

After all, doing grants via an insecure mechanism would kinda mess up the idea of using grants for extra security.


There is a standard and reasonable way to get at metadata: you can see the information_schema table, but you won't see rows for database objects that you don't have access to.

MySQL and MariaDB follow this plan, but there is a major exception: InnoDB. Consider INNODB_SYS_TABLES, which has information about other tables. Of course this table should not exist at all (the sensible place is information_schema.TABLES), but the more troubling fact is that the relevant privilege is not "whether you have access to the other tables", but -- wow -- the PROCESS privilege. And to top it off, in MySQL (though not MariaDB) instead of an empty table you get an error message.

Statement: select * from information_schema.innodb_sys_tables;

Response from MySQL 5.7: ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

We Live In A Just World

Therefore, here is how I can crash my MySQL 5.7 server. Provided, ironically, that I do not have any privileges on any database objects. In other words, I've logged in as a user who has been granted the minimum:

GRANT USAGE ON *.* to 'peter'@'localhost';

The important prerequisites are: MySQL 5.7.9 compiled from source, a new installation, and an unprivileged user. It doesn't seem to happen under any other circumstances. So this is not a vulnerability alert. I like to show it, though, as an illustration of the punishment that awaits violators of the precepts of privileges.

As I indicated, I've logged in, and the database is empty. Now I say:


On the client I see
ERROR 2013 (HY000): Lost connection to MySQL server during query

On the server I see
mysqld: /home/pgulutzan/Downloads/mysql-5.7.9/sql/ void Diagnostics_area::set_error_status(uint, const char*, const char*): Assertion `! is_set() || m_can_overwrite_status' failed.
18:37:12 UTC - mysqld got signal 6 ;
As this is a crash and something is definitely wrong, the information collection process might fail.

Those who live in glass houses

I don't think it would be fair to end this without confessing: us too.

For example, the ocelotgui GUI client for MySQL and MariaDB can crash if I ask it not to send /* comments */ to the server, and there is a very long comment at the end of a statement after the semicolon. We are all sinners.

However, that bug, and a few minor ones, have been found during alpha tests. I'm still hopeful that we'll go beta within a few weeks, and invite anyone to try and find an embarrassing problem before that happens. The readme and the download are on github at

Generated columns in MariaDB and MySQL

It has been seven years since the last time I blogged about generated columns, and a lot has happened -- now both MariaDB and MySQL support them. So it's time to look again, see how well they're doing, and compare to the SQL standard's Optional feature T175 Generated columns.

This is not an introductory description or an explanation why you'd want to use generated columns rather than (say) triggers and views. For that, I'd recommend the relevant manuals or the blog posts by Alexander Rubin and Anders Karlsson.

The Generation Clause

Standard            MariaDB 10.1             MySQL 5.7
---------           ------------             ---------
[data type]         data_type                data type
AS                  AS                       AS
(expression)        (expression)             (expression)
                    [VIRTUAL | PERSISTENT]   [VIRTUAL | STORED]       
[constraints]       [constraints]            [constraints]
                    [COMMENT 'string']       [COMMENT 'string']

The above side-by-side BNFs show the standard syntax and the syntax that MariaDB and MySQL actually allow at the time I'm writing this. The MariaDB manual says incorrectly that either VIRTUAL or PERSISTENT is mandatory. The MySQL manual suggests incorrectly that the clause order is fixed, and has a typo: there should be two "]]"s after "STORED".

The first important deviation from standard SQL is that "data type" is not an optional clause in either MariaDB or MySQL. The data type can be figured out from the type of the expression, after all.

The second important deviation is that [GENERATED ALWAYS] is optional and there's a way to say whether the column is virtual (column value is generated when it's accessed) or persistent/stored (column value is generated when it's set, and kept in the database). I call this a single deviation because it's got a single backgrounder: compatibility with Microsoft SQL Server. In fact the original title of the worklog task (WL#411) was "Computed virtual columns as MS [i.e. Microsoft] SQL server has". We changed it to "Generated columns", but the perfume of Microsoftness lingers, and you'll see traces in the vocabulary too. For example, MariaDB has an error message: ""HY000 A computed column cannot be based on a computed column".

So the tip sheet is: for the sake of compatibility with the standard rather than with Microsoft, always say GENERATED ALWAYS, and call it a "generated" column not a "computed" column. It's okay to say VIRTUAL, though, because Oracle does.


In standard SQL these restrictions apply:

"Every column reference contained in [the generation expression) shall reference a base column of [the same table]."
In other words, a generated column cannot be based on another generated column. MariaDB adheres to this, but MySQL, as a harmless extension, allows

"[The generation expression] shall be deterministic."
This is pretty reasonable, and both MariaDB and MySQL comply.

"[The generation expression] shall not contain a routine invocation whose subject routine possibly reads SQL-data."
This is reasonable too, but MariaDB and MySQL go much further -- they forbid every user-defined function, even if it's declared that it's deterministic and reads no SQL data.

"[The generation expression] shall not contain a query expression".
In other words, GENERATED ALWAYS AS (SELECT ...)" is a no-no. Again, reasonable, and I doubt it will occur to anyone to try.

Differences between MariaDB and MySQL

We're actually looking at two different implementations -- MariaDB's generated columns come ultimately from a user contribution by Andrey Zhakov, while MySQL's generated columns are younger and are more of an in-house development. (Update added later: Mr Zhakov deserves credit for the MySQL development too, see the comments.) Things worth noticing are:
* the PERSISTENT versus STORED syntax detail, mentioned earlier,
* GENERATED is a reserved word in MySQL but not in MariaDB,
* MariaDB has some restrictions about foreign keys that MySQL doesn't have.

MySQL lacks some restrictions about foreign keys, eh? That could lead to interesting results. I tried this sequence of statements:

                 s2 INT AS (s1) STORED,
                 FOREIGN KEY (s1) REFERENCES t1 (s1)
                 ON UPDATE CASCADE);
UPDATE t1 SET s1 = 2;

And the results from the two SELECTs looked like this:

mysql> SELECT * FROM t1;
| s1 |
|  2 |
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
| s1   | s2   |
|    2 |    1 |
1 row in set (0.00 sec)

If you're thinking "whoa, that shouldn't be right", then I'm sure you'll understand why MariaDB doesn't allow this trick.


In the standard there are two relevant INFORMAIION_SCHEMA columns: IS_GENERATED and GENERATION_EXPRESSION. In MySQL all we get is a value in the EXTRA column: "VIRTUAL GENERATED". In MariaDB also we get a value in the EXTRA column: "VIRTUAL".

I'd say that both implementations are deficient here -- you can't even see what the "(expression)" was.

Hurray For Everybody

Both MariaDB and MySQL have slight flaws in their implementations of generated columns, but my complaints here shouldn't cause worry. Both are robust, as I found out by wasting time looking for bugs. This feature can be used.

What about us?

In my last blog post I may have been over-confident when I predicted ocelotgui (our GUI for MariaDB and MySQL) would allow multiple connections and go beta this month. We will have something that, I hope, will be far more exciting.