Posted by Passionate_Lass on July 18, 2008 at 3:51pm
194 followers
| Project: | Drupal core |
| Version: | 6.x-dev |
| Component: | database system |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed (fixed) |
| Issue tags: | Needs tests, select distinct as distinct bug |
Issue Summary
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 ASCCopy 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.
Comments
#1
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.
#2
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
#3
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.
#4
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? :)
#5
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.
#6
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
#7
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.
#8
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
#9
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?
#10
Enter '1' in the format field. Unfortunately I think that will still be '24 years ago' rather than just '24'.
#11
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.
#12
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.
#13
To summarize:
SELECT DISTINCT(node.nid) AS nid ...is being rewritten as:
SELECT DISTINCT(node.nid) AS DISTINCT(node.nid) ...#14
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,
));
#15
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
#16
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!).
#17
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
#18
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)'.
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 ;)
#19
Sorry, same patch as above, but ran from the Drupal root rather than from the includes directory.
#20
Shouldn't you have marked this needs review, not needs work?
#21
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.)
#22
@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 :)
#23
Ok, just bear in mind that patches marked CNW will get less attention than CNR.
#24
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' !
#25
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...
#26
@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 ?
#27
Interesting, when the official solution of this problem can be expected?
#28
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.
#29
@netbear : Did you try the patch in #24 ? Would be great to have some more feedback on that one.
#30
subscribing
#31
@Anselm Heaton
No, I haven't yet, but I solved my problem with setting my view property DISTINCT to "Off".
#32
how do you specificly set the view property to "off"
#33
I ran into the same problem with Domain Access. Subscribing. Thx merlin for pointing me this way.
--
J.
#34
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
#35
Thanks man. It works!!! I use distinct for views and acces_content module. The errors are now gone!
#36
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.
#37
I had the same problem with Drupal 6.9 and Views 6.x-2.2 and the patch in #24 works great.
Ben
#38
subscribing
#39
Subscribing.
#40
Subscribe.
#41
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
#42
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.
#43
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.
#44
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!
#45
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.
#46
@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 :)
#47
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?
#48
@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).
#49
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.
#50
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 DESCHere'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,
));
?>
#51
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!!
#52
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.
#53
And we need to fix it in HEAD first, then backport it.
#54
The last submitted patch failed testing.
#55
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.
#56
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.
#57
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.
#58
I agree, the breakage here is actually critical.
#59
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
#60
@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.
#61
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.
#62
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
#63
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 'FROM node n WHERE n.status = 1 AND n.nid IN (7, 18, 282)' 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 'FROM node n WHERE n.status = 1 AND n.nid IN (183)' 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 ' r.tid, n.title, n.type, n.sticky, u.name, u.uid, n.created AS timestamp, n.comm' 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 588Same problem as 1st, there's a problem in the select fields.
#64
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
#65
subscribing
#66
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.#67
subscribing
#68
@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.
#69
@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 :)
#70
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!
#71
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
#72
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.)
#73
Addition: couldn't print view to page; problematic view attached as text file. Hope that's ok. It's a pretty standard calendar view.
#74
Just to subscribe
#75
The patch works for me in both 6.9 and 6.10.
Thanks for the great work on this.
#76
subscribe
#77
scrubscribulating....
#78
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.
#79
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.
#80
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.
#81
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.
#82
subscribe
#83
subscribe
#84
Subscribing.
#85
So this patch is worth trying. Works for me!
#86
#24 worked for 6.12 and saved my life.
This should be, has to be, must be committed to the next release.
Tx.
#87
Subscribe, need to follow up when core is updated.
#88
Subscribing
#89
subscribin'
#90
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.
#91
Patch @ #24 worked for me too. Was having problems with Views Calc.
#92
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!
#93
Let's try to pass the automated test....
#94
subscribe
#95
Patch in #24 fixed it for me.
#96
The patch in #24/#93 (same patch) fixed it for me too. I haven't seen any adverse affects yet.
#97
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.
#98
subscribing
#99
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.
#100
I will try to carve out some time to run some tests using Domain Access.
#101
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?
#102
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.
#103
#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.
#104
Subscribing.
#105
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.
#106
Organic groups is a node access module.
#107
subscribing
#108
subscribing
#109
@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.
#110
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
#111
subscribing
#112
subscribing
#113
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_aliasSELECT 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, 25These 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_aliasSELECT 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#114
I applied patch #93 and now Views works...
*Update*
Nevermind what I said about not working :)
#115
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.
#116
I tested the patch and it is working! I also have no duplicates.
#117
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
#118
Subscribe. #93 worked for me as well. Thanks tassoman!
#119
Also fixes Drupal 6.13...
How the hell is this not committed yet?
#120
Also experiencing this problem, though only when I move my view from the views ui into my module.
$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,
));
#121
Can we try to run this patch test (#93) for Drupal 6 HEAD?
#122
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.
#123
#124
Subscribing. Patch #93 indeed works. Tested on 6.12 with Content Access. Lovely ! Thanks!
#125
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
#126
Same story as others - without the patch we get *lots* of errors like this on Views set to distinct=Yes:
<?phpuser 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.
#127
The patch in #93 is outdated and has code style issues.
The current patch to review is #122.
#128
Tested #122 and works. Ready to go?
#129
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
#130
Subscribe. Perhaps applying the patch to 6.x would bring any problems with it out of the woodwork >:)
#131
Committed to Drupal 6, thanks.
#132
I think we need to (can we?) backport this to Drupal 5.
#133
Works as expected!
#134
Restoring status.
#135
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?
#136
Open a new issue for one of those modules, please.
#137
subscribing
#138
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 nidinstead of:
SELECT DISTINCT(node.nid) AS nidI guess this is caused by D6 core and is not a Content Access issue?
Anyone else experiencing this problem?
#139
@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.
#140
I think it has been committed to the 6.x-dev version which will be the upcoming 6.14, not the existing 6.13?
#141
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.
#142
Just confirming patch@#122 works on D5 5.19 (database.mysql.inc,v 1.66.2.3).
Patch manually applied
Thanks folks!!!
#143
@opteronmx
Can you roll a D5 patch based on what you hand-rolled, please?
#144
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.
#145
Distinct should be set by hand by each developer on request, when need.
We should either
Pardon me if I am a little bit tough, but I don't understand why this DISTINCT issue was added to Drupal.
#146
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 fooINNER JOIN foo.b = bar.b
WHERE c="quick"
OR
SELECT a from fooLEFT 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.
#147
I don't know where you have found this, but it is clearly not true.
This is clearly not true either.
#148
@jmpoure: please test the patch in #122 and report if the rewritten queries are better.
#149
Thanks, I will report back.
#150
I think I understand, the patch applies to mysql and mysqli, no pg. Porting it. Will report.
#151
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.
#152
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.
#153
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.
#154
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.
#155
Were duplicates:
#156
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
#157
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.
#158
subscribe
#159
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
#160
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.
#161
And it appears on normal list pages, like /node?
#162
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".
#163
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
#164
@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.
#165
@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.
#166
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,
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).
#167
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.
#168
@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.
#169
Since we have a potential patch (#166) changing status to need review.
#170
Subscribe
I'm having to do a dirty hook_views_pre_execute() to fix the query :-(
#171
Subscribing.
#172
@171: why changing back to "needs work"?
The patch solves the problem for me and there is no need for any kind of workaroud.
#173
It's time to write some tests for this. Could someone provide example of queries in which the DISTINCT fails to be added?
#174
@Damien. See #122 for a View from before the patch. I need to write some new tests later today.
#175
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).
#176
subscribing
#177
subscribing.
#178
For what it's worth, #166 works for me (using Drupal 6.14 and Views 2.6).
Thanks =)
#179
subscribing
#180
subscribing
#181
#166 worked for me as well with Drupal 6.14 & Views 2.6
#182
subscribing
#183
Setting to RTBC, since no one else seems willing.
#184
I suggest we add simpletests to that. Enough is enough.
#185
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?
#186
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:
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:
(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).
#187
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.
#188
Crucially with the patch from #166, queries 7 and 8 fail.
#189
Don't we actually want to use the following method, and actually look for the 'AS' explicitly?
This passes all the tests in #187.
#190
subscribe
#191
I've committed Steven's work from #189 and #187 to Pressflow 6.
#192
Considering I've committed this, I may as well mark it RTBC for Drupal.
#193
'(\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>ASwould match.I suggest:
'((?:\s*,|\s+AS|\s*$))/is',#194
Which we can further reduce to:
'(\s*,|\s+AS|\s*$)/is'#195
Hence:
#196
Subscribing
#197
Can we get a review of #196 from one or two of the parties interested enough to 'subscribe'.
#198
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".
#199
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 DESCis 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 DESCThe 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_createdThe 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.
#200
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.
#201
#202
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.
#203
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.
#204
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'#205
additionally, there are some more queries to be included in the simpletests:
maybe:
#206
Tests attached, and passing.
#207
Thank you! Patch in #204 solved all issues (that I am currently aware of) in my site!
#208
+1 on patch in #204, fixed the "missing distinct in views w/ node access enabled" issue for me.
#209
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!
#210
+1 for patch in #204.
I confirm that patch works. I tried this on one of the production machine and it is working fine.
#211
@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.
#212
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.
#213
subscribing
#214
Keep the tests coming please! It would be best to not introduce another regression :)
#215
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?
#216
@kentr: The patch is against a fresh/unpatched 6.14
#217
Just tagging as 'Needs Tests' since Gabor seems to be suggesting it needs more.
#218
also updating to 6,x-dev, since this issue is in current dev.
#219
Tagging.
#220
What extra tests are needed?
#221
Subscribe
#222
Subscribe
#223
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.
#224
@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?
#225
@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
#226
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_countFROM {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} nINNER 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_countFROM {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.
#227
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.
#228
maybe "needs review" is a more appropriate status...
#229
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.
#230
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} nINNER 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.
#231
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!
#232
The latest patches have lost the tests, plus those need to be extended to cover the newly identified use cases.
#233
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.
#234
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)
#235
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).
#236
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.
#237
Coming from #613616: You have an error in your SQL syntax, subscribing.
#238
Please roll the tests in.
#239
Which tests, Damien?
The patch #233 is the same as in #204. And that one was considered as RTBC...
#240
Probably the tests from #206. Where should they go? Probably a patch for the simpletest module?
#241
#233 works just fine
#242
@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.#243
@Damien: because I had a duplicate in several view lists and that node is no longer listed twice now....
#244
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.
#245
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
#246
suscribe
#247
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.
#248
Subscribing
#249
Subscribing
#250
subscribe
#251
patch conflicts with views 2.7 http://drupal.org/node/579892
#252
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 ...
#253
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.
#254
subscribing.
#255
subscribe
#256
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.
#257
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.
#258
patch in #233 works for me Drupal 6.14 with views 2.7
#259
Patch in #233 helped me also. Thanks a lot!
#260
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.
#261
@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.
#262
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?
#263
@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.
#264
@agentrickard, this patch fixed the problem with forum and forum access, views and taxonomy on my own projects.
#265
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!
#266
Please only test the patch from #257 from now on.
#267
Patch from #257 applied cleanly to DRUPAL-6 and all tests passed.
#268
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.
#269
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.
#270
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.
#271
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!
#272
@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
#273
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.
#274
@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.
#275
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.patchpatching 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?
#276
Any other tests needed?
#277
We need to get someone to test this on a pgsql system.
#278
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:
patch -p1 < db_rewrite_and_tests-D6.patch
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.
#279
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.
#280
@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.
#281
@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.
#282
Subscribing
#283
subscribing
#284
OK patch at #257 fixed my duplicate listings (D6.14, Views 6.x-2.7, OG 6.x-2.0)
#285
This should be against drupal 6.x-dev.
#286
Under PgSQL the database tests in #257 fail, specifically:
Add DISTINCT to SELECT table.field FROM tableAdd DISTINCT to SELECT table.field AS table_field FROM tableAdd DISTINCT to SELECT table.field,table.field2 FROM tableAdd DISTINCT to SELECT table.field AS table_field, table.field2 AS table_field2 FROM tableAdd DISTINCT to SELECT COUNT(table.field) FROM tableAdd DISTINCT to SELECT COUNT(table.field) AS table_field FROM tableAdd DISTINCT to SELECT table.field1, COUNT(table.field), table.field2 FROM tableSo 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]
#287
The patch doesn't fix Postgres (doesn't even touch the postgres file): we need someone using Postgres to submit a patch for it.
#288
@mathieu: Agreed - as long as it doesn't block the patch for mysql - those two can be submitted independently - right?
#289
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]);#290
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
#291
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.
#292
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.
#293
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?
#294
@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.
#295
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.
#296
subscribing
#297
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.
#298
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. "
#299
@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.
#300
subscribe