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 #7634
  This is the dolt half of dolthub/go-mysql-server#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: #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 #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 #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: #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 #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: dolthub/go-mysql-server#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 #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