SQL error when enabling 'distinct' in a view

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.

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.

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.

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.

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')

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!

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.

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);

If you get error like 'unable to order by unknown field' add this line too:
$views_query = preg_replace("#ORDER BY (.*)#is", ' ', $views_query);

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.

@restyler: Would you mind posting your view to help debug this?

Sure.

$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,
));

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?

I can confirm that disabling 'distinct' solves this issue.

Thank you, that will help.

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

Hi all,

Turning off Node Distinct did not solve the bug in my site. Any suggestions?

Thanks,

Omri

Turning off Node Distinct did not solve the bug in my site.

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?

If you get error like 'unable to order by unknown field' add this line too:
$views_query = preg_replace("#ORDER BY (.*)#is", ' ', $views_query);

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

I'm getting the same error if the Display Style is a View and I add a Views relationship to Flags Flag:Node Flag.
I don't have to add a field, just the relationship.
Deleting the relationship (while still using the View) removes the error.

I'm trying to add a Flag Favorite link to each item that appears in the search list, allowing the user to click on Favorite links to accumulate a set of nodes from multiple Faceted Searches. Then they click the My Favorites tab to see all the nodes they identified as of interest from all searches.

I have the same Flags working in Views retrieved from outside of Faceted Search.

Steve

Using:
Drupal 6.14
Faceted Search 6.x-1.0-beta
Faceted Search Ranges 6.x-1.x-dev
CCK Facets 6.x-1.x-dev
Views 6.x-2.8
Flags 6.x-1.1
Flag Weights 6.x-1.x-dev

OK, I tested every solutions proposed here and nothing worked, I was still getting duplicate nodes in my faceted search view.
BUT, as I also use the usefull Content Access module, I tried unchecking “Per content node access control settings” for all my content types and BINGO! It worked!
So I thought, until I found that a particular content type was still producing duplicates.
Then I realized that this content type was using the Workflow module. I deactivated the “Workflow access” submodule and BOOM! No more duplicates!

Conclusion: I guess the issue we’re dealing with here is related to node access.

@jeromev,
This issue was opened regarding the SQL error referred to in the Issue Title "SQL error..."

The duplicate node issue is just an artifact experienced while searching for the cause of the SQL error, it is not the main problem being solved.

Are you seeing the SQL error?

You're absolutely right, steveray.

I am getting this same error when outputing my search results to a view using distinct. When I remove the distinct setting it works fine, but then I have duplicates. I've tried upgrading the to the dev version, but still same problem. I would love to be able to use this module, but really cannot until this is fixed. Does anyone have a solution?

I am trying to achieve the exact same thing, steveray. Any luck? The facet search listings don't appear when you have the following 2 items setup:

- Relationship: Flags: favorites (uncheck Include only flagged content)
- Fields: Flags: Flag link (Relationship: favorites)

The list appears again when you remove the Field and leave the Relationship. Have you succeeded in showing the Favorites flag link with the list?

I confirm the issue: I am having the SQL error and no result in the search when trying to use views to display the results of faceted search.

I tried also the prefix workaround in the settings.php file as suggested by the readme that is I added to settings.php the following lines:
$db_prefix = array(
'default' => 'mytableprefix_',
'temp_faceted_search_results_1' => '',
);
as i have only one faceted search environment labeled n. 1.

Please note that in the settings.php there may be also a line:

$db_prefix = 'mytableprefix_';

somewhere down the road, so you need to comment that line in order for the prefix workaround to take effect.

Anyway nothing helped for me :((((

Turning off distinct did not help, either.

I use D6, views 6.x-2.2, faceted search 6.x-1.0-beta2

I wonder if the MySql permission "Create temporary tables" should be granted to the database user? May this be the issue?

I can also confirm that disabling distinct fixed my problem.

Subscriving, did the fixes like in #11 & #12 - but would like a nicer fix too - will look into the issue later on!

I ran into this issue without using distinct. I had used a filter on the view though ('admin or published'). Removing the filter solved the issue.

sub

subscribing

subscribing

@big67: this worked for me for searches with results and when there are no results still the same error. back to the drawing board.