Exposed Search Filters broken in 6.x-2.4

Let me guess. When you use them pink elephants come out of your screen and trample your keyboard turning into useless goo? Oh wait. This is an issue queue. I shouldn't have to guess what you mean by 'break'.

Please use proper issue queue etiquette, lest I send pink elephants to your house to turn your keyboard into useless goo to save the rest of us.

Can you do me a favor and paste the query that the view produces?

I'm having the same issue.
Drupal 6.10
Views 6.x-2.4

Here's the query:

SELECT node.nid AS nid,
   SUM(search_index.score * .count) AS score,
   field_project_image_fid,
   field_project_image_list,
   field_project_image_data,
   node.type AS node_type,
   node.vid AS node_vid,
   node.title AS node_title
 FROM node node 
 LEFT JOIN search_index search_index ON node.nid = search_index.sid
 LEFT JOIN search_total  ON search_index.word = .word
 WHERE (node.type in ('project')) AND (node.status <> 0) AND (search_index.word = 'teatro') AND (search_index.type = 'search_index')
 GROUP BY search_index.sid, node_title, nid, field_project_image_fid, field_project_image_list, field_project_image_data, node_type, node_vid
 HAVING COUNT(*) >= 1
 ORDER BY node_title ASC

Here's the error

user warning: 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 score, field_project_image_fid, field_project_image_list, field_pr' at line 2 query: SELECT node.nid AS nid, SUM(search_index.score * .count) AS score, field_project_image_fid, field_project_image_list, field_project_image_data, node.type AS node_type, node.vid AS node_vid, node.title AS node_title FROM node node LEFT JOIN search_index search_index ON node.nid = search_index.sid LEFT JOIN search_total ON search_index.word = .word WHERE (node.type in ('project')) AND (node.status <> 0) AND (search_index.word = 'teatro') AND (search_index.type = 'search_index') GROUP BY search_index.sid, node_title, nid, field_project_image_fid, field_project_image_list, field_project_image_data, node_type, node_vid HAVING COUNT(*) >= 1 ORDER BY node_title ASC in /var/www/vhosts/dodorama.com/subdomains/guerri/httpdocs/site/sites/all/modules/views/includes/view.inc on line 735.

Here's the view export:

$view = new view;
$view->name = 'projects';
$view->description = '';
$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('fields', array(
  'title' => array(
    'label' => '',
    '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',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'title' => array(
    'order' => 'ASC',
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'override' => array(
      'button' => 'Sovrascrivi',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'project' => 'project',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'status' => array(
    'operator' => '=',
    'value' => '1',
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'status',
    'table' => 'node',
    'field' => 'status',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('title', 'Projects');
$handler->override_option('empty', 'Non sono ancora stati inseriti progetti');
$handler->override_option('empty_format', '1');
$handler->override_option('items_per_page', 0);
$handler->override_option('use_pager', '0');
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
  'grouping' => '',
  'type' => 'ul',
));
$handler = $view->new_display('page', 'Project List', 'page_1');
$handler->override_option('path', 'projects_list');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));
$handler = $view->new_display('page', 'Home', 'page_2');
$handler->override_option('fields', array(
  'field_project_image_fid' => array(
    'label' => '',
    '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' => 'none',
    'format' => 'home_linked',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_project_image_fid',
    'table' => 'node_data_field_project_image',
    'field' => 'field_project_image_fid',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
  'title' => array(
    'label' => '',
    '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',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'random' => array(
    'order' => 'ASC',
    'id' => 'random',
    'table' => 'views',
    'field' => 'random',
    'override' => array(
      'button' => 'Usa predefinito',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('title', 'Featured projects');
$handler->override_option('items_per_page', 3);
$handler->override_option('path', 'home');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));
$handler = $view->new_display('page', 'Project Page', 'page_3');
$handler->override_option('fields', array(
  'field_project_image_fid' => array(
    'label' => '',
    '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,
    'label_type' => 'none',
    'format' => 'project_thumbs_default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_project_image_fid',
    'table' => 'node_data_field_project_image',
    'field' => 'field_project_image_fid',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
  'title' => array(
    'label' => '',
    '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',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'title' => array(
    'order' => 'ASC',
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Use default',
    ),
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'project' => 'project',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'status' => array(
    'operator' => '=',
    'value' => '1',
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'status',
    'table' => 'node',
    'field' => 'status',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'field_typology_value_many_to_one' => array(
    'operator' => 'or',
    'value' => array(),
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'field_typology_value_many_to_one_op',
      'identifier' => 'field_typology_value_many_to_one',
      'label' => 'Tipologia',
      'optional' => 1,
      'single' => 1,
      'remember' => 0,
      'reduce' => 0,
    ),
    'id' => 'field_typology_value_many_to_one',
    'table' => 'node_data_field_typology',
    'field' => 'field_typology_value_many_to_one',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
    'reduce_duplicates' => 0,
  ),
  'field_state_value_many_to_one' => array(
    'operator' => 'or',
    'value' => array(),
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'field_state_value_many_to_one_op',
      'identifier' => 'field_state_value_many_to_one',
      'label' => 'Stato',
      'optional' => 1,
      'single' => 1,
      'remember' => 0,
      'reduce' => 0,
    ),
    'id' => 'field_state_value_many_to_one',
    'table' => 'node_data_field_state',
    'field' => 'field_state_value_many_to_one',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
    'reduce_duplicates' => 0,
  ),
  'keys' => array(
    'operator' => 'optional',
    'value' => '',
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'keys_op',
      'identifier' => 'keys',
      'label' => 'Search',
      'optional' => 1,
      'remember' => 0,
    ),
    'id' => 'keys',
    'table' => 'search_index',
    'field' => 'keys',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('path', 'projects');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));

I'm having the same problem (also in version 6.x-2.4) , my errors are:

* user warning: 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 score, users.picture AS users_picture, users.name AS users_name, u' at line 2 query: SELECT COUNT(*) FROM (SELECT DISTINCT(users.uid) AS uid, SUM(search_index.score * .count) AS score, users.picture AS users_picture, users.name AS users_name, users.created AS users_created, users.access AS users_access, value FROM users users LEFT JOIN search_index search_index ON users.uid = search_index.sid LEFT JOIN search_total ON search_index.word = .word WHERE (users.uid not in ('0')) AND (search_index.word = 'test') AND (search_index.type = 'search_index') GROUP BY search_index.sid, users_access, users_created, users_name, uid, users_picture, value HAVING COUNT(*) >= 1 ORDER BY users_access DESC, users_created DESC, users_name ASC ) count_alias in /home2/ikverdie/public_html/modules/views/includes/view.inc on line 705.

* user warning: 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 score, users.picture AS users_picture, users.name AS users_name, u' at line 2 query: SELECT DISTINCT(users.uid) AS uid, SUM(search_index.score * .count) AS score, users.picture AS users_picture, users.name AS users_name, users.created AS users_created, users.access AS users_access, value FROM users users LEFT JOIN search_index search_index ON users.uid = search_index.sid LEFT JOIN search_total ON search_index.word = .word WHERE (users.uid not in ('0')) AND (search_index.word = 'test') AND (search_index.type = 'search_index') GROUP BY search_index.sid, users_access, users_created, users_name, uid, users_picture, value HAVING COUNT(*) >= 1 ORDER BY users_access DESC, users_created DESC, users_name ASC LIMIT 0, 25 in /home2/ikverdie/public_html/modules/views/includes/view.inc on line 731.

Thanks!

Greetz Gunther

I'm having a similar issue: #428276: Search using Views filters and error in SQL syntax - please help.

Thanks in advance for help.

The error is caused by the missing table alias for the search_total table. I found that changing line 90 in views_handler_filter_search.inc from:

      $search_total = $this->query->add_relationship(NULL, $join, $search_index);

to

      $search_total = $this->query->add_relationship('search_total', $join, $search_index);

fixed the problem for me. However, I'm not convinced this is the best course of action. It seems that the query->add_relationship method should detect a NULL alias and handle that properly.

Hi,

I changed line 90 in views_handler_filter_search.inc, and i'm having no errors anymore! But when i search something, it doesn't give me results:( Does anyone have the same problem?

Thanks,
Gunther

Confirmed locally. This is going to break the drupal.org issue queues if I were to upgrade d.o to views 6.x-2.4, so I'm debugging it now. Stay tuned for a patch.

Discussed this at length with Earl. There's no way to automatically handle a NULL $alias for add_relationship() itself. For example, if you default the alias to the $base table parameter, you're screwed because the resulting query will be trying to reuse the existing relationship instead of using a new alias. In fact, the way add_relationship() is invoked by the search filter (the root of this bug) would break if we used $base if $alias is NULL inside add_relationship() itself.

So, the problem is that $alias can't be NULL to add_relationship(). I grepped the views code, and found the following:

1. includes/handlers.inc:      $relationship = $this->handler->query->add_relationship(NULL, $r_join, $r_join->table, $this->handler->relationship);
   

   This is inside an edge case in add_table(). Earl says to punt on this and he'll revisit when he can swap back in the inner workings of add_table(). ;)

2. includes/query.inc:  function add_relationship($alias, $join, $base, $link_point = NULL) {
   

   Supplies a real $alias, no problem here.

3. modules/comment/views_handler_field_ncs_last_comment_name.inc://    $this->user_table = $this->query->add_relationship(NULL, $join, 'users', $this->relationship);
   

   Commented out, not a problem.

4. modules/comment/views_handler_sort_ncs_last_comment_name.inc://    $this->user_table = $this->query->add_relationship(NULL, $join, 'users', $this->relationship);
   

   Commented out, not a problem.

5. modules/search/views_handler_filter_search.inc:      $search_total = $this->query->add_relationship(NULL, $join, $search_index);
   

   The bug here. Fixed with the attached patch.

6. modules/translation/views_handler_relationship_translation.inc:    $this->alias = $this->query->add_relationship($alias, $join, $this->definition['base'], $this->relationship);
   

   Supplies a real $alias, no problem here.

So, the only real problem is the bug reported here, and the edge case in add_table(). Two patches attached -- one that fixes the search bug, and another that removes the commented out bogus versions.

Although most of this is over my head, I thank you for the prompt responses, dww.
I'll read up on applying patches (oddly enough, I haven't patched anything since I started using Drupal) and try it out.

Hi dww,

Thanks for your patch! I patched it, but when i'm trying to search on a particular word, he won't give me results. I'm eventhough sure that the particular word must give me results. In my attachment you can find a screenshot of my view, i was wondering if verything was correct.

Thanks for your time!

Gunther

If you tested it and say it doesn't work right, you shouldn't set the status to RTBC. However, I just tested again locally and my patched copy is definitely working. Even still, moving this back to needs review, pending testing from other people experiencing this bug.

Since I only really read/speak english, that screenshot doesn't make much sense to me. Besides, a screenshot of the view overview itself is pretty unhelpful for debugging -- posting an exported copy of your view would be a lot more useful.

That said, it sounds like you might have a problem where your search index is stale instead of a problem with this patch (which would be a support request, not something to discuss in this issue).

Hi,

Sorry for the screenshot, here's a exported version of another view, where it doesn't work either. Maybe it's indeed a support problem.

$view = new view;
$view->name = 'Gebruikerslijst2';
$view->description = 'Gebruikerslijst';
$view->tag = 'Gebruikerslijst';
$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', 'Gebruikerslijst', 'default');
$handler->override_option('fields', array(
  'name' => array(
    'label' => 'Gebruikersnaam',
    '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_user' => 1,
    'overwrite_anonymous' => 0,
    'anonymous_text' => '',
    'exclude' => 0,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Override',
    ),
  ),
  'mail' => array(
    'label' => 'E-mailadres',
    '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_user' => '0',
    'exclude' => 0,
    'id' => 'mail',
    'table' => 'users',
    'field' => 'mail',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'keys' => array(
    'operator' => 'optional',
    'value' => '',
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => 'keys_op',
      'label' => 'Zoeken: Search Terms',
      'use_operator' => 0,
      'identifier' => 'keys',
      'optional' => 0,
      'remember' => 0,
    ),
    'id' => 'keys',
    'table' => 'search_index',
    'field' => 'keys',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Override',
    ),
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('title', 'Profiel zoeken');
$handler->override_option('header_format', '2');
$handler->override_option('header_empty', 0);
$handler->override_option('items_per_page', 0);
$handler->override_option('use_pager', '0');
$handler->override_option('distinct', 1);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
  'grouping' => '',
  'type' => 'ul',
));
$handler->override_option('row_options', array(
  'inline' => array(
    'picture' => 'picture',
    'name' => 'name',
    'created' => 'created',
    'access' => 'access',
  ),
  'separator' => '',
));
$handler = $view->new_display('page', 'Pagina', 'page_1');
$handler->override_option('filters', array(
  'keys' => array(
    'operator' => 'optional',
    'value' => '',
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'keys_op',
      'identifier' => 'keys',
      'label' => 'Zoeken: Search Terms',
      'optional' => 0,
      'remember' => 0,
    ),
    'id' => 'keys',
    'table' => 'search_index',
    'field' => 'keys',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Use default',
    ),
  ),
));
$handler->override_option('path', 'testzoeken');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => 'Gebruikerslijst',
  'description' => 'Gebruikerslijst',
  'weight' => '2',
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));

Thanks for your help!

Gunther

@guntherdevisch: Can you search for the same term on your site using the site-wide search box? Only if it works using non-views search and fails using views does your question belong in this issue queue. If not, please just open a new issue -- it clearly has nothing to do with this bug or patch (or views itself). Thanks.

Yes dww, the search works within the non-views search-box and fails within my views search-box.

Sorry for the ambiguity. I'm Dutch:)

Greetz,
Gunther

I tested the patch and I can confirm it works in my installation.
Thanks

guntherdevisch: IF the search is failing to find a word but is not producing the error people were seeing, that is a different issue altogether.

Both of dww's patches are committed.

Oke, that's true. It don't have the errors anymore, so dww's patches worked indeed. Thanks! The fact my search is failing is strange, but is as you say, another issue.

Greetz,
Gunther

Just marking this so we know that we need to be sure to deploy from views 6.x-2.5 when we next rev the version of views running on d.o. Sorry for the extra noise, merlinofchaos, but we're depending on this tag to keep things sane for d.o.

d.o is now running views 6.x-2.5, so this is now totally resolved.