db_rewrite_sql causing issues with DISTINCT
Passionate_Lass - July 18, 2008 - 15:51
| Project: | Drupal |
| Version: | 6.x-dev |
| Component: | database system |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | needs review |
| Issue tags: | select distinct as distinct bug |
Description
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.
#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