News Archive

Markus recently published a video in which he talks about Java & SQL, and that lead to a little discussion with me on Twitter. The conclusion Markus often came to is that one has to revert to writing plain SQL for some of the more advanced use cases because of limitations that JPA imposes. I agree that JPA could use an update to support some of the more advanced features of DBMS like set operations, CTEs and recursive CTEs, but that will take some time. Adding such features to a JPA implementation like Hibernate is a first step, but one has to convince a broad audience of people about the benefits. Sometimes people can’t imagine how an ORM could make use of these more advanced DBMS concepts. To avoid having to convince people, I started developing Blaze-Persistence, a query builder API that lives on top of JPA – it implements support for these advanced concepts with the JPA model.

In the following article I will discuss some of the problems and limitations Markus discovered and provide alternate solutions that work with the JPA model by using Blaze-Persistence.

1. Pattern #1 - Data listing
2. Pattern #2: Search

(Read more)

I’ve recently presented my new talk “Java & SQL — Stronger Together” as a free webinar.

(Read more)

Back in 2017, Dimitri Fontaine asked me about an interview for his upcoming book. Now there is a second edition, “The Art of PostgreSQL” (affiliate link). We thought take this opportunity to publish this interview here.

1. Affiliate Links

(Read more)

The first on-premises download of the Oracle Database 19c became available in April 2019. In the old numbering scheme, this release would just be a patch release, so it is no surprise that this release is “focused on fixing known issues, rather than adding new functionality”.

There are nevertheless some enhancements in the supported SQL dialect and other noteworthy changes.

1. Listagg(distinct …)
2. Extensions to Existing JSON Functions
3. New JSON Functions
4. Standard SQL/JSON Path Features
5. SQL/JSON Path Extensions
6. Desupported Features
7. Oracle Documentation

(Read more)

PosgreSQL 11 was released four months ago and my review is long overdue. Here we go!

With respect to standard SQL, the main theme in PostgreSQL 11 is window functions (over). For almost eight years, from 2009 until 2017, PostgreSQL was the only major free open-source product to support SQL window functions. Just a year later, by September 2018, all open-source competitors have caught up…and some even overtook PostgreSQL. The PostgreSQL community was prepared. PostgreSQL 11 was just released in 2018, and it has restored and even expanded its leadership position.

This article explains this race and covers other improvements in PostgreSQL 11.

1. Complete SQL:2011 Over Clause
2. Create Procedure and Call
3. Parameters in Fetch First|Next
4. Relative XPath Expressions
5. Other News
6. Extra: Create Index … Include

(Read more)

SQLite is an undervalued database—some people even believe it is a toy database that is not suitable for production use. In fact, SQLite is a very solid database that can handle terabytes of data, but it doesn’t have a network layer.

SQLite is “just” a library, not a server. That, of course, makes it inappropriate for some use cases, and at the same time it makes it the best fit for many other use cases. Really…many other use cases. SQLite even claims to be the most widely deployed and used database engine. I guess that is only possible because SQLite is in the public domain. SQLite is your go-to solution whenever you want to use SQL to store structured data in a file.

The SQL dialect of SQLite is also very strong. For example, it introduced the with clause four years before MySQL did. Lately, it has even added window functions—just five months after MySQL did the same.

This article covers the SQL enhancements that were added to SQLite in 2018, i.e. the new SQL features introduced in versions 3.22.0 through 3.26.0.

1. Boolean Literals and Tests
2. Window Functions
3. Filter Clause
4. Insert … on conflict (“Upsert”)
5. Rename Column
6. Coming Next on Modern-SQL.com

(Read more)

Back in February 2018, Oracle released version 18c of their database for users of the Oracle Cloud and Engineered Systems. It took another five months⁠—until July—before a download became available for on-site installation. After three more months⁠—in October—the free Express Edition (XE) was also upgraded to 18c. I guess it’s safe to say that Oracle Database 18c is finally released. Now it’s time for me to have a look at it from an SQL standards perspective.

Please keep in mind that Oracle Database 18c is just a “minor” release⁠⁠—even though the previous version was 12.2. Why is this? Oracle just decided to use the last two digits of the release year for the version number from now on.

1. JSON
2. Polymorphic Table Functions (PTF)
3. Group By () on Empty Input
4. Listagg without Within Group (Order by ...)
5. Beyond Standard SQL
6. Coming Next on Modern-SQL.com

(Read more)

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.

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)

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].

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)

“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.

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:

1. No proper group by handling (per default)
2. Not following joins to PRIMARY KEYs or UNIQUE constraints

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. (Update April 2019: MySQL 8.0.16 finally honors check constraints)

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 ;)

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)

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)

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.

1. In the from clause
2. Some functions conform (by “coincidence”)
3. Some minor omissions
4. No on overflow clause • Limited distinct

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)