MongoDB is more difficult than MySQL

A giant leap for SQL: MySQL 8.0 released

"Are you still using SQL-92" is the opening question of my Modern SQL lecture. A surprisingly large part of the audience then quite openly admitted to using a 25-year-old technology. When asked who is still using Windows 3.1 - also released in 1992 - just a few jokers raise their hands.

Of course, this comparison is not entirely fair. Nevertheless, it shows that knowledge of newer SQL standards is in poor shape. There have been five updates since SQL-92. The currently valid version is SQL: 2016. Many developers have never heard of it.

So what many developers don't realize is that since 1999, SQL is no longer limited to relational algebra or the relational model. Indeed, SQL: 1999 introduced operations that have no equivalent in relational algebra (,) and included data types in the standard that break the classic notion of the first normal form () 0.

Since then - that is, for 19 years - it is no longer important whether an SQL function corresponds to the relational idea. It is important that a function has well-defined semantics and solves a real problem. The academic approach has given way to a pragmatic one. Today, SQL offers a practical solution to almost all data processing problems. Some correspond to the relational thought, others do not.

Intent

Say not relational database for SQL databases. SQL is really more than just relational.

It's just a shame that many developers are still using SQL as they were 25 years ago. I think this is mainly due to the lack of know-how and interest on the part of the developers1 and the weak support in the database products.

Let's look at this argument in terms of MySQL. When I look at market share, I believe that MySQL’s lack of modern SQL did more than its fair share in this sad situation. I mentioned this problem in 2013 in my article “MySQL is as bad for SQL as MongoDB is to NoSQL”. The key message is that "MongoDB is a popular but bad representative of its kind - just like MySQL is for SQL". Joe Celko put his opinion on MySQL differently: "MySQL is not SQL, it just borrows keywords from SQL".

Examples of the questionable interpretation of SQL can be found in the MySQL WAT lecture on YouTube. 2 Note that this video is from 2012 and refers to MySQL 5.5 (the current version at that time). Since then, the situation has improved significantly with versions 5.6 and 5.7. 3

It is particularly gratifying that when the new standard values ​​were introduced, a step further was thought in order to keep the effects of changed standard settings as low as possible. For example, when it was activated by default, the effort was made to include perhaps the most complete check of functional dependencies.

It was around this time - when MySQL 5.7 came out - that I stopped ridiculing MySQL. I'm just kidding, of course. Of course, I still make fun of MySQL from time to time. But it has become a little more difficult.

Speaking of which, did you know that MySQL still doesn't support constraints? As in previous versions, clauses can be specified for, but they are silently ignored. Yes, implicitly: no error message, no warning. Even MariaDB fixed that a year ago. (Update April 2019: MySQL 8.0.16 now checks constraints).

Oh, now I'm making fun of MySQL again. Sorry, old habit!

Regardless of this, the development philosophy of MySQL has changed noticeably with the last few versions. What happened? You probably already know: MySQL has been under new management since Oracle bought it with Sun. And I have to admit, that was maybe the best thing that has happened to SQL in the last 10 years. And by that I really mean SQL - not MySQL!

The reason I believe a single version of MySQL has a dramatic effect on the entire SQL ecosystem is simple: MySQL is the weakest link in the chain. If you strengthen that link, you strengthen the whole chain. Let me do this.

MySQL is very popular. Second most popular SQL database ever, according to db-engines.com. More importantly, by far the most popular free SQL database. This popularity has an impact on anyone dealing with more than just one particular SQL database. These are often independent software manufacturers who, for example, produce editorial systems (CRM), web shops or object / relational mappers (ORMs). Due to its immense popularity, it is often necessary for these manufacturers to support MySQL. Only a few of them take it upon themselves to properly support several dialects - Java Object Oriented Querying (jOOQ) stands out particularly praiseworthy here. Many manufacturers simply limit themselves to the commonly supported SQL dialect. So basically on MySQL.

Another group of people affected by the ubiquity of MySQL are people who want to learn SQL. MySQL is just too obvious for this: free and popular. That must be a good basis for learning. What you do not know is that you are building your SQL skills on the basis of the weakest SQL dialect among the common databases. Loosely based on Joe Celko, this means that these people know the key words, but do not understand their meaning correctly. Of course, they haven't heard of modern SQL either.

That all changed last week when Oracle finally released a stable version (GA) of MySQL 8.0. This version is a milestone as MySQL finally outgrows SQL-92 and the purely relational dogma. In addition to some other standard SQL functions, MySQL now supports window functions () and common table expressions (CTE,). Without a doubt, the two most important SQL functions since SQL-92.

The days are numbered when manufacturers claim that they cannot use these functions because MySQL does not support them. Window functions and CTEs are now in the documentation of the most popular free SQL database. So I boldly say: MySQL 8.0 is a small step for a database, but a giant leap for SQL4.

And it gets even better: the future is bright too! When MySQL got into the clutches of Oracle, part of the MySQL team (including the founder) split off their own version: MariaDB. It seems that MariaDB's strategy is to get MySQL users excited about their own product by introducing many new functions. In my opinion, it falls short of quality - as it did before with MySQL - but that's a different story. At this point, it's more important that MariaDB started checking constraints a year ago. So the question arises: how much longer can MySQL afford to ignore constraints? One might also ask how much longer they can bear my ridicule.

With MariaDB 10.2, window functions and CTEs were introduced in addition to constraints. At that time, MySQL was in beta with CTEs, but no window functions yet. So MariaDB moves faster. 5

With version 10.3 MariaDB plans to introduce "system versioned tables". In a nutshell: After this function has been activated for a table, the database keeps old row versions when mine executes or. In the case of queries, of course, you always get the latest valid data, unless you use a special syntax () to ask for old versions. More on this in the MariaDB announcement.

System versioning was introduced into the standard with SQL: 2011. At the moment, it looks like MariaDB will become the first free SQL database to support this feature. I hope that this is an incentive for other manufacturers and that the demand for modern SQL functions will generally increase.

Now that the implementation of modern SQL is finally moving, only one problem remains: the details! The functions of the SQL standard often consist of countless sub-functions. For this reason alone, it is common not to support all aspects. Using the example of window functions, this means that it is not enough to say that a database supports window functions. Which window functions are really supported? Which frame units (,,)? The answers to these questions differentiated a marketing gimmick from a powerful function.

In my mission to make modern SQL more accessible to developers, I test these details to highlight the differences. The results are presented in matrices as above. The rest of the article is about comparing the new standard SQL features of MySQL 8.0 with the competition. As you will see, MySQL 8.0 generally did a good job. Only the new JSON functions are an exception.

Window functions

There was SQL before window functions and there was SQL after window functions. Window functions are, without exaggeration, a "game changer". Once you understand it, you don't know how you could ever live without it. Common use cases like finding the best N-rows per group, creating running sums and moving averages, or summarizing successive events are just the tip of the iceberg. Window functions are the main tool for avoiding self-joins. That alone makes many SQL queries smaller and faster. Window functions are so powerful that even new SQL implementations such as the Apache Foundation (Hive, Impala, Spark), NuoDB or Google BigQuery introduced them years ago. So it's fair to say: MySQL is coming out a little late for this party.

The following matrix shows how well popular SQL databases support the various aspects of the clause. As you can see, MySQL even surpasses the functionality of the "world's most advanced relational open source database", as PostgreSQL describes itself on the new homepage. PostgreSQL 11 is preparing to take the lead in this area again.

The actual window functions offered by MySQL 8.0 are also almost at the current level of the competitors.

Common Table Expressions ()

The next important extension in MySQL 8.0 are the so-called Common Table Expressions (CTEs or the -Clauses). Important use cases are walking through graphs with a single query, generating any number of rows, converting CSV strings into rows (reverse /) or just literary SQL.

Here too, MySQL's first throw catches up with the top.

Other standard SQL functions

In addition to window functions and the clause, MySQL 8.0 also introduced several other standard functions. Compared to the previous two, these are rather unspectacular.

As you can see below, Oracle is pushing standards-compliant JSON support. The Oracle database and MySQL are currently leading in this area (don't forget: both from Oracle). The functions and have even been backported to MySQL 5.7.22. It is noticeable, however, that MySQL has not implemented the standard syntax for these functions. Additions such as B. a -Clauses are generally not supported. does not accept the keywords and nor the colon () to separate the attribute names from the values. It appears that MySQL treats these functions as normal function calls - not according to the syntax of the standard.

I also find it interesting that values ​​are handled just as wrongly as the Oracle database (the default behavior is 6). When you see the same discrepancy in two supposedly independent products, it's always interesting. In this case, both come from the same manufacturer - that adds that certain something to the whole thing.

The last two functions from the collection, the grouping function (belongs to rollup) and column names in the From clause, solve very specific problems. The MySQL 8.0 implementation can keep up with other databases.

In addition, MySQL 8.0 also introduces roles. The reason why these are missing in the matrix is ​​simple: My self-built tool for creating these matrices cannot currently be used by multiple users side by side. That means, I cannot currently test access rights. But that is still to come - stay tuned.

Other interesting innovations

Finally, I would like to mention a few new features that have nothing to do with the SQL standard.

One of them is about the specification for:

Most, if not all, databases use the same sorting when creating an index as they do with the -clause. The default setting is ascending. Sometimes it is necessary to sort individual columns of an index in opposite directions. That's what you use then. The MySQL 5.7 documentation says the following about this:

A specification can end in or. These keywords are allowed for future expansions in order to save index values ​​increasing or decreasing. Currently they are recognized but ignored; Index values ​​are always saved in ascending order.

"Are recognized but ignored". To be more precise, they are ignored without any warning. Analogous to -Constraints.

This has been fixed in MySQL 8.0. Now there is a warning. Don't panic - just kidding. is now taken into account.

Of course, there are countless other improvements in MySQL 8.0. The article "What's New in MySQL 8.0?" Gives a very good overview. A first taste:

About the author

Markus Winand is the SQL Renaissance Ambassador on a mission to alert developers to the evolution of SQL in the 21st century. Markus can be hired as a trainer, speaker and consultant on winand.at.

Footnotes