Can't paginate proximity search queries in Craft 5.x

[myleshyson]

We have a query that orders locations by distance and paginates through those locations. However when upgrading to Craft 5.x, I'm noticing that using .count() or paginating that query throws a sqlstate error.

These are the queries we're running that's causing the issue.

{# throws  a sqlstate error. #}
{% set locationCount = craft
  .entries()
  .section('locations')
  .relatedTo(entryId)
  .cache()
  .map({target: distanceTarget, range: 12500 })
  .orderBy('distance')
  .count()
%}

{% set locationsQuery = craft
  .entries()
  .section('locations')
  .with(['locationHours', 'phoneNumbers', 'heroImage', 'emergencyAnnouncements', 'redirectTo'])
  .limit(limit)
  .cache()
  .map({target: distanceTarget, range: 12500 })
  .orderBy('distance')
%}

{# also throws a sqlstate error #}
{% set pageInfo = sprig.paginate(locationsQuery, page) %} {# sprig just uses the core Paginator class here. #}

This is the error I'm seeing from mysql.

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'subquery.distance'; this is incompatible with sql_mode=only_full_group_by

I think it's because the query is missing a GROUP BY statement when using count(). I can just query all locations and count them at run time (not ideal but does the trick). However that won't work for pagination, since that also uses count() internally to calculate the total number of pages. This might be a craft issue but wanted to post here first in case not.

Craft Version: 5.3.0.3 PHP Version: 8.2 Google Maps Plugin Version: 5.0.2

[myleshyson]

Found that if I manually add the group by statement to both queries they execute successfully.

{% set locationCount = craft
  .entries()
  .section('locations')
  .relatedTo(entryId)
  .cache()
  .map({target: distanceTarget, range: 12500 })
  .orderBy('distance')
  .groupBy(['distance', 'elements.id', 'elements_sites.id']) {# adding this resolves the error #}
  .count()
%}

Maybe the map field can add this automatically when doing a proximity search? Or at the very least can the docs be updated to mention this?

[amphibian]

I just encountered this bug now in this exact same scenario - proximity search with paginated results. A fix for this would be great!

Craft 5.5.3 Google Maps 5.0.3 MySQL 8.0.35 PHP 8.3.9