Hi!

I don't know whether this is a problem of the ACL module or of Drupal Core, but when I use ACL (with or without content_access module) together with the forum module from Drupal core, I get this kind of errors: 

warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" at character 31 in /usr/share/drupal5/includes/database.pgsql.inc on line 125.
user warning: query: SELECT r.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.nid = r.nid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( n.status = 1 AND n.type = 'forum' ) GROUP BY r.tid in /usr/share/drupal5/includes/database.pgsql.inc on line 144.
warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" at character 24 in /usr/share/drupal5/includes/database.pgsql.inc on line 125.
user warning: query: SELECT COUNT( DISTINCT ON (n.nid) n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 26 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 4 INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( n.status = 1 AND n.type = 'forum' AND n.created > 1193917972 AND h.nid IS NULL) in /usr/share/drupal5/includes/database.pgsql.inc on line 144.

Another error is popping up when using "Recent posts" menu: 

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

This happens after enabling the ACL module and rebuilding permissions.

I'm using Debian Etch, Drupal 5.3-1 and Postgresql 8.1.

Regards,
Ingo

CommentFileSizeAuthor
#8 patch-database.pgsql_.inc846 bytesij

Comments

salvis’s picture

salvis
he/his
commented
Category: support » bug

Here's your query (extracted from your error message):

SELECT 
    r.tid, 
    COUNT( DISTINCT ON (n.nid) n.nid) AS topic_count, 
    SUM(l.comment_count) AS comment_count 
  FROM 
    node n 
    INNER JOIN node_comment_statistics l ON n.nid = l.nid 
    INNER JOIN term_node r ON n.nid = r.nid 
    INNER JOIN node_access na ON na.nid = n.nid 
  WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) 
    AND ( n.status = 1 AND n.type = 'forum' ) 
  GROUP BY r.tid;

Apparently, this query comes from the forum_get_forums() function in forum.module:

    $sql = "
      SELECT 
          r.tid, 
          COUNT(n.nid) AS topic_count, 
          SUM(l.comment_count) AS comment_count 
        FROM 
          {node} n 
          INNER JOIN {node_comment_statistics} l ON n.nid = l.nid 
          INNER JOIN {term_node} r ON n.nid = r.nid 
        WHERE n.status = 1 AND n.type = 'forum' 
        GROUP BY r.tid";
    $sql = db_rewrite_sql($sql);

The JOIN with node_access and the associated conditions are added by _node_access_join_sql(), called from node_db_rewrite_sql().

The latter also sets 

    $return['distinct'] = 1;

which causes db_rewrite_sql() to call db_distinct_field(), whose database.pgsql.inc version finally inserts the DISTINCT ON clause.

With all that said, I can't see what's wrong with it...

Could it be that PostgreSQL has a problem because the node table's primary key is (nid, vid) and there's no (nid) index as requested by the DISTINCT ON clause?

ij’s picture

ij commented

Apparently, yes.

When I take an sql statement drupal (or postgres) complains about and try it directly in psql I get this: 

drupal5_mycr=# SELECT COUNT( DISTINCT ON (n.nid) n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 1 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1194006191 AND h.nid IS NULL;
ERROR:  syntax error at or near "ON" at character 24
LINE 1: SELECT COUNT( DISTINCT ON (n.nid) n.nid) FROM node n INNER J...
                               ^

The marker "^" is right below ON in the original output.

When I alter the query and remove "ON (n.nid)" it seems to work (sort of): 

drupal5_mycr=# SELECT COUNT( DISTINCT n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 1 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1194006191 AND h.nid IS NULL;
 count
-------
     0
(1 row)

I don't know what the expected result of this query is, but it's empty before posting a forum topic as well as afterwards.
I hope this helps... :-)

Regards,
Ingo

ij’s picture

ij commented

Wouldn't it be sufficient to use the following SQL query?

SELECT DISTINCT COUNT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 1 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1194006191 AND h.nid IS NULL;

Instead of "select count ( distinct on ())" just a simple plain "select distinct count(n.nid)"?

Regards,
Ingo

ij’s picture

ij commented

Ok, some more testing...

After writing a test forum topic I get the following error when trying to view the forum



    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" at character 24 in /usr/share/drupal5/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT COUNT( DISTINCT ON (n.nid) n.nid) FROM node n INNER JOIN term_node r ON n.nid = r.nid AND r.tid = 1 WHERE n.status = 1 AND n.type = 'forum' in /usr/share/drupal5/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions in /usr/share/drupal5/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT ON (n.nid) n.nid, f.tid, n.title, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, IF(l.last_comment_uid != 0, cu.name, l.last_comment_name) AS last_comment_name, l.last_comment_uid, l.comment_count AS num_comments FROM node_comment_statistics l, users cu, term_node r, users u, forum f, node n WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND n.nid = r.nid AND r.tid = 1 AND n.uid = u.uid AND n.vid = f.vid ORDER BY n.sticky DESC, l.last_comment_timestamp DESC, n.created DESC LIMIT 25 OFFSET 0 in /usr/share/drupal5/includes/database.pgsql.inc on line 144.

To no surprise there's no forum entry listed for that forum (because of the error).

Taking the whole sql query to psql again gives the following result: 

drupal5_mycr=# SELECT DISTINCT ON (n.nid) n.nid, f.tid, n.title, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, IF(l.last_comment_uid != 0, cu.name, l.last_comment_name) AS last_comment_name, l.last_comment_uid, l.comment_count AS num_comments FROM node_comment_statistics l, users cu, term_node r, users u, forum f, node n WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND n.nid = r.nid AND r.tid = 1 AND n.uid = u.uid AND n.vid = f.vid ORDER BY n.sticky DESC, l.last_comment_timestamp DESC, n.created DESC LIMIT 25 OFFSET 0;
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Removing the "ON (n.nid)" again gives the following result: 

drupal5_mycr=# SELECT DISTINCT n.nid, f.tid, n.title, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, IF(l.last_comment_uid != 0, cu.name, l.last_comment_name) AS last_comment_name, l.last_comment_uid, l.comment_count AS num_comments FROM node_comment_statistics l, users cu, term_node r, users u, forum f, node n WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND n.nid = r.nid AND r.tid = 1 AND n.uid = u.uid AND n.vid = f.vid ORDER BY n.sticky DESC, l.last_comment_timestamp DESC, n.created DESC LIMIT 25 OFFSET 0;
 nid | tid | title | sticky | name  | uid | timestamp  | comment_mode | last_comment_timestamp | last_comment_name | last_comment_uid | num_comments
-----+-----+-------+--------+-------+-----+------------+--------------+------------------------+-------------------+------------------+--------------
   1 |   1 | test  |      0 | admin |   1 | 1196598685 |            2 |             1196598685 | admin             |                1 |            0
(1 row)

... which looks fine for me.

I think the problem is the common usage of "ON (n.nid)" together with DISTINCT. I don't know whether this is caused by ACL module or Drupal core, though...

Regards,
Ingo

salvis’s picture

salvis
he/his
commented

Thank you for your research!

I don't know whether the "ON (n.nid)" is necessary, but core seems to insert it specifically for pgsql.

Please try the following: replace

COUNT( DISTINCT ON (n.nid) n.nid)

with

COUNT( DISTINCT ON (n.nid, n.vid) n.nid)

or

COUNT( DISTINCT ON (n.nid, n.vid) n.nid n.vid)

I wonder whether either of these works...

ij’s picture

ij commented

Neither one works: 

drupal5_mycr=# SELECT COUNT( DISTINCT ON (n.nid, n.vid) n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 1 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1194006191 AND h.nid IS NULL;
ERROR:  syntax error at or near "ON" at character 24
LINE 1: SELECT COUNT( DISTINCT ON (n.nid, n.vid) n.nid) FROM node n ...
                               ^

drupal5_mycr=# SELECT COUNT( DISTINCT ON (n.nid, n.vid) n.nid n.vid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 1 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1194006191 AND h.nid IS NULL;
ERROR:  syntax error at or near "ON" at character 24
LINE 1: SELECT COUNT( DISTINCT ON (n.nid, n.vid) n.nid n.vid) FROM n...
                               ^

I think that's a bug in database.pgsql.inc of Drupal. The PostgreSQL docu is not very clear about this (http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-DISTINCT), but I think "SELECT COUNT ( DISTINCT ON (something) something)" is plain wrong. It should be "SELECT COUNT(DISTINCT n.nid)" as the distinction should be on "n.nid" is this case:

drupal5_mycr=# SELECT COUNT(DISTINCT n.vid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 1 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1194006191 AND h.nid IS NULL;
 count
-------
     0
(1 row)

Regards,
Ingo

killes@www.drop.org’s picture

killes@www.drop.org commented
Project: ACL » Drupal core
Version: 5.x-1.5 » 5.x-dev
Component: Code » postgresql database

moving, seems to be a core issue.

ij’s picture

ij commented
StatusFileSize
new patch-database.pgsql_.inc846 bytes

Just a small patch: 

--- /usr/share/drupal5/includes/database.pgsql.inc.orig 2006-12-27 23:13:56.000000000 +0100
+++ /usr/share/drupal5/includes/database.pgsql.inc      2007-12-02 19:47:16.931449537 +0100
@@ -418,7 +418,7 @@
  * @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";
+  $field_to_select = 'DISTINCT '. $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);

But I guess there's more to it than this simple patch as I have absolutely no ACL settings in the interface with activated ACL and content_access modules.

Regards,
Ingo

ivansb@drupal.org’s picture

ivansb@drupal.org
Primary language Italian
Location Milano
commented

Apparently pgsql like the on syntax just OUTSIDE count()

select count(distinct nid) from node;
works

select count(distinct on nid) from node;
doesn't.

http://www.postgresql.org/docs/8.1/interactive/sql-expressions.html#SYNT...

dww’s picture

dww
we/he/they
commented
Status: Active » Closed (duplicate)

http://drupal.org/node/181689
Same bug, basically the same patch.

smk-ka’s picture

smk-ka commented

According to the Postgres manual, DISTINCT ON is considered "bad style":

The DISTINCT ON clause is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results. With judicious use of GROUP BY and subqueries in FROM the construct can be avoided, but it is often the most convenient alternative.

I'm not sure, though, why it was used before, that is, if there (ever) was a need to use this variant.

FYI, from the reference documentation:

SELECT DISTINCT select_list ...
SELECT DISTINCT ON (expression [, expression ...]) select_list ...
http://www.postgresql.org/docs/8.1/interactive/queries-select-lists.html...

aggregate_name (DISTINCT expression [ , ... ] )
http://www.postgresql.org/docs/8.1/interactive/sql-expressions.html#SYNT...

ij’s picture

ij commented

With my patch (see above) there seems to be another problem in the blog module: 

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /usr/share/drupal5/includes/database.pgsql.inc on line 125.
    * user warning: query: SELECT DISTINCT n.nid, n.created FROM node n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.nid, n.sticky DESC, n.created DESC LIMIT 10 OFFSET 0 in /usr/share/drupal5/includes/database.pgsql.inc on line 144.

I tried the patch from http://drupal.org/node/181689 (http://drupal.org/files/issues/pg_count_fix.patch) and this patch seems to work fine so far...

Anyway, with either patch there's no ACL settings when editing content, although acl as well as content_access module is activated.

ivansb@drupal.org’s picture

ivansb@drupal.org
Primary language Italian
Location Milano
commented

that is another issue... in pg all the column in the order by MUST appear in the select.

What is missing is not the ON rather the sticky field.

SELECT DISTINCT n.nid, sticky, n.created FROM node n WHERE n.type = 'blog' AND n.status = 1 ORDER BY n.nid, n.sticky DESC, n.created DESC LIMIT 10 OFFSET 0

Be warned that DISTINCT list... will pick up distinct tuplets not distinct values for each column so that (10,1,'2007-10-20') is actually distinct from (10,0,'2007-10-20') but in this query it shouldn't be a problem.

To apply DISTINCT to just a part of the tuplets you've to use the on syntax and parenthesis and the DISTINCT column won't be part of the result.

drop table foca;
create table foca (a int, b int, c int);
insert into foca values(1,1,1);
insert into foca values(2,1,2);
insert into foca values(1,2,3);
insert into foca values(1,1,4);
insert into foca values(1,1,5);

select distinct on (a, b) c from foca;

1
3
2

if you want a and b too:

select distinct on (a, b) a,b,c from foca;

as you notice you've no warranty on which tuplets get discharged unless you add an order by clause... but this is the case of the original problem and well we're not talking about the ON clause use for this error.

BTW this is a common error in drupal code. I've seen missing columns quite frequently and as you notice they don't work.

Is there a good place where to write a "common postgresql errors made by mysql developer"?

for the curious minds: foca=seal in italian ;)

dww’s picture

dww
we/he/they
commented

When an issue is marked duplicate, the point is to move discussion to the older issue. There's no sense continuing to post here. Thanks.