Custom view sort plugin not working only when used in table sorting
The problem is that for click sorting it seems that not the view sort plugin is used but the field handler which holds a method clickSort
So here is the solution:
1.) I defined a custom field handler. This can be done in hook_views_data or hook_views_data_alter. I did it in my custom views EntityViewsData extending class:
$data['custom_entity']['committee_role']['field']['id'] = 'committee_role';

2.) Then I defined the field handler class where clickSort can be overwritten.
use Drupal\Core\Database\Database;
use Drupal\views\Plugin\views\field\EntityField;

/**
 * @ingroup views_field_handlers
 *
 * @ViewsField(&quot;committee_role&quot;)
 */
class CommitteeRole extends EntityField {

  public function clickSort($order) {
    // No column selected, can't continue.
    if (empty($this-&gt;options['click_sort_column'])) {
      return;
    }

    $this-&gt;ensureMyTable();
    $field_storage = \Drupal::service('entity_field.manager')-&gt;getFieldStorageDefinitions('committee_membership')[$this-&gt;field];
    $allowed_values = array_keys(options_allowed_values($field_storage));
    $formula = 'FIELD(' . $this-&gt;tableAlias . '.' . $this-&gt;field . ', ' . implode(', ', array_map(array(Database::getConnection(), 'quote'), $allowed_values)) . ')';

    $this-&gt;query-&gt;addOrderBy(NULL, $formula, $order, $this-&gt;tableAlias . '_' . $this-&gt;field . '_keys');
  }
}

After clearing the caching the results in the table are sorted the same way as in the other view. Strange that there is an additional sorting logic in views when a table display is used and the sorting is done via column headers.