Nested condition in query not working when basefield

Problem/Motivation

When entity_reference_revisions is used as basefield (see EntityTestHostRelationship) it creates edge case bug where nested condition (such as field_reffield.entity.id) in entity query fails. With non-base field the condition works just fine.

Steps to reproduce

Enable entity_host_relationship_test and run following snippet:

$entityTypeManager = \Drupal::entityTypeManager();
$storage = $entityTypeManager->getStorage('entity_host_relationship_test');

$query = $storage->getQuery()
  ->accessCheck(FALSE)
  ->condition('entity', NULL, '<>')
  ->execute();
dump($query); // Works just fine.

$query = $storage->getQuery()
  ->accessCheck(FALSE)
  ->condition('entity.entity.id', NULL, '<>')
  ->execute();
dump($query); // Fails.

Note that in the error entity refers to the field name

In Tables.php line 432:
                      
  'entity' not found                        

Proposed resolution

This may or may not be related or duplicated by the core issue https://www.drupal.org/project/drupal/issues/3025164 →

Ideally the queries would work regardless if the field is basefield or not.