Hello,
I'm trying to use the "Distinct" feature but even with a very simplistic setup I get the following error message:

    * 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) FROM node node ) AS count_alias' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid) FROM node node ) AS count_alias in sites\all\modules\views\includes\view.inc on line 652.
    * 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) FROM node node LIMIT 0, 10' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid) FROM node node LIMIT 0, 10 in sites\all\modules\views\includes\view.inc on line 677.

My view looks like this:

$view = new view;
$view->name = 'test';
$view->description = 'test view';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE;
/* Edit this to true to make a default view disabled initially */ $handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array('nid' => array('label' => 'Nid', 'link_to_node' => 0, 'exclude' => 0, 'id' => 'nid', 'table' => 'node', 'field' => 'nid', 'relationship' => 'none',),));
$handler->override_option('access', array('type' => 'none', 'role' => array(), 'perm' => '',));
$handler->override_option('distinct', 1);

This view of course doesn't actually produce any duplicated values, but I get the same errors with more complex views as well...

Comments

merlinofchaos’s picture

Status: Active » Closed (duplicate)

This appears to be a bug in core with db_rewrite_sql. =(

See: http://drupal.org/node/284392

Right now the only workaround is to avoid use of the DISTINCT setting.

malcovich’s picture

Have not been able to find any new progress on this "DISTINCT" issue. Has there been any new development?

aangel’s picture

There is a patch that worked for me here:
http://drupal.org/node/284392