Correctness Update

September 6, 2024

3 min read

Welcome back to another installment of Dolt's monthly correctness updates! If you missed it, here's last month's update. This month, we tackled several dolt-specific issues and make improvements to long-standing trouble areas.

Dolt-Specific Improvements

From regressions to involved bug fixes, we've made a lot of changes to the Dolt side of this month.

Auto Increment with `dolt_reset()`

Dolt is a version controlled database, which introduces complications with Auto Increment columns MySQL doesn't have to deal with. Tim noticed that when restoring a dropped table using (dolt_reset()), the Auto Increment value was incorrectly reset to 1. Subsequent inserts would then fail, throwing duplicate key errors. This was a side effect of our global Auto Increment tracker, which is in charge of maintaining the auto increment value across branches. We've since fixed this issue, and the Auto Increment value is now reset to what it was at the RootValue.

Column Defaults

One of our long-term customers noticed some strange behavior when merging column defaults. The queries create table t (f float default '1'); and create table t (f float default '1.0');, but were showing up as conflicts when perform a Dolt merge. This was due to the way we were saving column defaults; we stored them as strings, which is correct for column default expressions, but not for column default literals. While this didn't affect the actual behavior of the default value, it did cause unnecessary conflicts and was annoying for users. So, we fixed it; we now normalize the column default literal values to match the column type and encoding them consistently on disk.

Case Insensitive Regression

Lately, Dolt has been making improvements to performance. For certain queries, we are able to take advantage of custom iterators directly over our prolly tree, giving us improved performance. Unfortunately, one of these changes caused a regression in case-insensitive queries that a customer noticed.

This query was always returning no results:

SELECT * FROM 
    EMPLOYEES e 
INNER JOIN 
    DEPARTMENTS d 
ON 
    e.department_id = d.ID

Meanwhile, this query was returning the correct results:

SELECT 
    *
FROM 
    EMPLOYEES e
INNER JOIN 
    DEPARTMENTS d
ON 
    e.DEPARTMENT_ID = d.ID

It turns out that we comparing the column names in a case-sensitive manner, so we got that patched, and rolled out the fix shortly after.

Triggers

A few weeks ago, we received a bug report with a complicated set of triggers. The customer had a series of tables with triggers chained together; deleting a row from one table, would trigger a delete another table, which would then cause another trigger to fire, and so on. Fixing this issue exposed several issues with our trigger implementation, specifically around aliasing and scoping. This took longer than the usual bug fix, but the issue is resolved, and our triggers are more capable than ever.

Hash In Tuple Collations

A few days ago, a user found an issue with Dolt and SQLAlchemy. Initially, we believed the issue was related to Foreign Keys, but Jason discovered that the issue actually stemmed from collations. More specifically, it was our HashInTuple operator that was causing the issue. We were hashing tuples without regard to the collation of the columns, which could be different, leading to incorrect results.

Metrics Summary

Since last time, we've increase the total number of enginetests from 43825 to 44435. A small fraction of these tests are skipped, so the total number of skipped tests is now 224. This yields a 99.50 percent pass rate.

Demand for the missing MySQL functions has been low so our attention has been around customer issues, so there haven't been any new functions added to Dolt. However, the docs were incorrectly listing a few internal-use-only MySQL functions, so we removed them from the docs. This means that we support 308 out of 438, netting 71 percent coverage.

Conclusion

That's all for this month's correctness update. We're always working to improve Dolt, so if you have any issues or feature requests, please let us know. You can reach us on Discord.

Blog