Page MenuHomePhabricator

"ORDER BY" clause in query for Special:ActiveUsers throws PostgreSQL error: column "mwuser.user_name" must appear
Closed, ResolvedPublic


Author: bityard+mwbugs

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) */ 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.

Version: 1.23.1
Severity: normal
OS: Linux



Event Timeline

bzimport raised the priority of this task from to Medium.Nov 22 2014, 3:35 AM
bzimport added a project: Wikimedia-Rdbms.
bzimport set Reference to bz68087.
bzimport added a subscriber: Unknown Object (MLST).

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. ***

Change 147647 merged by jenkins-bot:
PostgreSQL: Fix Special:ActiveUsers GROUP BY query

Umherirrender claimed this task.

Fix will be part of 1.25

Jjanes suggested backporting to 1.23 (and 1.24) in gerrit comment

Jdforrester-WMF added a subscriber: Jdforrester-WMF.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.