This release contains a backwards incompatible change to the dolt_merge() procedure. It now returns the commit hash of the merge as well as fast-forward and conflicts result flags that it returned before.

Per Dolt’s versioning policy, this is a minor version bump (major.minor.patch) because consumers of the dolt_merge() results will have to update to the new return values.

Merged PRs

dolt

• 6163: add hash column to result set for dolt_merge(..., '--no-ff')
  fix for: #6159
• 6158: improve valid identifer logic
  Changed our regex for identifiers to better match MySQL.
  Now, we allow all characters within the unicode range [\u0001-\uFFFF].
  However, to utilize characters outside of ASCII range (most of unicode) they must be back-quoted

  create table `ドルト` (i int primary key);

  This works in MySQL without the backquotes, but that is a separate fix likely stemming from vitess.
  This is now valid in dolt and MySQL, which might be bad for our branching capabilities.

  create table `branch/table` (i int primary key);

  Fix for: #6156
  Docs: https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
• 6114: Better docs for sql-server command
  Provides a full example of a YAML file with all currently supported fields and their defaults (several were missing).
  Also removes the databases YAML config element.

go-mysql-server

• 1836: update cached table count in prepared statements
  Prepared statements were caching table counts. We need to update the table count when finalizing prepared statements to bring table count up to date with any intermediate edits.
• 1830: Use SO_REUSEADDR and SO_REUSEPORT options when creating the sql server on Unix
  This prevents a transient error we've been seeing where the server sometimes fails to start, and the OS claims port already in use, even though we've already confirmed that the port is not in use prior to running dolt sql-server.
• 1829: plan.TableCountLookup short circuits count()
  In many cases it is unnecessary to read an entire table to report count(*). We can use the RowCount() interface to jump to the answer.
• 1828: Consolidated collation maps
  Main file to check is the generate/main.go file.
• 1825: implement create spatial ref sys
  This implements the create spatial reference system ..., which lets users add custom SRID to the information schema.
  MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/create-spatial-reference-system.html
  MySQL is much more restrictive when it comes to what is a valid DEFINITION for an entry in this table, and the rules are unclear, so we are much more permissive for now.
  Additionally, this information persist in MySQL between server restarts, which we do not do. However, MySQL does throw a warning stating that updating may discard any changes the user makes.
  Lastly, the values persist between test runs, and we don't support deleting from information_schema, so some tests are modified.
  fix for: #6002
• 1822: join filter closure and constant join lookups
  This PR adds a set of join planning improvements.
  1. Table aliases can accept multi column indexes
     We have never been able to choose a multi-expression range scan through table aliases.
     Before:

  tmp2> explain select * from t alias where a = 1 and b = 1 and c = 1;
  +-----------------------------------------------------------+
  | plan                                                      |
  +-----------------------------------------------------------+
  | Filter                                                    |
  |  ├─ (((alias.a = 1) AND (alias.b = 1)) AND (alias.c = 1)) |
  |  └─ TableAlias(alias)                                     |
  |      └─ IndexedTableAccess(t)                             |
  |          ├─ index: [t.a]                                  |
  |          ├─ filters: [{[1, 1]}]                           |
  |          └─ columns: [a b c]                              |
  +-----------------------------------------------------------+

  After:

  tmp2> explain select * from t alias where a = 1 and b = 1 and c = 1;
  +-----------------------------------------------------------+
  | plan                                                      |
  +-----------------------------------------------------------+
  | Filter                                                    |
  |  ├─ (((alias.a = 1) AND (alias.b = 1)) AND (alias.c = 1)) |
  |  └─ TableAlias(alias)                                     |
  |      └─ IndexedTableAccess(t)                             |
  |          ├─ index: [t.a,t.b,t.c]                          |
  |          ├─ filters: [{[1, 1], [1, 1], [1, 1]}]           |
  |          └─ columns: [a b c]                              |
  +-----------------------------------------------------------+

  This has silently been impacting join performance in particular, where table aliases are more common. This is a small change but I'd expect this to have a broad positive impact for customers.
  2. Join equivalence closure
  A join like select * from xy join uv on x = u join ab on u = a has two initial join edges, x = u and u = a. Those edges create expression groupings xy x uv, uv x ab, xy x uv x ab. There misses a transitive edge, x = a, with a corresponding join group xy x ab. We should generate plans for most transitive edges now (transitive edges in apply joins are harder).
  For joins with many tables this will unlock many potential join paths.
  3. Use functional dependencies to find more lookup and merge joins
  We can use constants and aggregated equivalency sets (equal filters) to be more aggressive with lookup join selection. Previously we only searched the current join ON equal conditions for expressions that match an index prefix for a lookup join, but constants are also valid lookup keys.
  Refer to #5993 and #3797 for in-depth examples.
  4. Use functional dependencies to do better lookup join costing
  Even though we do not have index statistics, we can still use functional dependencies on indexes to detect whether a lookup will have MAX_1_ROW. Two examples where we can detect MAX_1_ROW: our lookup index is the primary key, and our lookup key provides a constant or equals expression for every pk column; our lookup index is unique, our lookup key has constants or equal expressions for every column, and we can prove that every key expression is non-nullable.
  MAX_1_ROW lookups are a rare binary condition, most of the time selectivity is in the continuous range 0-1. When they do occur they are usually the most efficient access pattern. Many of the test changes from HASH_JOIN or MERGE_JOIN to LOOKUP_JOIN are a result of this improvement. The issues linked above in (3) have practical examples.

vitess

• 247: Support FK definitions inline in column definitions
  Adds support for declaring FK references inline in column definitions. Does not support ON DELETE and ON UPDATE yet. Example: ALTER TABLE t ADD COLUMN col2 int REFERENCES other_table(id);
  Also cleaned up a few rules around non-reserved keywords to enable event to be used unquoted in ALTER TABLE statements.
• 246: support CREATE SPATIAL REFERENCE SYSTEM ... syntax
  Syntax for: #6002
• 240: Support more JSON_TABLE functionality
  Source: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

  JSON_TABLE(
  expr,
  path COLUMNS (column_list)
  )   [AS] alias
  column_list:
  column[, column][, ...]
  column:
  name FOR ORDINALITY
  |  name type PATH string path [on_empty] [on_error]
  |  name type EXISTS PATH string path
  |  NESTED [PATH] path COLUMNS (column_list)
  on_empty:
  {NULL | DEFAULT json_string | ERROR} ON EMPTY
  on_error:
  {NULL | DEFAULT json_string | ERROR} ON ERROR
  

  Note: the MySQL docs indicate that PATH is optional in the NESTED case, but it doesn't seem that way.
  I chose to follow what they say rather than what they do.

Closed Issues

• 6123: event reserved keyword quoting differences with MySQL
• 6002: Support CREATE SPATIAL REFERENCE SYSTEM query
• 6159: Feature request: call dolt_merge(..., '--no-ff') should return the commit hash
• 6156: Dolt does not appear to support all MySQL FK and Table valid naming characters