given these params:
$table=node
$field=nid
$query=SELECT DISTINCT(node.nid), node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp, node.type AS node_type, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, og_node_data.title AS og_node_data_title, og_node_data.nid AS og_node_data_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 LEFT JOIN {og_ancestry} og_ancestry ON node.nid = og_ancestry.nid LEFT JOIN {node} og_node_data ON og_ancestry.group_nid = og_node_data.nid WHERE (%s.%s %s '%s') ORDER BY node_comment_statistics_last_comment_timestamp DESC

I get this query which has a second DISTINCT which is invalid and unwanted:

SELECT DISTINCT(node.nid), node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp, node.type AS node_type, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, og_node_data.title AS og_node_data_title, og_node_data.DISTINCT(node.nid) AS og_node_data_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 LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid LEFT JOIN node og_node_data ON og_ancestry.group_nid = og_node_data.nid WHERE (node.STATUS = '1') ORDER BY node_comment_statistics_last_comment_timestamp DESC LIMIT 0, 25

I'll also add that this is happenning with uid=1 so we do in fact do the DISTINCT rewriting for that user. We don't do $where or $join rewriting. Not sure if that is by design. But thats a different issue.

CommentFileSizeAuthor
#1 rewrite.patch2.4 KBchx

Comments

chx’s picture

Status: Active » Needs review
StatusFileSize
new2.4 KB

What do we want before the field? Either a whitespace or $table.. This is embodied in this patch. The preg devours that one onewhitespace but it's either after a comma or after SELECT -- and I have added a space after \1 so we are covered. Yes, lookahead could solve this devour problem, too but I did not want to make this even more complex.

sammys’s picture

hehe bitten once again by the db_rewrite_sql() monster! thanks for sorting this out @chx

moshe weitzman’s picture

Status: Needs review » Reviewed & tested by the community

this fixed my problem, and didn't cause other ill affects.

dries’s picture

Status: Reviewed & tested by the community » Fixed

Committed to CVS HEAD.

Anonymous’s picture

Status: Fixed » Closed (fixed)