Aside from needing to manually patch the buggy comment.module thanks to this very very old bug:
https://drupal.org/node/97327
...upgrading from 6.20 to 6.22 exposed a SQL error in the system/user.module roundabout line 789 on my PostgreSQL configuration.
The error:
pg_query(): Query failed: ERROR: column "s.timestamp" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...559 AND s.uid > 0 GROUP BY u.uid, u.name ORDER BY s.timestam... ^ in /www/drupal/[deleted]-6.22/includes/database.pgsql.inc on line 138.
The query:
query: SELECT u.uid, u.name, MAX(s.timestamp) AS timestamp FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.timestamp >= 1325818559 AND s.uid > 0 GROUP BY u.uid, u.name ORDER BY s.timestamp DESC LIMIT 10 OFFSET 0 in /www/drupal/farbot-6.22/modules/user/user.module on line 789.
The solution:
modify user/user.module so that the line 789 query goes from this:
// Display a list of currently online users.
$max_users = variable_get('user_block_max_list_count', 10);
if ($authenticated_count && $max_users) {
$authenticated_users = db_query_range('SELECT u.uid, u.name, MAX(s.timestamp) AS timestamp FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid
WHERE s.timestamp >= %d AND s.uid > 0 GROUP BY u.uid, u.name ORDER BY s.timestamp DESC', $interval, 0, $max_users);
to this:
// Display a list of currently online users.
$max_users = variable_get('user_block_max_list_count', 10);
if ($authenticated_count && $max_users) {
$authenticated_users = db_query_range('SELECT u.uid, u.name, MAX(s.timestamp) AS timestamp FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.timestamp >= %d AND s.uid > 0 GROUP BY u.uid, u.name, s.timestamp ORDER BY s.timestamp DESC', $interval, 0, $max_users);
The only difference is the addition of the s.timestamp in the GROUP BY clause. No more sql error, and the code still seems to work. This code is in an area of the file that is getting the current list of users that are online.
That's my story and I'm stickin to it. Now to really get angry when I try to go to D7 and it breaks completely trying (again). :-D
Comments
6.25: line 803
This issue has moved to line 803 in the user.module shipping with Drupal 6.25. Same fix works for PostgreSQL.