In includes/database.pgsql.inc, db_distinct_field() function uses wrong DISTINCT syntax, resulting in errors. I noticed this on the main forums page of my site (getting counts of forum posts/topics failed). The fix was simple. Here is a diff between the provided version and my fixed version:

421c421
< $field_to_select = 'DISTINCT ON ('. $table .'.'. $field .") $table.$field";
---
> $field_to_select = 'DISTINCT ('. $table .'.'. $field .") ";

I experienced and fixed this problem with drupal running on PG 8.1.6. But the problem will also exist on 7.4.13, and the fix is the same.

Apologies if this has been reported before. I'm a new drupal user and did not see a place to search existing bug reports.

Comments

bohmer’s picture

bohmer commented

I realize now that my "fix" is not a full fix for this issue. It makes the forums page work, but other places need the "DISTINCT ON (field) field" syntax instead of the "DISTINCT (field)" syntax. I have tried rebuilding the drupal site from scratch, and have not been able to reproduce the error on the new site. I will update this report if I can figure out a reproduceable test case.

thekk’s picture

thekk commented

An example of bug is located in a bug in Organic Groups, but I've also encountered it in the forums module.
I'm not all that familiar with of the inner workings of the postgresql dbal, but a solution for this problem might to rewrite the db_distinct_field() to add a check which is something like:
- Check the number of fields in the SELECT part of the query (',' seperated)
-- Only 1: Use: $field_to_select = 'DISTINCT ('. $table .'.'. $field .')';
-- More than 1: Use: $field_to_select = 'DISTINCT ON ('. $table .'.'. $field .") $table.$field";

pearcec’s picture

pearcec commented
Status: Active » Closed (duplicate)

See http://drupal.org/node/128846