Merged PRs

dolt

• 5038: Block merges that corrupt indexes
  Today, many merges in the new format can leave indexes in a corrupted state. This PR blocks those merges as a short-term fix. In the long term we will make these merges automatic.
• 5030: go/store/nbs: Update chunk journal to support GC
• 5025: Fix instructions for first connect
  Fixes #5024
• 5018: go/store/{blobstore,nbs}: Added conjoin for GCS remotes
• 5017: Sysbench summaries for testing
• 5012: Move TableFileStore from nbs to chunks
• 5006: Rewrite broken-merge.bats in enginetest package
  Rewrite of broken-merge.bats in engine test package. Adds additional tests.
• 5005: Perf improvements for remote read replicas
  This significantly speeds up the process of syncing a remote-based replica by 1) pulling chunks once, rather than once per branch in the case of replicas that want everything, and 2) by eliminating a bunch of duplicated and wasted work. Latency for up-to-date case is now <100 ms.
• 5004: Split dolt_queries into three files.
• 4997: go/store/prolly: Refactored prolly diff iter to accept zero-value maps
• 4994: Fix remotes-aws.bats for DOLT
• 4985: adding tests for prepared, execute, and deallocate
• 4976: Fixed DELIMITER
  Previously we required delimiters to have the previous delimiter at the end, but that requirement no longer exists, which matches MySQL's behavior.
• 4956: Batch replication branch pulling

go-mysql-server

• 1496: skip once after for stored procedures
• 1494: add 'privileges' column for information_schema.columns table
  Made information_schema database and its tables accessible to all users.
  Note:
  • column privileges is not supported yet.
  • view columns not supported yet.
• 1493: cte with same name in subquery
  fix for: https://github.com/dolthub/dolt/issues/4950
• 1491: Revert "resolve cte before resolving tables"
  Reverts dolthub/go-mysql-server#1485
• 1490: /sql/datetimetype: fix ranges to match mysql
• 1486: allow naked identifiers for as of statements
  added a special case for unresolved columns for statements using as of <identifer>
  fix for: https://github.com/dolthub/dolt/issues/3116
• 1485: resolve cte before resolving tables
  Changes:
  • resolve the CTE before validating procedures
  • handle SubqueryAlias
    Fixes: https://github.com/dolthub/dolt/issues/4480
• 1481: Truncate now checks system variable foreign_key_checks
  Truncate now checks system variable foreign_key_checks and does not throw if the variable is not 1.
  This fixes https://github.com/dolthub/dolt/issues/5007
• 1480: add some supported columns for STATISTICS and COLUMNS info schema tables
  • Added sub_part for STATISTICS table.
  • Fixed column_default, datetime_precision, extra and other columns for COLUMNS table.
  • Skipped cardinality column for STATISTICS table as it's not supported in Dolt yet.
  • Skipped privileges column for COLUMNS table for now. (in progress)
  • Modified all Dolt supported MySQL functions to use its FunctionName() method to create the string statement of the function itself. The FunctionName() method returns the name of the function in all lower cases. This makes it so that all Dolt supported MySQL functions are consistent to what MySQL returns for queries such as SHOW CREATE TABLE or the column_default of information_schema.COLUMNS table.
    Notes:
  • Dolt does not support Time length yet.
  • Dolt currently handles BOOL and TINYINT data types the same with INT8, but MySQL explicitly defines BOOL as tinyint(1), which is different from tinyint for TINYINT data types.
  • View columns are not available in COLUMNS table yet.
• 1479: check that all bindings are used
  Fix for: https://github.com/dolthub/dolt/issues/4481
• 1477: added collation support for STRCMP
  adding collation support for STRCMP
• 1476: group_by: collation-aware grouping
  Hi doltfriends! Here's another compatibility fix. This one I'm more confident about: GROUP BY is not collation aware when grouping!
  The included test reproduces the issue trivially, but for completeness: when using a GROUP BY statement that includes a textual column, the column's collation needs to be taken into account when creating the aggregation groups.
  E.g., for the following schema and query

  CREATE TABLE test (id bigint, title varchar(255) collate utf8mb4_0900_ai_ci, PRIMARY KEY(id));
  INSERT INTO `test` (title) VALUES ('val1'), ('VAL1'), ('val2'), ('VAL2')
  SELECT COUNT(*) FROM test GROUP BY title
  

  We should expect the result of the SELECT to contain two rows, as there are only two groups when grouping by title. Even though we've inserted 4 unique values, the utf8mb4_0900_ai_ci with which the column is collated is case insensitive, so in practice, 'val1' = 'VAL1' and 'val2' = 'VAL2' when grouping. This is not the current behavior.
  The fix is straightforward: use a weight string when hashing to generate the grouping key instead of using the expression's literal value. I did a small refactoring in collations.go so that we can write the weight string directly into the grouping key's hasher and continue using the same code for the existing HashUint API.
  cc @dbussink
• 1475: Fixed stored procedure panics
  This fixes two panics that were discovered for stored procedures. The first comes from this issue: https://github.com/dolthub/dolt/issues/4980. The second deals with an issue with external stored procedures, where the usage of an uninitialized user variable would throw a panic over the nil value.
• 1470: Subquery caching rules onceAfterAll
  We cannot cache subquery results for a scope until the child scope has been finalized. A child scope cannot be finalized until the parent scope is finalized. Caching subqueries has to happen after all scopes are finalized. Applying hash lookups in the old way is much more difficult with this setup. This also exposed some bugs related to cacheability checks and subquery/union finalization recursion. Rewrite rules that depend on subquery caching to be in order traversals in OnceAfterAll.
  The original bug sourced from a bad resolve in a query like this:

  select ab.* from ab join pq on a = p where b = (select y from xy where y in (select v from uv where v = b)) order by a;
  

  The join and two scopes appear necessary to trigger the original bug. The join's children will be unresolved when we index the inner scopes, whereas a single relation scope's child will be a resolved table. The two scopes provide a gap for determining cachability in the middle scope while the child scope still had a deferredColumn, which previously permitted caching.
  GMS bump: https://github.com/dolthub/dolt/pull/4987
• 1448: implementing PREPARE, EXECUTE, and DEALLOCATE
  fx for: https://github.com/dolthub/dolt/issues/4452

Closed Issues

• 5035: Inaccurate ranges for DATETIME and TIMESTAMP
• 4950: Multiple CTEs with the same name can cause incorrect results
• 4480: Analyzer bug when using CTEs in stored procedures
• 3116: Allow AS OF strings to be naked identifers
• 5024: README instruction does not work for connecting
• 4481: Unused query bindings should be an error
• 5007: TRUNCATE with foreign keys doesn't always work
• 5000: unexpected dolt sql console crash
• 4452: Support prepared statements SQL statements
• 4982: query could not complete when rows are done
• 3253: support executing delimiter statement without requiring query terminator
• 4975: Intermittently receive the message error reading from or writing to noms: new manifest created with non 0 lock when running dolt in docker.
• 4320: call dolt_backup() doesn't work if called on a sql-server started in a parent directory.
• 4977: Can't create database with navicat: Dolt does not accept single quotes on charset identifier

Latency

Merged PRs

dolt

• 4986: Fix 'new manifest created with non 0 lock' error
  Fixes #4975
  Fix dropping and creating the same database on dolt sql-server. We were caching doltdb's using a singleton and at the manifest cache level which lead to problems.
• 4974: Systab edits
• 4971: Reset source branch after checkout
  When checking out a branch, clear the source branch's working set only after the destination checkout succeeds without errors.

go-mysql-server

• 1475: Fixed stored procedure panics
  This fixes two panics that were discovered for stored procedures. The first comes from this issue: https://github.com/dolthub/dolt/issues/4980. The second deals with an issue with external stored procedures, where the usage of an uninitialized user variable would throw a panic over the nil value.
• 1474: fix hexadecimal literal value conversion
  MySQL docs:
  In numeric contexts, MySQL treats a hexadecimal literal like a BIGINT UNSIGNED (64-bit unsigned integer).
• 1469: bringing back assert prepared
  For some reason, I deleted AssertErrPrepared, which allowed many tests to incorrectly pass.
  This PR adds that back and fixes the failing tests.
  There were a couple validation rules that were never run, which seems wrong (validateUnionSchemasMatch is still never run for prepared queries).
  I also added a helper TestSingleScriptPrepared to more easily debug prepared script tests
  There are prepared tests in dolt that also fail because of these changes, so I also had to fix them
  Companion PR: https://github.com/dolthub/dolt/pull/4968
• 1468: Update README.md
  added team link and dolt docs link
• 1466: fixes bug where TIMEDIFF wasn't returning null when given null
  • added unit tests for null arguments for TIMEDIFF
  • added unit tests for null arguments for DATEDIFF
  • updated TIMESTAMPDIFF to throw an error when given a null unit
  • added unit tests for null arguments for TIMESTAMPDIFF
• 1461: Implemented LOOP, LEAVE, and ITERATE, partially implemented DECLARE ... HANDLER
  This needs many, many, many more tests. Also should rewrite how stored procedures are analyzed as well, but that's a larger project overall.
• 1460: add STRCMP functionality
  fixes: https://github.com/dolthub/dolt/issues/4478

vitess

• 208: Allow charsets/collations in single or double quotes for create database/alter database
  Fixes: https://github.com/dolthub/dolt/issues/4977
• 206: Update README.md
  added blurb about Dolt's use of vitess and links
• 205: Added LOOP, LEAVE, and ITERATE
  Finding a workaround to allow : to parse resulted in the changes you see in token.go. There may be a better way to go about it, but I don't want to spend more time on it since this appears to work.

Closed Issues

• 4977: Can't create database with navicat: Dolt does not accept single quotes on charset identifier
• 4980: Panic in stored procedure
• 4992: incorrect result from order by clause for case sensitive string values
• 4975: Intermittently receive the message error reading from or writing to noms: new manifest created with non 0 lock when running dolt in docker.
• 4981: insert on decimal type column fails with prepared stmt - decimal conversion issue

Latency

Merged PRs

dolt

• 4973: Fix working set not found error for dolt migrate
  In dolt versions <= 0.26.10, a working set was not created when a branch was created. In newer dolt versions it is created. Migrate expected a working set to exist for every branch ref.
  Now, if a working set is missing we pretend it exists.
• 4970: go/store/nbs: conjoiner.go: Fix bug with use of canceled context.
• 4967: go/commands: filter-branch --all now rewrites tags, added --branches flag
• 4942: new sysbench runner and CI for systab
  Harness and CI for testing system table performance. Scripts are codegen'd in https://github.com/dolthub/systab-sysbench-scripts. We compare native system tables to materialized versions with logically equivalent rows.

  +-------------------------------------+-----------+----------+-------+
  | name                                | mean_mult | med_mult | stdd  |
  +-------------------------------------+-----------+----------+-------+
  | dolt_commit_ancestors_commit_filter | 6.89      | 7.65     | 0.377 |
  | dolt_commits_commit_filter          | 6.08      | 6.74     | 0.311 |
  | dolt_diff_log_join_on_commit        | 1.39      | 1.36     | 5.834 |
  | dolt_diff_table_commit_filter       | 37.77     | 40.8     | 1.756 |
  | dolt_diffs_commit_filter            | 25.8      | 29.53    | 0.738 |
  | dolt_history_commit_filter          | 25.52     | 27.95    | 0.799 |
  | dolt_log_commit_filter              | 4.64      | 5.14     | 0.401 |
  +-------------------------------------+-----------+----------+-------+
  

• 4941: Block branch switches when both branches have working set changes
  Block branch switches when both current and target branches have working set changes.
  Allow this behavior to be skipped using the -f flag, in which case the two working sets are combined (as is the current behavior).
  This fixes https://github.com/dolthub/dolt/issues/4863

Closed Issues

• 4863: Inserted rows not persisting on switched branch
• 4962: Change (error?) in the handling of window functions between 0.50.14 and 0.50.15
• 4478: function/string: strcmp is missing
• 4792: Add the git config variable to always set the upstream on push

Latency

Merged PRs

dolt

• 4958: go/commands: added verbose mode to filter-branch

go-mysql-server

• 1464: clean up on current information schema
  Updated information_schema tables' schemas.
  Fixed some table values for the schema changes
• 1463: fix sq finalize ordering bug
  Transform table + filter into table lookup in a subquery expression should happen in finalizeSubquery, not in resolveSubquery. Otherwise we will push a filter with a GetField index that could be rearranged when the parent scope is finalized.
• 1462: sqa finalize self before child recursion bug
  We must finalize a subquery [/ expression]'s scope before finalizing any child subquery [/expression]. Otherwise, the child may reference invalid parent scope indexes that are not fixed until the parent is finalized.
• 1461: Implemented LOOP, LEAVE, and ITERATE, partially implemented DECLARE ... HANDLER
  This needs many, many, many more tests. Also should rewrite how stored procedures are analyzed as well, but that's a larger project overall.
• 1459: Bug fixes for CrossJoin scope visibility and disposing mem caches for IndexedInSubqueryFilter
crossJoinIter was the only join iterator that wasn't taking in a parentRow with outer scope values and passing that row prefix to its children. There's some low-hanging fruit to refactor the joinIters to reduce duplication, but it didn't seem high priority, so I stopped short of that. Fixes: https://github.com/dolthub/dolt/issues/4926
  While testing that, I also hit a bug with a mem cache not being released by IndexedInSubqueryFilter and included that fix here. There were also some dispose member and Dispose functions in the join iterators that I verified were not used anywhere, so I pulled those out to tidy up.
  Dolt CI Tests: https://github.com/dolthub/dolt/pull/4947
• 1458: Reorder joins before finalizing subqueries
  We cannot set field indexes for subquery expressions until the parent scope has been finalized.
  The query that surfaced this bug was a subquery expression with a dependency on a join child, whose order is rearranged after we finalize subquery. Ex: the query below swaps the schema from under the subquery expression; y and is_one should match, but we reorder the join for a lookup, changing the scope schema from [xy,uv] -> [uv,xy]. The subquery expression now selects the wrong column for comparison:

  > select y, (select 1 where y = 1) is_one
  from xy join uv on x = v
  order by y;
  +---+--------+
  | y | is_one |
  +---+--------+
  | 0 | 1      |
  | 0 | 1      |
  | 1 | NULL   |
  | 1 | NULL   |
  +---+--------+
  

  We select the correct field now when the subquery is finalized after the parent scope is fixed:

  > select y, (select 1 where y = 1) is_one
  from xy join uv on x = v
  order by y;
  +---+--------+
  | y | is_one |
  +---+--------+
  | 0 | NULL   |
  | 0 | NULL   |
  | 1 | 1      |
  | 1 | 1      |
  +---+--------+
  
  

vitess

• 205: Added LOOP, LEAVE, and ITERATE
  Finding a workaround to allow : to parse resulted in the changes you see in token.go. There may be a better way to go about it, but I don't want to spend more time on it since this appears to work.
• 204: adding parser support for show databases like and where
  partial fix for: https://github.com/dolthub/dolt/issues/4484

Closed Issues

• 4926: GetField error for query with recursive CTE

Latency

Merged PRs

dolt

• 4948: go/cmd/dolt: sqlserver: yaml_config.go: Make some structs public to better support programmatic config generation.
• 4940: go/cmd/commands: added --datasets flag to dolt branch to list all dat…
  …asets
• 4939: ambyii's escape commit tag
  re: https://github.com/dolthub/dolt/pull/4920
• 4937: support push.autoSetupRemote config variable
• 4929: Adds skipped bats for silent merge failures
  5 skipped bats showing how merge silently fails today.
  After this PR, next steps is to throw an error if we are three-way merging these kinds of schema changes. Still thinking about how to detect these issues and fail-fast.
• 4924: found another minor bug in results.go
  Found the same kind of bug as before this time in results.go. I changed the comparison to be correct as before.
  Question about line 36. We are requiring exact order and not just content, is that correct or should we sort the args before comparing them?
• 4923: found minor bug in validate.go
  In validate.go I found a small bug where there is a comparison on the same object (len(new) != len(new)). I assume this was meant to compare len(new) with len(old) so I fixed that here.
• 4913: Remove GetGCKeepers
• 4911: go/cmd/dolt/commands/sqlserver: Allow a server in a cluster to configure which address it listens on for replication traffic.
• 4907: Added bats tests for dolt_branch(-c...)

go-mysql-server

• 1458: Reorder joins before finalizing subqueries
  We cannot set field indexes for subquery expressions until the parent scope has been finalized.
  The query that surfaced this bug was a subquery expression with a dependency on a join child, whose order is rearranged after we finalize subquery. Ex: the query below swaps the schema from under the subquery expression; y and is_one should match, but we reorder the join for a lookup, changing the scope schema from [xy,uv] -> [uv,xy]. The subquery expression now selects the wrong column for comparison:

  > select y, (select 1 where y = 1) is_one
  from xy join uv on x = v
  order by y;
  +---+--------+
  | y | is_one |
  +---+--------+
  | 0 | 1      |
  | 0 | 1      |
  | 1 | NULL   |
  | 1 | NULL   |
  +---+--------+
  

  We select the correct field now when the subquery is finalized after the parent scope is fixed:

  > select y, (select 1 where y = 1) is_one
  from xy join uv on x = v
  order by y;
  +---+--------+
  | y | is_one |
  +---+--------+
  | 0 | NULL   |
  | 0 | NULL   |
  | 1 | 1      |
  | 1 | 1      |
  +---+--------+
  

• 1457: simplify LIKE expressions to take advantage of indexes
  I needed this for my blog :)
  It might give performance benefits 🤷
  Certain LIKE expressions over string columns should take advantage of prefix indexes.
  TODO: there are a few more optimization we could make, but they'd take a bit more effort (I'd also need to confirm that MySQL does them as well).
• 1456: writing tests with non-determinism
• 1455: fix incorrect result from arithmetic ops
  The result of the query select 2000.0 / 250000000.0 * (24.0 * 6.0 * 6.25 * 10.0); is fixed, but the decimal scale is incorrect (made issue for it)
• 1454: enforce test ordering
• 1452: Order the test results.
  This fixes the currently-failing dolt tests: https://github.com/dolthub/dolt/actions/runs/3622688428/jobs/6107705037
• 1451: fix visibility for on duplicate key update with cte
  fix for: https://github.com/dolthub/dolt/issues/4562
  adding a special case in analyzer when insert.Source is a *plan.With.
• 1450: If a window does not have "ORDER BY" specified, use a default frame.
  If a window does not have "ORDER BY" specified, use a default frame.
  This fixes https://github.com/dolthub/go-mysql-server/issues/1449
• 1447: Disambiguate aggregation expressions
  Disambiguate aggregation expressions so different columns with same sources don't end up clobbering each other.
  Fixes https://github.com/dolthub/dolt/issues/4140
• 1445: fix type handling for derived table columns
  derived table column is now created from the most permissive type and the values are casted into that type at Eval.
  have skipped Prepared test as sort field type does not match non-prepared execution.
• 1440: Implemented DECLARE support for variables and cursors
  This adds support for DECLARE variables and cursors. I implemented nesting support by giving all variables a procedure-unique ID, with the scope determining which ID is resolved. A flat slice of IDs are far simpler to track than a proper tree structure.
  I did skip one test for GMS, as a RowIter reads the current state of the table, rather than the state that the RowIter generated with.
• 1437: add any_value() and option to enforce strict group by
  Added support for another option in @@sql_mode called ONLY_FULL_GROUP_BY, which makes it so select statements over group by must contain columns that are part of the group by. Before, dolt was always strict about group by selects, now (to match MySQL) we are not strict by default.
  Additionally, there is now support for the "aggregate" function any_value() which bypasses the ONLY_FULL_GROUP_BY if it is set.
  Also, changes GetGlobalVariable and GetSessionVariable to convert sql.SetTypes to strings.
  MySQL docs for reference:
  • any_value(): https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html
  • ONLY_FULL_GROUP_BY: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
    fix for: https://github.com/dolthub/dolt/issues/1448
    and: https://github.com/dolthub/dolt/issues/4604

Closed Issues

• 4919: Percent symbol (%) in commit strings is interpreted a format placeholder
• 3631: Command Line working sets become a working set for every branch after starting a server
• 4792: Add the git config variable to always set the upstream on push
• 1448: Support for selecting non-aggregate expressions that don't appear in GROUP BY clause
• 4604: Support for any_value
• 4562: on duplicate key clause missing visibility into tables in subqueries and ctes
• 4298: DISTINCT returns incorrect result for decimal types
• 4140: Result from first window function reused by other window functions
• 1449: Windows without ORDER BY return wrong results

Latency

Merged PRs

dolt

• 4912: Starter for adding system table benchmark to CI
• 4909: go/store/nbs: cleanup NBS interfaces
  • refactored conjoiner to conjoinStrategy which is now only responsible for deciding when to conjoin and which tableSpecs to conjoin. A common implementation of conjoin() is used in all cases
  • added getRecordRanges(requests []getRecord) to the chunkSource interface, rather than asserting past the interface in NomsBlockStore.GetChunkLocations
• 4906: Add dolt_conflict_id to dolt_conflicts_$table_name
  This PR makes manipulating the dolt_conflicts_$table_name tables easier. It adds a new dolt_conflict_id column. The column is a hash of the primary keys for a keyed table (or the value hash of the row for a keyless table) and the from_root_ish column. The dolt_conflict_id column uniquely identifies a conflict.
  A sample:

  dolt sql -q "select * from dolt_conflicts_t;"
  +----------------------------------+---------+-----------+--------+----------+---------------+----------+------------+-----------------+------------------------+
  | from_root_ish                    | base_pk | base_col1 | our_pk | our_col1 | our_diff_type | their_pk | their_col1 | their_diff_type | dolt_conflict_id       |
  +----------------------------------+---------+-----------+--------+----------+---------------+----------+------------+-----------------+------------------------+
  | b6qeivhltgrm2la9057uis84eav5n2rl | 1       | NULL      | 1      | -100     | modified      | 1        | 100        | modified        | +Z9y2YEvo0d1ZupbzGiyrQ |
  +----------------------------------+---------+-----------+--------+----------+---------------+----------+------------+-----------------+------------------------+
  

• 4901: Allow rows to be inserted by updating dolt_conflicts_$table_name
• 4900: fixes dolt table export appending success message to csv when using redirect syntax
  dolt table export was previously appending "Successfully exported data." to the end of the csv file when using the redirect syntax (e.g. dolt table export hospitals > hospitals.csv). The success message didn't get appended when redirect syntax wasn't used (e.g. dolt table export hospitals hospitals.csv). This fix makes it so that both formats work as expected.
  issue: https://github.com/dolthub/dolt/issues/4889
• 4899: fixing time tests
• 4898: Adding TestJoinQueriesPrepared in Dolt
  Adding a new TestJoinQueriesPrepared enginetest function.
  GMS update: https://github.com/dolthub/go-mysql-server/pull/1436
• 4894: Truncate table should not drop conflicts or CVs
• 4879: Fix value being read in param suffix
  This commit will:
  • Fix an issue with the arg parser, where params suffixes were being recognized as param values (eg. --messageTest where Test is taken as the value for the --message param).
    Note that with this new change, values being passed as suffixes to params is only allowed for long for params (ie -- flags).
    Fixes #4616

go-mysql-server

• 1443: Clean up CachedResult Dispose lifecycle
  Dispose should only be called on iterators whose resources are transient. Resources that outlive a single iterator should be Disposed at the end of the query through the Node (not iter). A CachedResult can be safely Disposed after the reseult is transferred to the parent HashLookup. A HashJoin should not be disposed when its parent is an iterative node that can call RowIter again. But if that is mistaken for some yet unforseen edge case, disposing the CachedResult returns it to a state where it will correctly recompute its contents rather than cycling on a nil'd cache where |cacheDone| is left as true.
• 1442: gms support for filters on SHOW DATABASES
  fix for: https://github.com/dolthub/dolt/issues/4484
  companion pr: https://github.com/dolthub/vitess/pull/204
• 1441: Fix unresolved insert source panic
  PR overlays two commits:
  1. revert faulty PR https://github.com/dolthub/go-mysql-server/pull/1434
  2. fix panic in a different way
• 1436: Adding TestJoinQueriesPrepared in GMS
  Adding a new TestJoinQueriesPrepared enginetest function and unskipping a few join tests that are working now.
  Dolt update: https://github.com/dolthub/dolt/pull/4898
• 1435: trim collation for information_schema.columns.dataType and information_schema.columns.colType
  Our dataType column in the information_schema.columns table didn't match MySQL when there were columns that had collations. dBeaver reads in metadata from information_schema.columns, and when there was an error, it threw an error saying that the table was read-only.
  fix for: https://github.com/dolthub/dolt/issues/4891
• 1434: Fixes issue 4857: insert qualify CTE source bug
  Edge case where we failed to resolve columns in the insert body if the source had a certain nested CTE/table alias/column alias structure.
  Something about how CTEs get parsed with TableAlias (while a regular subquery does not) triggers a path where we attempt to index an unresolved Subquery alias. This PR puts the resolveInsert rule before resolveColumns, and adds fixes for a richer post-analyze insert Source while resolving the root insert scope.
  Re: https://github.com/dolthub/dolt/issues/4857
• 1433: Fix nested subquery join index in subquery expression w/ outer scope visibility
  The plan representation and exec scope passing diverged for subqueries. We were indexing assuming no scope passing, but at runtime still prepending scope rows, which indirectly snuck around not explicitly passing scope through the subquery alias.
• 1432: get better convertToType for mergeUnionSchema rule
  Instead of converting every values to string, the type can be determined as appropriate as possible to avoid precision loss in number types.
• 1431: Changing handling of type conversion to better match MySQL's behavior
  Fixes: https://github.com/dolthub/dolt/issues/4874
  Dolt CI Test Run: https://github.com/dolthub/dolt/pull/4887
• 1429: implementing time
  fix for: https://github.com/dolthub/dolt/issues/4643
  changes:
  • allow conversion between sql.Time and sql.Datetime
  • return NULL instead of errors, and throw warnings
  • added time()

vitess

• 204: adding parser support for show databases like and where
  partial fix for: https://github.com/dolthub/dolt/issues/4484
• 203: Added OPEN, CLOSE, FETCH
  These are needed for CURSOR support.

Closed Issues

• 4565: Cluster replication does not support authentication or authorization on the replication endpoint.
• 4854: Conflicts should be easier to resolve on the SQL path
• 4867: Client Dockerfile—does not know new WORKDIR pattern
• 4857: qualify nested CTE insert source bug
• 4484: Filter for show databases
• 4891: DBeaver cell editing relies on information_schema.columns.dataType which Dolt sets incorrectly when there are collations
• 4889: dolt table export prints Successfully exported data. to STDOUT, should be STDERR
• 4502: index ordering/index selection regression
• 4496: sort node inappropriately pushed below group by
• 4490: Sort nodes above Window/Group By Aggregations
• 4874: Information_Schema.Columns => COLUMN_KEY in ('PRI','pri') causes matched behavior to MYSQL
• 3974: Support indexes on TEXT and BLOB column types
• 3713: Capability to lock a branch
• 4788: Feature Request: Version Notice
• 4486: json unquote and null values
• 751: table rename causes an error with dolt reset --hard
• 656: In a multidb setup (server or shell) indexes are not used when tables are not in the current db
• 4491: Import resource utilization
• 2091: sql-server INSERT performance is unacceptable for reasonably sized data imports.
• 4643: DATE_ADD does not support TIME input
• 4616: Dolt argument parser gets confused by suffixes
• 4892: dolt should return the same errors as mysql for any transaction serialization issues
• 4331: DECIMAL value in union query loses precision and scale information
• 4865: Error while restoring mysql dump into dolt

Latency

Merged PRs

dolt

• 4886: dolt_conflicts_$tbl_name: updates to the our version persist
  Any columns prefixed with our_ can now be updated on the dolt_conflicts_$tbl_name tables. Updates on the our columns get translated to updates on the conflict source table.
  First pass on implementing the suggestion in #4854. Handles keyless and keyed tables. Does not add the auto-incrementing id to the conflict table. Will get this out to the customer first and then come back.
• 4882: add send email action to failed ORM integration tests
  When nightly ORM integration test fails, it sends email to me and Tim.
• 4878: go/store/nbs: chunkJournal, journalWriter, and journalChunkSource
  Adds chunkJournal, a log-based ChunkStore that integrates into current NomsBlockStore machinery. chunkJournal implements tablePersister and manifest. When memtables are passed to Persist(), their contents appended to the journal file at current offset and a journalChunkSource (implementing chunkSource) is returned. journalChunkSource is an in-memory object containing offset metadata for reading back chunks from the journal file. The chunkJournal also persists root hash updates to the log via the manifest interface. This PR is adapted from #4803, instead of returning a unique journalChunkSource on each Persist() call, a single chunk source is returned repeatedly. The tableSet naturally deduplicates these chunkSource references by storing them in a chunkSourceSet (#4860).
  This implementation is currently behind a feature flag. Lots of work remains to fully support current NBS functionality.
  • Push/Pull/Clone paths are largely unsupported.
  • chunkJournal is limited to a fixed size, doesn't have support for finalizing the current journal as a table file
  • Current chunk record format is temporary and needs support for format extension

go-mysql-server

• 1433: Fix nested subquery join index in subquery expression w/ outer scope visibility
  The plan representation and exec scope passing diverged for subqueries. We were indexing assuming no scope passing, but at runtime still prepending scope rows, which indirectly snuck around not explicitly passing scope through the subquery alias.
• 1430: fix declaration so the compiler checks it properly
• 1425: tests for non-unique indexes over keyless tables
  Non-unique indexes work as expected for in-memory tables, but there were no tests.
  companion pr: https://github.com/dolthub/dolt/pull/4873
  tests for: https://github.com/dolthub/dolt/issues/4861

vitess

• 203: Added OPEN, CLOSE, FETCH
  These are needed for CURSOR support.
• 202: support explaining tables, and adding extended keyword
  Even though it is deprecated, we can support EXPLAIN EXTENDED, since it does the same thing as normal EXPLAIN
  https://dev.mysql.com/doc/refman/5.7/en/explain.html#:~:text=In%20older%20MySQL%20releases%2C%20extended%20information%20was%20produced%20using%20EXPLAIN%20EXTENDED.
  Additionally, EXPLAIN, DESCRIBE, and DESC are all interchangable, so we should be able to EXPLAIN tbl
  Fix for: https://github.com/dolthub/dolt/issues/4185

Closed Issues

• 4864: Incorrect capitalization of sql-server param in docs
• 4865: Error while restoring mysql dump into dolt
• 4862: dolt migrate drops secondary indexes, checks, and collations when there's a string column

Latency

Merged PRs

dolt

• 4875: Changes to schema instantiation
  Fix for #4862
  Changes the way we instantiate schema.Schema in Dolt. In the past, we've had a couple bugs that have been caused by codepaths that build new schema objects. We've accidentally dropped primary key ordinals twice before and in #4862 we dropped indexes, checks, and collations.
• 4873: use range instead of point lookups for non-unique indexes
  fix for: https://github.com/dolthub/dolt/issues/4861
  tests here: https://github.com/dolthub/go-mysql-server/pull/1425

go-mysql-server

• 1426: Have JSONDocument implement driver.Valuer
• 1425: tests for non-unique indexes over keyless tables
  Non-unique indexes work as expected for in-memory tables, but there were no tests.
  companion pr: https://github.com/dolthub/dolt/pull/4873
  tests for: https://github.com/dolthub/dolt/issues/4861

Closed Issues

• 4473: Support uptime global variable.
• 4861: Keyless tables missing non-UNIQUE index support

Latency

Merged PRs

dolt

• 4872: Changing loglevel param to be case insensitive.
  This allows the loglevel CLI param and log_level YAML config key to be case insensitive.
  Fixes: https://github.com/dolthub/dolt/issues/4864
• 4871: fixes for dolt Docker images and ORM integration tests
  • Added WORKDIR for client Dockerfile
  • Fix path issue in dolt server docker entrypoint
  • Fix ORM integration tests
• 4858: add ORM integration tests job to github nightly jobs
  If the ORM tests fail, the github email notification will likely to go to the github actor of the scheduled job.
  It can be set to fixed emails through dawidd6/action-send-mail@v2 action
• 4851: use tini init system in docker container
  It's helpful for avoiding zombie processes and provides signal handling such as SIGTERM from docker stop
• 4849: Get the version v1.1.2 of github.com/HdrHistogram/hdrhistogram-go
  Updating to the latest version of github.com/HdrHistogram/hdrhistogram-go removes a lot of extraneous packages that were pulled in with v1.0.0, such as the linter github.com/golangci/golangci-lint
  This resolves https://github.com/dolthub/dolt/issues/4836
• 4848: go/doltcore/dbfactory: enforce singleton creation of datas.Database
• 4847: pick new viable underlying index for foreign keys when current one is dropped
  When dropping underlying secondary indexes used by foreign key, another existing viable one should be used in its place, unless there isn't one.
  TODO:
  • we should show duplicate indexes to primary key because MySQL does
• 4845: history system table missing projections
  history table was returning size-0 rows to the filter in the test case; the filter was erroring for the out of bounds index
• 4832: Online shallow gc
  Adds a dolt_gc procedure that runs shallow online garbage collection (prunes unused table files). A more complete online garbage collection is coming soon

go-mysql-server

• 1426: Have JSONDocument implement driver.Valuer
• 1424: Refactor check for canonical join
• 1422: add uptime
  not quite sure how to test or if it's the best way to have system variables that dynamically update...
  fix for: https://github.com/dolthub/dolt/issues/4473
• 1420: Fix issue where case expression is evaluated too early
  Fix issue where case expression is evaluated and the result is being treated as a literal.
  Fixes https://github.com/dolthub/dolt/issues/4716
• 1417: Fixed integer handling when given a floating number
  Integers are supposed to round floating numbers, but we just did a simple conversion so we were truncating the decimal portion.
• 1412: prevent modifying columns to blob/text without prefix length in index
  There's an edge case in where there's an index defined over a column without a prefix length, and if you modify that colunm to a text/blob it should throw an error. Additionally, it should preserve prefix lengths when it's still a string type. If the new type is too short, the prefix length should be dropped.
• 1411: Fix for commutative join types getting stuck in analysis loop
  This change fixes an issue where the analyzer can get stuck in a loop bouncing back and forth between the original and commuted plan and being unable to stabilize the plan.
  As part of that fix... it also deduplicates logically identical relExprs in exprGroups and changes the finalizeSubqueries analyzer rule so that it only runs once on each subquery, from the bottom of the plan tree, up.
  Fixes: https://github.com/dolthub/dolt/issues/4816
  Dolt Tests: https://github.com/dolthub/dolt/pull/4859

vitess

• 202: support explaining tables, and adding extended keyword
  Even though it is deprecated, we can support EXPLAIN EXTENDED, since it does the same thing as normal EXPLAIN
  https://dev.mysql.com/doc/refman/5.7/en/explain.html#:~:text=In%20older%20MySQL%20releases%2C%20extended%20information%20was%20produced%20using%20EXPLAIN%20EXTENDED.
  Additionally, EXPLAIN, DESCRIBE, and DESC are all interchangable, so we should be able to EXPLAIN tbl
  Fix for: https://github.com/dolthub/dolt/issues/4185
• 200: Support calling procedures from specified database.

  Description

  Support calling procedures from specified database, for example call mydb.test();

  Related Issue(s)

  This fixes https://github.com/dolthub/dolt/issues/4725

  Checklist

  • "Backport me!" label has been added if this change should be backported
  • Tests were added or are not required
  • Documentation was added or is not required

  Deployment Notes

  No deployment notes, this adds missing SQL support.

Closed Issues

• 4866: docker-entrypoint.sh—get_config_file_path_if_exists—.$CONFIG_DIR makes path relative
• 4867: Client Dockerfile—does not know new WORKDIR pattern
• 4864: Incorrect capitalization of sql-server param in docs
• 4816: Analyzer error: exceeded max analysis iterations (8)
• 4840: [Feature Request]: Support PostgreSQL
• 4500: Describe for default value expressions is formatted incorrectly
• 4185: Support EXPLAIN EXTENDED
• 4174: Recursive CTE fails when executed using JDBC driver
• 4716: Simple case statement doesn't work with enums
• 4843: When sql server is started in docker, any subsequent dolt cli call leave defunct processes
• 4836: Dolt CLI has an unnecessary dependency on golangci-lint

Merged PRs

dolt

• 4846: Schemas with different PK ordinals should not be equal.
  Fixes https://github.com/dolthub/dolt/issues/4844
• 4831: Fix issues with docker entrypoint
  1. Fix unability to process .sql in initdb
     If user has some .sql files in /docker-entrypoint-initdb.d they will have an error:

  2022-11-21 11:02:13+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
  /usr/local/bin/docker-entrypoint.sh: line 98: docker_process_sql: command not found
  

  This PR fixes it
• 4829: add Hibernate smoke test
• 4825: Blob chunker rewrite
  Rewrite blob chunking to use a a fixed amount of memory.
  
  (name) (iterations) (nanoseconds/iteration) (bytes/iteration) (allocations/iteration)

  -- after
  BenchmarkBlobBuilder/datasize:_3
  BenchmarkBlobBuilder/datasize:_3-12         	  234128	      5673 ns/op	    2608 B/op	       8 allocs/op
  BenchmarkBlobBuilder/datasize:_4
  BenchmarkBlobBuilder/datasize:_4-12         	   26074	     41468 ns/op	   17560 B/op	      24 allocs/op
  BenchmarkBlobBuilder/datasize:_5
  BenchmarkBlobBuilder/datasize:_5-12         	    2737	    378950 ns/op	  133004 B/op	     134 allocs/op
  BenchmarkBlobBuilder/datasize:_6
  BenchmarkBlobBuilder/datasize:_6-12         	     282	   3974650 ns/op	 1288281 B/op	    1269 allocs/op
  -- before
  BenchmarkBlobBuilder
  BenchmarkBlobBuilder/datasize:_3
  BenchmarkBlobBuilder/datasize:_3-12         	   15231	     75445 ns/op	  460464 B/op	      17 allocs/op
  BenchmarkBlobBuilder/datasize:_4
  BenchmarkBlobBuilder/datasize:_4-12         	    6844	    171266 ns/op	  923121 B/op	      50 allocs/op
  BenchmarkBlobBuilder/datasize:_5
  BenchmarkBlobBuilder/datasize:_5-12         	    1875	    549793 ns/op	 1040770 B/op	     270 allocs/op
  BenchmarkBlobBuilder/datasize:_6
  BenchmarkBlobBuilder/datasize:_6-12         	     273	   4389403 ns/op	 2668899 B/op	    2543 allocs/op
  

• 4812: use prefix index for referenced strings
  fixes error message for unique key violation to print prefix instead of string ref
  for indexes over referenced strings, we always just store the prefix of the string/byte inline
• 4768: change dolt docs doc_text column to longtext
  Fix for: https://github.com/dolthub/dolt/issues/4713

go-mysql-server

• 1419: errors to date_add() and date_sub() are warnings
  fixes part of: https://github.com/dolthub/dolt/issues/4643
  Will implement TIME() and sql.Time type in a separate PR.
• 1416: add histogram column to information_schema.column_statistics
  fix for: https://github.com/dolthub/dolt/issues/4412
• 1415: support show status
  Fix for: https://github.com/dolthub/dolt/issues/4473
  The way I get and display the variables in the right scope is a little inefficient, but not enough to really matter.
  TODO:
  • Need a good way to test these queries.
  • we are missing a ton of variables, worth adding?
• 1414: limit arithmetic literals are all ints but different types
  for query with ... limit 0, 200 in TopN plan, arithmetic was returning decimal type because 200 was handled asuint8 and 0 was int8 types, so if the both the values are any non-matching int type, the values are converted to int64 for + and - operations.
• 1412: prevent modifying columns to blob/text without prefix length in index
  There's an edge case in where there's an index defined over a column without a prefix length, and if you modify that colunm to a text/blob it should throw an error. Additionally, it should preserve prefix lengths when it's still a string type. If the new type is too short, the prefix length should be dropped.
• 1399: Add tests to verify that we call call qualified procs.
  Adding tests for https://github.com/dolthub/dolt/issues/4725
  go.mod is pointing to this PR's branch: https://github.com/dolthub/vitess/pull/200

vitess

• 201: adding parser support for prepared, execute, and deallocate
  supporting syntax for: https://github.com/dolthub/dolt/issues/4452
• 200: Support calling procedures from specified database.

  Description

  Support calling procedures from specified database, for example call mydb.test();

  Related Issue(s)

  This fixes https://github.com/dolthub/dolt/issues/4725

  Checklist

  • "Backport me!" label has been added if this change should be backported
  • Tests were added or are not required
  • Documentation was added or is not required

  Deployment Notes

  No deployment notes, this adds missing SQL support.

Closed Issues

• 4844: [dolt migrate] Fails validation when the primary keys are re-ordered
• 4238: Two dot dolt log
• 4511: Prisma tool compatibility
• 4737: migrate: byte slice is not of expected size
• 4840: [futuries requiest]: support PostgreSQL
• 4790: [dolt migrate]: migration failed with conflicts
• 4687: sql log took long time, how can i make it faster?
• 4707: as of queries fail in StarRocks
• 4713: Dolt docs doc_text column is varchar(16383), should be TEXT
• 4412: mysqldump of JSON type columns require a HISTOGRAM column in information_schema.COLUMN_STATISTICS which Dolt does not provide
• 4725: Calling a stored procedure with DB name in the query is not supported

Latency

Merged PRs

dolt

• 4820: go/doltcore/migrate: added --drop-conflicts flag to migration, check …
  …for migrated databases
  fix for https://github.com/dolthub/dolt/issues/4790
• 4815: dolt init fails with invalid arguments
• 4784: Fix a panic that occurs when grpc stream Recv() method returns an err…
  …or before stream is closed
  Stack trace from dolthubapi-asyncworker:

  ...
  2022-11-11T20:01:12.919Z	ERROR	process/process.go:273	finished unary call with code Unknown	{"grpc.start_time": "2022-11-11T20:01:12Z", "grpc.request.deadline": "2022-11-11T20:11:12Z", "system": "grpc", "span.kind": "server", "grpc.service": "ld.services.dolthubapi.internal.v1alpha1.BountyService", "grpc.method": "ProcessMergeCommit", "request_id": "b947de69-f3d8-4b05-8d09-38cb2c11c326", "invocation_id": "01d9a1a2-0cc3-4619-b919-42a666a257d0", "error": "pq: duplicate key value violates unique constraint \"scoreboard_build_shards_pkey\"", "grpc.code": "Unknown", "grpc.time_ms": 102.431}
  2022-11-11T20:01:12.920Z	INFO	request_logging/request_logging.go:21	beginning unary call	{"grpc.start_time": "2022-11-11T20:01:12Z", "grpc.request.deadline": "2022-11-11T20:01:13Z", "system": "grpc", "span.kind": "server", "grpc.service": "grpc.health.v1.Health", "grpc.method": "Check", "invocation_id": "a149d64f-e063-4f7b-ae40-a304bf86f50e"}
  2022-11-11T20:01:12.920Z	INFO	process/process.go:273	finished unary call with code OK	{"grpc.start_time": "2022-11-11T20:01:12Z", "grpc.request.deadline": "2022-11-11T20:01:13Z", "system": "grpc", "span.kind": "server", "grpc.service": "grpc.health.v1.Health", "grpc.method": "Check", "invocation_id": "a149d64f-e063-4f7b-ae40-a304bf86f50e", "grpc.code": "OK", "grpc.time_ms": 0.124}
  2022-11-11T20:01:12.922Z	INFO	zap/grpclogger.go:73	[transport]transport: loopyWriter.run returning. connection error: desc = "transport is closing"	{"system": "grpc", "grpc_log": true}
  panic: runtime error: index out of range [1] with length 1
  goroutine 2252120 [running]:
  github.com/dolthub/dolt/go/libraries/doltcore/remotestorage.(*DoltChunkStore).getDLLocs.func2.2.2()
  external/com_github_dolthub_dolt_go/libraries/doltcore/remotestorage/chunk_store.go:603 +0x269
  golang.org/x/sync/errgroup.(*Group).Go.func1()
  external/org_golang_x_sync/errgroup/errgroup.go:75 +0x64
  created by golang.org/x/sync/errgroup.(*Group).Go
  external/org_golang_x_sync/errgroup/errgroup.go:72 +0xa5
  

• 4382: Add dolt schema update-tag
  Adds dolt schema update-tag. In cases where merge is throwing a schema tag conflict such as in #3963, it may be useful to manually update the tags.

go-mysql-server

• 1414: limit arithmetic literals are all ints but different types
  for query with ... limit 0, 200 in TopN plan, arithmetic was returning decimal type because 200 was handled asuint8 and 0 was int8 types, so if the both the values are any non-matching int type, the values are converted to int64 for + and - operations.

vitess

• 201: adding parser support for prepared, execute, and deallocate
  supporting syntax for: https://github.com/dolthub/dolt/issues/4452
• 199: Added ALTER DATABASE parsing
  Adds support for parsing ALTER DATABASE queries.

Closed Issues

• 4476: Dolt produces diff when no cells have visibly changed
• 4827: Errors from sql-server
• 4682: HashLookup on byte slice causes panic
• 4557: Inserting bulk records is too slow
• 4771: dolt init doesn't fail when a non-option argument is provided

Latency

Merged PRs

dolt

• 4821: Change dolt_diff_summary table function to return empty diffs for tab…
  …les with pk set changes.
  • The 3 argument version returns an error if the table specified has a pk set change.
  • The 2 argument verison returns empty diffs and returns a warning.
    I considered approximating the diff for the pk set change, but it feels like an overreach for the customers need.
    Fixes: #4818
• 4769: Add bats for column truncation on import
  Associated GMS PR:
  https://github.com/dolthub/go-mysql-server/pull/1403

go-mysql-server

• 1408: hashlookups can hash byte slices
  fix for: https://github.com/dolthub/dolt/issues/4682
• 1407: adding tests for unique indexes
  verifies this was fixed: https://github.com/dolthub/go-mysql-server/issues/571
  pulls a duplicate key update test from dolt
• 1406: in memory implementation of prefix indexes

Closed Issues

• 4818: SELECT * from dolt_diff_summary('commitA', 'commitB') should not abort if a primary key change is detected.
• 4474: dolt migrate fails with "migration failed cause: Incorrect datetime value"
• 571: Unique indexes do not work in the in-memory implementation

Latency

Merged PRs

dolt

• 4804: A smoke test for MikroORM
• 4800: Skip empty diffs caused by non-canonicalized tuples.
  At one point, we started to trim null suffixes from tuples. In versions prior to this change, tuples could have a null field at the end of the tuple. Now we always truncate the tuple.
  If old versions insert rows with null values as the last column, it produces a tuple that is longer than the tuple that would be inserted by the latest version. This leads to empty diffs.
• 4799: Fix missing projection on indexed table
  This error only applies to DoltIndexedTable, which as far as I can tell is limited to the history tables.
• 4797: allow not and is null clauses with dolt diff table
• 4795: Table func join tests
  sibling PR here: https://github.com/dolthub/go-mysql-server/pull/1405
• 4785: use prefix indexes for inline string columns
  Trim the ranges for prolly index lookup to match prefix lengths.
  Mark prefix indexes as supported, except for text/blob prefix indexes.
  Also moves IndexPrefix tests to gms.
  companion pr: https://github.com/dolthub/go-mysql-server/pull/1406
• 4749: go/libraries/doltcore/sqle/cluster: Add JWT authentication for peer communication to cluster replicas.

go-mysql-server

• 1405: join table functions
  tests here: https://github.com/dolthub/dolt/pull/4795
  The main correctness risk here is if any integrator table functions do not commute/associate during join reordering. We already manually handle JSON_TABLE, but there could be others.
• 1404: revert parallel ita
  sister PR here: https://github.com/dolthub/dolt/pull/4776
• 1403: Add tests for column truncation of bad values with INSERT IGNORE
  Associated Dolt PR:
  https://github.com/dolthub/go-mysql-server/pull/1403

Closed Issues

• 4810: Feature Request for dolt diff
• 4791: Strange Behavior with Dolt History Table
• 4739: Segfault when querying inconsistent windows
• 4802: Default change: Reset --hard
• 4780: Strange behavior with dolt diff table
• 4793: SQL statements with INNER JOIN

Latency

Merged PRs

dolt

• 4794: /go/{cmd,libraries}: print failed to read cause
• 4786: Renamed --ignore-skipped-rows to --quiet
• 4781: fix merge --no-ff --no-commit
  If the editor process is killed or the editor was saved with empty message, it gives error.
  Fixes dolt merge --no-ff --no-commit, now it does not fast-forward and commit. It leaves the working set with changes.

Closed Issues

• 4762: dolt merge behavior should more closely match git merge

Merged PRs

dolt

• 4782: go/doltcore/migrate: fix migration datetime bug
  fix for #4474
• 4779: Fixed package docs for dolt command
• 4776: Revert parallel ita
  https://github.com/dolthub/go-mysql-server/pull/1404
• 4747: Replace dashes when using the default directory in sql-client
  Fixes https://github.com/dolthub/dolt/issues/4743
• 4745: Branch control bats tests
  Added bats tests for the new branch control feature
• 4732: Branch control permissions across databases
• 4728: Added validation test to check for test libraries in binary

go-mysql-server

• 1402: give HAVING visibility into outer scope
  fix for: https://github.com/dolthub/dolt/issues/4723
• 1400: Adding error code mapping and more tests for ErrNonAggregatedColumnWithoutGroupBy
  Adding a SQL error code mapping for ErrNonAggregatedColumnWithoutGroupBy and tests for validation with opaque nodes.
  From PR feedback in: https://github.com/dolthub/go-mysql-server/pull/1396

Closed Issues

• 4777: Branch Not Created
• 4723: resolveHaving analyzer rule doesn't account for outer scope visibility
• 4743: dolt sql-client does not substitute dashes for underscores when looking for the default database to use

Merged PRs

dolt

• 4773: /go/libraries/doltcore/{env,sqle}: print cause with error
• 4767: fix DOLT_ROOT_PATH bug
  Currently there is an issue where if you run something like:
  DOLT_ROOT_PATH=. dolt backup restore <remote> subfolder/reponame
  This fixes that by returning the absolute path.

go-mysql-server

• 1400: Adding error code mapping and more tests for ErrNonAggregatedColumnWithoutGroupBy
  Adding a SQL error code mapping for ErrNonAggregatedColumnWithoutGroupBy and tests for validation with opaque nodes.
  From PR feedback in: https://github.com/dolthub/go-mysql-server/pull/1396
• 1398: prevent foreign keys from referencing indexes with prefix lengths
  MySQL does not support foreign keys that reference indexes with prefixes, but we could?
  https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#:~:text=Index%20prefixes%20on%20foreign%20key%20columns%20are%20not%20supported

Closed Issues

Merged PRs

dolt

• 4760: wrap db read error
• 4759: integration-tests/go-sql-server-driver: Use 385{1,2} instead of 5005{1,2} for cluster remotesapi ports.
• 4757: delete old import perf files
• 4753: go/store/val: allow year encoding to store value 0
  Previously, the year encoding did not support a value of 0. It now uses uint8 255 to represent 0 since that value is free in the encoding space.
  Companion GMS PR:
  https://github.com/dolthub/go-mysql-server/pull/1395
• 4744: Added basic smoke test for TypeORM
• 4740: Add dolt-specific tests for a GMS change
  Adding BATS tests that will exercise the changes introduced by https://github.com/dolthub/go-mysql-server/pull/1389
  Tests will fail until PR 1389 is merged and deployed.
• 4722: implementing prefix indexes for string types
  This PR implements prefix indexes for inline string data type (CHAR, VARCHAR, BINARY, and VARBINARY) columns.
  The changes seem to work for BLOB and TEXT though.
  Fix for: https://github.com/dolthub/dolt/issues/3974
  Changes:
  1. doltIndex now has a prefixLengths field which is a []uint16
  • it contains the prefixes for each of the columns in the index definition
  • it is in order of the prefix definition
  • a nil or empty list means that the columns in the index should not be trimmed
  • none of the columns are string type
  • the columns are inline string, but the entire string should be used for comparison
  2. The flatbuffer files, serialization, and deserialization code deal with the prefix index lengths
  3. Currently, dolt will indicate that it won't support this index, because...it can't; at least not completely right now (see analyzer changes at bottom)
     TODO: analyzer changes
  • primary key prefixes are futher away, so we still throw an unsupported error for those.
  • there needs to be an analyzer change to actually take advantage of this index.
    Example:

  > create table t (v varchar(2), index (v(1)));
  > insert into t values ("aa"), ("ba"), ("bb"), ("bc"), ...;
  > select v from t where v = "aa";
  

  The query here should be able to take advantage of the secondary index; it should do a range prefix lookup over the table for just the rows that start with "a", then apply an equality filter over the resulting table. Currently, it will just do a full table scan.
• 4689: Redo import perf
  New import tests. Refer to README for usage details.

go-mysql-server

• 1397: fix intDiv div operation and more edge cases with precision for all arithmetic operations
  IntDiv does operations in decimal type values and returns the int part of the decimal value result. If the value goes out of bound of int64, it errors.
  Fixed few more edge cases for all arithmetic operations including:
  • any time type value gets parsed as int value with pattern of YYYYMMDDHHMMSS or YYYYMMDD depending on what's defined. This applies to all arithmetic operations.
  • invalid string tries to parse available numbers from the beginning of the string value and used in the arithmetic operation. 12.4ag3.2jgw499 is parsed as number 12.4, and a942.6ng488 is parsed as 0.
• 1396: Adding validation for illegal use of aggregation functions
  GMS currently panics if an aggregation function that relies on the implicit, all-rows grouping is mixed aggregation functions that rely on a window. This PR adds validation to detect these invalid queries and return the exact same error message as MySQL.
  Fixes: https://github.com/dolthub/dolt/issues/4739
• 1395: Add tests for year type behavior
  Adding tests for https://github.com/dolthub/dolt/issues/4742
• 1394: Fix test determinism
• 1393: handle float number for bit arithmetic ops
  MySQL rounds any float value to integer before any bit operation and returns non-negative result only. Int64 values are converted to Uint64 type values including negative numbers.
  Note:
  All results will be in uint64 type as _binary introducer conversion is not supported yet. E.g. these are not supported:

  _binary b'0001' | b'0010'
  _binary X'0008' << 8
  BINARY NULL & NULL
  BINARY NULL >> 4
  

  We don't support bit-wise compliment operation ~ and BIT_COUNT function yet.
• 1389: Provide better errors when attempting to use stored procedures without a DB
  Initial attempt at fixing https://github.com/dolthub/dolt/issues/3832 by throwing a better-worded error if no database is selected.
• 1387: extend Index interface to handle prefix lengths, and add in-memory implementation
  SHOW CREATE TABLE displays the prefix lengths specified for secondary indexes
• 1385: Moving qualifyColumns up higher in analyzer rules order
  This change moves the qualifyColumns analyzer rule up to the second position in the DefaultRules batch (resolveNaturalJoins still needs to run first to resolve duplicate column names.)
  The main issue I hit was that pushdownGroupByAliases was handling identifying aliases in GroupBy grouping expressions, and because GroupBy combines its projection expressions and grouping expressions in its Expressions() implementation, I had to special case GroupBy in qualifyColumns to handle identifying alias references in the grouping expressions that were defined in the projection expressions.
  The other interesting point is an open question around the right way to resolve ambiguity in the order by clause between an alias name and a column name. Documentation and testing shows that an alias should be preferred in that case, but MySQL behaves differently and prefers the column if the ambiguous reference is the argument to a function. For now, I changed the one test this affects to qualify the reference in the order by clause with a table name, and I opened an issue on MySQL's bug tracker to try and get confirmation if this is a bug or working as expected.
  Dolt CI tests: https://github.com/dolthub/dolt/pull/4721

Closed Issues

• 4742: year column type does not round-trip when column value is 0
• 3832: SHOW CREATE PROCEDURE dolt_branch fails when no DBs exist
• 4761: dolt merge --no-ff <branch> (no message) and dolt merge --no-ff --no-commit <branch> are still committing
• 4735: Resolve error for window expression in function
• 4715: LAST_VALUE window function seems not quite right

Merged PRs

dolt

• 4730: go/comands/docs: Update dolt docs commands with new names
• 4698: adding tests for index prefix errors
  Companion PR: https://github.com/dolthub/go-mysql-server/pull/1371
• 4697: warning instead of error for unix socket address already in use when starting sql server
  depends on https://github.com/dolthub/go-mysql-server/pull/1372
• 4691: Support three dot log
  CLI:
  dolt log main...branchA
dolt log main branchA --not $(dolt merge-base main branchA)
  SQL:
  dolt_log('main...branchA')
• 4686: Prisma compatibility smoke testing
  Prisma is a popular ORM library for TypeScript/JavaScript applications, with around 25k GitHub stars.
  This PR extends our nascent ORM testing image to run a simple smoke test for Prisma.
  Next, we need to get these tests running as part of CI and would be great to do deeper testing with Prisma and other ORM libraries, ideally running their test suites against a Dolt sql-server.
• 4683: fix decimal result print
  Some queries result in decimal type result, but sqlType gets numberType with int or float. Any decimal type result value is now printed as decimal value regardless of its sqlType returned from schema.
  Depends on https://github.com/dolthub/go-mysql-server/pull/1365
• 4681: Allow supplying multiple start/excluding refs to dolt log
  For three dot log we need to support supplying multiple starting points/excluding refs to the commit iterators, so that we can do something like dolt log A B --not $(dolt merge-base --all A B)
  This PR supports providing multiple arguments to dolt log:
  dolt log A B
dolt log A B ^C
dolt log A ^B ^C
dolt log A --not B C

go-mysql-server

• 1394: Fix test determinism
• 1392: Insert dependency passthrough cols inbetween aggregation and projection
  Re: https://github.com/dolthub/dolt/issues/4735
  Aggregation flattening dropped passthrough column dependencies, causing the parent Projection to throw GetField resolve errors.
  From the new docstring:

  // The aggregation node must provide input dependencies for parent projections.
  // Each parent expression can depend on zero or many aggregation expressions.
  // There are two basic kinds of aggregation expressions:
  // 1) Passthrough columns from scope input relation.
  // 2) Synthesized columns from in-scope aggregation relation.
  

  This is only relevant for expression that reference aggregations, which is somewhat curious. So for example, in select x/y, x/count(y) from x, the x/y expression is executed entirely in the child aggregation node. x/count(y) is separated into two steps, 1) evaluate count(y) and passthrough x, and then evaluate arithmetic x/count(y) in the parent projection. The error here is we were failing to expose the x passthrough to the parent operator that evaluated the division.
• 1390: allow decimal values for mod operation
  % can be operated on decimal values.
• 1388: Fix groupby on decimal col failing
  See detailed discussion here.
• 1386: more error checks for prefix lengths
  improves errors when creating prefix indexes
• 1383: Moved privilege caching to session
  This accomplishes two things:
  1. This changes how we cache privilege sets. The previous behavior was fine for the existing feature set, but it didn't support roles all too well. Right now all roles are applied to any user that they're assigned to, but in a future PR roles will be selectable (like they are in MySQL). Role selection is at the session level, so it makes sense that the cached privilege set is also at the session level. It's straightforward to update a sesson's roles and invalidate the cache in the same place.
  2. If an integrator wanted to see what privileges a session had, then they'd need to query the engine, which meant keeping a reference to the engine in multiple places. Now, they just query the session on the context, which they're already passing around.
• 1382: Flag for finished but empty CachedResult
  Hashed joins can have a CachedResult that filters to zero rows. We currently try to recompute the map every time when we find a nil result. This adds a short-circuit both to the CachedResult, and the parent join (n x 0 = 0). Every RowIter that attempts to re-rowIter a cachedResult that can be zero now should check for the ErrEmptyCachedResult sentinel. This is only possible in joinIter currently.
• 1381: Adding support for setting 'ANSI' as a sql_mode
  Fixes: https://github.com/dolthub/dolt/issues/4663
• 1372: allow server to start if unix socket file path is in use without unix socket set up
• 1371: improve errors for invalid index prefixes
  We currently allow specifying prefix lengths on primary and secondary keys defined on VARCHAR and CHAR text types despite not actually using the prefix for lookups. This PR allows dolt to throw the right errors.
  Additionally, we also allow specifying prefix lengths for non-string columns, which doesn't make sense.
  Hopefully nobody has created tables that depend on this buggy behavior.
• 1365: division / operation result in decimal type except for float type column reference
  Division operator, /, always results in decimal type value. Only exception is that if there is float type column reference on either side of the operator, it should result in float type value.
  Added decimal.Decimal type handling for ROUND and FLOOR functions

Closed Issues

• 4746: A CI/CD Observability Solution Suggestion
• 4715: LAST_VALUE window function seems not quite right
• 4653: panic on dolt table import -u --continue
• 4684: GROUP BY on decimal type column gives incorrect result
• 4584: arithmetic on integers should default to float
• 4680: Make Dolt's default unix socket creation best effort
• 4663: Support for setting ANSI SQL mode

Latency

Merged PRs

dolt

• 4714: GMS bump
• 4710: go/doltcore/sqle: pickup sql.IndexDef bump
• 4703: go/store/nbs: fix memory accounting for MemoryQuotaProvider
• 4692: Got rid of testing libraries from dolt binary
  This decreases the size of the linux dolt binary by 8MB, from 75 to 67MB.

go-mysql-server

• 1381: Adding support for setting 'ANSI' as a sql_mode
  Fixes: https://github.com/dolthub/dolt/issues/4663
• 1380: sql: Added IndexDef to sql/index.go, sql/core.go
• 1379: prepend rows for only projector nodes
  This PR was reverted but now it has fixed tests that passes for dolt engine tests. The table is keyless table, which causes output result to be in randomized order in dolt compared to gms, so the tests now include order by clauses to persist the order of the output rows.
• 1377: make json ordering tests less strict
  We shouldn't include tests for ordering between two MySQL "json object" types. MySQL notes that their ordering is unspecified but deterministic.
  The new storage format has a different deterministic ordering than the old storage format in dolt.
• 1373: Validate stored procedures, but only throw errors when they are invoked.
  This fixes https://github.com/dolthub/dolt/issues/4670

Closed Issues

• 1478: CREATE TABLE ... AS SELECT ... supports DISTINCT
• 4699: dolt sql not showing results (windows)
• 4606: show keys from information_schema.columns panics
• 4623: dolt sql-client panics if passed a branch scoped database
• 4628: dolt sql-client -q does not acceot multiple statements
• 4635: dolt sql-client --no-auto-commit still commits
• 3334: JSON output might be broken due to invalid escapes in the data
• 4670: Stored procedure referencing dropped column causes error during checkout or reset.
• 4365: IN predicate returns wrong results when used with GROUP BY ... HAVING subquery

Latency

Merged PRs

dolt

• 4688: go/libraries/doltcore/sqle/cluster: Add the ability to generate a keypair, serve a JWKS, and sign outbound RPCs with a JWT.
• 4679: go/store/nbs: remove mmapTableIndex
• 4669: go/doltcore/actions: Added special case handling for renamed tables on dolt reset --hard
  fix for https://github.com/dolthub/dolt/issues/751
• 4636: Support --amend in dolt_commit
  Fixes https://github.com/dolthub/dolt/issues/4595: dolt_commit procedure does not support --amend

go-mysql-server

• 1377: make json ordering tests less strict
  We shouldn't include tests for ordering between two MySQL "json object" types. MySQL notes that their ordering is unspecified but deterministic.
  The new storage format has a different deterministic ordering than the old storage format in dolt.
• 1376: Revert "remove having clause from prepending rows (#1368)"
  This reverts commit f179734a7fe27eae530e6e6cf6525e1f236824ea.
• 1375: don't escape <, >, or & when printing json
• 1374: enum zero() is 1
• 1369: Deleted handler_test_common.go (only used in handler_test.go)
  This prevents testify libraries from being included in binaries that depend on the server package.
• 1368: remove having clause from prepending rows
• 1367: Fix logic for replacing expressions with alias references in OrderBy clause
  The previous code in pushdownSort was able to replace a missing column reference with an alias reference when that column was aliased, but it wasn't able to correctly replace compound expressions such as ORDER BY BINARY(mytable.mycol) due to how we tracked only the missing column references and didn't consider the sort field expression as a whole.
  This change expands that alias substitution support so that it looks at the entire sort field expression (i.e. not just the individual named references inside it) and replaces missing sort fields with alias references only when the entire sort field expression matches the aliased expression.
  This fixes an issue with Prisma compatibility with Dolt (https://github.com/dolthub/dolt/issues/4511).
• 1366: Fixes filtering for null values on json extract
  This PR fixes two issues:
  • JSON_EXTRACT returning json null values when it should be returning sql null values if a path is unresolvable.
  • The relative ordering between json null values, sql null values, and json values.
• 1364: Updated and expanded engine examples
  The README.md had an updated example, but the actual _example/main.go file did not. I've expanded the example file a bit to include information on setting up users, and slightly simplified the README.md example. I've also added tests for everything, so that if anything breaks, we'll know we need to update both the example file and the README.md portion.
• 1363: Convert errors during ComPrepare to SQLError
  We were already converting errors in ComStmtExecute, ComMultiQuery, and ComQuery to SQLError so that the correct error codes would be sent to clients. This change adds that support to ComPrepare, too.
  Added a unit test for that case and took the opportunity to simplify the interface for CastSQLError a little bit.
  This change helps get Prisma support a little further along (https://github.com/dolthub/dolt/issues/4511), but it doesn't look like it fully resolves everything Prisma needs to work with Dolt.
• 1361: Fixed collation check on foreign key columns
• 1358: fix example package
  close #1357
  This fixes runtime panic raised by example app in /_example.
  I checked SQL client can obtain response in my local machine.

  ~/go-mysql-server/_example$ go build
  ~/go-mysql-server/_example$ ./_example
  

  $ mysql --host=127.0.0.1 --port=3306 --database=mydb -u root
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 1
  Server version: 5.7.9-Vitess
  Copyright (c) 2000, 2022, Oracle and/or its affiliates.
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  mysql> select * from mytable;
  +----------+-------------------+-------------------------------+---------------------+
  | name     | email             | phone_numbers                 | created_at          |
  +----------+-------------------+-------------------------------+---------------------+
  | Evil Bob | [email protected] | ["555-666-555","666-666-666"] | 2018-04-18 09:41:13 |
  | Jane Doe | [email protected]      | []                            | 2018-04-18 09:41:13 |
  | John Doe | [email protected]      | ["555-555-555"]               | 2018-04-18 09:41:13 |
  | John Doe | [email protected]   | []                            | 2018-04-18 09:41:13 |
  +----------+-------------------+-------------------------------+---------------------+
  4 rows in set (0.00 sec)
  

• 1356: add tests for sql type Zero() functions
  Implementers of GMS might expect similar values to be returned by Convert() and Zero(). For decimal and enum implementations this was not the case and has been fixed.
• 1355: Allow any select statement for CREATE TABLE AS SELECT ...
  Also fixes a semantics bug in the schema produced by some such statements.
• 1348: fix visibility for on duplicate key update
  Currently, we treat plan.InsertInto.Source independently from Destination, and is not considered one of InsertInto's children. It is evaluated much later in the analysis process in the rule resolveInsertRules in a similar way as subqueries (we recurse the analyzer on it). This is problematic if we want to reference tables from Source.
  In this PR, I resolve the tables for InsertInto.Source and added extra logic to correctly index those tables' columns.
  There is a special case for on duplicate key update <expr> in that the LHS of the expr can only see Insert.Destination while the RHS can see Insert.Destination and Insert.Source.
  Partial fix for: https://github.com/dolthub/dolt/issues/4562
  Note: This does not work for CTEs
  This is only kind of a fix for the issue. The right way to fix this is probably to completely resolve InsertInto.Source before doing anything else, but I wasn't able to get that working yet.
• 1339: Update DateAdd/DateSub to return correct types.
  This change fixes https://github.com/dolthub/dolt/issues/4376
  Previous implementation was hard-coded to return sql.Date, but now we are following the MySQL standard and return a type based on the inputs.
  The tests in the repo are verifying that the correct data is returned, but we're not testing the actual SQL data that is received, so none of our tests are catching this case yet. We should open a new work item to start testing the actual SQL that's being returned by dolt. For this bug, testing was performed using a local version of dolt with these changes.
  The failing query from the original bug is now working:

  SELECT NOW(), DATE_ADD(NOW(), INTERVAL 14 DAY), dolt_version();
  +----------------------------+----------------------------------+----------------+
  | NOW()                      | DATE_ADD(NOW(), INTERVAL 14 DAY) | dolt_version() |
  +----------------------------+----------------------------------+----------------+
  | 2022-10-18 18:53:24.406345 | 2022-11-01 18:53:24.406345       | 0.50.4         |
  +----------------------------+----------------------------------+----------------+
  1 row in set (0.02 sec)
  

Closed Issues

• 2984: Column aliasing with alias name the same as column name during aggregation queries results in column not found error (in some cases)
• 4595: dolt_commit procedure does not support --amend
• 1357: _example/main.go raises panic

Latency