Hi there,

I seem to be getting an incorrect query being built when I have node access enabled on the content types implementing node references. Here's the query:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_collection0.nid' in 'on clause': SELECT DISTINCT field_data_field_collection0.entity_type AS entity_type, field_data_field_collection0.entity_id AS entity_id, field_data_field_collection0.revision_id AS revision_id, field_data_field_collection0.bundle AS bundle FROM {field_data_field_collection} field_data_field_collection0 INNER JOIN {field_data_field_media_type} field_data_field_media_type1 ON field_data_field_media_type1.entity_type = field_data_field_collection0.entity_type AND field_data_field_media_type1.entity_id = field_data_field_collection0.entity_id INNER JOIN {node} node ON node.nid = field_data_field_collection0.entity_id LEFT OUTER JOIN {node_access} na ON na.nid = field_data_field_collection0.entity_id INNER JOIN {node_access} na_2 ON na_2.nid = field_data_field_collection0.nid WHERE (field_data_field_collection0.field_collection_nid = :db_condition_placeholder_0) AND (field_data_field_media_type1.field_media_type_tid = :db_condition_placeholder_1) AND (field_data_field_collection0.deleted = :db_condition_placeholder_2) AND (node.type = :db_condition_placeholder_3) AND (field_data_field_collection0.entity_type = :db_condition_placeholder_4) AND(((( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) ))AND (na.grant_view >= :db_condition_placeholder_9) AND (field_data_field_collection0.entity_type = :db_condition_placeholder_10) )OR (field_data_field_collection0.entity_type <> :db_condition_placeholder_11) )AND(( (na_2.gid = :db_condition_placeholder_12) AND (na_2.realm = :db_condition_placeholder_13) )OR( (na_2.gid = :db_condition_placeholder_14) AND (na_2.realm = :db_condition_placeholder_15) ))AND (na_2.grant_view >= :db_condition_placeholder_16) ; Array ( [:db_condition_placeholder_0] => 58 [:db_condition_placeholder_1] => 723 [:db_condition_placeholder_2] => 0 [:db_condition_placeholder_3] => media_file [:db_condition_placeholder_4] => node [:db_condition_placeholder_5] => 0 [:db_condition_placeholder_6] => all [:db_condition_placeholder_7] => 1 [:db_condition_placeholder_8] => gle [:db_condition_placeholder_9] => 1 [:db_condition_placeholder_10] => node [:db_condition_placeholder_11] => node [:db_condition_placeholder_12] => 0 [:db_condition_placeholder_13] => all [:db_condition_placeholder_14] => 1 [:db_condition_placeholder_15] => gle [:db_condition_placeholder_16] => 1 ) in field_sql_storage_field_storage_query() (line 577 of /var/www/clientname/www/modules/field/modules/field_sql_storage/field_sql_storage.module).

The problem seems to be that node_query_node_access_alter() (or rather _node_query_node_access_alter()) is assuming that the join for any node access query is going to connect to a field_data_field_name.nid, whereas in fact the correct column using node reference is field_data_field_name.field_name_nid - resulting in a PDOException.

I'm still working out a solution to this, or whether this should really be elevated to a bug with core, but if anyone has an immediate solution then I'm all ears.

Thanks,

Chris

Comments

xorne’s picture

xorne commented

Hi,

Same issue for me with the latest version of views, and it's a problem with this reference module, even with the dev version.
Any idea how to fix that?

Thanks

xorne’s picture

xorne commented

Ok it's fixed with the dev version but you need to clear all cache :P

DeFr’s picture

DeFr commented
Status: Active » Closed (duplicate)

Duplicate of #1275096: Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column ... , fixed in dev version.

Anonymous’s picture

Anonymous (not verified) commented
Project: References » Drupal core
Version: 7.x-2.0-beta3 » 7.x-dev
Component: Code: node_reference » field system
Status: Closed (duplicate) » Active

I thought that this issue had actually been resolved by the above patch, but upon further inspection the problem does seem to bounce back to core, rather than references itself.

My node reference is being used in an EFQ rather than a view, and #1275096: Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column ... seems to only apply to Views relationships. I think this may actually be related to #997394: field_sql_query hardcodes a 'node_access' tag on EFQs with a fieldCondition.

Trying to figure out a solution.

xjm’s picture

xjm
she/her
Primary language English
commented
Version: 7.x-dev » 8.x-dev
Priority: Major » Normal
Status: Active » Postponed (maintainer needs more info)

Postponing for details on what (if any) the core bug is.

Anonymous’s picture

Anonymous (not verified) commented

My understanding of the issue (which may be incorrect) is that when a reference is made to a field using node access, _node_query_node_access_alter() checks whether it's linking to a node or to a field value - in this case, a field value which is in essence a foreign key to the node table (field_collection_nid on field_data_field_collection), the node reference.

The problem is that instead it's linking to a nid field on the field_data_field_collection table:

INNER JOIN {node_access} na_2 ON na_2.nid = field_data_field_collection0.nid

From here, the node reference is already correctly joined to the parent table in order to gather the nodes to be returned as results, but there is an issue where the node_access table is joined on the value from the node reference field table - the join can't just be on the entity_id in that field as that's for the result nodes themselves (and that part works correctly through a LEFT OUTER JOIN), the joined reference node also needs to be checked, whether through an additional join to the node table which is then referenced by node_access, or through the ability to link to a foreign key field on a field table created by Field API.

If this is already possible and my understanding of how it's creating the base table relationship is wrong, I apologise - this may still be a problem with the References module itself and it may need to rewrite the query or its schema somehow to join in the correct way. I'll be happy to take my problem back there if that's what's needed.

Version: 8.0.x-dev » 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

bart vanhoutte’s picture

bart vanhoutte commented
Status: Postponed (maintainer needs more info) » Active

Just ran into the error using Drupal 8.1.3; Entity Reference Revisions 8.x-1.0-rc7 & Paragraphs 8.x-1.0-rc5. Using hook_node_access_records & hook_node_grants.

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_revision__field_paragraphs.nid' in 'where clause': SELECT base_table.vid AS vid, base_table.nid AS nid                                                  [error]
FROM
{node_revision} base_table
INNER JOIN {node_revision__field_paragraphs} node_revision__field_paragraphs ON node_revision__field_paragraphs.revision_id = base_table.vid
WHERE  (node_revision__field_paragraphs.field_paragraphs_target_id IS NOT NULL ) AND ( EXISTS  (SELECT na.nid AS nid
FROM
{node_access} na
WHERE (( (gid IN  (:db_condition_placeholder_0)) AND (realm = :db_condition_placeholder_1) )OR( (gid IN  (:db_condition_placeholder_2)) AND (realm = :db_condition_placeholder_3) ))AND (na.grant_view >= :db_condition_placeholder_4) AND (na.fallback =
:db_condition_placeholder_5) AND (node_revision__field_paragraphs.nid = na.nid) ))
GROUP BY base_table.vid, base_table.nid
ORDER BY base_table.vid ASC
LIMIT 50 OFFSET 0; Array
(
    [:db_condition_placeholder_0] => 0
    [:db_condition_placeholder_1] => all
    [:db_condition_placeholder_2] => 0
    [:db_condition_placeholder_3] => story
    [:db_condition_placeholder_4] => 1
    [:db_condition_placeholder_5] => 1
)
 in paragraphs_post_update_set_paragraphs_parent_fields() (line 60 of .../modules/contrib/paragraphs/paragraphs.post_update.php).

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.9 was released on September 7 and is the final bugfix release for the Drupal 8.1.x series. Drupal 8.1.x will not receive any further development aside from security fixes. Drupal 8.2.0-rc1 is now available and sites should prepare to upgrade to 8.2.0.

Bug reports should be targeted against the 8.2.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.6 was released on February 1, 2017 and is the final full bugfix release for the Drupal 8.2.x series. Drupal 8.2.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.3.0 on April 5, 2017. (Drupal 8.3.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.3.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

webcultist’s picture

webcultist commented

I seem to have a similar problem. I created a paragraphs field. The paragraph has 2 fields, a normal text field and an entity reference field for taxonomy terms. I got an error when creating the fields and get an error when saving the node with the filled paragraph:

Drupal\Core\Entity\EntityStorageException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'type' in 'field list': INSERT INTO {paragraphs_item} (revision_id, type, uuid, langcode) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3); Array ( [:db_insert_placeholder_0] => [:db_insert_placeholder_1] => ingredient [:db_insert_placeholder_2] => 4b8c3f90-79e1-4811-8af4-32a0ed6279d2 [:db_insert_placeholder_3] => de ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (line 777 of core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).
Drupal\Core\Database\Statement->execute(Array, Array) (Line: 624)
Drupal\Core\Database\Connection->query('INSERT INTO {paragraphs_item} (revision_id, type, uuid, langcode) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3)', Array, Array) (Line: 87)
Drupal\Core\Database\Driver\mysql\Connection->query('INSERT INTO {paragraphs_item} (revision_id, type, uuid, langcode) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3)', Array, Array) (Line: 32)
Drupal\Core\Database\Driver\mysql\Insert->execute() (Line: 850)
Drupal\Core\Entity\Sql\SqlContentEntityStorage->doSaveFieldItems(Object) (Line: 263)
Drupal\Core\Entity\ContentEntityStorageBase->doSave(NULL, Object) (Line: 392)
Drupal\Core\Entity\EntityStorageBase->save(Object) (Line: 768)
Drupal\Core\Entity\Sql\SqlContentEntityStorage->save(Object) (Line: 364)
Drupal\Core\Entity\Entity->save() (Line: 259)
Drupal\Core\Field\Plugin\Field\FieldType\EntityReferenceItem->preSave() (Line: 254)
Drupal\entity_reference_revisions\Plugin\Field\FieldType\EntityReferenceRevisionsItem->preSave() (Line: 244)
Drupal\Core\Field\FieldItemList->delegateMethod('preSave') (Line: 202)
Drupal\Core\Field\FieldItemList->preSave() (Line: 479)
Drupal\Core\Entity\ContentEntityStorageBase->invokeFieldMethod('preSave', Object) (Line: 429)
Drupal\Core\Entity\ContentEntityStorageBase->invokeHook('presave', Object) (Line: 435)
Drupal\Core\Entity\EntityStorageBase->doPreSave(Object) (Line: 298)
Drupal\Core\Entity\ContentEntityStorageBase->doPreSave(Object) (Line: 389)
Drupal\Core\Entity\EntityStorageBase->save(Object) (Line: 768)
Drupal\Core\Entity\Sql\SqlContentEntityStorage->save(Object) (Line: 364)
Drupal\Core\Entity\Entity->save() (Line: 286)
Drupal\node\NodeForm->save(Array, Object)
call_user_func_array(Array, Array) (Line: 111)
Drupal\Core\Form\FormSubmitter->executeSubmitHandlers(Array, Object) (Line: 51)
Drupal\Core\Form\FormSubmitter->doSubmitForm(Array, Object) (Line: 585)
Drupal\Core\Form\FormBuilder->processForm('node_recipe_edit_form', Array, Object) (Line: 314)
Drupal\Core\Form\FormBuilder->buildForm('node_recipe_edit_form', Object) (Line: 74)
Drupal\Core\Controller\FormController->getContentResult(Object, Object)
call_user_func_array(Array, Array) (Line: 123)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}() (Line: 574)
Drupal\Core\Render\Renderer->executeInRenderContext(Object, Object) (Line: 124)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->wrapControllerExecutionInRenderContext(Array, Array) (Line: 97)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}()
call_user_func_array(Object, Array) (Line: 144)
Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object, 1) (Line: 64)
Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1) (Line: 57)
Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1) (Line: 47)
Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1) (Line: 99)
Drupal\page_cache\StackMiddleware\PageCache->pass(Object, 1, 1) (Line: 78)
Drupal\page_cache\StackMiddleware\PageCache->handle(Object, 1, 1) (Line: 47)
Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1) (Line: 50)
Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1) (Line: 23)
Stack\StackedHttpKernel->handle(Object, 1, 1) (Line: 656)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.4 was released on January 3, 2018 and is the final full bugfix release for the Drupal 8.4.x series. Drupal 8.4.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.5.0 on March 7, 2018. (Drupal 8.5.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.5.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

szato’s picture

szato commented
Status: Active » Closed (duplicate)
Related issues: +#2823957: EntityQuery fails when combining revisions and node access.

I think we can close this issue, because of duplicate:
https://www.drupal.org/project/drupal/issues/2823957