database.pgsql.inc has invalid DISTINCT syntax.

I don't have a PostgreSQL install so I can't double-check this. Can someone confirm this problem, and its patch. Thanks.

I think rewrite 'SELECT DISTINCT ON (table.field) table.field ...' to 'SELECT DISTINCT (table.field) ...' may not be enough.
The issue is related to the issue I've reported (http://drupal.org/node/128846#comment-218906), isn't it?

I think rewrite 'SELECT DISTINCT ON (table.field) table.field ...' to 'SELECT DISTINCT (table.field) ...' may not be enough.
The issue is related to the issue I've reported (http://drupal.org/node/128846#comment-218906), isn't it?

I'm talking about PostgreSQL issue, not MySQL. I want to say that

#1: SELECT DISTINCT ON (n.nid) n.nid, n.sticky FROM node n;

and

#2: SELECT DISTINCT (n.nid), n.sticky FROM node n;

is not equal. I think the patch
'database.pgsql_.inc_.20070225.pgsql_.patch' modifies behaviour of
db_distinct_field() from #1 to #2.

With #1 SQL, SELECT keeps only the first row of each set of rows where
nid is equal (this might be what we want to do in db_distinct_field()).

With #2 SQL, duplicated rows (not only nid, but also sticky are equal)
are removed from the result set.

SELECT + distinct-clause + select-list + from-clause

#1

distinct-clause => DISTINCT ON (n.nid)
select-list => n.nid, n.sticky
from-clause => FROM node.n

#2

distinct-clause => DISTINCT
select-list => (n.nid), n.sticky == n.nid, n.sticky
from-clause => FROM node n;

P.S. I'm very sorry that I posted the same comment twice, it was a
technical problem (I was using mobile environment, and it was not
stable).

P.S. The following is a segment from PostgreSQL manual page
(http://www.postgresql.org/docs/8.2/interactive/sql-select.html).

Synopsis

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

DISTINCT Clause

If DISTINCT is specified, all duplicate rows are removed from the
result set (one row is kept from each group of duplicates).

DISTINCT ON ( expression [, ...] ) keeps only the first row of each
set of rows where the given expressions evaluate to equal.

Note that the "first row" of each set is unpredictable unless ORDER BY
is used to ensure that the desired row appears first.

Not sure if it's of any help but I have the same issue. The patch supresses the SQL error but the nodes are sorted in the wrong order on my startpage when the patch is applied.

Doh!
the patch attached in http://drupal.org/node/128846#comment-218906 seems to solve the sorting issue as well. Thanks takashi, this has been bugging me for a while.

Sorry about the spam...
Did some more testing and have run into issues. Will post another update tomorrow.

I will continue discussion in http://drupal.org/node/128846 since it's related to the patch posted there.

http://drupal.org/node/128846 is more-recently updated and has well-commented code.