Merged PRs

dolt

• 9437: Match quoted and unquoted check definitions during merge. When resolving check constraints, we perform string comparison against a check's definition in the schema and from the create table statement. Since updating the check constraints to include backquotes (in this PR https://github.com/dolthub/go-mysql-server/pull/3040), it's possible older check constraint definitions do not have backquotes. This PR makes it so the check considered the case where backquotes are missing and adds a backwards compatibility test.
• 9419: Add indexes to dolt_commit_diff_ tables that include the primary key of the underlying table The dolt_commit_diff_ system tables allow for comparing a table at two different commits. They require a filter on the to_commit and from_commit fields, which the table exposes as an index. This PR extends those indexes to have additional columns based on the primary keys of the underlying table. This allows a user to efficiently compare only a specific range of that table. Specifically, if the table's primary key columns are named pk1, pk2, ... pkN, then the system table now provides two indexes:
• to_commit, from_commit, from_pk1, from_pk2... from_pkN
• to_commit, from_commit, to_pk1, to_pk2... to_pkN If the select filter uses a prefix of either of these indexes, then we can implement the select as a Diff on only the intended range. Because the to_ columns are NULL for removed rows, and the from_ columns are NULL for inserted rows, a filter on the from_ columns will only return modifications and deletions, and a filter on the to_ columns will only return modifications and additions. In hindsight, perhaps the primary key rows should have appeared in the diff as a single column instead of both a to_ and from_ column, but that ship has sailed. As for the implementation, I don't love the fact that code paths that diff the entire table without accepting a range pass around a zero-valued sql.Lookup, which we then detect by checking to see if lookup.index == nil. It's a code smell for sure. But I couldn't think of a better way to do it.
• 9411: go: sqle/statspro: Rework the way stats manages its session in order to make it safe to continue collecting stats during a GC run. Previously GC, even with the session-aware safepoint controller, such as is used with Auto GC, would cancel any ongoing stats work and would restart it at the end of the run. Because Auto GC can run quite frequently and because stats can take a while to run to completion, this meant that on some workloads stats would never successfully populate. This PR changes stats so that it more correctly integrates with the session-safe GC safepoint controller. That allows GC to ensure that the stuff stats is currently working on gets carried over to the collected view of database, and stats itself has a chance to run to completion regardless of what GC work is going on. This PR leaves the cancel-stats-on-collect behavior enabled for the kill-connections safepoint controller, where that behavior is still the correct one.
• 9402: Fix 9391 test for max1row err Fixes [#9391] Add dolt_commit_ancestors table query test
• 9401: Renaming default branch results in updating local config. Update the init.defaultBranch config when renaming the default branch. Fixes: https://github.com/dolthub/dolt/issues/8039
• 9400: Fix 9390, 9391 max1Row iterator returned more than one row Fixes [#9390] Fixes [#9391] Dolt history table indexes are never marked as unique, regardless of the underlying table’s index properties: prevents unique constraints from being incorrectly reported or exported for history tables
• 9399: go/libraries/doltcore/remotestorage: chunk_store.go: Fix a bug where we did not refresh expired table file URLs appropriately.
• 9398: dolt_rm stored procedure For issue [#9330] Implements a dolt_rm() stored procedure:
• Remove tables from the database and from tracking
• --cached option to untrack tables, but leave them in the database.
• 9396: Implement dolt_diff_dolt_procedures Partial fix for [#9373]
• 9395: Implement dolt_history_dolt_procedures Partially fixes: [#9373]
• 9389: Archive cmd improvements This change has a grab bag of improvements from working with media_wiki database. 1) All table files, not just oldgen, and converted to archives. 2) Updates to the DB are performed incrementally. Each table file conversion results in a swap of tables, allowing for large job termination and not losing all progress 3) Compression workers are parallelized. 4) Revert is incremental, and works on newgen.
• 9387: archive format 3. Supporting larger indexes The media_wiki database has a 900Gb table file, which fails to convert to an archive because the index size greatly exceeds the size of an uint32. This change extends to a uint64.

go-mysql-server

• 3061: Fix [#9428]: Allow default keyword as value for generated columns. 🤖 Generated with Claude Code Fix [#9428] Allows INSERT INTO table VALUES (default) to work with generated columns
• 3060: Merge scopeColumn types in SetOp scope fixes [#9024] Previously, the scopeColumn type defaulted to that of the corresponding scopeColumn in the left scope. This would cause a type conversion error if the left scopeColumn was a less general type than the corresponding right scopeColumn. The merged scopeColumn is also made nullable if one of the merging columns is nullable. GeneralizeTypes also checks if two types are already equal. This prevents DolgresTypes from always being generalized as a LongText.
• 3058: Add variety of Enum tests This PR adds a bunch of tests for the Enum type.
• 3057: Changing how NO_MERGE_JOIN hint is applied to fix a panic The join planning hint system previously generated all join combinations, then relied on a set of "blocked" join ops to skip over plans with MergeJoin ops. This resulted in some plans panic'ing when this option was set, because the code generally assumes that each expression group will have a non-nil Best member set once the first expression in the group has been evaluated. However, for MergeJoin, this assignment would be skipped and Best would be nil, which causes a panic in some plans when the NO_MERGE_JOIN hint (or the @@disable_merge_join system variable) was enabled. This new logic skips ever adding the MergeJoin ops to the memo, which reduces time spent planning/costing the join, and also avoids breaking the assumption that Best will always be populated after the first expression in an expression group is costed. Also cleans up a few other small issues in the code, such as renaming dbSube() to dpEnumerateSubsets().
• 3056: Revert "Merge pull request [#3046] from dolthub/macneale4-claude/query-ok" This reverts commit 0828810fe6f752c4d2ac6eed320be028fab7ed19, reversing changes made to 22622a0de59153903b233ce9839292abc6b6f878.
• 3053: moved keyless subquery test to correct place move test added in [#3052]
• 3052: Use IsJoin flag to check if keyless tables are actually allowed fixes [#9334] Makes clear that IsJoin flag is only for explicit UPDATE JOIN statements. Update nodes with subqueries that have been optimized into an UpdateJoin node would have a false IsJoin.
• 3051: Fixes 7201 non json data err comparison errs Fixes [#7201] Add tests since issue seems already fixed
• 3050: fix exists subqueries in stored procedures Missed a case for ExistsSubquery statements when replacing variables in Stored Procedures. fixes: https://github.com/dolthub/dolt/issues/9409
• 3049: Fix 7300 date time overflow underflow Fixes [#7300] Update interval apply() to use go built-in time package Update Date, Datetime, Timestamp range to match MYSQL docs Add new format for times Increase accuracy of time fractionals in formats but round to microsecond precision
• 3048: Added field to ScriptTest to skip test
• 3047: Detect when one index is always better than another. I observed an issue with a table with two indexes that would always pick the worse one. The indexes were non-unique, had nullable columns, and the lookups were partial, so there was very little information for the analyzer to go off of. However, one index had added strictly more constraints than the other: create table test( pk int primary key, shared1 int, shared2 int, a3 int, a4 int, b3 int, b4 int, key a_idx(shared1, shared2, a3, a4), key b_idx(shared1, shared2, b3, b4) ); A SELECT query on the above table with filters on shared1, shared2, and a3 would use b_idx, while a query on the above table with filters on shared1, shared2, and b3 would use a_idx, the opposite of what was expected. Even though neither index can guarantee a single result, using the index that can apply the additional filter is obviously strictly better. This seemed to be happening due to a special case that occurred when an index could be used to match every part of an AND expression: the rest of the analysis would treat the entire AND expression a single matched filter instead of tracking the individual sub-expressions. This makes a certain amount of logical sense, but I don't think it actually serves any benefit, and very easily leads to situations like the above where an index that matches all three clauses (and gets counted as one matched filter) gets considered worse than an index that only matches two of the clauses (which counts as two matched filters) But this wasn't enough to fix the issue, because in some cases the analyzer would still pick the worse index. So this PR adds an extra rule to always prefer an index that uses a superset of filters in its prefix than the other. This seems like it should always be correct, unless I'm missing something.
• 3046: Fix SET statements to return OkResult instead of empty rows SET statements (system variables, user variables, and transaction settings) now return MySQL-compatible OkResult instead of empty rows, enabling proper "Query OK, 0 rows affected" confirmation messages in SQL clients.
• 3045: Fix 9280 escape ctrl z comment strs Fixes [#9280] Update query tests for \Z escape character
• 3043: fix missed case for nullable enums This PR adds a missed edge case from this: https://github.com/dolthub/go-mysql-server/pull/2985 We were incorrectly making nullable enum columns take the first value.
• 3042: Apply multiple triggers to UPDATE JOIN Fixes [#9356] and [#5400] Encountered [#9403] (added bug detection and errors out)
• 3041: fix group_concat with order by subquery clauses group_concat does something special and appends result to the outer scope's row. As a result, we need to account for that while assigning indexes. fixes: https://github.com/dolthub/dolt/issues/9302

Closed Issues

• 9024: subqueries should coerce union types
• 9428: Allow default keyword as value to column with generated constraint
• 7300: Date and time functions should check for overflow/underflow
• 9356: Fix Update Join support for foreign keys and triggers
• 7201: JSON values containing non-JSON data (datetime, decimal, etc) cause error when used in <=> comparisons
• 9334: Update with subquery in keyless tables
• 9409: Stored procedure argument is not accessible in EXISTS subquery
• 9302: ORDER BY clause in group_concat doesn't work with subqueries
• 9390: SELECT * FROM dolt_history_{{ table_name }} filter errors with result max1Row iterator returned more than one row
• 9280: Escape Control+Z in comment strings
• 9373: Implement dolt_history_dolt_procedures and dolt_diff_dolt_procedures
• 8039: Changing the default branch name should be reflected in the provider/config
• 9366: REGEXP performance regression
• 9391: SELECT * FROM dolt_commit_ancestors filter errors with result max1Row iterator returned more than one row