cannot browse by taxonomy - postgres sql error ? [#11510]

I'm using 4.5rc with postgresql 7.4.5 apache 1.x on rhes3. the error occurs regardless of user status/role -- all users get this error message; not sure if this is really sql/postgres related, or if this feature is not working because of another miss configurations/set up problem... can't seem to track down the cause.

when you try and select a category (either from the category block, or from the summary view for a node, or you try and navigate to a specific taxonomy term by directly inputting a URL ie: http://www.example.com/taxonomy/term/3) I get the following error and the resulting page says "There are currently no posts in this category." when there are multiple posts within that category:

warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 104.

user error:
query: SELECT DISTINCT(n.nid) FROM node n INNER JOIN term_node tn0 ON n.nid = tn0.nid WHERE n.status = 1 AND '1' AND tn0.tid IN (3) ORDER BY n.sticky DESC, n.created DESC LIMIT 10 OFFSET 0 in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 121.

Comment	File	Size	Author
#3	pgsql_taxo_bug.diff	1.96 KB	adrian
#3

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

michaelemeyers CreditAttribution: michaelemeyers commented 13 October 2004 at 21:56

Title:

cannot browse by taxonomy - postgres sql error or config/set up problem?

» cannot browse by taxonomy - postgres sql error ?

searching through the release notes for postgresql I found the following:

http://www.postgresql.org/docs/7.4/interactive/release-7-0.html
Require SELECT DISTINCT target list to have all ORDER BY columns (Tom)

previously you could have columns and expressions that did not appear in the target list of the select statement in the order by clause...

so it seems that as of Release 7.0 (2000-05-08) it is required that when using SELECT DISTINCT all columns in the ORDER BY clause must be in the select statement? given that this change is over four years old and I have found little documentation about this... my confidence level is not high; however this fix did solve the problem... and the extra data/columns being returned does not seem to cause any problems/conflicts that I've noticed (yet). input/thoughts would be appreciated. thanks _m

in the taxonomy.module file it appears that there are only two SELECT DISTINCT statements with an ORDER BY

line 804:
$sql = 'SELECT DISTINCT(n.nid) FROM {node} n '. node_access_join_sql() .' INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid IN ('. $str_tids .') AND n.status = 1 AND '. node_access_where_sql() .' ORDER BY n.sticky DESC, n.created DESC';

line 814:
$sql = 'SELECT DISTINCT(n.nid) FROM {node} n '. node_access_join_sql() . $joins .' WHERE n.status = 1 AND '. node_access_where_sql() . $wheres .' ORDER BY n.sticky DESC, n.created DESC';

I have changed these lines as follows:

line 804:
$sql = 'SELECT DISTINCT(n.nid), n.sticky, n.created FROM {node} n '. node_access_join_sql() .' INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid IN ('. $str_tids .') AND n.status = 1 AND '. node_access_where_sql() .' ORDER BY n.sticky DESC, n.created DESC';

line 814:
$sql = 'SELECT DISTINCT(n.nid),, n.sticky, n.created FROM {node} n '. node_access_join_sql() . $joins .' WHERE n.status = 1 AND '. node_access_where_sql() . $wheres .' ORDER BY n.sticky DESC, n.created DESC';