🇨🇿Czech Republic tma0
How to get list of installed modules in raw list (beyond drush) ?
But related code is in core module. I have 2020 users, 5 roles in DB. I have no control on mysql as it is on hosting. I can reproduce in phpmyadmin as raw query and it always happens when "SELECT FROM WHERE IN () AND IN ()" or "SELECT FROM WHERE IN () SELECT FROM IN()" command is raised. Likely mysql does an UNION or so. INNER JOIN works well.
🇨🇿Czech Republic tma0
Seems it is about some mysql settings. The same code I see in github for D11 so likely persists (https://github.com/drupal/drupal/blob/11.x/core/modules/user/src/Plugin/...). The error is misleading and fix is avoiding multiple IN conditions generating large sets. Even subquery does not help. But INNER JOIN furtunately works:
if ($uids) {
$roles = user_roles();
$result = $this->database->query('SELECT DISTINCT [u1].[entity_id] AS [uid], [u1].[roles_target_id] AS [rid] FROM
(SELECT [entity_id], [roles_target_id] FROM {user__roles} WHERE [entity_id] IN ( :uids[] )) [u1],
(SELECT [entity_id], [roles_target_id] FROM {user__roles} WHERE [roles_target_id] IN ( :rids[] )) [u2]
WHERE [u1].[entity_id] = [u2].[entity_id]', [':uids[]' => $uids, ':rids[]' => array_keys($roles)]);
// fix: SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
// {user__roles} [u] WHERE [u].[entity_id] IN ( :uids[] ) AND [u].[roles_target_id] IN ( :rids[] )', [':uids[]' => $uids, ':rids[]' => array_keys($roles)]);