This is a development release, including several new features and performance improvements.

Merged PRs

go-mysql-server

• 445: Allow limits and offsets to use value args (? in prepared statements) This fixes #439
• 441: Test fixes for savepoints
• 440: /{.github,go}: bump min go version to 1.15
• 437: /.github/workflows/format.yml: enable autoformatting
• 435: /.github/workflows/bump-dependency.yaml: remove working dir, doesnt exist
• 434: Rewrote transaction tests to be much more succinct, and added several more
• 433: Fixed non-deterministic test query
• 432: Repro test case for ErrFieldMissing bug
• 431: Fixed foreign key errors in NTC test setup
• 430: Fixed bug in field indexes related to pushdown of indexes in subqueries
• 429: Added statement boundaries
• 427: Initial percent_rank fn
  • percent_rank function implemented, which is basically the step function quantile of column-sorted rows within a partition.
  • moved helpers to window/window.go
  • created WindowExpression interface to facilitate helper function re-use (there are multiple ways of doing that, extra interface isn't super necessary but made sense to me at the time) todo:
  • bats with great expectation specific query
• 426: added ErrIndexOutOfBounds test
• 425: Bug fixes for date comparisons This fixes https://github.com/dolthub/dolt/issues/1572
• 424: Transaction support This change:
  • Moves a bunch of transaction and other session management code out of Handler into Engine
  • Introduces a new set of tests around transactions
  • Fixes the error message for duplicate key violations
  • Removes AsyncNode and related code
  • Eliminates duplicate query parsing
• 423: Vinai/distinct expressions This pr does the following
  1. Fix incorrect behavior with AVG
  2. Enable the use of DISTINCT within aggregation eg. SUM(DISTINCT )
  3. Enables the use of DISTINCT on aggregation that is grouped
• 422: Fix for hanging joins when the secondary table is empty using an in-memory strategy This fixes #222
• 421: fix bug in join search
• 420: Update sqlogic test harness and bump sqllogic dependency This pr enables users of gms to run and parse the sqllogic harness against all of the sqlogictests (with filtering). It also bump the sqllogic dependency
• 419: [WIP]: LastIncrementId and name resolution collision fix
• 418: Add ISNULL(expr) and compatible with Navicat Premium When I use Navicat Premium 15.0.28 to design tables. Get error: 1105 - function: 'isnull, maybe you mean ifnull?' not found After I implement ISNULL(expr). Get error: 1105 - column "PARTITION_DESCRIPTION" could not be found in any table in scope This RP is fixed this problem.
• 417: set autocommit status flag
• 416: ReleaseSavepoint fix
• 415: Rollback and savepoint support
• 414: Vinai/show create table check constraints This pr allow for the printing of check constraints during "SHOW CREATE TABLE"
• 410: Commit transactions without beginning them, to support commit without a selected database
• 409: Add the partitions table Adds the empty partition table
• 404: sql/analyzer: Push filters under SubqueryAliases which they apply to. This allows optimizations to be applied to the filter clauses within the SubqueryAlias node, including pushdown and index lookup.
• 403: sql/analyzer: Split analysis of subqueries so that once-after rules are applied uniformly. This makes is so that rules through default-rules get applied as part of analyzing the subquery. Later, early in the once-after phase, union and subquery queries get their own once-after processing. This allows us to keep the node tree in a state where we can apply transformations and optimizations across Opaque node boundaries, and come back for further processing later.
• 402: Added _binary support & REGEXP_LIKE Removed REGEXP_MATCHES as it does not exist in MySQL. It is a PostgreSQL function. 5.7 documentation: https://dev.mysql.com/doc/search/?d=12&p=1&q=regexp_matches 8.0 documentation: https://dev.mysql.com/doc/search/?d=201&p=1&q=regexp_matches PostgreSQL: https://www.postgresql.org/docs/9.6/functions-matching.html It was added before we forked the project, so I removed it. Not sure why it was ever added to begin with, as no MySQL client will ever expect it nor know how to interact with the results.
• 401: Vinai/json contains Adds the json_contains function
• 400: Support for transactions. Start transaction and Commit implemented so far, as well as @@autocommit handling Needs tests, which are harder to write than standard engine tests
• 398: Fixed view test script
• 397: Changed default handling & delay fk table resolution
• 396: /server: Unwrap netutil.ConnWithTimeouts in pollForClosedConnection
• 395: Refactor nil check handling nil responses won't throw check constraint errors anymore
• 394: Fixed bug that only allowed some select statements as view definitions
• 392: Fixed REPLACE logic to now match MySQL Previously we thought that REPLACE was a DELETE then INSERT, but this is not the actual order that MySQL takes. According to the MySQL documentation (https://dev.mysql.com/doc/refman/8.0/en/replace.html):

  MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE):
  Try to insert the new row into the table
  While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
  Delete from the table the conflicting row that has the duplicate key value
  Try again to insert the new row into the table
  

  Special emphasis on the While, as you have to keep trying until you either get a different error, or it succeeds. So this has now been duplicated.
• 391: Install a format checker script and workflow
• 390: Adds the key column usage table https://dev.mysql.com/doc/refman/8.0/en/information-schema-key-column-usage-table.html
• 389: Vinai/constraints table Created new pr due to past problems
• 388: Added savepoint no-ops
• 387: server/handler.go: Use exposed mysql.Conn.Conn instead of trying to track it through Accept.
• 385: fix deadlock with exchange node go routines This fixes deadlocks in exchange node go routines which can happen when the exchange node is closed before all the data is read as occurs with a query that includes a limit clause. This also can occur if the context is canceled. These deadlocked go routines will retain references and prevent garbage collection.
• 382: Fixed system variables
• 381: Vinai/insert ignore into This PR adds support for INSERT IGNORE INTO. It is currently missing functionality for ignoring typer errors. After scoping the type errors component of this it looked to a lot of work to capture all of them with the correct MySQL notation I also noticed a lack of partition support in MySQL terms as well so I ignored that.
• 380: add existing row to unique constraint failure
• 379: sql/plan/if_else.go: Handle conditional expressions evaluating to null as false.
• 378: sql/{plan,analyzer}: Add hash lookup functionality for equality condition joins on already cached results.
• 377: sql/plan/join.go: Improve correctness and testing of right joins with scope rows available.
• 376: Bug fix: check constraints were being marked invalid for tables with upper case letters
• 375: Missing table name for error
• 374: Added Means To Make This Work on Android This change has the netstat ability of the MySQL server assume that if it can't access /proc/sys/kernel/osrelease, then it'll be unlikely to be able to access any other /proc files such as /proc/net/tcp and /proc/net/tcp6. For a more specific check for Android, one can check if the getprop command exists in the PATH environment as getprop is an Android specific command to check the build properties for the system.
• 373: Added new file full of session variables, plus new types I'm sure there are mistakes in here. This was like 85% manually done, and I had lots of mental TODOs while working through it, so I very well may have forgotten to edit a variable, add a note, or change the type. Either way, the majority of variables are here. I did exclude some as I couldn't find any GLOBAL/SESSION or Dynamic information for them, but this is like 98% of the variables. Now to hook them up and replace our current variable handling.
• 372: Fixed incorrect TestModifyColumn tests Altering the type and such shouldn't change the index properties, but our tests were enforcing the removal of primary keys, which is incorrect. This has been fixed.
• 371: Added ALTER COLUMN DEFAULT
• 370: sql/analyzer/life_ctes.go: Add an analyzer rule to lift CTEs so they are visible across unions and distinct nodes.
• 369: sql/expression/case.go: Add DebugString.
• 368: Improvements and corrections for check constraints Biggest changes:
  1. Better validation
  2. Checks in CREATE TABLE statements work as expected now (poor validation before)
  3. ALTER TABLE DROP CONSTRAINT works as expected (doesn't assume a foreign key)
  4. Unnamed checks are expected to be named by integrators now
• 367: Support multiple statements per ALTER
• 366: Vinai/group concat This PR adds support for the Group_Concat Aggregation Function
• 365: Fix SELECT INTO ... bug for AUTO_INCREMENT columns
• 364: Driver improvements for Dolt It turns out Dolt needs to have control over how sql.Sessions are created, and it needs to modify the *sql.Context prior to query execution. This MR adds two interfaces:
  • driver.SessionBuilder
  • driver.ContextBuilder When a driver is created, with driver.New, if the provider implements either of these interfaces, then the provider's implementation will be used for creating sessions/contexts. Otherwise, a default implementation is used. The Dolt provider can then implement those two interfaces and do the necessary work to setup the session/context:
  • New SQL session
  • Create a dolt session (wrapping a sql session)
  • For each Dolt database in the SQL catalog:
  • Call doltSession.AddDB
  • New SQL context
  • Create a sql context
  • For each Dolt database in the SQL catalog:
  • Get the working root of the corresponding Dolt env
  • Call doltDatabase.SetRoot
  • Call sqle.RegisterSchemaFragments Prior to adding SessionBuilder, attempting to execute a query would panic, when Dolt attempts to coerce the session into a Dolt session. Prior to adding ContextBuilder, some features would not work - for example, queries had no access to uncommitted changes. For reference, this is the Dolt driver implementation I'm working on.
• 363: sql/expression/case.go: Type a CASE expression by considering all its branches. Implements a lot of the typing behavior described here: https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html#operator_case
• 362: Give correct error code for duplicate database. Return a Vitess error with code ERDbCreateExists(1007) when trying to create a database that already exists
• 360: Brought sql.PushdownTable back to memory.Table And made its implementation not reduce its schema (same as dolt). sql.FilteredTable has significant issues, so that one is still disabled by default.
• 359: Added IsGenerated to sql.Index
• 358: sql/analyzer/indexes.go: Avoid N^2 behavior in index merge for large indexed InTuple expressions.
• 356: Implement a stdlib SQL driver This PR implements a basic database/sql/driver.Driver. With this driver, sqle.Engines can be queried via database/sql. Also closes #361
• 354: Fixed procedure params not being used in INSERT/REPLACE values Fixes https://github.com/dolthub/dolt/issues/1496

vitess

• 76: Disallow non integer values for limit and offset, allow value args
• 74: add parsing for create temporary table
• 73: go/vt/sqlparser: Follow MySQL in making ORDER BY/LIMIT on left hand of UNION a syntax error.
• 72: Added savepoint and friends
• 71: go/mysql/conn.go: Expose underlying net.Conn of the mysql.Conn.
• 70: Changed SET syntax
• 69: Added ALTER COLUMN DEFAULT
• 68: Support multiple statements per ALTER
• 67: fix separator parser for Group_Concat

Closed Issues

• 439: Error: Unsupported feature: LIMIT with non-integer literal
• 361: memory database with JSON column cannot be updated