Views 3.18 > 3.20 update problem with duplicate/group_by [#2985178]

After updating from Views 3.18 > 3.20 we noticed duplicate entries on some the previously working views.

[EDIT new finding]
I found that the issue is the taxonomy relationship (see image)

This was not causing duplicates prior to this update.

Perhaps it's the same issue as https://www.drupal.org/project/views/issues/2929804?

The only difference in query seems to be the Group By described below.

[Group by difference - old diagnosis] (I first thought this was the issue, but it seems to be related to taxonomy relationship, see above)

I noticed the difference in query was that many more fields were being added to 'Group By' on 3.20.

We have a custom code that adds nid field and a group by using query alter:

/**
 * Implements hook_views_query_alter().
 */
function hook_views_query_alter(&$view, &$query) {

  // On what views should we group by nid?
  $group_by_views = array(
    'college_program_listing',
  );

  // Add group by nid to the views
  if (in_array($view->name, $group_by_views)) {
    $query->add_field('node', 'nid', '', array('function' => 'group_by'));
    $query->add_groupby('node.nid');
  }
}

Result

Old query with 3.18 + this code:
GROUP BY node.nid, nid

Now with 3.20 + same code:
GROUP BY node.nid, nid, taxonomy_term_data_node.name, node.title, field_data_field_program_polaris_id.field_program_polaris_id_value

I'm assuming this is what's causing the duplicates. [Not anymore seems to be the taxonomy relationship described above]

Thanks!
Sia

Comment	File	Size	Author
#7	mysql-group-by-duplication-workaround-2985178-7.patch	1.83 KB	nagy.balint
#7	7.x-3.x: PHP 7.1 & MySQL 5.5, D7 222 pass PHP 7 & PostgreSQL 9.1, D7 run-tests.sh exception PHP 7 & SQLite 3.14, D7 218 pass, 68 fail
#2	Screenshot 2018-07-11 13.37.20.png	442.66 KB	Siavash

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

11 July 2018 at 18:02

Siavash created an issue. See original summary.

Comment #2

Siavash CreditAttribution: Siavash commented 11 July 2018 at 18:38

Issue summary:

View changes

File	Size
Screenshot 2018-07-11 13.37.20.png	442.66 KB

Comment #3

Siavash CreditAttribution: Siavash commented 11 July 2018 at 18:49

Issue summary:

View changes

Comment #4

nagy.balint CreditAttribution: nagy.balint commented 15 July 2018 at 12:29

I have the same issue.

before:
GROUP BY uid, field_data_profile_firstname_user_entity_type, field_data_profile_lastname_user_entity_type, field_data_og_user_node_user_entity_type

after:
GROUP BY uid, users.uid, field_data_profile_firstname_user_entity_type, field_data_profile_firstname.profile_firstname_value, field_data_profile_lastname_user_entity_type, og_membership.created, field_data_og_user_node_user_entity_type, users.name

Comment #5

nagy.balint CreditAttribution: nagy.balint commented 15 July 2018 at 12:30

Comment #6

nagy.balint CreditAttribution: nagy.balint commented 15 July 2018 at 13:34

I tracked this issue down to this commit: https://cgit.drupalcode.org/views/commit/?id=82987986d4ca7b4efc91ecfcbed...

It works fine just before it.

Comment #7

nagy.balint CreditAttribution: nagy.balint commented 15 July 2018 at 15:08

File	Size
mysql-group-by-duplication-workaround-2985178-7.patch	1.83 KB
7.x-3.x: PHP 7.1 & MySQL 5.5, D7 222 pass PHP 7 & PostgreSQL 9.1, D7 run-tests.sh exception PHP 7 & SQLite 3.14, D7 218 pass, 68 fail

Here is a workaround that only adds the fix from the previous issue for pgsql, while keeping the old behavior elsewhere, and so keeping backward compatibility for mysql.

This is just a quick workaround, likely a better fix can be made.

Comment #8

chrisgross CreditAttribution: chrisgross commented 24 July 2018 at 19:08

Priority:

Normal

» Critical

I am seeing this problem, as well, only as early as 3.19. This happens even if I turn off all grouping, aggregation and exposed filters. All I did was add a criteria to sort my nodes by taxonomy term weight, and I started getting duplicates where I did not have them on 3.18. I think this issue is actually critical because it has probably broken a huge number of views. #7 does fix this for me.