I was working on a view that listed faculty members on a Drupal site. The request was pretty straightforward. We have profiles on the site and each profile has one or more categories such as "Core Faculty", "Affiliate Faculty", "Administration", "Staff", "Graduate Student", "PhD Student", etc. This is done with a taxonomy term on the profile content type.
Now that we know how the content type is set up, we want to have a view that displays just faculty. By default this view should show all profiles who have the term "Core Faculty" and/or "Affiliate Faculty". This is simple to do. The hard part is they want an exposed filter that so that they can refine the list to just one of the two categories.
After messing around I eventually thought about using a exposed grouped filter on category. I set it up so the default is both categories and it's not optional so it forces the filter on the initial load.
However, this came with an unfriendly error:
I can see what it's trying to do in the query and why it's invalid.
WHERE (( (users.status <> '0') AND (users.uid > '1') AND (field_data_field_category.field_category_tid = '1', '2') ))
If we just change the field_category_tid = '1', '2' to field_category_tid in('1', '2') we'd be golden. I did some Googling around for this error message and I found an open issue and a workaround. Apparently all you need to do is select "allow multiple selections", save, and then uncheck "allow multiple selections" and save again. Clears it up nicely.
This workaround has been around for 3 years, and it still hasn't been fixed. I am by no means an expert at views and how views constructs queries, but if you are or have the time to investigate it, feel free to work on the issue. It looks like there is also an issue for it in Drupal 8.