pgsql doesn't like: count( DISTINCT ON (table.field) table.field)

then this is probably a bug of the project module. content access just uses drupal node access API.

if you wish me to debug, you must include exact step by step instructions on how to reproduce this error.

thanks.

I've run into the same issue. In this case, I simply entered a project and a couple of issues and then selected "View all support requests".

Postgres is a bit more rigid its interpretation of the SQL. In SELECT COUNT (DISTINCT) query, the problem is in the COUNT(DISTINCT ON (n.nid) n.nid) clause. SELECT COUNT(DISTINCT n.nid) works. Since the nid is all that is returned from the query and the DISTINCT expression is also the nid, there shouldn't be any need to express it twice.

As for the second query, Postgres requires that the ORDER BY expression match the DISTINCT ON expression. So, ORDER BY n.changed should be ORDER BY n.nid.

Hope this helps.

Here is more information from my experience. The first time I did a check-out with no -r, second time I downloaded the latest release. Both versions produced the same result.

Postgres 8.1
Drupal 5.3
Project 5.x-1.0
Project Issues 5.x-1.1

I did a fresh install of 5.3 and added a new project and a new issue.

path: project/issues/1
errors:

# arning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" at character 24 in /home/user/site/public_html/includes/database.pgsql.inc on line 125.

# user warning: query: SELECT COUNT( DISTINCT ON (n.nid) n.nid) FROM node n INNER JOIN project_issues p ON p.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON p.assigned = u.uid WHERE n.status = 1 AND ((p.pid = '1')) AND ((p.sid = '1' OR p.sid = '2' OR p.sid = '4')) in /home/user/site/public_html/includes/database.pgsql.inc on line 144.

# warning: pg_query() [function.pg-query]: Query failed: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions in /home/user/site/public_html/includes/database.pgsql.inc on line 125.

# user warning: query: SELECT DISTINCT ON (n.nid) n.nid FROM node n INNER JOIN project_issues p ON p.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON p.assigned = u.uid WHERE n.status = 1 AND ((p.pid = '1')) AND ((p.sid = '1' OR p.sid = '2' OR p.sid = '4')) ORDER BY n.changed DESC LIMIT 20 OFFSET 0 in /home/user/site/public_html/includes/database.pgsql.inc on line 144.

The errors in question are caused by og and/or og_project.

We did some work tonight to try to fix the issue and we were lead to the bad SQL being generated by the output of pager_query() and db_rewrite_sql() functions in issue.inc.

I lost track of the changes and original line numbers so I did a fresh install (db and files) so I could report something useful here and I ended up finding out something more interesting.

Previously I had installed og and og_project, but this time I did not. I was now able to use issue tracking with postgres just fine. The problems are only introduced after enabling og and og_project. Even after disabling og/og_project, the postgres errors will persist if those were ever enabled.

reassigning to og_project -- seems like the most likely culprit.

og_project desperately needs love. patches welcome in the mean time.

after looking at og_project, i figured there's no way it could be that. i ended up tracking the issue down to core.

includes/database.pgsql.inc
function db_distinct_field($table, $field, $query)

$query passed in

SELECT count(node.nid) FROM {node} node LEFT JOIN {og} og ON node.nid = og.nid INNER JOIN {users} users ON node.uid = users.uid WHERE (node.status = '1') AND (og.directory = '1') AND (node.type IN ('group'))

return

SELECT count( DISTINCT ON (node.nid) node.nid) FROM {node} node LEFT JOIN {og} og ON node.nid = og.nid INNER JOIN {users} users ON node.uid = users.uid WHERE (node.status = '1') AND (og.directory = '1') AND (node.type IN ('group'))

pgsql doesn't like: count( DISTINCT ON (node.nid) node.nid)

I was able to correct the issue by omitting 'ON (table.field)' if 'count(table.field)' was passed in to the function. Just the $field_to_select line was changed. I'm not sure of the global impact although it shouldn't hurt anything if it was going to break in count() case anyway.

original function:

function db_distinct_field($table, $field, $query) {
  $field_to_select = 'DISTINCT ON ('. $table .'.'. $field .") $table.$field";
  
  // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).
  $query = preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
  $query = preg_replace('/(ORDER BY )(?!'.$table.'\.'.$field.')/', '\1'."$table.$field, ", $query);
  return $query;
}

my fix:

function db_distinct_field($table, $field, $query) {
	// omit ON (table.field) for select count()
	$field_to_select = 'DISTINCT ' . (eregi('count(\s)*\((\s)*'. $table .'.'. $field .'(\s)*\)', $query) ? '' : ' ON ('. $table .'.'. $field .')') . "  $table.$field";
	
  // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).
  $query = preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
  $query = preg_replace('/(ORDER BY )(?!'.$table.'\.'.$field.')/', '\1'."$table.$field, ", $query);
  return $query;
}

og, og_project, project, and project_issue work well now with og access control enabled.

here's a patch generated from the above changes. my regex foo is pretty weak -- could somebody strong in that area double-check the expression?

also, would be great if some other folks could install this patch and see if it fixes the problem for them as well.

better title

Would it be possible to fix this at the level pager_query() is called with the original query? A separate $count_query, which does not get mangled, could be passed.

Isn't it a legit case that someone may want to use db_distinct_field() on a count() query?

Depends on if this is a systematic problem, or just one odd query. It looks like this does need a general solution since http://drupal.org/node/125434 is a duplicate of this. The only patch there worth considering simply removed 'ON.'

I experienced the same DISTINCT ON query problem on Views module pager query. I tried the patch above and the problem is away. thanks.

Cross-posting from http://drupal.org/node/196831, sorry...

According to the Postgres manual, DISTINCT ON is considered "bad style":

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.

I'm not sure, though, why it was used before, that is, if there (ever) was a need to use this variant.

FYI, from the reference documentation:

SELECT DISTINCT select_list ...
SELECT DISTINCT ON (expression [, expression ...]) select_list ...
http://www.postgresql.org/docs/8.1/interactive/queries-select-lists.html...

aggregate_name (DISTINCT expression [ , ... ] )
http://www.postgresql.org/docs/8.1/interactive/sql-expressions.html#SYNT...

bugs get fixed in the development version.

It seems that the patch from above (http://drupal.org/files/issues/pg_count_fix.patch) doesn't work anymore for drupal 5.5:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions in /usr/share/drupal5/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT ON (n.nid) n.nid, f.tid, n.title, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, IF(l.last_comment_uid != 0, cu.name, l.last_comment_name) AS last_comment_name, l.last_comment_uid, l.comment_count AS num_comments FROM node_comment_statistics l, users cu, term_node r, users u, forum f, node n WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND n.nid = r.nid AND r.tid = 26 AND n.uid = u.uid AND n.vid = f.vid ORDER BY n.sticky DESC, l.last_comment_timestamp DESC, n.created DESC LIMIT 25 OFFSET 0 in /usr/share/drupal5/includes/database.pgsql.inc on line 144.

database.pgsql.inc is already patched with the pg_count_fix.patch:

function db_distinct_field($table, $field, $query) {
  // omit ON (table.field) for select count()
  $field_to_select = 'DISTINCT ' . (eregi('count(\s)*\((\s)*'. $table .'.'. $field .'(\s)*\)', $query) ? ''
 : ' ON ('. $table .'.'. $field .')') . "  $table.$field";

  // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).
  $query = preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $fi
eld .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
  $query = preg_replace('/(ORDER BY )(?!'.$table.'\.'.$field.')/', '\1'."$table.$field, ", $query);
  return $query;
}

Any tips?

If 6.x is already fixed (and 5.x as well?) then this can be marked as fixed - or ideally a duplicate of the issue that solved it.

As I already stated, but maybe it was not clear enough:

The mentioned patch worked fine with Drupal 5.3. Then I upgraded beginning of this week to 5.5 and now it doesn't work anymore. So I consider the bug not fixed for 5.5. Therefore I'm setting the version to 5.5.

Furthermore the patch only applies to DISTINCT ON clauses combined with count(), but the usuage of DISTINCT ON (table.column) table.column doesn't work at all in pgsql.

When using the function db_distinct_field from database.myspql.inc there are no errors, but users are not able to successfully post to forums, i.e. they can post, but the posts won't show up in the forum.

Something is horribly broken, IMHO.

Regards,
Ingo

I've found that the above patches causes problems in various modules. Once content_access module is working fine, but forum modules gives errors whereas a different patch gives a working forum, but a bailing out content_access module.

Finally I've found another patch to this issue which works perfectly fine for me for some weeks now and I would like to see it included in the next 5.x release:

http://drupal.org/node/128846#comment-678398

ij, I updated that patch last week to solve another problem reported by Gábor Hojtsy. Here is the new one: http://drupal.org/node/128846#comment-759767

Salvis is trying to get the patch committed. We need Postgresql users to test the patch. This patch is by far the best fix for a wide range of Postgresql problems. Please help getting the patch committed: http://groups.drupal.org/node/6980

Since the thread where I posted that patches fixed most of the Postgresql problems including this one, I think you guys should stop posting on this thread. Instead, post your comments here: http://drupal.org/node/128846

Looks like everything moved to http://drupal.org/node/128846.