After I installed this module, all my stories are in reverse order (show very first post on top). I'm using PostgreSQL if that helps. Any ideas how I can fix this?

Comments

AndyColson’s picture

AndyColson commented

Ok, Its not only this module... any of the node access modules (Content Access, taxonomy_access and tac_lite) have the same problem... so maybe its a problem in core and not these modules?

-Andy

AndyColson’s picture

AndyColson commented
Project: Content Access » Drupal core
Version: 5.x-1.1 » 5.2
Component: Miscellaneous » node system
Category: support » bug

Ok, more info...

I ran it twice, with devel enabled, to see the query's run.

Normally this is the sql executed:

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

But when I enable one of the Content Access modules, this is the sql:

SELECT DISTINCT ON (n.nid) n.nid, n.sticky, n.created
FROM node n
WHERE n.promote = 1 AND n.status = 1
ORDER BY n.nid, n.sticky DESC, n.created DESC LIMIT 10 OFFSET 0

Note the ORDER BY changed. I tracked to node.module line 2421, which shows this:

$result = pager_query(db_rewrite_sql('SELECT n.nid, n.sticky, n.created FROM {node} n WHERE n.promote = 1 AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC'), variable_get('default_nodes_main', 10));

So is the db_rewrite_sql changing the order by too?

The hook method does not seem to rewrite the orderby, here is the code:

function node_db_rewrite_sql($query, $primary_table, $primary_field) {
if ($primary_field == 'nid' && !node_access_view_all_nodes()) {
$return['join'] = _node_access_join_sql($primary_table);
$return['where'] = _node_access_where_sql();
$return['distinct'] = 1;
return $return;
}
}

If I remove the distinct line ($return['distinct'] = 1;) it seems to work correctly.

-Andy

kazi-1’s picture

kazi-1 commented

I am using version 4 (PostgreSQL database), and just upgraded to 4.7.8. After doing so, I had the same problem. Although the line numbers are different, the patch mentioned above fixed the problem, but I didn't like the idea of just removing the DISTINCT directive, so I dug into the file "database.pgsql.inc" (within the "includes" directory). I found the function to automatically create DISTINCT queries: db_distinct_field(). The function looks like this (starting at line 376 in my version):

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 .'\.)?(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1'. $field_to_select .'\3', $query);
  $query = preg_replace('/(ORDER BY )(?!'.$table.'\.'.$field.')/', '\1'."$table.$field, ", $query);
  return $query;
}

Notice that the ORDER BY is indeed getting re-written... although I'm not sure why this is necessary. I commented out the line
$query = preg_replace('/(ORDER BY )(?!'.$table.'\.'.$field.')/', '\1'."$table.$field, ", $query);
and it fixed the problem. I'm still not sure what the "correct" fix would be... but this worked for me.

Hope this helps!
-- kazi

kazi-1’s picture

kazi-1 commented

Scratch that... I just figured out why ORDER BY has to be changed... Not doing so results in this error:

pg_query(): Query failed: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions in /mypath/mydomain.com/includes/database.pgsql.inc on line 94.

So the DISTINCT directive is on nid, so nid must be included in the first ORDER BY... but that is exactly what we _don't_ want to do, since it breaks the sort order. I'm back to Andy's original fix: comment out the $return['distinct'] = 1; line in node.module.

rbroberts’s picture

rbroberts commented
Version: 5.2 » 5.5

This is still true in 5.5. I upgraded last night from an older 4.7 to 5.5 do deal with various security issues. By far the most painful part of the process was finding out why my front page had entries sorted in chronological order instead of reverse. I tried uninstalling some modules, I tries installing the Views modules, I had already edited some queries in database.pgsql.inc, and none of it had any affect.

Andy's fix of commenting out

$return['distinct'] = 1;

worked fine. Here's some information from my status page:

Drupal	5.5
Database schema	Up to date
PHP	5.1.6
PostgreSQL database	8.1.9
Web server	Apache/2.2.2 (Fedora)
dpearcefl’s picture

dpearcefl commented
Status: Active » Closed (won't fix)

Due to the age of the last comment on this issue and due to the fact that D5 is no longer supported, I am closing this issue.