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

Description

Author: bityard+mwbugs

Description:
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:

Query:
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) 98.209.46.232 */ 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

Details

Reference
bz68087
bzimport set Reference to bz68087.
bzimport added a subscriber: Unknown Object (MLST).
bzimport created this task.Jul 16 2014, 4:11 AM

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

https://gerrit.wikimedia.org/r/147647

  • Bug 71530 has been marked as a duplicate of this bug. ***

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

https://gerrit.wikimedia.org/r/147647

Umherirrender closed this task as "Resolved".Jan 7 2015, 8:35 PM
Umherirrender claimed this task.

Fix will be part of 1.25

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

Umherirrender set Security to None.
Jdforrester-WMF added a subscriber: Jdforrester-WMF.

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