Views Distinct / Node Access Problems

I've been battling a core bug that creates problems when you use node access systems like Organic Groups and try to create views that are limited to distinct nodes. When you are using a node access system and you set 'distinct' to 'true' in any node view, you get ugly ugly error messages like:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_date_field_date_value FROM node node LEFT JOIN term_node term_node ON node.vid = term_node.vid INNER JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.status <> 0) AND (node.type in ('event')) AND (term_data.name = 'children') ORDER BY node_data_field_date_field_date_value ASC ) count_alias

Yuck!!

This is actually a core bug, see http://drupal.org/node/284392. Core's db_rewrite_sql() will rewrite the query from DISTINCT(node.nid) AS nid to an incorrect query of DISTINCT(node.nid) AS DISTINCT(node.nid). This invalid query will cause a fatal error keeping the query from executing.

read more