I have a view (in Drupal 8.9) that’s not really all that complicated, but it does include relationships, filters, and a contextual filter. Some of the conditions added by views to the LEFT JOIN clauses are wrong, and cause the view to return zero results.

Here’s what it is supposed to do:

• Filter: content type = "Supplier"
• Relationship: link content from field_supplier in "Container" content type
• Relationship: link content from field_customer in "Container" content type
• Contextual Filter: match logged-in user to field_users in "Customer" content type

Essentially, that should return all suppliers a customer (based on logged-in user) has purchased containers from.

Here is the generated query:

SELECT node_field_data.nid AS nid, node_field_data_node__field_supplier.nid AS node_field_data_node__field_supplier_nid, node_field_data_node__field_customer.nid AS node_field_data_node__field_customer_nid FROM {node_field_data} node_field_data  LEFT JOIN {node__field_supplier} node__field_supplier    ON node_field_data.nid = node__field_supplier.entity_id   AND node__field_supplier.deleted = '0'   AND (node__field_supplier.langcode = node_field_data.langcode        OR node__field_supplier.bundle = 'purchase_contract')  LEFT JOIN {node_field_data} node_field_data_node__field_supplier    ON node__field_supplier.field_supplier_target_id = node_field_data_node__field_supplier.nid  LEFT JOIN {node__field_customer} node_field_data_node__field_supplier__node__field_customer    ON node_field_data_node__field_supplier.nid = node_field_data_node__field_supplier__node__field_customer.entity_id   AND node_field_data_node__field_supplier__node__field_customer.deleted = '0'   AND (node_field_data_node__field_supplier__node__field_customer.langcode = node_field_data_node__field_supplier.langcode        OR node_field_data_node__field_supplier__node__field_customer.bundle = 'sales_contract')  LEFT JOIN {node_field_data} node_field_data_node__field_customer    ON node_field_data_node__field_supplier__node__field_customer.field_customer_target_id = node_field_data_node__field_customer.nid  LEFT JOIN {node__field_users} node_field_data_node__field_customer__node__field_users    ON node_field_data_node__field_customer.nid = node_field_data_node__field_customer__node__field_users.entity_id   AND node_field_data_node__field_customer__node__field_users.deleted = '0'   AND (node_field_data_node__field_customer__node__field_users.langcode = node_field_data_node__field_customer.langcode        OR node_field_data_node__field_customer__node__field_users.bundle = 'supplier')  WHERE ((node_field_data_node__field_customer__node__field_users.field_users_target_id = '95')) AND (node_field_data.type IN ('supplier')) ORDER BY node_field_data.title ASC 

There are three parts of that query that are similar, so I’ll just point out one of them. In the last LEFT JOIN, just above the WHERE clause, is this:

AND (node_field_data_node__field_customer__node__field_users.langcode = node_field_data_node__field_customer.langcode        OR node_field_data_node__field_customer__node__field_users.bundle = 'supplier') 

First, I don’t understand why this is using an "OR". Either user’s langcode = customer field’s langcode –OR– bundle of customer field is "supplier"? That doesn’t make sense. That field_users exists on two different content types – Supplier and Customer. In this view, the contextual filter is supposed to be on users attached to Customer nodes, so that is just plain wrong.

The other two parts that are similar are where it checks node__field_supplier.bundle = 'purchase_contract' and node_field_data_node__field_supplier__node__field_customer.bundle = 'sales_contract'

Those are all things views adds to the query sort of "behind the scenes". I don’t see how I can configure my view any differently to affect those parts of the SQL.