Hello,

I wrote PostgreSQL/MySQL compilance guidelines:
http://drupal.org/node/555514

Here are patches:

# warning: pg_query() [function.pg-query]: Query failed: ERREUR: pour SELECT DISTINCT, ORDER BY, les expressions doivent apparaître dans la liste SELECT LINE 12: ... 1486923809)) )) AND ( n.moderate = 1 )ORDER BY n.changed ... ^ in /home/html/test/includes/database.pgsql.inc on line 139.
# user warning: query: SELECT DISTINCT n.nid FROM node n LEFT JOIN cave_tracker cave_tracker ON cave_tracker.id = n.nid AND cave_tracker.type = 'node' WHERE (( EXISTS ( SELECT 1 FROM troll_whitelist troll_w WHERE troll_w.net <= cave_tracker.ip AND troll_w.bcast >= cave_tracker.ip) OR NOT EXISTS ( SELECT 1 FROM troll_blacklist troll_b WHERE troll_b.net <= cave_tracker.ip AND troll_b.bcast >= cave_tracker.ip AND NOT (troll_b.net <= 1486923809 AND troll_b.bcast >= 1486923809)) )) AND ( n.moderate = 1 )ORDER BY n.changed DESC LIMIT 10 OFFSET 0 in /home/html/test/sites/all/modules/modr8/modr8_admin.inc on line 199.

The solution is explained here:
http://drupal.org/node/555530

You need to write line 198:
$page_sql = db_rewrite_sql('SELECT n.nid, n.changed FROM {node} n WHERE '. $is_published .' n.moderate = 1 ORDER BY n.changed DESC');

Please test under MySQL and apply.
Thanks.

CommentFileSizeAuthor
#7 715838-pgsql-7.patch1.08 KBpwolanin

Comments

grub3’s picture

To be more precise, this applies to modr8_admin.inc.

grub3’s picture

Any progress?

pwolanin’s picture

Status: Needs review » Active

Can you please supply a patch? i.e. a file containing a unified diff per: http://drupal.org/patch/create

pwolanin’s picture

It's totally unclear why the change you suggest is required, even from the linked pages. They talk about a group-by, not order-by.

josh waihi’s picture

The problem is how other modules are acting on db_rewrite_sql. And making nid DISTINCT which it doesn need since node.nid is a distinct column. See #681760: Try to improve performance and eliminate duplicates caused by node_access table joins for how we solved this in core.

pwolanin’s picture

So what's the correct fix, if any, for modr8?

pwolanin’s picture

Status: Active » Needs review
StatusFileSize
new1.08 KB

I'm still not clear why this is needed, but certainly doesn't hurt anything. So this is the change you suggest?

pwolanin’s picture

Status: Needs review » Fixed

committed

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.