Merged PRs

dolt

• 7792: Fix nil write session panic
• 7790: chore: fix some function names
• 7776: dolt_history tables: Issue a warning when types have changed
  Adding a session warning when dolt_history tables can't create a rowConverter for a row due to a field's type having changed. In these cases, the old value can't always be returned safely as the new type, so we return NULL and issue a session warning. In the future, we could attempt to convert the old value to the new type.
  Follow up to https://github.com/dolthub/dolt/pull/7771
• 7766: chore: use errors.New to replace fmt.Errorf with no parameters
• 7760: Support for multiple schemas in a database
  Companion PRs:
  https://github.com/dolthub/vitess/pull/338
  https://github.com/dolthub/go-mysql-server/pull/2466
  https://github.com/dolthub/doltgresql/pull/181
  This PR is a proof of concept for addressing multiple table namespaces (schemas) in a database. This is to support schemas in Doltgres initially, but could also be used to implement storing multiple databases in the same Dolt commit graph if we wanted to do that. All tables are stored in the root's table map like always, but tables with non-empty schemas have that schema name prepended in its map key, surrounded by null bytes.
  There are lots of things that don't work yet. This is what does work:

  create schema mySchema;
  create schema otherSchema;
  CREATE TABLE mySchema.test (pk BIGINT PRIMARY KEY, v1 BIGINT);
  insert into mySchema.test values (1,1), (2,2);
  CREATE TABLE otherSchema.test (pk BIGINT PRIMARY KEY, v1 BIGINT);
  insert into otherSchema.test values (3,3), (4,4);
  SELECT * FROM mySchema.test;
  SELECT * FROM otherSchema.test;
  

  Things I specifically want feedback on:
  • I am not crazy about the sweeping changes to table-access interfaces in root_val.go. In particular it seems likely to cause errors over time as people forget to include schema names in code changes where necessary, silently breaking doltgres. Another option I considered was to introduce some sort of RootReader interface on top of root value, which you create with a particular schema name, and thereafter all table accesses reference the given schema. There may be places where we have to juggle multiple RootReader instances at once, rather than just asking for qualified table names from a single root. But this is the pattern I used for the boundary between GMS and Dolt and it worked well, so maybe it could work for root_val.go as well.

go-mysql-server

• 2482: don't check schema compatibility for select into

  fixes: https://github.com/dolthub/dolt/issues/7781

• 2481: aliasExpr inputExpression case-insensitive check
  This PR adds case-insensitive check for aliasExpr.InputExpression and expr.String() to determine the expr is referencable.

• 2473: remove re-parsing select stmt in create view stmt
  Depends on https://github.com/dolthub/vitess/pull/339

• 2466: Schema-qualified table names
  This PR also fixes a couple unrelated issues:

  • IMDB query plans are brought up to date (this is most of the change lines)
  • Fixed bugs in certain show statements (information_schema tests)

vitess

• 343: Exposing binary JSON encoding to SQL expression logic for testing
  Exposing logic in Vitess that decodes MySQL's internal, binary JSON serialization encoding, so that we can use it to test JSON serialization.
• 342: refactor and fully implement alter table syntax
  MySQL Docs; https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
  fixes: https://github.com/dolthub/go-mysql-server/issues/2472
• 340: improving partition parsing
  partially addresses: https://github.com/dolthub/go-mysql-server/issues/2472
• 339: capture select expressions that are sub statements within other expressions
• 338: Add a schema qualifier to table names

Closed Issues

• 6064: Prevent Indexing JSON Fields
• 7781: cannot convert type longtext COLLATE utf8mb4_0900_ai_ci to datetime
• 2472: Parser support for PARTITION BY

Merged PRs

dolt

• 7778: Bump mysql2 from 3.9.4 to 3.9.7 in /integration-tests/mysql-client-tests/node
  Bumps mysql2 from 3.9.4 to 3.9.7.
• 7775: Allow updating conflict table even if the underlying table schema changed.
  This is an old check from before we had schema merge. I'm convinced that it was never actually needed. This PR has the necessary changes in order to remove it.
  It's important that the user is able to resolve data conflicts even if the table schema has changed, because otherwise it is not possible to manually resolve data conflicts after a schema merge.
  This PR also contains some additional tests, some of which are currently disabled because of https://github.com/dolthub/dolt/issues/7767 and https://github.com/dolthub/dolt/issues/7762
• 7771: Don't generate new tags for column type changes
  Currently, when a column's type changes to a new type family (e.g. changing from varchar to int), we assign a new column tag. This prevents us from easily identifying the column across type changes. For example:
  • If column X is renamed and then its type is later changed, we can't track it across the rename or type change anymore. This usage pattern removes the value that column tags were intended to provide.
  • If column X changes its type, we can't easily differentiate between column X being dropped and a new column X being added. Heuristically, we can look at all the data in the table if it's all exactly identical, then we know it's a rename, but this is a somewhat hacky heuristic that can become ambiguous in more nuanced cases. This currently causes us to generate incorrect SQL patch statements that drops the renamed column and adds a new column. If customers were to apply these incorrect patch statements, it would drop all the data from their existing column, instead of converting it to the new type. This affects dolt_patch() and dolt diff -r sql and also prevents us from generating correct DDL statements for binlog replication.
    This PR changes schema alterations so that columns now retain their original column tag across type changes, which fixes the issues above.
    A side effect of this change is that if a customer is working on a branch, creates a column, and changes its type, the tag won't match if the schema on another branch is updated to the final type change (i.e. without going through the initial/intermediary type). Code was originally added for this case (see: https://github.com/dolthub/dolt/issues/3950), however, since then, we have added support for schema merging and this isn't an issue anymore. I've confirmed that we can successfully merge non-matching column tags, from both directions, and added a test for this case, too.
    Longer-term, I agree we should continue exploring removing tags completely, but in the short-term, this fixes a correctness problem with SQL patch statement generation that binlog support needs.
    Related to: https://github.com/dolthub/dolt/issues/6710#issuecomment-1734318087
• 7769: AUTO_INCREMENT columns have NOT NULL constraint
  companion pr: https://github.com/dolthub/go-mysql-server/pull/2467
• 7768: fix: fix function name
• 7731: [nbs] getMany read planning and parallelize
  Add read planning for journal getMany so instead of doing randIO we get seqIO. SeqIO has a large effect for disk storage systems. Also parallelize getMany calls. The biggest change here is the semantics around how reading locks the journal file. Reads hold the journal lock for longer, which could lower write throughput in some cases. If we see evidence of this, we can do more work to limit the amount of time batch reads can interruptibly holding the journal lock.

go-mysql-server

• 2468: Remove session mutex, status variables atomic inc
  Avoid concurrent ctx use in doQuery. I standardized the session status variables to uint64 to make it easier to have them be atomics. The return types for these variables in MySQL seem to be strings, so switching them from int640->uint64 seems OK. We could always change the presentation layer later. Removing the session mutex appears to be safe after these changes.
• 2467: improve DESCRIBE columns and auto_increment bugs
  This PR makes it so our DESCRIBE statement is more correct when there are unique keys.
  There's an edge case we miss:

  create table t (i int not null, j int not null, unique key (j), unique key (i));
  describe t;
  

  In MySQL, UNIQUE KEY j is created before UNIQUE KEY i, so describe makes j as PRI.
  In Dolt, we store indexes in a map and return them in order of index name, so we mark i as PRI.
  There are skipped tests for this
  Additionally, this adds the NOT NULL constraint to columns that are marked AUTO_INCREMENT.
  partially addresses: https://github.com/dolthub/dolt/issues/2289
• 2465: projection schema finds default values
  fixes: https://github.com/dolthub/dolt/issues/6016
• 2464: skipping auto_increment on error tests
  I was wrong, this is very broken in dolt.
  Could not find a quick fix, so skipping tests to unblock auto-bumps.
  reopens: https://github.com/dolthub/dolt/issues/3157

vitess

• 340: improving partition parsing
  partially addresses: https://github.com/dolthub/go-mysql-server/issues/2472
• 337: Consistently using pointer to AliasedValues in InsertRows interface, never values.
  Once again, golang reminds me that a value type implementing an interface forces the pointer type to also implement the interface, and mixing the two messes up our runtime type reflection.
  I changed all uses of the AliasedValues in InsertRows interface to be pointers so that we can interact with them consistently on the GMS side.

Closed Issues

• 3129: dolt login works with DoltHub, but not a DoltLab instance

Performance

Merged PRs

dolt

• 7757: chore: fix some comments
• 7739: Fix some comments
• 7735: Bump mysql2 from 2.3.3 to 3.9.4 in /integration-tests/mysql-client-tests/node
  Bumps mysql2 from 2.3.3 to 3.9.4.
• 7704: Implement traditional auto-increment lock mode hold the lock for the duration of the insert iter.
  Fixes https://github.com/dolthub/dolt/issues/7634
  This is the dolt half of https://github.com/dolthub/go-mysql-server/pull/2439
  This adds support for innodb_autoinc_lock_mode=0 ("traditional"). When this system variable is set, the engine will guarantee that every insert statement generates consecutive IDs for AUTO INCREMENT columns.
  This PR also allows the user to set innodb_autoinc_lock_mode=1 ("consecutive"), although the behavior is currently identical to "traditional". This is acceptable because both modes make the same guarantees (that each statement gets consecutive IDs), and the observed behavior is the same in almost all cases.
  (The "consecutive" contains an additional optimization: if there is a known upper bound on the number of IDs that must be generated for an insert, under "consecutive" mode the engine will just increment the counter by that upper bound and immediately release the lock. In places where not all of those IDs are actually used, the excess are wasted. This PR does not include that optimization. Thus, with this PR, traditional and consecutive behave the same.)

go-mysql-server

• 2464: skipping auto_increment on error tests
  I was wrong, this is very broken in dolt.
  Could not find a quick fix, so skipping tests to unblock auto-bumps.
  reopens: https://github.com/dolthub/dolt/issues/3157
• 2463: Update GMS to detect INSERT statements with row alias and return error.
  We parse these statements but don't yet support them. So for now we return a helpful error.
• 2461: tests for auto_inc with error
  Hard to tell which PR or combination or PRs fixed this, but this issue no longer reproduces in GMS or Dolt.
  This PR just adds an explicit test case for it.
  fixes https://github.com/dolthub/dolt/issues/3157
• 2460: implement last_days()
  mysql docs:
  https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_last-day
• 2458: add table comments to information_schema.tables
  fixes https://github.com/dolthub/dolt/issues/2389
• 2456: support to_days() and from_days()
  This PR implements the TO_DAYS and FROM_DAYS MySQL functions.
  Initially, I attempted to use the existing logic from Add/Sub Date along with the 0 year, but there were overflow issues.
  So, there's a skipped test for Intervals.
  to_days docs:
  https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_to-days
  from_days docs:
  https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-days
• 2455: Feature: gtid_subtract() function
  Adds support for MySQL's gtid_subtract() built-in function:
  https://dev.mysql.com/doc/refman/8.0/en/gtid-functions.html#function_gtid-subtract
• 2452: Adding support for the SHOW BINARY LOG STATUS statement
  Adds support for handling MySQL's SHOW BINARY LOG STATUS statement.
  As with the other replication commands, we test the privilege checks in GMS and test the actual integration for data flowing through this statement in Dolt.
• 2449: Bug fix: SHOW VARIABLES LIKE should be case-insensitive
  Bug fix to make SHOW VARIABLES LIKE '...' match MySQL's behavior by matching patterns case-insensitively.
  Also includes changes to add the missing binlog_format system variable that some replication clients need to query, and initializes the server_uuid system variable.
• 2448: use SubStatementStr field in DDL for getting sub statement in CREATE VIEW
  This PR allows using defined string for sub statement instead of slicing the original query. If this field is empty, then slice the original query to get the sub statement.
• 2446: support DECLARE in BEGIN...END BLOCK in TRIGGER
  This PR allows us to use DECLARE statements in TRIGGERS.
  The analyzer rule applyTriggers was altered to initialize ProcedureReference for TriggerBeginEndBlock.
  The important part was ensuring that all relevant nodes (TriggerBeingEndBlock, BeginEndBlock, ProcedureParam) all had the same ProcedureReference and to search in all the nodes.
  Additionally, nil guards are added to all receiver methods for ProcedureReference to prevent panics.
  It seems like events might have this issue, but there's a banaid fix for it. Not sure if I want to touch that code.
  fixes: https://github.com/dolthub/dolt/issues/7720
• 2445: updating declare in trigger error to be more descriptive
• 2443: docs: very minor grammar fixes in README.md
  Hi, I just wanted to fix a couple of minor grammatical errors in the README.md file.
• 2439: For AutoIncrement lock modes other than "interleaved", hold the lock for the duration of the insert iter.
  This is the GMS side of https://github.com/dolthub/dolt/issues/7634
  This PR changes the engine to make it acquire a lock (provided by the storage layer) when innodb_autoinc_lock_mode is set to the "consecutive" or "traditional" values. More specifically, it calls a new function in the AutoIncrementSetter interface which optionally acquires a lock and returns a callback for releasing the lock.
  The in-memory DB doesn't have multithreading support, so when just using GMS, this is a no-op. A followup PR in Dolt will update its implementation of AutoIncrementSetter to handle the locking.

vitess

• 337: Consistently using pointer to AliasedValues in InsertRows interface, never values.
  Once again, golang reminds me that a value type implementing an interface forces the pointer type to also implement the interface, and mixing the two messes up our runtime type reflection.
  I changed all uses of the AliasedValues in InsertRows interface to be pointers so that we can interact with them consistently on the GMS side.
• 336: Adding support for subtracting GTIDSets
  Needed to support the gtid_subtract() built-in function. Also exposes the ParseMysql56GTIDSet function so that we can parse GTIDSet strings from GMS.
  Related GMS PR: https://github.com/dolthub/go-mysql-server/pull/2455
• 334: Parser support for FLUSH TABLES statement
  Adds parser support for the FLUSH TABLES <tablename_list> [WITH READ LOCK] statement.
  Also adds SHOW MASTER STATUS as an alias for SHOW BINARY LOG STATUS for compatibility with clients that use the older syntax (e.g. Debezium).
• 333: Add support for parsing row and column aliases on insert statements.
  Related to https://github.com/dolthub/dolt/issues/7638
  This allows us to parse statements of the form:
  INSERT INTO t VALUES (?, ?, ?) AS new ON DUPLICATE KEY v = new.v;
• 332: add SubStatementStr for DDL struct
• 331: add check option to create view
  This PR adds optional WITH CHECK OPTION support for CREATE VIEW
• 330: support rename constraint syntax
  This PR adds syntax support for ALTER TABLE ... RENAME CONSTRAINT [FOREIGN KEY / CHECK]... for foreign key constraints.

Closed Issues

• 6016: Schema of "Create table t SELECT * FROM tbl" drops default values when compared to MySql
• 2199: trim(trailing ',' from column) syntax not supported
• 7093: Nested subquery problems
• 6553: Index scan analysis perf
• 6407: Alias references in subquery expressions
• 6226: Dolt picks the wrong filter order. Very slow query resulting in DoltHub timeouts
• 6180: Slow Join + Filter query
• 5954: select ... not in ... returns wrong results for nulls
• 2389: Table comments are "ignored"
• 5661: EXPLAIN with uuid_to_bin is not working in case primary key in IntelliJ IDEA
• 7385: Messaging on a merge where a table was altered and deleted on each side of the merge not helpful
• 5958: Prepared statements v2
• 5993: Joins missing optimal indexes
• 5444: Slow join orders
• 5405: dolt clone command only output is the word 'Killed'
• 3922: dolt sql and other commands should connect to running database
• 3364: Pandas "shell" as alternative to dolt sql
• 2638: dolt conflicts resolve <table> --combine-ours or --combine-theirs
• 4489: support for := operator in SELECT
• 4479: Support row and column aliases in INSERT ON DUPLICATE KEY UPDATE
• 1995: Expecting Indexed JOINs. Got Inner JOINs.
• 4485: ORDER BY expressions containing aggregation functions are not handled appropriately by the analyzer.
• 505: Dolt CLI should always use the pager when results are more than one page.
• 7720: Crash on BEFORE INSERT trigger using DECLARE variable
• 7634: Support for traditional and consecutive behavior for innodb_autoinc_lock_mode
• 2447: Any Benchmarks available

Performance

Merged PRs

dolt

• 7727: Version Command Refactor
  The Version command is currently used by doltgres, but there are some issues. You can't add extra args to it, and it prints out the wrong binary name. This refactor makes the binary name printed by the Version command configurable, and it also refactors the Exec so that its functionality can be extended by doltgres without having to duplicate things like checkAndPrintVersionOutOfDateWarning, and the printing of the feature version.
• 7724: Move Version
  Moves Version into a package where it can be referenced by external code.
• 7721: Improve error messaging when encountering a primary key schema change during a merge.
  When a merge encounters a table that can't be merged because its primary key changed, we now:
  • Include the name of the affected table
  • Indicate whether the primary key discrepancy is between the two branches or between one branch and the common ancestor.
• 7718: [store] use struct{} as a value in a hashset
  Testing if CI works with branch in repo. Original: https://github.com/dolthub/dolt/pull/7706
• 7713: [statspro] Avoid duplicating branch suffix when resolving stats database
  fixes: https://github.com/dolthub/dolt/issues/7710
• 7711: match mysql fk name generation
  This PR removes logic where we would use a consistent hash to create foreign key names, when one isn't explicitly provided.
  Instead, we use logic in GMS to generate a name with the template "<tbl_name>_ibfk_<num>", which matches MySQL behavior.
  As a result, we now allow multiple foreign keys over the same sets of columns (as long as the names are different).
  companion pr:
  https://github.com/dolthub/go-mysql-server/pull/2438
  fixes:
  https://github.com/dolthub/dolt/issues/7650
• 7708: chore: fix function names in comment
• 7707: go/libraries/doltcore/dbfactory, misc: Ensure we close a *grpc.ClientConn when we are done with it.
  In the cluster commithook, we had a failure mode where we would leak a *grpc.ClientConn without closing it. It turns out, if an outbound request has made on a ClientConn, it will continue retrying the connection. And we would leak one on a fixed interval, resulting in ever-increasing CPU utilization.
• 7706: [store] use struct{} as a value in a hashset
• 7704: Implement traditional auto-increment lock mode hold the lock for the duration of the insert iter.
  Fixes https://github.com/dolthub/dolt/issues/7634
  This is the dolt half of https://github.com/dolthub/go-mysql-server/pull/2439
  This adds support for innodb_autoinc_lock_mode=0 ("traditional"). When this system variable is set, the engine will guarantee that every insert statement generates consecutive IDs for AUTO INCREMENT columns.
  This PR also allows the user to set innodb_autoinc_lock_mode=1 ("consecutive"), although the behavior is currently identical to "traditional". This is acceptable because both modes make the same guarantees (that each statement gets consecutive IDs), and the observed behavior is the same in almost all cases.
  (The "consecutive" contains an additional optimization: if there is a known upper bound on the number of IDs that must be generated for an insert, under "consecutive" mode the engine will just increment the counter by that upper bound and immediately release the lock. In places where not all of those IDs are actually used, the excess are wasted. This PR does not include that optimization. Thus, with this PR, traditional and consecutive behave the same.)
• 7699: Add per-table locking for AutoIncrementTracker
  This PR refactors the AutoIncrementTracker to hold a separate mutex for each table instead of a single mutex for the entire database.
• 7689: Allow 'old' versions of Dolt to handle future table file versions
  Now that we are fairly certain that the dolt table format for compressed history files will have a new file signature, we want current versions of dolt to provide decent messages if one of those files is encountered.
  Automated testing of this is tricky at the moment. I hand edited an existing table file to ensure that a sane message comes back.
  Also, dolt sql-server doesn't currently print a message at all when a bad table file is encountered.
• 7670: Add name on cd-release-pgo workflows
  add name on cd-release-pgo workflows

go-mysql-server

• 2442: prevent panic on triggers with declare statements
  We're reusing a code from stored procedures to handle begin end blocks, but we're missing a lot of set up that prevents certain variables from being nil. Consequently, we panic in a couple places.
  This PR fixes some of those panics, but reveals other problems we have in resolving/executing triggers of this format.
  Partially addresses: https://github.com/dolthub/dolt/issues/7720
• 2440: support ALTER TABLE ... RENAME CONSTRAINT ... for foreign key constraints
  This PR adds support for ALTER TABLE ... RENAME CONSTRAINT ... for foreign key constraints.
  This is a feature that is NOT supported in MySQL, but we're adding it to make it easier to resolve merge conflicts resulting from foreign key name collisions.
  related: https://github.com/dolthub/go-mysql-server/pull/2438
• 2439: For AutoIncrement lock modes other than "interleaved", hold the lock for the duration of the insert iter.
  This is the GMS side of https://github.com/dolthub/dolt/issues/7634
  This PR changes the engine to make it acquire a lock (provided by the storage layer) when innodb_autoinc_lock_mode is set to the "consecutive" or "traditional" values. More specifically, it calls a new function in the AutoIncrementSetter interface which optionally acquires a lock and returns a callback for releasing the lock.
  The in-memory DB doesn't have multithreading support, so when just using GMS, this is a no-op. A followup PR in Dolt will update its implementation of AutoIncrementSetter to handle the locking.
• 2438: have generated index and foreign key names match mysql
  changes:
  • auto-generated secondary index names don't match (after a conflict)
  • we started at 0, while mysql starts at 2
  • auto-generate foreign key names in a way that matches mysql
  • in-memory used to just have empty string as the constraint name
    we used to error when generating foreign keys over the same sets of columns, but not anymore
    related: https://github.com/dolthub/dolt/issues/7650
• 2437: lowercase when looking up self referential foreign key columns
  fixes: https://github.com/dolthub/dolt/issues/7700
• 2429: server trace time includes parsing

vitess

• 330: support rename constraint syntax
  This PR adds syntax support for ALTER TABLE ... RENAME CONSTRAINT [FOREIGN KEY / CHECK]... for foreign key constraints.
• 329: Changes to binlog event creation functions
  • Exposing the Length() function in the BinlogEvent interface so calling code can access the event size present in a binlog event's header. Needed for calculating binlog file position.
  • Renaming FakeBinlogStream → BinlogStream so calling code can use it when serializing binlog events.

Closed Issues

• 7634: Support for traditional and consecutive behavior for innodb_autoinc_lock_mode
• 7722: remote 'origin' not found.
• 7650: Default constraint symbol value is not same as MySQL, lead to replication breaks
• 2394: AUTO_INCREMENT clause missing in result from SHOW CREATE TABLE
• 7710: Statistics database qualify fails for branch connection string
• 2364: Panic when executing sql statements from external command line tool after startup

Merged PRs

dolt

• 7688: add alternative type check for auto_increment type validation
• 7679: dolt_verify_constraints() support for all constraint types
  The dolt_verify_constraints() stored procedure now supports verifying all constraint types: foreign keys, not null, unique, and check constraints. This new implementation reuses merge logic to calculate the constraint violations.
  Note that the dolt constraints verify CLI command next needs to be migrated to use this stored procedure so that it can also verify all constraint violation types. Until then, customers can use dolt sql -q "CALL dolt_verify_constraints(...)" if they want to verify all constraint types from the CLI.
  Doc updates: https://github.com/dolthub/docs/pull/2093
  Fixes: https://github.com/dolthub/dolt/issues/6320

go-mysql-server

• 2437: lowercase when looking up self referential foreign key columns
  fixes: https://github.com/dolthub/dolt/issues/7700
• 2436: Making @@server_id default value match MySQL
• 2434: Fixing the default value for the binlog_checksum system variable
  Small change to make the default value of the global binlog_checksum system variable match MySQL's default value (i.e. "CRC32").
• 2433: NULL to nil
  The SHOW FIELDS/COLUMNS FROM <table> query would return the string "NULL" for Default column rather than nil.
  This mattered for Knex, which relied on it being NULL and not "NULL".
  fixes: https://github.com/dolthub/dolt/issues/7692
• 2432: support Threads_connected and Threads_running status variables
  This PR adds support for Threads_connected and Threads_running status variables.
  Additionally, the local enginetest are flaking consistently in dolt ci, so those have been removed;
  we have handler tests for com_delete, com_insert, and com_update anyway.
  Related: https://github.com/dolthub/dolt/issues/7646
• 2431: Setting Innodb_buffer_pool_pages_total to 1, to avoid an issue with Datadog's collector
  Datadog's metric collector errors out with a divide by zero error if the Innodb_buffer_pool_pages_total status variable is 0; changing it to 1 avoids this and allows the agent to collect metrics from Dolt.
• 2430: have status variables use go routines
  This PR changes Status Variables to update through go routines, to avoid slowing down query execution due to the mutexes present.
• 2426: use @@session.collation_server during create database ...
  This PR makes it so create database ... actually reads the @@session.collation_server variable.
  Additionally, this ensures that settings @@character_set_server sets @@collation_server and vice versa.
  Interestingly, it seems like MySQL actually ignores the global scope of these system variables, and reads the session scope instead.
  fixes https://github.com/dolthub/dolt/issues/7651

vitess

• 329: Changes to binlog event creation functions
  • Exposing the Length() function in the BinlogEvent interface so calling code can access the event size present in a binlog event's header. Needed for calculating binlog file position.
  • Renaming FakeBinlogStream → BinlogStream so calling code can use it when serializing binlog events.
• 328: revert decimals
  issue: https://github.com/dolthub/vitess/pull/328

Closed Issues

• 7700: self referential foreign key is case sensitive
• 7649: Questions about Dolt
• 7651: set global character_set_server not working the same way in MySQL, may lead inconsistency
• 6320: dolt verify-contraints does not check unique constraints
• 7692: Show fields returns 'NULL' (in quotes)
• 7665: Error 1105: type time.Time not supported as bind var

Merged PRs

dolt

• 7673: CLI Docs: Adding docs for system_variables field in config.yaml for sql-server command
  The CLI docs for sql-server doesn't include the system_variables field available in config.yaml.
• 7672: [store] use hasCache to minimize pendingRef pool
  Expand the ChunkStore interface to let the nodeStore access recently accessed chunks. Avoid adding a child ref to the pendingRef list when already present in nbs.hasCache. For TPC-C this appears to reduce the pending ref count by another ~80%.
• 7666: [statspro] Avoid copying histograms, perf improvement
  companion: https://github.com/dolthub/go-mysql-server/pull/2421
• 7654: [store] one hashset for child ref checks
  When writes add chunks at execution time, we currently add their direct child references to the memory table's pending ref queue to check for GC'd refs. We do this by constructing a hash map to pass every chunk's child refs to the memtable's pending queue. This PR avoids creating a new hashset per chunk by using a callback to delay reading out the refs. We make one child ref set per memtable, which has the added benefit of deduplicating child refs between chunks.

go-mysql-server

• 2427: support Com_delete, Com_insert, Com_update status variables
  related: https://github.com/dolthub/dolt/issues/7646
• 2426: use @@session.collation_server during create database ...
  This PR makes it so create database ... actually reads the @@session.collation_server variable.
  Additionally, this ensures that settings @@character_set_server sets @@collation_server and vice versa.
  Interestingly, it seems like MySQL actually ignores the global scope of these system variables, and reads the session scope instead.
  fixes https://github.com/dolthub/dolt/issues/7651
• 2423: Adding test for preparing time.Time types
  This PR adds tests for using time.Time, some tests have to be skipped because we don't support Timespan correctly.
  companion pr: https://github.com/dolthub/vitess/pull/327 https://github.com/dolthub/vitess/pull/328
  test for https://github.com/dolthub/dolt/issues/7665
• 2422: Support Questions status variable
  This PR adds logic to update status variable Questions.
  This only works in the server context, probably doesn't through dolt sql cli.
  https://github.com/dolthub/dolt/issues/7646
• 2421: [stats] costed index scan perf
  Histogram copying is expensive. Instead pass and mutate references. We have to use a different struct type to load stats from JSON in order to support histogram interface generalization.
  related Dolt-side: https://github.com/dolthub/dolt/pull/7666
• 2420: support case-insensitive LIKE for show status/variables
  MySQL stores session and global variables in a performance_schema database, and these tables have a case-insensitive collation on the variable names.
  This PR emulates that behavior by hard coding the collation the schemas for ShowStatus and ShowVariables nodes.
• 2419: Bug fix: Allow JSON scalar comparison between int64 and float64
  When comparing JSON values, numbers may be represented internally as an int64 or float64, but our comparison code wasn't casting an int64 to a float64 in order to compare it with a float64 value.
  Fixes https://github.com/dolthub/dolt/issues/7656
• 2418: fix show create database to actually show charset/collation
  This PR fixes the SHOW CREATE DATABASE ... statement to actually show the charset/collation that the db is under instead of always default.
  Additionally, this PR parses the charset database option, instead of ignoring it like before.
  partially fixes: https://github.com/dolthub/dolt/issues/7651
• 2416: /{.github,go.mod,go.sum}: bump go version
• 2414: stubbing out status variables
  This PR adds the initial implementation of Status Variables.
  There are 682 status variables, and are very similar to System Variables.
  Every variable is read-only (and can only be updated by the server itself), and there are session-specific variables.
  MySQL Docs: https://dev.mysql.com/doc/refman/8.0/en/server-status-variable-reference.html
  Related: https://github.com/dolthub/dolt/issues/7646

vitess

• 328: revert decimals
  issue: https://github.com/dolthub/vitess/pull/328
• 327: add case for time.Time and decimal in bindvars
  We were unable to use time.Time and decimal type variables bind vars, so they couldn't be used as arguments to prepare statements.
  This PR addresses that issue.
  fixes: https://github.com/dolthub/dolt/issues/7665
• 325: /{.github,go.mod,go.sum}: bump go version
• 320: Updates for binlog primary protocol
  • Porting over more code from vitess.io/vitess – support for the COM_REGISTER_REPLICA command.
  • Fixing a bug in Vitess' deserialization of the COM_BINLOG_DUMP_GTID command.
  • Adding some String functions to help with debugging.
  • Exposing the ability to flush a connection's buffer, as a short-term workaround for needing to flush the buffer more frequently than at the end of the connection in order to support `COM_BINLOG_DUMP_GTID'.

Closed Issues

• 7651: set global character_set_server not working the same way in MySQL, may lead inconsistency
• 7601: One more issue related to the Adobe Commerce ( Magento ) installation.
• 7665: Error 1105: type time.Time not supported as bind var
• 7656: JSON_CONTAINS regression
• 7653: Panic in calculateMergeConflicts during dolt pull if sql-server is running

Performance

Merged PRs

dolt

• 7659: Don't panic for conflicts in the dolt pull command
  This was reported by a user: https://github.com/dolthub/dolt/issues/7653
  This was covered by a test, but it turns out that the pull, fetch, and push tests were all using SQL_ENGINE=remote-server incorrectly.
• 7558: Statistics for multiple branches
  Fork interface for how to store database statistics. Can either store in the original source database, in a separate database in .dolt/stats, or an alternative implementation like a lsm that will have easier append only semantics. The implementation for the noms chunkstore isn't particularly efficient, we will not deduplicate common chunks between branches.
  How the new code is organized: statspro has generic interfaces for how a Dolts-specific stats implementation works. statsnoms is an implementation that uses a second database at .dolt/stats to store statistic refs. The stats refs are the same, just now they are named by the branch they reference (ex: refs/statistics/main). So storage is the concern of the concrete implementation. The common interface forces the implementation to handle branches. The branch switching in statsnoms are just named refs.
  A high level of what's happening during certain operations: There are still two operations, load and update. Load now either initializes the stats database at .dolt/stats or loads existing stats. Update is the same, ANALYZE or auto refresh.
  Most of the changes are just forcing the logic through a generic interface. There were import cycle issues (dtables) and deadlock issues for creating a database (dolt provider takes a lock that prevents doing certain operation on the session in the stats provider) that motivated packaging decisions.

go-mysql-server

• 2412: New interface for binlog primary callbacks
  First pass on connecting the GMS layer with the Dolt layer for handling callbacks when the SQL server is acting in binlog primary mode, through the new BinlogPrimaryController interface. This new interface pretty closely mirrors the existing callback interface for replica callbacks, the BinlogReplicaController interface.
  Related to https://github.com/dolthub/dolt/issues/7512

Closed Issues

Performance

Merged PRs

dolt

• 7644: go/store/datas/pull: pull_chunk_fetcher: Move chunk fetching to a streaming interface instead of batch.
  We want to better pipeline I/O when pulling from a remote, and moving a streaming interface where storage can see more of the addresses that are needed at once will allow us to achieve it.
  For now, we implement the streaming interface just by calling the existing batch get interface.
• 7643: [no release notes] odd n ends clean up
  Random things I cleaned up while digging into NBS. All NoOp.
• 7637: innodb_autoinc_lock_mode tests
  companion pr: https://github.com/dolthub/go-mysql-server/pull/2410
• 7629: refactor tablespec
  Use changes to handle table option parsing, and apply auto_increment table option.
  Additionally, changes resetScripts() in doltHarness to not drop and recreate all auto_increment tables.
  This step is not necessary, and causes problems now because we actually display and read AUTO_INCREMENT table option in show create table statements.
  Contains changes from: https://github.com/dolthub/dolt/pull/7631
  Companion PR: https://github.com/dolthub/go-mysql-server/pull/2401
• 7582: add dolt_hashof_table func and dolt_hashof (Deprecate hashof)
  I created this function for my GDC demo. We can talk about pros and cons of integrating this back to main.
• 7579: use system variable interface
  Depends on GMS PR: https://github.com/dolthub/go-mysql-server/pull/2375
• 7572: go/store/datas/pull: pull_table_file_writer.go: Optimize pull/push to allow for concurrent table file uploads.
  Move management of the upload workers and the table file writer to its own struct which is testable in isolation.

go-mysql-server

• 2412: New interface for binlog primary callbacks
  First pass on connecting the GMS layer with the Dolt layer for handling callbacks when the SQL server is acting in binlog primary mode, through the new BinlogPrimaryController interface. This new interface pretty closely mirrors the existing callback interface for replica callbacks, the BinlogReplicaController interface.
  Related to https://github.com/dolthub/dolt/issues/7512
• 2411: implement json_search()
  MySQL Docs: https://dev.mysql.com/doc/refman/8.3/en/json-search-functions.html#function_json-search
• 2410: Adding system variable innodb_autoinc_lock_mode
  We currently only support innodb_autoinc_lock_mode = 2, not 0 or 1.
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
  related: https://github.com/dolthub/dolt/issues/7634
• 2404: Improve handling of unsigned and decimal types in JSON
  Fixes https://github.com/dolthub/go-mysql-server/issues/2391
  MySQL's JSON type differs from standard JSON in some important ways. It supports types not supported in standard JSON, such as separate types for integers and floats, an unsigned int type, and a decimal type.
  Prior to this PR, we would convert values to JSON by using the encodings/json package to marshall the value to a JSON string and then unmarshall it to a go map. This is not only slow, but it's incorrect for these additional types.
  The main purpose of this PR is to add special handling for these types that allow them to be stored in JSON documents. We also avoid generating and parsing JSON in places where it's not actually necessary, and fix bugs where decimals get incorrectly converted into strings, or unsigned ints get converted into signed ints.
  Finally, this fixes an issue where we send incorrect bytes for JSON-wrapped decimal values along the wire.
• 2403: fix dbName not being used in the example
  This PR makes sure that dbName in the example is actually being used, instead of having a hardcoded "mydb" in createTestDatabase.
  fixes #2402
• 2401: refactor and parse table options, support auto_increment table option
  Table Options are now parsed as structs, so we can read/use some of the variables.
  Character Sets, Collations, Create Table, TableSpec, etc. have been refactored.
  Additionally, this PR adds support to parse and use the auto_increment table option.
  TODO:
  • CREATE TABLE ... LIKE ... needs to preserve table opts, like comments
  • alter table add column ... auto_increment does not work when there are already rows
    Companion PR: https://github.com/dolthub/vitess/pull/322
• 2399: fix custom insert ordering for pk
  fixes https://github.com/dolthub/go-mysql-server/issues/2397
• 2398: fix in-memory implementation of RenameTable to read from session
  The in-memory implementation of RenameTable uses data from the BaseDatabase, instead of reading it from the session.
  This is problematic when there are multiple alter statements.
  Additonally, includes some small refactor so all functions are pointer receiver instead of a mix.
  fixes https://github.com/dolthub/go-mysql-server/issues/2396
• 2394: Bug fix: Set non-boolean system variable enum values to 'ON' or 'OFF'
  We were automatically converting ON and OFF values to to true and false when setting a system variable, which made it impossible to set system variables to those enum values. For example:

  SET @@GLOBAL.gtid_mode='ON';
  Variable 'gtid_mode' can't be set to the value of 'true'
  

• 2393: Restored and refactored missing functionality required by Dolt
• 2375: create system variable interface
  This PR creates SystemVariable and SystemVariableScope interfaces.
  This allows doltgres to use these interfaces for defining and handling all the configuration parameters.
  The SystemVariable struct is renamed to MysqlSystemVariable.
  The SystemVariableScope byte is renamed to MysqlSVScopeType.

vitess

• 324: Ensure that float values parsed into expressions will always be parsed back into floats, not decimals.
• 323: Parser support for SOURCE_AUTO_POSITION
  When acting as a replica, Dolt implicitly assumes SOURCE_AUTO_POSITION is set to 1. This adds parser support so that it can be explicitly specified. Adding this so that when we're configuring a MySQL replica, we can use the same setup code without having to special case this parameter.
• 322: parse table options into struct
  Instead of just making a large string that is reparsed in GMS, parse table_options into a list of structs similar to how we handle index_options and constraint_options.
• 321: [ast] walk tableFuncExpr for bind variables
• 320: Updates for binlog primary protocol
  • Porting over more code from vitess.io/vitess – support for the COM_REGISTER_REPLICA command.
  • Fixing a bug in Vitess' deserialization of the COM_BINLOG_DUMP_GTID command.
  • Adding some String functions to help with debugging.
  • Exposing the ability to flush a connection's buffer, as a short-term workaround for needing to flush the buffer more frequently than at the end of the connection in order to support `COM_BINLOG_DUMP_GTID'.
• 317: Port: Support for Vitess server to send binlog events
  Porting over support from the main Vitess repo for a server to send back binlog events over a connection. Also includes support for handling the COM_BINLOG_DUMP_GTID command.
  Related to https://github.com/dolthub/dolt/issues/7512

Closed Issues

• 7641: Does it support connecting to existing MySQL or linking to TDSQL
• 5486: Ability to "project" schema at a commit onto data at another commit
• 7630: Add topic 'mariadb' to GitHub repository
• 7621: Migrate dolt gc to SQL
• 2402: Consistent usage of variables in the example
• 2391: Potential regression: number cast to JSON no longer read as float
• 2396: Running multiple migrations in a transaction
• 2397: Primary key column order changes column order on insert

Performance

Merged PRs

dolt

• 7617: go/libraries/doltcore/merge: fulltext_rebuild.go: Add some comments, refactor some things into functions, even if they are still too big.
• 7616: [sqle] don't panic reading history table
  Found an issue with the history table while testing GORM. It's specific to the ComPrepare interface, this doesn't trigger with a simple PREPARE query.

  DEBU[0197] preparing query                               paramsCount=2 query="SELECT column_name, column_default, is_nullable = 'YES', data_type, character_maximum_length, column_type, column_key, extra, column_comment, numeric_precision, numeric_scale , datetime_precision FROM information_schema.columns WHERE table_schema = ? AND table_name = ? ORDER BY ORDINAL_POSITION" statementId=2
  ERRO[0197] mysql_server caught panic:
  interface conversion: sql.Table is *sqle.WritableDoltTable, not *sqle.AlterableDoltTable
  /Users/maxhoffman/go/pkg/mod/github.com/dolthub/[email protected]/sql/planbuilder/parse.go:169 (0x101612ae7)
  com/dolthub/go-mysql-server/sql/planbuilder.(*Builder).BindOnly.func1: panic(r)
  /usr/local/go/src/runtime/panic.go:914 (0x1000d2187)
  gopanic: done = runOpenDeferFrame(d)
  /usr/local/go/src/runtime/iface.go:263 (0x1000a516f)
  panicdottypeE: panic(&TypeAssertionError{iface, have, want, ""})
  /usr/local/go/src/runtime/iface.go:273 (0x1000a5128)
  panicdottypeI: panicdottypeE(t, want, iface)
  /Users/maxhoffman/go/github.com/dolthub/dolt/go/libraries/doltcore/sqle/database.go:357 (0x101a1f1a7)
  com/dolthub/dolt/go/libraries/doltcore/sqle.Database.getTableInsensitive: return NewHistoryTable(baseTable.(*AlterableDoltTable).DoltTable, db.ddb, head), true, nil
  

• 7615: [sqle] Allow bindvars in most function constructors
  This fixes all but the diff_table() function I think. That requires heavier refactoring to support prepared statements.
• 7610: Optimize full-text to selectively rebuild
  This changes the Full-Text rebuild process to only trigger on tables that were changed in both roots (ours and theirs), rather than rebuilding every time a merge is performed regardless of what was actually changed. The rules here are fairly permissive, in that if a parent table or any of the indexed child tables are changed in both, then we rebuild all Full-Text indexes for that table. I chose this behavior out of caution, as dual changes in a single child table is probably due to some kind of renaming, so it's safer to rebuild everything for that table.
• 7609: Drop the nbs.addr type in favor of hash.Hash type
  These two types were essentially the same, with the addition of the Prefix and Suffix methods to hash.Hash they are
  the same.
• 7592: go/store/datas/pull: Restore puller optimization for parallel HasMany calls. Fix crash in pull_chunk_tracker.

go-mysql-server

• 2393: Restored and refactored missing functionality required by Dolt
• 2392: implement sql_mode = 'NO_AUTO_VALUE_ON_ZERO'
  This PR implements the sql_mode NO_AUTO_VALUE_ON_ZERO.
  This makes it so that 0 values (not NULL) do not increment the auto_increment counter.
  MySQL Docs: https://dev.mysql.com/doc/refman/8.3/en/example-auto-increment.html
  fixes https://github.com/dolthub/dolt/issues/7600
• 2390: Remove the NewDefaultServer, which doesn't work for any included session implementation
  Fixes https://github.com/dolthub/go-mysql-server/issues/2364
  Added a memory.NewSessionBuilder method, now used in the example code.
• 2380: Stubbing out support for COM_BINLOG_DUMP_GTID command
  Stubbing out support for two new mysql.Handler methods to support streaming binlog events from a server to a client.
  Depends on Vitess PR: https://github.com/dolthub/vitess/pull/317
  Related to https://github.com/dolthub/dolt/issues/7512

vitess

• 319: make constraint name optional for primary key
  fixes https://github.com/dolthub/dolt/issues/7601
• 317: Port: Support for Vitess server to send binlog events
  Porting over support from the main Vitess repo for a server to send back binlog events over a connection. Also includes support for handling the COM_BINLOG_DUMP_GTID command.
  Related to https://github.com/dolthub/dolt/issues/7512

Closed Issues

• 7604: wikipedia import into mediawiki can consistently produce a Dolt deadlock
• 7600: Specifying an AUTO_INCREMENT primary key results in spurious duplicate primary key given error
• 7602: Conditionally creating a table in a script produces index already exists error

Performance

Merged PRs

dolt

go-mysql-server

• 2387: exit early when IF NOT EXISTS and table exists
  This PR addresses various issues related to CREATE TABLE IF NOT EXISTS ... queries.
  Before, we simply ignored the table exists error, and continued creating indexes, foreign keys, and checks.
  This led to errors when attempting to create indexes/foreign keys/checks that already exists.
  Additionally, it would errorneously create indexes/foreng keys/checks that did exist.
  The correct behavior is to do nothing if IF NOT EXISTS is specified and the table exists.
  Also this contains some refactors and simplifications.
  fixes https://github.com/dolthub/dolt/issues/7602
• 2386: ignore large tokens in fulltext indexes
  We were panicking when attempting to insert/delete tokens that exceed the column type length.
  It appears as though MySQL simple ignores these tokens.
  fixes: https://github.com/dolthub/dolt/issues/7593
• 2385: optimize sql.HashOf
  • pool *xxhash.Digest objects
  • use fmt.Fprintf to write to hash
    benchmark stats

  oos: linux
  goarch: amd64
  pkg: github.com/dolthub/go-mysql-server/sql
  cpu: AMD Ryzen 9 7900 12-Core Processor
  │     b1      │                 b2                  │
  │   sec/op    │    sec/op     vs base               │
  HashOf-24           79.65n ± 4%   70.86n ±  7%  -11.03% (p=0.002 n=6)
  ParallelHashOf-24   10.47n ± 4%   11.85n ± 19%        ~ (p=0.368 n=6)
  geomean             28.88n        28.98n         +0.32%
  │     b1     │                   b2                   │
  │    B/op    │    B/op     vs base                    │
  HashOf-24           4.000 ± 0%   0.000 ± 0%  -100.00% (p=0.002 n=6)
  ParallelHashOf-24   4.000 ± 0%   0.000 ± 0%  -100.00% (p=0.002 n=6)
  geomean             4.000                    ?                      ¹ ²
  ¹ summaries must be >0 to compute geomean
  ² ratios must be >0 to compute geomean
  │     b1     │                   b2                   │
  │ allocs/op  │ allocs/op   vs base                    │
  HashOf-24           2.000 ± 0%   0.000 ± 0%  -100.00% (p=0.002 n=6)
  ParallelHashOf-24   2.000 ± 0%   0.000 ± 0%  -100.00% (p=0.002 n=6)
  geomean             2.000                    ?                      ¹ ²
  ¹ summaries must be >0 to compute geomean
  ² ratios must be >0 to compute geomean
  

• 2383: promote string lookup range types
  When performing range lookups, we convert the key to the type of the column.
  The conversion throws an error when the key doesn't fit within the type for the index.
  The fix is to promote these (only for StringType) so the ranges fit.
  There were issues with type.Promote() for all types.
  Additionally, there are some inconsistencies with MySQL when performing these checks with NUL characters (\0). They are skipped tests for now.
  related https://github.com/dolthub/dolt/issues/7588
• 2382: add support for json_pretty
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html#function_json-pretty
• 2381: [memory] force mutating the editAcc AutoInc because tableEditor is unreliable
  I can't figure a clean way to get the insert editor's edit accumulator and table editor data in sync when a self-referential foreign key initializes the session editor during analysis. So I just forced us to mutate the edit accumulator's auto increment id, which should prevent bugs of the kind we've been seeing. Zach might have a better understanding of how this should work.
  fixes: https://github.com/dolthub/go-mysql-server/issues/2369

vitess

• 319: make constraint name optional for primary key
  fixes https://github.com/dolthub/dolt/issues/7601
• 318: Support for more binlog statements
  Adding parsing support for:
  • show replicas
  • show binary logs
  • show binary log status
• 315: support binary charset in table option
  This PR adds binary as a valid charset option.
  Additionally, this changes CHARSET to expand to synonym CHARACTER SET for parsing in GMS.
  fixes https://github.com/dolthub/dolt/issues/7576

Closed Issues

• 7593: Panic is Wikipedia import cause by a Replace into a table with Full text indexes
• 7602: Conditionally creating a table in a script produces index already exists error
• 7601: One more issue related to the Adobe Commerce ( Magento ) installation.
• 7588: Select statement throwing too large for column error
• 2369: Self-referencing foreign key constraint breaks auto-incrementing ids in memory mode

Performance

Merged PRs

dolt

• 7591: go/store/datas/pull: Revert puller changes from 1.34.0 until we investigate changes in resource utilization.
• 7584: fix comparison for []byte
  convert []bytes to string before comparing them
  companion pr (with tests):
  https://github.com/dolthub/go-mysql-server/pull/2377
  fixes https://github.com/dolthub/dolt/issues/7578
• 7581: fix bad charset tests and dolt table import schema
  recent charset changes exposed some issues with existing tests
  companion: https://github.com/dolthub/go-mysql-server/pull/2376
• 7539: Feature: Schema overriding
  Allows customers to specify a commit, branch, or tag in the @@dolt_schema_override_commit session variable and have all table's data mapped to the schema from that commit, branch, or tag, when queried.

  Example

  As a simple example, consider a database with a main branch that has added the new column birthday to a table, and an olderBranch branch with a table that has not been updated with that schema change. Customers cannot use the same queries from the main branch to query the data on the olderBranch because of the schema difference. Setting a schema override allows the customer to map the table schemas on the olderBranch branch to the same schema as on the main branch. This can be useful when you want to run queries on older data, but don't want to rewrite your queries for older schemas.

  CALL dolt_checkout(‘olderBranch’);
  SELECT name, birthday from people;
  column "birthday" could not be found in any table in scope
  SET @@dolt_schema_override_commit = ‘main’;
  SELECT name, birthday from people;
  +-------+----------+
  | name  | birthday |
  +-------+----------+
  | Sammy | NULL     |
  +-------+----------+
  

  Limitations

  The first version of schema override support is subject to several limitations. Please reach out to us and let us know if you'd like any of these to be prioritized.
  • Read-only – when a schema override has been set, only read queries can be executed. Attempting to write data or execute DDL will result in an error about the database being read-only.
  • System tables – Dolt system tables currently do not honor schema overrides.
  • Collation changes – Collations affect how data is sorted when stored on disk. To map data from one collation to another collation requires extra processing to ensure the returned results are sorted according to the mapped collation. This extra processing is not supported yet, so collation changes will not appear when overriding a schema.
  • Column defaults – If the overridden schema has added new columns with column defaults, those column defaults do not currently get applied when that column is queried. Using the column default value, instead of NULL is a planned enhancement.
    Design doc
    Reference docs update: https://github.com/dolthub/docs/pull/2062
    Fixes: https://github.com/dolthub/dolt/issues/5486

go-mysql-server

• 2382: add support for json_pretty
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html#function_json-pretty
• 2378: map straight join to inner join
  This PR temporarily remaps STRAIGHT_JOIN operator to INNER_JOIN operator.
  fixes https://github.com/dolthub/dolt/issues/7580
• 2377: fix binary cast to maintain length
  We were improperly dropping CAST node during comparison.
  It might be fine in many cases, but it is definitely not for comparing BINARY column types.
• 2376: return errors for charset/collation mismatch
• 2373: charset table option tests
  In GMS, we reparse table options with a regular expression, but we only cover CHARACTER SET and not its synonym CHARSET.
  As a result, we just ignore table options for CHARSET.
  The fix is in https://github.com/dolthub/vitess/pull/315
  TODO: maybe should just address this TODO instead...
• 2372: implement JSON_QUOTE()
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_json-quote
• 2371: don't use all caps for engines info table
  Some clients rely on the correct casing of column names
  For some reason, the casing is different when performing SHOW ENGINES; vs select * from information_schema.engines;
  Fortunately, the way we do SHOW ENGINES; is actually a SELECT ... under the hood.
  So the hacky fix is to just use an alias :)
  related https://github.com/dolthub/dolt/issues/7574
• 2362: Feature: last_insert_uuid() function
  UUIDs are often used in place of auto_increment IDs, but MySQL doesn't provide an easy way to get the last generated UUID that was used in an insert. This change introduces a new function, last_insert_uuid() that operates similarly to last_insert_id(). For a column identified as a UUID column, callers can use last_insert_uuid() to retrieve the last generated UUID value that was inserted into that column. In order to be considered a UUID column, a column must be part of the primary key and it must meet one of the following type signatures:
  • VARCHAR(36) or CHAR(36) with a default value expression of UUID()
  • VARBINARY(16) or BINARY(16) with a default value expression of UUID_to_bin(UUID()) (optionally, the swap_flag for UUID_to_bin may also be specified)

  Example usage:

  create table t (pk binary(16) primary key default (UUID_to_bin(UUID())), c1 varchar(100));
  insert into t (c1) values ("one"), ("two");
  select last_insert_uuid();
  select c1 from t where pk = uuid_to_bin(last_insert_id());
  

  Related to https://github.com/dolthub/dolt/issues/7547

vitess

• 315: support binary charset in table option
  This PR adds binary as a valid charset option.
  Additionally, this changes CHARSET to expand to synonym CHARACTER SET for parsing in GMS.
  fixes https://github.com/dolthub/dolt/issues/7576
• 314: support casting to year

Closed Issues

• 5486: Ability to "project" schema at a commit onto data at another commit
• 7580: Support STRAIGHT_JOIN used by MediaWiki
• 7578: Panic on comparison in Query generated by MediaWiki.
• 7574: MediaWiki needs SHOW ENGINES to return first letter uppercase column names like MySQL
• 7576: default charset=binary is messing up in a create table statement
• 7568: Is too large for column error
• 7547: Feature suggestion: LAST_INSERT_UUID()

Performance

Merged PRs

dolt

• 7553: go/store/datas/pull: Create a PullChunkTracker for keeping track of what to pull.
  The PullChunkTracker is an optimization which can concurrently call HasMany on the destination database while chunks are being pulled from the source database.

go-mysql-server

• 2368: support JSON_MERGE() and JSON_MERGE_PATCH()
  MySQL Docs:
  • https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-merge
  • https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-merge-patch
• 2367: implement JSON_OVERLAPS()
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-overlaps
• 2366: Update README with latest sample code from _example/main.go
  The latest release of go-mysql-server requires a different way of instantiating the database server. Notably, a function that provides sessions must now be provided.
  This change updates our README with the latest working example code from _example/main.go.
  Related to https://github.com/dolthub/go-mysql-server/issues/2364
• 2365: implement random_bytes()
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_random-bytes
• 2362: Feature: last_insert_uuid() function
  UUIDs are often used in place of auto_increment IDs, but MySQL doesn't provide an easy way to get the last generated UUID that was used in an insert. This change introduces a new function, last_insert_uuid() that operates similarly to last_insert_id(). For a column identified as a UUID column, callers can use last_insert_uuid() to retrieve the last generated UUID value that was inserted into that column. In order to be considered a UUID column, a column must be part of the primary key and it must meet one of the following type signatures:
  • VARCHAR(36) or CHAR(36) with a default value expression of UUID()
  • VARBINARY(16) or BINARY(16) with a default value expression of UUID_to_bin(UUID()) (optionally, the swap_flag for UUID_to_bin may also be specified)

  Example usage:

  create table t (pk binary(16) primary key default (UUID_to_bin(UUID())), c1 varchar(100));
  insert into t (c1) values ("one"), ("two");
  select last_insert_uuid();
  select c1 from t where pk = uuid_to_bin(last_insert_id());
  

  Related to https://github.com/dolthub/dolt/issues/7547
• 2361: implement ADDDATE()
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_adddate
• 2360: fix special case for now() string
  The BATS tests import-mysqldump.bats was failing due to a recent string replace case added by https://github.com/dolthub/go-mysql-server/pull/2357.
  I didn't account for when NOW() would be used with other functions inside a DEFAULT column expression, and was replacing the "NOW(" with "CURRENT_TIMESTAMP(". This was causing problems as we reparse ColumnDefaultExprs, resulting in unparenthesized default expressions, which threw an error.
• 2359: unskip fixed tests
  There are some tests that are fixed, but we haven't unskipped them.
  Additionally, this cleans up the way we skip certain tests so that it is more consistent.
• 2357: fix ON UPDATE CURRENT_TIMESTAMP precision
  I had mistaken assumed that MySQL did not allow any precision arguments for NOW() (and synonyms) for column DEFAULT and ON UPDATE expressions. It turns out MySQL only requires that the column type precision match the expression.
  Additionally, we did not perform these error checks for DEFAULT expression, so now we do.
  fixes https://github.com/dolthub/dolt/issues/7555
• 2351: implement JSON_TYPE()
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
  Companion PR:
  https://github.com/dolthub/vitess/pull/314

vitess

• 314: support casting to year
• 313: supports FROM s.account, in which account is non-reserved keyword

Closed Issues

• 7561: "Data length" (i.e., table size on disk) value in DBeaver appears to be wrong
• 7555: Got "Invalid ON UPDATE clause" after bump to v0.18.0
• 3403: Support Shallow Clone
• 6982: Investigating TPCC perf
• 7371: Crash by INNER JOIN
• 2364: Panic when executing sql statements from external command line tool after startup
• 2341: Does it support Functional Key Parts index ?
• 2349: Foreign key constraints break auto-incrementing ids in memory mode
• 1157: show index from table_name doesn't include primary key
• 1340: Hangs on TPC-H Query 1
• 399: sql.FilteredTable usage and implementation
• 514: benchmark memory db
• 807: Documentation of some interfaces and some important concepts
• 1567: navicat connect DB but can not edit
• 2213: UPDATE statements are Noop
• 2215: any plan to build new release?

This release contains backwards incompatible changes:

Dolt CREATE TABLE and ALTER TABLE statements enforce maximum row lengths for schemas. A preexisting schema with columns that sum to a row length greater than the maximum allowable will continue to work in-place, but will need to be migrated to a valid schema before Dolt will accept other schema alterations. A multi-alter statement can change several VARCHAR columns to either (1) shorter VARCHARs or (2) TEXT columns with a serializable maximum length.

Per Dolt’s versioning policy, this is a minor version bump because these changes may impact existing applications. Please reach out to us on GitHub or Discord if you have questions or need help with any of these changes.

Merged PRs

dolt

• 7544: schema validator impl
  Allow alter statements on tables with bad schemas.
  companion: https://github.com/dolthub/go-mysql-server/pull/2348
• 7537: Fixed handler bug related to ExtendedTypes
• 7533: [store] row length guards for prolly serial messages
  Prevent writing rows that exceed the prolly serial message max size. When writing fields we use the current buffer size, the new field length, and the serial message metadata size to validate inserts.
  fixes: https://github.com/dolthub/dolt/issues/7487
  fixes: https://github.com/dolthub/dolt/issues/7524
  companion: https://github.com/dolthub/go-mysql-server/pull/2342
• 7507: [stats]: fix panic for empty table stats with one root chunk
  There's one root chunk for an empty table that needs special handling because there is no histogram bucket for it.

go-mysql-server

• 2350: [planbuilder] Limit allows unsigned ints
  Parameterizing limits in stored procedures exposed a bug where we weren't accepting unsigned ints in LIMIT/OFFSET. This is only possible in prepared or stored procedure contexts afaik.
• 2348: Optional integrator schema validation
  Move table schema validation so that we validate the accumulated result rather than sequential alters.
• 2347: implement json_depth()
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-depth
• 2346: fix null and empty paths for json functions
• 2345: Return correct MySQL error code when inserting into nonexistent columns.
  Fixes https://github.com/dolthub/go-mysql-server/issues/2344
  This PR also adds a test framework for testing MySQL error codes, since this doesn't appear to currently be tested. This should make adding tests for other error codes easy.
  Some of the MySQL error codes that I expected us to use (such as 1050: ERTableExists) have zero usages in GMS or Dolt. We're probably returning 1105: ERUnknownError for these.
• 2343: fix json bool comparison
  JSON Boolean values are special; they do not behave like normal when converted/compared against other types.
  fixes https://github.com/dolthub/dolt/issues/7528
• 2342: Add row length guards
  Tests and basic engine-side guards similar to mysql's behavior.
  re: https://github.com/dolthub/dolt/issues/7524
• 2339: only use crosshashjoin on subqueryaliases
  This fixes 2 sqllogictests.
• 2319: Add net.Listener as server config option
  Presently go-mysql-server is served by a net.Listener backed by a real port or socket. In some environments (like testing) we want to avoid the host's networking stack entirely. This changeset adds support for the caller to provide the net.Listener, which gives them full control over where the sever serves.
  This opens the door for some cool workflows. For example, you can run the server using a buffer-based net.Listener implementation (which is what happens in the test that I added).
  I landed on this solution while thinking through https://github.com/dolthub/go-mysql-server/issues/2314

vitess

• 313: supports FROM s.account, in which account is non-reserved keyword
• 311: add syntax support for SELECT ... INTO OUTFILE ... options
  We had parser support for SELECT ... INTO OUTFILE, but were lacking parser support for the variety of OUTFILE options.
  This PR adds support for all the options and certain combinations of them.
  However, we are missing support for duplicate options and certain orderings of these options; tests for these are added as error tests with a TODO.
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/select-into.html
  MariaDB Docs (this has the actual grammar):
  https://mariadb.com/kb/en/select-into-outfile/

Closed Issues

• 7458: Any chance to get dynamic parameters for user procedures?
• 7524: Large row sizes panic on insert
• 7487: Rows that are too large will panic
• 2349: Foreign key constraints break auto-incrementing ids in memory mode
• 2344: Server error message compatibility when doing INSERT

Performance

Merged PRs

dolt

• 7535: Allow importing tables when the columns contain characters that require escaping.
  dolt table import was previously generating invalid SQL statements for importing these tables.
• 7531: bump sqllogictest version
• 7516: Less docker layers and more compact Docker image
  Install both packages on one Docker layer and delete package cache after installation.
  Docker image size decreased from 259 MB to 210 MB.
• 7514: Bump actions/setup-go and use Go version from go.mod file
  Changes:
  • Bump actions/setup-go
  • Use Go version from go.mod file
  • Move actions/checkout before actions/setup-go

go-mysql-server

• 2343: fix json bool comparison
  JSON Boolean values are special; they do not behave like normal when converted/compared against other types.
  fixes https://github.com/dolthub/dolt/issues/7528
• 2336: fix rangetree removal of largest node without left child
  When removing a right child, a missing nil guard was causing us to improperly propagate the new max range.
  This resulted in overlapping ranges.
  This fixes 1 sqllogictest.
• 2335: Schema pruning considers database name
  Fix some inconsistencies with project and join hints. New test added for new project pruning changes.
• 2334: fix char to bool conversion
  This PR adds missing nil guards in a few conversion methods.
  fixes https://github.com/dolthub/dolt/issues/7515
• 2333: [planbuilder] Skip post-aggregate projection when there are no aliases in target scope
  re: https://github.com/dolthub/dolt/issues/6982
  This takes the last big chunk of CPU pressure off of TPC-C execution, which is mostly disk limited now:
  
• 2332: [time] shortcut for no timezone conversion
  This function reads files from disk to try to convert a timezone to itself. There are other optimizations we could do to maybe try checking for the integer offset before doing syscalls.
  re: https://github.com/dolthub/dolt/issues/6982, ~10% TPC-C boost
• 2331: fix NOT expression in conjunction with WHERE EXISTS(<subquery>)
  The analyzer rule unnest_exists_subqueries was accidentally dropping NOT expressions when hoisting subqueries from WHERE EXISTS... clauses.
  This should fix 8 sqllogictests.
  Correctness: https://github.com/dolthub/dolt/pull/7510
• 2327: memo/planbuilder: Off by one error, and issue importing statistics for testing
  The original bug was a one-off error where conjCollector was checking for the wrong equivalence column. That spiraled into:
  • statistics were not properly being used for some tests because PRIMARY case sensitivity in ANALYZE ... UPDATE HISTOGRAM statements
  • one integration test had a weird edge case where OSX and linux compute slightly differently float values, leading to different plans locally vs CI. One option was to truncate the specificity of all cost values with a multiply/round to avoid numerical weirdness...picking the first lowest plan also seems to work.
  • variety of other small bugs in costing
  • update join bug where the projections after a rearrangement lost expression id/nullability info. This was exposed by the changes above

Closed Issues

• 7528: JSON boolean is string?
• 7491: Use go.etcd.io/bbolt instead of github.com/boltdb/bolt
• 7515: Crash by IF and CHAR

Merged PRs

dolt

go-mysql-server

• 2331: fix NOT expression in conjunction with WHERE EXISTS(<subquery>)
  The analyzer rule unnest_exists_subqueries was accidentally dropping NOT expressions when hoisting subqueries from WHERE EXISTS... clauses.
  This should fix 8 sqllogictests.
  Correctness: https://github.com/dolthub/dolt/pull/7510
• 2330: Fix DISTINCT over DECIMALS
  There was another place where we were using hashstructure package, which does not hash decimal.Decimal types correctly.
  Switched to xxhash package, which is what we use everywhere else.
  Reusing solution from: https://github.com/dolthub/go-mysql-server/pull/2279
  This will fix 1 sqllogictest.
• 2329: expression.Div Micro Benchmarks
  There are going to be changes to our division behavior that impact both its accuracy and speed.
  This PR adds benchmarks to track the runtime improvements/degradations
• 2326: Various Decimal Type Fixes
  This PR has a variety of fixes to have arithmetic operations (especially those involving decimals) behave more like MySQL.
  The logic for the Type() method for Arthmetic and Div is simpler, and better tested.
  When comparing Decimal results from division operations, MySQL has an internal Scale that is different than the Scale used when returning Decimal results for display.
  Here's a matrix displaying the resulting scale:
  (Ex: 1 / 3 = 0.333333333; scale 0 div scale 0 should return scale 9)
  
  Additionally, this PR adds test for arithmetic operations over Datetime and Year types. There are still a some problems dealing with precision and parsing there...
  Note: I believe the division optimization where float division is preferred over decimal division for internal calculations may be causing problems. More testing is needed to see if it's possible to enable this without causing inaccuracies/precision loss.
  There are microbenchmarks measuring the performance of div expression, and it turns out these changes actually greatly improve the runtime.
  Correctness: https://github.com/dolthub/dolt/pull/7484
  Fixes
  • https://github.com/dolthub/dolt/issues/7207
• 2323: SQLEXCEPTION proc handler
  We originally supported one type of procedure handler, NOT FOUND, which explicitly checked for an error when fetching from a cursor io.EOFs. The implementation for that handler would walk the entire BEGIN/END scope stack inside the Fetch call looking for a handler, execute the handler body, and then embed the scope height into a special return error. The error walked back up the callstack looking for the BEGIN/END block embedded in the error message.
  This PR:
  1. Refactors handlers to coordinate message passing in a centralized way. No metadata is embedded in FetchEOF, because each scope will explicitly compare its handlers to errors raised during execution within its BEGIN/END bounds. (FetchEOF is important because we differentiate between 3 different types of io.EOF in procedure loops).
  2. Add type/object support for representing specific signal handers.
  3. Add SQLEXCEPTION signal handling, which will trigger for any error type (other than io.EOF) that bubbles up during a BeginEndIter's execution.
     re: https://github.com/dolthub/dolt/issues/7454
     Another thing I noticed is that some of our tests return nil for empty stored procedures when mysql returns ERROR 1329 (02000): No data - zero rows fetched, selected, or processed. https://github.com/dolthub/dolt/issues/new

Closed Issues

Merged PRs

dolt

• 7494: Fix windows file paths for dropping databases
  We have to convert windows file path separators to unix ones, when removing them from singleton cache.
  Additionally, unskips some bats tests
  Fixes https://github.com/dolthub/dolt/issues/7492
• 7490: Bug fix: IndexedDoltTable didn't fully implement DoltTable's interface
  IndexedDoltTable wasn't fully compatible with DoltTable's interface, which was causing some queries to execute incorrectly due to using the wrong field indexes. This only happened with queries where the table was used in a read-only context (such as an AS OF query), since in a read-write context WritableIndexedDoltTable is used, which is composed of a WritableDoltTable, so fully inherits the interface. IndexedDoltTable is now consistent with that, and is now composed of a DoltTable instance. This enables the GMS code in assignExecIndexes to use the correct, limited schema of the secondary index and to apply the correct field indexes.
  Fixes: https://github.com/dolthub/dolt/issues/7488
• 7486: Shallow Clone
  Add the --depth flag to the dolt_clone stored procedure and the dolt cli
  This is possibly an MVP, as it's kind of impossible to test everything this touches. The guiding principle was to not break the code for any fully cloned repository.
  There currently remain 7 problems to address in this code. They are each marked with "NM4"
• 7482: go: sqle/cluster: When in standby mode, take the epoch of the primary.
  When in detected_broken_config, become standby if we see a higher numbered primary.

Closed Issues

• 7492: Creation of a database with a name identical to a recently dropped-and-dolt_purge_dropped_database() database fails on Windows
• 7488: Field index bug with AS OF query and secondary index
• 7098: decimal result scale does not match MySQL

Auto-refresh is now available (off by default). A controller API for statistics stats, stops, and monitors stats threads. More details here.

Per Dolt’s versioning policy, this is a minor version bump because these changes may impact existing applications. Please reach out to us on GitHub or Discord if you have questions or need help with any of these changes.

Merged PRs

dolt

• 7468: go/libraries/events: Restructure events collection so that we can send events while the dolt process is running.
  We make the GlobalCollector mutable.
  We give the Collector a sendingThread which can have an Emitter. When the Collector collects enough events, it forwards a batch of them to the sendingThread. If the sendingThread has an Emitter, it attempts to send its current batch on that Emitter.
  On shutdown, contexts get canceled, background threads get waited on, and then an accumulated un-emitted events are returned from the Collector, same as previously.
• 7463: SELECT ... INTO OUTFILE ... tests
  This PR adds BATS tests for a SELECT ... INTO OUTFILE ... feature we're adding.
  Companion pr: https://github.com/dolthub/go-mysql-server/pull/2317
• 7457: Added storage capabilities for GMS Extended Types
  This adds the necessary scaffolding to support the new ExtendedType interface within GMS. This interface is primarily used by DoltgreSQL to implement PostgreSQL types.
  Related PRs:
  • https://github.com/dolthub/go-mysql-server/pull/2312
  • https://github.com/dolthub/doltgresql/pull/120
• 7424: Stats auto refresh
  Three new system variables to configure stats auto refresh, one for turning on, one for a timer of how often to check whether stats are fresh, and a third for a freshness threshold to trigger writing new stats. The system variables are global, and will apply to all databases in a server context. Adding or dropping databases, tables, and indexes are reflected by the statistics provider.
  Statistic updates now funnel through a process where we preserve buckets for pre-existing chunks and create new buckets specifically for new chunk ordinals. The auto-refresh threshold compares the number of new and deleted buckets compared the the existing bucket count. So inserts, updates, and deletes all create new chunks or delete chunks relative to the previously saved statistics, and the fraction of (new+deleted)/previous count is compared against the threshold.
  TODO:
  • how much logging is appropriate
  • go server tests
  • is stats dataset replication blocked by default?
    other:
  • ANALYZE table and auto-refresh concurrency, last write wins? as long as auto-refresh thread doesn't die
  • drop database/ANALYZE concurrency, error for writing stats to a deleted folder

go-mysql-server

• 2323: SQLEXCEPTION proc handler
  We originally supported one type of procedure handler, NOT FOUND, which explicitly checked for an error when fetching from a cursor io.EOFs. The implementation for that handler would walk the entire BEGIN/END scope stack inside the Fetch call looking for a handler, execute the handler body, and then embed the scope height into a special return error. The error walked back up the callstack looking for the BEGIN/END block embedded in the error message.
  This PR:
  1. Refactors handlers to coordinate message passing in a centralized way. No metadata is embedded in FetchEOF, because each scope will explicitly compare its handlers to errors raised during execution within its BEGIN/END bounds. (FetchEOF is important because we differentiate between 3 different types of io.EOF in procedure loops).
  2. Add type/object support for representing specific signal handers.
  3. Add SQLEXCEPTION signal handling, which will trigger for any error type (other than io.EOF) that bubbles up during a BeginEndIter's execution.
     re: https://github.com/dolthub/dolt/issues/7454
     Another thing I noticed is that some of our tests return nil for empty stored procedures when mysql returns ERROR 1329 (02000): No data - zero rows fetched, selected, or processed. https://github.com/dolthub/dolt/issues/new
• 2322: Allow InjectedExpr to handle name resolution
  Modifies the InjectedExpr handling to support additional expressions, such as those requiring name resolution.
  Related PRs:
  • https://github.com/dolthub/vitess/pull/312
  • https://github.com/dolthub/doltgresql/pull/121
• 2317: support SELECT ... INTO OUTFILE/DUMPFILE ...
  This adds support for MySQL's SELECT ... INTO OUTFILE/DUMPFILE ... feature.
  It is the complement to LOAD DATA. There is no LOCAL option, so files created using this feature are on the server.
  This PR adds a custom TestSuite for testing these files, as it needs to write, read, and delete files.
  syntax: https://github.com/dolthub/vitess/pull/311
  fixes https://github.com/dolthub/dolt/issues/7453
• 2316: Allow primary keys with auto_increment columns to be dropped when an appropriate index exists
  MySQL allows a primary key with an auto_increment column to be dropped as long as there is a secondary index that includes the auto_increment column as the first column in the index. (MySQL Reference)
  This PR also enables dropping a primary key by referencing it by it's ID (PRIMARY), in order to match MySQL's behavior, e.g.

  DROP INDEX `PRIMARY` ON t;
  

  Related to https://github.com/dolthub/dolt/issues/7456
• 2312: Added the ExtendedType interface
  This adds the ExtendedType interface, which is used within DoltgreSQL to implement PostgreSQL types, as well as in Dolt to properly handle the new type and value serialization.
  Related PRs:
  • https://github.com/dolthub/dolt/pull/7457
  • https://github.com/dolthub/doltgresql/pull/120
• 2300: Fix External Index Creation
  Fixes regression introduced by 9b9301f8c4709d2d32982068322662643e02f231
  Currently

  CREATE INDEX foo USING driver table (column)
  

  creates a table scan if driver is not "", "btree" or "hash".
  The regression is caused by the change:

  -	outScope.node = plan.NewCreateIndex(
  +	createIndex := plan.NewCreateIndex(
  ddl.IndexSpec.ToName.String(),
  table,
  cols,
  ddl.IndexSpec.Using.Lowered(),
  config,
  )
  +	createIndex.Catalog = b.cat
  return
  

vitess

• 312: Allow InjectedExpr to handle name resolution
  Adds the capability for additional expressions that need to be resolved to InjectedExpr.
  Related PRs:
  • https://github.com/dolthub/go-mysql-server/pull/2322
  • https://github.com/dolthub/doltgresql/pull/121
• 311: add syntax support for SELECT ... INTO OUTFILE ... options
  We had parser support for SELECT ... INTO OUTFILE, but were lacking parser support for the variety of OUTFILE options.
  This PR adds support for all the options and certain combinations of them.
  However, we are missing support for duplicate options and certain orderings of these options; tests for these are added as error tests with a TODO.
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/select-into.html
  MariaDB Docs (this has the actual grammar):
  https://mariadb.com/kb/en/select-into-outfile/
• 309: Added a way for Doltgres to inject expressions
  This primarily adds a new AST node that DoltgreSQL takes advantage of. All currently-existing nodes are specifically designed for MySQL transformations. In many cases, Doltgres will have different transformations that need to take place, and this provides a way for Doltgres to implement those transformations without having to modify other packages whenever a new transformation is added or edited.
  Related PRs:
  • https://github.com/dolthub/go-mysql-server/pull/2312
  • https://github.com/dolthub/doltgresql/pull/120
• 308: Change Handler.ComResetConnection to return an error
  Related to https://github.com/dolthub/dolt/issues/3921

Closed Issues

• 7453: Add support for "SELECT INTO" for files
• 7456: Can't drop primary index.
• 3921: Implement COM_RESET_CONNECTION

Latency

This release contains backwards incompatible changes:

• Dolt sql-server now honors the COM_RESET_CONNECTION command from the MySQL protocol. This command instructs the sql-server to clear all session state (e.g. prepared statements, user variables, session variables) and is most often used for connection pooling, to allow a connection's session to be cleared before it is given to another thread to use. This change makes Dolt's sql-server match MySQL's behavior in connection pooled environments, but we are calling this out as a potential backwards incompatible change in case any customers are relying on the previous behavior, such as relying on prepared statements to persist in the session after it is returned to the connection pool.

Per Dolt’s versioning policy, this is a minor version bump because these changes may impact existing applications. Please reach out to us on GitHub or Discord if you have questions or need help with any of these changes.

Merged PRs

dolt

• 7466: go/cmd/dolt/dolt.go: A different way of processing profile-path.
• 7462: Update validation for dropping an auto_increment primary key column
  Allows auto_increment primary key columns to be dropped, so long as there exists a secondary index that covers the auto_increment column.
  Depends on https://github.com/dolthub/go-mysql-server/pull/2316
  Related to https://github.com/dolthub/dolt/issues/7456

go-mysql-server

• 2316: Allow primary keys with auto_increment columns to be dropped when an appropriate index exists
  MySQL allows a primary key with an auto_increment column to be dropped as long as there is a secondary index that includes the auto_increment column as the first column in the index. (MySQL Reference)
  This PR also enables dropping a primary key by referencing it by it's ID (PRIMARY), in order to match MySQL's behavior, e.g.

  DROP INDEX `PRIMARY` ON t;
  

  Related to https://github.com/dolthub/dolt/issues/7456
• 2315: Stored procedures can use params as LIMIT,OFFSET
  fixes: https://github.com/dolthub/dolt/issues/7458
  Fixes procedure param types in the process.
  Testing against mysql it doesn't seem like subqueries or regular column types are valid as LIMIT values in any case other than procedure parameters. I still need to test whether trigger columns can be used in LIMIT,OFFSET, but that seems like a separate problem.
• 2311: fix group by ... having ... aliasing once more
  This PR simplifies the GROUP BY and HAVING aliasing rules to much more closely match MySQL's weirdness.
  TODO: Go through various group by aliasing issues and link fixed ones here
  correctness: https://github.com/dolthub/dolt/pull/7455
• 2310: Implement COM_RESET_CONNECTION
  Implements the COM_RESET_CONNECTION command to allow a sql-server to clear session state on a connection so that it can be safely reused, for example, in connection pools.
  This matches MySQL's behavior, but customers may see a behavior change from this if they are relying on the old behavior. For example, if customers are relying on prepared statements to remain available in a session after it has been released to a connection pool and then reassigned to a new worker thread. The behavior now matches MySQL, where prepared statements, session vars, and user vars are all reset on a connection when the COM_RESET_CONNECTION command is executed on a connection.
  Depends on https://github.com/dolthub/vitess/pull/308
  Related to https://github.com/dolthub/dolt/issues/3921
• 2308: fix decimal precision for division
  divScale tracks the number of division operators on the leftSubtree. It can be used to determine if the current expression is the outermost Div expression, and doubles as a way to determine how many decimal places of precision we need to add.
  opScale tracks the total number of Arithmetic operators in the expression tree in total. It is used to identify the outermost Arithmetic expression.
  There were a few places where we were mixing up divScale and opScale, leading to improper rounding; this PR fixes that.
  As a result, we are able to properly round decimal results when the outermost operator is not a Div.
  Additionally, this PR also contains a fix for functions that return float64 being incorrectly converted to decimal.
  correctness: https://github.com/dolthub/dolt/pull/7442
  fixes https://github.com/dolthub/dolt/issues/4931
• 2307: Persist table comment from create table
  We parse table comments, but they were not persisted anywhere. This change adds support for persisting table comments so that they can be round-tripped between create table and show create table.
  There are still several limitations with table comments that we should eventually follow up on:
  • Table comments containing any single quotes are not supported – Vitess parses the table options, but then strips any quotes and replaces them with single quotes, then pushes all table options back into a single string that GMS parses again, using regexes to pull out charset, collation, and comment. If a table comment is double quoted and contains single quotes in the string, Vitess doesn't currently preserve enough information for us to reparse that correctly in GMS. The right fix for this is to change Vitess to return table options in a structure, instead of as a single string.
  • ALTER TABLE does not support altering a table's comment.
    Related to https://github.com/dolthub/dolt/issues/7416

vitess

• 310: Limit/offset proc param
  re: https://github.com/dolthub/dolt/issues/7458
• 307: allow hexnums for stats_auto_recalc table option
  This fixes 1 sqllogictest

Closed Issues

• 7435: Make phone home behavior opt-in
• 7384: Can't add multiple foreign keys in CREATE TABLE
• 7458: Any chance to get dynamic parameters for user procedures?
• 4931: incorrect decimal scale if division is used, but it is not the last operation
• 7448: dolt push may be overly aggressive in its fast-forward check.
• 2314: Can't turn on multiStatements when using memory driver

Latency

Merged PRs

dolt

• 7450: push should ignore tracking branch

  Fix for: https://github.com/dolthub/dolt/issues/7448

• 7443: add disable version check warning
  Adds a message to provide instructions on how to disable the version out-of-date check. This message will print once per version.
  Related: https://github.com/dolthub/dolt/issues/7435

• 7441: Persist table comment from create table
  We already parse table comments, but they were not persisted anywhere. This change adds support for persisting table comments so that they can be round-tripped between create table and show create table.
  In addition to the follow ups in the GMS layer (mentioned in https://github.com/dolthub/go-mysql-server/pull/2307), there is one follow up in the dolt layer to update Dolt's merge logic to merge table comment changes.
  Depends on https://github.com/dolthub/go-mysql-server/pull/2307
  Related to https://github.com/dolthub/dolt/issues/7416

go-mysql-server

• 2306: Bug fix: honor database qualifier on view name for create view statements
  Related to https://github.com/dolthub/dolt/issues/7438
• 2304: implementing json_keys() function
  This PR adds support for JSON_KEYS().
  MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-keys

Closed Issues

• 7416: add table create comment to table spec
• 7438: Issue creating views across databases

Merged PRs

dolt

• 7432: dolt_clone and --user
  Previously the dolt_clone() stored procedure would take the --user flag, but not use it in a gRPC call to a sql-server. This
  addresses this limitation, and adds a test to verify it works.
• 7426: fix sqllogictest harness to use float rounding
  When we generated the results for MySQL (specifically the float results), we used the golang fmt.Sprintf('%.3f') string, even for decimal types. Meanwhile, the doltharness was using decimal.Round(). There's evidently a difference in the way these two rounding operations work, so we had some failures due to rounding errors.
  This PR makes it so decimals are rounded the same way floats are, so that results are consistent.

go-mysql-server

• 2306: Bug fix: honor database qualifier on view name for create view statements
  Related to https://github.com/dolthub/dolt/issues/7438
• 2305: [fixidx] Table function expression ids
  Simplify and fix plan.TableAlias indexing. Some table alias children have their own expression ids, but sql.TableFunction implementations don't necessarily extend the plan.TableIdNode interface and rely on the analyzer to populate column ids. There are a couple ways to simplify this in the future, like adding an intermediate prunable sql.Projector node for table functions, or having pruning clean up after itself by updating node and parent table alias columns.
  TODO: this case is kind of hard to test, but trying to come up with something.
• 2303: float64 is larger type than decimal for coalesce() function
  This causes 937 failures in sqllogictests
• 2302: adding @@server_uuid system variable
  This PR adds support for the @@server_uuid system variable.
  However, this just generates a random UUID on server start, and does not persist it to a file.
  MySQL Docs:
  https://dev.mysql.com/doc/refman/8.0/en/replication-options.html#sysvar_server_uuid
  Fixes https://github.com/dolthub/dolt/issues/7431
• 2301: fix coalesce type()
  Although only the first argument is returned, coalesce still examines the types of all the arguments passed in, and uses the "largest" type.
• 2298: fix outermost division placement
  MySQL round appends 4 places of precision to their division operators, but only on the very last division operation.
  All inner divisions need to preserve as much precision as possible. To track this, we have a divScale variable in.
  We also have an opScale to track if an operation is the outermost operator, for other rounding/precision reasons.
  We were getting these values mixed up, leading to results with incorrect precision.
• 2295: allow tables with self referential foreign keys to be dropped
  Our logic for preventing DROPs on a table if that table was referenced in a FOREIGN KEY did not account for when the table was self referencing. This PR fixes that and closes the gap in our testing for this area.
  fixes https://github.com/dolthub/dolt/issues/7418
• 2292: Various aggregation/indexing fixes
  re: https://github.com/dolthub/go-mysql-server/pull/2271
  Use expression ids to fix expression indexes most of the time. This makes it easier to resolve definitions once in planbuilder, rather than having to redo all that work a second time during assignExecIndexes. This should be more reliable for most queries and make it easier to make alias resolving refactors.
  Many expressions now implement sql.IdExpression and absorb unique expression ids. Trigger variables and stored procedure params still lack ids and fallback to string matching behavior.
  The biggest lingering issue relates to how dual tables, aliases, and subquery expressions interact. Dual tables have a schema with one column named with an empty string. Subquery expressions expect the +1 column offset from a dual table parent schema (if I'm reading correctly, it might depend on other context). On the other hand, that column can make it difficult to deduplicate projections and throw off execution indexing. There is one query that I haven't been able to get working that has a combination of dual table and alias issues that I think needs a heavier lift to manage correctly.

  SELECT
  "testing" AS s,
  (SELECT max(i)
  FROM (SELECT * FROM mytable) mytable
  RIGHT JOIN
  ((SELECT i2, s2 FROM othertable ORDER BY i2 ASC)
  UNION ALL
  SELECT CAST(4 AS SIGNED) AS i2, "not found" AS s2 FROM DUAL) othertable
  ON i2 = i) AS rj
  FROM DUAL`
  

  This query splits the target projection into two levels to account for the dual table column and alias dependency. One thing that does fix this is inlining the alias reference to avoid computing the rj subquery a second time in the second alias. But that alias replacement breaks some of the TestOrderByGroupBy tests that also have alias issues.

vitess

• 307: allow hexnums for stats_auto_recalc table option
  This fixes 1 sqllogictest
• 306: allow function keywords
  We have some extra keywords (they aren't listed as reserved or non-reserved in MySQL), to handle the special syntax.
  These keywords are allowed as table and column names without backquotes.
• 305: Allow UNIQUE and PRIMARY KEY to be specified on the same column
  Related to: https://github.com/dolthub/dolt/issues/7395

Closed Issues

• 7388: Cannot use column names containing spaces or uppercase characters as a generated primary key
• 7428: Feature request for supporting health and readiness checks in Dolt DB
• 7431: @@server_uuid generated by mysqlsh. Dolt does not support that system variable.
• 7417: Allow custom commit messages using @@dolt_transaction_commit = 1
• 2296: add create table comment on table clause to table spec
• 2225: go mod tidy error

Latency