EntityQuery fails when combining revisions and node access.

Here's a fail patch proving the problem exists. It only happens, for me, when you combine an allRevisions() query with a field that has a dedicated table. It tries to join the field table on the 'nid' column, which field tables don't have.

Also...8.6.x please!

Same error with paragraphs (entity_reference_revisions), on revision delete:
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_revision__[ENTITY_REFERENCE_REVISION_FIELD].nid' in 'where clause': SELECT base_table.vid AS vid, base_table.nid AS nid FROM {node_revision} base_table INNER JOIN {node_revision__[ENTITY_REFERENCE_REVISION_FIELD]} node_revision__[ENTITY_REFERENCE_REVISION_FIELD] ON node_revision__[ENTITY_REFERENCE_REVISION_FIELD].revision_id = base_table.vid WHERE (node_revision__[ENTITY_REFERENCE_REVISION_FIELD].[ENTITY_REFERENCE_REVISION_FIELD]_target_revision_id = :db_condition_placeholder_0) AND (EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (((gid IN (:db_condition_placeholder_1)) AND (realm = :db_condition_placeholder_2)) OR ((gid IN (:db_condition_placeholder_3)) AND (realm = :db_condition_placeholder_4)) OR ((gid IN (:db_condition_placeholder_5)) AND (realm = :db_condition_placeholder_6)) OR ((gid IN (:db_condition_placeholder_7)) AND (realm = :db_condition_placeholder_8))) AND (na.grant_view >= :db_condition_placeholder_9) AND (na.fallback = :db_condition_placeholder_10) AND ((node_revision__[ENTITY_REFERENCE_REVISION_FIELD].nid = na.nid)))); Array ( [:db_condition_placeholder_0] => 334030 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => 286 [:db_condition_placeholder_4] => view_unpublished_author [:db_condition_placeholder_5] => 1 [:db_condition_placeholder_6] => view_unpublished_published_content [:db_condition_placeholder_7] => 1 [:db_condition_placeholder_8] => view_unpublished_content [:db_condition_placeholder_9] => 1 [:db_condition_placeholder_10] => 1 ) in Drupal\entity_reference_revisions\Plugin\Field\FieldType\EntityReferenceRevisionsItem->deleteRevision() (line 341 of /home/devel/www/***/web/modules/contrib/entity_reference_revisions/src/Plugin/Field/FieldType/EntityReferenceRevisionsItem.php).

The idea was good, but does it really hurt if we hardcode the 4 table names we really want to see there? We already hardcode two of them below. we could call getBaseTable(), getDataTable(), ... I guess...

The test is a start, but IMHO it should be in a kernel test as we don't need the UI for this. We also don't actually need a cardinality unlimited field, all configurable fields have dedicated tables at the moment. And we should have actual data, so we can assert that the query doesn't just run, it also returns the expected results. Possibly combined with an actual limit applied through node grants?

+++ b/core/modules/node/node.module
@@ -1077,9 +1077,7 @@ function node_query_node_access_alter(AlterableInterface $query) {
-    /** @var \Drupal\Core\Entity\Sql\DefaultTableMapping $table_mapping */
-    $table_mapping = \Drupal::entityTypeManager()->getStorage('node')->getTableMapping();
-    $node_base_tables = $table_mapping->getTableNames();
+    $node_base_tables = ['node', 'node_field_data', 'node_revision', 'node_revision_data'];

As you pointed out, we can make this a bit more flexible by loading the node entity type definition and calling getBaseTable(), getDataTable(), etc., rather than hard-coding. It's probably a better idea to use the abstraction layer.

...all configurable fields have dedicated tables at the moment.

"At the moment"? What if this changes later? Would it be more future-proof to try with fields of varying cardinality?

The test is a start, but IMHO it should be in a kernel test as we don't need the UI for this.

I put it into an existing test so that we could take advantage of existing node access-related test code (the node_access_test module in particular). It'd take a lot more effort and set-up code to stand up a kernel test that will reproduce this bug, and browser tests aren't that slow anymore since they're not installing a gigantic profile with a kazillion modules.

And we should have actual data, so we can assert that the query doesn't just run, it also returns the expected results. Possibly combined with an actual limit applied through node grants?

Yet another reason to keep the test in the functional space, to take advantage of BrowserTestBase's "realness".

Are you sure any of that code is actually needed, it might be enough to just have a node query access module enabled that adds grants?

Functional tests are still easily 5x slower than kernel tests and it is worth to keep tests kernel tests if they do not need a UI.

> Yet another reason to keep the test in the functional space, to take advantage of BrowserTestBase's "realness".

Don't agree with that. Nothing prevents us from adding data in a kernel test. The primary entity query test coverage (\Drupal\KernelTests\Core\Entity\EntityQuery*Test) are kernel tests.

The Needs Review Queue Bot tested this issue. It either no longer applies to Drupal core, or fails the Drupal core commit checks. Therefore, this issue status is now "Needs work".

Apart from a re-roll or rebase, this issue may need more work to address feedback in the issue or MR comments. To progress an issue, incorporate this feedback as part of the process of updating the issue. This helps other contributors to know what is outstanding.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

The "Node Access module" is not fully compatible with Drupal 10, let alone Drupal 11.x. I'm unable to reproduce this issue for now.

Not much action here in a while, is this still reproducible?

Wanted to bump 1 more time if anyone still experiencing this.

I encountered this issue on Drupal 11.3.2 when combining custom hook_node_grants() implementations with entity reference revision fields (paragraphs).

Steps to reproduce:
1. Implement custom hook_node_grants() and hook_node_access_records()
2. Create content with entity reference revision fields (e.g., paragraph fields)
3. Visit a page containing this content as an anonymous user

Error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_revision__field_FIELDNAME.nid' in 'where clause'

Root cause:
In NodeGrantDatabaseStorage::alterQuery() (line 193), the code hardcodes $field = 'nid' for all tables. However, field revision tables (node_revision__field_*) and field data tables (node__field_*) use entity_id instead of nid
as their primary key column.

Proposed solution:
The attached patch detects the table type and uses the appropriate column name:
- entity_id for field tables (node_revision__field_* and node__field_*)
- nid for base node tables

This issue affects sites using custom node access grants with entity reference revision fields in Drupal 11.3+.

#13 looks much better to me at least but I think calling the four methods from the table mapping instead of hardwiring them would be best.