Module throwing wrong query with 'node_access' but without 'nid' (Column not found (nid) in on clause...)

I'm seeing this error after recently migrating my site to Dreamhost. I'm not using i18n, so this may be unrelated or our problem may not be related to the i18n module.

OK, so for me this was somehow being caused by the "Bypass content access control" permission not being given to the user trying to add content. Strange, right?

After reading #2 (not using i18n), please confirm it is i18n module causing the trouble (and if so which of the submodules) by disabling it then enabling the submodules one at a time.

I am receiving the same error on an entity_reference module field and I am not using the i18n module:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_body0.nid' in 'where clause': SELECT field_data_body0.entity_type AS entity_type, field_data_body0.entity_id AS entity_id, field_data_body0.revision_id AS revision_id, field_data_body0.bundle AS bundle FROM {field_data_body} field_data_body0 WHERE (field_data_body0.deleted = :db_condition_placeholder_0) AND (field_data_body0.entity_type = :db_condition_placeholder_1) AND (field_data_body0.bundle IN (:db_condition_placeholder_2)) AND ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (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) )OR( (na.gid = :db_condition_placeholder_9) AND (na.realm = :db_condition_placeholder_10) )OR( (na.gid = :db_condition_placeholder_11) AND (na.realm = :db_condition_placeholder_12) )OR( (na.gid = :db_condition_placeholder_13) AND (na.realm = :db_condition_placeholder_14) )OR( (na.gid = :db_condition_placeholder_15) AND (na.realm = :db_condition_placeholder_16) )OR( (na.gid = :db_condition_placeholder_17) AND (na.realm = :db_condition_placeholder_18) ))AND (na.grant_view >= :db_condition_placeholder_19) AND (field_data_body0.entity_id = na.nid) )) AND(( (field_data_body0.entity_type = :db_condition_placeholder_20) )OR (field_data_body0.entity_type <> :db_condition_placeholder_21) )AND ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_22) AND (na.realm = :db_condition_placeholder_23) )OR( (na.gid = :db_condition_placeholder_24) AND (na.realm = :db_condition_placeholder_25) )OR( (na.gid = :db_condition_placeholder_26) AND (na.realm = :db_condition_placeholder_27) )OR( (na.gid = :db_condition_placeholder_28) AND (na.realm = :db_condition_placeholder_29) )OR( (na.gid = :db_condition_placeholder_30) AND (na.realm = :db_condition_placeholder_31) )OR( (na.gid = :db_condition_placeholder_32) AND (na.realm = :db_condition_placeholder_33) )OR( (na.gid = :db_condition_placeholder_34) AND (na.realm = :db_condition_placeholder_35) )OR( (na.gid = :db_condition_placeholder_36) AND (na.realm = :db_condition_placeholder_37) ))AND (na.grant_view >= :db_condition_placeholder_38) AND (field_data_body0.nid = na.nid) )) ORDER BY field_data_body0.body_value ASC; Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => node [:db_condition_placeholder_2] => branch [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => all [:db_condition_placeholder_5] => 15 [:db_condition_placeholder_6] => content_access_author [:db_condition_placeholder_7] => 2 [:db_condition_placeholder_8] => content_access_rid [:db_condition_placeholder_9] => 4 [:db_condition_placeholder_10] => content_access_rid [:db_condition_placeholder_11] => 5 [:db_condition_placeholder_12] => content_access_rid [:db_condition_placeholder_13] => 2 [:db_condition_placeholder_14] => taxonomy_access_role [:db_condition_placeholder_15] => 4 [:db_condition_placeholder_16] => taxonomy_access_role [:db_condition_placeholder_17] => 5 [:db_condition_placeholder_18] => taxonomy_access_role [:db_condition_placeholder_19] => 1 [:db_condition_placeholder_20] => node [:db_condition_placeholder_21] => node [:db_condition_placeholder_22] => 0 [:db_condition_placeholder_23] => all [:db_condition_placeholder_24] => 15 [:db_condition_placeholder_25] => content_access_author [:db_condition_placeholder_26] => 2 [:db_condition_placeholder_27] => content_access_rid [:db_condition_placeholder_28] => 4 [:db_condition_placeholder_29] => content_access_rid [:db_condition_placeholder_30] => 5 [:db_condition_placeholder_31] => content_access_rid [:db_condition_placeholder_32] => 2 [:db_condition_placeholder_33] => taxonomy_access_role [:db_condition_placeholder_34] => 4 [:db_condition_placeholder_35] => taxonomy_access_role [:db_condition_placeholder_36] => 5 [:db_condition_placeholder_37] => taxonomy_access_role [:db_condition_placeholder_38] => 1 ) in field_sql_storage_field_storage_query() (line 577 of /usr/www/users/tn1s/pandh/modules/field/modules/field_sql_storage/field_sql_storage.module).

@noahott,

Thanks, that's useful information.

So it seems to me that there's some module using node_access tag for some field related queries, but not passing any table with 'nid' field, then other modules implementing node_access (i18n_select, taxonomy_access.... ?)

I'd say the bug is in the original module throwing the query (?). However I'd like i18n_select to be improved so it just ignores these queries.

We need some more information here:
- Which is the module throwing the wrong query tagged with 'node_access' ?
- On which page you get the error (which may help answering the first question).
- Which other access control modules are having trouble with this? (For #5 if not i18n, which one?)

As far as I'm aware, I'm not using any special modules with node_access apart from core. For us, the page appeared when a privileged user tried to edit or create a node that they did have access to. They had all node permissions, but lacked the bypass access control permission. Granting that permission allowed them to create and edit nodes as normal.

@davidneedham,
Not talking about modules doing any access control but about a module that does some node / field loading (it seems field so it may be a field module) and using the 'node access' tag for the wrong queries. So it could be any module providing a field type.

@Jose,

Got it. If it's helpful, here's some more information. I have one content type referencing another via the entity reference module. Both of them throw the error. There are other fields present on both of the content types, but they're mostly fields that are already present on other content types that do not have errors. The error seems to reference the body field though...

The error I'm seeing:
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_body0.status' in 'where clause': SELECT field_data_body0.entity_type AS entity_type, field_data_body0.entity_id AS entity_id, field_data_body0.revision_id AS revision_id, field_data_body0.bundle AS bundle FROM {field_data_body} field_data_body0 WHERE (field_data_body0.deleted = :db_condition_placeholder_0) AND (field_data_body0.entity_type = :db_condition_placeholder_1) AND (field_data_body0.bundle IN (:db_condition_placeholder_2)) AND (field_data_body0.status = :db_condition_placeholder_3) ORDER BY field_data_body0.body_value DESC; Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => node [:db_condition_placeholder_2] => video_series [:db_condition_placeholder_3] => 1 ) in field_sql_storage_field_storage_query() (line 577 of /home/calvarysf/almost.calvarysf.org/modules/field/modules/field_sql_storage/field_sql_storage.module).

Si it seems to be somehow related to EntityReference, moving to that module. See updated Issue Summary.

Tagging as 'i18n compatibility'

I have this error too

The error is occuring on all "node creation" forms of content types with a specific entity reference - Last time the error occured I could fix it by manually deleting the entity-reference in mysql an reenabling it. But after a module update the error reoccured.

I marked this error as cirtical, because the error makes it completly impossible to create content.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Here my error message:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_jahr0.nid' in 'where clause': SELECT field_data_field_jahr0.entity_type AS entity_type, field_data_field_jahr0.entity_id AS entity_id, field_data_field_jahr0.revision_id AS revision_id, field_data_field_jahr0.bundle AS bundle FROM {field_data_field_jahr} field_data_field_jahr0 WHERE (field_data_field_jahr0.deleted = :db_condition_placeholder_0) AND (field_data_field_jahr0.entity_type = :db_condition_placeholder_1) AND (field_data_field_jahr0.bundle IN (:db_condition_placeholder_2)) AND(( ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (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) )OR( (na.gid = :db_condition_placeholder_9) AND (na.realm = :db_condition_placeholder_10) )OR( (na.gid = :db_condition_placeholder_11) AND (na.realm = :db_condition_placeholder_12) )OR( (na.gid = :db_condition_placeholder_13) AND (na.realm = :db_condition_placeholder_14) ))AND (na.grant_view >= :db_condition_placeholder_15) AND (field_data_field_jahr0.entity_id = na.nid) )) AND (field_data_field_jahr0.entity_type = :db_condition_placeholder_16) )OR (field_data_field_jahr0.entity_type <> :db_condition_placeholder_17) )AND ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_18) AND (na.realm = :db_condition_placeholder_19) )OR( (na.gid = :db_condition_placeholder_20) AND (na.realm = :db_condition_placeholder_21) )OR( (na.gid = :db_condition_placeholder_22) AND (na.realm = :db_condition_placeholder_23) )OR( (na.gid = :db_condition_placeholder_24) AND (na.realm = :db_condition_placeholder_25) )OR( (na.gid = :db_condition_placeholder_26) AND (na.realm = :db_condition_placeholder_27) )OR( (na.gid = :db_condition_placeholder_28) AND (na.realm = :db_condition_placeholder_29) ))AND (na.grant_view >= :db_condition_placeholder_30) AND (field_data_field_jahr0.nid = na.nid) )) ORDER BY field_data_field_jahr0.field_jahr_value DESC; Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => node [:db_condition_placeholder_2] => projekt [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => all [:db_condition_placeholder_5] => 25 [:db_condition_placeholder_6] => og_access:node [:db_condition_placeholder_7] => 375 [:db_condition_placeholder_8] => og_access:node [:db_condition_placeholder_9] => 7 [:db_condition_placeholder_10] => og_access:node [:db_condition_placeholder_11] => 27 [:db_condition_placeholder_12] => og_access:node [:db_condition_placeholder_13] => 6 [:db_condition_placeholder_14] => og_access:node [:db_condition_placeholder_15] => 1 [:db_condition_placeholder_16] => node [:db_condition_placeholder_17] => node [:db_condition_placeholder_18] => 0 [:db_condition_placeholder_19] => all [:db_condition_placeholder_20] => 25 [:db_condition_placeholder_21] => og_access:node [:db_condition_placeholder_22] => 375 [:db_condition_placeholder_23] => og_access:node [:db_condition_placeholder_24] => 7 [:db_condition_placeholder_25] => og_access:node [:db_condition_placeholder_26] => 27 [:db_condition_placeholder_27] => og_access:node [:db_condition_placeholder_28] => 6 [:db_condition_placeholder_29] => og_access:node [:db_condition_placeholder_30] => 1 ) in field_sql_storage_field_storage_query() (Zeile 582 von /[...]/modules/field/modules/field_sql_storage/field_sql_storage.module).

FYI: The field with the entityreference is called "Projekt".

UPDATE:

I could disable the error with disabling options for sorting in the entity selection screen

Seems like a duplicate of #1413108: entityFieldQueryAlter() should check if correct table exists.

I do not believe this is a duplicate.

I currently have:

• Drupal 7.17
• Entityreference 7.x-1.x-dev
• Custom node_access with grants

I have an EFQ that resembles:

  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'node')
    ->entityCondition('bundle', 'host')
    ->propertyCondition('status', 1)
    ->propertyOrderBy('created', 'DESC')
    ->pager(10)
    ->addTag('node_access')
    ->fieldCondition('field_organisation', 'target_id', $org_nid, '=');
  $result = $query->execute();

;

If I have both the entityrefence where clause in there, and also the node_access check, then I receive:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_organisation0.nid' in 'where clause': SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {field_data_field_organisation} field_data_field_organisation0 INNER JOIN {node} node ON node.nid = field_data_field_organisation0.entity_id WHERE (field_data_field_organisation0.field_organisation_target_id = :db_condition_placeholder_0) AND (field_data_field_organisation0.deleted = :db_condition_placeholder_1) AND (node.status = :db_condition_placeholder_2) AND (field_data_field_organisation0.entity_type = :db_condition_placeholder_3) AND (field_data_field_organisation0.bundle = :db_condition_placeholder_4) AND(( ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (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) )OR( (na.gid = :db_condition_placeholder_9) AND (na.realm = :db_condition_placeholder_10) ))AND (na.grant_view >= :db_condition_placeholder_11) AND (field_data_field_organisation0.entity_id = na.nid) )) AND (field_data_field_organisation0.entity_type = :db_condition_placeholder_12) )OR (field_data_field_organisation0.entity_type <> :db_condition_placeholder_13) )AND ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_14) AND (na.realm = :db_condition_placeholder_15) )OR( (na.gid = :db_condition_placeholder_16) AND (na.realm = :db_condition_placeholder_17) )OR( (na.gid = :db_condition_placeholder_18) AND (na.realm = :db_condition_placeholder_19) ))AND (na.grant_view >= :db_condition_placeholder_20) AND (field_data_field_organisation0.nid = na.nid) )) ) subquery; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => node [:db_condition_placeholder_4] => host [:db_condition_placeholder_5] => 0 [:db_condition_placeholder_6] => all [:db_condition_placeholder_7] => 4 [:db_condition_placeholder_8] => hp_read_only [:db_condition_placeholder_9] => 4 [:db_condition_placeholder_10] => hp_write_only [:db_condition_placeholder_11] => 1 [:db_condition_placeholder_12] => node [:db_condition_placeholder_13] => node [:db_condition_placeholder_14] => 0 [:db_condition_placeholder_15] => all [:db_condition_placeholder_16] => 4 [:db_condition_placeholder_17] => hp_read_only [:db_condition_placeholder_18] => 4 [:db_condition_placeholder_19] => hp_write_only [:db_condition_placeholder_20] => 1 ) in field_sql_storage_field_storage_query() (line 582 of /var/www/hostplot_web/modules/field/modules/field_sql_storage/field_sql_storage.module)

This looks exactly like the above issues.

When I either:

• Replace the EFQ tag with ->addTag('DANGEROUS_ACCESS_CHECK_OPT_OUT');
• Remove the entityreference field check

The error goes away.

Can anyone shed some light on this?

I don't know exactly what's causing this, probably a bug in node access. But clearly, this is not a bug in Entity Reference. Requalifying as a support request.

I`ve started to have this error after installing Internationalization module. I`ve been using EntityReference without problems before.

Like Gaara, the error is occurring on all "node creation" forms of content types with a specific entity reference. Why this is not a critical bug?

The workaround for me is to disable sorting in all my entity reference fields! But, then I don`t have sorting...

Please help!

I had the same error, and disabling sorting did the trick. The sorting options seems to be useful, though :-(

I can also confirm disabling sorting fixed this as a temporary workaround. However, we too would like to use the sorting functionality.

P.S. bug re-opened: https://drupal.org/node/1920998

Updating for EntityReference

Had a similar error and found that by removing the 'sort' configuration under the entity-reference the error went away.

I am experiencing a very similar problem as described in: https://www.drupal.org/node/2759785.

Basically, I have problems with content access module, and og_access module (a submodule provided by organic groups).

Any suggestions?

Thx

This seems to be actually an entityreference bug but a duplicate of #1920998: Unknown Column Error for All Users Except User 1 When Sort Applied To Simple Entity Selection Mode. Since there's a patch there already I'm marking this as duplicate.