Merged PRs

dolt

• 6637: Bug fix: Check constraint merging
  We had a typo in the check constraint merging code that was only triggered when an existing check constraint was altered on the "source" side of the merge and the "destination" side of the merge had the exact same definition as the common ancestor. The new test shows a concrete example. Note that the schema merge tests run in both directions, from left to right, and from left to right, so they are better at catching bugs like this that only appear in one merge direction.
• 6631: go: sqle: async replication: Fix a bug in the logic which decides what to push to stop pushing deleted branches after we push them once.
• 6630: go: sqle/cluster: mysqldb_persister.go: Add exponential backoff to users and grants replication to avoid spamming logs.
• 6622: allows dolt log outside dolt repos
  Allows dolt log to be called outside of dolt repos
• 6621: fixes profiles not working with multiple profiles added
  Fixes an issue with adding and using multiple profiles

go-mysql-server

• 1994: Fixes for subquery indexing/correlation tracking
  I missed a place where we were using getField indexes to do a correlation check. This adds the changes necessary to replace that check with one that uses a subquery's tracked correlation column set. This also adds an expression id column to GetField expression that preserves the id tracking information between iterative passes of the analyzer. It would probably be preferable to avoid all cases where we unnecessarily re-run rules, but this is more near at hand.
• 1993: JSON Array Mutation
  The json_array_insert() and json_array_append() methods are in the family of other mutation functions (set,insert,replace,remove).
  • JSON_ARRAY_APPEND()
  • JSON_ARRAY_INSERT()
    Related: #6585
• 1992: Delete fixidx from pushdown
  Summary:
  • delete fixidx usages in pushdownFilters and generateIndexScans
  • use a mapping on subquery aliases when pushing filters through subqueries
  • better max1Row memo now
  • misc changes to re-indexing to compensate for work previously done during pushdown
• 1991: Fix show processlist panic
  close #6625
• 1990: Cast limit/offset to int type for prepared path
  fixes: #6610
• 1988: JSON Mutation functions implemented as stored procedures
  Earlier PR (dolthub/go-mysql-server#1983) enabled us to modify json document objects, but none of that functionality was exposed as actual JSON_* functions. This change ties it together. The following functions will behave identically to MySQL (to the best of my knowledge).
  • JSON_SET
  • JSON_INSERT
  • JSON_REPLACE
  • JSON_REMOVE
    Related: #6585
• 1987: Move JSON Functions into their own sub directory
  Purely mechanical refactor done by the IDE. This is in preparation for adding support for several more JSON functions.
• 1986: When pushing down filters, ensure removal of original filter.
  In cases where the filter expression changed during push down because the column IDs changed, we were accidentally checking for expressions with the new column IDs, not the old ones, so the old filter expressions weren't being removed.
• 1985: Fix panic in merge join when using custom Indexes that don't allow range lookups.
  For instance, dolt has Commit indexes for tables that use commit hash as an index, but ranges don't make sense for those.
  There's no equivalent in GMS, so I created "point_lookup_table" table function for use in tests.
• 1984: Partially reorder indexing rules; use unique ids rather than execution ids
  Name binding stores caching information upfront. Rule interdependencies pushed me into fixing a bunch of other rules before tests would pass. All together I think most of the changes are simplifications that I was planning on doing related to the fixidx refactor. I was hoping to make it more piecemeal. Hopefully this gets us ~50% of the way towards removing those dependencies.
  fixidx is mostly contained to reorderJoins and fixAuxiliaryExpressions now, both near the end of analysis. If we move the indexing in reorderJoins into fixAuxiliaryExpressions, all indexing will happen at the end of analysis. That would let us index complicated joins with subqueries correctly and all queries more reliably.
  summary:
  • rewrite cacheability to use correlated column references
  • volatile functions now prevent caching
  • rewrite moveFiltersOutOfJoinConditions to put filters below join when appropriate
  • subquery decorrelation uses (and updates) correlated column references
  • alias subquery strings simplified to use the query string, not the plan string
  • fix jsonTable and lateral join analysis
  • fixAuxiliaryExpresssions at end of analysis
  • recursive analyzer rules (insert, trigger, procedure) are all at end of analysis now
• 1983: JSON Mutation
  Add JSON Value mutation operations and tests. These changes do not alter the operation of dolt in anyway yet - that will come in a second PR which updates the JSON_SET procedure, and adds support for the JSON_REPLACE, JSON_INSERT, and JSON_REMOVE procedures at the same time. This is laying the foundation for that work.
• 1982: Inline flatten aliases
  This will skip a tree walk for most queries, inlining the rule in the places where nested table aliases can occur during binding.
• 1981: Make the IntSequence test function consistently use int64 for its generated values
  The previous implementation had an issue where it assumed the type used in the received IndexLookup, but this type can actually depend on exactly how the lookup was generated (and whether the bounds value was parsed from the query or generated internally.) This caused a panic if it was used in Lookup joins.
  This makes no such assumptions and adds extra tests.
• 1934: New Merge Join planner
  This should fix #6020 once finished, and then some.
  The killer new feature in this new join planner is "Multi-Column Merge Joins", that is, merges where the comparison used for merges incorporates multiple filter conditions. This allows us to, in some cases, choose a much more selective index for merge joins. This improves both memory usage and performance because there will be fewer cases where the join iterator needs to keep multiple secondary rows in memory and cross-join them with multiple primary rows.
  The algorithm goes like this:
  • For each index on the left table:
  • Compute the max set of filter expressions that match that index
  • Check to see if any indexes on the right table match that same set of filters in the same order.
  • If so, use this set of filter expressions to generate a Merge Join plan. If there are multiple expressions, we combine them into a comparison on tuples.
  • Remove the last filter expression and check again; repeating until the "matched filters" list is empty.
    I added a test in join_planning_tests that demonstrates the potential of this new algorithm, allowing us to select a better index that otherwise allowed.

Closed Issues

• 6633: DBeaver loses connection after calling DOLT_GC()
• 6536: Dolt performance comparison to postgres and mysql
• 6625: SHOW FULL PROCESSLIST; causes panic
• 6610: invalid type: double since 1.13.5

Latency