By kieran.osullivan on

Problem in Brief
SQL error appearing at top of tracker page.

What I want to know
Where on Gods green earth is the sql that generates the recent posts page.

I know how to manually change the query to make it correct for postgres. but I just don't know where to put my changes. I have tried the tracker.module file but that had no affect. I also commented out the queries in this module and re-started the http server with no affect.

I have looked at the db_rewrite_sql function but can't tell how I woud change it to get the desired reuslt.

System Info
OS Linux
DB PostgreSQL 7.4 or PostgreSQL 8.0 (both have the same error)

Problem Details
The Recent Posts functonality works but there is an error at the top of the page.

If you are not logged in you see the following error:

warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" at character 24 in /var/www/drupal/includes/database.pgsql.inc on line 125.
user warning: query: SELECT count( DISTINCT ON (node.nid) node.nid) FROM node node LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN users users ON node.uid = users.uid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( (node.status = '1') ) in /var/www/drupal/includes/database.pgsql.inc on line 144.

If you are logged in you see this

warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" at character 24 in /var/www/drupal/includes/database.pgsql.inc on line 125.
user warning: query: SELECT count( DISTINCT ON (node.nid) node.nid) FROM node node LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN users users ON node.uid = users.uid WHERE (node.status = '1') in /var/www/drupal/includes/database.pgsql.inc on line 144.

Thanks.

⋅ Categories: Drupal 5.x

Comments

pwolanin’s picture

pwolanin commented

It looks to me like there is some problem in the way the query is getting re-wriitten for postgresql. The original query (in the tracker module) does not have the "ON".

Please search for similar bug reports and file a new one if it's not already in the queue.

Looks like the culprit might be this function in includes/database.pgsql.inc:



/**
 * Wraps the given table.field entry with a DISTINCT(). The wrapper is added to
 * the SELECT list entry of the given query and the resulting query is returned.
 * This function only applies the wrapper if a DISTINCT doesn't already exist in
 * the query.
 *
 * @param $table Table containing the field to set as DISTINCT
 * @param $field Field to set as DISTINCT
 * @param $query Query to apply the wrapper to
 * @return SQL query with the DISTINCT wrapper surrounding the given table.field.
 */
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;
}

Do you have an access control module installed?

This might be related to this bug: http://drupal.org/node/111830

---
Work: BioRAFT

kieran.osullivan’s picture

kieran.osullivan commented

I commented out the following line
$query = preg_replace('/(ORDER BY )(?!'.$table.'\.'.$field.')/', '\1'."$table.$field, ", $query);

Every thing worked fine after that.

pwolanin’s picture

pwolanin commented

Did you find any related bug reports? Should this be filed as a new one?

---
Work: BioRAFT

kieran.osullivan’s picture

kieran.osullivan commented

I think that this is not new I have seen other similar reports on the issue.

pwolanin’s picture

pwolanin commented

Filed an issue here: http://drupal.org/node/125434

please add your details there.

---
Work: BioRAFT

blueminds’s picture

blueminds commented

Hi all,

error was created at database.pgsql.inc in function db_distinct_field.

Not sure for what reason field to select was generated as follows

$field_to_select = 'DISTINCT ON ('. $table .'.'. $field .") $table.$field";

but can tell that it is not correct.

just rewrite it like this and all wil work fine.

$field_to_select = 'DISTINCT ('. $table .'.'. $field .")";
delgado-1’s picture

delgado-1 commented

I don't think that there is anything wrong with the use of the DISTINCT ON syntax. It is valid (although supposedly not standard) SQL according to http://www.postgresql.org/docs/8.2/interactive/queries-select-lists.html...

I am experiencing a similar error to the original poster, but patching my database.pgsql.inc in such a way just leads to a different error being generated subsequently:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/local/www/drupal5/includes/database.pgsql.inc on line 125.

    * user warning: query: SELECT DISTINCT (n.nid), n.title, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.status = 1 AND n.type = 'forum' ORDER BY n.nid, l.last_comment_timestamp DESC LIMIT 5 OFFSET 0 in /usr/local/www/drupal5/includes/database.pgsql.inc on line 144.

My own installation particulars are Drupal 5.1, PHP 5.2.1, and PostgreSQL 8.2.4 (all on FreeBSD 6.2).

evey’s picture

evey commented

I had the same issue as the original poster (Again Drupal 5 on postgres) and was rather confused as to where it was being generated since I had not enabled the 'tracker' module.

I realised that it's actually provided by a view - go to Administer > Content Management > Views and it's the 'tracker' view within the Default View section.

Whilst you can't edit the properties for it there, you can click on the 'Add' button against the tracker view and create a clone copy (Just renaming it to say 'mytracker' etc.)
I experimented by removing display fields gradually/filters - and found no difference - same postgres sql issues, until I unchecked 'Use Pager' - then the SQL errors went.

So - please anyone trying to resolve this - look at the paging side in relation to the issue.

I tried to modify the original tracker definition within /modules/views/modules/views_user.inc to set $view->use_pager to false, but this had no effect (Also tried echo to page and found the surrounding function wasn't being called - I guess some form of caching going over my head..)
So my work around was to disable the original 'tracker' view within the Default Views and use 'Add' to create a copy of that view but with 'Use Pager' truned off.

PloPli’s picture

PloPli commented

I add the same problem, both with using the tracker view and outside this (even with views disabled). proposed solution worked for me but I cannot attest of side effects.