Hello, I am using drupal with postgresql and when see taxonomy block module, I have recieved this SQL bug.

warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in D:\www\argis\includes\database.pgsql.inc on line 45.

user error:
query: SELECT DISTINCT(n.nid), n.title, n.body FROM term_node t INNER JOIN node n ON t.nid = n.nid WHERE t.tid IN (6) AND n.status = 1 ORDER BY sticky DESC, created DESC LIMIT 10 in D:\www\argis\includes\database.pgsql.inc on line 63.

It seems, that PostgreSQL does not like columns in ORDER BY, which are not in SELECT clausule. I have simply added them into SELECT, but now there was this SQL bug - olny for anonymous users.

warning: pg_query(): Query failed: ERROR: relation "n" does not exist in D:\www\argis\includes\database.pgsql.inc on line 45.
user error:
query: SELECT DISTINCT(n.nid), n.title, n.body, sticky, created FROM term_node t INNER JOIN node_access na ON na.nid = n.nid INNER JOIN node n ON t.nid = n.nid WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access1')) AND t.tid IN (6) AND n.status = 1 ORDER BY sticky DESC, created DESC LIMIT 10 in D:\www\argis\includes\database.pgsql.inc on line 63.

It seems, that some hooks joined some table (node_access) and where clausules (acces rigths) to
SQL command, but there are in wrong order (for PostgreSQL). Solution - change order for tables
node and term_node (table node first). See a patch.

CommentFileSizeAuthor
taxonomy_block_module_fix_pgsql.patch869 byteslojza

Comments

crunchywelch’s picture

Status: Active » Fixed

Thanks for the patch, this is in cvs/4.7 version.

crunchywelch’s picture

Status: Fixed » Closed (fixed)