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.
| Comment | File | Size | Author |
|---|---|---|---|
| #1 | rewrite.patch | 2.4 KB | chx |
Comments
Comment #1
chx commentedWhat 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\1so we are covered. Yes, lookahead could solve this devour problem, too but I did not want to make this even more complex.Comment #2
sammys commentedhehe bitten once again by the db_rewrite_sql() monster! thanks for sorting this out @chx
Comment #3
moshe weitzman commentedthis fixed my problem, and didn't cause other ill affects.
Comment #4
dries commentedCommitted to CVS HEAD.
Comment #5
(not verified) commented