Merged PRs

dolt

• 9813: /go/cmd/dolt/commands/sqlserver/sqlserver.go: remove incorrect help text line from --mcp-password option.

go-mysql-server

• 3206: fix equality check in buildSingleLookupPlan()
  A join optimization to generate look up plans was incorrectly being applied to filters that were not simple equalities.
  This resulted in filters getting dropped and incorrect results.
  fixes: #9803
• 3205: Relax restriction that was preventing us from caching the result of subqueries.
  dolthub/go-mysql-server#1470 was supposed to, among other things, add restrictions to when we generate CachedResults nodes to cache subquery results. However, the added check is overly broad, and as a result it became impossible to actually cache subquery results. Currently, there is not a single plan test that contains a CachedResults node in the output plan.
  The cacheSubqueryAliasesInJoins function has a comment:

  //The left-most child of a join root is an exception that cannot be cached.

  No rationale is given for this. Looking at it, it seems like we used to generate CachedResults nodes before we finished resolving references in the query, and now we wait until after. So it's possible that this is the reason for the restriction, and the entire check is no longer necessary.
  Either way, the implementation is more restrictive than the comment would suggest. Due to how the algorithm tracks state via function parameters, it doesn't propagate state from a child node to it's parents/siblings, and the flag for recording that it's encountered a subqeury gets unset. As a result, no Subqueries will actually be cached.
  This PR fixes the tree walk to correctly remember once it's encountered a subquery and allow subsequent subqueries to be cached. This is still more broad than the comment would suggest, since it doesn't require that this subquery appear in the left-most child of the join.
  Among the changed plan tests, we see that CachedResults nodes are now emitted. Most of them are the children of HashLookups, but there are some that are the children of InnerJoin and SemiJoin nodes where one of the things being joined is a subquery.
• 3200: implement double truncation and find correct hash comparison type
  Changes:
  • Fix HashLookup to find a compatible comparison type to convert left and right values to before hashing. We used to always pick the left type, which led to inconsistencies with MySQL
  • Implement Double value truncation, so incorrect double values are still partially converted to float64.
    Fixes: #9799
• 3190: Refactorings to support index scans for pg catalog tables
  This PR adds new interfaces to allow different expressions types to participate in the standard index costing process, which results in a RangeCollection. Also plumbs additional type info through so that types with a more precise conversion process can participate as well.

Closed Issues

• 9799: String-Boolean comparison in JOIN queries returns incorrect results
• 9803: JOIN returns incorrect results when NULL compared against outer table expression