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

Assigned To

Authored By

	• bzimport
	Jul 16 2014, 4:11 AM

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

Related Objects
Search...

View Standalone Graph

This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Status	Assigned	Task
		· · ·
Invalid	None	T2384 [REPLACED BY TAG] PostgreSQL/pgsql support (tracking)
Resolved	Jjanes	T70087 "ORDER BY" clause in query for Special:ActiveUsers throws PostgreSQL error: column "mwuser.user_name" must appear
		· · ·

Event Timeline

• bzimport raised the priority of this task from to Medium.Nov 22 2014, 3:35 AM

• bzimport added a project: MediaWiki-libs-Rdbms.

• 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