SQL error when enabling 'distinct' in a view
doughold - February 18, 2009 - 17:36
| Project: | Faceted Search |
| Version: | 6.x-1.0-beta2 |
| Component: | Views integration |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | needs work |
Jump to:
Description
Here is the error:
* user warning: Operand should contain 1 column(s) query: CREATE TEMPORARY TABLE temp_faceted_search_results_1 (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP SELECT n.nid AS nid, 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp)) - 1234218775) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0.2)) AS score FROM drupal_node AS n LEFT JOIN drupal_node_comment_statistics AS c ON n.nid = c.nid WHERE ((n.status = 1) AND (n.type IN ('product'))) AND n.nid IN (SELECT DISTINCT(node.nid) AS nid, node.type AS node_type, node.title AS node_title, node_data_field_image_cache.field_image_cache_fid AS node_data_field_image_cache_field_image_cache_fid, node_data_field_image_cache.field_image_cache_list AS node_data_field_image_cache_field_image_cache_list, node_data_field_image_cache.field_image_cache_data AS node_data_field_image_cache_field_image_cache_data, node_data_field_image_cache.nid AS node_data_field_image_cache_nid, node_data_field_image_cache.delta AS node_data_field_image_cache_delta, node.vid AS node_vid, uc_products.sell_price AS uc_products_sell_price FROM drupal_node node LEFT JOIN drupal_uc_products uc_products ON node.vid = uc_products.vid LEFT JOIN drupal_content_field_image_cache node_data_field_image_cache ON node.vid = node_data_field_image_cache.vid WHERE node.type IN ('product') ORDER BY node_title ASC ) GROUP BY n.nid ASC ORDER BY score DESC in /var/clients/client0/web22/web/sites/all/modules/faceted_search/faceted_search.inc on line 1174.
* user warning: Table 'naturalselection.temp_faceted_search_results_1' doesn't exist query: SELECT COUNT(*) FROM temp_faceted_search_results_1 in /var/clients/client0/web22/web/sites/all/modules/faceted_search/faceted_search.inc on line 1175.The second error doesn't matter as it is directly caused by the first, but I am not savvy enough with my queries to figure out what is wrong with the first one. From what I have researched thought it appears to have something to do with the SELECT statement...
#1
Edit: I just reset my uc_products view back to default, which is what I was filtering, and it seemed to have fixed the error. I don't exactly know what I changed in the view that caused this error, but maybe this will shed some light on some validation that needs to be implemented or some notes in the documentation as to what not to do!?
#2
The SQL error is caused by the subquery, which is selecting multiple columns. However, I can't reproduce the problem. Even when my test view selects multiple fields or uses multiple sort criteria, in the end the query is fine.
If you ever find the particular element of your view that was causing this, please re-open this issue.
#3
Got the same problem.
user warning: Operand should contain 1 column(s) query: CREATE TEMPORARY TABLE temp_faceted_search_results_1 (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP SELECT n.nid AS nid, 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp)) - 0) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0)) AS score FROM node AS n LEFT JOIN node_comment_statistics AS c ON n.nid = c.nid WHERE ((n.status = 1) AND (n.type IN ('place'))) AND n.nid IN (SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, votingapi_cache_node_overall_average.value AS votingapi_cache_node_overall_average_value, votingapi_cache_node_overall_count.value AS votingapi_cache_node_overall_count_value, node_data_field_level.field_level_value AS node_data_field_level_field_level_value, node_data_field_level.nid AS node_data_field_level_nid, node.type AS node_type FROM node node LEFT JOIN votingapi_cache votingapi_cache_node_overall_average ON node.nid = votingapi_cache_node_overall_average.content_id AND (votingapi_cache_node_overall_average.content_type = 'node' AND votingapi_cache_node_overall_average.tag = 'overall' AND votingapi_cache_node_overall_average.function = 'average') LEFT JOIN votingapi_cache votingapi_cache_node_overall_count ON node.nid = votingapi_cache_node_overall_count.content_id AND (votingapi_cache_node_overall_count.content_type = 'node' AND votingapi_cache_node_overall_count.tag = 'overall' AND votingapi_cache_node_overall_count.function = 'count') LEFT JOIN content_type_place node_data_field_level ON node.vid = node_data_field_level.vid ORDER BY votingapi_cache_node_overall_average_value DESC ) GROUP BY n.nid ASC ORDER BY score DESC in Z:\home\najachops\www\sites\all\modules\faceted_search\faceted_search.inc on line 1174.
user warning: Table 'najachops.temp_faceted_search_results_1' doesn't exist query: SELECT COUNT(*) FROM temp_faceted_search_results_1 in Z:\home\najachops\www\sites\all\modules\faceted_search\faceted_search.inc on line 1175.
The problem is located in this part:
AND n.nid IN (SELECT DISTINCT(node.nid) AS nid, node.title AS ...
the faceted search uses the views sql query in WHERE statement and expects to get 1 field (nid) but view returns all fields - as my view has 'fields' style and several fields to display.
#4
More info:
So to reproduce the error all you need is enabling 'table' display style for your view that is used to display search results.
#5
Seems like I get a similar error with 5.x-1.0-beta4, and views (works fine without views).
The strange thing is that I have 3 different faceted search environments, the first is working. I set up the others to use to use the exact same view as the first one, and I get this error. The same happened when I try to use other views. Does faceted search when used with views, just support one environment?
user warning: Table 'tromsopuls_bibliografia_v2.bib_temp_faceted_search_results_3' doesn't exist query: pager_query SELECT count( DISTINCT(node.nid)) FROM bib_node node INNER JOIN bib_temp_faceted_search_results_3 temp_faceted_search_results_3 ON node.nid = temp_faceted_search_results_3.nid LEFT JOIN bib_content_type_libro node_data_field_copia ON node.vid = node_data_field_copia.vid WHERE (node.status = '1') in /home/bibliografia/bibliografia.tromsopuls.no/includes/database.mysqli.inc on line 156.
#6
I think I have found the error. The db-prefix is not used when the temp-tables is created (CREATE TEMPORARY TABLE temp_faceted_search_results_2) , this seems not to be any problem with 1 environment, but with several environments faceted search starts to query the temp-tables with db-prefix (INNER JOIN dbprefix_temp_faceted_search_results_2 temp_faceted_search_results_2), which doesn't exist.
I guess this error only happen if you use db-prefex.
CREATE TEMPORARY TABLE temp_faceted_search_results_2 (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP SELECT DISTINCT(n.nid) AS nid FROM bib_node AS n INNER JOIN bib_term_node AS term_node_718 ON n.nid = term_node_718.nid WHERE ((n.status = 1) AND (term_node_718.tid = 718)) AND n.nid IN (SELECT DISTINCT(node.nid) FROM bib_node node LEFT JOIN bib_content_type_libro node_data_field_copia ON node.vid = node_data_field_copia.vid WHERE (node.status = '1') ) GROUP BY n.nid ASC
SELECT count( DISTINCT(node.nid)) FROM bib_node node INNER JOIN bib_temp_faceted_search_results_2 temp_faceted_search_results_2 ON node.nid = temp_faceted_search_results_2.nid LEFT JOIN bib_content_type_libro node_data_field_copia ON node.vid = node_data_field_copia.vid WHERE (node.status = '1')
#7
Argh! Just read the readme file...
Known issues:
- If your site is using table prefixing, you will need to tell Drupal not to
prefix temporary tables needed by Faceted Search Views.
Reference: http://drupal.org/node/227634#comment-864171.
Sorry!
#8
#9
Please look at http://drupal.org/node/376382#comment-1330280 .
I do not use table prefixes in my tables. My problem is completely unrelated to table prefixes - it's related to inner SQL query that returns multiple fields when it should return only one.
#10
#11
I've managed to fix that (quick&dirty, I guess)
In faceted_search_views.module in query_alter() method add the following line before
$query->add_subquery('n.nid IN ('. $views_query .')', $views_args);:$views_query = preg_replace("#,(.*?)FROM#is", ' FROM', $views_query);#12
If you get error like 'unable to order by unknown field' add this line too:
$views_query = preg_replace("#ORDER BY (.*)#is", ' ', $views_query);#13
Yes, that looks a bit dirty... The first call to preg_replace() removes all fields from the query except the first one, but will the first one always be the one we need to keep (the nid)? I'd like to understand how you get those queries with Views, to make sure we find a fix that works under all circumstances.
#14
@restyler: Would you mind posting your view to help debug this?
#15
Sure.
<?php$view = new view;
$view->name = 'places2';
$view->description = 'Places faceted';
$view->tag = '';
$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(
'votingapi_cache' => array(
'label' => 'Voting results (overall)',
'required' => 0,
'votingapi' => array(
'value_type' => '',
'tag' => 'overall',
'function' => 'average',
),
'id' => 'votingapi_cache',
'table' => 'node',
'field' => 'votingapi_cache',
'relationship' => 'none',
),
));
$handler->override_option('fields', array(
'title' => array(
'label' => 'Restaurant name',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
),
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'table' => 'node',
'field' => 'title',
'relationship' => 'none',
),
'value' => array(
'label' => 'Rating',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
),
'set_precision' => FALSE,
'precision' => 0,
'decimal' => '.',
'separator' => ',',
'prefix' => '',
'suffix' => '',
'appearance' => 'fivestar_views_value_display_handler',
'exclude' => 0,
'id' => 'value',
'table' => 'votingapi_cache',
'field' => 'value',
'relationship' => 'votingapi_cache',
),
'field_level_value' => array(
'label' => 'Price level',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'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_level_value',
'table' => 'node_data_field_level',
'field' => 'field_level_value',
'relationship' => 'none',
),
'field_review_count_value' => array(
'label' => 'Reviews',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
),
'link_to_node' => 0,
'label_type' => 'custom',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_review_count_value',
'table' => 'node_data_field_review_count',
'field' => 'field_review_count_value',
'relationship' => 'none',
),
));
$handler->override_option('filters', array(
'type' => array(
'operator' => 'in',
'value' => array(
'place' => 'place',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'type',
'table' => 'node',
'field' => 'type',
'relationship' => 'none',
),
'status' => array(
'operator' => '=',
'value' => '1',
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'status',
'table' => 'node',
'field' => 'status',
'relationship' => 'none',
),
));
$handler->override_option('access', array(
'type' => 'none',
));
$handler->override_option('items_per_page', 20);
$handler->override_option('use_pager', '1');
$handler->override_option('distinct', 1);
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
'grouping' => '',
'override' => 1,
'sticky' => 1,
'order' => 'desc',
'columns' => array(
'title' => 'title',
'value' => 'value',
'field_level_value' => 'field_level_value',
'field_review_count_value' => 'field_review_count_value',
),
'info' => array(
'title' => array(
'sortable' => 1,
'separator' => '',
),
'value' => array(
'sortable' => 1,
'separator' => '',
),
'field_level_value' => array(
'sortable' => 1,
'separator' => '',
),
'field_review_count_value' => array(
'sortable' => 1,
'separator' => '',
),
),
'default' => 'value',
));
$handler->override_option('exposed_block', TRUE);
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'restaurants');
$handler->override_option('menu', array(
'type' => 'normal',
'title' => 'Places',
'description' => '',
'weight' => '0',
'name' => 'primary-links',
));
$handler->override_option('tab_options', array(
'type' => 'none',
'title' => '',
'description' => '',
'weight' => 0,
));
?>
#16
I had a view which was working fine until I enabled distinct. I started getting that same error.
When I disable distinct, the error goes away.
Maybe this is the key factor here?
#17
I can confirm that disabling 'distinct' solves this issue.
#18
Thank you, that will help.
#19
Hi David,
I'm assuming that this issue is not considered solved just because turning off Node Distinct in views gets rid of the SQL error?
On our site, Faceted Search is still displaying duplicate nodes when using a view for display. I'm still not sure why this is happening, but my initial testing leads to to taxonomy as being part of the issue.
All the nodes that are being duplicated have two vocabs. As soon as I remove terms from one vocab, the duplication starts. I'm doing some further to confirm that this is indeed the case.
I should also point out that we are using the 1.0-beta2 version, and will experiment with the latest dev in a moment.
Thanks,
Rene
#20
Hi all,
Turning off Node Distinct did not solve the bug in my site. Any suggestions?
Thanks,
Omri
#21
Turning off Node Distinct did not solve the bug in my site.
#22
I have the same SQL error. switching off distinct helps to get rid of it but now it is displaying duplicate nodes.
Is there any solution for this?
#23
Or you can just turn off the default sort in your view.
I had the same error as the first post with Open Atrium beta3.1, and http://drupal.org/node/376382#comment-1351838 helped me to solve the problem. This was effectively not related to prefix nor distinct.
-Polo