I hope I followed the correct procedure since I searched and couldn't find a bug report similar to what I have here. I get the following error when I tried to create a taxonomy filter in one of the views I created

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 ')' at line 1 query: SELECT * FROM term_data td WHERE td.tid IN () in /home/national/public_html/sites/all/modules/views/modules/taxonomy/views_handler_filter_term_node_tid.inc on line 295.

Here is the export:

$view = new view;
$view->name = 'Em_video';
$view->description = 'Embedded videos from youTube etc';
$view->tag = 'videos';
$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' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
  'field_em_video_embed' => 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,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 0,
    'label_type' => 'none',
    'format' => 'video_thumbnail',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_em_video_embed',
    'table' => 'node_data_field_em_video',
    'field' => 'field_em_video_embed',
    'relationship' => 'none',
  ),
  'tid' => array(
    'label' => 'Gallery',
    '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,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'type' => 'separator',
    'separator' => ', ',
    'link_to_taxonomy' => 1,
    'limit' => 1,
    'vids' => array(
      '11' => 11,
      '2' => 0,
      '7' => 0,
      '6' => 0,
      '10' => 0,
      '8' => 0,
      '4' => 0,
      '1' => 0,
      '3' => 0,
      '9' => 0,
      '5' => 0,
    ),
    'exclude' => 0,
    'id' => 'tid',
    'table' => 'term_node',
    'field' => 'tid',
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'created' => array(
    'order' => 'ASC',
    'granularity' => 'minute',
    'id' => 'created',
    'table' => 'node',
    'field' => 'created',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'em_video' => 'em_video',
      'video' => 'video',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Override',
    ),
  ),
  'tid' => array(
    'operator' => 'or',
    'value' => '',
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'tid_op',
      'identifier' => 'tid',
      'label' => 'Search video types',
      'optional' => 1,
      'single' => 1,
      'remember' => 0,
    ),
    'type' => 'textfield',
    'limit' => TRUE,
    'vid' => '11',
    'id' => 'tid',
    'table' => 'term_node',
    'field' => 'tid',
    'hierarchy' => 0,
    'configure_hs' => '<a href="/admin/build/views/hs_config/Em_video/default/tid">Configure Hierarchical Select</a>',
    'relationship' => 'none',
    'reduce_duplicates' => 0,
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('use_ajax', TRUE);
$handler->override_option('items_per_page', 100);
$handler->override_option('use_pager', '1');
$handler->override_option('style_plugin', 'grid');
$handler->override_option('style_options', array(
  'grouping' => '',
  'columns' => '6',
  'alignment' => 'horizontal',
  'fill_single_line' => 1,
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'videos');
$handler->override_option('menu', array(
  'type' => 'normal',
  'title' => 'Videos',
  'description' => 'Video gallery',
  'weight' => '0',
  'name' => 'primary-links',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));

Here is the query:

SELECT node.nid AS nid,
   node.title AS node_title,
   node_data_field_em_video.field_em_video_embed AS node_data_field_em_video_field_em_video_embed,
   node_data_field_em_video.field_em_video_value AS node_data_field_em_video_field_em_video_value,
   node_data_field_em_video.field_em_video_provider AS node_data_field_em_video_field_em_video_provider,
   node_data_field_em_video.field_em_video_data AS node_data_field_em_video_field_em_video_data,
   node_data_field_em_video.field_em_video_version AS node_data_field_em_video_field_em_video_version,
   node_data_field_em_video.field_em_video_duration AS node_data_field_em_video_field_em_video_duration,
   node.type AS node_type,
   node.vid AS node_vid,
   DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 7200 SECOND), '%Y%m%d%H%i') AS node_created_minute
 FROM node node 
 LEFT JOIN content_type_em_video node_data_field_em_video ON node.vid = node_data_field_em_video.vid
 WHERE node.type in ('em_video', 'video')
   ORDER BY node_created_minute ASC
CommentFileSizeAuthor
#4 views-SQL-error-921564.patch618 byteswjaspers
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

dawehner’s picture

Awesome report!

Could you have a look at #721774: HS Taxonomy Views + Views 3 + taxonomy term filter: You have an error in your SQL syntax whether this seems to be the same issue?

vip_sa’s picture

I have views version 6x2.11 and that is fr views 3 but I disabled the module in any case and it seems to fix it. Thank you very much for the feedback

dawehner’s picture

So this is fixed?

You can help the maintainer of modules and set the status to fixed if any issue is "fixed".

wjaspers’s picture

Status: Active » Needs review
FileSize
618 bytes

Hierarchical select didn't trigger this issue for me.
I patched my views handler, and the SQL error went away.
Give this a shot.
It may need to be tested.

dawehner’s picture

I don't understand the patch. Why should tid be a string here?

wjaspers’s picture

It fixed this bug in my instance.
It seems as if text is getting into TID from another source at this point.

MySQL functions the same way with both quoted and unquoted numbers.
In this way, if the source value is wrong, the query still runs -- without causing an SQL error.

merlinofchaos’s picture

MySQL does but pgsql does NOT. This would break under pgsql.

dawehner’s picture

Status: Needs review » Needs work

So

Additonal i still think this belongs into Hierarchical Select.

brunorios1’s picture

subscribing...

MustangGB’s picture

Status: Needs work » Closed (won't fix)