SQL Syntax warning and query failure

I'm seeing this problem too. However, the view doesn't work at all (and why would it if the 'WHERE' clause is missing?).

FWIW what I'm doing is a little complicated. Am building a sports fixtures database. There are 2 node references in a fixture, the home (side A below) and away (side B) sides. I'd like to build a view that shows all the fixtures for a team.

Using relationships I can easily show all the home games, or all the away games separately by using the team name as an argument and a relation ship to either 'home' or 'away' in the fixture. However, when I try and combine these the error occurs:

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 'LIMIT 0, 10' at line 16 query: SELECT node.nid AS nid, node.title AS node_title, node_data_field_court.field_court_value AS node_data_field_court_field_court_value, node.type AS node_type, node.vid AS node_vid, node_data_field_side_a.field_side_a_nid AS node_data_field_side_a_field_side_a_nid, node_data_field_side_b.field_side_b_nid AS node_data_field_side_b_field_side_b_nid, node_data_field_court.field_when_value AS node_data_field_court_field_when_value FROM node node LEFT JOIN content_type_fixture node_data_field_side_a ON node.vid = node_data_field_side_a.vid LEFT JOIN node node_node_data_field_side_a ON node_data_field_side_a.field_side_a_nid = node_node_data_field_side_a.nid LEFT JOIN content_type_fixture node_data_field_side_b ON node.vid = node_data_field_side_b.vid LEFT JOIN node node_node_data_field_side_b ON node_data_field_side_b.field_side_b_nid = node_node_data_field_side_b.nid LEFT JOIN content_type_fixture node_data_field_court ON node.vid = node_data_field_court.vid WHERE LIMIT 0, 10 in /home/raintonr/sites/drupal_multisites/sites/all/modules/views/includes/view.inc on line 731.

This is the SQL generated in views preview:

SELECT node.nid AS nid,
   node.title AS node_title,
   node_data_field_court.field_court_value AS node_data_field_court_field_court_value,
   node.type AS node_type,
   node.vid AS node_vid,
   node_data_field_side_a.field_side_a_nid AS node_data_field_side_a_field_side_a_nid,
   node_data_field_side_b.field_side_b_nid AS node_data_field_side_b_field_side_b_nid,
   node_data_field_court.field_when_value AS node_data_field_court_field_when_value
 FROM node node 
 LEFT JOIN content_type_fixture node_data_field_side_a ON node.vid = node_data_field_side_a.vid
 LEFT JOIN node node_node_data_field_side_a ON node_data_field_side_a.field_side_a_nid = node_node_data_field_side_a.nid
 LEFT JOIN content_type_fixture node_data_field_side_b ON node.vid = node_data_field_side_b.vid
 LEFT JOIN node node_node_data_field_side_b ON node_data_field_side_b.field_side_b_nid = node_node_data_field_side_b.nid
 LEFT JOIN content_type_fixture node_data_field_court ON node.vid = node_data_field_court.vid
 WHERE 
 

And here's the view exported:

$view = new view;
$view->name = 'team_fixtures';
$view->description = 'Team Fixtures';
$view->tag = 'teams';
$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(
  'field_side_a_nid' => array(
    'label' => 'Side A',
    'required' => 0,
    'delta' => -1,
    'id' => 'field_side_a_nid',
    'table' => 'node_data_field_side_a',
    'field' => 'field_side_a_nid',
    'relationship' => 'none',
  ),
  'field_side_b_nid' => array(
    'label' => 'Side B',
    'required' => 0,
    'delta' => -1,
    'id' => 'field_side_b_nid',
    'table' => 'node_data_field_side_b',
    'field' => 'field_side_b_nid',
    'relationship' => 'none',
  ),
));
$handler->override_option('fields', array(
  'title' => array(
    'label' => 'Title',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'field_court_value' => array(
    'label' => 'Court',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      '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_court_value',
    'table' => 'node_data_field_court',
    'field' => 'field_court_value',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'field_side_a_nid' => array(
    'label' => 'Side A',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      '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_side_a_nid',
    'table' => 'node_data_field_side_a',
    'field' => 'field_side_a_nid',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'field_side_b_nid' => array(
    'label' => 'Side B',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      '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_side_b_nid',
    'table' => 'node_data_field_side_b',
    'field' => 'field_side_b_nid',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'field_when_value' => array(
    'label' => 'When',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_to' => '',
      'group' => TRUE,
    ),
    'repeat' => array(
      'show_repeat_rule' => '',
    ),
    'fromto' => array(
      'fromto' => 'both',
    ),
    'exclude' => 0,
    'id' => 'field_when_value',
    'table' => 'node_data_field_when',
    'field' => 'field_when_value',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('arguments', array(
  'views_or_begin' => array(
    'default_action' => 'default',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'default_argument_fixed' => '',
    'id' => 'views_or_begin',
    'table' => 'node',
    'field' => 'views_or_begin',
    'relationship' => 'none',
  ),
  'field_side_a_nid' => array(
    'default_action' => 'ignore',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'break_phrase' => 0,
    'not' => 0,
    'id' => 'field_side_a_nid',
    'table' => 'node_data_field_side_a',
    'field' => 'field_side_a_nid',
    'validate_user_argument_type' => 'uid',
    'validate_user_roles' => array(
      '2' => 0,
    ),
    'relationship' => 'field_side_a_nid',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'fixture' => 0,
      'page' => 0,
      'profile' => 0,
      'story' => 0,
      'team' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_vocabulary' => array(
      '1' => 0,
    ),
    'validate_argument_type' => 'tid',
    'validate_argument_transform' => 0,
    'validate_user_restrict_roles' => 0,
    'validate_argument_php' => '',
  ),
  'views_or_next' => array(
    'default_action' => 'default',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'default_argument_fixed' => '',
    'share_arguments' => 1,
    'id' => 'views_or_next',
    'table' => 'node',
    'field' => 'views_or_next',
    'relationship' => 'none',
  ),
  'field_side_b_nid' => array(
    'default_action' => 'ignore',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'break_phrase' => 0,
    'not' => 0,
    'id' => 'field_side_b_nid',
    'table' => 'node_data_field_side_b',
    'field' => 'field_side_b_nid',
    'validate_user_argument_type' => 'uid',
    'validate_user_roles' => array(
      '2' => 0,
    ),
    'relationship' => 'field_side_b_nid',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'fixture' => 0,
      'page' => 0,
      'profile' => 0,
      'story' => 0,
      'team' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_vocabulary' => array(
      '1' => 0,
    ),
    'validate_argument_type' => 'tid',
    'validate_argument_transform' => 0,
    'validate_user_restrict_roles' => 0,
    'validate_argument_php' => '',
  ),
  'views_or_end' => array(
    'default_action' => 'default',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'default_argument_fixed' => '',
    'id' => 'views_or_end',
    'table' => 'node',
    'field' => 'views_or_end',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
  'grouping' => '',
  'override' => 1,
  'sticky' => 0,
  'order' => 'asc',
  'columns' => array(
    'title' => 'title',
    'field_court_value' => 'field_court_value',
    'field_side_a_nid' => 'field_side_a_nid',
    'field_side_b_nid' => 'field_side_b_nid',
    'field_when_value' => 'field_when_value',
  ),
  'info' => array(
    'title' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'field_court_value' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'field_side_a_nid' => array(
      'separator' => '',
    ),
    'field_side_b_nid' => array(
      'separator' => '',
    ),
    'field_when_value' => array(
      'sortable' => 0,
      'separator' => '',
    ),
  ),
  'default' => '-1',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'teams/%/fixtures');
$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 got the same result, but the problems resolved nicely when I unchecked the option "show all" on argument not present. My guess is that Views gets confused if one of the arguments says "hide view if no argument is present" while the other says "show everything if no argument is present".

Please confirm if the same thing helps for you.

//Johan Falk, [NodeOne](http://nodeone.se), [Sweden](http://drupalsverige.se)

I'm trying to set up a similar View, with home and away team. If you got yours to work correctly, can you describe exactly how you set up the View to make it work? I'm frustrated so far.

Re: #2, the same workaround resolves the error for me, as well. Thanks for the information.

@Itangalo, When you unchecked the "show all" option, what other option did you select instead?

@drupup and @farjam60: Sorry for responding so late. I am not 100 percent sure after all this time, but a qualified guess is that I set all arguments to "hide view if no argument is present".

This issue may be related to #396094: exposed filters generate error. Could someone test the patch I posted there:
http://drupal.org/node/396094#comment-3742482

The related issue is about exposed filters instead of arguments, and I've only tested the filter-related fix, but included in the patch a similar change to the argument handler.

If that patch fixes this issue, then let's close this as a duplicate.

As i see this problem consists of two parts:
1) Views behaviour when it stops arguments processing if argument is invalid.
2) Views_or behaviour when it removes all "where" conditions at "begin alternatives" phase and restores them at "end alternatives".
So, it Views stops arguments processing (look at "view::_build_arguments") - we get empty "where"....
Here is my rough and dirty patch to deal with such situation. Works fine for me - my views now support "show all" options as no_argument or invalid_argument rollback.

Patch #6 over at #396094: exposed filters generate error fixed errors and looks cleaner than one at #8 here, but I am not sure if both of them are doing the same, so Im not gona make this duplicate.

I tried #6 from #396094: exposed filters generate error and it didn't help me. Actually it doesn't deal with "show all" rollback of bad argument. The key is that end_alternatives query method is not executed in this case and it has no sense to make any changes there... At begin_alternatives stage query must be ready to execute in case of future stopping next arguments processing by view::_build_arguments
I'll be very happy to see cleaner solution than mine - ready for tests.

#8 made my view display nodes instead of fields, smallprint is "Works fine for me" ;)

Hm. It's strange - #8 doesn't touch any display options - only "where" part of query. Can you post your view export (maybe to PM, not here)?

yes, it was my misconfiguration, sorry