Group By causes the non-compliance of "group multiple values" for fields with cardinality > 1

I have reproduced the problem on a simpler view, Here is the exported view ...

$view = new view;
$view->name = 'test_view';
$view->description = '';
$view->tag = '';
$view->base_table = 'node';
$view->human_name = 'test_view';
$view->core = 7;
$view->api_version = '3.0-alpha1';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'field_termid' => 'field_termid',
  'field_image' => 'field_image',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'field_termid' => array(
    'sortable' => 0,
    'default_sort_order' => 'asc',
    'align' => '',
    'separator' => '',
  ),
  'field_image' => array(
    'align' => '',
    'separator' => '',
  ),
);
$handler->display->display_options['style_options']['override'] = 1;
$handler->display->display_options['style_options']['sticky'] = 0;
/* Field: Fields: termid */
$handler->display->display_options['fields']['field_termid']['id'] = 'field_termid';
$handler->display->display_options['fields']['field_termid']['table'] = 'field_data_field_termid';
$handler->display->display_options['fields']['field_termid']['field'] = 'field_termid';
$handler->display->display_options['fields']['field_termid']['label'] = '';
$handler->display->display_options['fields']['field_termid']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_termid']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_termid']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_termid']['alter']['external'] = 0;
$handler->display->display_options['fields']['field_termid']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_termid']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['field_termid']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_termid']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_termid']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_termid']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_termid']['element_label_colon'] = 1;
$handler->display->display_options['fields']['field_termid']['element_default_classes'] = 1;
$handler->display->display_options['fields']['field_termid']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_termid']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_termid']['settings'] = array(
  'thousand_separator' => ' ',
  'prefix_suffix' => 1,
);
$handler->display->display_options['fields']['field_termid']['field_api_classes'] = 0;
/* Field: Fields: image */
$handler->display->display_options['fields']['field_image']['id'] = 'field_image';
$handler->display->display_options['fields']['field_image']['table'] = 'field_data_field_image';
$handler->display->display_options['fields']['field_image']['field'] = 'field_image';
$handler->display->display_options['fields']['field_image']['label'] = '';
$handler->display->display_options['fields']['field_image']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['external'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_image']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_image']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_image']['element_label_colon'] = 1;
$handler->display->display_options['fields']['field_image']['element_default_classes'] = 1;
$handler->display->display_options['fields']['field_image']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_image']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_image']['click_sort_column'] = 'fid';
$handler->display->display_options['fields']['field_image']['settings'] = array(
  'image_style' => 'thumbnail',
  'image_link' => 'content',
);
$handler->display->display_options['fields']['field_image']['group_rows'] = 1;
$handler->display->display_options['fields']['field_image']['delta_limit'] = '1';
$handler->display->display_options['fields']['field_image']['delta_offset'] = '0';
$handler->display->display_options['fields']['field_image']['delta_reversed'] = 0;
$handler->display->display_options['fields']['field_image']['field_api_classes'] = 0;
/* Field: Node: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['title']['alter']['external'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['element_label_colon'] = 1;
$handler->display->display_options['fields']['title']['element_default_classes'] = 1;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Argument: Fields: termid (field_termid) */
$handler->display->display_options['arguments']['field_termid_value']['id'] = 'field_termid_value';
$handler->display->display_options['arguments']['field_termid_value']['table'] = 'field_data_field_termid';
$handler->display->display_options['arguments']['field_termid_value']['field'] = 'field_termid_value';
$handler->display->display_options['arguments']['field_termid_value']['style_plugin'] = 'default_summary';
$handler->display->display_options['arguments']['field_termid_value']['default_argument_type'] = 'fixed';
$handler->display->display_options['arguments']['field_termid_value']['break_phrase'] = 1;
$handler->display->display_options['arguments']['field_termid_value']['not'] = 0;
/* Filter: Node: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'test' => 'test',
);

/* Display: test */
$handler = $view->new_display('page', 'test', 'test_1');
$handler->display->display_options['path'] = 'testview/%';
$translatables['test_view'] = array(
  t('Defaults'),
  t('more'),
  t('Apply'),
  t('Reset'),
  t('Sort By'),
  t('Asc'),
  t('Desc'),
  t('Items per page'),
  t('- All -'),
  t('Offset'),
  t('Title'),
  t('All'),
  t('test'),
);

EDIT: This is a simplified export of a view that demonstrates the problem without all the complexity of my original view - but still demonstrates the issue.

Here is the associated query -

SELECT field_data_field_termid.field_termid_value AS field_data_field_termid_field_termid_value, field_data_field_image.field_image_fid AS field_data_field_image_field_image_fid, node.title AS node_title, node.nid AS nid_1, MIN(node.nid) AS nid, 'node' AS field_data_field_termid_node_entity_type, 'node' AS field_data_field_image_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_termid} field_data_field_termid ON node.nid = field_data_field_termid.entity_id AND (field_data_field_termid.entity_type = :views_join_condition_0 AND field_data_field_termid.deleted = :views_join_condition_1)
LEFT JOIN {field_data_field_image} field_data_field_image ON node.nid = field_data_field_image.entity_id AND (field_data_field_image.entity_type = :views_join_condition_2 AND field_data_field_image.deleted = :views_join_condition_3)
WHERE (( (node.type IN  ('test')) AND (field_data_field_termid.field_termid_value IN() ) ))
GROUP BY field_data_field_termid_field_termid_value, field_data_field_image_field_image_fid, node_title, nid_1
LIMIT 10 OFFSET 0

I notice the argument is not being passed into the IN() function - maybe that is the problem - though I do not see how. I have created a separate issue for that.

EDIT: For the sake of simplicity I have replicated the issue on a simple view that demonstrates the issue on a new install and latest dev views - and avoids all the complexity of my original view.

A simple way to replicate this problem is to do the following:

create a view on a node that has an image field that supports multiple images.
In the fields select the nodeid, the image and the title. Configure the image to show just one image.
Use a table style, filter the view by node type and save the view.

This view preview should correctly list all nodes with only one image displayed.

Now select "Use Grouping", check the box and "Update the view.

The view preview will now immediately display duplicate rows for nodes where the image field has multiple images.

AS I am certain that there is a problem and its not me doing something silly -I have been able to replicate the issue on a new install -I am upgrading it and marking it as active. This will be a major issue for anyone needing this functionality.

Great news that progress is being made on this. Does this however fix this issue? The original view I used listed nodes that had multiple images attached. The view restricted the number of images dispayed to one in the "Multiple fields Settings" of the Image. This worked until a further field was added on which a group by was used - this was where the original issue arose - and your patch has hopefully fixed.

However you mentioned that a delta filter might be required. Does this mean that the patch will break the Image limiting when used with "Group By"? If this is true - then delta filtering is required otherwise this issue is not fixed. The problem has simply moved!

I am concerned that if delta filtering IS required - there is no issue open to ensure it gets done. I will of course test the latest dev ASAP to test your patch but feel that closing this issue is perhaps a little premature.

I have had a chance to update to the latest dev views and there appear to be a number of problems.

1) As soon as "Group By" is selected - images cease to display and a number of notices appear ( see below ). These notices repeat for each row displayed.

# Notice: Undefined index: uri in theme_image_formatter() (line 533 of C:\Users\George\websites\localtest\public_html\modules\image\image.field.inc).
# Notice: Undefined index: alt in theme_image_formatter() (line 534 of C:\Users\George\websites\localtest\public_html\modules\image\image.field.inc).
# Notice: Undefined index: title in theme_image_formatter() (line 537 of C:\Users\George\websites\localtest\public_html\modules\image\image.field.inc).

2) I can see no Delta filter available for the image field.

3) The view still displays duplicates ( but without the images )

I experienced this on the simple view posted above ( as well as the original ). If there is any further info you need, please let me know.

I am using the official D7 core release.

The view does not group on the images ( at least I don't need it to ), grouping on ANY additional field causes the problem. #18 is correct.

Whilst I am very familiar with SQL group by - I am not familiar with the current views implementation, so I am not sure I can speak with any great authority. Essentially I am grouping on the Node ID, if I was working in SQL, I would group on one of the available fields, typically node id ( or entity id? ) - but confusingly - it doesn't seem to work that way in views - there seems to be no way to explicitly say "Group on this Field". Be that as it may, the grouping seems to work, the problem comes when a multi-field like image is involved.

I really liked the idea of having a delta filter for the image field ( I looked, but could'nt find one ), surely that would resolve the image duplication issue? This approach would need to consider an image field with no image and consistancy with the "Multiple Fields" option on the image field.

@merlinofchaos: You make a good point, I'm in the mysql "weird" camp! I was not aware that mysql's implementation was non standard ( it's been a VERY long time since I looked at SQL standards!) or that psql enforced all fields being in the group by. I can see that that does restrict HOW the group by's can be constructed. In mysql - it's the developer's responsibility to ensure that fields not in the group by - don't vary - or the query messes up.

I've been mulling this issue over myself. As has been pointed out in- #8,#9.#10 there is an incompatability between fields that can contain multiple values like image and "Group By". This is because fields like image are not really single fields - but can represent multiples. What is normally a single value - is really an array of values.

So perhaps the solution to this is to follow the logic and handle this type of field - not as a single vlaue - but as an array of values. Such fields might return an array of data to their display handlers, each row of the array representing a different delta.

In what I believe to be the most common scenario, a single value only is required - this could be implemented using a delta filter and would return an array with just one delta row ( or an empty array if no value found), without requiring much additional processing. In cases where multiple values are required, ie you want 2 or more images displayed per line - without duplicating rows - a further query could post-fetch and process multiple data into the field array ( one row per delta). I hate the thought of yet another query - but logically I can see no other way.

In a way this seems like the purest solution and properly reflects the nature of "multi-value" fields, additionally this approach could maintain compatability with the existing multi-field functionality. The down-side is that for those cases where more than one item of a multi field needs to be captured/displayed - another query must be run - but given that at that point - details of the result set are known - there could be some efficiencies found.

JMHO. I know things may be more complicated but I hope my perspective may help inspire a solution.

I can confirm that the patches appear to work well for me! I cannot be sure of the "Fix_data_absence.patch" as I am not sure if my code exercises it. Great work!

I'm not entirely sure how the system works, but I believe that before this issue can be marked "Reviewed & tested by the community", the code must not only be tested by several people but also the code reviewed by someone who is familiar with the current drupal coding standards ( which I am not ).

Also because the nature of this patch has architectural & design implications that are fairly fundamental, I believe this patch needs to be reviewed by one of the core developers. If my understanding of the patch is correct ( correct me if I'm wrong ), in group by cases, deltas are limited to one - which is probably correct for the majority of cases - but the approach is not consistant with the way views handles images without a group by,ignoring all the image multi field setups. Also I can think of many scenarios where something different may be required - like showing multiple images or tags per node. As previously pointed out by @merlinofchaos this is a difficult problem - but i do not think it is impossible and we need to think carefully before closing the door on handling this.

Don't get me wrong, I think this is an important patch that is urgently needed now and it certainly fixes the problem for me... but to progress it into the dev we must get the attention of a core developer who will have a broader view. I think this patch needs to go in as soon as possible but we should also consider opening another issue to get the whole thing working consistantly - no matter how difficult that my be.

JMHO

@merlinofchaos - I've tried the latest dev version (2nd June 2011) and the patch does not seem to work for me. Images all disappeared and I was seeing duplicates again.

I looked for where I could explicitly set the "group by" to entity id but could see nothing - am I missing something?

Edit: I'm also seeing a lot of notices ....

Notice: Undefined index: uri in theme_image_formatter() (line 532 of /home/common/drupal/versions/drupal-7.2/modules/image/image.field.inc).
Notice: Undefined index: alt in theme_image_formatter() (line 533 of /home/common/drupal/versions/drupal-7.2/modules/image/image.field.inc).
Notice: Undefined index: title in theme_image_formatter() (line 536 of /home/common/drupal/versions/drupal-7.2/modules/image/image.field.inc).

If you need any more info let me know ...

tx - That got it. Thank-you!