Representative Node Views fails due to invalid SQL

Thanks @eosrei for the D8 patch!

Afaik #1792848: Groupwise Max relationship of term ->node is broken used to be a duplicate of that.

The query fails on PostgreSQL because ON statement table aliases aren't escaped so __INNER becomes "inner". This is going to be very difficult to fix because condition is a string so it would be very complex to create any sort of string replacement. I don't like the idea of doing that anywhere - in the driver or in the views module. Ideally condition should not be a string anymore, but I think it is way too late for a major API change from Drupal 7.

I looked at simply not using a case-sensitive alias in this case, but I was still getting an invalid FROM error in PostgreSQL for the following query.

I'm kind of at an impasse here. I don't think there's any good code that can fix the issue with join conditions without a significant API change, but there really is no other way to fix the issue.

SELECT COUNT(*) AS expression
	FROM 
	(SELECT 1 AS expression
	FROM 
	taxonomy_term_data taxonomy_term_data
	LEFT JOIN node node_taxonomy_term_data ON (SELECT nodeinner.nid AS nidinner
	FROM 
	node nodeinner
	LEFT JOIN taxonomy_index taxonomy_indexinner ON nodeinner.nid = taxonomy_indexinner.nid
	LEFT JOIN taxonomy_term_data taxonomy_term_data_nodeinner ON taxonomy_indexinner.tid = taxonomy_term_data_nodeinner.tid
	INNER JOIN taxonomy_term_field_data taxonomy_term_data_node__taxonomy_term_field_datainner ON taxonomy_term_data_nodeinner.tid = taxonomy_term_data_node__taxonomy_term_field_datainner.tid
	WHERE (( (taxonomy_term_data_node__taxonomy_term_field_datainner.tid = taxonomy_term_field_data.tid ) ))
	ORDER BY nodeinner.nid DESC
	LIMIT 1 OFFSET 0) = node_taxonomy_term_data.nid
	INNER JOIN taxonomy_term_field_data taxonomy_term_field_data ON taxonomy_term_data.tid = taxonomy_term_field_data.tid) subquery

ERROR:  invalid reference to FROM-clause entry for table "taxonomy_term_field_data"
LINE 12: ...rm_data_node__taxonomy_term_field_datainner.tid = taxonomy_t...
                                                              ^
HINT:  Perhaps you meant to reference the table alias "taxonomy_term_data_node__taxonomy_term_field_datainner".

I looked at simply not using a case-sensitive alias in this case, but I was still getting an invalid FROM error in PostgreSQL for the following query.

Right -- to get rid of that error, you have to do the JOIN on the taxonomy_term_field_data before referencing that table in the subquery. In other words, do the INNER JOIN on taxonomy_term_field_data before the LEFT JOIN on node. Unfortunately, I have no idea where to look to switch the order of those JOINs.

Needs re-roll.

Go bots...

I just gave this patch a try, but it unfortunately does not solve my issues, because I have a slightly different view setup. My term is a relationship as well, wich is then the relationship for the representative node.

This results in the following error message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_field_data.tid' in 'where clause'.

So the field name is generated as taxonomy_term_field_data.tid, but it has to be taxonomy_term_field_data_node_field_data.tid because of the second relationship.

Steps to reproduce

• Create a node view
• Add a "Taxonomy terms on node" relationship (term_node_tid)
• Add the "Representative node" relationship (tid_representative) using the other relationship
• Run the view preview and you will get the "Unknown column..." error

So I guess there are some escaping issues not using $this->tableAlias where necessary, as this variable already has the correct base table alias.

EDIT:

Forgot to mention that, if i replace the wrong field name, my query works completely fine - with and without your patch.

... and just another edit: Your patch definitely solves other SQL issues than mine ;) I had to extend the GroupwiseMax relationship plugin to add some more filters to the temporary view used for querying the represantitive node. Without your patch, my filters received values from the parent view, but with you patch everything works as expected for that case. So the only thing that should be fixed is the one mentioned above, that the field name is generated wrong.

It is great that people actually care about this!

Just a really quick review, sorry for no feedback in such a long time.

1. +++ b/core/modules/views/src/Plugin/views/relationship/GroupwiseMax.php
   @@ -272,10 +268,10 @@ class GroupwiseMax extends RelationshipPluginBase {
   -      // But if we're using a whole view, we don't know what we have!
   -      if ($options['subquery_view']) {
   

   Why do we ignore the option now?

2. +++ b/core/modules/views/src/Plugin/views/relationship/GroupwiseMax.php
   @@ -283,17 +279,31 @@ class GroupwiseMax extends RelationshipPluginBase {
   +    views_query_views_alter($subquery);
   ...
   +    // Replace subquery argument placeholders.
   +    $quoted = $subquery->getArguments();
   +    $connection = \Drupal\Core\Database\Database::getConnection();
   +    foreach ($quoted as $key => $val) {
   +      // Replace the **CORRELATED** placeholder with the outer field name.
   +      if ($val === '**CORRELATED**') {
   +       $quoted[$key] = $this->definition['outer field'];
   +      }
   +      // Add quotes for all other values
   +      else {
   +        $quoted[$key] = $connection->quote($val);
   +      }
   +    }
   ...
   +    $subquery_sql = strtr($subquery_sql, $quoted);
   

   I'm wondering whether we could replace **CORRELATED** in views_query_views_alter()

As of today, is there any work-around for the issue? I'm experiencing it in 8.4 for a simple case: a view of taxonomy terms, with one representative item per term. SQL fails.

The only filter is the vocabulary type. The only relationship is Representative node.

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '".deleted = :views_join_condition_0 WHERE ("taxonomy_term_field_data_node_field_' at line 3:...

Even though the issue is in Drupal core, last comment was 2 years ago. Is it because there is no obvious solution to the problem?

Here's a reroll of #9 for 8.4, so that it can be referenced with composer. It still contains the problems mentioned since #9, but it's better than nothing and fixes the bug we had.

Triggering test bots.

Go to Views find the view that causes this problem and copy it. When you are sure that you copied the view, delete the old view and place again the new copy wherever you want in your site.
Worked for me.

The problem mentioned in #18 is due to conditionNamespace assuming it's getting a single "foo.a = bar.b" condition. But if you're using a field of the node, this will actually be "node_field_dataINNER.nid = foo.entity_id AND node__field_dateINNER.deleted = '0'", which contains an AND that's not handled.

Furthermore, the prefixing assumes that the left part of the comparison is what needs to be namespaced. However, this is not always true, e.g. for LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON "node_field_dataINNER".nid = taxonomy_index.nid. In this case, the namespace is applied to the wrong variable.

This should fix the problem from #11/#18. Regardless, there is still the feedback from #13, tests to be written and the code style isn't quite up to standards either.

Whoopsie, here's the updated patch.

patch applies with following minor problems

Checking patch core/modules/views/src/Plugin/views/relationship/GroupwiseMax.php...
Hunk #1 succeeded at 193 (offset 1 line).
Hunk #2 succeeded at 243 (offset 1 line).
Hunk #3 succeeded at 269 (offset 1 line).
Hunk #4 succeeded at 280 (offset 1 line).
Hunk #5 succeeded at 339 (offset 1 line).
Applied patch core/modules/views/src/Plugin/views/relationship/GroupwiseMax.php cleanly.

worked for 8.3.7 as expected but does not work for 8.5.1

This plugin is pretty complicated, seems like a hard problem to solve.

1. +++ b/core/modules/views/src/Plugin/views/relationship/GroupwiseMax.php
   @@ -278,16 +279,31 @@ protected function leftQuery($options) {
   +    $connection = \Drupal\Core\Database\Database::getConnection();
   

   This would generally be \Drupal::database(); or at the least not fully qualified.

   But more importantly can we safely assume this is running on the same database as the default connection? Couldn't the quoting fail to match the database if its running on a different connection?

2. +++ b/core/modules/views/src/Plugin/views/relationship/GroupwiseMax.php
   @@ -278,16 +279,31 @@ protected function leftQuery($options) {
   +       $quoted[$key] = $this->definition['outer field'];
   

   Indention is off by one and fails standards check.

3. $subquery_sql = strtr($subquery_sql, $quoted);
   

   string replacing into a query sets off warnings for me but I don't have a better solution. The complexity of what's going on here doesn't offer an obvious solution and is why this issue and that odd todo about the order by exist. At the very least we have to be sure our quoting is 100% right per my earlier comment.

Patch #25 fixes the problem for me. Thank you very much!

Tested on Drupal 8.3.7, MariaDB 5.5.64.

Taxonomy term view with required "Representative node" relationship without "Representative view".

The error was:

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Learning levels[learning_levels]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_field_data.tid1' in 'where clause': SELECT taxonomy_term_field_data.weight AS taxonomy_term_field_data_weight, taxonomy_term_field_data.tid AS tid, node_field_data_taxonomy_term_field_data.nid AS node_field_data_taxonomy_term_field_data_nid FROM {taxonomy_term_field_data} taxonomy_term_field_data INNER JOIN {node_field_data} node_field_data_taxonomy_term_field_data ON (SELECT node_field_dataINNER.nid AS nidINNER FROM {node_field_data} node_field_dataINNER LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node_field_dataINNER ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = taxonomy_term_field_data.tid1 ) ORDER BY node_field_dataINNER.nid DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid LEFT JOIN {taxonomy_term__field_cc_level} taxonomy_term__field_cc_level ON taxonomy_term_field_data.tid = taxonomy_term__field_cc_level.entity_id AND (taxonomy_term__field_cc_level.deleted = :views_join_condition_0 AND taxonomy_term__field_cc_level.langcode = taxonomy_term_field_data.langcode) WHERE (taxonomy_term_field_data.vid IN (:db_condition_placeholder_2)) AND (taxonomy_term__field_cc_level.field_cc_level_value <> :db_condition_placeholder_3) ORDER BY taxonomy_term_field_data_weight ASC LIMIT 11 OFFSET 0; Array ( [:db_condition_placeholder_2] => learning_level [:db_condition_placeholder_3] => 1 [:views_join_condition_0] => 0 ) in Drupal\views\Plugin\views\query\Sql->execute() (line 1488 of /vagrant/web/core/modules/views/src/Plugin/views/query/Sql.php).

I had the same issue on Drupal 8.8.1. The patch #25 fixes my error. Good job, @ckaotik , thank you.

The error was:

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Platform[platform_special_topics_taxonomy_editors_pick]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_field_data.tid1' in 'where clause': SELECT taxonomy_term__field_teaser_media.field_teaser_media_target_id AS taxonomy_term__field_teaser_media_field_teaser_media_target_, taxonomy_term_field_data.name AS taxonomy_term_field_data_name, taxonomy_term_field_data.description__value AS taxonomy_term_field_data_description__value, node_field_data_taxonomy_term_field_data.created AS node_field_data_taxonomy_term_field_data_created, COUNT(node_field_data_taxonomy_term_field_data.nid) AS node_field_data_taxonomy_term_field_data_nid, MIN(taxonomy_term_field_data.tid) AS tid, MIN(node_field_data_taxonomy_term_field_data.nid) AS node_field_data_taxonomy_term_field_data_nid_1, MIN(field_special_topic_taxonomy_term_field_data.nid) AS field_special_topic_taxonomy_term_field_data_nid FROM {taxonomy_term_field_data} taxonomy_term_field_data INNER JOIN {node_field_data} node_field_data_taxonomy_term_field_data ON (SELECT node_field_dataINNER.nid AS nidINNER FROM {node_field_data} node_field_dataINNER LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node_field_dataINNER ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = taxonomy_term_field_data.tid1) ORDER BY node_field_dataINNER.created DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid INNER JOIN {node__field_special_topic} node__field_special_topic ON taxonomy_term_field_data.tid = node__field_special_topic.field_special_topic_target_id AND node__field_special_topic.deleted = :views_join_condition_0 INNER JOIN {node_field_data} field_special_topic_taxonomy_term_field_data ON node__field_special_topic.entity_id = field_special_topic_taxonomy_term_field_data.nid LEFT JOIN {taxonomy_term__field_teaser_media} taxonomy_term__field_teaser_media ON taxonomy_term_field_data.tid = taxonomy_term__field_teaser_media.entity_id AND taxonomy_term__field_teaser_media.deleted = :views_join_condition_1 WHERE (taxonomy_term_field_data.vid IN (:db_condition_placeholder_2)) AND (node_field_data_taxonomy_term_field_data.status = :db_condition_placeholder_3) AND (field_special_topic_taxonomy_term_field_data.status = :db_condition_placeholder_4) GROUP BY taxonomy_term__field_teaser_media_field_teaser_media_target_, taxonomy_term_field_data_name, taxonomy_term_field_data_description__value, node_field_data_taxonomy_term_field_data_created ORDER BY node_field_data_taxonomy_term_field_data_created DESC LIMIT 4 OFFSET 0; Array ( [:db_condition_placeholder_2] => special_topics [:db_condition_placeholder_3] => 1 [:db_condition_placeholder_4] => 1 [:views_join_condition_0] => 0 [:views_join_condition_1] => 0 ) in Drupal\views\Plugin\views\query\Sql->execute() (line 1543 of core/modules/views/src/Plugin/views/query/Sql.php).

We still need tests here. The changes are complex and this plugin is obviously under tested.

Given that we have clear steps to reproduce the fail adding a test that fails on HEAD and passes with the patch should be possible.

Testing the issue with drupal 8.8.5. SInce patch #25 is already in core. But still adding relationship for representative node, got this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INNER. DESC LIMIT 1 OFFSET 0) = node_field_data_users_field_data.nid WHERE (user' at line 4: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {users_field_data} users_field_data LEFT JOIN {node_field_data} node_field_data_users_field_data ON (SELECT node_field_dataINNER.nid AS nidINNER FROM {node_field_data} node_field_dataINNER INNER JOIN {users_field_data} users_field_data_node_field_dataINNER ON "node_field_dataINNER".uid = "users_field_data_node_field_dataINNER".uid LEFT JOIN {users_field_data} users_field_data_node_field_data_1INNER ON "node_field_dataINNER".uid = "users_field_data_node_field_data_1INNER".uid WHERE (("users_field_data_node_field_data_1INNER".uid = users_field_data.uid)) AND (("node_field_dataINNER".status = 1 OR ("node_field_dataINNER".uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1)) ORDER BY INNER. DESC LIMIT 1 OFFSET 0) = node_field_data_users_field_data.nid WHERE (users_field_data.default_langcode = :db_condition_placeholder_0) AND (users_field_data.uid != :db_condition_placeholder_1)) subquery; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 0 )

Here is Issue for sort criteria: right syntax to use near 'INNER. DESC LIMIT 1

While adding relationship for representative node it has field Representative sort criteria. But sort order clause is not providing table and field separately. Its just providing the table_name.field_name. So added a possible workaround here. since tables are aliased by appending subquery_namespace.

SInce patch #25 is already in core

Not any version of core I'm aware of.

The call to \Drupal\Core\Database\Database::getConnection(); is still pretty troubling to me.

Also alex's tests comment still hasn't been addressed.

neclimdul my bad, it was due to abnormal termination of composer.

Refering the issue in comment #35, I updated the patch file. The Patch file additionally include the changes.

no worries, just making sure we get the right patches :) moving target to d9.

Patch applied to drupal 9.1.x-dev for Representative Node Views.

Thank you, Narendra, for patch #40. It fixed this issue for me when updating a Drupal 8.7 site to 8.8.5.

It kills me that this issue has to be rerolled for every new version of core.

Thanks @narendra for your contribution. Patch #40 works for me. I tried to reproduce the issue as per the steps given in IS with latest code of 9.1.x branch. I am able to reproduce the issue.

Before patch applies I am getting below error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '***CURRENT_USER*** AND ***CURRENT_USER*** <> 0 AND ***VIEW_OWN_UNPUBLISHED_NODES' at line 11: SELECT COUNT(*) AS "expression" FROM (SELECT 1 AS "expression" FROM {users_field_data} "users_field_data" LEFT JOIN {node_field_data} "node_field_data_users_field_data" ON (SELECT "node_field_dataINNER"."nid" AS "nidINNER" FROM {node_field_data} "node_field_dataINNER" INNER JOIN {users_field_data} "users_field_data_node_field_dataINNER" ON "node_field_dataINNER".uid = "users_field_data_node_field_dataINNER".uid LEFT JOIN {users_field_data} "users_field_data_node_field_data_1INNER" ON "node_field_dataINNER".uid = "users_field_data_node_field_data_1INNER".uid WHERE (("users_field_data_node_field_data_1INNER".uid = users_field_data.uid)) AND (("node_field_dataINNER".status = "1 OR (node_field_dataINNER".uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0 AND ***VIEW_OWN_UNPUBLISHED_NODES*** = 1) OR ***BYPASS_NODE_ACCESS*** = 1)) ORDER BY "node_field_dataINNER"."changed" DESC LIMIT 1 OFFSET 0) = node_field_data_users_field_data.nid WHERE ("users_field_data"."default_langcode" = :db_condition_placeholder_0) AND ("users_field_data"."uid" != :db_condition_placeholder_1)) "subquery"; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 0 )

After applying patch it works well and error is resolved.

Good to move to RTBC.

I've installed the patch in comment #40 and am having the same problem that hctom had in comment #11. With the patch the representative node relationship works on views that use taxonomy term as the base table, however the relationship doesn't work for views that use node as the base table (where the taxonomy term itself is brought in via a relationship).

This is the error Views gives:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_field_data.tid' in 'where clause': SELECT COUNT(*) AS expression FROM (SELECT DISTINCT node__field_rs_release_date.field_rs_release_date_value AS node__field_rs_release_date_field_rs_release_date_value, node_field_data.nid AS nid, taxonomy_term_field_data_node__field_rs_product_link.tid AS taxonomy_term_field_data_node__field_rs_product_link_tid, node_field_data_taxonomy_term_field_data.nid AS node_field_data_taxonomy_term_field_data_nid, 1 AS expression FROM {node_field_data} node_field_data LEFT JOIN {node__field_rs_product_link} node__field_rs_product_link ON node_field_data.nid = node__field_rs_product_link.entity_id AND node__field_rs_product_link.deleted = :views_join_condition_0 LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_rs_product_link ON node__field_rs_product_link.field_rs_product_link_target_id = taxonomy_term_field_data_node__field_rs_product_link.tid LEFT JOIN {node_field_data} node_field_data_taxonomy_term_field_data ON (SELECT node_field_dataINNER.nid AS nidINNER FROM {node_field_data} node_field_dataINNER LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node_field_dataINNER ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = taxonomy_term_field_data.tid) ORDER BY nidINNER DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid LEFT JOIN {node__field_rs_release_date} node__field_rs_release_date ON node_field_data.nid = node__field_rs_release_date.entity_id AND node__field_rs_release_date.deleted = :views_join_condition_1 WHERE (node_field_data.status = :db_condition_placeholder_0) AND (node_field_data.type IN (:db_condition_placeholder_1))) subquery; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => release [:views_join_condition_0] => 0 [:views_join_condition_1] => 0 )

Unfortunately there appears to be an issue with the patch in #40 :(

After applying the patch custom sort fields would not apply and ordering was defaulting to node ids.

Looking at the changes these isset operations feel a bit off to me, should they be checking $temp_view->query->fields[$order_key] rather than just $order_key?

+      $sort_table_field = explode('.', $order_key);
+      $sort_table = isset($order_key['table']) ?
+        $temp_view->query->fields[$order_key]['table'] : current($sort_table_field);
+      $sort_field = isset($order_key['field']) ?
+        $temp_view->query->fields[$order_key]['field'] : end($sort_table_field);

conflicted with #3222769: [November 8, 2021] Replace all list (array destructuring) assignment to the array syntax

also fixing the database connection because its still not fixed and now its failing PHPCS.

Also, Timmy is right, that looks really weird. order_key has to be a string for the explode but then we try to check the values like an array? One of those cases where isset is hiding errors i think. I've reverted those changes and all the tests seem happy so lets see.

@neclimdul thanks so much for the updated patch, resolves the issue and applies cleanly for Drupal 9.3. RTBC + 1

Patch #50 also worked for me on 9.3.0. Thanks, @neclimdul !

Patch #50 doesn't seem to work for me on 9.3.6. I tried to have a list of all terms of a taxonomy (of name serie) order by terms last used in contents (so last used terms should be displayed first) .
Here is the configuration I tried on a taxonomy based view:
- add representative node in relationship, order by a date field (descendant) (field_date_publication)
- add new "Relationship : field_serie : Taxonomy term" with "representative node" relationship selected
- Order criteria : (Representative Node) Content : publication Date (desc), with "representative node" selected in relationship

=> views gives this error :
SQLSTATE[HY000]: General error: 4078 Illegal parameter data types row and int for operation '=': SELECT DISTINCT "taxonomy_term_field_data"."tid" AS "tid", "node_field_data_taxonomy_term_field_data"."nid" AS "node_field_data_taxonomy_term_field_data_nid", "taxonomy_term_field_data_node__field_serie"."tid" AS "taxonomy_term_field_data_node__field_serie_tid", DATE_FORMAT(node_field_data_taxonomy_term_field_data__node__field_date_publication.field_date_publication_value, '%Y%m%d') AS "node_field_data_taxonomy_term_field_data__node__field_date_p", 'liste_series:page_1' AS "view_name" FROM "drupal_taxonomy_term_field_data" "taxonomy_term_field_data" LEFT JOIN "drupal_node_field_data" "node_field_data_taxonomy_term_field_data" ON (SELECT "node_field_dataINNER"."nid" AS "nidINNER", ':default' AS "view_name" FROM "drupal_node_field_data" "node_field_dataINNER" LEFT JOIN "drupal_taxonomy_index" "taxonomy_indexINNER" ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN "drupal_taxonomy_term_field_data" "taxonomy_term_field_data_node_field_dataINNER" ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = taxonomy_term_field_data.tid) ORDER BY "node_field_dataINNER"."created" DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid LEFT JOIN "drupal_node__field_serie" "node_field_data_taxonomy_term_field_data__node__field_serie" ON node_field_data_taxonomy_term_field_data.nid = node_field_data_taxonomy_term_field_data__node__field_serie.entity_id AND node_field_data_taxonomy_term_field_data__node__field_serie.deleted = :views_join_condition_0 LEFT JOIN "drupal_taxonomy_term_field_data" "taxonomy_term_field_data_node__field_serie" ON node_field_data_taxonomy_term_field_data__node__field_serie.field_serie_target_id = taxonomy_term_field_data_node__field_serie.tid LEFT JOIN "drupal_node__field_date_publication" "node_field_data_taxonomy_term_field_data__node__field_date_publication" ON node_field_data_taxonomy_term_field_data.nid = node_field_data_taxonomy_term_field_data__node__field_date_publication.entity_id AND node_field_data_taxonomy_term_field_data__node__field_date_publication.deleted = :views_join_condition_1 AND (node_field_data_taxonomy_term_field_data__node__field_date_publication.langcode = node_field_data_taxonomy_term_field_data.langcode OR node_field_data_taxonomy_term_field_data__node__field_date_publication.bundle = :views_join_condition_3) WHERE ("taxonomy_term_field_data"."vid" IN (:db_condition_placeholder_4)) AND ("taxonomy_term_field_data"."status" = :db_condition_placeholder_5) ORDER BY "node_field_data_taxonomy_term_field_data__node__field_date_p" DESC; Array ( [:db_condition_placeholder_4] => serie [:db_condition_placeholder_5] => 1 [:views_join_condition_0] => 0 [:views_join_condition_1] => 0 [:views_join_condition_3] => article )

I have the same error when I remove the "Relationship : field_serie : Taxonomy term" relationship.

At the risk of being wrong: the issue for me came from the encasing done by "conditionNamespace" at line 324 of GroupwiseMax.php that is not compatible with MySQL/MariaDB. I've tried with this correction that worked for me:

  /**
   * Helper function to namespace query pieces.
   *
   * Turns 'foo.bar' into '"foo_NAMESPACE".bar'.
   * PostgreSQL doesn't support mixed-cased identifiers unless quoted, so we
   * need to quote each single part to prevent from query exceptions.
   */
  protected function conditionNamespace($string) {
    $db_driver = \Drupal::database()->driver();

    // Default encasing for PostgreSQL (and others ?) database driver.
    $encasing='"';
    // MySQL / MariaDB specific encasing.
    if($db_driver == 'mysql') {
      $encasing = '`';
    }

    $parts = explode(' = ', $string);
    foreach ($parts as &$part) {
      if (strpos($part, '.') !== FALSE) {
        $part = $encasing . str_replace('.', $this->subquery_namespace . $encasing . '.', $part);
      }
    }

    return implode(' = ', $parts);
  }

I posted a patch file below for those willing to try this.

I hope that a Drupal developer can do this in a better manner. The escaping of tables names should not be done by this plugin code.

Removed

Fixed failed commands on #55
Attached patch Against Drupal 9.5.x

After applying the patch 58 on drupal 9.5 same error is showing

reroll of 50 after #3268818: Fix class comment doc blocks in non tests 'Drupal.Commenting.DocComment.ShortSingleLine' which included a different modification to a CS doc fix also included in this issue. Just accepted the upstream change. Merge diff is just the top line of the GroupwiseMax.php class doc.

Still needs tests though... I'm sure there's some pretty straightforward method of creating the view from the IS and running the preview to see the failure. Don't have time at the moment though. Would really appreciate someone starting that test.

@neclimdul updated patch is working well for us:

ENV info:
Drupal: 9.4.8
PHP: 8.1.12

blind 10.1 reroll. conflict was just a block in the GroupWiseMax and and it was breaking with the str_contains changes so the patch doesn't have an interdiff because the patch uses preg instead of the str_contains logic.

Not entirely sure where the problem stems from, but the latest patch has these lines (line#303 in the file after patching):

+      // Replace the **CORRELATED** placeholder with the outer field name.
+      if ($val === '**CORRELATED**') {
+        $quoted[$key] = $this->definition['outer field'];
+      }

It appears that the outer field definition is just a plain table name with column, and not a properly aliased reference. This essentially results in an error such as the column is not found. Example (see the line I singled out):

SELECT "section_association"."id"                            AS "id",
       "users_field_data_section_association__user_id"."uid" AS "users_field_data_section_association__user_id_uid",
       "node_field_data_users_field_data"."nid"              AS "node_field_data_users_field_data_nid"
FROM section_association "section_association"
LEFT JOIN section_association__user_id "section_association__user_id"
ON section_association.id = section_association__user_id.entity_id AND section_association__user_id.deleted = '0'
    INNER JOIN users_field_data "users_field_data_section_association__user_id" ON section_association__user_id.user_id_target_id = users_field_data_section_association__user_id.uid
    INNER JOIN node_field_data "node_field_data_users_field_data" ON (SELECT "node_field_data_reps"."nid" AS "nid_reps"
    FROM
    node_field_data "node_field_data_reps"
    LEFT JOIN users_field_data "users_field_data_node_field_data_reps" ON "node_field_data_reps".uid = "users_field_data_node_field_data_reps".uid

    WHERE ("users_field_data_node_field_data_reps".uid = users_field_data.uid)
    /* 
      Should be:
      WHERE ("users_field_data_node_field_data_reps".uid = "node_field_data_users_field_data".uid)
     */

    ORDER BY "node_field_data_reps"."changed" DESC
    LIMIT 1 OFFSET 0) = node_field_data_users_field_data.nid
WHERE ("section_association"."access_scheme" IN ('section'))
  AND ("users_field_data_section_association__user_id"."uid"
    > '1')

In the code, replacing $quoted[$key] = $this->definition['outer field']; with $quoted[$key] = $this->relationship . "." . $this->definition['relationship field']; solves the issue.

Frankly I don't know if that is the proper way of generating the correct aliased reference, and am not sure if there are any implications to this approach. But it solves my issue.

Adding a patch with the correction from the previous comment

The patch in #71 (as well as the diff from MR 6352) both result in a WSOD error for me (Drupal 10.2.6 PHP 8.2.18):

Drupal\Core\Database\IntegrityConstraintViolationException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'tid' in where clause is ambiguous: SELECT "node_field_data_taxonomy_term_field_data"."title" AS "node_field_data_taxonomy_term_field_data_title", "node_field_data_taxonomy_term_field_data"."created" AS "node_field_data_taxonomy_term_field_data_created", "node_field_data_taxonomy_term_field_data"."uid" AS "node_field_data_taxonomy_term_field_data_uid", "taxonomy_term_field_data"."weight" AS "taxonomy_term_field_data_weight", COUNT(field_discussion_taxonomy_term_field_data.nid) AS "field_discussion_taxonomy_term_field_data_nid", SUM(field_discussion_taxonomy_term_field_data__comment_entity_statistics.comment_count) AS "field_discussion_taxonomy_term_field_data__comment_entity_st", MIN(taxonomy_term_field_data.tid) AS "tid", MIN(field_discussion_taxonomy_term_field_data.nid) AS "field_discussion_taxonomy_term_field_data_nid_1", MIN(node_field_data_taxonomy_term_field_data.nid) AS "node_field_data_taxonomy_term_field_data_nid" FROM "taxonomy_term_field_data" "taxonomy_term_field_data" LEFT JOIN "node__field_discussion" "node__field_discussion" ON taxonomy_term_field_data.tid = node__field_discussion.field_discussion_target_id AND node__field_discussion.deleted = :views_join_condition_0 LEFT JOIN "node_field_data" "field_discussion_taxonomy_term_field_data" ON node__field_discussion.entity_id = field_discussion_taxonomy_term_field_data.nid LEFT JOIN "node_field_data" "node_field_data_taxonomy_term_field_data" ON (SELECT "node_field_dataINNER"."nid" AS "nidINNER" FROM "node_field_data" "node_field_dataINNER" LEFT JOIN "taxonomy_index" "taxonomy_indexINNER" ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN "taxonomy_term_field_data" "taxonomy_term_field_data_node_field_dataINNER" ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = "".tid) ORDER BY "node_field_dataINNER"."created" DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid INNER JOIN (SELECT "tn"."nid" AS "nid" FROM "taxonomy_index" "tn" WHERE "tn"."tid" = :db_condition_placeholder_6 UNION SELECT "tn"."nid" AS "nid" FROM "taxonomy_index" "tn" INNER JOIN "taxonomy_term__parent" "th" ON "tn"."tid" = "th"."entity_id" INNER JOIN "taxonomy_term__parent" "th1" ON "th"."parent_target_id" = "th1"."entity_id" WHERE "th1"."entity_id" = :db_condition_placeholder_7) "taxonomy_index_depth" ON field_discussion_taxonomy_term_field_data.nid = taxonomy_index_depth.nid LEFT JOIN "taxonomy_term__field_path_prefix" "taxonomy_term__field_path_prefix" ON taxonomy_term_field_data.tid = taxonomy_term__field_path_prefix.entity_id AND taxonomy_term__field_path_prefix.deleted = :views_join_condition_1 AND (taxonomy_term__field_path_prefix.langcode = taxonomy_term_field_data.langcode OR taxonomy_term__field_path_prefix.bundle = :views_join_condition_3) LEFT JOIN "comment_entity_statistics" "field_discussion_taxonomy_term_field_data__comment_entity_statistics" ON field_discussion_taxonomy_term_field_data.nid = field_discussion_taxonomy_term_field_data__comment_entity_statistics.entity_id AND field_discussion_taxonomy_term_field_data__comment_entity_statistics.entity_type = :views_join_condition_4 WHERE ((field_discussion_taxonomy_term_field_data.status = 1 OR (field_discussion_taxonomy_term_field_data.uid = 20 AND 20 <> 0 AND 1 = 1) OR 1 = 1 OR 1 = 1)) AND ("field_discussion_taxonomy_term_field_data"."type" IN (:db_condition_placeholder_5)) GROUP BY "node_field_data_taxonomy_term_field_data_title", "node_field_data_taxonomy_term_field_data_created", "node_field_data_taxonomy_term_field_data_uid", "taxonomy_term_field_data_weight" ORDER BY "taxonomy_term_field_data_weight" ASC; Array ( [:db_condition_placeholder_5] => post [:views_join_condition_0] => 0 [:db_condition_placeholder_6] => 1083 [:db_condition_placeholder_7] => 1083 [:views_join_condition_1] => 0 [:views_join_condition_3] => brand [:views_join_condition_4] => node ) in Drupal\mysql\Driver\Database\mysql\ExceptionHandler->handleExecutionException() (line 45 of core/modules/mysql/src/Driver/Database/mysql/ExceptionHandler.php).

The patch in #66 works for me (doesn't result in a WSOD anyway) but gives this warning:
Deprecated function: Creation of dynamic property Drupal\views\Plugin\views\relationship\GroupwiseMax::$table_aliases is deprecated in Drupal\views\Plugin\views\relationship\GroupwiseMax->leftQuery() (line 253 of core/modules/views/src/Plugin/views/relationship/GroupwiseMax.php).

This patch is just the one in #66 with this bit added:

/**
* The table aliases used by the subquery.
*/
// phpcs:ignore Drupal.NamingConventions.ValidVariableName.LowerCamelName
protected array $table_aliases;

$tableAliases (as in the patch from #72) also works and meets the camelCase requirement, but for simplicity's sake I wanted to stay as close as possible to the patch that seems to work for us.

Doing a reroll I tried to understand the subquery changes Charly was trying to do. They weren't working for me and the previous code is pretty obtuses and admits its fixing confusing edge cases so I made a branch without it since I believe it was working for us as is. Split his changes into a separate branch if anyone wants to view and compare.

Sorry, about the branch noise. I forgot to request access and before you do the branch names get mangled and I didn't notice until after I pushed them.

Moving to NW for the test coverage.

Also tagging for issue summary update to use the standard template please

Thanks.

Rerolling #72 for Drupal Core 10.5.6.

Working in production.

Added the standard template and moved text around. I did not update the text itself, so leaving the 'Needs issue summary update' tag.

cleaned up rebase. Don't see tests or IS updates so leaving NW for now.