Using brand new install of drupal 6.3.

I am creating a list of members using some custom cck fields I set up in the content profile module.

Export of View:

$view = new view;
$view->name = 'Members_List';
$view->description = '';
$view->tag = 'members';
$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', 'Members', 'default');
$handler->override_option('fields', array(
  'name' => array(
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
  ),
  'picture' => array(
    'id' => 'picture',
    'table' => 'users',
    'field' => 'picture',
  ),
  'created' => array(
    'label' => 'Joined',
    'date_format' => 'time ago',
    'custom_date_format' => '',
    'exclude' => 0,
    'id' => 'created',
    'table' => 'users',
    'field' => 'created',
    'relationship' => 'none',
  ),
  'field_birthdate_value' => array(
    'label' => '',
    'link_to_node' => 0,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_birthdate_value',
    'table' => 'node_data_field_birthdate',
    'field' => 'field_birthdate_value',
    'relationship' => 'none',
  ),
  'field_orientation_value' => array(
    'label' => '',
    'link_to_node' => 0,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_orientation_value',
    'table' => 'node_data_field_orientation',
    'field' => 'field_orientation_value',
    'relationship' => 'none',
  ),
  'field_relationshipstatus_value' => array(
    'id' => 'field_relationshipstatus_value',
    'table' => 'node_data_field_relationshipstatus',
    'field' => 'field_relationshipstatus_value',
  ),
  'field_city_value' => array(
    'id' => 'field_city_value',
    'table' => 'node_data_field_city',
    'field' => 'field_city_value',
  ),
  'field_province_value' => array(
    'id' => 'field_province_value',
    'table' => 'node_data_field_province',
    'field' => 'field_province_value',
  ),
));
$handler->override_option('sorts', array(
  'name' => array(
    'order' => 'ASC',
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
  'role' => array(),
  'perm' => '',
));
$handler->override_option('title', 'Members List');
$handler->override_option('header', 'Members List');
$handler->override_option('header_format', '1');
$handler->override_option('header_empty', 1);
$handler->override_option('use_ajax', TRUE);
$handler->override_option('use_pager', '1');
$handler->override_option('distinct', 1);
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
  'grouping' => '',
  'override' => 1,
  'sticky' => 0,
  'order' => 'asc',
  'columns' => array(
    'name' => 'name',
    'picture' => 'picture',
    'created' => 'created',
    'field_birthdate_value' => 'field_birthdate_value',
    'field_orientation_value' => 'field_orientation_value',
    'field_relationshipstatus_value' => 'field_relationshipstatus_value',
    'field_city_value' => 'field_city_value',
    'field_province_value' => 'field_province_value',
  ),
  'info' => array(
    'name' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'picture' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'created' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'field_birthdate_value' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'field_orientation_value' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'field_relationshipstatus_value' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'field_city_value' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'field_province_value' => array(
      'sortable' => 1,
      'separator' => '',
    ),
  ),
  'default' => 'name',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'members');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
$handler = $view->new_display('page', 'Page', 'page_2');
$handler->override_option('path', 'members');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
$handler = $view->new_display('page', 'Page', 'page_3');
$handler->override_option('path', 'members');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));

Export of Query:

SELECT DISTINCT(node.nid) AS nid,
   users.name AS users_name,
   users.uid AS users_uid,
   users.picture AS users_picture,
   users.created AS users_created,
   node.type AS node_type,
   node_data_field_birthdate.field_birthdate_value AS node_data_field_birthdate_field_birthdate_value,
   node_data_field_birthdate.field_birthdate_value2 AS node_data_field_birthdate_field_birthdate_value2,
   node_data_field_birthdate.nid AS node_data_field_birthdate_nid,
   node_data_field_birthdate.field_orientation_value AS node_data_field_birthdate_field_orientation_value,
   node_data_field_birthdate.field_relationshipstatus_value AS node_data_field_birthdate_field_relationshipstatus_value,
   node_data_field_birthdate.field_city_value AS node_data_field_birthdate_field_city_value,
   node_data_field_birthdate.field_province_value AS node_data_field_birthdate_field_province_value
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN content_type_profile node_data_field_birthdate ON node.vid = node_data_field_birthdate.vid
    ORDER BY users_name ASC

Copy of error:

    * 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), users.name AS users_name, users.uid AS users_uid, u' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), users.name AS users_name, users.uid AS users_uid, users.picture AS users_picture, users.created AS users_created, node.type AS node_type, node_data_field_birthdate.field_birthdate_value AS node_data_field_birthdate_field_birthdate_value, node_data_field_birthdate.field_birthdate_value2 AS node_data_field_birthdate_field_birthdate_value2, node_data_field_birthdate.nid AS node_data_field_birthdate_nid, node_data_field_birthdate.field_orientation_value AS node_data_field_birthdate_field_orientation_value, node_data_field_birthdate.field_relationshipstatus_value AS node_data_field_birthdate_field_relationshipstatus_value, node_data_field_birthdate.field_city_value AS node_data_field_birthdate_field_city_value, node_data_field_birthdate.field_province_value AS node_data_field_birthdate_field_province_value FROM node node INNER JOIN users users ON node.uid = users.uid LEFT JOIN content_type_profile node_data_field_birthdate ON node.vid = node_data_field_birthdate.vid ORDER BY users_name ASC ) AS count_alias in /home/qwo/public_dev/qwo02.0/sites/default/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), users.name AS users_name, users.uid AS users_uid, u' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), users.name AS users_name, users.uid AS users_uid, users.picture AS users_picture, users.created AS users_created, node.type AS node_type, node_data_field_birthdate.field_birthdate_value AS node_data_field_birthdate_field_birthdate_value, node_data_field_birthdate.field_birthdate_value2 AS node_data_field_birthdate_field_birthdate_value2, node_data_field_birthdate.nid AS node_data_field_birthdate_nid, node_data_field_birthdate.field_orientation_value AS node_data_field_birthdate_field_orientation_value, node_data_field_birthdate.field_relationshipstatus_value AS node_data_field_birthdate_field_relationshipstatus_value, node_data_field_birthdate.field_city_value AS node_data_field_birthdate_field_city_value, node_data_field_birthdate.field_province_value AS node_data_field_birthdate_field_province_value FROM node node INNER JOIN users users ON node.uid = users.uid LEFT JOIN content_type_profile node_data_field_birthdate ON node.vid = node_data_field_birthdate.vid ORDER BY users_name ASC LIMIT 0, 10 in /home/qwo/public_dev/qwo02.0/sites/default/modules/views/includes/view.inc on line 677.
Files: 
CommentFileSizeAuthor
#452 284392-fix-typo-D6.patch893 bytesbrianV
#452 284392-simplify-with-tests-db_distinct_fields-D5.patch5.54 KBbrianV
#445 284392-simplify-with-tests-db_distinct_fields-rev4.D6.fixed_.patch5.79 KBGábor Hojtsy
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-simplify-with-tests-db_distinct_fields-rev4.D6.fixed_.patch.
[ View ]
#397 284392-simplify-with-tests-db_distinct_fields-rev4.D6.patch11.63 KBbenoitg
#388 284392-simplify-db_distinct_fields-rev4.D6.patch6.52 KBbrianV
#381 views_missing_pager_modified_queries_report1.txt9.81 KBjannalexx
#377 284392-simplify-db_distinct_fields-rev3.D6.patch5.88 KBbrianV
#365 284392-simplify-db_distinct_fields-rev2.D6.patch5.58 KBbrianV
#362 284392-simplify-db_distinct_fields.D6.patch4.78 KBbrianV
#361 rewrite-view-export.txt11.34 KBrealityloop
#361 rewrite-sql.txt1.47 KBrealityloop
#361 rewrite-output.png27.92 KBrealityloop
#361 rewrite-vocabs.png24.8 KBrealityloop
#354 Before Patch.png103.44 KBjdwfly
#354 After Patch.png90.84 KBjdwfly
#353 views26.txt44.9 KBbenoitg
#353 views28.txt45.07 KBbenoitg
#351 views_2.6_2.8_queries.distinct.comparison.txt89.33 KBjannalexx
#349 temp_debug.patch1.45 KBbenoitg
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch temp_debug.patch.
[ View ]
#340 284392.2.patch14.11 KBmathieu
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392.2.patch.
[ View ]
#339 284392.db_rewrite.patch11.38 KBmathieu
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392.db_rewrite.patch.
[ View ]
#338 284392.patch5.72 KBmathieu
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392.patch.
[ View ]
#337 db_rewrite_and_tests-D6.6.patch15.79 KBbenoitg
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch db_rewrite_and_tests-D6.6.patch.
[ View ]
#336 db_rewrite_and_tests-D6.5.patch6.65 KBbenoitg
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch db_rewrite_and_tests-D6.5.patch.
[ View ]
#330 db_rewrite_and_tests-D6.4.patch5.69 KBbenoitg
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch db_rewrite_and_tests-D6.4.patch.
[ View ]
#330 drupal_distinct_sql_misuse.sql_.txt3.16 KBbenoitg
#308 database.mysql_.inc_.patch1.23 KBopteronmx
FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in database.mysql_.inc__1.patch.
[ View ]
#298 db_rewrite_and_tests-D6.3.patch7.48 KBevoltech
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch db_rewrite_and_tests-D6.3.patch.
[ View ]
#257 db_rewrite_and_tests-D6.patch5.18 KBmathieu
#233 database-distinct-drupal-6.14-2_1.patch1.13 KBtyr
FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in database-distinct-drupal-6.14-2_1_0.patch.
[ View ]
#230 database-distinct-drupal-6.14-3_1.patch1.75 KBJuliaKM
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch database-distinct-drupal-6.14-3_1.patch.
[ View ]
#227 database-distinct-drupal-6.14-3.patch1.27 KBtyr
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch database-distinct-drupal-6.14-3_0.patch.
[ View ]
#206 database.test3.69 KBSteven Jones
#204 database-distinct-drupal-6.14-2.patch1.13 KBtyr
FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in database-distinct-drupal-6.14-2_1.patch.
[ View ]
#195 284392-database-distinct-drupal-6.14.patch1.1 KBSteven Jones
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-database-distinct-drupal-6.14_1.patch.
[ View ]
#189 284392-database-distinct-drupal-6.14.patch1.11 KBSteven Jones
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-database-distinct-drupal-6.14.patch.
[ View ]
#187 database.test2.85 KBSteven Jones
#175 debug-steps.txt732 bytesagentrickard
#175 view.txt3.12 KBagentrickard
#166 284392-database-distinct-drupal-6.14.patch1.09 KBken54671
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-database-distinct-drupal-6.14_0.patch.
[ View ]
#122 view.txt2.06 KBagentrickard
#122 284392-database-distinct.patch1.96 KBagentrickard
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-database-distinct.patch.
[ View ]
#93 database-D6.patch1.96 KBtassoman
#73 calendarview.txt16.03 KBsrjosh
#62 node.test5.18 KBAlice Heaton
#24 database.patch1.96 KBAlice Heaton
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch database_16.patch.
[ View ]
#24 test_patch.php_.txt1.37 KBAlice Heaton
#19 database.patch2.15 KBAlice Heaton
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch database_15.patch.
[ View ]
#18 database.patch2.01 KBAlice Heaton
Failed: Failed to apply patch.
[ View ]
#11 birthdate.JPG26.02 KBPassionate_Lass

Comments

Project:Views» Drupal core
Version:6.x-2.x-dev» 6.x-dev
Component:Views Data» node.module
Category:support» bug

Wow, and here I thought Drupal 6 would finally have fixed various db_rewrite_sql bugs.

First, thank you for this extremely completely bug report. It is nice to see these.

To summarize: db_rewrite_sql is rewriting this, which is the last step that happens; the query that Views reports to you is the unaltered version. It happens just prior to rewrite (and prior to a query substitution phase but that only replaces phrases like ***CURRENT_USER*** with the uid of the current user).

Views' version of the query does this:

SELECT DISTINCT(node.nid) AS nid,

But the query that's actually run does this:

SELECT DISTINCT(node.nid) AS DISTINCT(node.nid)

That 'nid' is being rewritten into DISTINCT(node.nid) incorrectly.

Note that it is possible this might not be entirely core's fault. Be sure to check any of your modules that might be using db_rewrite_sql and doing their own rewriting.

You're very welcome. I just followed the instructions I got when I went to submit a report.

How would I know they are using db_rewrite_sql? I know enough to figure things out but nothing in depth code wise.

Just so I know, I'm trying to pull up all users but when I didn't turn on the distinct function it pulled up multiple versions of the same user for some reason. If I shut off distinct I get:


Members List

Members List
Name Picture Joined Birthdays Orientation Relationship Status City Province
Kristina 20 hours 41 min ago 08/13/1983 Lesbian Seeing Someone Hull Quebec
Kristina 20 hours 41 min ago
Kristina 20 hours 41 min ago
Kristina 20 hours 41 min ago
Kristina 20 hours 41 min ago
Kristina 20 hours 41 min ago

passionate_lass: You're trying to list users but your view type is set to 'node'. So what you're really listing is content attached to users. You should create a new view, whose type is set to 'users' (on the initial creation page) and you'll get much better results. That will avoid this DISTINCT stickiness, at least.

As for db_rewrite_sql, that's just something that requires familiarity with Drupal, I think; all queries will go through rewrite, unless run by user ID #1, but not all rewrites will do anything.

Ah.

I tried using type "users" but it wouldn't let me access the fields I set up in CCK linked to the content profile node type. Umm... Does that make sense? :)

Ahh. If you have a content profile node type...there should be some way of filtering the view to just that node type, then. What module provides this? I haven't any experience with it.

Yeah I think that is why it is glitching up. If I try the user view type it doesn't let me access node information, while if I try the node view type it lets me access user information in turn permitting me to create the listing I want.

http://drupal.org/project/content_profile

Try removing the distinct and add a Node: Type filter and select the node type you're using for your content profile. That will limit your view to just those nodes.

That module really needs some views integration so that it can create a relationship.

I think they are trying to make the module as simple as possible. *rolls eyes* In turn cutting out views integration.

However, you are a genius! That worked perfectly! Yay!

This is totally unrelated to this issue but a question I'd like to ask since it pertains to the same view / listing. Is there a way to take the birthdate field I created and turn it into age in the view... or is that a cck dohicky? XD

Tried using "time ago" format but it gives "24 years 49 weeks ago ago" for my age. Is there a way to tell it just to display years?

Enter '1' in the format field. Unfortunately I think that will still be '24 years ago' rather than just '24'.

StatusFileSize
new26.02 KB

Hm.

I don't see an option for that. I uploaded an attachment for what I see when I look at the configure field window.

Oh right, that's a date.module field. You'll have to ask in the date.module queue for that one, then. It would appear that Karen didn't implement the format field that the core Views dates have.

Title:View that provides sql errordb_rewrite_sql causing issues with DISTINCT

To summarize:

SELECT DISTINCT(node.nid) AS nid ...

is being rewritten as:

SELECT DISTINCT(node.nid) AS DISTINCT(node.nid) ...

This started to happen to all my views immediately after I installed nodeaccess. Should I be reporting this over there? I did a search for db_rewrite_sql through my code base, but didn't find anything.

I even removed the unique setting in the view and it still gives me this error:

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_time_select.field_date_time_select_v' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_date_time_select.field_date_time_select_value2 AS node_data_field_date_time_select_field_date_time_select_value2, node.title AS node_title, node.type AS node_type, node_data_field_date_time_select.field_date_time_select_value AS node_data_field_date_time_select_field_date_time_select_value, node_data_field_date_time_select.nid AS node_data_field_date_time_select_nid FROM node node LEFT JOIN content_type_event node_data_field_date_time_select ON node.vid = node_data_field_date_time_select.vid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'nodeaccess_rid') OR (na.gid = 0 AND na.realm = 'nodeaccess_uid') OR (na.gid = 0 AND na.realm = 'nodeaccess_author'))) AND ( (node.type in ('event')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_date_time_select.field_date_time_select_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-08-22') )ORDER BY node_data_field_date_time_select_field_date_time_select_value ASC LIMIT 0, 5 in /Users/stephenm/Sites/bedc.dev/htdocs/sites/all/modules/views/includes/view.inc on line 681.

When I look at the SQL that Views is creating, it looks fine:

SELECT node.nid AS nid,
   node.title AS node_title,
   users.name AS users_name,
   users.uid AS users_uid,
   node_revisions.teaser AS node_revisions_teaser,
   node_revisions.format AS node_revisions_format,
   node.changed AS node_changed,
   node.created AS node_created
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
WHERE (node.type in ('resource')) AND (node.status <> 0)
   ORDER BY node_created DESC

And here are the gory details - an export of my view:

$view = new view;
$view->name = 'resources';
$view->description = 'Lists all of the resources in order of most recent first';
$view->tag = 'resources';
$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(
  'title_1' => array(
    'label' => '',
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title_1',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
  'name' => array(
    'label' => 'Posted by',
    'link_to_user' => 1,
    'exclude' => 0,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'relationship' => 'none',
  ),
  'teaser' => array(
    'label' => '',
    'exclude' => 0,
    'id' => 'teaser',
    'table' => 'node_revisions',
    'field' => 'teaser',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'view_node' => array(
    'label' => '',
    'text' => 'read more >',
    'exclude' => 0,
    'id' => 'view_node',
    'table' => 'node',
    'field' => 'view_node',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'changed' => array(
    'label' => 'Updated date',
    'date_format' => 'small',
    'custom_date_format' => '',
    'exclude' => 0,
    'id' => 'changed',
    'table' => 'node',
    'field' => 'changed',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'created' => array(
    'order' => 'DESC',
    'granularity' => 'second',
    'id' => 'created',
    'table' => 'node',
    'field' => 'created',
    'relationship' => 'none',
  ),
));
$handler->override_option('arguments', array(
  'name' => array(
    'default_action' => 'ignore',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '%1',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'glossary' => 0,
    'limit' => '0',
    'case' => 'ucwords',
    'path_case' => 'lower',
    'transform_dash' => 0,
    'add_table' => 0,
    'require_value' => 0,
    'id' => 'name',
    'table' => 'term_data',
    'field' => 'name',
    'relationship' => 'none',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'forum' => 0,
      'event' => 0,
      'page' => 0,
      'resource' => 0,
      'resource_guide_page' => 0,
      'story' => 0,
      'vendor' => 0,
      'vendor_page' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_vocabulary' => array(
      '5' => 0,
      '1' => 0,
      '6' => 0,
      '3' => 0,
      '2' => 0,
    ),
    'validate_argument_type' => 'tid',
    'validate_argument_php' => '',
    'override' => array(
      'button' => 'Override',
    ),
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'resource' => 'resource',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
  'status' => array(
    'operator' => '=',
    'value' => 1,
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'status',
    'table' => 'node',
    'field' => 'status',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'role',
  'role' => array(
    '2' => 2,
  ),
  'perm' => 'access administration menu',
));
$handler->override_option('title', 'Resource Guide');
$handler->override_option('empty', 'There are no resources to meet your request in this category.');
$handler->override_option('empty_format', '3');
$handler->override_option('items_per_page', 20);
$handler->override_option('distinct', 0);
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'resource-guide');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));

Hi, all

I confirm comment #14. Although I have no idea if this is related to access things. This distinct error _in my case_ only appear whenever I installed modules dealing with access. This happened to me either when I install User_Relationship_Node_Access or later Friendlist_Access. The easiest solution as suggested by merlin (http://drupal.org/node/284753) is to disable distinct from views for now until this issue here is resolved. Thanks

Is there a patch/hack for this?
As far as I can tell there have been a number of issues related to db_rewrite_sql (don't laugh if this is stating the obvious!), and it's hard for me to tell which posts/patches might apply to this particular problem (just started php class at NYU, hoping to get better at solving these things myself!).

I can confirm I have the same issue, when enabling the ACL module. I can also confirm removing 'Distinct' in my views stops the problems.

@cpelham : I tested with Drupal 6.4 and Views 2.0 rc4 ; so this issue is not solved

Component:node.module» mysql database
Status:Active» Needs work
StatusFileSize
new2.01 KB
Failed: Failed to apply patch.
[ View ]

Ok, I've pinpointed the problem. The function db_distinct_field in includes/database.mysql and includes/database.mysqli is very broken !

It will replace any occurence of (say) 'nid' found between the original 'SELECT' and a 'FROM' with 'DISTINCT(node.nid)'.

  • 'DISTINCT(node.nid) AS nid' will become 'DISTINCT(node.nid) AS DISTINCT(node.nid)
  • 'SELECT foo_node.nid FROM ...' becomes 'SELECT foo_ DISTINCT(node.nid) FROM ...'
  • 'SELECT ... FROM ... WHERE x IN (SELECT node.nid FROM ...)' becomes 'SELECT ... FROM ... WHERE x IN (SELECT DISTINCT(node.nid) FROM ...)'

I've seen all the cases above happen on my test sites. This is not a node.module issue, but a mysql database issue ; so I'm moving it there (not sure if it should go to 'database system' or to 'mysql database').

Attached is a patch (against Drupal 6.4) that fixes the problem on my test site. Anyone can test it to confirm ? It's not as terse as the original, and will most likely run slower, but at least it works ;)

StatusFileSize
new2.15 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch database_15.patch.
[ View ]

Sorry, same patch as above, but ran from the Drupal root rather than from the includes directory.

Shouldn't you have marked this needs review, not needs work?

I can confirm on 6.4 with views 6.x-2.0-rc4 and cck 6.x-2.0-rc8 that this cleared up the distinct(nid) problem that showed up only when I installed tac_lite.

(I haven't reviewed the code, though -- not quite sure what I would need to be on the lookout for.)

@merlinofchaos : I've put it on "needs work" because I think it can be optimised. The original code is just one preg_replace ; my version has 2 ifs, 2 preg_match, 1 preg_replace and 1 str_replace...

It's more of a proof of concept (ie. to show that this is indeed where the bug is).

Of course it's better to have slow working code than fast buggy code, but I'm sure my version can be optimised with a bit of thinking :)

Ok, just bear in mind that patches marked CNW will get less attention than CNR.

Status:Needs work» Needs review
StatusFileSize
new1.37 KB
new1.96 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch database_16.patch.
[ View ]

Ok, I've put my thinking cap on and wrote a more optimised version. I don't think this can be written with only one preg_replace as per the original (because you need to cater for the possibility of having sub-queries) ; this version has one if, one preg_match and one preg_replace.

It works on my site, and I've written a test script to check for various potential problems. Both are attached ; the latest patch as 'database.patch' and the test as 'test_patch.php' (this is just my own test - I'm yet to look up how Drupal tests work...)

Now set to 'code needs review' !

Applied the patch and all content was gone. Unapplied the patch, and all content was still gone. Rebuilt permissions and content was fine, views included. No more errors. Odd...

@Paulo Rodrigues : From what you say, it sounds like the patch affected the queries used by some access modules to evaluate grants. Which access module are you using ? The patch is stricter than the original code - it won't rewrite some queries that the original would (wrongly) have ; so maybe there's a problem with the access module expecting the old (wrong) behaviour ?

Or maybe there's a problem with the patch ! Would be great to be able to see the generated SQL to see what the problem is - did you get any mysql errors ? Can you try logging queries with the Developer module and looking at those ?

Interesting, when the official solution of this problem can be expected?

Just to subscribe and to say that have absolutely the same problem: views worked fine untill I've installed Content access module. After that the problem with DISTINCT(node.nid) appeared.

@netbear : Did you try the patch in #24 ? Would be great to have some more feedback on that one.

subscribing

@Anselm Heaton
No, I haven't yet, but I solved my problem with setting my view property DISTINCT to "Off".

how do you specificly set the view property to "off"

I ran into the same problem with Domain Access. Subscribing. Thx merlin for pointing me this way.
--
J.

Not to pile on too much, but does the patch also address the DISTINCT issue with pager queries, which is related?

#264092: pager count(*) error

Thanks man. It works!!! I use distinct for views and acces_content module. The errors are now gone!

Netbear at #31 said:

No, I haven't yet, but I solved my problem with setting my view property DISTINCT to "Off".

Ditto here. Thanks.

I had the same problem with Drupal 6.9 and Views 6.x-2.2 and the patch in #24 works great.

Ben

subscribing

Subscribing.

Subscribe.

patch #24 as workaround in views (pager issues with access modules and db_rewrite_sql - distinct error messages)
ACL http://drupal.org/node/367761
VIEWS http://drupal.org/node/366419
works ok for now, thanks

Subscribing.
Edit:
I also just applied the patch to a test copy of the site I'm working on. All seems to be well now, no more problems with DISTINCT.

Subscribing, this function has interfered with Views for as long as I have been using it (2-3 years). It would be *really* nice to get this fixed in D6 (and I assume the problem still exists in D7). It is a horrible problem that needlessly breaks perfectly good Views SQL.

I applied the patch to my site. Yes, the old problem is gone, however, a new conflict came up. One of my block built by views goes wrong. The content in this block has been showed twice. I don't think it's a good idea to change sth in the database.mysql.inc for a module. It do cause other problems!

This is a patch that is badly needed in many situations. If you're going to report that it creates other problems it would be better to provide complete details about how to reproduce any problems it caused. It's hard to imagine any way that this patch would cause a block to appear twice.

@tiantian20007 : Thanks for this. As KarenS says, this is an important patch, so if you could help us test it and fix potential problems with it, that would be very helpful. I can see two things happening :

1. You had not selected 'DISTINCT' in the view for your block, and it was set (wrongly) by the old code. The new code does not set it, so you have to specify manually in your view. Can you edit the view displayed in your block, and in "basic settings" set "Distinct" to "Yes".

Does that fix your problem ? If so the problem is not with the patch :)

2. If the above does not fix your problem, then the problem might reside with the patch not applying "DISTINCT" when it should. In that case could you install the Devel module, and enable query logging and displaying. Then view the same page, once with the patch and once without.

In both cases, grab the generated queries (will be displayed below your page normally) and send both versions here. If you have the skills to identify which query generates the block, please just send just both versions of that one, otherwise send it all and I'll try and work it out.

I know this is a bit of work, but if you have the time it would benefit the whole of the Drupal comunity :)

I have never gotten the error listed above, just when I clicked on distinct it wouldn't provide distinct results (it didn't seem to do anything really). I applied the patch in #24 successfully to root/includes/database.mysql.inc and root/includes/database.mysqli.inc but I am still not returning distinct results.

Any Ideas?

@dkane : You seem to be having a different issue. The bug mentioned here actually generates incorrect SQL (can happen with a combination of Views and access control modules). So the symptoms would be SQL errors, either visible on your screen or on your logs. If you do not have those symptoms, I suggest you open a different ticket for you issue (I would assume on the Views module).

As I recall (please correct me if I recall wrongly) Earl has written that the failures of the Distinct setting in the Views UI (such as displaying no nodes at all) is caused by a bug in the db_rewrie_sql. This particular thread is titled db_rewrite_sql causing issues [plural] with DISTINCT. This is an issue with distinct so I think it is correct to reference here in this thread and to await a solution via the patch that will eventually come from this thread.

Hey Everyone. I think this issue has to do with my bug. All of my views-lists are showing multiple items of the same node - in this case, blog entries. I believe it has to do with the caching associated with VotingAPI since the lists are sorted by votes - and the multiple entries will display because the exact same entries will have different vote numbers. I am using the Plus1 module to tally votes. Basically, I want to be able to select "Distinct" within my view to solve this problem. But whenever I select "Distinct" - I am given a SQL Error, which I copied below. The query and an export of the view are also copied below.

I believe the Patch should be a fix for this problem. Is that correct? If so, could someone just provide me with some more detailed help on how to install the patch. I'm a bit of a Drupal newbie here and learning as I go. Is it the .patch file I should be using or the .txt file? And am I copying and pasting that as a text file into my root directory or a different directory? Or am I appending that code as text to an existing file? Just a little unclear on what I'm supposed to be doing.

Thanks so much for your help. I really hope this solves my problem and I will definitely report back!!

Best,
Michael

Further Information on My Bug:

When I try to select Distinct on all these lists, I am given the following error:

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), users.name AS users_name, users.uid AS users_uid, n' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), users.name AS users_name, users.uid AS users_uid, node.title AS node_title, votingapi_cache_node_points_vote.value AS votingapi_cache_node_points_vote_value, node.created AS node_created FROM node node LEFT JOIN votingapi_cache votingapi_cache_node_points_vote ON node.nid = votingapi_cache_node_points_vote.content_id AND (votingapi_cache_node_points_vote.content_type = 'node' AND votingapi_cache_node_points_vote.value_type = 'points' AND votingapi_cache_node_points_vote.tag = 'vote') INNER JOIN term_node term_node ON node.vid = term_node.vid INNER JOIN users users ON node.uid = users.uid WHERE (term_node.tid = 28) AND (node.type in ('blog')) ORDER BY votingapi_cache_node_points_vote_value DESC LIMIT 0, 10 in /home/forens7/public_html/modules/views/includes/view.inc on line 731.

Here's the Query from the live preview:

SELECT node.nid AS nid, users.name AS users_name, users.uid AS users_uid, node.title AS node_title, votingapi_cache_node_points_vote.value AS votingapi_cache_node_points_vote_value, node.created AS node_created FROM node node  LEFT JOIN votingapi_cache votingapi_cache_node_points_vote ON node.nid = votingapi_cache_node_points_vote.content_id AND (votingapi_cache_node_points_vote.content_type = 'node' AND votingapi_cache_node_points_vote.value_type = 'points' AND votingapi_cache_node_points_vote.tag = 'vote') INNER JOIN term_node term_node ON node.vid = term_node.vid INNER JOIN users users ON node.uid = users.uid WHERE (term_node.tid = 28) AND (node.type in ('blog')) ORDER BY votingapi_cache_node_points_vote_value DESC

Here's an export of the view:

<?php
$view
= new view;
$view->name = 'Best_LD_Blogs';
$view->description = 'The most recommended blogs within the L-D category. ';
$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('relationships', array(
 
'votingapi_cache' => array(
   
'label' => 'Voting results',
   
'required' => 0,
   
'votingapi' => array(
     
'value_type' => 'points',
     
'tag' => 'vote',
     
'function' => '',
    ),
   
'id' => 'votingapi_cache',
   
'table' => 'node',
   
'field' => 'votingapi_cache',
   
'relationship' => 'none',
  ),
));
$handler->override_option('fields', array(
 
'name' => array(
   
'label' => 'Name',
   
'link_to_user' => 1,
   
'exclude' => 0,
   
'id' => 'name',
   
'table' => 'users',
   
'field' => 'name',
   
'relationship' => 'none',
  ),
 
'title' => array(
   
'label' => 'Title',
   
'link_to_node' => 1,
   
'exclude' => 0,
   
'id' => 'title',
   
'table' => 'node',
   
'field' => 'title',
   
'relationship' => 'none',
  ),
 
'value' => array(
   
'label' => 'Votes',
   
'set_precision' => FALSE,
   
'precision' => 0,
   
'decimal' => '.',
   
'separator' => ',',
   
'prefix' => '',
   
'suffix' => '',
   
'appearance' => '',
   
'exclude' => 0,
   
'id' => 'value',
   
'table' => 'votingapi_cache',
   
'field' => 'value',
   
'relationship' => 'votingapi_cache',
  ),
 
'created' => array(
   
'label' => 'Post date',
   
'date_format' => 'small',
   
'custom_date_format' => '',
   
'exclude' => 0,
   
'id' => 'created',
   
'table' => 'node',
   
'field' => 'created',
   
'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
 
'tid' => array(
   
'operator' => 'or',
   
'value' => array(
     
'28' => '28',
    ),
   
'group' => '0',
   
'exposed' => FALSE,
   
'expose' => array(
     
'operator' => FALSE,
     
'label' => '',
    ),
   
'type' => 'select',
   
'vid' => '2',
   
'id' => 'tid',
   
'table' => 'term_node',
   
'field' => 'tid',
   
'hierarchy' => 0,
   
'relationship' => 'none',
   
'reduce_duplicates' => 0,
  ),
 
'type' => array(
   
'operator' => 'in',
   
'value' => array(
     
'blog' => 'blog',
    ),
   
'group' => '0',
   
'exposed' => FALSE,
   
'expose' => array(
     
'operator' => FALSE,
     
'label' => '',
    ),
   
'id' => 'type',
   
'table' => 'node',
   
'field' => 'type',
   
'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
 
'type' => 'none',
));
$handler->override_option('title', 'Lincoln Douglas Debate');
$handler->override_option('header', '<br>
<h2><table> <tr> <td> <strong> Best L-D Blogs </strong></td> <td> <a href="http://www.forensicscommunity.com/forum/2"> Discuss L-D Debate </a></td>  </table> </h2> <br>
'
);
$handler->override_option('header_format', '2');
$handler->override_option('header_empty', 1);
$handler->override_option('footer', '<br> <br>
<br> <br> <center> <h2> <b> Lincoln-Douglas Debate Videos </b> </h2> <br> </center>
<center>
<?php
   $params[\'width\'] = 652;
   $params[\'height\'] = 432;
   $params[\'playlist\'] = \'ld_videos\';
   print dashplayer_get_player($params);
</center>
'
);
$handler->override_option('footer_format', '3');
$handler->override_option('footer_empty', 1);
$handler->override_option('distinct', 0);
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
 
'grouping' => '',
 
'override' => 1,
 
'sticky' => 0,
 
'order' => 'desc',
 
'columns' => array(
   
'name' => 'name',
   
'title' => 'title',
   
'value' => 'value',
   
'created' => 'created',
  ),
 
'info' => array(
   
'name' => array(
     
'sortable' => 0,
     
'separator' => '',
    ),
   
'title' => array(
     
'sortable' => 0,
     
'separator' => '',
    ),
   
'value' => array(
     
'sortable' => 0,
     
'separator' => '',
    ),
   
'created' => array(
     
'sortable' => 0,
     
'separator' => '',
    ),
  ),
 
'default' => 'value',
));
$handler = $view->new_display('block', 'Block', 'block_1');
$handler->override_option('block_description', '');
$handler->override_option('block_caching', -1);
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'lincolndouglas');
$handler->override_option('menu', array(
 
'type' => 'none',
 
'title' => '',
 
'description' => '',
 
'weight' => 0,
 
'name' => 'navigation',
));
$handler->override_option('tab_options', array(
 
'type' => 'none',
 
'title' => '',
 
'description' => '',
 
'weight' => 0,
));
?>

Okay, nevermind my last post.

I posted the issue as a bug earlier than this comment, and I found out that it was a very simple easy fix that doesn't need a patch.

All I was forgetting to do was to set my Voting API Relationship to something other than "no filtering" within the View. Silly me. I'm happily relieved it was a simple issue. Ignore my last comment. Thanks anyway!!

This issue is getting terribly polluted with unrelated issues. I think we have to ignore the comment in #44 since it provided no information and there was not follow up to produce any evidence that this patch caused any problems.

The current patch is the one in #24. @Anselm Heaton, can you re-roll and add a simpletest to your patch using your examples? Then we would not only fix this problem but have a method to keep it from popping up again.

Version:6.x-dev» 7.x-dev

And we need to fix it in HEAD first, then backport it.

Status:Needs review» Needs work

The last submitted patch failed testing.

Version:7.x-dev» 6.x-dev

db_rewrite_sql() is not going to be in 7.x anymore (thank goodness... no more SQL string regex!). This is only going to affect 6.x.

Status:Needs work» Needs review

Yay! I didn't realize that db_rewrite_sql() is gone. Then we don't need tests (wouldn't hurt either, but not required). Resetting status because it failed testing because it isn't a D7 patch.

I applied it to a site and it removed the errors while seemingly not breaking any of the access protection.
Hasn't thoroughly tested it though - but I think it's a very important/urgent patch.

Priority:Normal» Critical

I agree, the breakage here is actually critical.

Before I upgraded from 6.9 to 6.10, my Views worked properly. After upgrading, I got this error in some of my views.

http://drupal.org/node/404482

@bigheadfish, your report is muddying the waters here. We need to figure out if this patch is working. You didn't apply the patch, you're just re-reporting the original problem. If you want to help fix it, apply the patch and see if it fixes your problem.

I did not try the patch.

I use str_replace (safer than preg_replace) right before line 731 in view.inc. It is definitely a very bad practice but it works for now. I will wait till the official bug fix is released.

StatusFileSize
new5.18 KB

Here is SimpleTest test file for this problem. For the moment it contains 12 unit tests, all testing the db_distinct_field function. Feel free to add more ;)

I couldn't get SimpleTest to pick it up from the includes/ folder (in Drupal 6), so I've added (randomly) to the node module, and the group 'Core'. Sorry this is the first time I try SimpleTest, let me know if you know what I did wrong ;)

Without the patch :
5 passes
7 failures

With the patch in #24 :
12 passes

It fixes the view problem but two other problems popped out:

1. some of my primary link menu items didn't appear. it caused an SQL error:

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 &#039;FROM node n WHERE n.status = 1 AND n.nid IN (7, 18, 282)&#039; at line 1 query: SELECT FROM node n WHERE n.status = 1 AND n.nid IN (7, 18, 282) in /home/test/public_html/includes/menu.inc on line 991.
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 &#039;FROM node n WHERE n.status = 1 AND n.nid IN (183)&#039; at line 1 query: SELECT FROM node n WHERE n.status = 1 AND n.nid IN (183) in /home/test/public_html/includes/menu.inc on line 991.

If you notice, no SELECT field is there.

2. In the forums, no posts are listed under a certain forum even though there are supposed to be items underneath and when looking in site/forum, no latest posts are recognized. SQL error:
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 &#039; r.tid, n.title, n.type, n.sticky, u.name, u.uid, n.created AS timestamp, n.comm&#039; at line 1 query: SELECT , r.tid, n.title, n.type, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, IF(l.last_comment_uid != 0, cu.name, l.last_comment_name) AS last_comment_name, l.last_comment_uid, l.comment_count AS num_comments, f.tid AS forum_tid FROM node_comment_statistics l INNER JOIN node n ON n.nid = l.nid INNER JOIN users cu ON l.last_comment_uid = cu.uid INNER JOIN term_node r ON n.vid = r.vid INNER JOIN users u ON n.uid = u.uid INNER JOIN forum f ON n.vid = f.vid WHERE n.status = 1 AND r.tid = 117 ORDER BY n.sticky DESC, l.last_comment_timestamp DESC, n.created DESC LIMIT 0, 25 in /home/norkisgroup/public_html/modules/forum/forum.module on line 588

Same problem as 1st, there's a problem in the select fields.

Component:mysql database» database system

This critical bug is not to hard to tickle if you're using views and node access. I've been bitten a few times by this.

The the problems easily waterfalls because if you can't use a module such as workflow_access, you're not really able to establish sane read/write permissions based on workflow states of a given node. You must either give 'administer nodes' permission to editor/moderator type roles or set node.status to TRUE prematurely to allow these people to view nodes (which they can already edit if they have 'edit any XXX content' permission, and this of course means that anyone can view nodes that may not be considered public in terms of workflow state. eeep.

This problem is pretty easy to reproduce:

* enable views, workflow and workflow access
* apply a workflow to nodetype XXXX
* create a node view for XXXX nodes that uses the 'distinct' filter.
* access the created view

subscribing

I applied the patch in #24 and it appears to fix the DISTINCT issues I was having, but it appears to be creating a new problem.

The following error looks like it is coming from the simplenews module:

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 ' n.title, sn.s_status FROM node n INNER JOIN term_node t ON n.vid = t.vid INNER ' at line 1 query: SELECT , n.title, sn.s_status FROM node n INNER JOIN term_node t ON n.vid = t.vid INNER JOIN simplenews_newsletters sn ON n.nid = sn.nid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'workflow_access') OR (na.gid = 3 AND na.realm = 'workflow_access') OR (na.gid = 703 AND na.realm = 'workflow_access_owner'))) AND ((n.moderate != 1 OR n.uid = 703)) AND ( (t.tid = 25 AND n.status = 1) )ORDER BY n.created DESC LIMIT 0, 5 in [DRUPAL_ROOT]/sites/all/modules/simplenews/simplenews.module on line 1234.

subscribing

@radj, @ntt

You seem to be having the same issue. It's not easy to reproduce (my forums work well) - do you think you could post the SQL statement WITHOUT the patch ? Thanks :)

In the mean time I'll see if I can work out in code cases where it would get this wrong.

@radj, @ntt :

I've tried the queries generated by menu.inc and simplenews.inc, and on my site they get rewritten properly. So we're having a clash with another module here; it's not really possible to know which. db_distinct_field is called after hook_db_rewrite_sql, so I assume one module is rewriting the query in a way that confuses the patched db_distinct_field.

Though I can't see how that happens, so I'll really need to look at one of the queries in question. Could you try to work out which query is failing ? If you have a debugger, you could set a watchpoint so see when the query gets rewritten badly. Otherwise, you could try the following old style debugging :

- edit includes/database.inc ;
- find "function db_rewrite_sql" and locate

  if ($distinct) {
    $query = db_distinct_field($primary_table, $primary_field, $query);
  }

- Replace that with :
  if ($distinct) {
    $old_query = $query;
    $query = db_distinct_field($primary_table, $primary_field, $query);
    if (preg_match('/SELECT\s+(FROM|,)/', $query) {
      echo "<h1>Rewrite error!</h1>Query '$old_query' became '$query' for '$primary_table . $primary_field' .<br/>";
    }
  }

This should output some stuff - can you copy it here (or send it to me directly).

Thanks :)

Hi, I was having a serious issue with Drupal 6.10, Views 6.x-2.5 and Taxonomy Access Control 6.x-1.x-dev

When TAC was enabled, views did not work at all (no pager, no view, nothing), except one view of users, because of the famous query rewrite issue. With patch in #24 everything works again and I haven't had any collateral damages.

Thanks Anselm!

Patch #24 worked for me! Initially it did not work and I thought there is some problem in the patch, but applying the patch and then clearing the cache solved my issue.

Thanks

Patch #24 also worked for me. I am running:
D6.10
ACL 6.x-1.0-beta4
Forum Access 6.x-1.0-beta3
Calendar 6.x-2.1
Date 6.x-2.x-dev (Patched as in #409476: date_timezone_set() & date_format() error when editing CCK date repeating fields.)

StatusFileSize
new16.03 KB

Addition: couldn't print view to page; problematic view attached as text file. Hope that's ok. It's a pretty standard calendar view.

Just to subscribe

The patch works for me in both 6.9 and 6.10.

Thanks for the great work on this.

subscribe

scrubscribulating....

The patch in #24 worked great for me to fix the problems I found using Organic Groups access control in #384650: Node distinct broken when using node access control. I haven't run into any other problems so far.

#66 seems to be coming from 'workflow_access'. I don't know anything about that module.

Status:Needs review» Reviewed & tested by the community

Also, it is almost certain that #66 is reporting a problem that already exists, not a new problem caused by this patch.

This is a huge problem for many. This patch appears to fix many critical problems and I'd vote to commit it even if it doesn't find and fix every problem. Then there could be follow-on patches for additional problems if someone finds a solution for them.

If we get new information that #66 is not also broken in the current, unpatched, code, then the code needs work, otherwise I would venture to mark it ready to commit.

Status:Reviewed & tested by the community» Needs work

Bah, reading more carefully, #66 says it is a new problem. We need more information about that I guess. Looks like is some combination of Workflow access and Simplenews, but there is not enough information in the report for anyone to be able to test it. In particular, a copy of the view so someone is able to try to reproduce it.

I'm not using any of those modules, so I can't do it.

There is an existing issue in the workflow issue queue that says workflow access is broken now in the same way all the other modules are broken, which should imply that it needs the same fix. I've posted on that issue #354745: Workflow Access breaks Views that utilize the "distinct" basic setting to try to get some users of that module to come and test this patch.

subscribe

subscribe

Subscribing.

So this patch is worth trying. Works for me!

#24 worked for 6.12 and saved my life.

This should be, has to be, must be committed to the next release.

Tx.

Subscribe, need to follow up when core is updated.

Subscribing

subscribin'

I had a the following problem: "Module "content_access" in combination with view "date_browser" give MYSQL error" (see: http://drupal.org/node/465146 )

Patch from #24 solved the probeln for me.

  • Drupal 6.12
  • Views 6.x-2.5
  • Content Access 6.x-1.1
  • Patch @ #24 worked for me too. Was having problems with Views Calc.

    Patch @#24 worked for my case too.

    I have a simple view listing multiple imagefield fields from different nodes using user:User ID from URL as argument.
    Before patching the pager wouldn't show up in the view. Now it works properly and no other issues arose, so far.
    For the record: I was not using distinct setting in my view (distinct:no)
    Thanks a lot!

    StatusFileSize
    new1.96 KB

    Let's try to pass the automated test....

    subscribe

    Patch in #24 fixed it for me.

    The patch in #24/#93 (same patch) fixed it for me too. I haven't seen any adverse affects yet.

    I've applied #24 patch and it seems to work. haven't done extensive testing.

    I hope this critical problem catches the attention of the core team. It's a pretty significant bug.

    subscribing

    Status:Needs work» Needs review

    OK, we have lots and lots of reports that #24 works to fix the problem. There are, unfortunately, a couple reports saying it didn't. But no one who reported that it didn't work provided enough information for anyone to do anything with, and none of them have come back to provide more clarification. So effectively, they derailed this patch without doing anything to help fix the problem.

    I'm going to try marking this back to 'needs review' and see where it goes from here. I also blogged about it and posted a temporary workaround at http://www.lullabot.com/blog/views-distinct-node-access-problems, which will hopefully be helpful to people with this problem and get more eyes on this issue.

    I will try to carve out some time to run some tests using Domain Access.

    OK, we have lots and lots of reports that #24 works to fix the problem. There are, unfortunately, a couple reports saying it didn't. But no one who reported that it didn't work provided enough information for anyone to do anything with, and none of them have come back to provide more clarification. So effectively, they derailed this patch without doing anything to help fix the problem.

    What would be helpful from others at this point?

    Testing against a variety of node access modules.

    Someone should test against OG and TAC as well. You need the folks who use and maintain Node Access modules to test this.

    #25, #63, #66 were reports that it broke things, but none of them came back to respond with more information that could be used to determine whether there is a definite problem or not. If we could get some resolution on those it would help a lot, otherwise at some point we will, I guess, just have to ignore them.

    For the rest, saying 'it works' isn't particularly helpful. Saying 'it worked for me to fix a broken view when using the XXX node access module' is much more useful. Saying 'it fixed my broken query that looked like XXXX and correctly rewrote it to YYYY' is even better.

    Subscribing.

    When I said the patch fixed my problem in #96 my problem was that using distinct in a node view was returning no records when it should have been.

    I am not using any node access modules but I am using og, og_user_roles and og_vocab.

    Organic groups is a node access module.

    subscribing

    subscribing

    @KarenS

    This is on my list to test as part of the Node Access cleanup for D7, so I should get to poke at it this week.

    I have two nearly identical servers, both running the same version of Drupal & modules against the same database back-end. Organic Groups is installed but the problem query was a Taxonomy query. Here are all the modules installed (not all are enabled):

    calendar
    cck
    content_access
    customerror
    date
    fckeditor
    filefield
    imce
    jquerymenu
    menu_per_role
    node_privacy_byrole
    og
    panels
    pathauto
    rules
    token
    views
    workflow

    I applied the patch on only one of the servers. Here are the queries generated by the View:

    SQL with Distinct set to No

    SELECT term_data.name AS term_data_name,
       COUNT(node.nid) AS num_records
    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
    WHERE node.status <> 0 OR (node.uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0) OR ***ADMINISTER_NODES*** = 1
    GROUP BY term_data_name
      ORDER BY term_data_name ASC

    SQL with Distinct set to Yes

    SELECT term_data.name AS term_data_name,
       COUNT(DISTINCT(node.nid)) AS num_records
    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
    WHERE node.status <> 0 OR (node.uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0) OR ***ADMINISTER_NODES*** = 1
    GROUP BY term_data_name
      ORDER BY term_data_name ASC

    Result on unpatched server

    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), term_data.name AS term_data_name FROM node node LEFT J' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), term_data.name AS term_data_name 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 INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'content_access_author') OR (na.gid = 1 AND na.realm = 'content_access_rid') OR (na.gid = 0 AND na.realm = 'og_public'))) AND ( (node.status <> 0 OR (node.uid = 0 AND 0 <> 0) OR 0 = 1) AND (term_data.name = 'Materials Management') )ORDER BY term_data_name ASC in /usr/local/drupal/sites/all/modules/views/includes/view.inc on line 759.

    Result on patched server

    No error

    subscribing

    subscribing

    Confirming that the patch in #24/93 fixed my problem running a view using Drupal 6.12, Views 2.6, OG 2.0 RC3.

    A view using "Distinct" set to yes caused an error. There were two associated queries being rewritten as below:

    SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.type AS node_type, node.vid AS node_vid, node.title AS node_title FROM node node LEFT JOIN content_field_workshop_date node_data_field_workshop_date ON node.vid = node_data_field_workshop_date.vid WHERE (node.status <> 0) AND (node.type IN ('product','workshop')) AND (node.type in ('workshop')) ORDER BY node_vid ASC ) count_alias

    SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.type AS node_type, node.vid AS node_vid, node.title AS node_title FROM node node LEFT JOIN content_field_workshop_date node_data_field_workshop_date ON node.vid = node_data_field_workshop_date.vid WHERE (node.status <> 0) AND (node.type IN ('product','workshop')) AND (node.type in ('workshop')) ORDER BY node_vid ASC LIMIT 0, 25

    These are the same queries after the patch:

    SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid, node.type AS node_type, node.vid AS node_vid, node.title AS node_title FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN content_field_workshop_date node_data_field_workshop_date ON node.vid = node_data_field_workshop_date.vid WHERE (node.status <> 0) AND (node.type IN ('product','workshop')) AND (node.type in ('workshop')) AND (term_node.tid = 3) ORDER BY node_vid ASC ) count_alias

    SELECT DISTINCT(node.nid) AS nid, node.type AS node_type, node.vid AS node_vid, node.title AS node_title FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN content_field_workshop_date node_data_field_workshop_date ON node.vid = node_data_field_workshop_date.vid WHERE (node.status <> 0) AND (node.type IN ('product','workshop')) AND (node.type in ('workshop')) AND (term_node.tid = 3) ORDER BY node_vid ASC LIMIT 0, 25

    I applied patch #93 and now Views works...

    *Update*
    Nevermind what I said about not working :)

    This issue is not about duplicates, it is about the query being rewritten and failing to run at all because it is invalid. You have a valid query that doesn't produce the results you intended. You duplicates are a natural outcome of some kinds of views that use taxonomy. That has nothing to do with this issue.

    I tested the patch and it is working! I also have no duplicates.

    Hoping to move the process along by encouraging better feedback...

    If you're submitting test results, please see these comments regarding what is and isn't helpful:

    http://drupal.org/node/284392#comment-1723396
    http://drupal.org/node/284392#comment-1723482
    http://drupal.org/node/284392#comment-1750512

    Subscribe. #93 worked for me as well. Thanks tassoman!

    Also fixes Drupal 6.13...

    How the hell is this not committed yet?

    Version:6.x-dev» 6.12

    Also experiencing this problem, though only when I move my view from the views ui into my module.

    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_galleries_gallery.gid AS node_galleries_gallery_gid ' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_galleries_gallery.gid AS node_galleries_gallery_gid FROM node node INNER JOIN users users ON node.uid = users.uid INNER JOIN node_galleries node_galleries_gallery ON node.nid = node_galleries_gallery.gid WHERE (users.uid = 1) AND (node.type in ('personal_gallery')) LIMIT 0, 10 in /Users/derek/Code/www/install_test/sites/all/modules/contrib/views/includes/view.inc on line 755.

      $view = new view;
      $view->name = 'gallery_views';
      $view->description = t('Gallery views - Public galleries, Personal gallery, etc');
      $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(
        'gid' => array(
          'label' => '',
          'alter' => array(
            'alter_text' => 0,
            'text' => '',
            'make_link' => 0,
            'path' => '',
            'link_class' => '',
            'alt' => '',
            'prefix' => '',
            'suffix' => '',
            'help' => '',
            'trim' => 0,
            'max_length' => '',
            'word_boundary' => 1,
            'ellipsis' => 1,
            'strip_tags' => 0,
            'html' => 0,
          ),
          'link_to_node' => 1,
          'node_gallery_setting' => 'node_gallery_gallery',
          'exclude' => 0,
          'id' => 'gid',
          'table' => 'node_galleries_gallery',
          'field' => 'gid',
          'override' => array(
            'button' => 'Override',
          ),
          'relationship' => 'none',
        ),
      ));
      $handler->override_option('access', array(
        'type' => 'none',
      ));
      $handler->override_option('cache', array(
        'type' => 'none',
      ));
      $handler->override_option('distinct', 0);
      $handler->override_option('row_plugin', 'node');
      $handler = $view->new_display('page', 'My Galleries', 'page_1');
      $handler->override_option('fields', array(
        'gid' => array(
          'label' => '',
          'alter' => array(
            'alter_text' => 0,
            'text' => '',
            'make_link' => 0,
            'path' => '',
            'link_class' => '',
            'alt' => '',
            'prefix' => '',
            'suffix' => '',
            'help' => '',
            'trim' => 0,
            'max_length' => '',
            'word_boundary' => 1,
            'ellipsis' => 1,
            'strip_tags' => 0,
            'html' => 0,
          ),
          'link_to_node' => 1,
          'node_gallery_setting' => 'node_gallery_gallery',
          'exclude' => 0,
          'id' => 'gid',
          'table' => 'node_galleries_gallery',
          'field' => 'gid',
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
        ),
      ));
      $handler->override_option('filters', array(
        'uid_current' => array(
          'operator' => '=',
          'value' => '1',
          'group' => '0',
          'exposed' => FALSE,
          'expose' => array(
            'operator' => FALSE,
            'label' => '',
          ),
          'id' => 'uid_current',
          'table' => 'users',
          'field' => 'uid_current',
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
        ),
        'type' => array(
          'operator' => 'in',
          'value' => array(
            'node_gallery_gallery' => 'node_gallery_gallery',
          ),
          'group' => '0',
          'exposed' => FALSE,
          'expose' => array(
            'operator' => FALSE,
            'label' => '',
          ),
          'id' => 'type',
          'table' => 'node',
          'field' => 'type',
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
        ),
      ));
      $handler->override_option('title', 'My Galleries');
      $handler->override_option('use_pager', 'mini');
      $handler->override_option('distinct', 1);
      $handler->override_option('row_plugin', 'fields');
      $handler->override_option('path', 'gallery');
      $handler->override_option('menu', array(
        'type' => 'none',
        'title' => '',
        'description' => '',
        'weight' => 0,
        'name' => 'navigation',
      ));
      $handler->override_option('tab_options', array(
        'type' => 'none',
        'title' => '',
        'description' => '',
        'weight' => 0,
      ));
      $handler = $view->new_display('page', 'Public Galleries', 'page_2');
      $handler->override_option('fields', array(
        'gid' => array(
          'label' => '',
          'alter' => array(
            'alter_text' => 0,
            'text' => '',
            'make_link' => 0,
            'path' => '',
            'link_class' => '',
            'alt' => '',
            'prefix' => '',
            'suffix' => '',
            'help' => '',
            'trim' => 0,
            'max_length' => '',
            'word_boundary' => 1,
            'ellipsis' => 1,
            'strip_tags' => 0,
            'html' => 0,
          ),
          'link_to_node' => 1,
          'node_gallery_setting' => 'node_gallery_gallery',
          'exclude' => 0,
          'id' => 'gid',
          'table' => 'node_galleries_gallery',
          'field' => 'gid',
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
        ),
        'title' => array(
          'label' => '',
          'alter' => array(
            'alter_text' => 0,
            'text' => '',
            'make_link' => 0,
            'path' => '',
            'link_class' => '',
            'alt' => '',
            'prefix' => '',
            'suffix' => '',
            'help' => '',
            'trim' => 0,
            'max_length' => '',
            'word_boundary' => 1,
            'ellipsis' => 1,
            'strip_tags' => 0,
            'html' => 0,
          ),
          'link_to_node' => 0,
          'exclude' => 0,
          'id' => 'title',
          'table' => 'node',
          'field' => 'title',
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
        ),
      ));
      $handler->override_option('arguments', array(
        'name' => array(
          'default_action' => 'ignore',
          'style_plugin' => 'default_summary',
          'style_options' => array(),
          'wildcard' => 'all',
          'wildcard_substitution' => 'All',
          'title' => '%1\'s Galleries',
          'breadcrumb' => '',
          'default_argument_type' => 'fixed',
          'default_argument' => '',
          'validate_type' => 'none',
          'validate_fail' => 'not found',
          'glossary' => 0,
          'limit' => '0',
          'case' => 'none',
          'path_case' => 'none',
          'transform_dash' => 0,
          'id' => 'name',
          'table' => 'users',
          'field' => 'name',
          'validate_user_argument_type' => 'uid',
          'validate_user_roles' => array(
            '2' => 0,
          ),
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
          'default_options_div_prefix' => '',
          'default_argument_user' => 0,
          'default_argument_fixed' => '',
          'default_argument_php' => '',
          'validate_argument_node_type' => array(
            'webform' => 0,
            'poll' => 0,
            'advpoll_binary' => 0,
            'advpoll_ranking' => 0,
            'forum' => 0,
            'boatingrssfeeditem' => 0,
            'feed' => 0,
            'node_gallery_gallery' => 0,
            'node_gallery_image' => 0,
            'page' => 0,
            'profile' => 0,
            'personal_gallery' => 0,
            'personal_gallery_image' => 0,
            'rotato_gallery' => 0,
            'rotato_gallery_image' => 0,
            'story' => 0,
          ),
          'validate_argument_node_access' => 0,
          'validate_argument_nid_type' => 'nid',
          'validate_argument_vocabulary' => array(
            '1' => 0,
            '2' => 0,
          ),
          'validate_argument_type' => 'tid',
          'validate_argument_transform' => 0,
          'validate_user_restrict_roles' => 0,
          'validate_argument_php' => '',
        ),
      ));
      $handler->override_option('filters', array(
        'type' => array(
          'operator' => 'in',
          'value' => array(
            'node_gallery_gallery' => 'node_gallery_gallery',
          ),
          'group' => '0',
          'exposed' => FALSE,
          'expose' => array(
            'operator' => FALSE,
            'label' => '',
          ),
          'id' => 'type',
          'table' => 'node',
          'field' => 'type',
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
        ),
      ));
      $handler->override_option('title', 'Public Galleries');
      $handler->override_option('distinct', 1);
      $handler->override_option('row_plugin', 'fields');
      $handler->override_option('path', 'gallery/%');
      $handler->override_option('menu', array(
        'type' => 'none',
        'title' => '',
        'description' => '',
        'weight' => 0,
        'name' => 'navigation',
      ));
      $handler->override_option('tab_options', array(
        'type' => 'none',
        'title' => '',
        'description' => '',
        'weight' => 0,
      ));
      $handler = $view->new_display('page', 'Personal Galleries', 'page_3');
      $handler->override_option('filters', array(
        'uid_current' => array(
          'operator' => '=',
          'value' => '1',
          'group' => '0',
          'exposed' => FALSE,
          'expose' => array(
            'operator' => FALSE,
            'label' => '',
          ),
          'id' => 'uid_current',
          'table' => 'users',
          'field' => 'uid_current',
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
        ),
        'type' => array(
          'operator' => 'in',
          'value' => array(
            'personal_gallery' => 'personal_gallery',
          ),
          'group' => '0',
          'exposed' => FALSE,
          'expose' => array(
            'operator' => FALSE,
            'label' => '',
          ),
          'id' => 'type',
          'table' => 'node',
          'field' => 'type',
          'override' => array(
            'button' => 'Use default',
          ),
          'relationship' => 'none',
        ),
      ));
      $handler->override_option('title', 'Personal Galleries');
      $handler->override_option('distinct', 1);
      $handler->override_option('row_plugin', 'fields');
      $handler->override_option('path', 'user/galleries');
      $handler->override_option('menu', array(
        'type' => 'none',
        'title' => '',
        'description' => '',
        'weight' => 0,
        'name' => 'navigation',
      ));
      $handler->override_option('tab_options', array(
        'type' => 'none',
        'title' => '',
        'description' => '',
        'weight' => 0,
      ));

    Can we try to run this patch test (#93) for Drupal 6 HEAD?

    StatusFileSize
    new1.96 KB
    FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-database-distinct.patch.
    [ View ]
    new2.06 KB

    I cannot replicate the error in #120. That needs to be researched as a separate issue.

    Tested against 6.13, works as expected. There were some minor code-style issues with the patch handling of string concatenation (corrected).

    Attached is my test View and the revised patch. Below are the queries. Testing Domain Access 6.x.2.0rc8, a node access module.

    Without patch, no distinct:
    SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'domain_site') OR (na.gid = 3 AND na.realm = 'domain_id'))) LIMIT 0, 10
    Without patch, with distinct
    SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.title AS node_title, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'domain_site') OR (na.gid = 3 AND na.realm = 'domain_id'))) LIMIT 0, 10
    Error: You have an error in your SQL syntax
    With patch, no distinct
    SELECT node.nid AS nid, node.title AS node_title, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'domain_site') OR (na.gid = 3 AND na.realm = 'domain_id'))) LIMIT 0, 10
    With patch, distinct
    SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'domain_site') OR (na.gid = 3 AND na.realm = 'domain_id'))) LIMIT 0, 10
    No error.

    Patch applies, tests clean. I wonder if the code can be optimized a little.

    Does this bug not affect pgSQL? Is there a reason not to re-use code from database.pgsql,inc?

    That aside, looks good.

    Version:6.12» 6.x-dev

    Subscribing. Patch #93 indeed works. Tested on 6.12 with Content Access. Lovely ! Thanks!

    Status:Needs review» Reviewed & tested by the community

    I applied the patch and it works with forum_access and lots of different Views2 distinct queries (which currently breaks horribly on Drupal 6.13).

    Can we get Dries or Gabor to put this in the que for Drupal 6.14 ?

    Still needs a changelog.txt addition, otherwise RTBC.

    Josh

    Same story as others - without the patch we get *lots* of errors like this on Views set to distinct=Yes:

    <?php
    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_node_data_field_thumbnail_node_data_field_image.fiel' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_node_data_field_thumbnail_node_data_field_image.field_image_fid AS node_node_data_field_thumbnail_node_data_field_image_field_image_fid, node_node_data_field_thumbnail_node_data_field_image.field_image_list AS node_node_data_field_thumbnail_node_data_field_image_field_image_list, node_node_data_field_thumbnail_node_data_field_image.field_image_data AS node_node_data_field_thumbnail_node_data_field_image_field_image_data, node_node_data_field_thumbnail.nid AS node_node_data_field_thumbnail_nid, node_node_data_field_thumbnail.type AS node_node_data_field_thumbnail_type, node_node_data_field_thumbnail.vid AS node_node_data_field_thumbnail_vid, node.title AS node_title, nodequeue_nodes_node.position AS nodequeue_nodes_node_position, node_counter.totalcount AS node_counter_totalcount FROM node node LEFT JOIN nodequeue_nodes nodequeue_nodes_node ON node.nid = nodequeue_nodes_node.nid AND nodequeue_nodes_node.qid = 6 LEFT JOIN content_field_thumbnail node_data_field_thumbnail ON node.vid = node_data_field_thumbnail.vid INNER JOIN node node_node_data_field_thumbnail ON node_data_field_thumbnail.field_thumbnail_nid = node_node_data_field_thumbnail.nid LEFT JOIN content_type_image node_node_data_field_thumbnail_node_data_field_image ON node_node_data_field_thumbnail.vid = node_node_data_field_thumbnail_node_data_field_image.vid LEFT JOIN node_counter node_counter ON node.nid = node_counter.nid WHERE (node.status <> 0 OR (node.uid = 1 AND 1 <> 0) OR 1 = 1) AND (node.type in ('gallery')) ORDER BY nodequeue_nodes_node_position DESC, node_counter_totalcount DESC LIMIT 0, 10 in /mysite/sites/all/modules/platform/views/includes/view.inc on line 755.
    ?>

    Running the patch in #93 removed such errors.

    The patch in #93 is outdated and has code style issues.

    The current patch to review is #122.

    Tested #122 and works. Ready to go?

    agentrickard / meba,

    I believe this patch is RTBC, thus, it does not require any more reviews (though, more are appreciated)... it just needs to get into the Drupal 6.14 branch...

    Josh

    Subscribe. Perhaps applying the patch to 6.x would bring any problems with it out of the woodwork >:)

    Status:Reviewed & tested by the community» Fixed

    Committed to Drupal 6, thanks.

    Version:6.x-dev» 5.x-dev
    Status:Fixed» Patch (to be ported)

    I think we need to (can we?) backport this to Drupal 5.

    Version:5.x-dev» 6.13
    Status:Patch (to be ported)» Active

    Works as expected!

    Version:6.13» 5.x-dev
    Status:Active» Patch (to be ported)

    Restoring status.

    I have a variant of this issue that the patch doesn't fix. Using node_privacy_byrole or taxonomy_access, I get a mysql error. This is the query for taxonomy_access:

    SELECT n.title, DISTINCT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'term_access'))) AND ( tn.tid in (1) AND n.status=1 )ORDER BY n.created DESC LIMIT 0, 1

    and it fails because n.title is before DISTINCT(n.nid)

    How can I fix it?

    Open a new issue for one of those modules, please.

    subscribing

    Actually, we still seem to have a problem here!
    I applied #122 to Drupal 6.13, but using Content Access module the entire "DISTINCT" command gets stripped off.

    So, the query begins with:
    SELECT node.nid AS nid

    instead of:
    SELECT DISTINCT(node.nid) AS nid

    I guess this is caused by D6 core and is not a Content Access issue?

    Anyone else experiencing this problem?

    @extect : This patch has now been commited to Drupal 6.13 - so reporting issues here is not helpful and will be ignored. Please upgrade to Drupal 6.13 and if you still see a problem, please report it separately.

    I think it has been committed to the 6.x-dev version which will be the upcoming 6.14, not the existing 6.13?

    I think it has been committed to the 6.x-dev version which will be the upcoming 6.14, not the existing 6.13?

    Yes you're correct - this is not included in 6.13, but has been commit to 6.x-dev. However my comment stands : this is now part of Drupal, not a patch anymore, so issues with it should be filed as new bugs against 6.x-dev ; you're unlikely to get a developer response here.

    Just confirming patch@#122 works on D5 5.19 (database.mysql.inc,v 1.66.2.3).

    Patch manually applied

    Thanks folks!!!

    @opteronmx

    Can you roll a D5 patch based on what you hand-rolled, please?

    I would like to inform you of grave performance issues under Drupal 6.13:

    SQL performance issue : forum_get_topics executes in 720 ms
    http://drupal.org/node/558894

    SQL performance issue: SQL forum bloc executes in 450ms
    http://drupal.org/node/558886

    This seems to me completely *** LOL *** that db_rewrite_sql rewrites a query adding arbitrary DISTINCT on the {nodes} table.

    On large installations like mine (500.000 page), the parser will start build a huge distinct index on the {node} table.
    Then the parser processes the index.

    This eats-up a tremendous part of CPU power and index memory, not to say time.
    I wonder why your guys ever thought of such a solution.

    It is the developer job to build an ANSI query starting
    usually LEFT of the query (where unique records reside)
    and then going right using INNER JOINS or LEFT JOINs.

    Was this issue fixed in PostgreSQL-DEV?
    Was it also fixed in D7?

    Please inform me ASAP.
    I am migrating a 500000 PHPBB post and my quad core will probably explode when it gets online with 2000 users.

    Can you remove this creazy DISTINCT automation or not?

    This is always what happens when using MySQL.
    Franckly, you should always use PostgreSQL and read detailed logs to understand how the parser works.

    In most cases unless you have more than 1000 users online, there is no need for caching if SQL queries are executed normally.

    Distinct should be set by hand by each developer on request, when need.

    We should either

    • remove this distinct automation and let developers decide by themselves what data should be distinct. DISTINCT has a huge cost in memory and CPU time
    • OR print of Drupal front page that it does not support more that 200 users simulnaneously. Then print "buy a web farm, add connection pooling, add caching, add a MySQL cluster, and spend 100.000 dollars".
    • Pardon me if I am a little bit tough, but I don't understand why this DISTINCT issue was added to Drupal.

    I read part the abstraction layer.

    It seems that when a field is distinct in database, your add "DISTINCT" to the SQL query.
    This is absurd and contrary to SQL standards.

    On the converse, setting DISTINCT in a database helps the database produce distinct values.
    When starting left on the distinct table you will always obtain distinct records.

    Example:

    SELECT a from foo
    INNER JOIN foo.b = bar.b
    WHERE c="quick"

    OR

    SELECT a from foo
    LEFT JOIN foo.b = bar.b
    WHERE c="quick"

    will always produce distinct values if a is distinct.

    Distinct has huge cost, it can drive a database down.
    In fact, this may be why Drupal is SO slow.
    (of course, Drupal send 20 times the same query in a page, I will report back later).

    Removing distinct rewrite will double the speed immediately.
    Distinct is ANSI and SQL92 compliant, it has to be set by hand when needed.

    Looking at Drupal code, it is very rarely needed.

    Can we discuss?

    Damien, could you explain core developers that DISTINCT is the absolute evil to databases.

    It seems that when a field is distinct in database, your add "DISTINCT" to the SQL query.

    I don't know where you have found this, but it is clearly not true.

    When starting left on the distinct table you will always obtain distinct records.

    This is clearly not true either.

    @jmpoure: please test the patch in #122 and report if the rewritten queries are better.

    Thanks, I will report back.

    I think I understand, the patch applies to mysql and mysqli, no pg. Porting it. Will report.

    I got a quick look into the present devel code (not patch):

    function db_distinct_field($table, $field, $query) {
      if (!preg_match('/FROM\s+\S+\s+AS/si', $query)
      && !preg_match('/DISTINCT\s+ON\s*\(\s*(' . $table . '\s*\.\s*)?' . $field . '\s*\)/si', $query)
      && !preg_match('/DISTINCT[ (]' . $field . '/si', $query)
      && preg_match('/(.*FROM\s+)(.*?\s)(\s*(WHERE|GROUP|HAVING|ORDER|LIMIT|FOR).*)/Asi', $query, $m)) {
        $query = $m[1];
        $query .= preg_replace('/([\{\w+\}]+)\s+(' . $table . ')\s/Usi', '(SELECT DISTINCT ON (' . $field . ') * FROM \1) \2 ', $m[2]);
        $query .= $m[3];
      }
      return $query;
    }

    The logic of the code is to add DISTINCT whenever needed and not in the query, right?
    So there is a possible risk that a normal query gets included in the script, by error, right?

    I think this way to go is a dead-end, because you cannot ask a single line of PHP code to optimize an SQL query.
    Database optimizers have 100.000 lines of code. If you think you can solve this issue and rewrite DISTINCT go ahead.

    Furthermore (SELECT DISTINCT ON (' . $field . ') * FROM foo )
    is a subquery.

    It can potentially run a select distinct clause on the node table, thus making a sequential scan.
    On my 650.000 rows server, this costs at least 650.000 CPU cycles.

    You can read my guide http://drupal.org/node/559302 and comment.

    Feel free to propose a patch for PostgreSQL driver.
    Personally, I will not take the risk to drive down Drupal writing pregs.
    I am too young in the community to take the risk to work on such a patch.
    Sorry.

    But on the converse, I can do intensive testing after someone submit a patch for PostgreSQL.
    IMHO, we should remove this query rewriting stuff and do intensive testing of Drupal to add DISTINCT manually where needed.

    This issue is a bit of a long-winded mess. Is there a D5 patch? Who has reviewed it? Please bump out of 'to be ported' status if there is something reviewable for D5.

    There is no d5 patch. in #142, @opteronmx reports that he patched D5 manually, but he never contributed anything back.

    Feel free to open a new issue, if this is too cloudy.

    Version:5.x-dev» 6.14
    Status:Patch (to be ported)» Needs work

    Confirming #138. The same is happening here with the patch which has been committed lately to drupal core 6.14.

    Apparently the DISTINCT()-modifier is not added by db_distinct_field when the field has an alias.
    I solved the problem by changing the regexp-replace slightly. Instead of
    '/((?:^|,)\s*)(?<!DISTINCT\()(?:'. $table .'\.)?'. $field .'(\s*(?:,|$))/is'
    in database.mysqli.inc, line 370 I'm using
    '/((?:^|,)\s*)(?<!DISTINCT\()(?:'. $table .'\.)?'. $field .'(.*?(?:,|$))/is'

    I don't know about any side-effects this may have on the original issue.

    Is this a core problem or a Views problem? My understanding is that Views fixed this problem in its own way.

    #579018: Duplicates in views for anonymous users (duplicate)
    #501552: Provide workaround for core DISTINCT bug in db_rewrite_sql

    IIRC, it reared its ugly head when people discovered that checking the "Distinct" option in Views produced these queries, but the rewriting issue that causes it is in core and would affect other queries depending on that rewriting.

    subscribe

    subscribing.

    I wasn't sure where to place my comments and put it in the Views queue http://drupal.org/node/579892#comment-2064586 but I don't think it is a views problem per se.

    I have found that any user with permission to administer nodes does not see this problem.

    Izzy

    Just want to confirm I started seeing this problem after upgrading to 6.14. It shows only for OG node (another node access module) and non-admin users.
    As a workaround I have selected distinct option for the view (tracket) but there is a performance penalty. But I didn't have to select that in previous versions so this is a bummer. So from a end user perspective (who has very limited knowledge) this introduces new issue rather than fixing issue.

    And it appears on normal list pages, like /node?

    I'm also experiencing this issue.

    The view is very simple: show published nodes with content type = X. No sort, no grouping, no nothing.

    Admins and view preview show one entry per node, as expected.

    Anonymous users see duplicated entries in the form NODE1, NODE1, NODE2, NODE2 etc.

    If I set the "distinct" option to "yes" on the view, then the problem goes away.

    I've been tracking this issue here in Drupal.org and one thing I noticed is that for almost all reports, the person is using some form of node access module (which is my case, BTW). Don't know if this is related or just plain coincidence.

    Everyone see a normal (non-duplicated) node listing in "/node".

    Sorry for the duplicates but this issue has been discussed in other threads, http://drupal.org/node/579892#comment-2067648

    The patch in #33 of that thread solved the problem for me. Looks like Drupal core 6.14 may be ok.

    Izzy

    @agentrickard
    I am seeing this in tracker view which shows list of nodes belonging to OG. It does not show on list of nodes (in the same tracker) outside OG. So it is definitely related to node access. Obviously if you are seeing a single node in fullpageview it does not show.

    @izmeez
    the patch you have said is not a solution, it is a workaround. What that is doing is forcing every view to use distinct. You can achieve the same effect by checking the "distinct" option in view without applying the patch. That patch(or worakround) has performance implications.

    @ajayg, thanks for the explanation. Would the "distinct" option have to be set in each view or is there a global setting?

    This also explains why the problem and workaround works for nodequeues.

    StatusFileSize
    new1.09 KB
    FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-database-distinct-drupal-6.14_0.patch.
    [ View ]

    Here is a patch for #154 suggested earlier.

    I tested this patch with Drupal 6.14 and vanilla Views 6.x-2.6.

    For me, it appears to resolve issues I was having with duplicate entries for node_access queries:

    Authors and only authors see dupplicate items after upgrading Drupal to 6.14

    and obviates the need to patch views with the various workarounds:

    Provide workaround for core DISTINCT bug in db_rewrite_sql

    I think it is at the very least on the right track. As merlinofchaos stated,

    clearly this is widespread and probably a bug in that core patch.

    Can others test this patch? If you are using a dev version of Views with the workaround patch, you may need to reverse that patch first.

    Patch for Drupal 6.14 (with vanilla Views 6.x-2.6).

    distinct option has to be set for each view. In some cases it is good things because you do not want each and every view to suffer from potential performance load. In some cases it may be hassle to set each view with distinct. In that case the patch is better option as it sets distinct for every view.

    @ajayg, NO, DISTINCT should be set only when needed. Most small views using only a single table doesn't need distinct at all.

    For example, in my post #162, the view translate to this simple SQL query:

    select * from node where type = 'mytype'

    There's no need to use distinct with such type of queries.

    DISTINCT is a necessary evil when you cannot arrange complicated queries in a way to return distinct values (though if you're willing to work a little bit on manually rearranging your query, you may do it without distinct), because it is a performance and resource hog.

    Status:Needs work» Needs review

    Since we have a potential patch (#166) changing status to need review.

    Subscribe

    I'm having to do a dirty hook_views_pre_execute() to fix the query :-(

    Status:Needs review» Needs work

    Subscribing.

    Status:Needs work» Needs review

    @171: why changing back to "needs work"?

    The patch solves the problem for me and there is no need for any kind of workaroud.

    Status:Needs review» Needs work

    It's time to write some tests for this. Could someone provide example of queries in which the DISTINCT fails to be added?

    @Damien. See #122 for a View from before the patch. I need to write some new tests later today.

    Status:Needs work» Needs review
    StatusFileSize
    new3.12 KB
    new732 bytes

    OK, I did some deep digging into this, and the issue, as I suspected, is that the two patches conflict. Using 6.13 with the Views patch is fine. Using 6.14 without the Views patch throws duplicates IF multiple node access permissions are present.

    The solution seems to be to revert the patch to Views. Apply the patch in #166 to Drupal core, and use DISTINCT in Views where needed.

    A sample View and some replication instructions follow.

    I tested using Organic Groups and Domain Access (which I maintain).

    subscribing

    subscribing.

    For what it's worth, #166 works for me (using Drupal 6.14 and Views 2.6).

    Thanks =)

    subscribing

    subscribing

    #166 worked for me as well with Drupal 6.14 & Views 2.6

    subscribing

    Status:Needs review» Reviewed & tested by the community

    Setting to RTBC, since no one else seems willing.

    Status:Reviewed & tested by the community» Needs work

    I suggest we add simpletests to that. Enough is enough.

    Core simpletests that check against a contrib module (which is the only place this error occurs)?

    How and where do you suggest that we do that?

    I don't have any experiences with simpletests, but I think one should test the function "db_distinct_field" against some example queries, not against other modules. Or am I mistaken?

    The function description says:

    Wraps the given table.field entry with a DISTINCT(). The wrapper is added to the SELECT list entry of the given query and the resulting query is returned. This function only applies the wrapper if a DISTINCT doesn't already exist in the query.

    For example db_distinct_field("node", "nid", "SELECT node.nid FROM node") should return "SELECT DISTINCT(node.nid) FROM node". And so should also more complex queries. For example, the following does NOT work without the patch (#166): db_distinct_field("node", "nid", "SELECT node.nid AS node_nid FROM node") should return "SELECT DISTINCT(node.nid) AS node_nid FROM node", BUT returns "SELECT node.nid AS node_nid FROM node".

    so, the tests may be:

    1. db_distinct_field("table", "field", "SELECT table.field FROM table") ?= "SELECT DISTINCT(table.field) FROM table"
    2. db_distinct_field("table", "field", "SELECT table.field AS table_field FROM table") ?= "SELECT DISTINCT(table.field) AS table_field FROM table"
    3. db_distinct_field("table", "field", "SELECT DISTINCT(table.field) FROM table") ?= "SELECT DISTINCT(table.field) FROM table"
    4. db_distinct_field("table", "field", "SELECT DINSTINCT(table.field) AS table_field FROM table") ?= "SELECT DISTINCT(table.field) AS table_field FROM table"
    5. db_distinct_field("table", "field", "SELECT table.field,table.field2 FROM table") ?= "SELECT DISTINCT(table.field),table.field2 FROM table"
    6. db_distinct_field("table", "field", "SELECT table.field AS table_field, table.field2 AS table_field2 FROM table") ?= "SELECT DISTINCT(table.field) AS table_field, table.field2 AS table_field2 FROM table"
    7. db_distinct_field("table", "field", "SELECT DISTINCT(table.field),table.field2 FROM table") ?= "SELECT DISTINCT(table.field),table.field2 FROM table"
    8. db_distinct_field("table", "field", "SELECT DINSTINCT(table.field) AS table_field, table.field2 AS table_field2 FROM table") ?= "SELECT DISTINCT(table.field) AS table_field, table.field2 AS table_field2 FROM table"

    (Number 2,4,6 and 8 will fail with the current code)

    Anyway, this whole database-API is bogus and also - luckily - past with drupal 7. For example this very function "db_distinct_field" is somehow nonsense, because it can create queries which are not mysql-conform (like having multiple distinct fields or something like this: "SELECT field1, DISTINCT(field2) FROM ..."). Moreover, mysql only has the distinct-row feature (see http://dev.mysql.com/doc/refman/5.0/en/select.html), thus the function should be named "db_distinct_query" and contain just one parameter ($query) and one line:

    return preg_replace('/^(SELECT)/is', 'SELECT DISTINCT',$query);

    In this case the resulting queries will look a little bit different: "SELECT DISTINCT field ..." vs. "SELECT DISTINCT(field) ...". For mysql both are equal (with the first beeing the "right" and documented syntax).

    StatusFileSize
    new2.85 KB

    Here's those assertions as a simpletest .test file.

    I think 4 and 8 were failing because of the 'DINSTINCT' rather than an error in the db_distinct_field function.

    On drupal 6.14, queries 2 and 6 fail.

    Crucially with the patch from #166, queries 7 and 8 fail.

    Status:Needs work» Needs review
    StatusFileSize
    new1.11 KB
    FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-database-distinct-drupal-6.14.patch.
    [ View ]

    Don't we actually want to use the following method, and actually look for the 'AS' explicitly?

    This passes all the tests in #187.

    subscribe

    I've committed Steven's work from #189 and #187 to Pressflow 6.

    Status:Needs review» Reviewed & tested by the community

    Considering I've committed this, I may as well mark it RTBC for Drupal.

    Status:Reviewed & tested by the community» Needs work

    '(\s*?(?:,|(?:AS)|$))/is',

    The first '?' is probably not needed (* matches 0 or more), the non-capture pattern is probably not needed either. On the other hand, we need to make sure there is at least a space between the field and the 'AS' or <fieldname>AS would match.

    I suggest:

    '((?:\s*,|\s+AS|\s*$))/is',

    Which we can further reduce to:

    '(\s*,|\s+AS|\s*$)/is'

    Status:Needs work» Needs review
    StatusFileSize
    new1.1 KB
    FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 284392-database-distinct-drupal-6.14_1.patch.
    [ View ]

    Hence:

    Subscribing

    Can we get a review of #196 from one or two of the parties interested enough to 'subscribe'.

    Steven, I guess I can't really "review" it, cause I'm not that experienced but here is my report. My dev site is Drupal 6.14, Views 6.x-2.6, node access modules. I applied this patch and it fixed dupes problem with Views. Now I don't need to turn on "distinct" checkbox in Views setting to remove dupes. Before this I had dupes for node author even with simple views, e.g. "latest published nodes of type X".

    Status:Needs review» Reviewed & tested by the community

    I tested the issue with some debug prints.

    <?php
     
    if (preg_match('/^SELECT(.*?)FROM(.*)/is', $query, $matches)) {
       
    $select = preg_replace(
         
    '/((?:^|,)\s*)(?<!DISTINCT\()(?:'. $table .'\.)?'. $field .'(\s*,|\s+AS|\s*$)/is',
         
    '\1'. $field_to_select .'\2', $matches[1], 1
       
    );
       
    watchdog('PREG_REPL ORG', $query);
       
    watchdog('PREG_REPL REPLACED', 'SELECT'. $select .'FROM'.$matches[2]);
        return
    'SELECT'. $select .'FROM'.$matches[2];
      }
    ?>

    Before the patch:

    SELECT node.nid AS nid, node.title AS node_title, node_data_field_boole_bezahlt.field_boole_bezahlt_value AS node_data_field_boole_bezahlt_field_boole_bezahlt_value, node.type AS node_type, node.vid AS node_vid, node.created AS node_created FROM {node} node INNER JOIN {content_field_boole_bezahlt} node_data_field_boole_bezahlt ON node.vid = node_data_field_boole_bezahlt.vid INNER JOIN {users} users ON node.uid = users.uid WHERE (node.type in ('%s')) AND (node_data_field_boole_bezahlt.field_boole_bezahlt_value IN ('%s', '%s')) AND (users.uid = ***CURRENT_USER***) ORDER BY node_created DESC

    is not rewritten eg. PREG_REPL ORG === PREG_REPL REPLACED .

    After the patch PREG_REPL REPLACED is:

    SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, node_data_field_boole_bezahlt.field_boole_bezahlt_value AS node_data_field_boole_bezahlt_field_boole_bezahlt_value, node.type AS node_type, node.vid AS node_vid, node.created AS node_created FROM {node} node INNER JOIN {content_field_boole_bezahlt} node_data_field_boole_bezahlt ON node.vid = node_data_field_boole_bezahlt.vid INNER JOIN {users} users ON node.uid = users.uid WHERE (node.type in ('%s')) AND (node_data_field_boole_bezahlt.field_boole_bezahlt_value IN ('%s', '%s')) AND (users.uid = ***CURRENT_USER***) ORDER BY node_created DESC

    The regular expression now accepts an AS as string end.

    $matches[1] is

    node.nid AS nid, node.title AS node_title, node_data_field_boole_bezahlt.field_boole_bezahlt_value AS node_data_field_boole_bezahlt_field_boole_bezahlt_value, node.type AS node_type, node.vid AS node_vid, node.created AS node_created

    The regexps in words;

    (\s*(?:,|$))
    Match any spaces till you find the next "," or string end.

    (\s*,|\s+AS|\s*$)
    Match any spaces till "," or one space AS or any spaces till strings end.

    This now does match the first field in the query up to the next , or "AS" or line end and this is what I beleive we want to achieve with this patch. So my $match[1] "node.nid" is matched and replaced.

    I hope this review helps. I did test this and the rewrite seems correct to me as well as the regexp. I set this to tested and reviewed.

    Don't know if this is the right thread for this, but this patch does not fix taxonomy_term_count_nodes() returning the wrong number.
    In D6.13 it did something like this:

    SELECT t.tid, COUNT( DISTINCT(n.nid)) AS c FROM term_node t ...

    In D6.14 with or without the #195 patch it does:

    SELECT t.tid, COUNT(n.nid) AS c FROM term_node t ...

    So if one thing is sure, this patch does not cure all the problems.

    Status:Reviewed & tested by the community» Needs work

    Views eventually introduced a workaround for the original behavior. Which means we can probably just rollback the patch to the original behavior which is less broken than the current behavior.

    I also tried the Views-patch. It has the same behavior. It fixes the display of duplicate nodes, but that one also does not get the taxonomy_term_count_nodes() right. Most noticable result is that (in my case) in image albums all image counts are a factor 4 too high. Manually setting 'distinct' in all related views does not solve that issue.

    StatusFileSize
    new1.13 KB
    FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in database-distinct-drupal-6.14-2_1.patch.
    [ View ]

    Oh, we forgot that DISTINCT can also be used in aggregate functions like COUNT. I'd suggest the following regex (basically just the one from #195, but matches also brackets):

    '/((?:^|,|\()\s*)(?<!DISTINCT\()(?:'. $table .'\.)?'. $field .'(\s*(?:,|\sAS|\)|$))/is'

    Status:Needs work» Needs review

    additionally, there are some more queries to be included in the simpletests:
    maybe:

    • db_distinct_field("table", "field", "SELECT COUNT(table.field) FROM table") ?= "SELECT COUNT(DISTINCT(table.field)) FROM table"
    • db_distinct_field("table", "field", "SELECT COUNT(table.field) AS table_field FROM table") ?= "SELECT COUNT(DISTINCT(table.field)) AS table_field FROM table"
    • db_distinct_field("table", "field", "SELECT table.field1, COUNT(table.field), table.field2 FROM table") ?= "SELECT table.field1, COUNT(DISTINCT(table.field)), table.field2 FROM table"
    • db_distinct_field("table", "field", "SELECT COUNT(DISTINCT(table.field)) FROM table") ?= "SELECT COUNT(DISTINCT(table.field)) FROM table"

    StatusFileSize
    new3.69 KB

    Tests attached, and passing.

    Thank you! Patch in #204 solved all issues (that I am currently aware of) in my site!

    +1 on patch in #204, fixed the "missing distinct in views w/ node access enabled" issue for me.

    Status:Needs review» Reviewed & tested by the community

    I applied the patch and made sure the double views items where gone. Than I did let the test from #206 run and it worked fine.

    The new regexp adds a check for ( and )

    The only problem that is left is that I can think of a good place in D6 to put this test as there are no db tests afaik. But for the patch itself I believe its fine and it now seems to cover all SQL that I could think of.

    @steve jones
    Thank you for the great test! This makes things much easier!

    +1 for patch in #204.

    I confirm that patch works. I tried this on one of the production machine and it is working fine.

    @tyr:

    Is #204 against the 6.14 or 6.x-dev version? The patch isn't working for me b/c there are unmatched patch segments.

    Thanks.

    I confirm that with patch from #195 I was still getting dupes for number of nodes tagged with a term, and with patch from #204 I get no dupes at all.

    @kentr: make sure you reverse any previous patches first.

    subscribing

    Keep the tests coming please! It would be best to not introduce another regression :)

    Is that a request for more tests? If so what other queries would you like testing? Subqueries might be worth a look, I suppose.

    Where should be put the tests? In D6 core, or in simpletest?

    @kentr: The patch is against a fresh/unpatched 6.14

    Issue tags:+Needs tests

    Just tagging as 'Needs Tests' since Gabor seems to be suggesting it needs more.

    Version:6.14» 6.x-dev

    also updating to 6,x-dev, since this issue is in current dev.

    Tagging.

    What extra tests are needed?

    Subscribe

    Subscribe

    I ran this patch on my 6.14 site.
    Indeed the duplicates do disappear. However I get this message when trying to create an organic groups (v 6.x-2.x-dev (2009-Oct-14)) node:

    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(n.nid) FROM node n WHERE n.type IN ('topic','group') AND n.status = 1 O' at line 1 query: SELECT n.title, DISTINCT(n.nid) FROM node n WHERE n.type IN ('topic','group') AND n.status = 1 ORDER BY n.title ASC in /home3/scgreeno/public_html/scgreen.org/sites/all/modules/custom/og/og.module on line 1612.

    Perhaps relevant is the fact that I never got the duplicate problem on my own dev server, running PHP 5.2.11, MySQL 5.4.1, but do consistently have the duplicates problem on my production system which is running PHP 5.2.9 and MySQL 5.0.81.

    It is a show stopper because I can't create OG records.

    Ah, spoke too soon.
    Reverted to OG 6.x-2.x and it now works!

    Postnote:
    Thanks to Steven and jcmarco in #224 and #225 below for input.
    Line 1629, the db_rewrite_sql, which is in the dev version and not the release version seems to be the culprit.

    @ptoly Seems like thats an og issue, it needs to reorder its columns in that query, raise a bug in og's issue queue.

    @Gábor What other tests would you like to see?

    @ptoly That problem was reported here and patch seems to work fine, but it is still pending to be applied to OG
    #569110: user warning: You have an error in your SQL syntax

    I was running into a similar problem using Forum and the Node privacy by role modules and Drupal 6.14. The topic count and posts counts were doubled in my forum for users with one particular role assigned. I am using an access module, Node privacy by role.

    After applying the patch at #204, the topic counts were correct.

    However, now, instead of both the number of posts and the number of topics being doubled, just the $forum->num_posts value is doubled. After some investigating, I realized that it's doubled because while the COUNT(n.nid) part of the following query is rewritten by db_distinct_field, the SUM(l.comment_count) part is not. Here is the query from forum.module and before and after applying patch #204 and db_rewrite_sql.

    Query from forum.module in 6.14

    SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count
    FROM {node} n INNER JOIN {node_comment_statistics} l ON n.nid = l.nid
    INNER JOIN {term_node} r ON n.vid = r.vid
    WHERE n.status = 1 GROUP BY r.tid

    Query before #204 with db_rewrite_query

    SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM {node} n
    INNER JOIN {node_comment_statistics} l ON n.nid = l.nid
    INNER JOIN {term_node} r ON n.vid = r.vid INNER JOIN {node_access} na ON na.nid = n.nid
    WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'node_privacy_byrole_role') OR (na.gid = 5 AND na.realm = 'node_privacy_byrole_role') OR (na.gid = 7 AND na.realm = 'node_privacy_byrole_user'))) AND ( n.status = 1 )GROUP BY r.tid

    Query after applying #204 with db_rewrite_query

    SELECT r.tid, COUNT(DISTINCT(n.nid)) AS topic_count, SUM(l.comment_count) AS comment_count
    FROM {node} n INNER JOIN {node_comment_statistics} l ON n.nid = l.nid
    INNER JOIN {term_node} r ON n.vid = r.vid
    INNER JOIN {node_access} na ON na.nid = n.nid
    WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'node_privacy_byrole_role') OR (na.gid = 5 AND na.realm = 'node_privacy_byrole_role') OR (na.gid = 7 AND na.realm = 'node_privacy_byrole_user'))) AND ( n.status = 1 )GROUP BY r.tid

    It looks like the regex in db_distinct_field doesn't modify the SUM part of the query but instead just the COUNT part. As a result, the topic count is accurate after applying patch #204 but not the comment_count.

    I'm not sure if this is relevant to the Views issue which started the discussion but thought I'd mention it just in case.

    Status:Reviewed & tested by the community» Needs work
    StatusFileSize
    new1.27 KB
    FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch database-distinct-drupal-6.14-3_0.patch.
    [ View ]

    Thanks Julia, this is definitely another drawback.

    The problem is, that the regex was constructed to insert DISTINCT() only once per query. This is fine as long as one doesn't handle with aggregate functions (or subqueries or something similar).

    In my opinion, letting the regex insert as many DISTINCTs as possible is the only way to fix this.

    Status:Needs work» Needs review

    maybe "needs review" is a more appropriate status...

    Was experiencing same problem after upgrade from 6.13 to 6.14 with Views 6.x-2.6 and Workflow Access 6.x-1.1.

    Patch #227 fixed the problem for me.

    Thank you.

    StatusFileSize
    new1.75 KB
    FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch database-distinct-drupal-6.14-3_1.patch.
    [ View ]

    I tested #227 against the following scenario:

    Drupal 6.14 with Node privacy by role. In order to trigger the count problem, I set up a forum with access restricted by content type and a new user (Jon Doe) with two different roles. (You can see way more detail about the ways I've tested forum access + forum in #113611: Forum count incorrect when using access control modules)

    After applying #227, the topic count was no longer duplicated but the post count (topic count + comment count) still was. I'm not sure that this is a problem with this patch or somehow connected to another issue.

    After applying #227, my new query was:

    SELECT r.tid, COUNT(DISTINCT(n.nid)) AS topic_count, SUM(l.comment_count) AS comment_count FROM {node} n
    INNER JOIN {node_comment_statistics} l ON n.nid = l.nid
    INNER JOIN {term_node} r ON n.vid = r.vid INNER JOIN {node_access} na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'node_privacy_byrole_role') OR (na.gid = 3 AND na.realm = 'node_privacy_byrole_role') OR (na.gid = 4 AND na.realm = 'node_privacy_byrole_role') OR (na.gid = 12 AND na.realm = 'node_privacy_byrole_user'))) AND ( n.status = 1 )GROUP BY r.tid

    Also, I think that #227 is patched from the includes directory instead of the root one. Here's a re-rolled patch.

    I've tested the patch in #230, and it applied fine.

    I was having a problem after enabling the access control module of organic groups, just wanted to let you know that the problem is now gone (was seeing duplicated view rows only if current uid was 0 or 1).

    Not bold enough to set the status to reviewed though!

    Status:Needs review» Needs work

    The latest patches have lost the tests, plus those need to be extended to cover the newly identified use cases.

    Status:Needs work» Reviewed & tested by the community
    StatusFileSize
    new1.13 KB
    FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in database-distinct-drupal-6.14-2_1_0.patch.
    [ View ]

    forget everything I wrote in #227. It's just bullshit.

    The original patch from #204 is just fine... and the bug #226 (and #230) has nothing to do with this issue because forum.module doesn't call db_distinct_field() for the field to be summed. I don't know what I was thinking when I wrote this "patch". Sorry.

    To avoid confusion, I'm re-posting the patch from #204 above.

    Status:Reviewed & tested by the community» Needs work

    Sorry This should not be RTBC in light of the comment #226. #226 is clearly saying although patch worked mostly, it still showing some issue with forum module. Since Forum is in core, any patch here should include a fix for forum module as well. If that is a different issue with fourm, please atleast create another issue and provide a link here. But I wonder if forum issue is really a different issue since it wasn't there prior to 6.14 (unless I am mistaken)

    Status:Needs work» Reviewed & tested by the community

    Here is the link to the issue within forum.module: #113611: Forum count incorrect when using access control modules

    Furthermore, the bug regarding the wrong posts-count was already present in <= 6.13 and has not been affected by any of the patches discussed here. (the second part of the issue in #113611 regarding the wrong topic-count indeed wasn't there in 6.13, but has been solved by the patch #204).

    Just wanted to clarify that this issue only relates to #113611: Forum count incorrect when using access control modules in that applying patch #233 adds a DISTINCT to the COUNT(n.nid) for this query in forum_get_forums:

        $sql = "SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM {node} n INNER JOIN {node_comment_statistics} l ON n.nid = l.nid INNER JOIN {term_node} r ON n.vid = r.vid WHERE n.status = 1 GROUP BY r.tid";
        $sql = db_rewrite_sql($sql); //db_distinct_field called

    db_distinct_field is called in forum_get_forums within db_rewrite_sql and as a result wraps COUNT(n.nid) in DISTINCT since n.nid is the primary field. Since db_distinct_field can only wrap the primary field in DISTINCT, there doesn't seem to be any way that #233 could fix both the comment and node duplication described in #113611: Forum count incorrect when using access control modules.

    However, I think it's relevant that after #233 is applied, the COUNT(n.nid) is now accurate for sites using forum and a contributed access control module when it previously was not. This will be new behavior for anyone using using forum with an access control module. I'm not sure whether that's relevant for this issue, the other one, or both.

    For very detailed discussion of how the COUNT(n.nid) and SUM(l.comment_count) values are inaccurate, check out #113611: Forum count incorrect when using access control modules. Any help you can offer would be much appreciated. It is my understanding that this problem existed before 6.14.

    Status:Reviewed & tested by the community» Needs work

    Please roll the tests in.

    Which tests, Damien?
    The patch #233 is the same as in #204. And that one was considered as RTBC...

    Probably the tests from #206. Where should they go? Probably a patch for the simpletest module?

    Status:Needs work» Reviewed & tested by the community

    #233 works just fine

    Status:Reviewed & tested by the community» Needs work

    @bwynants: how do you know that the patch works fine if there are no tests?

    I leave as an exercise for the reader to count the number of people that said that the patch in #122 was working fine.

    At the minimum, I would like to see the tests from #206 rolled in the patch, as modules/system/tests/database.test.

    @Damien: because I had a duplicate in several view lists and that node is no longer listed twice now....

    After upgrading to 6.14 any views showing nodes that are in more than 1 group are duplicated (once per group), as described in this issue: http://drupal.org/node/583170

    The og maintainer says that it is because of this bug.

    I'm getting duplicates from some views with multiple taxonomies that I think is related to this:

    Export of View:

    $view = new view;
    $view->name = 'lerp_admin_coord_centre';
    $view->description = 'LERP Admin Coordination Centre';
    $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('relationships', array(
      'flag_content_rel' => array(
        'label' => 'flag',
        'required' => 0,
        'flag' => 'lerp_appoved',
        'user_scope' => 'any',
        'id' => 'flag_content_rel',
        'table' => 'node',
        'field' => 'flag_content_rel',
        'relationship' => 'none',
      ),
    ));
    $handler->override_option('fields', array(
      'field_autotitle_value' => array(
        'label' => '',
        'alter' => array(
          'alter_text' => 0,
          'text' => '',
          'make_link' => 0,
          'path' => '',
          'link_class' => '',
          'alt' => '',
          'prefix' => '',
          'suffix' => '',
          'target' => '',
          'help' => '',
          'trim' => 0,
          'max_length' => '',
          'word_boundary' => 1,
          'ellipsis' => 1,
          'strip_tags' => 0,
          'html' => 0,
        ),
        'empty' => '',
        'hide_empty' => 0,
        'empty_zero' => 0,
        'link_to_node' => 0,
        'label_type' => 'none',
        'format' => 'default',
        'multiple' => array(
          'group' => TRUE,
          'multiple_number' => '',
          'multiple_from' => '',
          'multiple_reversed' => FALSE,
        ),
        'exclude' => 0,
        'id' => 'field_autotitle_value',
        'table' => 'node_data_field_autotitle',
        'field' => 'field_autotitle_value',
        'relationship' => 'none',
      ),
      'tid' => array(
        'label' => 'Building',
        'alter' => array(
          'alter_text' => 0,
          'text' => '',
          'make_link' => 0,
          'path' => '',
          'link_class' => '',
          'alt' => '',
          'prefix' => '',
          'suffix' => '',
          'help' => '',
          'trim' => 0,
          'max_length' => '',
          'word_boundary' => 1,
          'ellipsis' => 1,
          'strip_tags' => 0,
          'html' => 0,
        ),
        'type' => 'separator',
        'separator' => ', ',
        'empty' => '',
        'link_to_taxonomy' => 0,
        'limit' => 1,
        'vids' => array(
          '2' => 2,
          '1' => 0,
          '8' => 0,
          '7' => 0,
          '5' => 0,
          '6' => 0,
          '4' => 0,
          '3' => 0,
        ),
        'exclude' => 0,
        'id' => 'tid',
        'table' => 'term_node',
        'field' => 'tid',
        'relationship' => 'none',
      ),
      'tid_1' => array(
        'label' => 'Floor',
        'alter' => array(
          'alter_text' => 0,
          'text' => '',
          'make_link' => 0,
          'path' => '',
          'link_class' => '',
          'alt' => '',
          'prefix' => '',
          'suffix' => '',
          'help' => '',
          'trim' => 0,
          'max_length' => '',
          'word_boundary' => 1,
          'ellipsis' => 1,
          'strip_tags' => 0,
          'html' => 0,
        ),
        'type' => 'separator',
        'separator' => ', ',
        'empty' => '',
        'link_to_taxonomy' => 0,
        'limit' => 1,
        'vids' => array(
          '4' => 4,
          '1' => 0,
          '8' => 0,
          '7' => 0,
          '5' => 0,
          '6' => 0,
          '2' => 0,
          '3' => 0,
        ),
        'exclude' => 0,
        'id' => 'tid_1',
        'table' => 'term_node',
        'field' => 'tid',
        'relationship' => 'none',
      ),
      'tid_2' => array(
        'label' => 'Room',
        'alter' => array(
          'alter_text' => 0,
          'text' => '',
          'make_link' => 0,
          'path' => '',
          'link_class' => '',
          'alt' => '',
          'prefix' => '',
          'suffix' => '',
          'help' => '',
          'trim' => 0,
          'max_length' => '',
          'word_boundary' => 1,
          'ellipsis' => 1,
          'strip_tags' => 0,
          'html' => 0,
        ),
        'type' => 'separator',
        'separator' => ', ',
        'empty' => '',
        'link_to_taxonomy' => 0,
        'limit' => 1,
        'vids' => array(
          '3' => 3,
          '1' => 0,
          '8' => 0,
          '7' => 0,
          '5' => 0,
          '6' => 0,
          '2' => 0,
          '4' => 0,
        ),
        'exclude' => 0,
        'id' => 'tid_2',
        'table' => 'term_node',
        'field' => 'tid',
        'relationship' => 'none',
      ),
      'edit_node' => array(
        'label' => '',
        'alter' => array(
          'alter_text' => 0,
          'text' => '',
          'make_link' => 0,
          'path' => '',
          'link_class' => '',
          'alt' => '',
          'prefix' => '',
          'suffix' => '',
          'help' => '',
          'trim' => 0,
          'max_length' => '',
          'word_boundary' => 1,
          'ellipsis' => 1,
          'strip_tags' => 0,
          'html' => 0,
        ),
        'text' => 'edit',
        'exclude' => 0,
        'id' => 'edit_node',
        'table' => 'node',
        'field' => 'edit_node',
        'relationship' => 'none',
      ),
      'field_draggable_sort_value' => array(
        'label' => '',
        'alter' => array(
          'alter_text' => 0,
          'text' => '',
          'make_link' => 0,
          'path' => '',
          'link_class' => '',
          'alt' => '',
          'prefix' => '',
          'suffix' => '',
          'help' => '',
          'trim' => 0,
          'max_length' => '',
          'word_boundary' => 1,
          'ellipsis' => 1,
          'strip_tags' => 0,
          'html' => 0,
        ),
        'link_to_node' => 0,
        'label_type' => 'none',
        'format' => 'default',
        'multiple' => array(
          'group' => TRUE,
          'multiple_number' => '',
          'multiple_from' => '',
          'multiple_reversed' => FALSE,
        ),
        'exclude' => 0,
        'id' => 'field_draggable_sort_value',
        'table' => 'node_data_field_draggable_sort',
        'field' => 'field_draggable_sort_value',
        'relationship' => 'none',
      ),
      'name' => array(
        'label' => 'Faculty/Division',
        'alter' => array(
          'alter_text' => 0,
          'text' => '',
          'make_link' => 0,
          'path' => '',
          'link_class' => '',
          'alt' => '',
          'prefix' => '',
          'suffix' => '',
          'target' => '',
          'help' => '',
          'trim' => 0,
          'max_length' => '',
          'word_boundary' => 1,
          'ellipsis' => 1,
          'strip_tags' => 0,
          'html' => 0,
        ),
        'empty' => '',
        'hide_empty' => 0,
        'empty_zero' => 0,
        'link_to_taxonomy' => 0,
        'exclude' => 0,
        'id' => 'name',
        'table' => 'term_data_7',
        'field' => 'name',
        'relationship' => 'none',
        'override' => array(
          'button' => 'Override',
        ),
      ),
      'ops' => array(
        'label' => '',
        'alter' => array(
          'alter_text' => 0,
          'text' => '',
          'make_link' => 0,
          'path' => '',
          'link_class' => '',
          'alt' => '',
          'prefix' => '',
          'suffix' => '',
          'target' => '',
          'help' => '',
          'trim' => 0,
          'max_length' => '',
          'word_boundary' => 1,
          'ellipsis' => 1,
          'strip_tags' => 0,
          'html' => 0,
        ),
        'empty' => '',
        'hide_empty' => 0,
        'empty_zero' => 0,
        'link_type' => 'toggle',
        'exclude' => 0,
        'id' => 'ops',
        'table' => 'flag_content',
        'field' => 'ops',
        'relationship' => 'flag_content_rel',
      ),
    ));
    $handler->override_option('sorts', array(
      'field_draggable_sort_value' => array(
        'order' => 'ASC',
        'delta' => -1,
        'id' => 'field_draggable_sort_value',
        'table' => 'node_data_field_draggable_sort',
        'field' => 'field_draggable_sort_value',
        'override' => array(
          'button' => 'Override',
        ),
        'relationship' => 'none',
      ),
      'name' => array(
        'order' => 'ASC',
        'id' => 'name',
        'table' => 'term_data',
        'field' => 'name',
        'override' => array(
          'button' => 'Override',
        ),
        'relationship' => 'none',
      ),
    ));
    $handler->override_option('filters', array(
      'type' => array(
        'operator' => 'in',
        'value' => array(
          'lerp_coord_centre' => 'lerp_coord_centre',
        ),
        'group' => '0',
        'exposed' => FALSE,
        'expose' => array(
          'operator' => FALSE,
          'label' => '',
        ),
        'id' => 'type',
        'table' => 'node',
        'field' => 'type',
        'relationship' => 'none',
      ),
    ));
    $handler->override_option('access', array(
      'type' => 'role',
      'role' => array(
        '6' => '6',
        '3' => '3',
        '5' => '5',
        '8' => '8',
        '7' => '7',
      ),
      'perm' => '',
    ));
    $handler->override_option('cache', array(
      'type' => 'none',
    ));
    $handler->override_option('title', 'LERP Administration Coordination Centre');
    $handler->override_option('header_format', '1');
    $handler->override_option('header_empty', 0);
    $handler->override_option('footer_format', '1');
    $handler->override_option('empty_format', '1');
    $handler->override_option('use_ajax', TRUE);
    $handler->override_option('items_per_page', 0);
    $handler->override_option('use_pager', '1');
    $handler->override_option('style_plugin', 'draggabletable');
    $handler->override_option('style_options', array(
      'grouping' => '',
      'override' => 1,
      'sticky' => 1,
      'order' => 'asc',
      'columns' => array(
        'title' => 'title',
        'tid' => 'tid',
        'tid_1' => 'tid_1',
        'tid_2' => 'tid_2',
        'edit_node' => 'edit_node',
        'field_draggable_sort_value' => 'field_draggable_sort_value',
      ),
      'info' => array(
        'title' => array(
          'sortable' => 0,
          'separator' => '',
        ),
        'tid' => array(
          'separator' => '',
        ),
        'tid_1' => array(
          'separator' => '',
        ),
        'tid_2' => array(
          'separator' => '',
        ),
        'edit_node' => array(
          'separator' => '',
        ),
        'field_draggable_sort_value' => array(
          'sortable' => 1,
          'separator' => '',
        ),
      ),
      'default' => 'field_draggable_sort_value',
      'tabledrag_hierarchy' => array(
        'field' => 'none',
        'handler' => 'native',
      ),
      'tabledrag_order' => array(
        'field' => 'field_draggable_sort_value',
        'handler' => 'cck',
      ),
      'draggableviews_extensions' => array(
        'extension_top' => '0',
        'extension_bottom' => '0',
      ),
      'tabledrag_order_visible' => array(
        'visible' => 0,
      ),
      'tabledrag_hierarchy_visible' => array(
        'visible' => 0,
      ),
      'draggableviews_depth_limit' => '-1',
      'tabledrag_types_add' => 'Add type',
      'tabledrag_expand' => array(
        'expand_links' => 'expand_links',
        'collapsed' => 0,
        'by_uid' => 0,
      ),
      'tabledrag_lock' => array(
        'lock' => 0,
      ),
      'draggableviews_default_on_top' => '0',
    ));
    $handler->override_option('row_options', array(
      'inline' => array(),
      'separator' => '',
    ));
    $handler = $view->new_display('page', 'Page', 'page_1');
    $handler->override_option('path', 'lerp-admin-coord');
    $handler->override_option('menu', array(
      'type' => 'normal',
      'title' => 'LERP Coord Centre',
      'description' => '',
      'weight' => '0',
      'name' => 'menu-editorlinks',
    ));
    $handler->override_option('tab_options', array(
      'type' => 'none',
      'title' => '',
      'description' => '',
      'weight' => '0',
    ));

    SQL Query that Views is running:

    SELECT node.nid AS nid,
       node_data_field_autotitle.field_autotitle_value AS node_data_field_autotitle_field_autotitle_value,
       node.type AS node_type,
       node.vid AS node_vid,
       node.uid AS node_uid,
       node_revisions.format AS node_revisions_format,
       node_data_field_draggable_sort.field_draggable_sort_value AS node_data_field_draggable_sort_field_draggable_sort_value,
       term_data_7.name AS term_data_7_name,
       term_data_7.vid AS term_data_7_vid,
       term_data_7.tid AS term_data_7_tid,
       flag_content.content_id AS flag_content_content_id
    FROM node node
    LEFT JOIN flag_content flag_content_node ON node.nid = flag_content_node.content_id AND flag_content_node.fid = 2
    LEFT JOIN content_type_lerp_coord_centre node_data_field_autotitle ON node.vid = node_data_field_autotitle.vid
    LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
    LEFT JOIN content_field_draggable_sort node_data_field_draggable_sort ON node.vid = node_data_field_draggable_sort.vid
    LEFT JOIN term_node term_node ON node.vid = term_node.vid
    LEFT JOIN term_data term_data_7 ON term_node.tid = term_data_7.tid AND term_data_7.vid = 7
    LEFT JOIN flag_content flag_content ON node.nid = flag_content.content_id AND (flag_content.fid = 2 AND flag_content.uid = ***CURRENT_USER***)
    WHERE node.type in ('lerp_coord_centre')
       ORDER BY node_data_field_draggable_sort_field_draggable_sort_value ASC

    Example of output:(this output is the same nid)
    H6.90 Board Room / H / 6 / H6.90 / edit / / Revoke
    H6.90 Board Room / H / 6 / H6.90 / edit / Berwick Campus Faculty of Information Technology / Revoke

    suscribe

    Sorry for being a Johnny-Come-Lately at the last moment of this valuable conversation, but has anyone considered what apprears to me (as a non-DB expert) to be the valuable insight from Gary Wisniewski here: http://drupal.org/node/580838?

    In his attempt to eliminate view dupes under Domain Access Advanced, he describes his success with hacking node module's hook_db_rewrite_sql to take advantage of subselects rather than distinct. He notes the accuracy and efficiency of this approach but cautions that the DB, such as MySQL 5+, must support subselects. So couldn't a db_check on this take advantage of his subselect recommendation while using the challenging distinct approach when the DB won't support it? Again, sorry if this is a ridiculous contribution to a valuable conversation. But sometimes insights/recommendations get lost in the vast mountain of info that the Drupal conversation has become.

    Subscribing

    Subscribing

    subscribe

    patch conflicts with views 2.7 http://drupal.org/node/579892

    Running Drupal 6.14 I had this appearing after upgrading Views from 6.x-2.6 to 6.x-2.7 with postings, groups, i.e. in different views. I applied the patch from http://drupal.org/node/579892#comment-2220974 which seems to solve this for now ... The thread with the patch indicates it's really this issue, so subscribing here ...

    Subscribing. #233 eliminates duplicates for me on Drupal 6.14, OG 6.x-2.0, and Views 6.x-2.7. I haven't tested with forum.

    subscribing.

    subscribe

    Patch in #233 "worksforme". Tested under Drupal 6.14, fixes issues I was having with Views 2.7 (no conflict, contrary to what was said in #251) and OG (2.0).

    @Damien Tournoud: not sure what tests are needed: this is only applicable to D6 and afaik there are no unit tests in this version.

    StatusFileSize
    new5.18 KB

    Not sure whether that's what was asked for, but here's a re-roll combining the latest patch with the latest tests, as described in #242. Not changing status, as Damien has asked for tests for use cases that are not covered at this time.

    patch in #233 works for me Drupal 6.14 with views 2.7

    Patch in #233 helped me also. Thanks a lot!

    Patch in #233 also works for me in tests with core 6.14, Views 2.7, OG 2.0, and nodequeues.

    EDIT With further testing, confirmed the existence of the problem with gmap marker not displaying output correctly, http://drupal.org/node/623234 Reverted to views 2.6 and so far all seems to be working.

    Status:Needs work» Reviewed & tested by the community

    @DamZ - You keep insisting on tests, but this is a D6-specific patch. If you think we require tests for D6 (when there is no testing framework), please post why.

    #233 is RTBC. Setting as such.

    Following on the "D6 doesn't have a testing framework" notion, what other key modules should this be verified against? Several people have verified it works correctly with the latest OG and Views, what others are people concerned with?

    @DamienMcKenna

    Other modules from this thread that I can recall:

    1) Any node access module (I tested with Domain Access)
    2) Taxonomy
    3) Forum
    4) Views

    @jannalexx

    Views should not patch this issue. This needs to be fixed in core. #579892: Authors and only authors see duplicate items after upgrading Drupal to 6.14 is not the proper solution.

    @agentrickard, this patch fixed the problem with forum and forum access, views and taxonomy on my own projects.

    Coming from issue #586738: Duplicate items on og_ghp_ron when marked as "Public" and can confirm that the patch in #233 works for me.

    Thanks, tyr!

    Status:Reviewed & tested by the community» Needs review

    Please only test the patch from #257 from now on.

    Status:Needs review» Reviewed & tested by the community

    Patch from #257 applied cleanly to DRUPAL-6 and all tests passed.

    All - I am very new to Drupal and am enjoying working with it immensely.

    I've noted in the patches that MySQL is the database being modified. Has anyone modified the patch for PostgreSQL? I'm seeing the same effects in OG as noted in these posts - all items are duplicated. I'm configured with Drupal 6.14, Views 6.x-2.7, and Organic Groups 6.x-2.0 with PostgreSQL 8.3.8. Please let me know what I can do to fix the issue in my configuration. Also, I will provide any additional information necessary and any assistance I can give in the analysis. Thanks much!

    EDIT - noted in some previous testing that if I give the user role _administer nodes_ under _node module_ at /admin/user/permissions removes the duplication in OG. Not sure I want every user to have that permission however.

    Status:Reviewed & tested by the community» Needs work

    Hi Skippy,

    Are you able to apply the patch from #257, and set up simpletest too, and then run the database tests #257 adds? Don't do this on a production site. We could point you at the relevant documentation if needed.

    Steven - thanks for the quick reply. I have two sites (production and development) but haven't gone live yet. I haven't tried applying the patch yet as it looks like the code is vastly different but I will try it. Til now I've only looked at the code in patches and haven't actually applied them. Any relevant documentation for patch application, simpletest setup, and database tests would be awesome and I'll post back with my results.

    Applying patches:
    http://drupal.org/patch/apply

    Simpletest:
    http://drupal.org/project/simpletest
    Installs like any other module, but you also need to patch core by following step 1 here: http://drupalcode.org/viewvc/drupal/contributions/modules/simpletest/INS...

    I'll be in IRC, nick darthsteven if you need more personable help!

    @mathieu
    Please use git's no-prefix option when generating patches with git, like: git diff --no-prefix

    For everyone else, you can apply the patch in #257 with: patch -p1 < db_rewrite_and_tests-D6.patch

    Patch applies cleanly. Doesn't appear to break anything. Didn't fix the problem I was having with views and finder, but then I hadn't pinned it down to this bug anyway =D

    Steven - thanks for the info. I've gotten a little ways through the install and then had an issue but took too long to figure out IRC to catch you so I'll post here. Here is what I've done...

    Downloaded #257 patch to drupal root directory (with index.php)
    Attempted install of patch with input and results shown below:

    [root]# patch -p0 < db_rewrite_and_tests-D6.patch
    can't find file to patch at input line 5
    Perhaps you used the wrong -p or --strip option?
    The text leading up to this was:
    --------------------------
    |diff --git a/includes/database.mysql.inc b/includes/database.mysql.inc
    |index 43342e9..a06bd17 100644
    |--- a/includes/database.mysql.inc
    |+++ b/includes/database.mysql.inc
    --------------------------
    File to patch: includes/database.mysql.inc
    patching file includes/database.mysql.inc
    can't find file to patch at input line 18
    Perhaps you used the wrong -p or --strip option?
    The text leading up to this was:
    --------------------------
    |diff --git a/includes/database.mysqli.inc b/includes/database.mysqli.inc
    |index a2415a1..aeb980c 100644
    |--- a/includes/database.mysqli.inc
    |+++ b/includes/database.mysqli.inc
    --------------------------
    File to patch: includes/database.mysqli.inc
    patching file includes/database.mysqli.inc
    patching file b/modules/system/tests/database.test
    [root]#

    Per post #272 (http://drupal.org/node/284392#comment-2261144)
    - re-ran db_rewrite_and_tests-D6.patch with -p1

    [root]# patch -p1 < db_rewrite_and_tests-D6.patch
    patching file includes/database.mysql.inc
    Reversed (or previously applied) patch detected! Assume -R? [n] y
    patching file includes/database.mysqli.inc
    Reversed (or previously applied) patch detected! Assume -R? [n] y
    patching file modules/system/tests/database.test

    Downloaded and installed SimpleTest Module and applied patch.
    Activated SimpleTest Module and received DOMDocument error.
    - repaired with addition of php-xml module for FC11
    Receiving two notices of db error as below:

    Notice: Undefined index: in views_query->add_table() (line 263 of
    /var/www/html/devsite/sites/all/modules/views/includes/query.inc).

    I don't know where to go next and don't know if the add_table() error is from the patch or from SimpleTest - I'm not that smart. I'll muck around with it some more and will look for any suggestions you have. Thanks and have a great weekend.

    @Skippy: rather than using patch -p0 < patchname.patch, use patch -p1 < patchname.patch. You would usually use -p0 but this patch isn't structured quite the way other Drupal patches are structured, so you need a different flag.

    I applied the patch from #257 after I had applied the patch needed for and delivered by simpletest. The patches ran successfully (notice the -p1 needed for the patchfile from #257):

    /site$ patch -p0 < D6-core-simpletest.patch
    patching file includes/bootstrap.inc
    patching file includes/common.inc
    Hunk #1 succeeded at 2605 (offset 10 lines).
    Hunk #2 succeeded at 3731 (offset 10 lines).
    patching file install.php
    /site$ patch -p1 < db_rewrite_and_tests-D6.patch
    patching file includes/database.mysql.inc
    patching file includes/database.mysqli.inc
    patching file modules/system/tests/database.test

    The patch seems to solve my problems with duplicate items otherwise (problems appeared after upgrading views from 6.x-2.6 to 2.7). Testing the created database functionality with simpletest yields: Database functionality: 13 passes, 0 fails, and 0 exceptions.

    Relevant versions used:
    -- Drupal 6.14
    -- Content Construction Kit (CCK) 6.x-2.6
    -- Organic groups 6.x-2.0 (patched with #431944: Public Audience checkbox still visible with all private group settings #11)
    -- Content Type Administration by Organic Group 6.x-1.2
    -- OG Forum 6.x-2.0
    -- SimpleTest 6.x-2.9
    -- Views 6.x-2.7

    Anything I can do further to help get this towards a release?

    Status:Needs work» Needs review

    Any other tests needed?

    We need to get someone to test this on a pgsql system.

    We've been experiencing this with OG Access Control. Here's the minimum that it takes to reproduce:

    Steps to reproduce, apply the patch, and confirm that the patch fixes "something," which in this case is the display of duplicate entries at yoursite.com/og when OG Access Control is turned on:

    1. Install Drupal 6.x-dev, Organic Groups 6.x-2.0, and Views 6.x-2.7
    2. Enable OG, OG Views Integration, Views and Views UI
    3. Create a group content type and a group post content type
    4. Create a group as user 1
    5. Create a new user (jane) and add her to the group
    6. Make jane an admin of the group
    7. Log in as jane
    8. Visit yoursite.com/og
    9. Notice that there is one group listed -- this is what you would expect. We haven't turned on OG Access Control yet.
    10. Enable OG Access Control
    11. Visit admin/content/node-settings/rebuild and rebuild permissions
    12. Log in as jane again and visit yoursite.com/og -- notice now that there are 2 rows for the group you created in step 1
    13. Apply the patch from #257 with:

      patch -p1 < db_rewrite_and_tests-D6.patch

    14. As jane, visit yoursite.com/og and notice that there is now only 1 group listed

    Looking at the patch, and repeating what has already been said above, there is nothing in the patch that addresses pgsql. It still appears that we need pgsql users to:

    1. Confirm that there is a problem (or problems) there
    2. The patch fixes the problem(s)

    If the patch doesn't fix the problem(s) then we need someone to resubmit the patch with a pgsql fix in there.

    Please, excuse me if I am not understanding something.

    @marcp, in comment #278, have you tried this with Views 2.6 ?

    As I shared in comment #260 with 6.14 patched, og 2.0 and views 2.6 there does not appear to be a problem. We are using og_access. We do not have a test suite setup that we can run to test all functions but so far it seems to be ok.

    @izmeez - No, I haven't tried it with Views 2.6. I'm not sure there's any point, really, since Views isn't going to go backwards and, as far as I can tell from reading this thread and others Merlin believes this is a core bug and there's no code in Views to work around the issue.

    My only question was whether to try this with the latest dev versions of OG and Views.

    @marcp - Thanks. After making the comment I realized you seemed to be trying to move forward on resolving this.

    I tried the dev version of Views a few days ago and found it was working with the core 6.14 patch but had the same problems with gmap (where a patch is being developed) but have not tried it since.

    I also tried the dev version of og a few days ago because of another issue I was try to track down and ran into some major problems that I didn't have time to investigate so I reverted back to og 2.0

    Sorry, I can't be of much help.

    Subscribing

    subscribing

    Version:6.x-dev» 6.14

    OK patch at #257 fixed my duplicate listings (D6.14, Views 6.x-2.7, OG 6.x-2.0)

    Version:6.14» 6.x-dev

    This should be against drupal 6.x-dev.

    Under PgSQL the database tests in #257 fail, specifically:

    • Add DISTINCT to SELECT table.field FROM table

      Query was rewritten to: SELECT table.field FROM table, expected query: SELECT DISTINCT(table.field) FROM table

    • Add DISTINCT to SELECT table.field AS table_field FROM table

      Query was rewritten to: SELECT table.field AS table_field FROM table, expected query: SELECT DISTINCT(table.field) AS table_field FROM table

    • Add DISTINCT to SELECT table.field,table.field2 FROM table

      Query was rewritten to: SELECT table.field,table.field2 FROM table, expected query: SELECT DISTINCT(table.field),table.field2 FROM table

    • Add DISTINCT to SELECT table.field AS table_field, table.field2 AS table_field2 FROM table

      Query was rewritten to: SELECT table.field AS table_field, table.field2 AS table_field2 FROM table, expected query: SELECT DISTINCT(table.field) AS table_field, table.field2 AS table_field2 FROM table

    • Add DISTINCT to SELECT COUNT(table.field) FROM table

      Query was rewritten to: SELECT COUNT(table.field) FROM table, expected query: SELECT COUNT(DISTINCT(table.field)) FROM table

    • Add DISTINCT to SELECT COUNT(table.field) AS table_field FROM table

      Query was rewritten to: SELECT COUNT(table.field) AS table_field FROM table, expected query: SELECT COUNT(DISTINCT(table.field)) AS table_field FROM table

    • Add DISTINCT to SELECT table.field1, COUNT(table.field), table.field2 FROM table

      Query was rewritten to: SELECT table.field1, COUNT(table.field), table.field2 FROM table, expected query: SELECT table.field1, COUNT(DISTINCT(table.field)), table.field2 FROM table

    So the code needs work to make PgSQL work properly too. Is there are reason why the PgSQL code is different in this function? Are we implementing some MySQL specific functionality with regard to the distincts and our tests?

    [The fact the first test in the list is failing is worrying]

    Status:Needs review» Needs work

    The patch doesn't fix Postgres (doesn't even touch the postgres file): we need someone using Postgres to submit a patch for it.

    @mathieu: Agreed - as long as it doesn't block the patch for mysql - those two can be submitted independently - right?

    EDIT: copy/paste error in queries..

    About PgSQL... Hoping to move this forward, here's all that I found, using the test case described by marcp in #278.

    What Devel shows as query that was executed (and that returns two rows):

    SELECT n.nid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 1 AND na.realm = 'og_admin'))) AND ( n.status = 1 AND n.nid IN (1))

    What it seems it should be instead (...I'm no PgSQL expert, someone else should confirm):

    SELECT DISTINCT ON (nid) n.nid FROM (SELECT * FROM node) n INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 1 AND na.realm = 'og_admin'))) AND ( n.status = 1 AND n.nid IN (1));

    This is the line that needs to be modified:

    $query .= preg_replace('/([\{\w+\}]+)\s+(' . $table . ')\s/Usi', '(SELECT DISTINCT ON (' . $field . ') * FROM \1) \2 ', $m[2]);

    Steven - re: pgsql and the #257 patch...

    I created a new site(Drupal 6.14, Views 6.x-2.7, and Organic groups 6.x-2.0) with a new PostgreSQL 8.3.8 database. I then followed the steps outlined by marcp in #278 and got the exact results expected until step 14 after the patch application where I still have the duplication evident.

    I am no coding or sql guru like all of you but am willing to use this test site to verify operations of the magic you (all) create. Thank you immensely for your efforts and expertise.

    Skippy

    This is an interesting problem when it comes to pgsql, while mysql has the capability to added DISTINCT clauses inside select statements, pgsql does not, at least not dorectly. Postgress documentation for DISTINCT says to prepend the the DISTINCT statement to the select list.

    If db_distinct_field was called at the end of the query building this would be a simple patch to db_distinct_fiel in includes/database.pgsql.inc:

    426c426,428
    <     $query = "SELECT DISTINCT ON ($table.$field) * FROM (". $query .") node ";
    ---
    >     $query = $m[1];
    >     $query .= preg_replace('/([\{\w+\}]+)\s+(' . $table . ')\s/Usi', '(SELECT DISTINCT ON (' . $field . ') * FROM \1) \2 ', $m[2]);
    >     $query .= $m[3];

    But it seems that the node_access module is adding join clauses after db_distinct_field() is called and incorrectly places the clause after this substitution is made.

    I will be looking more into this tomorrow.

    On a side note I have confirmed that the patch in #257 fixes this issue for mysql using the steps from #278.

    I'm not sure if this is of any help or if this is even the right issue to post to because of the cross over with http://drupal.org/node/579892

    Anyway, the setup is Drupal 6.14 patched with #257 without tests active. OG 2.0 and Views tested with 2.6, 2.7 and the 2.x-dev 2009-11-12. A view of a list of users without a relationship shows only single items. When a relationship is added for OG: Group node (member) triplicates appear unless distinct is set to yes.

    I found out that at my side the number of times the same teaser in a frontpage is presented corresponds to the number of roles a user is assigned to + 1. E.g. A if a user has 2 roles assiged to, he sees all the teasers on the frontpage 3 times if he/she is logged in.

    Does this point bring value in this discussion?

    Moreover, can sb somehow briefly summarize which problem is exactly discussed in this thread and in which cicumstances it is likely to occur?

    @myDRU - I'm assuming you are using some sort of node access control module on your site. Try applying the patch in #257 and see if it fixes your problem.

    Looking more into this the solution for pgsql support seems to be adding an modified version of the includes/database.inc:db_rewrite_sql() function to includes/database.pgsql.inc. Specifically we need to allow for a slightly differn sql pattern then the one matched for in db_rewrite_sql():

    $pattern = '{
          # Beginning of the string
          ^
          ((?P<anonymous_view>
            # Everything within this set of parentheses is named "anonymous view"
            (?:
              [^()]++                   # anything not parentheses
            |
              \( (?P>anonymous_view) \)          # an open parenthesis, more "anonymous view" and finally a close parenthesis.
            )*
          )[^()]+WHERE)
        }x';

    There is some use of recursive pattern matching here that I don't have my head fully wrapped around yet. I will try to have a patch for this tomorrow.

    subscribing

    Yes, patch #257 solves the problem! Happy.
    FYI: I'm using
    node privacy byrole 6.x-1.5
    Views 6.x-2.7
    Drupal 6.14
    ... and many roles and content types.

    StatusFileSize
    new7.48 KB
    FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch db_rewrite_and_tests-D6.3.patch.
    [ View ]

    This patch fixes the bug described by @marcp in #278 on pgsql. Included in the patch is the work from #257.

    As I started looking at the test cases provided by #257 I realize that this "fix" for pgsql may not be completely ideal. The solution implemented patches both database.pgsql.inc:db_distinct_field() and database.inc:db_rewrite_sql(). The fix uses the non-standard "SELECT DISTINCT ON" format. This has a side effect that if ther is an 'ORDER BY" clause and the the distinct field and the "ORDER BY" field are different the sb will through an error.

    I was reading that the "proper" way to do this is "With judicious use of GROUP BY and subqueries in FROM the construct can be avoided, but it is often the most convenient alternative. "

    @evoltech: thanks for the patch! It does indeed fix the issue, but testing the use case described in #278, I get the following warnings:

        * warning: pg_query() [function.pg-query]: Query failed: ERROR: missing FROM-clause entry for table "n" LINE 1: SELECT DISTINCT ON (n.nid) * FROM (SELECT n.nid FROM node n ... ^ in /home/mathieu/drupal/drupal-6.14/includes/database.pgsql.inc on line 139.
        * user warning: query: menu_tree_check_access SELECT DISTINCT ON (n.nid) * FROM (SELECT n.nid FROM node n WHERE n.status = 1 AND n.nid IN (1)) node in /home/mathieu/drupal/drupal-6.14/includes/menu.inc on line 1006.

    subscribe

    Pages