SQL Error when database configuration is set sql_mode=ONLY_FULL_GROUP_BY

[JohntheFish]

Affected Version of Concrete CMS

9.x

Description

dashboard/users/search

When MySQL is configured with
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY

The dashboard users page results in a SQL whoops.

An exception occurred while executing 'SELECT u.uID, u.uDateAdded FROM Users u LEFT JOIN UserSearchIndexAttributes ua ON u.uID = ua.uID WHERE (u.uIsActive = ?) AND (u.uIsValidated != 0) GROUP BY u.uID ORDER BY u.uDateAdded desc, u.uID desc LIMIT 10' with params [true]: SQLSTATE[42000]: Syntax error or access violation: 1055 'dev_920.u.uDateAdded' isn't in GROUP BY

How to reproduce

Configure MySQL with
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY

Restart database

Visit dashboard page for Members at dashboard/users/search

Possible Solution

The SQL for this search needs to be re-written to include u.uDateAdded in the Group By clause.

Additional Context

I don't usually have my databases configured with ONLY_FULL_GROUP_BY.

A customer came across an issue in one of my addons that I traced to their database settings including ONLY_FULL_GROUP_BY. This was the default setting of their host.

To track and test the addon bug, I reconfigured one of my test databases for ONLY_FULL_GROUP_BY (and fixed the addon bug). A week later I needed to check the members list on a connected site and came across this bug at dashboard/users/search.

As similar code is used across other dashboard listing pages there could be similar bugs on other dashboard pages. I have not found any such similar bugs yet.

I have not checked any v8 sites for this bug.

[JohntheFish]

This forum post about the document library may be a similar problem with ONLY_FULL_GROUP_BY
https://forums.concretecms.org/t/document-library-blocks-report-sql-error-after-upgrade-from-v9-1-3-to-v9-2-6/7070/1