Using taxonomy exposed filter in a view ends in MySQL can only use 61 tables in a join

Question

Scenario

1. One hierarchical vocabulary called Countries with 300 taxonomy terms, one for each country, indenting like:

   • America (level 1)
   • • Anguila (level 2)
   • • Antigua y barbuda (level 2)
   • • ... (level 2)
   • Europe (level 1)
   • • Austria (level 2)
   • • Armenia (level 2)
   • • ... (level 2)

2. One content type called Office, then you add a field called Country, and its type is taxonomy reference, related to you recent created vocabulary called Countries.

3. After that, you create a few nodes: the first is called 'My secret office', checking Afghanistan in its country field. The second node is called 'My beautiful office', checking Albania.

Now you think it should be interesting if you could have a page where you can see a list of all your offices. The finest way to achieve this is by creating a view. To do so, you create a view page with this configuration:

• Filter criteria: Content: Content type (= Office)
• Page settings: Path: /offices

Then you visit your new page /offices and you can see 2 offices: My secret office and My beautiful office. Everything works as expected.

Then you add a country filter in you page. Going back to views configuration:

• Filter criteria: Content: Has taxonomy term (exposed, Show as hierarchical) related to Countries.

You return to visit your page, and you can see a filter listing 300 countries and your 2 offices. You are on fire, so you decide to test your brand new filter:

• You check Afghanistan option, and click Search; then you only see My secret office. Great.
• You check Albania option, and click Search; then you only see My beautiful office. Awesome.
• You now check Afghanistan, Albania, Algeria, Andorra, Angola and so on until you have checked all your 300 countries. The correct output should be your 2 offices, since you have no more; so you click search and BAAAM: White Screen of Death. But you are smart, and you have allowed PHP yo show the error log. You see that MySQL had a failure:

SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join

It follows the entire query Drupal is trying to execute. You notice everything is fine EXCEPT for this:

LEFT JOIN {taxonomy_index} taxonomy_index_value_0 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_0.tid = :views_join_condition_1

LEFT JOIN {taxonomy_index} taxonomy_index_value_1 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_1.tid = :views_join_condition_2

LEFT JOIN {taxonomy_index} taxonomy_index_value_2 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_2.tid = :views_join_condition_3

LEFT JOIN {taxonomy_index} taxonomy_index_value_3 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_4.tid = :views_join_condition_4

and so on. Drupal is using the left join technique to access the database. The problem is that Drupal is using one left join for each taxonomy termed checked, meaning the first one is related to Afghanistan, the second one to Albania, the third is for Algeria, and so on.

¿How can I make a search using more than 61 options?

I think it is not a crazy thing that an user could search for offices in America and Europe, resulting this in more than 100 countries being checked. While looking for a solution, I have wrote a litle Javascript code that prevents the user from marking more than 50 countries so he doesn't face the WSoD.

Setup:

• Drupal 8.8.1 (core and all its modules updated)
• I'm not using any contributed module related to taxonomy or views
• PHP 7.1
• MySQL 5.5

Answer 1

UPDATE

As discussed here, this issue was caused by some missconfiguration at view level and normally should not occur. Read the full post to get the full picture on how you can step on this error.

ORIGINAL ANSWER

I'm adding my findings as answer since comments have limitations both on styles and length, but by all means THIS IS NOT an actual answer to the question.

I've tried to reproduce the describe behavior using an exisitng Drupal site that I'm working on:

1. I've a content A with a er field targeting vocabulary B
2. I've generated over 300 terms with hierarchy
3. Added 2 nodes tagged with 2 of the generated terms
4. Created a view as described in the question, but with a slight change: I've one display using "Has term" filter an another display "Has term (with depth)" filter; I explain below why I did this.

The reason using two displays with these two filters is because I saw that "Has taxonomy term" filter (plugin id taxonomy_index_tid) is implemented by \Drupal\taxonomy\Plugin\views\filter\TaxonomyIndexTid which extends from \Drupal\views\Plugin\views\filter\ManyToOne. Reviewing the code I saw that neither the ManyToOne base filter or the TaxonomyIndexTid implement JOIN queries. On the other hand \Drupal\taxonomy\Plugin\views\filter\TaxonomyIndexTidDepth extends from TaxonomyIndexTid and has this on it's ::query() method

public function query() {
  ...
  // Now build the subqueries.
  $subquery = Database::getConnection()->select('taxonomy_index', 'tn');
  $subquery->addField('tn', 'nid');
  $where = (new Condition('OR'))->condition('tn.tid', $this->value, $operator);
  $last = "tn";

  if ($this->options['depth'] > 0) {
    $subquery->leftJoin('taxonomy_term__parent', 'th', "th.entity_id = tn.tid");
    $last = "th";
    foreach (range(1, abs($this->options['depth'])) as $count) {
      $subquery->leftJoin('taxonomy_term__parent', "th$count", "$last.parent_target_id = th$count.entity_id");
      $where->condition("th$count.entity_id", $this->value, $operator);
      $last = "th$count";
    }
  }
  elseif ($this->options['depth'] < 0) {
    foreach (range(1, abs($this->options['depth'])) as $count) {
      $field = $count == 1 ? 'tid' : 'entity_id';
      $subquery->leftJoin('taxonomy_term__parent', "th$count", "$last.$field = th$count.parent_target_id");
      $where->condition("th$count.entity_id", $this->value, $operator);
      $last = "th$count";
    }
  }

  $subquery->condition($where);
  $this->query->addWhere($this->options['group'], "$this->tableAlias.$this->realField", $subquery, 'IN');
}

As shown it does LEFT JOINS to work with the selected depth and hierachy. All though after selecting all 300 terms in both displays I was unable to reproduce the described error (see screenshots below).

Has taxonmy term

Has taxonomy term (with depth)

Also as commented @mpdonadio the filter is using WHERE IN to filter the terms and delegates to LEFT JOIN when dealing with hierarchy.

Maybe these can shed some light on your error.