Column 'nid' in field list is ambiguous query

same here.

the relevant modules im using are:
-cck
-content profile

i installed other modules, i'm not sure they're relevant but i list them anyway
- profile permission
- profile fields

what im trying to achieve is to put a profile node with custom data (i need it to be numeric data) and extract the data in there (put there by content profile and cck) into a page and a block. this is the exported view i made:

$view = new view;
$view->name = 'ranklist';
$view->description = 'Lista la tabla de peleadores';
$view->tag = 'user';
$view->view_php = '';
$view->base_table = 'users';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
  'content_profile_rel' => array(
    'label' => 'ranking',
    'required' => 1,
    'type' => 'profile',
    'id' => 'content_profile_rel',
    'table' => 'users',
    'field' => 'content_profile_rel',
    'relationship' => 'none',
  ),
));
$handler->override_option('fields', array(
  'picture' => array(
    'label' => 'Imagen',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'imagecache_preset' => '',
    'exclude' => 0,
    'id' => 'picture',
    'table' => 'users',
    'field' => 'picture',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'name' => array(
    'label' => 'Nombre',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_user' => 1,
    'overwrite_anonymous' => 0,
    'anonymous_text' => '',
    'exclude' => 0,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'created' => array(
    'label' => 'Fecha de registro',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'date_format' => 'small',
    'custom_date_format' => '',
    'exclude' => 0,
    'id' => 'created',
    'table' => 'users',
    'field' => 'created',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'delete_node' => array(
    'label' => 'Operaciones',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'text' => '',
    'exclude' => 0,
    'id' => 'delete_node',
    'table' => 'users',
    'field' => 'delete_node',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'edit_node' => array(
    'label' => 'Edit link',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'text' => '',
    'exclude' => 0,
    'id' => 'edit_node',
    'table' => 'users',
    'field' => 'edit_node',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'field_weapon_value' => array(
    'label' => 'Arma predilecta',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'group' => 0,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => 0,
    ),
    'exclude' => 0,
    'id' => 'field_weapon_value',
    'table' => 'node_data_field_weapon',
    'field' => 'field_weapon_value',
    'relationship' => 'content_profile_rel',
  ),
  'field_flags_value' => array(
    'id' => 'field_flags_value',
    'table' => 'node_data_field_flags',
    'field' => 'field_flags_value',
  ),
  'field_deaths_value' => array(
    'id' => 'field_deaths_value',
    'table' => 'node_data_field_deaths',
    'field' => 'field_deaths_value',
  ),
  'field_rankpts_value' => array(
    'id' => 'field_rankpts_value',
    'table' => 'node_data_field_rankpts',
    'field' => 'field_rankpts_value',
  ),
));
$handler->override_option('sorts', array(
  'field_rankpts_value' => array(
    'order' => 'DESC',
    'delta' => -1,
    'id' => 'field_rankpts_value',
    'table' => 'node_data_field_rankpts',
    'field' => 'field_rankpts_value',
    'relationship' => 'content_profile_rel',
    'override' => array(
      'button' => 'Override',
    ),
  ),
));
$handler->override_option('filters', array(
  'uid' => array(
    'operator' => 'not in',
    'value' => array(
      '0' => 0,
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'uid',
    'table' => 'users',
    'field' => 'uid',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('items_per_page', 30);
$handler->override_option('use_pager', '1');
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
  'grouping' => '',
  'override' => 1,
  'sticky' => 0,
  'order' => 'desc',
  'columns' => array(
    'value' => 'value',
    'picture' => 'picture',
    'name' => 'name',
    'value_1' => 'value_1',
    'created' => 'created',
    'delete_node' => 'delete_node',
    'edit_node' => 'delete_node',
  ),
  'info' => array(
    'value' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'picture' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'name' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'value_1' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'created' => array(
      'sortable' => 1,
      'separator' => '',
    ),
    'delete_node' => array(
      'separator' => ' |  ',
    ),
    'edit_node' => array(
      'separator' => '',
    ),
  ),
  'default' => 'value',
));
$handler = $view->new_display('page', 'Página', 'page_1');
$handler->override_option('filters', array(
  'uid' => array(
    'operator' => 'not in',
    'value' => array(
      '0' => 0,
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'uid',
    'table' => 'users',
    'field' => 'uid',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('path', 'ranklist');
$handler->override_option('menu', array(
  'type' => 'normal',
  'title' => 'Ranking',
  'description' => '',
  'weight' => '0',
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));
$handler = $view->new_display('block', 'Bloque', 'block_1');
$handler->override_option('fields', array(
  'name' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 1,
      'text' => '[name]',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_user' => 1,
    'overwrite_anonymous' => 0,
    'anonymous_text' => '',
    'exclude' => 0,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
  'field_rankpts_value' => array(
    'label' => 'Puntuacion de Ranking',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_rankpts_value',
    'table' => 'node_data_field_rankpts',
    'field' => 'field_rankpts_value',
    'relationship' => 'content_profile_rel',
    'override' => array(
      'button' => 'Use default',
    ),
  ),
));
$handler->override_option('items_per_page', 5);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
  'grouping' => '',
  'type' => 'ol',
));
$handler->override_option('block_description', 'ranking top 5');
$handler->override_option('block_caching', '8');

my error message is as follows:

user warning: Column 'nid' in field list is ambiguous query: SELECT users.uid AS uid, users.picture AS users_picture, users.name AS users_name, users.created AS users_created, node_users_node_data_field_weapon.field_weapon_value AS node_users_node_data_field_weapon_field_weapon_value, node_users.nid AS node_users_nid, node_users_node_data_field_weapon.delta AS node_users_node_data_field_weapon_delta, node_users.type AS node_users_type, node_users.vid AS node_users_vid, field_flags_value, nid, type, vid, field_deaths_value, field_rankpts_value, node_users_node_data_field_rankpts.field_rankpts_value AS node_users_node_data_field_rankpts_field_rankpts_value FROM users users INNER JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'profile' LEFT JOIN content_field_weapon node_users_node_data_field_weapon ON node_users.vid = node_users_node_data_field_weapon.vid LEFT JOIN content_type_profile node_users_node_data_field_rankpts ON node_users.vid = node_users_node_data_field_rankpts.vid WHERE users.uid not in ('0') ORDER BY node_users_node_data_field_rankpts_field_rankpts_value DESC LIMIT 0, 30 in /home/axlord/www/drupal-6.14/modules/views/includes/view.inc on line 755.

here i formatted the query a little for reading:

SELECT 

	users.uid AS uid, 
	users.picture AS users_picture, 
	users.name AS users_name, 
	users.created AS users_created, 
	node_users_node_data_field_weapon.field_weapon_value AS node_users_node_data_field_weapon_field_weapon_value, 
	node_users.nid AS node_users_nid, 
	node_users_node_data_field_weapon.delta AS node_users_node_data_field_weapon_delta, 
	node_users.type AS node_users_type, 
	node_users.vid AS node_users_vid, 
	field_flags_value, 
	nid, 
	type, 
	vid, 
	field_deaths_value, 
	field_rankpts_value, 
	node_users_node_data_field_rankpts.field_rankpts_value AS node_users_node_data_field_rankpts_field_rankpts_value 

FROM 

	users users 

INNER JOIN 

	node node_users ON users.uid = node_users.uid 
AND 

	node_users.type = 'profile' 
LEFT JOIN 

	content_field_weapon node_users_node_data_field_weapon 
ON 

	node_users.vid = node_users_node_data_field_weapon.vid 
LEFT JOIN 

	content_type_profile node_users_node_data_field_rankpts 
ON 

	node_users.vid = node_users_node_data_field_rankpts.vid 
WHERE 

	users.uid not in ('0') 
ORDER BY 

	node_users_node_data_field_rankpts_field_rankpts_value DESC 
LIMIT 0, 30

so that's about it. thank you in advance for attention.

I had this same problem, and after a few minutes I noticed I was forgetting to add relationships to some fields. You may try to check this.

I've occasionally seen this, but the conditions that create it seem to be complex enough that I've never been able to duplicate it reliably enough to debug it. I would love it if anyone could come up with reliable duping conditions (preferably without needing CCK fields, but I recognize that this may be necessary).

FWIW... I just encountered the problem and it went away.

I created a view of user nodes. Then, in the view, I created a relationship to a "user profile" cck type I had created, which included 3 text fields for first, middle and last name. Then I added the first, middle and last name fields. At that point, I got the error. The fields' formats were all "Default". I set the formats to "Plain Text" and the error went away.

Being a newbie, I don't understand why it happened, but I thought this might help someone out there diagnose and/or fix the problem.

I'm on Drupal 6.14 w/ Views 6.x-2.7 and CCK 6.x-2.6.

I just encountered the problem when I added some CCK fields at the same time in my view (Content Profile and User fields).

I have deleted all fields and new added this fields one by one (exactly the same fields)... no more problem !

FlexiField has the same issue (#606412: Views warning: Ambiguous query)...

I've got this problem, fixed it by referencing the table name in the GROUP BY clause. See attached below.

Hmm. The original query doesn't have a GROUP BY. I don't think this fix addresses the original problem.

I haven't actually figured out what *causes* the original problem.

Only for contribute with this issue:

I have noticed that sometime, when several fields are added together (lets say, Name, Surname and Age from a content Profile added all together not one by one) views fires and "Column 'nid' in field list is ambiguous query" error.

This is because relationships are not correctly saved. This is fixed if you go one by one filed not configured and save the changes again.

Probably my case is only related to Content Profile, and probably it should be fixed providing a default relationship for the field. However since a field may be in several content types this is not easy to determine, and for this reason Content Profile doesn't provide a default relationship.

So, #1, #2, #5, #6, what do you think about this explanation?

The workaround posted in #5 above by ericbellot also worked for me. I removed all the fields, then added them one by one, saving after each add.

Does anybody can replicate this issue in a different way to explained in #9? If not, we should make this as Won't fix, or move to Content Profile issue queue.

This is the first search result in Google, so I figured I would put my findings here...

I saw this issue occur on one of a client's custom module's views after the update from Drupal 6.14/Views 6.x-2.7 and Drupal 6.15/Views 6.x-2.8. I do not know which of these (or both) was the culprit, unfortunately.

I had no idea where to begin debugging this issue so I naturally searched for other issues where this problem was discussed, and came across #348864-18: user warning: Column 'nid' in field list is ambiguous query. There, the module developer recommends always setting Distinct: Yes to get around this problem, and it occurred to me that we had had this problem before, and setting Distinct: Yes solved it. This time I did the reverse, and changed Distinct: Yes to Distinct: No, and voila. No more red errors of death. Huh.

I don't think this is enough info to bring this out of "postponed (maintainer needs more info)" but maybe this will provide some kind of a breadcrumb to the next person who hits this. Though I would welcome a rational explanation as to why I accidentally fixed this by grasping at straws. ;) The only core issue that comes to mind is #284392: db_rewrite_sql causing issues with DISTINCT, but I don't recall any commits for that issue for 6.15...

Oh, I guess fwiw, the client is using Acquia Drupal, so technically it was an update from 1.2.19 (Drupal 6.14 core) to 1.2.21 (Drupal 6.15 core). But afaik, AD sticks with un-hacked copies of Drupal / modules.

After looking through other related-looking issues (#506818: [Postgres] GROUP BY uses field names instead of aliases, #460838: Distinct setting doesn't always work), I came across the patch from #7 here, which is the only one that fixes this problem for me.

My view is (note no content profile):

$view = new view;
$view->name = 'showcase_orgs';
$view->description = 'Showcase organization grid';
$view->tag = 'showcase';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
  'nodereferer_referers' => array(
    'label' => 'Referring Experience',
    'required' => 1,
    'referrer_delta' => '-1',
    'referrer_field' => 'field_exp_org',
    'id' => 'nodereferer_referers',
    'table' => 'node',
    'field' => 'nodereferer_referers',
    'relationship' => 'none',
  ),
));
$handler->override_option('fields', array(
  'field_org_logo_fid' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 0,
    'label_type' => 'none',
    'format' => 'showcase_org_logos_linked',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_org_logo_fid',
    'table' => 'node_data_field_org_logo',
    'field' => 'field_org_logo_fid',
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'field_exp_dates_value' => array(
    'order' => 'DESC',
    'delta' => -1,
    'id' => 'field_exp_dates_value',
    'table' => 'node_data_field_exp_dates',
    'field' => 'field_exp_dates_value',
    'relationship' => 'nodereferer_referers',
  ),
));
$handler->override_option('arguments', array(
  'uid' => array(
    'default_action' => 'not found',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '',
    'breadcrumb' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'user',
    'validate_fail' => 'not found',
    'break_phrase' => 0,
    'not' => 0,
    'id' => 'uid',
    'table' => 'users',
    'field' => 'uid',
    'validate_user_argument_type' => 'uid',
    'validate_user_roles' => array(
      '2' => 0,
      '4' => 0,
      '5' => 0,
      '3' => 0,
      '7' => 0,
      '8' => 0,
      '6' => 0,
      '9' => 0,
    ),
    'relationship' => 'nodereferer_referers',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'education' => 0,
      'education_org' => 0,
      'experience' => 0,
      'organization' => 0,
      'page' => 0,
      'profile' => 0,
      'project' => 0,
      'reel' => 0,
      'reel_media' => 0,
      'resume' => 0,
      'sw_knowledge' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_vocabulary' => array(
      '2' => 0,
      '4' => 0,
      '3' => 0,
      '1' => 0,
    ),
    'validate_argument_type' => 'tid',
    'validate_argument_transform' => 0,
    'validate_user_restrict_roles' => 0,
    'validate_argument_php' => '',
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'organization' => 'organization',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => 'type_op',
      'label' => 'Node: Type',
      'use_operator' => FALSE,
      'identifier' => 'type',
      'remember' => FALSE,
      'single' => TRUE,
      'optional' => TRUE,
      'reduce' => FALSE,
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('items_per_page', 6);
$handler->override_option('distinct', 1);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
  'grouping' => '',
  'type' => 'ul',
));
$handler = $view->new_display('panel_pane', 'Showcase organization logo grid', 'panel_pane_1');
$handler->override_option('pane_title', '');
$handler->override_option('pane_description', '');
$handler->override_option('pane_category', array(
  'name' => 'View panes',
  'weight' => 0,
));
$handler->override_option('allow', array(
  'use_pager' => FALSE,
  'items_per_page' => FALSE,
  'offset' => FALSE,
  'link_to_view' => FALSE,
  'more_link' => FALSE,
  'path_override' => FALSE,
  'title_override' => FALSE,
  'exposed_form' => FALSE,
));
$handler->override_option('argument_input', array(
  'uid' => array(
    'type' => 'panel',
    'context' => 'term.tid',
    'context_optional' => 0,
    'panel' => '0',
    'fixed' => '',
    'label' => 'User: Uid',
  ),
));
$handler->override_option('link_to_view', 0);
$handler->override_option('inherit_panels_path', 0);

The bad SQL generated (before the patch) is:

SELECT FIRST(DISTINCT(node.nid)),
   FIRST(node_data_field_org_logo.field_org_logo_fid) AS node_data_field_org_logo_field_org_logo_fid,
   FIRST(node_data_field_org_logo.field_org_logo_list) AS node_data_field_org_logo_field_org_logo_list,
   FIRST(node_data_field_org_logo.field_org_logo_data) AS node_data_field_org_logo_field_org_logo_data,
   FIRST(node.type) AS node_type,
   FIRST(node.vid) AS node_vid,
   FIRST(node_node_node_data_field_exp_dates.field_exp_dates_value) AS node_node_node_data_field_exp_dates_field_exp_dates_value
 FROM node node 
 LEFT JOIN content_type_experience node2 ON node.nid = node2.field_exp_org_nid
 INNER JOIN node node_node ON node2.nid = node_node.nid
 INNER JOIN users node_node__users ON node_node.uid = node_node__users.uid
 LEFT JOIN content_field_org_logo node_data_field_org_logo ON node.vid = node_data_field_org_logo.vid
 LEFT JOIN content_type_experience node_node_node_data_field_exp_dates ON node_node.vid = node_node_node_data_field_exp_dates.vid
 WHERE (node.type in ('organization')) AND (node_node__users.uid = 3)
 GROUP BY nid
  ORDER BY node_node_node_data_field_exp_dates_field_exp_dates_value DESC

The Postgres error:

warning: pg_query() [function.pg-query]: Query failed: ERROR: column reference "nid" is ambiguous LINE 15: GROUP BY nid ^ in /redacted/path/includes/database.pgsql.inc on line 139.

And the good SQL that works (after the patch):

SELECT DISTINCT(node.nid),
   FIRST(node_data_field_org_logo.field_org_logo_fid) AS node_data_field_org_logo_field_org_logo_fid,
   FIRST(node_data_field_org_logo.field_org_logo_list) AS node_data_field_org_logo_field_org_logo_list,
   FIRST(node_data_field_org_logo.field_org_logo_data) AS node_data_field_org_logo_field_org_logo_data,
   FIRST(node.type) AS node_type,
   FIRST(node.vid) AS node_vid,
   FIRST(node_node_node_data_field_exp_dates.field_exp_dates_value) AS node_node_node_data_field_exp_dates_field_exp_dates_value
 FROM node node 
 LEFT JOIN content_type_experience node2 ON node.nid = node2.field_exp_org_nid
 INNER JOIN node node_node ON node2.nid = node_node.nid
 INNER JOIN users node_node__users ON node_node.uid = node_node__users.uid
 LEFT JOIN content_field_org_logo node_data_field_org_logo ON node.vid = node_data_field_org_logo.vid
 LEFT JOIN content_type_experience node_node_node_data_field_exp_dates ON node_node.vid = node_node_node_data_field_exp_dates.vid
 WHERE (node.type in ('organization')) AND (node_node__users.uid = 3)
 GROUP BY node.nid
  ORDER BY node_node_node_data_field_exp_dates_field_exp_dates_value DESC

Based on this, and the fact that it doesn't break any of my other views, I'm happy to mark Josh's patch RTBC (although I guess someone needs to confirm it doesn't break MySQL).

@neilnz Sorry, but as merlinofchaos said in #8 some users are not using Group By in theirs queries.

Probably this is a kind of meta issue that is triggered by different causes.

Anyway, if patch in #7 have to be committed, it needs a re-roll since Views 3 is not using query.inc anymore.

But IMO, #7 is not enough to mark this whole issue as RTBC, sorry.

@webchick, Is there any chance to see the view export from your client's view?

I have an OpenAtrium install running on PGSQL that was getting these same errors. Actually I was getting ambiguous "uid" in some places and ambiguous "nid" in others depending on which table was the pivot, and which was being joined (aka views relationship). One common page the error could be seen was on the member directory. Applying the two-line patch in #7 fixed the errors for the members page as well as in other custom views.

Looking at:

SELECT DISTINCT(node.nid),
   FIRST(node_data_field_org_logo.field_org_logo_fid) AS node_data_field_org_logo_field_org_logo_fid,
   FIRST(node_data_field_org_logo.field_org_logo_list) AS node_data_field_org_logo_field_org_logo_list,
   FIRST(node_data_field_org_logo.field_org_logo_data) AS node_data_field_org_logo_field_org_logo_data,
   FIRST(node.type) AS node_type,
   FIRST(node.vid) AS node_vid,
   FIRST(node_node_node_data_field_exp_dates.field_exp_dates_value) AS node_node_node_data_field_exp_dates_field_exp_dates_value
FROM node node
LEFT JOIN content_type_experience node2 ON node.nid = node2.field_exp_org_nid
INNER JOIN node node_node ON node2.nid = node_node.nid
INNER JOIN users node_node__users ON node_node.uid = node_node__users.uid
LEFT JOIN content_field_org_logo node_data_field_org_logo ON node.vid = node_data_field_org_logo.vid
LEFT JOIN content_type_experience node_node_node_data_field_exp_dates ON node_node.vid = node_node_node_data_field_exp_dates.vid
WHERE (node.type in ('organization')) AND (node_node__users.uid = 3)
GROUP BY node.nid
  ORDER BY node_node_node_data_field_exp_dates_field_exp_dates_value DESC

It seems to me that if you do a
SELECT DISTINCT ON (node.nid) FROM node GROUP BY node.nid

It is equivalent to a
SELECT (node.nid) FROM node LEFT JOIN ... and then replacing INNER JOINS with LEFT JOINs and maybe some subqueries (?)
This is only a first impression, don't flame me!

Could you print the data definition involved. Just open pgAdmin3, copy table definition and paste here.
It will allow me to play with your SQL.

In general, DISTINCT should never be used and replaced with LEFT JOINs whenever possible, because DISTINCT creates sequential scans in every database, just to differentiate data. You may not notice a difference on 10 entries, but on 100.000 rows it can kill your database.

Also when using DISTINCT, it is very hard to predict how the database reacts with JOINs.

Please note I am not sure to help you, I can only try.

this is related to #460838: Distinct setting doesn't always work.

Here's a schema-only dump of the database from pg_restore in SQL format. Let me know if something else would be more useful.

Josh's problem in #7 is http://drupal.org/node/506818

Anything dealing with postgres and group by should be used in that issue.

Let's use this issue to work on the issue where sometimes the base field is added without an alias -- that seems to happen only in some strange relationship cases, as dagmar mentioned in #9.

Ok, I believe I understand this issue, thanks very much to q0rban reproducing this with a custom relationship and sending me the tarball so I could dsm() the crap out of it.

What happens is that some fields might use the additional_fields() mechanism of views_handler_field to try and get more fields, but due to the complexity of the relationships, those tables might not be automatically added.

What then happens is that views_handler_field::add_additional_fields() might try to add a field even though ensure_table failed. Then, query::add_field() is called with $table NULL, which means that the field is considered a formula and added by itself. Therefore 'nid' is added. Unfortunately, we can't detect and prevent that condition, since that's how formulae like like LEFT(node_title, 1) are added as fields. We can stop it in add_additional_fields() but that won't prevent handlers from failing to test if ensure_table produced a result and error appropriately if it did not.

We can, however, test add_additional_fields().

Here's a patch. I'd love it if anyone who experiences this issue *not* related to the pgsql issue could test this.

I tested this and it did indeed hide the errors, printed a nice debug error, and the view actually output again. :)

I'm going to still leave this as needs review since merlinofchaos was probably working from my codebase when he tested it.

I got solution unless it is only work for me.
I used:
* drupal-6.16
* views 6.x-2.8

I found the problem is only because I selected 'distinct' n tick 'yes'. now I changed to 'no'.

the procedures is as below:
go to admin > site buildings > views
click in edit of taxonomy_term(default)
in default play : basic settings : distinct, if that is yes (because my setting is Yes by default), just change to NO.
that is all. By the way, I dont have any relationships in that settings. and my arguments are termID and termID with depth. sorts by book weight asc. filters: node published and admin. others are keep default.

my taxonomy pages has no errors, which show books under by sequence of book weight.

wish it could help to somebody.

I experienced this error on a site in Views 2.x, and the patch in #20 resolved it. I realize that's not much of a thorough review -- I'd be happy to post an output of the affected view if that helps.

Thanks!

I was seeing the warning when adding imagefield as a relationship. Patch seems to have resolved the issue.

Drupal 6.16
Views 2.10
Filefield + Imagefield 3.3

Hello,
I tested the patch and it doesn't seem to work for me.
Environment: Drupal 6.16, MySQL 5.1.47, PHP 3.2.3, Views 6.x-2.10, Views JSON(Datasource) 6.x-1.0-beta1, CCK Not installed.

These are the steps I took in testing a JSON content view:
1. Added a relationship of comment to node.
2. Added 4 fields at the same time. Preview.
3. Received the ambiguous error. Notice 3 fields are actually ambiguous:
user warning: Column 'nid' in field list is ambiguous query: SELECT comments.cid AS cid, comments.name AS comments_name, comments.uid AS comments_uid, comments.homepage AS comments_homepage, comments.comment AS comments_comment, comments.format AS comments_format, nid, title, language, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid ORDER BY comments_timestamp DESC LIMIT 0, 1 in C:\UniServer\www\drupal-6.16\sites\all\modules\views\includes\view.inc on line 771.
4. Removed the 4 fields and added each one individually, as suggested above. Did not save until all were added.
5. Error was gone.
6. Repeated, by removing and then adding 4 fields at the same time.
7. Received the ambiguous error again exactly the same.
8. Added the patch code.
9. Went back to preview view and received slightly different error: language is out of the query:
user warning: Column 'nid' in field list is ambiguous query: SELECT comments.cid AS cid, comments.name AS comments_name, comments.uid AS comments_uid, comments.homepage AS comments_homepage, comments.comment AS comments_comment, comments.format AS comments_format, nid, title, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid ORDER BY comments_timestamp DESC LIMIT 0, 1 in C:\UniServer\www\drupal-6.16\sites\all\modules\views\includes\view.inc on line 771.
10. Removed the 4 fields and added them again at the same time.
11. Received same ambiguous error:
user warning: Column 'nid' in field list is ambiguous query: SELECT comments.cid AS cid, comments.name AS comments_name, comments.uid AS comments_uid, comments.homepage AS comments_homepage, comments.comment AS comments_comment, comments.format AS comments_format, nid, title, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid ORDER BY comments_timestamp DESC LIMIT 0, 1 in C:\UniServer\www\drupal-6.16\sites\all\modules\views\includes\view.inc on line 771.
12. Commented out the patch code and hit preview again.
13. Received ambiguous error with language once again part of query:
user warning: Column 'nid' in field list is ambiguous query: SELECT comments.cid AS cid, comments.name AS comments_name, comments.uid AS comments_uid, comments.homepage AS comments_homepage, comments.comment AS comments_comment, comments.format AS comments_format, nid, title, language, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid ORDER BY comments_timestamp DESC LIMIT 0, 1 in C:\UniServer\www\drupal-6.16\sites\all\modules\views\includes\view.inc on line 771.
14. Removed the 4 fields and added each individually, did not save until all were added.
15. Error is gone, code is returned in preview.

It seems the patch successfully removed one ambiguous field. Hope this helps a bit. I'm up and running with adding each field separately.

edit. sorry seemed to have changed the priority here-

This patch looks fine.

I broke manually a field and this debug message appeared and the view works further. This is good

#26 rlo: Ok, I reproduced your problem, and I understand it.

Your problem happens because when 'node' fields are added, they actually *require* a relationship. However, if you don't actually go and 'update' the field, they are set, by default, to use the base relationship. They cannot use this relationship, though, because they are not valid for 'comment' base, they are only valid for 'node' base. As soon as you click 'update' on the field, this problem goes away because the relationship is forced to be set by the selector.

This means that the default relationship logic needs some help. However it's not a critical issue because it's easily worked around by clicking 'update' on the broken field. The patch does fix other problems, though, and is being committed.

Can you please reproduce your comment in a new issue so we can address that separately? And thank you very much for the thoroughly detailed report you gave!

#20 has been committed to all branches.

Thanks for the education. I've opened #830716: Column 'nid' in field list is ambiguous query~ while adding multiple fields and not setting relationship -hope it is sufficient.