Aggregation queries fail across entity references

Problem/Motivation

With field paths such as entity_id.0.entity:node.created_at it's possible to create queries across entity references which allows to create joins based on related data. However, when generating aggregated queries that use an aggregate condition, groupBy or sortAggregate based on such a field then an invalid query will be created. This happens because QueryBase::getAggregationAlias<code> doesn't escape the <code>: and . characters.

Steps to reproduce

I'm not sure how to reduce this to a small case based on Nodes/Comments, so a query that can easily be executed by others is difficult, but internallly we have something like.

// Find conversations for the current user where they've received a message 
// and sort the conversations by the most recently sent messages.
\Drupal::entityTypeManager()->getStorage('chat_message_receipt')
  ->getAggregateQuery()
  ->condition('recipient', \Drupal::currentUser()->id())
  ->groupBy('message.0.entity:chat_message.conversation')
  ->sortAggregate('message.0.entity:chat_message.sent_at', 'MAX', 'DESC')
  ->execute();

Proposed resolution

Replace the implementation of QueryBase::getAggregationAlias with

    return strtolower(str_replace([':', '.'], '__', $field) . '_' . $function);

The generated alias shows up in the output and may be used for field selection. However, this change should be backwards compatible since current queries cannot contain these characters due to this bug and no replacement happens for any queries that don't currently contain these characters, so their alias will remain stable.

Remaining tasks

User interface changes

API changes

Data model changes

Release notes snippet

Entity reference fields in aggregate conditions, sort clauses, or group by statements, will no longer cause an invalid SQL query to be created.