Views query alter for fields stored in dedicated tables is not working properly

Problem/Motivation

In order to obtain a (possible) workspace-specific revision instead of the default one in Views results, the Workspaces module is using an undocumented (and quite obscure) feature from Views' standard join plugin, which allows you to use a "formula" (SQL expression) for left_field only if left_table is NULL.

However, the joins for fields stored in dedicated tables have additional conditions, for example on the langcode or deleted columns, and those conditions need the left_table configuration value to be populated in order to be built correctly.

Proposed resolution

Add a new left_formula configuration option for the standard join plugin and use it in workspaces' views query alter, which would allow us to keep the existing value for left_table unaltered.

Remaining tasks

Review.

User interface changes

Nope.

API changes

API addition: a new left_formula configuration option for the the Standard views join plugin.

Data model changes

Nope.

Original issue summary by @maseyuk

Appologies if this isn't assigned to the right component as im not sure who it lives with

I've just enabled the workspaces module on a website i've upgraded from 8.5.7 to 8.6.0 and a mysql error is produced when I have a view outputting a paragraph field.

So my setup is that I have a paragraph field called "Page sections" and a view that simply outputs this field (Set to display as "rendered entity") in a block. Now if I swap to "Stage" workspace a mysql error is shown on any pages where that block is set to show.

Interestingly the error always refers to the "page" content type but even when viewing other content types with/without the "page sections" field it still produces the same error for the "page" content type rather than the content type being viewing (Guessing on each page view it looks at all content types and is hitting this error first)

This is the error:

Exception in Page sections[page_sections]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR node__field_page_sections.bundle = 'page')

The full query which I believe is coming from Views just trying to output this fields content:

SELECT node__field_page_sections.delta AS node__field_page_sections_delta, node__field_page_sections.langcode AS node__field_page_sections_langcode, node__field_page_sections.bundle AS node__field_page_sections_bundle, node__field_page_sections.field_page_sections_target_id AS node__field_page_sections_field_page_sections_target_id, node__field_page_sections.field_page_sections_target_revision_id AS node__field_page_sections_field_page_sections_target_revisio, node_field_revision.created AS node_field_data_created, node_field_data.nid AS nid, paragraphs_item_field_data_node__field_page_sections.id AS paragraphs_item_field_data_node__field_page_sections_id
FROM
{node_field_data} node_field_data
LEFT JOIN {workspace_association} workspace_association ON node_field_data.nid = workspace_association.target_entity_id AND (workspace_association.target_entity_type_id = :views_join_condition_0 AND workspace_association.workspace = :views_join_condition_1)
LEFT JOIN {node_revision__field_page_sections} node__field_page_sections ON COALESCE(workspace_association.target_entity_revision_id, node_field_data.vid) = node__field_page_sections.revision_id AND node__field_page_sections.deleted = :views_join_condition_2 AND (node__field_page_sections.langcode = .langcode OR node__field_page_sections.bundle = :views_join_condition_4)
INNER JOIN {paragraphs_item_field_data} paragraphs_item_field_data_node__field_page_sections ON node__field_page_sections.field_page_sections_target_revision_id = paragraphs_item_field_data_node__field_page_sections.revision_id
LEFT JOIN {node_field_revision} node_field_revision ON COALESCE(workspace_association.target_entity_revision_id, node_field_data.vid) = node_field_revision.vid
WHERE ((node_field_data.nid = :node_field_data_nid)) AND (node_field_revision.status = :db_condition_placeholder_5)
ORDER BY node_field_data_created DESC;

So the error seems to be around the "AND (node__field_page_sections.langcode = .langcode" with the langcode not having a table?

The steps to reproduce should be:
1) Create a paragraph item
2) Add a paragraph field to a content type
3) Create a block in Views that outputs your paragraph field
4) Set that block to appear on all pages
5) Go to any node regardless of type and set the workspace to "stage"
6) The error will be shown