SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'vid' in field list is ambiguous [#1309142]

Problem/Motivation

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'xyz' in field list is ambiguous

Proposed resolution

(thanks to @astutonet from #5

Check your fields list for fields belonging to defined relations. See the advanced part of your view.

These fields probably have no relation name between (). "Name" versus "(Authors) Name"

Edit and save the fields.

This probably solved the issue.

(Solution taken from issue summary #1348060: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'xyz' in field list is ambiguous)

Remaining tasks

We need some example screenshots?

User interface changes

API changes

Original report by seanr

Getting this error with a view:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'vid' in field list is ambiguous

Here's the query:

SELECT DISTINCT node.nid AS nid, name AS name, vid AS vid, tid AS tid, description AS description
FROM 
{node} node
LEFT JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_node ON taxonomy_index.tid = taxonomy_term_data_node.tid
WHERE (( (node.type IN  ('accessories')) ))

There are numerous displays on this view, but I stripped out all but the relevant one for brevity:

$view = new view;
$view->name = 'Product_Groups_2011';
$view->description = 'grids of product families';
$view->tag = '';
$view->base_table = 'node';
$view->human_name = '';
$view->core = 0;
$view->api_version = '3.0-alpha1';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['items_per_page'] = 0;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['distinct'] = TRUE;
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'grid';
$handler->display->display_options['style_options']['columns'] = '3';
$handler->display->display_options['style_options']['fill_single_line'] = 1;
$handler->display->display_options['row_plugin'] = 'fields';
$handler->display->display_options['row_options']['inline'] = array(
  'tid' => 'tid',
  'description' => 'description',
);
$handler->display->display_options['row_options']['hide_empty'] = 0;
/* Relationship: Content: Taxonomy terms on node */
$handler->display->display_options['relationships']['term_node_tid']['id'] = 'term_node_tid';
$handler->display->display_options['relationships']['term_node_tid']['table'] = 'node';
$handler->display->display_options['relationships']['term_node_tid']['field'] = 'term_node_tid';
/* Relationship: Content: Taxonomy terms on node */
$handler->display->display_options['relationships']['term_node_tid_1']['id'] = 'term_node_tid_1';
$handler->display->display_options['relationships']['term_node_tid_1']['table'] = 'node';
$handler->display->display_options['relationships']['term_node_tid_1']['field'] = 'term_node_tid';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 1;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Field: Taxonomy term: Term description */
$handler->display->display_options['fields']['description']['id'] = 'description';
$handler->display->display_options['fields']['description']['table'] = 'taxonomy_term_data';
$handler->display->display_options['fields']['description']['field'] = 'description';
$handler->display->display_options['fields']['description']['relationship'] = 'term_node_tid';
$handler->display->display_options['fields']['description']['label'] = '';
$handler->display->display_options['fields']['description']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['description']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['description']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['description']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['description']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['description']['alter']['trim'] = 0;
$handler->display->display_options['fields']['description']['alter']['html'] = 0;
$handler->display->display_options['fields']['description']['hide_empty'] = 0;
$handler->display->display_options['fields']['description']['empty_zero'] = 0;
/* Contextual filter: Taxonomy vocabulary: Vocabulary ID */
$handler->display->display_options['arguments']['vid']['id'] = 'vid';
$handler->display->display_options['arguments']['vid']['table'] = 'taxonomy_vocabulary';
$handler->display->display_options['arguments']['vid']['field'] = 'vid';
$handler->display->display_options['arguments']['vid']['relationship'] = 'term_node_tid_1';
$handler->display->display_options['arguments']['vid']['exception']['title_enable'] = 1;
$handler->display->display_options['arguments']['vid']['default_argument_type'] = 'fixed';
$handler->display->display_options['arguments']['vid']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['vid']['specify_validation'] = 1;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'product_family' => 'product_family',
);
$handler->display->display_options['filters']['type']['expose']['operator'] = FALSE;

/* Display: Accessory Page */
$handler = $view->new_display('page', 'Accessory Page', 'page_4');
$handler->display->display_options['defaults']['relationships'] = FALSE;
/* Relationship: Content: Taxonomy terms on node */
$handler->display->display_options['relationships']['term_node_tid']['id'] = 'term_node_tid';
$handler->display->display_options['relationships']['term_node_tid']['table'] = 'node';
$handler->display->display_options['relationships']['term_node_tid']['field'] = 'term_node_tid';
$handler->display->display_options['relationships']['term_node_tid']['required'] = 0;
$handler->display->display_options['relationships']['term_node_tid']['vocabularies'] = array(
  'vocabulary_2' => 0,
  'vocabulary_7' => 0,
  'vocabulary_5' => 0,
  'vocabulary_12' => 0,
  'vocabulary_3' => 0,
  'vocabulary_6' => 0,
  'catalog' => 0,
  'vocabulary_21' => 0,
  'vocabulary_15' => 0,
  'vocabulary_14' => 0,
  'vocabulary_22' => 0,
  'vocabulary_11' => 0,
  'vocabulary_23' => 0,
  'vocabulary_24' => 0,
  'vocabulary_19' => 0,
  'vocabulary_16' => 0,
  'vocabulary_8' => 0,
  'vocabulary_13' => 0,
  'vocabulary_10' => 0,
  'vocabulary_9' => 0,
  'vocabulary_17' => 0,
  'vocabulary_20' => 0,
);
$handler->display->display_options['defaults']['fields'] = FALSE;
/* Field: Content: Path */
$handler->display->display_options['fields']['path']['id'] = 'path';
$handler->display->display_options['fields']['path']['table'] = 'node';
$handler->display->display_options['fields']['path']['field'] = 'path';
$handler->display->display_options['fields']['path']['exclude'] = TRUE;
$handler->display->display_options['fields']['path']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['path']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['path']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['path']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['path']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['path']['alter']['trim'] = 0;
$handler->display->display_options['fields']['path']['alter']['html'] = 0;
$handler->display->display_options['fields']['path']['hide_empty'] = 0;
$handler->display->display_options['fields']['path']['empty_zero'] = 0;
$handler->display->display_options['fields']['path']['absolute'] = 0;
/* Field: Taxonomy term: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'taxonomy_term_data';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['label'] = '';
$handler->display->display_options['fields']['name']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['name']['alter']['make_link'] = 1;
$handler->display->display_options['fields']['name']['alter']['path'] = 'http://www.aavidthermalloy.net[path]';
$handler->display->display_options['fields']['name']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['name']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['name']['alter']['trim'] = 0;
$handler->display->display_options['fields']['name']['alter']['html'] = 0;
$handler->display->display_options['fields']['name']['hide_empty'] = 0;
$handler->display->display_options['fields']['name']['empty_zero'] = 0;
$handler->display->display_options['fields']['name']['link_to_taxonomy'] = 0;
/* Field: Taxonomy term: Term description */
$handler->display->display_options['fields']['description']['id'] = 'description';
$handler->display->display_options['fields']['description']['table'] = 'taxonomy_term_data';
$handler->display->display_options['fields']['description']['field'] = 'description';
$handler->display->display_options['fields']['description']['label'] = '';
$handler->display->display_options['fields']['description']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['description']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['description']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['description']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['description']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['description']['alter']['trim'] = 0;
$handler->display->display_options['fields']['description']['alter']['html'] = 0;
$handler->display->display_options['fields']['description']['hide_empty'] = 0;
$handler->display->display_options['fields']['description']['empty_zero'] = 0;
$handler->display->display_options['defaults']['filters'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'accessories' => 'accessories',
);
$handler->display->display_options['filters']['type']['expose']['operator'] = FALSE;
$handler->display->display_options['path'] = 'product-group/accessories-v';

$translatables['Product_Groups_2011'] = array(
  t('Defaults'),
  t('more'),
  t('Apply'),
  t('Reset'),
  t('Sort by'),
  t('Asc'),
  t('Desc'),
  t('Items per page'),
  t('- All -'),
  t('Offset'),
  t('term'),
  t('All'),
  t('Heatsinks Page'),
  t('Heatsinks'),
  t('Attach Content pane'),
  t('Path'),
  t('http://www.aavidthermalloy.net/[path]'),
  t('View panes'),
  t('Attachments Page'),
  t('[name]'),
  t('http://www.aavidthermalloy.net[path]'),
  t('Interface Page'),
  t('Accessory Page'),
  t('Interface Context'),
);

If I turn off distinct, the error goes away, but then I end up with tons of duplicates.

Comment	File	Size	Author
#6	Fields-before-after.png	14.51 KB	clemens.tolboom
#5	fields&query-after.png	31.39 KB	astutonet
#5	fields&query-before.png	62.04 KB	astutonet

Comments

Comment #1

bojanz commented 29 October 2011 at 21:34

Status:

Active

» Postponed (maintainer needs more info)

Please retest with 7.x-3.x-dev, at this point RC1 is too old to debug against.

Comment #2

clemens.tolboom

Dutch

Groningen, 🇳🇱/🇪🇺

commented 21 November 2011 at 17:11

I probably duplicated this issue into #1348060: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'xyz' in field list is ambiguous but that has a solution/workaround.

Comment #3

astutonet

Sao Paulo, SP

commented 22 November 2011 at 22:07

I have the same problem in a view. In my case the message is:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'uid' in field list is ambiguous

This site is in local and is an upgrade from D6 to D7. The views module in D6 is 6.x3.x-dev and in D7 is 7.x-3.x-dev.

I have 40 views in this site and I do not know if all are experiencing this problem.

Tks

Comment #4

clemens.tolboom

Dutch

Groningen, 🇳🇱/🇪🇺

commented 23 November 2011 at 07:31

@astutonet please try the proposed resolution from the issue summary.

Then add a small screenshot of the fields list with a before and after.

Comment #5

astutonet

Sao Paulo, SP

commented 23 November 2011 at 15:57

Status	File	Size
new	fields&query-before.png	62.04 KB
new	fields&query-after.png	31.39 KB

After re-analyzing my problem, I could see that the view that displays the error code is the popular_content view, that have views on blocks and pages and should be used in conjunction with the statistics module.

The View with this occurrence was imported from version 6.x-3.x-dev, and in version 7.x-3.x-dev there is a view with the same characteristics.

The reported problem only occurs in the display "Home/Today ', is related to the "uid" and all other displays are working perfectly.

The solution presented in the summary solved the problem: simply edit the fields and save the view.

My API version is 3.0.

Tks.

Comment #6

clemens.tolboom

Dutch

Groningen, 🇳🇱/🇪🇺

commented 23 November 2011 at 16:48

Status	File	Size
new	Fields-before-after.png	14.51 KB

Thanks for the images

Comment #6.0

clemens.tolboom

Dutch

Groningen, 🇳🇱/🇪🇺

commented 23 November 2011 at 16:48

Issue summary:

View changes

Updated issue summary.

Comment #7

esmerel commented 19 January 2012 at 01:15

Status:

Postponed (maintainer needs more info)

» Active

Comment #8

kerios83 commented 11 February 2012 at 23:15

Turning on Advanced Forum 7.x-2.0-rc1 give me this error.

Comment #9

clemens.tolboom

Dutch

Groningen, 🇳🇱/🇪🇺

commented 12 February 2012 at 08:32

@kerios83: have you checked the 'Proposed resolution' from this issue summary?

And please report your views version as @bojanz #2 stated to use the 3.x branch.

Comment #10

dawehner

German

commented 12 February 2012 at 08:45

Status:

Active

» Postponed (maintainer needs more info)

Update status.

Comment #11

kerios83 commented 12 February 2012 at 10:41

@clemens.tolboom I have disabled forum views, no effect.

Drupal 7.12
Views 7.x-3.1
Advanced Forum 7.x-2.0-rc1

Comment #12

clemens.tolboom

Dutch

Groningen, 🇳🇱/🇪🇺

commented 12 February 2012 at 15:27

@kerios83 I just installed the set you mentioned and I cannot reproduce your error.

What I did was

git checkout 7.12
drush --yes site-install
drush --yes en views_ui advanced_forum devel_generate
drush genc --kill --types=forum 20 4
drush @drupal.d7 uli

next visited http://drupal.d7/forum ... no errors ... so I guess you gave me not enough information :(

What puzzles me is you disabled Advanced Forum and the error is still there ... is it another module?

[Powered by #1115636: Issue Macros and Templates]

Please read Making an issue report to improve this issue report.

Comment #13

kerios83 commented 12 February 2012 at 23:20

Wow I have just discovered that this is not advanced forum that cause this bug, it's drupal core forum...

I thought it's AF cause - after install it I have tried to use mysite.com/forum but even without AF mysite.com/forum is bugged.

- drupal 7.12

I will try this on clean install.

EDIT: On clean install everything is ok... I don't know whats going on.

I got 2 modules I think that MAY 'interupt' forum:

Domain Access 7.x-3.3
Internationalization 7.x-1.4 (Multilingual forum 7.x-1.4)

With normal forum module error looks like:

PDOException
: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous: SELECT f.tid AS tid, COUNT(n.nid) AS topic_count, SUM(ncs.comment_count) AS comment_count FROM {node} n INNER JOIN {node_comment_statistics} ncs ON n.nid = ncs.nid INNER JOIN {forum} f ON n.vid = f.vid INNER JOIN {node} node ON f.tid = node.tid WHERE (status = :db_condition_placeholder_0) AND (n.language IN (:db_condition_placeholder_1, :db_condition_placeholder_2)) GROUP BY tid; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => en [:db_condition_placeholder_2] => und ) in 
forum_forum_load()
 (line 
779
 of 
C:\xampp\htdocs\drupal\modules\forum\forum.module
).

AF:

PDOException
: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous: SELECT f.tid AS tid, COUNT(n.nid) AS topic_count, SUM(ncs.comment_count) AS comment_count FROM {node} n INNER JOIN {node_comment_statistics} ncs ON n.nid = ncs.nid INNER JOIN {forum} f ON n.vid = f.vid INNER JOIN {node} node ON f.tid = node.tid WHERE (status = :db_condition_placeholder_0) AND (n.language IN (:db_condition_placeholder_1, :db_condition_placeholder_2)) GROUP BY tid; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => en [:db_condition_placeholder_2] => und ) in 
advanced_forum_forum_load()
 (line 
142
 of 
C:\xampp\htdocs\drupal\sites\all\modules\advanced_forum\includes\core-overrides.inc
).

EDIT2:

Got it - it's Multilingual select 7.x-1.4 module

I'm sorry for being such a pain in the ass. I should have discover this a the beginning and them make a post. Again thx for help.

tested on clean install - same result - forum with Multilingual select 7.x-1.4 module = PDOException

I have created different issue - http://drupal.org/node/1437932

Comment #14

clemens.tolboom

Dutch

Groningen, 🇳🇱/🇪🇺

commented 14 February 2012 at 20:31

Nice analysis ... just for the ease of use you could use the [#issueid]
Comment #13 is now reported @ i18n #1437932: Multilingual select 7.x-1.4 module cause PDOException on drupal forum

Comment #15

esmerel commented 25 March 2012 at 23:18

Status:

Postponed (maintainer needs more info)

» Closed (duplicate)

Comment #16

guypaddock commented 8 April 2012 at 23:22

Version:	7.x-3.0-rc1	» 7.x-3.3
Status:	Closed (duplicate)	» Active

This is not directly related to i18n, hence not a duplicate.

I am currently able to reproduce this issue when I attempt to add multiple fields (around 5 or more) to a view at once. The first few get initialized correctly. The rest don't even display a settings form, get added without a relationship, and then this error is encountered. Editing and saving the fields corrects the issue.

Oddly, when the fields are initially added, a timestamp or other large number appears as a status message right after the fields are added.

Comment #17

clemens.tolboom

Dutch

Groningen, 🇳🇱/🇪🇺

commented 22 June 2012 at 16:12

@esmerel Of what issue is this one a duplicate?

Today I got this same lists of fields not connected to their relational table similar as the image from #6 shows.

Now the preview reported bad sql ... so we do have some improvement on the behavior.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS users_, users.created AS users_created, 'profile2' AS field_data_field_voorna' at line 1

Steps to reproduce:

Create a view with a relation.
Use a field from base table
Use a field from the relation
Export the view
Edit the export string by removing the line containing something like $handler->display->display_options['fields']['field_voornaam']['relationship'] = 'profile';
Copy the export string
Import the manipulated export into a new view
Preview the bad view

What is changed I guess is the code by adding a try catch in views_plugin+query_default::execute()

      catch (Exception $e) {
        $view->result = array();
        if (!empty($view->live_preview)) {
          drupal_set_message(time());
          drupal_set_message($e->getMessage(), 'error');

But the problem (report of the problem) is still not user friendly :(

Comment #18

dynamicdan commented 9 October 2012 at 10:41

The suggested fix in description/#5 worked for me.

I'm not sure if simply changing the label or saving the field makes a difference to fix it but obviously the relationship for each field is not being checked at some critical point. Changing the label or saving the new field does force this check of the relationship.

Comment #18.0

dynamicdan commented 9 October 2012 at 10:41

Issue summary:

View changes

Updated issue summary.

Comment #19

fietserwin

Le Mont-Dore

commented 15 April 2014 at 18:39

Version:

7.x-3.3

» 7.x-3.7

I can confirm that (on Views 3.7):
- like #16, I got this problem when adding 5 fields at once, for only the first 2, the form was shown.
- like #16, just editing and saving the field again fixes the problem.

Comment #20

mustafa.ata commented 22 May 2014 at 15:13

hey clemens.tolboom (writer of comment #6)

You saved my life..... THanks alot.

I was facing this one
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'uid' in field list is ambiguous

I just clicked on the field and then saved it .... This error gone :)

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'vid' in field list is ambiguous

Problem/Motivation

Proposed resolution

Remaining tasks

User interface changes

API changes

Original report by seanr

Comments

Comment #1

Comment #2

Comment #3

Comment #4

Comment #5

Comment #6

Comment #6.0

Comment #7

Comment #8

Comment #9

Comment #10

Comment #11

Comment #12

Comment #13

Comment #14

Comment #15

Comment #16

Comment #17

Comment #18

Comment #18.0

Comment #19

Comment #20

News items

Our community

Documentation

Drupal code base

Governance of community