News Archive


2018-08-22 What’s New in MariaDB 10.3

Let me start with an announcement: From now on, MariaDB is being treated as a distinct database on modern-sql.com.

The reason for the inclusion in my club of major SQL databases is simple: Although MariaDB was originally described as a “branch of MySQL that is, on the user level, compatible with the main version”, both versions have diverged considerably in the past few years. At first, the differences were mostly limited to operative aspects (including some legal aspects). Over the last two years, even the SQL dialects started to diverge notably. Treating MariaDB as a distinct product is the unavoidable consequence for me.

Furthermore, MariaDB’s popularity is still growing and it seems that the MariaDB team is finally embracing the SQL standard. I must actually say that “they now embrace modern SQL standards”—not the SQL-92 standard that’s been overhauled six times.

The release of MariaDB 10.3 demonstrates this in an impressive way. Read on to see what I mean.

Contents:

  1. System-Versioned Tables
  2. The Values Clause
  3. Sequence Generators
  4. Percentile_disc and Percentile_cont
  5. Intersect and Except
  6. Two-Phase Processing of Update’s Set Clause
  7. Self-Referencing Update and Delete
  8. Off Topic: Limit in Group_Concat
  9. Event Note: Modern SQL Training in Vienna
  1. System-Versioned Tables
  2. The Values Clause
  3. Sequence Generators
  4. Percentile_disc and Percentile_cont
  5. Intersect and Except
  6. Two-Phase Processing of Update’s Set Clause
  7. Self-Referencing Update and Delete
  8. Off Topic: Limit in Group_Concat
  9. Event Note: Modern SQL Training in Vienna

(Read more)

2018-06-05 PostgreSQL Standard SQL Gap Analysis

Last week I’ve presented my “PostgreSQL Standard SQL Gap Analysis” at PGCon.org in Ottawa. If this sound familiar you might confuse it with the opposite talk “Features Where PostgreSQL Beats its Competitors” I gave at FOSDEM and PgConf.de this year.

The abstract of the gap analysis:

PostgreSQL supports an impressive number of standard SQL features in an outstanding quality. Yet there remain some cases where other databases exceed PostgreSQL’s capabilities in regard to standard SQL conformance.

This session presents the gaps found during an in-depth comparison of selected standard SQL features among six popular SQL databases. The selected features include, among others, window functions and common tables expressions—both of them were recently introduced to MySQL and MariaDB.

The comparison uses a set of conformance tests I use for my website modern-sql.com. These tests are based on the SQL:2016 standard and attempt to do a rather complete test of the requirements set out in the standard. This includes the correct declared type of expressions as well as the correct SQLSTATE in case of errors (teaser: nobody seems to care about SQLSTATE).

This presentation covers two aspects: (1) features not supported by PostgreSQL but by other databases; (2) features available in PostgreSQL that are less complete or conforming as in other databases.

You can download the slides here [PDF; 5MB].

On my Own Behalf

I make my living from training, other SQL related services and selling my book. Learn more at https://winand.at/.

Below I list the covered features with a short comment for your convenience. The slides have more information including the charts that show which databases support these features.

    (Read more)

    2018-04-25 One Giant Leap For SQL: MySQL 8.0 Released

    “Still using SQL-92?” is the opening question of my “Modern SQL” presentation. When I ask this question, an astonishingly large portion of the audience openly admits to using 25 year old technology. If I ask who is still using Windows 3.1, which was also released in 1992, only a few raise their hand…but they’re joking, of course.

    Clearly this comparison is not entirely fair. It nevertheless demonstrates that the know-how surrounding newer SQL standards is pretty lacking. There were actually five updates since SQL-92—many developers have never heard of them. The latest version is SQL:2016.

    As a consequence, many developers don’t know that SQL hasn’t been limited to the relational algebra or the relational model since 1999. SQL:1999 introduced operations that don't exist in relational algebra (with recursive, lateral) and types (arrays!) that break the traditional interpretation of the first normal form.

    Since then, so for 19 years, whether or not a SQL feature fits the relational idea isn’t important anymore. What is important is that a feature has well-defined semantics and solves a real problem. The academic approach has given way to a pragmatic one. Today, the SQL standard has a practical solution for almost every data processing problem. Some of them stay within the relational domain, while others do not.

    Resolution

    Don’t say relational database when referring to SQL databases. SQL is really more than just relational.

    It’s really too bad that many developers still use SQL in the same way it was being used 25 years ago. I believe the main reasons are a lack of knowledge and interest among developers along with poor support for modern SQL in database products.

    Let’s have a look at this argument in the context of MySQL. Considering its market share, I think that MySQL’s lack of modern SQL has contributed more than its fair share to this unfortunate situation. I once touched on that argument in my 2013 blog post “MySQL is as Bad for SQL as MongoDB is to NoSQL”. The key message was that “MongoDB is a popular, yet poor representative of its species—just like MySQL is”. Joe Celko has expressed his opinion about MySQL differently: “MySQL is not SQL, it merely borrows the keywords from SQL”.

    You can see some examples of the questionable interpretation of SQL in the MySQL WAT talk on YouTube. Note that this video is from 2012 and uses MySQL 5.5 (the current GA version at that time). Since then, MySQL 5.6 and 5.7 came out, which improved the situation substantially. The default settings on a fresh installation are much better now.

    It is particularly nice that they were really thinking about how to mitigate the effects of changing defaults. When they enabled ONLY_FULL_GROUP_BY by default, for example, they went the extra mile to implement the most complete functional dependencies checking among the major SQL databases:

    About the same time MySQL 5.7 was released, I stopped bashing MySQL. Of course I'm kidding. I'm still bashing MySQL occasionally…but it has become a bit harder since then.

    By the way, did you know MySQL still doesn’t support check constraints? Just as in previous versions, you can use check constraints in the create table statement but they are silently ignored. Yes—ignored without warning. Even MariaDB fixed that a year ago.

    Uhm, I’m bashing again! Sorry—old habits die hard.

    Nevertheless, the development philosophy of MySQL has visibly changed over the last few releases. What happened? You know the answer already: MySQL is under new management since Oracle bought it through Sun. I must admit: it might have been the best thing that happened to SQL in the past 10 years, and I really mean SQL—not MySQL.

    The reason I think a single database release has a dramatic effect on the entire SQL ecosystem is simple: MySQL is the weakest link in the chain. If you strengthen that link, the entire chain becomes stronger. Let me elaborate.

    MySQL is very popular. According to db-engines.com, it’s the second most popular SQL database overall. More importantly: it is, by a huge margin, the most popular free SQL database. This has a big effect on anyone who has to cope with more than one specific SQL database. These are often software vendors that make products like content management systems (CRMs), e-commerce software, or object-relational mappers (ORMs). Due to its immense popularity, such vendors often need to support MySQL. Only a few of them bite the bullet and truly support multiple database—Java Object Oriented Querying (jOOQ) really stands out in this regard. Many vendors just limit themselves to the commonly supported SQL dialect, i.e. MySQL.

    Another important group affected by MySQL’s omnipresence are people learning SQL. They can reasonably assume that the most popular free SQL database is a good foundation for learning. What they don't know is that MySQL limits their SQL-foo to the weakest SQL dialect among those being widely used. Based loosely on Joe Celko’s statement: these people know the keywords, but don’t understand their real meaning. Worse still, they have not heard anything about modern SQL features.

    Last week, that all changed when Oracle finally published a generally available (GA) release of MySQL 8.0. This is a landmark release as MySQL eventually evolved beyond SQL-92 and the purely relational dogma. Among a few other standard SQL features, MySQL now supports window functions (over) and common table expressions (with). Without a doubt, these are the two most important post-SQL-92 features.

    The days are numbered in which software vendors claim they cannot use these features because MySQL doesn't support them. Window functions and CTEs are now in the documentation of the most popular free SQL database. Let me therefore boldly claim: MySQL 8.0 is one small step for a database, one giant leap for SQL.

    It gets even better and the future is bright! As a consequence of Oracle getting its hands on MySQL, some of the original MySQL team (among them the original creator) created the MySQL fork MariaDB. Apparently, their strategy is to add many new features to convince MySQL users to consider their competing product. Personally I think they sacrifice quality—very much like they did before with MySQL—but that’s another story. Here it is more relevant that MariaDB has been validating check constraints for a year now. That raises a question: how much longer can MySQL afford to ignore check constraints? Or to put it another way, how much longer can they endure my bashing ;)

    On my Own Behalf

    I make my living from training, other SQL related services and selling my book. Learn more at https://winand.at/.

    Besides check constraints, MariaDB 10.2 also introduced window functions and common table expressions (CTEs). At that time, MySQL had a beta with CTEs but no window functions. MariaDB is moving faster.

    In 10.3, MariaDB is set to release “system versioned tables”. In a nutshell: once activated for a table, system versioning keeps old versions for updated and deleted rows. By default, queries return the current version as usual, but you can use a special syntax (as of) to get older versions. Your can read more about this in MariaDBs announcement.

    System versioning was introduced into the SQL standard in 2011. As it looks now, MariaDB will be the first free SQL database supporting it. I hope this an incentive for other vendors—and also for users asking their vendors to support more modern SQL features!

    Now that the adoption of modern SQL has finally gained some traction, there is only one problem left: the gory details. The features defined by the standard have many subfeatures, and due to their sheer number, it is common practice to support only some of them. That means it is not enough to say that a database supports window functions. Which window functions does it actually support? Which frame units (rows, range, groups)? The answers to these questions make all the difference between a marketing gag and a powerful feature.

    In my mission to make modern SQL more accessible to developers, I’m testing these details so I can highlight the differences between products. The results of these tests are shown in matrices like the ones above. The rest of this article will thus briefly go through the new standard SQL features introduced with MySQL 8.0 and discuss some implementation differences. As you will see, MySQL 8.0 is pretty good in this regard. The notable exception is its JSON functionality.

    1. Window Functions
    2. Common Table Expressions (with [recursive])
    3. Other Standard SQL Features

    (Read more)

    2018-02-05 Standard SQL Features Where PostgreSQL Beats its Competitors

    Last Friday I've been at the PgDay before FOSDEM in Brussels and presented a selection of “Standard SQL Features Where PostgreSQL Beats its Competitors”. Slides below.

    Next time I give this talk (in German and updated) is at the „Deutschsprachige PostgreSQL Konferenz“ in Berlin (registration is open). Find a complete list of my upcoming conference talks at winand.at. There you can also find slides for other talks.

    (Read more)

    2017-06-15 What's New in SQL:2016

    In December 2016, ISO released a new version of the international SQL standard (ISO/IEC 9075:2016). It supersedes the previous version from 2011.

    This article is a brief overview of the new features being introduced into the SQL language. Strictly speaking, this article covers the additions to part 2 of the standard (SQL/Foundation), i.e. the most commonly used part.

    This article also shows the availability of these features among six major databases. Note that respective figures—shown below—only reflect whether or not the databases support the features in the way described by the standard. For example, an X in the JSON row does not mean the database has no JSON support—it simply means the database doesn't support the JSON features described by the SQL standard. As a matter of fact, all tested databases support JSON in some way—but not necessarily in the way described by the standard.

    Before going through all the shiny new features, let's look at some trivia: part 2 of the SQL:2016 standard has 1732 pages—that's 260 pages more (~18%) than the 2011 edition. It introduces 44 new optional features (+14%). Let's take a look at them…

    1. Row Pattern Recognition
    2. JSON
    3. Date and Time Formatting and Parsing
    4. Listagg
    5. Trigonometric and Logarithmic Functions
    6. Polymorphic Table Functions
    7. Miscellaneous Features
    8. Feature Taxonomy: Obsolete and New Features

    (Read more)

    About the Author

    Photo of Markus Winand

    Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

    Buy his Book on Amazon

    Cover of “SQL Performance Explained”: Squirrel running on grass

    The essence of SQL tuning in 200 pages

    Buy on Amazon
    (paperback only)

    Paperback and PDF also available at Markus’ store.

    Hire Markus

    Markus offers SQL training and consulting for developers working at companies of any size.
    Learn more »

    “modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
    Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license