Enabling aggregation breaks views with "Moderation state" fields

#17 applied to 10.1.3 and allowed my view with aggregation to work with a Moderation State field.

Setting to Needs Review as there are no steps to reproduce the error described in #18 and this is working for me.

Issue summary update and tests will still be needed.

I have just tested with 10.2.2 and the #17 patch seems to be working.

Added patch 17 for Drupal 10.2.4. It only fixes the issue if the aggregation setting for the moderation state is set to aggregate on the Entity ID instead of the Value. (See attached screenshot for more information.)

Thank you for this. #17 worked for me in Drupal 10.2.3.

#17 partially worked for me in Drupal 10.3.0 and 10.2.3
I use an aggregartion on content moderation states field with the count aggrator in combination with the chart module.

I've solved my issue with tha hook_views_query_alter

function hook_views_query_alter(ViewExecutable $view,  $query):void
{
    //Fix bug for views on content moderation
	if(!empty($query->fields)){
		foreach($query->fields as $kf=>$field){
			if(isset($field['function'])){
				if($field['field']=='moderation_state'){
					$query->fields[$kf]['table']= 'content_moderation_state';
				}
			}
		}
	}
}

#17 works for me with Drupal 10.2.6 (simple aggregation case by value column)
Thank you @anchal_gupta

UPD: The patch from #17 do not correctly evaluate the sort operation - simply, the ORDER BY wasn't been added
I have update a bit a patch from #17 to make sort works (see the interdiff)
This way it works for me with D10.2

The patch from #27 worked for me on Drupal 10.4.8.

Add new Kernel test based on existing kernel tests named ViewsModerationStatexxxxTest.php. In need of work.