Last modified: 2014-10-02 08:47:13 UTC
PostgreSQL version: 9.3
A database error is thrown when visiting the Special:ActiveUsers page. Initially I ran into the issue described in bug 67594 but hand-patched DatabasePostgres.php (per the bug) to work around it. But even after that is fixed, a different error appears:
SELECT user_name,user_id,COUNT(*) AS recentedits,qcc_title FROM "querycachetwo","mwuser","recentchanges" WHERE qcc_type = 'activeusers' AND qcc_namespace = '2' AND (user_name = qcc_title) AND (rc_user_text = qcc_title) AND (rc_type != '5') AND (NOT EXISTS (SELECT 1 FROM "ipblocks" WHERE (ipb_user=user_id) AND ipb_deleted = '1' )) GROUP BY qcc_title ORDER BY qcc_title LIMIT 51
Function: IndexPager::buildQueryInfo (ActiveUsersPager)
Error: 42803 ERROR: column "mwuser.user_name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...ildQueryInfo (ActiveUsersPager) 18.104.22.168 */ user_name,... ^
Bug 21196 contains a similar error but in a completely different part of the code.
After some research, I understand the issue somewhat but the fix is currently beyond my own abilities.
The summary is incorrect, it is the GROUP BY, not the ORDER BY, that causes the problem.
this can seemingly be fixed by just adding 'user_name' and 'user_id' into the GROUP BY list.
Since user_name is already constrained to be equal to qcc_title, then adding that to the GROUP BY list cannot change the results of the query.
Since user_id and user_name are both unique and not null in the same table, there must be a one to one correspondence between them and so adding user_id to the GROUP_BY when user_name is already there also cannot change the query result.
Change 147647 had a related patch set uploaded by Jjanes:
PostgreSQL: Fix Special:ActiveUsers GROUP BY query
*** Bug 71530 has been marked as a duplicate of this bug. ***