While trying to create a view, I found that Views is creating a query that is incompatible with PostgreSQL. The generated query is:

SELECT taxonomy_term_data.name AS taxonomy_term_data_name, taxonomy_term_data.vid AS taxonomy_term_data_vid, taxonomy_term_data.tid AS tid, taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name, COUNT(node_taxonomy_index.nid) AS node_taxonomy_index_nid
FROM
{taxonomy_term_data} taxonomy_term_data
LEFT JOIN {taxonomy_index} taxonomy_index ON taxonomy_term_data.tid = taxonomy_index.tid
LEFT JOIN {node} node_taxonomy_index ON taxonomy_index.nid = node_taxonomy_index.nid
LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
WHERE (( (taxonomy_vocabulary.machine_name IN ('', '', '', 'vocabulary_2')) AND (node_taxonomy_index.nid >= '1') ))
GROUP BY taxonomy_term_data_name, taxonomy_term_data_vid, tid, taxonomy_vocabulary_machine_name
ORDER BY taxonomy_term_data_name ASC

PostgreSQL complains that the field "tid" in the second-to-last line (line 10, highlighted in bold above) is ambiguous, as more than one table being JOINed have a "tid" column.

The PostgreSQL error message is:

SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "tid" is ambiguous LINE 10: ... taxonomy_term_data_name, taxonomy_term_data_vid, tid, taxon... ^

The export of the view is below:

$view = new view();
$view->name = 'jenny_s_taxonomy_bulk_deleter';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'taxonomy_term_data';
$view->human_name = 'Jenny\'s Taxonomy Bulk Deleter';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Jenny\'s Taxonomy Bulk Deleter';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'role';
$handler->display->display_options['access']['role'] = array(
3 => '3',
);
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '300';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['pager']['options']['id'] = '0';
$handler->display->display_options['pager']['options']['quantity'] = '9';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
'name_1' => 'name_1',
'views_bulk_operations' => 'views_bulk_operations',
'nid' => 'nid',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
'name_1' => array(
'sortable' => 1,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'views_bulk_operations' => array(
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'nid' => array(
'sortable' => 0,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
);
/* Relationship: Taxonomy term: Content with term */
$handler->display->display_options['relationships']['nid']['id'] = 'nid';
$handler->display->display_options['relationships']['nid']['table'] = 'taxonomy_index';
$handler->display->display_options['relationships']['nid']['field'] = 'nid';
/* Field: Taxonomy term: Name */
$handler->display->display_options['fields']['name_1']['id'] = 'name_1';
$handler->display->display_options['fields']['name_1']['table'] = 'taxonomy_term_data';
$handler->display->display_options['fields']['name_1']['field'] = 'name';
/* Field: Bulk operations: Taxonomy term */
$handler->display->display_options['fields']['views_bulk_operations']['id'] = 'views_bulk_operations';
$handler->display->display_options['fields']['views_bulk_operations']['table'] = 'taxonomy_term_data';
$handler->display->display_options['fields']['views_bulk_operations']['field'] = 'views_bulk_operations';
$handler->display->display_options['fields']['views_bulk_operations']['label'] = 'SELECT';
$handler->display->display_options['fields']['views_bulk_operations']['vbo_settings']['display_type'] = '0';
$handler->display->display_options['fields']['views_bulk_operations']['vbo_settings']['enable_select_all_pages'] = 1;
$handler->display->display_options['fields']['views_bulk_operations']['vbo_settings']['force_single'] = 0;
$handler->display->display_options['fields']['views_bulk_operations']['vbo_settings']['entity_load_capacity'] = '10';
$handler->display->display_options['fields']['views_bulk_operations']['vbo_operations'] = array(
'action::views_bulk_operations_delete_item' => array(
'selected' => 1,
'postpone_processing' => 0,
'skip_confirmation' => 0,
'override_label' => 0,
'label' => '',
),
'action::views_bulk_operations_script_action' => array(
'selected' => 0,
'postpone_processing' => 0,
'skip_confirmation' => 0,
'override_label' => 0,
'label' => '',
),
'action::views_bulk_operations_modify_action' => array(
'selected' => 0,
'postpone_processing' => 0,
'skip_confirmation' => 0,
'override_label' => 0,
'label' => '',
'settings' => array(
'show_all_tokens' => 1,
'display_values' => array(
'_all_' => '_all_',
),
),
),
'action::views_bulk_operations_argument_selector_action' => array(
'selected' => 0,
'skip_confirmation' => 0,
'override_label' => 0,
'label' => '',
'settings' => array(
'url' => '',
),
),
'action::system_send_email_action' => array(
'selected' => 0,
'postpone_processing' => 0,
'skip_confirmation' => 0,
'override_label' => 0,
'label' => '',
),
'action::pathauto_taxonomy_term_update_action' => array(
'selected' => 0,
'postpone_processing' => 0,
'skip_confirmation' => 0,
'override_label' => 0,
'label' => '',
),
);
/* Field: COUNT(Content: Nid) */
$handler->display->display_options['fields']['nid']['id'] = 'nid';
$handler->display->display_options['fields']['nid']['table'] = 'node';
$handler->display->display_options['fields']['nid']['field'] = 'nid';
$handler->display->display_options['fields']['nid']['relationship'] = 'nid';
$handler->display->display_options['fields']['nid']['group_type'] = 'count';
$handler->display->display_options['fields']['nid']['label'] = 'CONTENT NID';
/* Sort criterion: Taxonomy term: Name */
$handler->display->display_options['sorts']['name']['id'] = 'name';
$handler->display->display_options['sorts']['name']['table'] = 'taxonomy_term_data';
$handler->display->display_options['sorts']['name']['field'] = 'name';
/* Filter criterion: Taxonomy vocabulary: Machine name */
$handler->display->display_options['filters']['machine_name']['id'] = 'machine_name';
$handler->display->display_options['filters']['machine_name']['table'] = 'taxonomy_vocabulary';
$handler->display->display_options['filters']['machine_name']['field'] = 'machine_name';
$handler->display->display_options['filters']['machine_name']['value'] = array(
'min' => '',
'max' => '',
'value' => '',
'vocabulary_2' => 'vocabulary_2',
);
$handler->display->display_options['filters']['machine_name']['group'] = 1;
/* Filter criterion: Content: Nid */
$handler->display->display_options['filters']['nid']['id'] = 'nid';
$handler->display->display_options['filters']['nid']['table'] = 'node';
$handler->display->display_options['filters']['nid']['field'] = 'nid';
$handler->display->display_options['filters']['nid']['relationship'] = 'nid';
$handler->display->display_options['filters']['nid']['operator'] = '>=';
$handler->display->display_options['filters']['nid']['value']['value'] = '1';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'taxonomy-bulk-deleter';

Comments

toni.mueller’s picture

Hello Jared and Drupal community,

this bug still persists in combination with PostgreSQL postgresql 9.3+146really9.1+148 (but not with MySQL). I created a view for a tag cloud in drupal 7 based on Taxonomy terms Taxonomy 7.26. The problem is to add the Field '(node) COUNT(Content: Nid)' with Count Aggregation type to count all terms and display equal terms as one representative with the account of one term's sum. With this field the following error appears already in the view settings:
SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "tid" is ambiguous LINE 8: ... taxonomy_term_data_name, taxonomy_term_data_vid, tid, taxon... ^

The view itself can't be displayed as block and Tag cloud, respectively only if the Field '(node) COUNT(Content: Nid)' is added.
I believe that the best Open Source database can count! :-)

It would be great if PostgreSQL and taxonomy module experts could fix this issue.
Thank you!

Best wishes,

Toni

PS: View Export code:

$view = new view();
$view->name = 'tags_cloud';
$view->description = 'Taxonomy tags cloud block (with counter)';
$view->tag = 'default';
$view->base_table = 'taxonomy_term_data';
$view->human_name = 'Tags cloud';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Tags';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$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['pager']['options']['items_per_page'] = '200';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['pager']['options']['id'] = '0';
$handler->display->display_options['pager']['options']['quantity'] = '9';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: Taxonomy term: Content with term */
$handler->display->display_options['relationships']['nid']['id'] = 'nid';
$handler->display->display_options['relationships']['nid']['table'] = 'taxonomy_index';
$handler->display->display_options['relationships']['nid']['field'] = 'nid';
$handler->display->display_options['relationships']['nid']['group_type'] = 'count';
/* 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']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = FALSE;
$handler->display->display_options['fields']['name']['link_to_taxonomy'] = TRUE;
/* Field: COUNT(Content: Nid) */
$handler->display->display_options['fields']['nid']['id'] = 'nid';
$handler->display->display_options['fields']['nid']['table'] = 'node';
$handler->display->display_options['fields']['nid']['field'] = 'nid';
$handler->display->display_options['fields']['nid']['relationship'] = 'nid';
$handler->display->display_options['fields']['nid']['group_type'] = 'count';
$handler->display->display_options['fields']['nid']['label'] = '';
$handler->display->display_options['fields']['nid']['alter']['alter_text'] = TRUE;
$handler->display->display_options['fields']['nid']['alter']['text'] = '([nid])';
/* Filter criterion: Taxonomy vocabulary: Machine name */
$handler->display->display_options['filters']['machine_name']['id'] = 'machine_name';
$handler->display->display_options['filters']['machine_name']['table'] = 'taxonomy_vocabulary';
$handler->display->display_options['filters']['machine_name']['field'] = 'machine_name';
$handler->display->display_options['filters']['machine_name']['value'] = array(
'all' => 'all',
'tags' => 'tags',
'world' => 'world',
);
$handler->display->display_options['filters']['machine_name']['group'] = '0';

/* Display: Tags */
$handler = $view->new_display('block', 'Tags', 'block');
$handler->display->display_options['display_description'] = 'Tags cloud';
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['defaults']['use_more'] = FALSE;
$handler->display->display_options['use_more'] = TRUE;
$handler->display->display_options['defaults']['use_more_always'] = FALSE;
$handler->display->display_options['defaults']['use_more_always'] = FALSE;
$handler->display->display_options['use_more_always'] = TRUE;
$handler->display->display_options['defaults']['use_more_text'] = FALSE;
$handler->display->display_options['use_more_text'] = 'More';
$handler->display->display_options['defaults']['pager'] = FALSE;
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '40';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['block_description'] = 'Tags cloud';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'tags';

milos.kroulik’s picture

I also experienced this bug on PostgreSQL 9.3 after disabling aggregation on view.

webadpro’s picture

I am also facing this issue.

t_en’s picture

I think that this problem looks a lot like the one in https://www.drupal.org/node/1331056. Perhaps that issue can be of help.

steinmb’s picture

steinmb’s picture

steinmb’s picture

Version: 7.x-3.5 » 7.x-3.x-dev