Last modified: 2014-10-02 08:47:13 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 68087 - "ORDER BY" clause in query for Special:ActiveUsers throws PostgreSQL error: column "mwuser.user_name" must appear
"ORDER BY" clause in query for Special:ActiveUsers throws PostgreSQL error: c...
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
All Linux
: Normal normal (vote)
: ---
Assigned To: Nobody - You can work on this!
: 71530 (view as bug list)
Depends on:
Blocks: postgres
  Show dependency treegraph
Reported: 2014-07-16 04:11 UTC by bityard+mwbugs
Modified: 2014-10-02 08:47 UTC (History)
2 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Description bityard+mwbugs 2014-07-16 04:11:45 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) */ 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.
Comment 1 Jeff Janes 2014-07-16 21:14:14 UTC
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.
Comment 2 Gerrit Notification Bot 2014-07-18 20:44:46 UTC
Change 147647 had a related patch set uploaded by Jjanes:
PostgreSQL: Fix Special:ActiveUsers GROUP BY query
Comment 3 Andre Klapper 2014-10-02 08:47:13 UTC
*** Bug 71530 has been marked as a duplicate of this bug. ***

Note You need to log in before you can comment on or make changes to this bug.