Problems with PostgreSQL and GROUP BY statements

At my work, we ran into the same problem when doing a migration from Oracle to Postgres.

The problem, I think, is that Postgres orders its results after the select statement not before
so that it's impossible for it to order the results on a column you didn't select.

I'm told we mostly ended up rewriting the queries to avoid this problem but given the information
below, there might be a way around it without changing the queries too much.

Here are a couple of links potentially relating to this same error:

http://archives.postgresql.org/pgsql-general/2004-02/msg01199.php

http://www.postgresql.org/docs/7.4/static/queries-select-lists.html#QUERIES-DISTINCT

We always change the queries. From the link you posted :

The DISTINCT ON clause is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results. With judicious use of GROUP BY and subqueries in FROM the construct can be avoided, but it is often the most convenient alternative.

Adding the fields in the GROUP BY is the only way to have the sql work for both mysql and postgres.

I'm gonna make a patch for this now.

Adrian, any updates on this?

Attached patch should fix the error.
I've removed the ORDER BY (sorting does not have any impact on COUNT) and GROUP BY (we already have DISTINCT).
Please test.

I believe there's a problem with throttling by registered users. The code from throttle.module:
<?php
if ($max_guests = variable_get('throttle_anonymous', 0)) {
$guests = db_result(db_query('SELECT COUNT(sid) AS count FROM {sessions} WHERE timestamp >= %d AND uid = 0', time() - $
time_period));
}
else {
$guests = 0;
}
if ($max_users = variable_get('throttle_user', 0)) {
$users = db_result(db_query('SELECT COUNT(DISTINCT(uid)) AS count FROM {sessions} WHERE timestamp >= %d AND uid != 0 GR
OUP BY uid ORDER BY timestamp DESC', time() - $time_period));
}
else {
$users = 0;
}

The second query is incorrect. Morbus run following commands for me:
SELECT COUNT(DISTINCT(uid)) AS count FROM sessions WHERE timestamp >= 0 AND uid != 0 GROUP BY uid ORDER BY timestamp DESC
Result: 9 rows of '1'
SELECT COUNT(DISTINCT(uid)) AS count FROM sessions WHERE timestamp >= 0 AND uid != 0 GROUP BY uid
Result: 9 rows of '1'
SELECT COUNT(DISTINCT(uid)) AS count FROM sessions WHERE timestamp >= 0 AND uid != 0
Result: 1 row of '9'

First two queries are incorrect. They group rows (with 1 column - uid) by uid first - this generates 9 groups with the same uid. Then DISTINCT leaves only one row in each row with the uid. Then COUNT() counts rows in each group, which gives 9 groups of count==1.

The 3rd query is correct. It counts the number of different uids meeting the criteria. It's equivalent to the query for the anonymous users.

Currently throttling by registered users does not work. I've explained it nicely and project.module or drupal have eaten my explanation without any warning. The attached post fixes postgres' GROUP BY problem and also fixes throttling problem.

There. I've even tested the patch and it works. +1 ;)

Agreed, the GROUP BY and ORDER BY are both unnecessary. Please commit.

Committed to HEAD. Thanks.

Err.