tl;dr: Dolt now supports INSERT…RETURNING statements, similar to MariaDB. See what this means for Dolt’s MySQL compatibility.

Dolt is an open-source version-controlled SQL database meant to be a highly compatible replacement for MySQL. This means that transitively, we should also be a replacement for other MySQL-compatible databases, such as MariaDB. A couple weeks ago, Jason wrote about creating a Dolt replica from a MariaDB source.

Like Dolt, MariaDB is also an open-source SQL database meant to be highly MySQL-compatible. It’s currently very popular, with notable users like Wikipedia (Tim has written about running MediaWiki on Dolt) and WordPress (Neil has written about running WordPress on Dolt). MariaDB was originally forked from MySQL by MySQL’s founder to be open source forever. Many of MySQL’s original developers joined the effort and they have since developed many new features.

One of these new features is support for INSERT…RETURNING statements. In an INSERT…RETURNING statement, the RETURNING clause – made up of a RETURNING keyword followed by a list of SELECT expressions – allows users to query the affected rows without having to make a separate select statement. This can be useful for a variety of purposes, for example, verifying that expected values have been inserted or for getting AUTO INCREMENT values. Similarly, Postgres also supports RETURNING clauses for DML statements, which we support on Doltgres.

It was brought to our attention by one of our users that our lack of support for INSERT…RETURNING was preventing them from switching from MariaDB to Dolt. So we decided to support it!

Adding ‘RETURNING’ Clauses to the Parser

Reserved vs Non-reserved Keywords

In order to support RETURNING clauses, we needed to extend our SQL parser’s grammar by making RETURNING into a keyword. Keywords in SQL are words that hold significance in the grammar, usually indicating a type of statement, clause, or function, and can either be reserved or non-reserved. A non-reserved keyword is a keyword that can be used as an identifier without adding backquotes, whereas a reserved keyword requires backquotes to be used as an identifier. Some keywords are reserved simply because standard SQL forbids them from being used as column or table names. Others are reserved because the database that uses them needs to make them reserved to prevent grammar conflicts.

MySQL, MariaDB, and Dolt all have yacc parsers. yacc is a one-token lookahead parser generator, meaning that the parser only examines the next single token in an input stream to determine which grammar rule to apply. As a result, some keywords need to be reserved because the parser would otherwise not know if a given token refers to a function or to an identifier.

RETURNING is a non-reserved keyword in MySQL, while it is a reserved keyword in MariaDB. Because we strive to be highly MySQL-compatible, we initially tried to support INSERT…RETURNING with RETURNING as a non-reserved keyword, but we ran into grammar conflicts. First, the parser was not able to distinguish between a table alias and the RETURNING keyword. Second, the parser was not able to distinguish between a column identifier and the RETURNING keyword. So in order to support INSERT…RETURNING statements, we had to make RETURNING a reserved keyword.

Weighing Tradeoffs

We were faced with the choice of whether we wanted to support RETURNING clauses – allowing us to be compatible with more MariaDB programs – or if we wanted to strive for perfect MySQL compatibility. We ultimately decided to make RETURNING a reserved keyword. This meant Dolt no longer works with programs that used returning as a table alias or column name without wrapping it in backquotes. But while this change makes Dolt a tiny bit less compatible with MySQL, it makes us much more compatible with MariaDB and allows more programs to use Dolt and have version-controlled data. This also allows us to easily extend our grammar to support REPLACE…RETURNING and DELETE…RETURNING statements in the future, allowing for even more MariaDB compatibility.

This is also not the only time we’ve slightly diverged from MySQL to be more compatible with MariaDB. For example, Dolt and MariaDB support CREATE INDEX IF NOT EXISTS statements even though MySQL does not. Generally, we allow Dolt to diverge a tiny bit from MySQL if it means bigger gains for users.

Creating the RETURNING Clause

Once we had our RETURNING keyword, we could build the RETURNING clause by combining it with a SELECT expression list. Since it’s possible for an INSERT statement to not have a RETURNING clause, we made it optional by making it possible to be an empty string.

returning_clause_opt:
 {
   $$ = SelectExprs(nil)
 }
| RETURNING select_expression_list
 {
   $$ = $2.(SelectExprs)
 }

Then we added returning_clause_opt to the end of all our INSERT statement grammar rules.

Handling RETURNING Clauses

Now we needed a way for the parser to communicate to the engine when an INSERT statement has a RETURNING clause and what SELECT expressions were part of that RETURNING clause. We had already defined the Insert AST (abstract syntax tree) to have a Returning field in order to handle INSERT…RETURNING statements for Doltgres. So we just needed the parser to extract the SELECT expression list from the RETURNING clause and populate the Returning field in the Insert AST.

Once we extended the Dolt parser to handle INSERT…RETURNING statements, not much additional work needed to be done to our SQL engine since we were already handling INSERT…RETURNING statements for Doltgres.

What’s Next?

We have yet to implement supporting RETURNING clauses in REPLACE and DELETE statements, but stay tuned for those new features. If you need them now for a project, file an issue and we’ll get them out ASAP. As MariaDB grows in popularity, we’ll likely continue to increase our compatibility with it.

Keep up with us by following our Github or joining our Discord. Much of our work, including the work detailed in this blog post, is driven by user feedback so if you have any feature requests, feel free to file an issue and it might make it into Dolt!