Q2 2021 Release

This is a development release. It contains many features and performance improvements added since the last release.

Merged PRs

• 353: Column aliases for subqueries
• 342: Added partial SIGNAL and DECLARE CONDITION support
• 341: JSON_OBJECTAGG
  This pr adds JSON_OBJECTAGG functionality: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_json-objectagg
• 340: Vinai/UUID
  This pr adds UUID support cc: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_is-uuid
• 339: Wrap all select expressions in aliases with their original string expression
  This makes the result set schema exactly match the select expressions in the query.
• 338: Vinai/dbeaver fixes
  This pr allows for queries like CREATE TABLE x.table(pk int) when the mysql client is not set to x.
• 337: Vinai/more table status fixes
  This pr fixes some mistakes in show table status where
  1. memory/table.go did not implement StatisticsTable
  2. ShowTableStatus is a part of Engine InformationSchema Tests
• 335: Updates to SHOW TABLE STATUS
• 334: Fixed procedure parameters missing in subqueries
• 333: SHOW CHARSET & SHOW ENGINE
  First step in getting some sql engines
• 332: JSON Values
  This change moves the engine towards representing JSON values as Go values rather than strings.
  I'm strongly considering a

  type JSONValue interface{}
  

  There seems to be no precedent for this
• 331: sql/{plan,analyzer}: {join,indexed_join}: Update join logic to work within subqueries when query scope is non-empty.
• 330: Support for WITH clauses (common table expressions)
• 329: sql/analyzer: join_search: Update indexed join search to respect structural requirements on left and right joins.
  Only commute join nodes when they are actually safe to commute.
• 328: Vinai/load data quick fixes
  Add quick fixes from testing load data on the Dolt side.
• 327: Add functionality and tests for JSON_ARRAGG.
  This pr does not include window function capabilities
• 326: Added function stubs for unimplemented JSON functions
  31 unimplemented JSON functions, including two aggregation functions JSON_ARRAYAGG and JSON_OBJECTAGG.
• 325: sql/analyzer/indexed_joins.go: Use partial indexes for indexed joins when a full index isn't available.
• 324: sql/analyzer/filters.go: Never push a filter expression with a subquery down.
• 323: sql/analyzer/prune_columns.go: Fix scope parameter in fixRemainingFieldsIndexes when fixing Child of a subquery alias.
• 322: sql/{plan,analyzer}: Cache subquery results when joining against a subquery.
• 321: Bug fix for parallelization
  We have been parallelizing Filter and Project nodes, but this is a problem for such nodes that contain a Subquery expression, since the query plan for that subquery may not tolerate parallelization. This fixes that bug and adds tests.
• 320: Added proper PROCEDURE nested SELECT behavior + tests
• 319: sql/analyzer: Avoid pruning columns of nodes under an IndexedJoin, since the IndexedJoin expression itself is inscrutable.
• 318: sql/analyzer/pushdown.go: Push down appropriate filter predicates to sit right on top of a SubqueryAlias when appropriate.
• 317: Max/check constraint
  check constraints in memory engine
  depends on dolthub/vitess#53
  TODOs maybe:
  • in mysql, an ALTER TABLE ADD CHECK fails if any of the existing rows fail the check. This is what the current implementation does, I'm not sure if toggling that off should be supported.
  • when an INSERT row that fails a constraint, mysql errors and does not write any of the values. The current implementation defaults to INSERT IGNORE's behavior, where warnings are issues for failed checks but good rows are still included.
    TODOs in future:
  • DropColumn, RenameColumn, ModifyColumn should error if touching a constraint-protected columns
• 316: Vinai/load data - Part 1 very basic functionality review
  This PR introduces basic functionality for LOAD DATA.
• 315: Added SHOW & DROP PROCEDURE, along with basic IF/ELSE support
• 315: Added SHOW & DROP PROCEDURE, along with basic IF/ELSE support
• 314: sql/analyzer/prune_columns.go: Turn off pruneColumns when a Subquery expression exists.
• 312: Added INOUT/OUT params to procedures & nested procedures
  Stored procedures now work with INOUT/OUT directions for their parameters. Not only is this critical for nested CALL statements to work properly, but additional changes were made to handle nested CALLs. Stored procedures are now loaded before analyzing the query body.
• 311: sql/analyzer/resolve_subqueries.go: Make subqueries which include subqueryaliases cacheable.
• 310: sql/plan/subquery.go: Make certain that analyzed nodes below a subquery that includes a subquery alias node see the row results they are expecting.
• 309: Max/float53
  Follow specific rules for mysql float/double/error depending on the float ops.
• 308: Window functions
  This is a full implementation of window functions, with only the ROW_NUMBER() function supported. Partitions and order-by work for the OVER() clause, but other constructs (PRECEDING, FOLLOWING, etc.) are not supported.
  Other limitations:
  • Can't use window functions with GROUP BY
  • Can use non-window aggregates with window functions, but only if they are declared with an empty OVER clause
• 307: Bump vitess
• 306: Roughest implementation of stored procedures
  No need to go into detail regarding what doesn't work (a lot), but it's groundwork to iterate on.
• 305: sql/analyzer: resolve_subqueries.go: Add a missing stripQueryProcess in resolveSubqueries.
• 304: pass the context into Close calls
• 303: sql/analyzer: Make join_search faster
  This helps the analyzer actually complete analyzing some of the example queries we're working on that have 9+ table joins. The basic idea is to recursively build every binary tree that has the in-order traversal we want and then attempt to apply join conditions to those trees.
  Performance here can be further improved by coupling visitJoinSearchNodes and assignConditions so that we can prune large portions of the subspace as being impossible from a join conditions standpoint. I may work on that next depending upon how further benchmarking goes. Currently a previous query which failed to analyze before take 30s.
• 301: Naive/hacky show grants
  hard-coded SHOW GRANTS handling:

  > dolt sql -q "show grants for current_user"
  +-------------------------------------------------------------+
  | Grants for root@%                                            |
  +-------------------------------------------------------------+
  | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
  +-------------------------------------------------------------+
  

• 300: Added Stored Procedure interface for integrators
• 299: sql/{plan,analyzer}: Add IndexedInSubqueryFilter for selecting the subquery results first and then making indexed lookups into the child.
• 298: Vinai/create database
  Add functionality for CREATE DATABASE
• 297: Max/sqlerror codes
  Append error codes/sql state to error messages for downstream clients.
• 296: Fixed BEGIN/END blocks with embedded SET statements
  BEGIN/END blocks did not return their results, and also did not build off of previous statements within the block. In addition, they were panicking by carrying a nil c...

Q1 2021 Release

This release includes many performance improvements and features, notably including indexed joins for 3 or more tables.

Merged PRs

• 278: remove sync.Once from autoincrement expression
• 275: add StatisticsTable interface
• 274: Fixed various bugs in subquery execution, added table ordering optimization
  Fixes many correctness issues in subquery execution:
  • Incorrect field indexes for subquery aliases in some cases
  • Incorrect field indexes for subquery expressions in the case of column pruning in other parts of the query
  • Queries selecting the same table more than once with aliases getting incorrect indexes applied
    Also introduces performance enhancements:
  • Table ordering optimizations based on rows counts of tables in a join
  • Better use of index pushdown for some join queries
• 273: Added the VALUES() function
  Requested in: dolthub/dolt#1225
  VALUES() is deprecated in the latest versions of MySQL (as of version 8.0.20, released April 2020), but it is recent enough that I feel its inclusion in the engine is justified.
• 271: add ability to remove rules
• 270: Fixed bug in creating self-referential foreign key constraint
• 269: Fix: not check tcp6 socket state while ipv6 is disabled
  If ipv6 is disabled on the system, tcp6 will not exist in the /proc/net dir.
  So open /proc/net/tcp6 will produce the error open /proc/net/tcp6: No such file or directory, we should not always check tcp6 socket state(unless it is opened).
• 268: Bug fix for dolthub/dolt#1219
• 267: sql/plan/exchange.go: Increase exchange node row chan buffer to 16 * parallelism.
• 265: Fixed bug in inserting literal NULL values as part of a SELECT statement
  Signed-off-by: Zach Musgrave [email protected]
• 264: Fix bugs in union distinct semantics, allow them in inserts
• 263: Export the struct param for NNary
• 262: sql/plan: exchange.go: Recover from panics in goroutine spawned by iterPartition.
• 258: sql/session.go: NewSpanIter: Enable this when the tracer is not Noop.
• 257: disable projection on indexed join
• 256: added describe queries for keyless tables
• 255: This function implement an Naryfunction type.
  Allows you to define sqle functions that have multiple children.
• 254: Fixed UNHEX/HEX roundtrip
  Simple fix but I ended up completely reevaluating our binary type implementation. Fixed a bug found in the cast package we were using to convert strings, and also changed UNHEX to return the proper SQL type.
• 252: Added hash functions
• 249: Alias bug fixes
  Fixes a number of buggy behaviors involving column indexes and table name resolution.
• 248: additional tests
  add a table with multiple keys
  an index that has a subset of those keys in a different order
  a couple queries
• 246: Error changes for INSERT ON DUPLICATE KEY UPDATE
• 245: Fixed tuple comparisons
• 242: Added TRUNCATE table and a DELETE fast path
• 240: Enginetests for Keyless tables
• 239: Zachmu/naked functions
  Fix for naked CURRENT_USER function call was in vitess, this just adds tests.
• 238: Zachmu/funcs
  Got rid of all embedded function fields in function types, since they make it impossible for analyzer to finish (function fields are not comparable with reflect.DeepEquals, which the analyzer uses to decide if the query plan has settled).
• 237: Added IS NULL and IS NOT NULL to indexing
• 236: Zachmu/indexed joins
  This needs more unit tests for the new join planner and more extensive engine tests for various kinds of N-table joins, but otherwise this is complete and I'm confident it works (for some values of works)
• 235: pickup vitess
• 234: Added no-op BEGIN support
  Signed-off-by: Zach Musgrave [email protected]
• 233: Reorder Master
• 232: Indexes search for exact match rather than submatches
• 231: added 'auto_increment' to EXTRA field for DESCRIBE table;
• 229: Add support for prepared statements.
• 227: added alter table auto_increment support
  This PR changes the semantics of AutoIncrementTable.GetAutoIncrementValue() to return the current value of the sequence, ie the next AUTO_INCREMENT id.
  Previously the semantics were to return the last used id. The return value of the function should now match what would be seen in MySQL's information_schema.table table
• 226: Zachmu/logictest
  Harness and runner for sqllogictest suite against the in-memory database implementation.
• 225: Andy/mysql auto increment
• 224: Zachmu/xx
  Use xxhash everywhere, and standardize the construction of hash keys.
• 223: Zachmu/in subquery
  Implemented hashed lookups for IN (SELECT ... ) expressions. This is about 5x faster than using indexed lookups into the subquery table in tests.
  In a followup I'm going to replace the existing CRC64 hashing with xxhash everywhere it's used, so we're back to a single hash function.
• 221: Fixed bug in delete and update caused by indexes being pushed down to…
  … tables
• 220: Support for COM_LIST_FIELDS, fixed SHOW INDEXES
• 219: Zachmu/turbine perf
  1. Do pushdown analysis within subqueries
  2. Push index lookups down to tables in subqueries
• 43: Zachmu/current user
• 42: error on COM_STMT_FETCH
• 41: go/mysql/conn.go: Ensure we write error packets for malformed queries in prepare statements.
• 40: added parser support for ALTER TABLE t AUTO_INCREMENT = int
• 39: support for COM_FIELD_LIST

Closed Issues

• 279: err:go get github.com/dolthub/go-mysql-server
• 261: Preserve NULL values on insert into tables using sub select queries
• 260: Support for UNION in INSERT statements
• 259: Support for UNION DISTINCT
• 253: Better support for parallel tests
• 241: expression.Tuple is uncomparable
• 230: DB Startup Config File - values not utilized
• 228: Create Table Statement to NewTable format?

Q4 2020 release

This release includes many new query features and bug fixes, most notably trigger execution, and includes the new github org name.

Merged PRs

• 218: Fix unit tests to run with -race.
• 217: validate auto_increment on in-line and out-of-line PR defs
• 216: Updated sql.MergeableIndexLookup interface
• 215: memory: *_index.go: Construct sql equality evaluations with accurate types in the literals.
• 214: Andy/auto increment
• 213: Zachmu/triggers bugfix
  Fixed bug in insert triggers, which couldn't handle out-of-order column insertions.
  Fixes dolthub/dolt#950
• 212: sql/analyzer: pushdown.go: Allow pushdown on Update, RowUpdateAccumulator and DeleteFrom plan nodes.
• 211: Zachmu/join bugs
• 210: sql/plan: {update,insert,update,process}.go: Fix some potential issues with context lifecycle and reuse.
  • insert, update, delete: Only call underlying table editors with our captured
    context once when we are Close(). Return a nil error after that.
  • process: Change to only call onDone when the rowTrackingIter is Closed.
  • process: Change to call childIter.Close() before onDone is called. Child
    iterators have a right to Close() before the context in which they are
    running is canceled.
• 208: Create UNIQUE index if present in column definition
• 207: Pushdown and plan decoration
  Two major changes:
  1. Changes to pushdown operation, to push table predicates below join nodes and to fix many bugs and deficiencies. Also large refactoring.
  2. Added DecoratedNodes to query plans to illustrate when indexes are being used to access tables outside the context of a join
• 206: Zachmu/inserts
  More trigger tests, and support for more kinds of SELECT statements as part of an INSERT
• 205: Implemented drop trigger
  As discussed, we disallow dropping any triggers that are referenced in other triggers.
• 204: Added trigger statements
• 203: Zachmu/triggers 6
• 202: Zachmu/triggers 5
  Added additional validation for trigger creation and execution:
  • Use of NEW / OLD row references
  • Circular trigger chains
• 200: Zachmu/triggers 4
  Support for DELETE and UPDATE triggers
• 199: Reference new org name
• 198: Added proper support for SET NAMES, and also turned off strict checki…
  …ng for setting unknown system variables.
  Signed-off-by: Zach Musgrave [email protected]
• 197: Zachmu/user vars
  User vars now working. Can stomp on a system var of the same name, as before my last batch of changes.
• 196: Allow CREATE TABLE LIKE to reference different databases
• 195: Zachmu/triggers 3
  This gets SET new.x = foo expressions working for triggers. This required totally rewriting how we were handling setting system variable as well, since these two kinds of statements are equivalent in the parser.
  Also deletes the convert_dates analyzer rule, which impacts 0 engine tests.
• 194: No longer return span iters from most nodes by default.
  Signed-off-by: Zach Musgrave [email protected]
• 193: Implemented CREATE TABLE LIKE and updated information_schema
  Tests will come in a separate PR
• 192: Zachmu/set exprs
  Updated to latest vitess, fixed a couple bugs with new UPDATE implementation
• 190: Harrison pr
  https://github.com/liquidata-inc/go-mysql-server/pull/189/files and a couple fixes
• 188: Zachmu/triggers 2
  Insert triggers working for the following cases:
  • Insert some rows
  • Delete some rows
  • Update some rows
    Missing, needs to be added:
  • set new.x = blah as part of a BEFORE INSERT trigger. Need to rewrite the SET handling parser logic for that.
  • error testing for bad triggers (like inserting on the same table the trigger is defined on)
    As part of this, I rewrote the execution logic for Update, Delete, and Insert entirely.
• 187: Additions to utc_timestamp and timediff
• 186: Db/fix collations
• 185: Fixed bug with column renames breaking default values
• 184: /sql/expression/function/{date.go, date_test.go, registery.go}: Add utc_timestamp
• 183: Fixes for bugs found during integration of column defaults
• 182: Zachmu/trigger integration
  Simple integration point for trigger definitions
• 180: Zachmu/triggers
• 178: Column Defaults Implementation Part 2
  Here is a comprehensive set of tests for default logic. Practically everything that was added in #174 is covered here, including some edge cases. In addition, the memory table implementation was broken/insufficient in a few ways,
  so I patched those up.
  The biggest change besides the tests is the additional pass when projecting expressions. This is required in order for defaults that reference other columns (especially those that come after) to be able to pull the correct value. This was something I noticed only after I wrote a tes
  t that wasn't behaving as expected (compared to the MySQL client). In fact, all of the changes outside of enginetests were due to fixing bugs that were found from testing.
• 176: Add import statements to readme example
  The example in the readme has no import statements, so it's unclear to someone new to the project. So I added some import statements!
• 174: Column Defaults Implementation Part 1
  This is missing basically all of the new tests, which will come in a separate PR. Proper expressions -> string behavior will also come in a separate PR. Besides that, this is pretty much most of it barring additional bug fixes. All existing tests (some of which use defaults already)
  pass.
  For integrators, they'll make use of the new engine.ApplyDefaults method.
• 168: Zachmu/sql correctness
  Fixed two bugs: NULL semantics in IN expressions, and backward indexes for Ascend / Descend indexes on multi-column indexes.
• 166: Handle exchange nodes
• 165: Don't set default max connections to 1
  Signed-off-by: Zach Musgrave [email protected]
• 162: Changed the test data for one_pk, two_pk to be able to differentiate …
  …between the various columns. This gives us more confidence that we're choosing the right field index in subqueries.
  Signed-off-by: Zach Musgrave [email protected]
• 161: Zachmu/show tables as of
  Implemented SHOW TABLES AS OF support
• 36: Added trigger statements
• 35: Reference new org name
• 34: Zachmu/prune
  All the changes from dolthub/vitess#33, but then also removed all go code that wasn't needed by the parser or the mysql server (most of it). #33 has tests that don't compile that are removed in this PR.
• 33: Zachmu/set
  Killed off UpdateExpr in favor of SetExpr, and made SetExpr handle all the same objects that UpdateExpr can. This unification was necessary when adding support for statements like 'set new.x = 1' in trigger bodies -- generates a parser conflict otherwise.
• 32: Added better error message support for parse errors
  Signed-off-by: Zach Musgrave [email protected]
• 31: Zachmu/stored procedures
  CREATE TRIGGER and BEGIN .. END block support in the parser.
• 30: Added support for SHOW TABLES AS OF statements
  Signed-off-by: Zach Musgrave [email protected]

Closed Issues

• 181: undefined ViewSelectPositionStart & ViewSelectPositionEnd
  ...

Liquidata Q3 release

First liquidata release, with many new features to the grammar and engine.