PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052

There is nothing I can do on my end. The problem seems to be the (nid IN (139) AND (type IN ('place')). Which modules added that to the query?

I'm getting exactly this same error. I understand that the problem seems to be outside the Entity Reference module, but I'm not sure where it is, so I thought I'd at least add my experience to the discussion here. I'm using D7.8 with Organic Groups 7.x-1.1, Entity Reference 7.x-1.0-beta1, and Content Access 7.x-1.2-beta1. I get errors like the one above with EITHER the OG Access Control module OR the Content Access module enabled; I get no errors if both those modules are disabled.

The error message I get with OG Access Control enabled is:

1052 Column 'nid' in where clause is ambiguous: SELECT DISTINCT node.nid AS entity_id, node.vid AS revision_id, nod\
e.type AS bundle, :entity_type AS entity_type FROM {node} node INNER JOIN {node_access} na ON na.nid = node.nid WHE\
RE (nid IN (:db_condition_placeholder_0)) AND (type IN (:db_condition_placeholder_1)) AND(( (na.gid = :db_condition\
_placeholder_2) AND (na.realm = :db_condition_placeholder_3) )OR( (na.gid = :db_condition_placeholder_4) AND (na.re\
alm = :db_condition_placeholder_5) )OR( (na.gid = :db_condition_placeholder_6) AND (na.realm = :db_condition_placeh\
older_7) )OR( (na.gid = :db_condition_placeholder_8) AND (na.realm = :db_condition_placeholder_9) )OR( (na.gid = :d\
b_condition_placeholder_10) AND (na.realm = :db_condition_placeholder_11) ))AND (na.grant_view >= :db_condition_pla\
ceholder_12) ; Array ( [:db_condition_placeholder_0] => 84 [:db_condition_placeholder_1] => institution [:db_condit\
ion_placeholder_2] => 0 [:db_condition_placeholder_3] => all [:db_condition_placeholder_4] => 5 [:db_condition_plac\
eholder_5] => group_access_authenticated [:db_condition_placeholder_6] => 10 [:db_condition_placeholder_7] => group\
_access_authenticated [:db_condition_placeholder_8] => 9 [:db_condition_placeholder_9] => group_access_authenticate\
d [:db_condition_placeholder_10] => 7 [:db_condition_placeholder_11] => group_access_authenticated [:db_condition_p\
laceholder_12] => 1 [:entity_type] => node ) in EntityFieldQuery->execute() (line 1117 of /var/www/mcrn7.nemac.org/\
includes/entity.inc).

The error I get with Content Access module enabled is:

1052 Column 'nid' in where clause is ambiguous: SELECT DISTINCT node.nid AS entity_id, node.vid AS revision_id, nod\
e.type AS bundle, :entity_type AS entity_type FROM {node} node INNER JOIN {node_access} na ON na.nid = node.nid WHE\
RE (nid IN (:db_condition_placeholder_0)) AND (type IN (:db_condition_placeholder_1)) AND(( (na.gid = :db_condition\
_placeholder_2) AND (na.realm = :db_condition_placeholder_3) )OR( (na.gid = :db_condition_placeholder_4) AND (na.re\
alm = :db_condition_placeholder_5) )OR( (na.gid = :db_condition_placeholder_6) AND (na.realm = :db_condition_placeh\
older_7) ))AND (na.grant_view >= :db_condition_placeholder_8) ; Array ( [:db_condition_placeholder_0] => 84 [:db_co\
ndition_placeholder_1] => institution [:db_condition_placeholder_2] => 0 [:db_condition_placeholder_3] => all [:db_\
condition_placeholder_4] => 48 [:db_condition_placeholder_5] => content_access_author [:db_condition_placeholder_6]\
 => 2 [:db_condition_placeholder_7] => content_access_rid [:db_condition_placeholder_8] => 1 [:entity_type] => node\
 ) in EntityFieldQuery->execute() (line 1117 of /var/www/mcrn7.nemac.org/includes/entity.inc).

In both cases I see that one of the clauses in the query is ( (nid in ...) AND (type in ...)), which is presumably the source of the ambiguous 'nid', but I don't know which module added that clause to the query. If someone can give me some debugging tips on how to figure out where that clause is being added, I'll be glad to try to delve in a little deeper and report what I find.

Also, in case it's relevant, I'll just point out that this error seems really similar to #766382: Column 'nid' is ambiguous when using node access modules.

Same problem here:
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'nid' in where clause is ambiguous: SELECT DISTINCT node.nid AS entity_id, node.vid AS revision_id, node.type AS bundle, :entity_type AS entity_type FROM {node} node INNER JOIN {domain_access} da_admin ON da_admin.nid = node.nid WHERE (nid IN (:db_condition_placeholder_0)) AND (type IN (:db_condition_placeholder_1)) AND(( (da_admin.gid = :db_condition_placeholder_2) AND (da_admin.realm = :db_condition_placeholder_3) )OR( (da_admin.gid = :db_condition_placeholder_4) AND (da_admin.realm = :db_condition_placeholder_5) )); Array ( [:db_condition_placeholder_0] => 30 [:db_condition_placeholder_1] => article [:db_condition_placeholder_2] => 0 [:db_condition_placeholder_3] => domain_site [:db_condition_placeholder_4] => 7 [:db_condition_placeholder_5] => domain_id [:entity_type] => node ) in EntityFieldQuery->execute() (line 1117 of /Applications/MAMP/htdocs/drupal/includes/entity.inc).
I use Domain access, which use Drupal node access system. So I agree with mark.phillips, the solution should be found in #766382: Column 'nid' is ambiguous when using node access modules...

I managed to make it working:
In base.inc file, at line 97, replace
$query->entityCondition('entity_id', $ids, 'IN');
by $query->entityCondition('e.entity_id', $ids, 'IN');
I don't know if it will resolve all issue but that's working for me

#4 is not an acceptable change: this is not how EntityFieldQuery::entityCondition() should be called.

#4 works. However, I agree with Damien in #5. This isn't how the docs state EntityFieldQuery::entityCondition() should be called. Also, what if the entity being referenced isn't a node? I don't think #4 would work anymore.

Damien, I believe that the code you pointed out as problematic in #1 is actually added by the entityreference module on line 97 of handler/base.inc.
$query->entityCondition('entity_id', $ids, 'IN');

The reason why this works without the node access or domain access modules is because the column nid is unique without those modules enabled. I'll point out now where the query is rewritten.

The query is rewritten in the core node module modules/node/node.module, in the function _node_query_node_access_alter. It is here that additional tables are joined and additional columns are selected. These include the node_access table, aliased as na.

The obvious issue is that nid is ambiguous. We now have an nid column returned by the original select, plus the na.nid column returned by the join in the _node_query_node_access_alter function.

I believe that EntityFieldQuery::entityCondition() should be modified to always include the fully qualified column name. This bug should be filed in core, since a similar problem affects other modules. Here is a quick patch that seems to correct the issue.

--- includes/entity.orig.inc    2011-10-28 23:58:27.000000000 -0400
+++ includes/entity.inc 2011-10-28 23:58:32.000000000 -0400
@@ -1079,7 +1079,7 @@
     $id_map['entity_id'] = $sql_field;
     $select_query->addField($base_table, $sql_field, 'entity_id');
     if (isset($this->entityConditions['entity_id'])) {
-      $this->addCondition($select_query, $sql_field, $this->entityConditions['entity_id']);
+      $this->addCondition($select_query, "$base_table." . $sql_field, $this->entityConditions['entity_id']);
     }
 
     // If there is a revision key defined, use it.
@@ -1087,7 +1087,7 @@
       $sql_field = $entity_info['entity keys']['revision'];
       $select_query->addField($base_table, $sql_field, 'revision_id');
       if (isset($this->entityConditions['revision_id'])) {
-        $this->addCondition($select_query, $sql_field, $this->entityConditions['revision_id']);
+        $this->addCondition($select_query, "$base_table." . $sql_field, $this->entityConditions['revision_id']);
       }
     }
     else {

I marked this as "closed (works as designed)" since the bug doesn't lie within this module. If camdarley or the OP wish to revert the change in #4 and test this it would be appreciated. It is working for me.

I'll have confirmed this bug exists in Drupal 7.9 and filed an issue here: #1325628: EntityFieldQuery::propertyQuery missing fully qualified column names causes ambiguous column DB error

-Jim

The error can also be triggered by the forum access module and is present in 7.9.
http://drupal.org/node/1343830

Is it correct that the error is related to Entity in core of Drupal?

#6

Your fix works with 7.9 and Forum Access as well.

Thanks.

the patch worked as well, for drupal 7.9 and content access

thanks!

I don't use any access module but I have also the same problem. But not for the super admin there is no error. Patch in #6 fixes it (D7.10)

Same problem. Patch in #6 fixes it (D7.10).
Thank you!

Thank you, Thank you! I'm running a combination of D7.10, Entity_Reference 7.x-1.0-beta3, and OG 7.x-1.x-dev. I was getting the same "ambiguous nid" error, but the patch in #6 fixed it for me.

Hi all,

I see new people with this problem are still appearing on this thread. I'm working on fixing this issue properly in core's includes/entity.inc, over at #1325628: EntityFieldQuery::propertyQuery missing fully qualified column names causes ambiguous column DB error.

Please check there for a Drupal 7.12 patch, and a pre-patched entity.inc if you don't do patches. I'll keep releasing updates on that issue until this bug gets properly fixed in core.

Just to let you know, after the update to 7.14 - I still had to hack core (see #6 for solution) in order to avoid this error.

@urlaub,

it sounds like you're triggering it a different way than node_access then. That is quite rare now, I could really use your help testing the 7.x final fix at #1325628: EntityFieldQuery::propertyQuery missing fully qualified column names causes ambiguous column DB error.

I will get on creating a proper 7.x backport of the patch there in about an hour (the latest version is 8.x only) If you're not following that issue you could just go there now and hit the green Follow button on the right, and then you'll get a notification when I post it?

Then just make sure the one I post there works - either apply the 7.x patch or use the pre-patched entity.inc for 7.14? That'd be very useful having someone who is still seeing the error review it for us now that node_access people aren't seeing it any more.

@damien_vancouver thanks for working on a core fix!
I am using now your patched file: http://drupal.org/files/entity.inc-patched-drupal7.14-1325628-79.txt and it works perfect!

Nevertheless since the upgrade to 7.14 I have plenty of warnings like:
Notice: Undefined variable: entity_type in list_options_list()
Warning: Missing argument 4 for list_options_list() in list_options_list()
..but this seems to be related to the list module or rules as I had those log messages without your patched entity file as well.

#6 worked great -- thanks jbova!

#6 works great, thanks!!!