[9.0] New translations and SQL optimization/simplification

[roji]

Note: consider work went into Cosmos querying capabilities, and so the below doesn't include those improvements; see #33033 for Cosmos work done in 9.0, including all query improvements.

SQL optimizations

• Redo our pruning implementation #31083
  • Prune OPENJSON columns list #32668
• Clean table alias generation and generate unique aliases up-front #32784
  • Query contains non-consecutive table aliases when tables are pruned #32757
• Fixes and improvements to StartsWith/EndsWith/Contains #31482
• Min/Max (GREATEST/LEAST) work
  • SQL Server: Translate Math.Max/Min in non-aggregate context #27794
  • Translate to GREATEST/LEAST in relational #31681
  • Translate Min/Max over inline collection via LEAST/GREATEST #32332
  • LEAST/GREATEST nullability work #32458
• Simplify NOT #34142
• Simplify AND and OR #34133
• Optimization of CASE/WHEN
  • Query: optimize nested CASE blocks #18774
  • Fix optimization of CASE op WHEN #33869
  • Query: optimize CASE statement - remove conditions that are never hit #18935
  • Simplify NOT (CASE ... THEN const) by pushing NOT into the THEN/ELSE #33934
• Optimization of COALESCE
  • Optimize away Coalesce for trivial cases #34002
  • Simplify COALESCE based on the nullability of its arguments #33938
  • Propagate allowOptimizedExpansion to CASE results #34304
• Translate logical Boolean operators as conditional Boolean operators #33873
• Implement nullability computation for SqlFunctionExpression #33814
• Simplify TRUE = expr in filtering contexts #33776
• Optimize away DISTINCT inside IN/EXISTS/set operations #34381

New translations

• GroupBy over complex types #33491
• Allow passing complex type instances to ExecuteUpdate #32058
• Translate ToString() over enums #33706
• Query: Bitwise Ex-Or support #16645
• SQL Server: Translate Math.Max/Min in non-aggregate context #27794
• SQL Server: translate non-aggregate string.Join to CONCAT_WS #28899
• SQL Server: translation for the PATINDEX function #33702
• SQL Server: add support for object in SqlServerConvertTranslator #33891
• SQLite: implement sum and average aggregation for decimal #33721

Provider-specific

• SQL Server: Rewrite query to support aggregate functions over aggregates/subqueries #34256
• SQL Server: use XOR to translate some == and != expressions #34124
• SQL Server: Use XOR to translate some NOT expressions #34080
• SQL Server: Use XOR to translate more == and != expressions #34168
• SQL Server: support bitwise NOT on boolean values #34213

A big thank you goes out to @ranma42 for many of the above!

[roji]

@AndriySvyryd I'm reopening this just to remember to finish updating this list - many improvements are missing from the above list.

We can filter out composite issues when looking at what's left for the release.