Incorrect filter group OR behavior, LEFT JOIN changed to INNER JOIN

I can report the group by function is not working too. I upgraded from 7-3.3 to 7-3.5 and my views with simple grouping by month no longer group.

Code: (group by is not included in the query)

SELECT node.changed AS node_changed, history.timestamp AS history_timestamp, node.created AS node_created, node.title AS node_title, node.nid AS nid, 'node' AS field_data_body_node_entity_type
FROM 
{node} node
LEFT JOIN {history} history ON node.nid = history.nid AND history.uid = '1'
WHERE (( (node.status = '1') AND (node.type IN  ('article')) ))
ORDER BY node_changed DESC
LIMIT 5 OFFSET 0

Grouping should be on the node.created field and is setup to do so in the settings.

Okay my issue was caused by theme developer and simplehtmldom modules. When these are both enabled, the group by is not inserted into the query. I turned them off and views 7-3.5 started working as it had been.

Hi. I've the same problem.
how i can resolve it?

thanks

I have the same problem and have temporarily plugged the hole by changing includes/handlers.inc line 896 in function ensure_my_table() from

          // query optimization, INNER joins are slightly faster, so use them
          // when we know we can.
          $join = $this->get_join();
          if (isset($join)) {
            $join->type = 'INNER';
          }

to

          // query optimization, INNER joins are slightly faster, so use them
          // when we know we can.
          $join = $this->get_join();
          if (isset($join)) {
            $join->type = 'LEFT';
          }

I haven't had time to text extensively, but it hasn't broken any of my other views so far.

EDIT: This isn't meant as a long-term solution. I just wanted to post a quick fix that worked in my case. If you use this, you should at least check that your other views work as expected. In summary, use at your own risk.

It works for me, however: all list-based views have to be re-edited and saved after the patch commit.

i had the same issue when going from 3.3 to 3.5; but saw a few different things

- i have now an INNER join where there used to be a LEFT (as reported)
- also have and AND in my WHEREwhere the OR used to be
- the OR'ed filter groups, even though they showed correctly in the views admin form; when i go to rearrange, even though i see a 2nd filter group; all of the filters are in the first group (hence the AND). moving them to the 2nd group and re-saving fixes the AND back to an OR

the incorrect inner join is being used with a boolean field; if that makes any difference.. trying patch out now.

the fix in #5 fixes for me; not sure what the patch #7 is about; doesn't seem to be related to this issue.

Patch 7 also worked for me, I overlooked patch 5. I will try it tomorrow. I think it is better to have a patch that changes as little as possible.

yes, patch in #7 works as well (changes from #5 reverted)

The patch in #7 works for me as well.

See also:
#1601036: Views: Use LEFT JOIN not INNER, and the issue it is marked as a duplicate of:
#1781744: Draft and Needs review pages are broken

Is the workbench issue now a views bug? Cannot review on mobile :-(

If a large number of valid views is broken and several modules I think it is.

I'd agree with hass...since this likely breaks a huge number of views, I don't understand why it wouldn't be 'critical'.

OK :-) Apologies to you guys working so hard on putting Views in core (my favorite of the many pieces of good news for D8).
As a summary, this issue is causing views with a SQL query containing a logical structure as simple as the following to not work correctly:

  AND A, B
OR
  AND X, Y

Given such a basic structure, it's likely that a huge number of views are not working correctly, so the priority has been changed to critical.

Yeah, this is definitely a critical bug. It basically broke a bunch of our views.

I made the #7 patch some weeks ago, and everything worked.

A few weeks pass ...

... and view is back to old bad behavior. Checked patch, and it is still in place:

<?php

/**
 * @file
 * Definition of views_handler_filter_field_list.
 */

/**
 * Filter handler which uses list-fields as options.
 *
 * @ingroup views_filter_handlers
 */
class views_handler_filter_field_list extends views_handler_filter_many_to_one {

  function get_value_options() {
    $field = field_info_field($this->definition['field_name']);
    $this->value_options = list_allowed_values($field);
  }
}

Not sure what else might be going on ...

-----

edit #1: I looked at the current dev release ( http://ftp.drupal.org/files/projects/views-7.x-3.x-dev.zip ) and it contains the unpatched code.

Have not updated, just trying to find code that works. See #24. Patch still in place, but error has returned regardless.

Also tried code from the 3.3 release ( http://drupal.org/node/1451068 ) and it failed as well. Probably because the function name is different than in the patch.

Agh. You are correct. That was a whole day wasted because someone on else on the project team unwisely fiddled my previously applied (and functional) patch.

Applied, but I am still having this issue again.

So I totally see all your points that the initial in #1494884: Why is there no "is all of" operator for select list filters? change was wrong
though I'm wondering how much we would break by moving it back.

At the same time we then probably would need some code for converting new created views back, similar like the current init() code does it and maybe even add support for "is all of". What do you think?

This also occurs in 6.x-2.16. Should we await a solution? To avoid hacking/patching I changed my model a bit.

subscribe.. this is crippling a view that I need for a site I'm building. I don't think it's possible to build the view without this working properly.

I agree with runeasgar. This issue is breaking a view that is needed for a site that is almost ready to go live.

For the issue with INNER JOIN instead of LEFT JOIN, without adding the patch in #7, you can select the Reduce duplicates checkbox from the first field in OR group filter and will transform your query to LEFT JOIN.
For more info check handlers.inc at line 890:
if ($this->handler->operator == 'or' && empty($this->handler->options['reduce_duplicates']))
which makes your query use INNER JOIN, so checking the reduce duplicates option takes you to line 960, using LEFT JOIN.

EDIT: You still need to export your views to code.

Patch from #7 works for me as interim solution in my drush make file.

As @dawehner said in #31 we cannot commit this as is => needs work.

I have no idea how to resolve this appropriately as I don't have the big picture of implications, so ideas welcome.

I applied the patch in #7 against 7.x-3.7 and it is not working for me.

I had thought this was an issue with Field Collections but it may be this issue. I had opened this up as #1993298: Views does not work with OR .

#36 works for me

racked my brain on this for many hours thinking "usually my brain/logic is wrong, not the program"....after switching around my logical operators several different ways I broke down to digging into the SQL. Quickly found this issue of 'inner join' and this helpful article.

Patch #7 worked for me too. I think this should be included in the next release.

Patch #7 saved my day. Thank you !

#36 works for me

I do not like to hack the module,
if the patch works it should be added to dev.

My solution witch worked for my situation is to negate the query.
instead of
Contentype=A or B
And
Tax_contentA= value1
or
Tax_contentB=value2

I made
Contentype=A or B
And
Tax_contentA not one of (all the other values except value1)
And
Tax_contentB not one of (all the other values except value2)

Which came out as a query with Left join and works

#36 worked for me. Thank you so much for the fix.

#36 sorted it our for me

Thought I was going crazy because I couldn't figure out why my filter wasn't working, but #36 worked for me as well. Thanks sorin.eugen.

Fixing the many-to-one helper (used by the many-to-one filter) will be nontrivial. It seems riddled with INNER joins, none of which will work when the filter group operator is OR or when the inter-filter group operator is OR. There are also "summary joins", which use INNER and I'm unsure about.

I wish I could offer a patch, but after looking at it for a couple of hours, it's clear that someone that really understands the architecture of how joins are determined needs to do this. In the interim, I'm using the reduce duplicates workaround.

#935984: or filter uses inner join for optional fields thus excluding expected results was marked a duplicate of this, but that was about terms (the bug I'm seeing) not fields per-se (the bug described here).

Just wanted to note that this bit me too, noticed the INNER JOIN was excluding half the results, and just checking the "Reduce Duplicates" box on the boolean field that changed the query from LEFT to INNER switched it back to a LEFT JOIN, and all was well. See also (on Drupal Answers): Changing an inner join to a left join in views 3.3?

I found another workaround when I was working around this bug.

Inner join:
Node status = 1
Node promoted = 1
AND
Field value = 1 OR
Field is empty

Left Join:
Node status = 1
Node promoted = 1
AND
Field is empty OR
Field value = 1

Thanks sorin.eugen for the workaround in #36. I used "reduce duplicates" on the field that was causing the problem and that fixed it. That's much easier than continuing to have to remember to re-patch Views every time I upgrade!

I just applied to 7.x-3.8 and it fixed the issue for me. This seems like a truly critical (regression) bug. I know the maintainers are probably focused on D8. Has anyone tested if this is not an issue in D8 or does it need to be fixed there first?

The patch in #7 has always worked for me; but i just made a simple filter arrangement today as: http://screencast.com/t/2awrl0pfbo

and i was getting inner join (broken)

until i did the solution in #36 and set Reduce Duplicates on one of the filters.

#36 worked for me as well, thanks for the tip.

The workaround from #46 did work for me.
I must add it did needed some experimenting with the order of the filter items and groups to get it working.

I would be interested in a long term solution for this issue as well. Based on everything I've read here I'm hesitant to build a complex view where this issue would ultimately arise and apply patches that may not work long term.

After applying the patch I had to refactor some of my views - usually those which used a field as a filter. When I opened the views administration page of such a view an error occurred:

Notice: Undefined index: or in views_handler_filter_in_operator->admin_summary() (line 307 of \views\handlers\views_handler_filter_in_operator.inc).

Additionally in preview it was stating that there is not a valid operator. When opening the settings of this filter no operator was chosen. I needed to re-activate the "Is one of" or another operator and the errors disappeared.

Best,
Tobias

patch #7 is not a proper solution according to view architecture, it basically tries to by-pass the views_handler_filter_many_to_one instead tries to use the views_handler_filter_in_operator, which is basically parent class of views_handler_filter_many_to_one. problem is mentioned in #5, this is the actual reason to get the INNER join in the query

After spending far too many hours trying to figure out what was wrong, and finally searching through the issue queue, I found this. I was able to use the workaround in #36 to get the View I am trying to build to work as expected.

Seems kind of absurd we're going on three years to resolve what seems to be a simple regression...

#36 seems to work for me too

workaround in #36 works for me but I have to check the Reduce duplicates checkbox on all filters that propose it (some don't), and I have do do it for filters in all of my 3 groups.

Hi,
I encountered the same issue when trying to filter on list fields which are not mandatory (could be empty) so the IN clause could not works and the INNER JOIN reduce the results to entities which HAVE data existing in the fields...

My case was :
some other exposed filters...
AND
field1 is one of a OR b (Exposed, Allow multiple selections)
AND
field2 is one of x OR y (Exposed, Allow multiple selections)

Result: nothing, usage of INNER JOIN, etc.

My solution came from #46 (thanks!), and I came to something like:
some exposed filters...
AND
field1 is one of a OR b (Exposed, Allow multiple selections)
OR
field1 is not one of a AND b (both options selected in the filter params)
AND
field2 is one of x OR y (Exposed, Allow multiple selections)
OR
field2 is not one of x AND y (both options selected in the filter params)

It's quite complicated, but it works...

Note: I also tried "field1 is empty" for the second test in the OR clause, but without success, all entities were shown regardless of exposed filters values.

I'm running Views 7.x-3.14 and I don't understand how to apply the recommended work-around in #36. There is no "reduce duplicates" checkbox on the filter settings. Does this only apply to views that are using aggregation?

My view is a simple one as described in #23, with an OR being counteracted by the INNER JOIN in the query.

I'm hesitant to use the patch in #7 because it's been years since that feature went in. Reverting it now may break I-don't-know-how-many other views on the site.

Can someone explain again why the solution in #5 is not the right approach? I understand that was not the file that was changed the day that everything broke, but that doesn't mean it is not the place for a solution today.

The following code comment is present:

// query optimization, INNER joins are slightly faster, so use them
// when we know we can.

It sounds like we are using inner joins here even when we know we can't. Is there a way we can expand the logic to properly determine if the JOIN should be INNER or LEFT? Perhaps adding a check to see if any of the filters are using OR instead of AND?

Attaching a patch based on #5, but leaving as NW since I expect this will cause a minor performance regression. Still better than broken views IMHO.

Agree with @jenlampton. The comment in the code apparently indicated that we can NOT use INNER join.
Applying the patch in #69 fixed the issue temporarily.

@jenlampton Your patch views-critical_inner_vs_left_join-1766338-69 worked for me, Thanks!

Selected checkbox "Reduce duplicates", the image given me to realize the request because LEFT JOIN and not INNER JOIN because.

Patch still applies cleanly to 7.x-3.15.

5 years later, the patch still works : what's the police doing ?

Patch in #69 still applies cleanly to 7.x-3.16.

Agreeing with @jenlampton. The use of "INNER JOIN" here is a false optimization.

My issue is the following (stripped down example):

1. to a content type with existing content: add a boolean field
2. to a view for published content of that type: add the new field to the filter as OR - Is one of - 1
3. Expected: No change on the result list
4. Actual: No results

Possible Solutions:

1. save all existing content - "missing" values are written to the new field table - make the DB consistent
2. change new criteria to Is none of - 0 - negate the condition
3. change new criteria to Reduce duplicates - checked
4. apply patch #1766338-69: Incorrect filter group OR behavior, LEFT JOIN changed to INNER JOIN
5. ...

I guess the query optimization is based on the assumption that the relationship of fields and nodes is consistent. Meaning there is a value row in the field table for every node of a given type. But Drupal allows this inconsistency.

I've tried to finde the introduction of this optimization and found #248749: Consider Use of Inner Joins for Taxonomy Filters . It was introduced in views-6.x-2.0-alpha5 (http://cgit.drupalcode.org/views/commit/?id=1f4cbd4).

It was intended for taxonomy filters but now applies to "boolean" filters as well (by #1494884: Why is there no "is all of" operator for select list filters?).
I've checked it - the false assumption of consistency applies to taxonomy filters as well...

Is this optimization still in views for Drupal 8? The use case above with a boolean field is no issue in D8 (so with less options to select from...).

So how to get patch #1766338-69: Incorrect filter group OR behavior, LEFT JOIN changed to INNER JOIN applied to views?
IHMO the issue title is misleading - its an old bug, not a regression...
Can someone help to rephrase it?

I've checked my Issue from #77 on D8 with a List (text) ans Taxonomy Term field. And it still doesn't work there.

#2559961: ManyToOneHelper ignores group configuration for some cases Mentions this one

Moving it to Drupal core as per #2559961-22: ManyToOneHelper ignores group configuration for some cases.

Patch in #69 still applies cleanly to 7.x-3.17.

Patch in #69 still applies cleanly to 7.x-3.18.

Is there a way we can expand the logic to properly determine if the JOIN should be INNER or LEFT? Perhaps adding a check to see if any of the filters are using OR instead of AND?

@jenlampton Regarding this, isn't this only a problem when using OR conjunctions? Can't we simply check for this, e.g.

$group = isset($this->handler->options['group']) ? $this->handler->options['group'] : FALSE;
if (isset($join) && ($group === FALSE || $this->handler->query->where[$group]['type'] == 'AND')) {
 $join->type = 'INNER';
}

I've attached a patch against 8.4 for my suggestion above. Reviews and better ideas are welcome :)
Also added the "Needs tests" tag.

Patch #7 worked for 7.x-3.x. Thanks for the fix.

@jenlampton Thanks for the patch in #69. I think your solution is right-on, and I don't think that there is a large enough performance hit for this not to be included in the next release. THANK YOU!

#84 applied cleanly for me on 8.4.5, but did not fix the problem -- below is the query generated by my view, before and after the patch at #84 -- it looks like, one of the JOINs changed, but the the "AND" did not change to an "OR":

pre-patch...

SELECT node_field_data.langcode AS node_field_data_langcode, node_field_data.title AS node_field_data_title, node_field_data.nid AS nid
FROM 
{node_field_data} node_field_data
LEFT JOIN {node__field_location_level} node__field_location_level ON node_field_data.nid = node__field_location_level.entity_id AND node__field_location_level.field_location_level_value = 'state'
INNER JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid
LEFT JOIN {taxonomy_index} taxonomy_index2 ON node_field_data.nid = taxonomy_index2.nid AND taxonomy_index2.tid != '109'
LEFT JOIN {node__field_associated_parent_page} node__field_associated_parent_page ON node_field_data.nid = node__field_associated_parent_page.entity_id AND node__field_associated_parent_page.deleted = '0'
WHERE (((node__field_location_level.field_location_level_value IS NULL)) AND ((taxonomy_index.tid = '109')) AND ((taxonomy_index2.tid = '98')) AND ((node__field_associated_parent_page.field_associated_parent_page_target_id = '71' ))) AND ((node_field_data.status = '1') AND (node_field_data.type IN ('location_filtering')))
ORDER BY node_field_data_title ASC

post-patch...

SELECT node_field_data.langcode AS node_field_data_langcode, node_field_data.title AS node_field_data_title, node_field_data.nid AS nid
FROM 
{node_field_data} node_field_data
LEFT JOIN {node__field_location_level} node__field_location_level ON node_field_data.nid = node__field_location_level.entity_id AND node__field_location_level.field_location_level_value = 'state'
LEFT JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid
LEFT JOIN {taxonomy_index} taxonomy_index2 ON node_field_data.nid = taxonomy_index2.nid AND taxonomy_index2.tid != '109'
LEFT JOIN {node__field_associated_parent_page} node__field_associated_parent_page ON node_field_data.nid = node__field_associated_parent_page.entity_id AND node__field_associated_parent_page.deleted = '0'
WHERE (((node__field_location_level.field_location_level_value IS NULL)) AND ((taxonomy_index.tid = '109')) AND ((taxonomy_index2.tid = '98')) AND ((node__field_associated_parent_page.field_associated_parent_page_target_id = '71' ))) AND ((node_field_data.status = '1') AND (node_field_data.type IN ('location_filtering')))
ORDER BY node_field_data_title ASC

 
(...)
For comparison, here's the same query using the patch at #29 over on #2559961: ManyToOneHelper ignores group configuration for some cases -- see the nested "OR" in the last part of the WHERE clause:

SELECT node_field_data.langcode AS node_field_data_langcode, node_field_data.title AS node_field_data_title, node_field_data.nid AS nid
FROM 
{node_field_data} node_field_data
LEFT JOIN {node__field_location_level} node__field_location_level ON node_field_data.nid = node__field_location_level.entity_id AND node__field_location_level.field_location_level_value = 'state'
INNER JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid
LEFT JOIN {taxonomy_index} taxonomy_index2 ON node_field_data.nid = taxonomy_index2.nid AND taxonomy_index2.tid != '109'
LEFT JOIN {node__field_associated_parent_page} node__field_associated_parent_page ON node_field_data.nid = node__field_associated_parent_page.entity_id AND node__field_associated_parent_page.deleted = '0'
WHERE ((node__field_associated_parent_page.field_associated_parent_page_target_id = '71' )) AND (((node_field_data.status = '1') AND (node_field_data.type IN ('location_filtering')) AND ((node__field_location_level.field_location_level_value IS NULL))) AND (((taxonomy_index.tid = '109')) OR ((taxonomy_index2.tid = '98'))))
ORDER BY node_field_data_title ASC

 
(...)
Bottom line for *me* is that I'm all set for now, using #29 from the aforementioned issue, but I figured I'd still share my test results over here!

@alisonjo2786 Thanks for the feedback and the example queries!

I think that issue (#2559961: ManyToOneHelper ignores group configuration for some cases) and this one are actually different problems, and depending on the use case, you would need fixes for both, i.e. have both the INNER JOIN change to a LEFT JOIN (this issue) and apply the OR/AND operator as defined in the filter group.
Have you tried using both patches at once?

Disecting your query generated with issue 2559961, the simplified WHERE structure is this:

(node__field_associated_parent_page.field_associated_parent_page_target_id = '71' )
AND 
(node_field_data.status = '1') 
AND 
(node_field_data.type IN ('location_filtering')) 
AND 
(node__field_location_level.field_location_level_value IS NULL)
AND 
(
	(taxonomy_index.tid = '109')
	OR 
	(taxonomy_index2.tid = '98')
)

This uses an INNER JOIN {taxonomy_index} taxonomy_index.

The WHERE query part from #84 would be this (reordered and re-indented):

(node__field_associated_parent_page.field_associated_parent_page_target_id = '71' )
AND 
(node_field_data.status = '1') 
AND 
(node_field_data.type IN ('location_filtering'))
AND 
(node__field_location_level.field_location_level_value IS NULL)
AND 
(taxonomy_index.tid = '109')
AND 
(taxonomy_index2.tid = '98')

This one uses a LEFT JOIN {taxonomy_index} taxonomy_index.

In your case this should not make a difference, but if you weren't joining with the same table for both OR connected conditions, the result would most likely still be wrong if only one table has a JOIN partner. Then the whole query would fail, instead of just one of the ORs.
Basically, this issue's patch only handles the LEFT vs. INNER JOIN logic, whereas the other one only handles the AND vs. OR condition logic.
Using one fix without the other still produces unexpected results.

I've also added #2580265: entity query nested conditions must use LEFT joins when any of the parent condition groups is using OR as a related issue, this is getting quite messy.

Do we still need to apply patch #69 to views 7.x-3.20? looks like yes but checking.

[ I patched by had because the line numbers have changed, but a simple patch]

It does look like this problem still exists, but the patch in #69 no longer applies. We will have to do some digging to see how to make it apply.

I re-did the patch using the same change as jenlampton in #69 - THANK YOU for creating the original. It would be great to get this into the next views release.

This issue moved to Drupal Core (see #79 #1766338-79: Incorrect filter group OR behavior, LEFT JOIN changed to INNER JOIN)
Drupal 7 does not contain Views.
I think testbot tries to apply the patch to drupal-core. That fails because there is no Views module.

I think the current strategy is to fix d8-views first and to backport the solution to d7-views.

I think the current strategy is to fix d8-views first and to backport the solution to d7-views.

I agree, let's get this fixed in D8 so we can backport to D7!

Marking as Needs Review for the only D8 patch we have, in #84 (the problems mentioned in #88 are most likely due to interactions with #2559961: ManyToOneHelper ignores group configuration for some cases).

fwiw I have a view with grouped filters in the following structure:

group 1
  condition 1
  AND condition 2
AND
group 2
  condition 3
  OR condition 4

I applied the patch in #84 but I also needed the one from here: https://www.drupal.org/project/drupal/issues/2559961#comment-12715853. Together these 2 patches resolved the issue and the view is returning the correct results.

This on D8.6.7

The patch in #94 still applies cleanly to views 7.x-3.22. Thank you to @John Pitcairn and @andyanderso for re-applying the patch! Sad it wasn't included in the last release, fingers crossed it'll go into the next one!

+1 to including it in the next 7.x release.

The patch in #94 still applies cleanly to views 7.x-3.23. RTBC for D7.

@DamienMcKenna would it be useful to create a separate non-core issue for D7 (for tests) or do you prefer to stick to a single issue?

+1 to include this in the next release (since apparently it didn't make it to 3.23).

It's a straightforward regression and everyone is more hesitant to apply crucial security updates if there is a possibility for listings in their website to break.

I had an issue that with taxonomy terms, the join is still using inner. See https://www.drupal.org/project/drupal/issues/2559961#comment-13291136

group 1
  condition 1
  AND condition 2
OR
group 2
  condition 3
  AND condition 4

I updated the patch to exclude inner joins on taxonomy terms. Please review!

Moved parenthesis to correct location in patch. Please review this one!

+++ b/core/modules/views/src/ManyToOneHelper.php
@@ -162,9 +162,11 @@ class ManyToOneHelper {
+          if (isset($join) && ($group === FALSE || ($this->handler->query->where[$group]['type'] == 'AND' && $this->handler->options['plugin_id'] != 'taxonomy_index_tid'))) {

Having a hardcoded reference to the 'taxonomy_index_tid' plugin in the ManyToOneHelper sounds less than ideal. Is there no other way to detect that this is needed?

@Lendude,

The only time I see is using the INNER join is when one (or more) of the fields are taxonomy terms. If its not a taxonomy term, then it will use the LEFT join.

Ideally it should check if is an OR grouping in the group, or any parent group, but I don't see a way to do that.

Agreed that ideally we don't have hardcoded corrections, but until another way is contrived, let's not let the perfect be the enemy of the good and get this needed fix in.

Like @specky_rum, I had to also apply the patch from ManyToOneHelper ignores group configuration for some cases. It works perfectly now :)
Drupal 8.7.5

We need to update the core version to the current one

The reason that the problem is related to taxonomy filter may be that this filter allows you to select "Reduce duplicates". Other filters do not have this setting.

https://git.drupalcode.org/project/drupal/blob/8.8.x/core/modules/views/src/ManyToOneHelper.php#L160.

if ($this->handler->operator == 'or' && empty($this->handler->options['reduce_duplicates'])) {
  if (empty($this->handler->options['add_table']) && empty($this->handler->view->many_to_one_tables[$field])) {
    // query optimization, INNER joins are slightly faster, so use them
    // when we know we can.
    $join = $this->getJoin();
    if (isset($join)) {
      $join->type = 'INNER';
    }

See also https://www.drupal.org/project/drupal/issues/2559961#comment-13347356

The patch in #94 still applies cleanly to views 7.x-3.24.

I had a similar bug on my SQL request from the view with Drupal 8.8.

My filters were like this:
(Condition 1 AND Condition 2) OR (Condition 3 AND condition 4)

And views just translate it like this:
(condition 4 AND Condition 1 AND Condition 2) OR (Condition 3)

I just checked the ""Reduce duplicates" option on my condition 4 and the SQL query was good again... Weird behaviour.

Verifying that the patch in #94 cleanly applies in 7.x-3.24.

@DamienMcKenna would it be useful to create a separate non-core issue for D7 (for tests) or do you prefer to stick to a single issue?

Since it's been way too long and the current issue is becoming a beast where the solved D7 part gets left behind for no good reason, I suppose that we move to the "duplicate" issue for D7 which can be found here.

It's been 8 years since this issue has been created and even though I'm all for backporting the eventual solution that may be found for D8, I'd prefer if we don't keep an obvious bug in the D7 branch and force users to re-apply patches on every Views update.

I'm updating this issue in the Views issue queue with the patch found in #94.

Please continue the discussion there for the 7.x branch of the issue and if there is a solution in the 8.x branch we'll see what we're going to do about backporting it.

I've added an additional check for grouping to the patch from #94 and posted an updated patch in the other issue. Noting here for those that were following this issue for a Drupal 7 fix.

I'm closing #2935113: No results when using the same vocab twice as exposed filter as a duplicate and I believe that it has a more concise set of steps to reproduce this, but I haven't been following this issue so I don't want to make major changes to the issue summary.

The steps are:

1. Install Drupal
2. Create an article with the tag 'test'
3. Create a view of articles and add 'Tags' as an exposed filter with 'Is one of', twice, using OR operator
4. Set both filters to 'test'
5. Confirm there are no results, when you should expect the test article to appear

I note that it works as expected if the exposed filters are set to 'Is all of'.

Patch #106 work for me together with https://www.drupal.org/project/drupal/issues/2559961#comment-13860069

I have a simple view with a filter to filter out based on 2 options in a list field (field_campaign_type). But as this was added after content was created; many nodes simply have the value as NULL.

With filter as: published AND (field = 'option A' OR field is empty)

This doesn't work at all as the OR isn't used in the query. Adding the "reduce duplicates" option to the 2 ORed filters seemed to fix in that it added the OR into the WHERE clause. But then, when setting some of my nodes to 'option B', those still show up as well. I see the query has an AND added to one of the LEFT JOINs:

LEFT JOIN node__field_campaign_type node__field_campaign_type_value_0 ON node_field_data.nid = node__field_campaign_type_value_0.entity_id AND node__field_campaign_type_value_0.field_campaign_type_value = '0'

removing the "AND node__field_campaign_type_value_0.field_campaign_type_value = '0'" and the view works as expected.

new MR available
the condition to use INNER for join: Only if there is no group with OR operator.
LEFT JOIN it's by default.

However this will not work without the solution for https://www.drupal.org/project/drupal/issues/2559961
which means that it can be really tested.

And a scenario to reproduce.
1. Create 2 taxonomies
and add some terms
2. Create 2 taxonomy term reference fields
And create different content
3. Create a view with 2 filters for those 2 fields
4. Play with AND and OR operators for those filters

Again the patch from https://www.drupal.org/project/drupal/issues/2559961 it's needed in terms to get the right results.

p.s. i have no idea why those 2 issues were separated.

Tests available on related ticket MR

please check
https://www.drupal.org/project/drupal/issues/2559961#comment-15085241

If this ticket isn't going to add tests and be dependent on another. Then it should be postponed.

But even then I think the tests should be different, as the tests on #2559961: ManyToOneHelper ignores group configuration for some cases should be testing that issue. If the tests are testing the same thing then these tickets would be dups of each other.

As the solution and tests are in the MR of the related issue ... i would say this is kind of duplicate.

closed also the MR ... test can't be added without the solution from related issue.

This is still an issue, but applying the code in the merge request worked very nicely. Can this be rereviewed?

As is the MR can't be merged as it's closed and No test coverage

If this ticket were to continue issue summary would have to be updated to standard template.

But would see if related issue that this was closed for solves the issue, depending on core version you are on.

Currently running Drupal 10.2.4, and when creating a View and adding an OR condition of two taxonomy fields, the resulting JOIN's on these tables remains as an INNER rather than a LEFT:

It seems the changes for this issue were not included in the related issue commits.

So i re-opened the MR and added the tests code from there.
i think we are back to review.

Thanks for reviving this and adding some tests.

However, the tests pass with or without the change, see https://git.drupalcode.org/issue/drupal-1766338/-/jobs/1278007 - so are they testing the correct thing?

@longwave thank you for the "guidance"
Also @SoulReceiver comments helped.
i remembered some things about this issue.

indeed the previous tests added tried for the related issue which actually solved this specific case when we have multiple "Has taxonomy term" filters.
And this multiple uses will bring "LEFT JOIN" from the second filter .. which means the tests will pass anyway.

The trick ... having the second taxonomy filter as "field filter". It still uses the "taxonomy_index_tid" filter plugin so, I think we're still in the right place and (+) it adds and extra test for taxonomy fields.
Also removed some tests that weren't that relevant here.

https://git.drupalcode.org/issue/drupal-1766338/-/jobs/1304700

I'm more than aware it's not the perfect/complete solution for the fix ... but at least it could save the day.
To save the (Views Filter Grouping) Universe ... I think another issue is needed ... starting maybe with some kernel tests about those FIlters GROUPING JOINS.

p.s. Views and tests needs a lot of efforts ... still

Can the issue summary be updated using standard template.

Original issue appears to have been written for D7.

Summary updated.

Issue summary appears much better

Ran test-only feature

There was 1 error:
1) Drupal\Tests\taxonomy\Functional\Views\TaxonomyIndexTidUiTest::testFilterGrouping
Behat\Mink\Exception\ResponseTextException: Failed asserting that the page matches the pattern '/ioy19583/ui' 1 time(s), 0 found.
Failed asserting that 0 is identical to 1.
/builds/issue/drupal-1766338/core/tests/Drupal/Tests/WebAssert.php:734
/builds/issue/drupal-1766338/core/modules/taxonomy/tests/src/Functional/Views/TaxonomyIndexTidUiTest.php:547
/builds/issue/drupal-1766338/vendor/phpunit/phpunit/src/Framework/TestResult.php:729
ERRORS!
Tests: 5, Assertions: 242, Errors: 1.

So shows coverage.

Manually testing following the steps I believe I am seeing the issue.

s an exposed filter with 'Is one of', twice

I probably would of just used one filter selecting the multiple options but maybe can understand scenarios were you would do it this way.

Change to ManyToOneHelper makes sense.

12 years wow, good job on this one!

I've tried to credit people who helped move this issue on and have been thanked on the issue for their contributions. Obviously with so many comments this can be hard to get right - hopefully I have. Please let me know if I've not.

Committed and pushed 0dac7cab32 to 11.x and 1c95773ffe to 10.3.x and c1c3ae32ca to 10.2.x. Thanks!