Node views integration that joins revisions to the default entity fails to consider langcode, resulting in duplicate rows

It'd be great to get a test to recreate this bug.

I am seeing duplicates too. See screenshot here

To recreate this:
I installed a plain Lightning site (lightning-8.x-3.103) Use the 3 core moderation modules that are enabled by default now in Lightning: Content Moderation, Workflows, Lightning Workflow, and Scheduler. Make sure Page content type is enabled as a workflow in /admin/config/workflow/workflows.
enable pathauto and add the pattern [node:title] for Page content type.
Create a page, then published it.
I enabled all 4 core translation modules:
Configuration Translation
Content Translation
Interface Translation
Language

I added the Spanish language.
I went to the first node and added a translated version of it.
And saved it as a draft. I went to "Moderated Content" and I
see 1 item. The translated version. I go to content overview
and I see 1 item, the original english version. Now I publish
the translated version. There are no items in "Moderated
Content" and 2 items in Content Overview. The original english
version and the traslated version. From the Content Overview
page I can access both versions and edit them. The revisions
are correct for both versions. I have more revisions for the
original english version. This all looks correct and better
then the 5 duplicates I get using the Workbench module.

The problem:
If I create a new draft of the ordinal english node or the translated version then go to:
"Moderated Content" /admin/content/moderated
I now see 2 items of that node they are duplicates. See screenshot here

Another issue I found. If I go to the translated version my url has the language code in the url path like this which is correct:
http://mylocal/es/test-1f-spanish
But when click to go to the Content overview the url path still has the language code in the url path like this:
http://mylocal/es/admin/content
And any other page I click on still has the language code in the url:
http://mylocal/es/admin/content/media
This is not correct, is it a setting in Language detection. /admin/config/regional/language/detection

Looked into this and tracked down the problem.

Updating the issue summary. I don't mind this staying in the content_moderation component since it's the only place where this actually surfaces (people might be more likely to find it here?), but I believe the fix and test should be in node. Happy to change the component if anyone feels differently.

Uploading the fix to see if anything else fails.

Adding tests for this exposed another place where this problem existed. Attaching tests and a fix for that too.

I also had this issue and applying patch #6 solved it (tested only on the "Moderated content" tab).

I was expecting these relationships to be added by \Drupal\views\EntityViewsData, but apparently that's not the case. I think we should open a followup to do that because it's needed by all content entity types. And NodeViewsData needs quite some cleanup as well.

In the meantime, I manually tested the patch and it works great, so it's a good step forward :)

Thanks for the review!

I think we might be doing that in #2652652: Provide a relationship from the revision table to the main table.

Nice! Here's another quick cleanup for something that I found while looking at NodeViewsData: #3002158: Clean up dead code in NodeViewsData

+++ b/core/modules/node/src/NodeViewsData.php
@@ -216,6 +216,10 @@ public function getViewsData() {
+    $data['node_field_revision']['nid']['relationship']['extra'][] = [
+      'field' => 'langcode',
+      'left_field' => 'langcode',
+    ];

@@ -228,6 +232,12 @@ public function getViewsData() {
+        'extra' => [
+          [
+            'field' => 'langcode',
+            'left_field' => 'langcode',
+          ]
+        ]

We are changing the views data which will affect the query. For view data changes to be applied we need to clear the cache. We just need to add an empty post update hook.

I do not believe the patch in #6 solves the problem, at least for the use case I tested it with

1. Fresh install of Drupal 8.6.1 using the Demo_umani profile
2. Enabled the 4 core multilingual modules
3. Added Spanish as the second language
4. Enabled translation of content and the basic page, leaving the default fields selected
5. Translated the About Umani basic page (node 16)
6. Added Spanish, changed the title to About Umani-Spanish, set to draft. Moderated Content shows the draft for the Spanish translation
7. Edited English, changed the title to About Umani-English, set to draft. Moderated Content now only shows the draft for English.
8. Published the Spanish draft.
9. Edited Spanish, changed the title to About Umani-Spanish V2, set to draft. Moderated Content shows two listings for the Spanish V2
10. Applied patch, cleared caches and Moderated Content shows only the one listing for Spanish V2. About Umani-English is still in Draft but not displayed on the Moderated Content view

Even setting the Moderated Content filter to English displays "No moderated content available. Only pending versions of content, such as drafts, are listed here."

I did notice that if I edit the English draft and save it without changing the state, now only the English draft shows in Moderated Content. It would appear that view is only displaying the most recent revision (and the one with status = 0, saving the English draft switched the Spanish to status = 1 and the English to status = 0 from the previous revision ID which had them the other way).

I would expect to see both the English AND the Spanish drafts in the Moderated Content page since both are in the draft state.

Thanks for the report. I've responded to the issue you logged in #3007456: Content Moderation State Field Revision with 2 Languages Set to Published.

Adding the cache clear as per @jibran's request.

Nice, back to RTBC then.

The latest patch includes the addition of views_update_8701() in node.install. This breaks the hook magic naming convention, and causes failures when running drush updb. The attached patch just renames the function to node_update_8701(), but is otherwise identical.

That's a very good catch, @ergonlogic!

Patch solves the duplicated content issue for us as well. Thanks! :)

+++ b/core/modules/node/node.install
@@ -280,3 +280,10 @@ function node_update_8700() {
+function node_update_8701() {

This should be a post-update rather than hook_update_N(). Otherwise looks good though.

Addressing #22. Not sure why post update is preferable here, but happy to defer.

@Sam152, some reasons for that are listed here: https://www.drupal.org/node/2960601

Good to know! Thanks.

Committed 75a159c and pushed to 8.7.x. Thanks!

This introduced test failures:
https://www.drupal.org/pift-ci-job/1125517
https://www.drupal.org/pift-ci-job/1125518

Reverted. It looks like the fails are on other DBs

Ah, I think we can into a similar issue in the moderation_state sorting issue. We might need to add an additional sort criteria to the test views, to ensure the order in the test is always consistent.

Ah! the old sorting issue. MySQL let's get away with a lot.

After applying the patch I was able to see the correct edited nodes in the moderated content view but I'm getting a new issue:

The state '' does not exist in workflow.

I have two languages Spanish and English, I edited the node in "en" (I also changed the state to Draft), after that I tried to edit the node in "es", I could not edit it given I got that error.

I didn't find the revision ID of the node in the 'content_moderation_state_field_data ' table, it didn't get this value $entity->moderation_state->value in the EntityTypeInfo.php file.

Here is where the error came up:

EntityTypeInfo.php file
Line 349

        // If the publishing status exists in the meta region, replace it with
        // the current state instead.
        if (isset($form['meta']['published'])) {
          $form['meta']['published']['#markup'] = $this->moderationInfo->getWorkflowForEntity($entity)->getTypePlugin()->getState($entity->moderation_state->value)->label();
        }

Is there someone who has the same issue?

@luisnicg, I don't think that is related to the join in the node module. It would be good if you could file a new issue with detailed steps to reproduce the issue, including the version of core you are running.

Sounds like #3000573: After enabling Workflows and Content Moderation there is a fatal error when trying to edit content translation, which has been fixed in 8.6.x-dev.

Adding some additional sorts which hopefully address the random fails in the other DB engines. Queuing tests on both branches and the other DB engines.

The one failed run wasn't related to this test:

Error retrieving a new session from the selenium server

Committed ab43176 and pushed to 8.7.x. Thanks!
Committed bc60cf1 and pushed to 8.6.x. Thanks!

Backported to 8.6.x because this does not have a complicated update path and it is a bug.

diff --git a/core/modules/node/src/NodeViewsData.php b/core/modules/node/src/NodeViewsData.php
index 5ce9d1b665..ef7d5d3efa 100644
--- a/core/modules/node/src/NodeViewsData.php
+++ b/core/modules/node/src/NodeViewsData.php
@@ -236,8 +236,8 @@ public function getViewsData() {
           [
             'field' => 'langcode',
             'left_field' => 'langcode',
-          ]
-        ]
+          ],
+        ],
       ],
     ] + $data['node_field_revision']['vid'];
 
diff --git a/core/modules/node/tests/src/Kernel/Views/RevisionRelationshipsTest.php b/core/modules/node/tests/src/Kernel/Views/RevisionRelationshipsTest.php
index 24f1119a4a..b21610c5fd 100644
--- a/core/modules/node/tests/src/Kernel/Views/RevisionRelationshipsTest.php
+++ b/core/modules/node/tests/src/Kernel/Views/RevisionRelationshipsTest.php
@@ -22,7 +22,7 @@ class RevisionRelationshipsTest extends ViewsKernelTestBase {
    * @var array
    */
   public static $modules = [
-    'node' ,
+    'node',
     'node_test_views',
     'language',
     'content_translation',

Fixed coding standards on commit.

Drupal 8.8.1

I encountered the same issue after I added the language translation to the users (admin/config/regional/content-language).

After I removed the users translation the issue persisted until I manually deleted the translation record for the translated user from the database table `users_field_data` and cleared the cache.

Anyway, I'd like to use users translation and not have the duplicates on the admin/content page.