We are excited to announce the release of significant enhancements to Dolt's SQL implementation, as well as a new feature, prompting a minor version bump to 0.18.0.

Diffable Queries

Dolt's diff command now supports a -q flag which will evaluate the results of a query at two commits and compare them:
dolt diff -q <query>

This gives users a richer programmatic interface into analyzing the impact of changes through time by showing them the exact changes in, potentially complex, queries at various points in the commit graph. For example, consider the National Vulnerabilities Database on DoltHub:

%dolt diff p30hoseurm9qfl7jhb9t4l7jfnshk6v9 -q 'select floor(impact_score), count(*) from cve group by floor(impact_score)'
+-----+-------------------------+----------+
|     | FLOOR(CVE.impact_score) | COUNT(*) |
+-----+-------------------------+----------+
|  <  | <NULL>                  | 8468     |
|  >  | <NULL>                  | 8428     |
|  <  | 1                       | 3125     |
|  >  | 1                       | 3124     |
|  <  | 2                       | 41465    |
|  >  | 2                       | 41668    |
|  <  | 3                       | 14324    |
|  >  | 3                       | 14318    |
|  <  | 4                       | 5385     |
|  >  | 4                       | 5418     |
|  <  | 5                       | 22039    |
|  >  | 5                       | 22034    |
|  <  | 6                       | 33798    |
|  >  | 6                       | 33890    |
|  <  | 9                       | 282      |
|  >  | 9                       | 283      |
|  <  | 10                      | 16798    |
|  >  | 10                      | 16864    |
+-----+-------------------------+----------+

This shows the changes in the counts of various vulnerabilities across a range of impact score buckets. We are excited to continue enhancing the richness of the programmatic interfaces we provide into Dolt's commit graph to facilitate more robust automated interactions.

Foreign Keys

Many of the major use-cases identified for Dolt involve the need to sync with existing RDBMS systems. With this in mind we are committed to moving towards total SQL compatibility as quickly as possible. A major piece of this is foreign keys. Many application servers use foreign keys to maintain valid structure around their data, and not supporting those constructs would make it tough to replicate an existing table structure that used them without manual schema manipulation.

In this release we now support inter-table foreign keys. We do not yet support intra-table foreign keys (i.e. a constraint on a table against the same table), which is less common, though allowed in the standard. We will support such constraints in a future release.

New SQL functions

We added support for the list of functions below. You can see an index of functions that we support relative to the MySQL standard in our docs.

GET_LOCK
IS_FREE_LOCK
IS_USED_LOCK
RELEASE_LOCK
RELEASE_ALL_LOCKS
ASCII
BIN
BIT_LENGTH
SIGN
UCASE
UNHEX
ACOS
ASIN
ATAN
CRC32
COS
COT
HEX
DEGREES
RADIANS
SIN
TAN
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DAYNAME
MICROSECOND
MONTHNAME
TIME_TO_SEC
WEEKOFYEAR
DATE_FORMAT
WEEK

Merged PRs

• 778: Bh/update gms
• 777: Merge Block
  As discussed (and in relation to #773), we will fail on all merges where the schemas are not the same. As a result, one index bats test no longer makes sense, and all other tests that rely on the behavior have been skipped/commented out.
• 776: moved query_diff to diff -q
• 774: Foreign Keys Part 3 Episode 2
  The last bit of things to add for foreign keys!
• 772: Zachmu/show indexes
  Implemented new interface methods needed by latest go-mysql-server
• 771: Foreign Keys Part 3 Episode 1
  Here are the merge changes along with some bats tests
• 770: /Jenkinsfile: Remove stages that are now in github actions
• 769: /.github/workflows/ci-compatibility-tests.yaml: Add compatibility test github actions
• 768: Added GROUP BY support, tests to dolt query_diff
• 767: /.github/workflows/ci-check-repo.yaml: Iterating on github actions check repo
  looking into fixing this: https://github.com/liquidata-inc/dolt/pull/767/checks?check_run_id=808971285#step:4:296

  2020/06/25 21:04:02 Error running `git merge-base remotes/origin/db/ci-github-actions-check-repo remotes/origin/master` to find merge parent: exit status 128
  exit status 1
  

  the exit status 128 is from //go/utils/checkcommiters/main.go ... im thinking could be the go version? i had to use 1.13 here to get past the error that occurred when i used go version ^1.13 regarding the -mod=readonly flag....
• 766: Andy/json dates
  fix addressing #755
• 764: /.github/workflows/ci-bats-tests.yaml: Add dolt bats tests ci github actions
  Seems to run the tests for both linux and macos ... need to fix the broken tests that have specific dependency issues
• 762: Db/ci GitHub actions go
  Run go tests for dolt on PRs
• 760: /.github/workflows/bump-brew-formula.yml: Attempt to fix syntax and dispell yaml syntax error
  My best guess at the correct syntax... not a huge fan of actions rn lol
• 758: Added skipped test for json DATETIME bug.
  Also, added || false on some regexes to create deterministic behavior in old versions of bash.
• 757: .gitmodules,proto/third_party/golang-protobuf: Remove unused golang-protobuf submodule.
• 754: proto/{third_party,Makefile}: Adopt protobuf-go and grpc-go for protobuf message generation.
• 753: Foreign Keys Part 3
  This PR implements the commit functionality, along with commit --force.
• 750: bats/remotes.bats: Add bats tests, some skipped, for some remote branch ref handling tests.
• 749: go/go.mod: go get -u ./....
• 747: bats: Change remotesrv pid handling to capture pid of background process.
• 746: query_diff via lazy projections
  Another iteration of query-diff. The methodology is:
  • Generate a query plan at each RootValue from & to
  • Alter the query plan to lazily evaluate projections. This allows access to any column that is used in ordering the query results
  • Determine the order of the query results by extracting all primary key columns and SortFields
  • Iterate over the modified query plans, diffing their results by comparing row order. Then evaluate the projections to produce the final diff
    This version does not yet incorporate Noms layer diffing. It also does not yet handle aggregate functions.
    depends on dolthub/go-mysql-server#127
• 745: README.md: Correct GOROOT to GOPATH in installation instructions.
• 743: Upgraded go-mysql-server and vitess
• 740: /benchmark/sql_regressions/run_regressions.sh: Iterating on timing commands
  spits out timing info for these different commands
• 738: go/store/datas: FindCommonAncestor: Fix potential for explosive growth if RefsByHeight queues if the same references are visited multiple times.
• 737: First draft of GitHub Action for bumping homebrew on tag
  This relies on us being able to keep a private secret on this repo. I know the repo is public but I believe we can keep a token private to repo admins and then provide access to that to the GitHub Action so that it can raise a PR against the Homebrew repository?
• 735: Release
• 139: Added format script
• 138: Andy/query diff 4
• 135: Zachmu/show create indexes
  Added non-primary keys to SHOW CREATE TABLE output
• 134: test fix
• 133: Zachmu/show indexes
  Fixed show index statements to work with native indexes. Added several new methods to Index interface, which will break existing integrators.
• 130: more functions
• 129: pow fix: these children aren't twins
• 128: Zachmu/wsl fix
  Added WSL-checking to socket checking code for linux, as it appears to be broken. In this case, don't do socket polling, same as Windows and Darwin. Also added additional trace logging to handler.go, and moved the socket polling goroutine to its own method.
• 127: made Project() public
• 125: sort by function name
• 124: Zachmu/vitess upgrade
• 123: Zachmu/index regression
  Fixed bug in ascend / descend index lookups, added engine tests for same. Also realized that the in-memory unmergeable index implementation didn't support ascened / descend, so fixed that.
• 122: Fixed a broken test (seemingly broken forever?)
• 121: Removed CODEOWNERS, require manual review request for PRs
• 120: crc and hex funcs
• 119: Zachmu/readme
  Updated docs and README
• 118: datetime functions
• 117: trig functions

Closed Issues

• 765: db abilities
• 755: From User: Datetime columns in JSON output not returned
• 741: README refers to 'GOROOT' incorrectly