SQL error when sorting Bibliography by author
| Project: | Bibliography Module |
| Version: | 6.x-1.6 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | fixed |
Jump to:
I'm using the Bibliography module against a PostgreSQL database, and when attempting to sort the Bibliography by author I get the following error:
* warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/www/drupal6/includes/database.pgsql.inc on line 139.
* user warning: query: SELECT DISTINCT(n.nid), n.*, b.*, bt.name as biblio_type_name FROM (SELECT DISTINCT ON (nid) * FROM node) n left join biblio b on n.vid=b.vid inner join biblio_types bt on b.biblio_type=bt.tid INNER JOIN biblio_contributor as bc on b.vid = bc.vid JOIN biblio_contributor_data as bcd on bc.cid = bcd.cid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public'))) AND ( (bc.rank=0) AND (bc.auth_category=1) AND (n.type='biblio' ) AND (n.status = 1 ) )ORDER BY bcd.lastname desc LIMIT 25 OFFSET 0 in /usr/local/www/drupal6/sites/all/modules/biblio/biblio.pages.inc on line 87.
If I modify the code on my test system and replace the
$selects[] = "DISTINCT(n.nid)"; (line 111 or thereabouts in biblio.pages.inc)
portion with
$selects[] = "n.nid"
it seems to work as expected; however, I don't have a mySQL system that I can use to confirm that it works properly.
If I leave the $select[] statement unchanged, and simply modify the $sortby variable to be "ORDER BY n.nid, ..." then the entries are returned but are sorted in nid order not alphabetical (which is how they should be).

#1
The DISTINCT clause is really only required when other modules like Organic Groups or Access Control modules modify the query, so it's entirely possible you will see no ill effects from removing this, but it can't be removed from the distribution because many other people are using such modules.
Ron.
#2
Hi Ron,
Sorry for the delay getting back to you. I've been rather busy of late.
If I understand what you're saying correctly, you have to leave the DISTINCT clause in the production version to support Organic Groups and similar modules, even though in my case its the use of Organic Groups that seems to be causing the problem in the first place.
Did I interpret you correctly?
#3