Impossible to query same field in different languages

Problem/Motivation

When you have translatable entity reference field and want to check that specific referenced entity is not used in other translations so you can delete it.

Example data state:

Configured languages: Japanese, German, English.

Let's say I wan't remove reference 10000001 from new EN translation, but I wan't be sure that it's not used in other translations so I can delete it.

I will run a query:

$entity_id = 6590308;
$entity_type = 'node';
$entity = Node::load($entity_id);

// IDs of referenced entities.
$ids = [10000001];
$field_name = 'field_tags';


$storage = $this->entityTypeManager->getStorage($entity_type);
$query = $storage->getQuery();
$query
  ->allRevisions()
  ->condition($entity->getEntityType()->getKey('id'), $entity->id(), '=');
$can_be_deleted = [];
$all_languages = \Drupal::languageManager()->getLanguages(LanguageInterface::STATE_CONFIGURABLE);
foreach ($ids as $id) {
  $orConditionGroup = $query->orConditionGroup();
  foreach ($all_languages as $language) {
    if ($entity->language()->getId() != $language->getId()) {
      $orConditionGroup->condition($field_name, $id, '=', $language->getId());
    }
  }
  $query->condition($orConditionGroup);
  $result = $query->execute();
  if (empty($result)) {
    $can_be_deleted[] = $id;
  }
}
$ids = $can_be_deleted;

Produced SQL query is going to join to the field table based on langcode that were first passed to condition group.

So if the first language in the example above would be Japanese it will succeed, but if the first on would be German query will fail , well not fail but it will return nothing.

Proposed resolution

Join on the langcode only if there is one language in field condition, otherwise add the langcode to the WHERE clause.

Remaining tasks

1. Provide test to prove the bug
2. Agree on suggested solution
3. Implement fix
4. Review
5. Commit

User interface changes

None

API changes

TBD

Data model changes

NONE

Release notes snippet

TBD