Improvements to SQL Function Support
Dolt is a versioned database that has a mix of Git and MySQL features.
One of our goals is to be a drop-in replacement for MySQL.
As a result, we need to support all the functions MySQL does; these are things like SIN(), CONCAT(), CURRENT_TIMESTAMP(), etc.
Over the years, we've had many customers ask for support for various functions, and we've been diligently adding them. At the time of writing this, dolt supports 262 of 438 of MySQL's functions.
Recent Improvements
11 of these functions were implemented roughly 2 weeks ago.
CHAR()ORD()ELT()FIELD()OCTET_LENGTH()SPACE()
ATAN2()PI()EXP()
BIT_COUNT()
QUARTER()
Future Improvements
Although feature requests from customers for MySQL functions on our Github have slowed down, there are still quite a few functions that we need to implement to achieve full MySQL drop-in replacement status. Don't hesitate to reach out if you need a function that we don't support yet; we can usually turn around requests in less than 24 hours.
DateTime, Timestamp, Date, and Time Functions
MySQL has a plethora of functions for working with dates and times, and we have only implemented a subset of them.
Recently, Jason added support for SUBDATE() at a customer's request.
While here, we noticed that we're actually missing quite a few functions in this area; we lack support for ADDDATE(), ADDTIME(), SEC_TO_TIME(), just to name a few.
Digging further, we uncovered a variety of issues with the way we display and handle dates and times.
Information Functions
MySQL has a few information functions that provide a variety of information about the database. These range from performance, information about certain strings, and information about user permissions.
A few years ago, Daylon added support for collations and charsets.
This was a huge step forward for Dolt, and came with information functions, COLLATION() and COERCIBILITY().
However, we are still missing support for the function CHARSET().
We implemented a few of the basic information functions (like VERSION() and ROW_COUNT()), but we are missing:
CURRENT_ROLE()BENCHMARK()ICU_VERSION()ROLES_GRAPHML()SESSION_USER()SYSTEM_USER()
Spatial Functions
A while ago, we announced support for Spatial Types and a year later we announced support for Spatial Indexes. These milestones included support for a variety of Spatial Functions. We have support for the more commonly used functions.
Functions that create geometries:
POINT()LINESTRING()POLYGON()- ...
Functions that encode/convert geometries:
ST_AsText()ST_AsBinary()ST_AsGeoJSON()- ...
Functions that do basic spatial analysis:
ST_DIMENSION()ST_LENGTH()ST_EQUALS()- ...
We also support a function that isn't in MySQL:
ST_PERIMETER()
Unfortunately, most of the missing functions are the ones that do more advanced spatial analysis. Mainly, we are missing functions ones that compare multiple geometries:
ST_TOUCHES()ST_UNION()- ...
And their Minimum Bounding Rectangle (MBR) counterparts:
MBRContains()MBREquals()MBRIntersects()- ...
JSON Functions
Lastly, we are missing a variety of JSON functions.
JSON type support along with many of our supported JSON functions were added by Andy.
Since then, we've added a few more commonly used functions, like JSON_EXTRACT(), JSON_ARRAY(), and JSON_OBJECT().
However, we are still missing a few functions that are less commonly used:
JSON_DEPTH()JSON_KEYS()JSON_MERGE()JSON_MERGE_PATCH()
Conclusion
As usual, Dolt is actively closing the gap to becoming a versioned database with full MySQL compatibility (PostgreSQL too!). In terms of SQL function support we've come a long way, but we still have a ways to go, and are always looking for feedback from our users. If any of the mentioned functions are important to you, please let us know by making an issue on our Github or by joining our Discord and chatting with us directly. We are very responsive and can push out releases within 24 hours. Dolt is open source, so if you feel inclined to implement any of these functions yourself, we'd be more than happy to review your pull request.