Welcome back to another installment of Dolt's monthly correctness updates! Here's last month's update, in case you missed it. This month, a couple new Dolt customers came to us with a variety of issues; they uncovered panics, syntax errors, and even had some feature requests. The team has been hard at work resolving these issues usually in less than 24 hours, resulting in an improved Dolt experience.

Stored Procedures

Stored Procedures are a handy feature in SQL databases that allow you to store and execute a set of SQL commands. A customer heavily relied on stored procedures in their workflow, and they found a couple issues with Dolt's implementation.

IF Statements Panic

The first issue was that IF statements would panic, specifically when the IF statement contained a subquery.

For example:

CREATE PROCEDURE p()
BEGIN
    IF (SELECT 0) = 0 THEN
        SELECT 'hi';
    END IF;
END;

Calling procedure p would panic.

It turns out the analyzer was not properly handling subqueries within stored procedures. The fix was to run the rule finalizeSubqueries over stored procedure bodies.

INSERT Statements Throw getfield index errors

The same customer also discovered that INSERT statements inside stored procedures would error.

For example:

CREATE PROCEDURE p()
BEGIN
    IF (SELECT COUNT(*) FROM t) = 0 THEN
        INSERT INTO t VALUES (1);
    END IF;
END;

Fortunately, the fix was very similar to the previous issue; we needed to run the assignExecIndexes rule over stored procedure bodies and over IF conditionals.

DECLARE CONTINUE HANDLER Support

Lastly, the same customer requested that we add support for DECLARE CONTINUE HANDLER in stored procedures. DECLARE ... HANDLER is a way to deal with exceptions in stored procedures. MySQL allows you to define three types of handlers: CONTINUE, EXIT, and UNDO. While Dolt was able to parse these handlers, we were incorrectly terminating execution when encountering a CONTINUE handler. To fix, we just had to not do that.

Syntax Errors

This month, users ran into a few shortcomings with our syntax support.

VALUES Syntax

Dolt has supported the VALUES table constructor for a while now, but not in all contexts. We support VALUES in INSERT statements and as a table reference in SELECT statements, but not as a standalone substitute for SELECT statements.

So, we added support for it.

Now, this works:

tmp/main*> values (1, 2, 3), (4, 5, 6);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
| 1        | 2        | 3        |
| 4        | 5        | 6        |
+----------+----------+----------+
2 rows in set (0.00 sec)

Note: VALUES still doesn't work as a select_expression; statements like SELECT VALUES(...) will still error.

Parentheses Around Table References

Oddly, our parser was unhappy with parentheses around tables.

For example, these queries would fail:

select * from (t);
select * from (t1) join (t2) where t1.i = t2.j

Fortunately, the fix was some simple recursion to unwrap the parentheses.

Now, you can wrap your tables with as many parentheses as you want.

tmp/main> select * from (((((t)))));
+---+------+
| i | t    |
+---+------+
| 1 | hi   |
| 2 | hello|
+---+------+
2 rows in set (0.00 sec)

Backticks in System Variables

Some ORMs like to use backticks when referencing system and user variables, which caused problems for Dolt's parser.

For example:

SELECT @@GLOBAL.`innodb_autoinc_lock_mode`, @@`innodb_autoinc_lock_mode`, @`user_var`;
+-------------------------------------+------------------------------+-------------+
| @@GLOBAL.`innodb_autoinc_lock_mode` | @@`innodb_autoinc_lock_mode` | @`user_var` |
+-------------------------------------+------------------------------+-------------+
|                                   1 |                            1 |          42 |
+-------------------------------------+------------------------------+-------------+

The fix required some more involved parser changes, but now Dolt can handle backticks in system and user variables.

So, you can now have spaces in your user variables if you really want:

tmp/main*> set @abc.`def ghi` = 100;
tmp/main*> select @abc.`def ghi`;
+--------------+
| @abc.def ghi |
+--------------+
| 100          |
+--------------+
1 row in set (0.00 sec)

Miscellaneous

Alongside those fixes, we also patched up a various other issues:

• fix update join queries over tables with update trigger
• fix errors for UPDATE IGNORE ... JOIN queries
• added syntax support for ADD CONSTRAINT FOREIGN KEY
• added syntax support for AS clause in ON DUPLICATE KEY UPDATE queries
• throw error when converting improperly utf8 encoded strings to CHAR or VARCHAR columns
• add support for AUTO_INCREMENT on TEMPORARY tables
• prevent tag collisions on TEMPORARY tables

Metrics Summary

Since last month, we've increase the total number of enginetests from 43215 to 43578. The number of skipped tests has increased from 204 to 218. This yields a 99.50 percent pass rate.

This month has been centered around customer issues, so there haven't been any new functions added to dolt. We're still at 308 out of 438 functions, so 70 percent coverage.

Conclusion

This was a particularly fruitful month for squashing bugs. We're committed to providing a reliable and correct version-controlled SQL database, and resolving customer issues makes Dolt better for everyone. If you have any features or bugs you want us to prioritize, don't hesitate to make an issue on our Github Issues page. Additionally, join our Discord to chat with the team and other Dolt users.