Hi.

English is not my mother language so I apologize if my message is slightly confused.

I'm using views in version 7.x-3.7 and Drupal in version 7.23.

I have an integer field of the type "list of value".
Basically what shows bellow:
1|One
2|Two
3|Three

I'm trying to use this field to filter and sort one view.

The filter should be: field <> 1 (different of one).
Of course there is not the option "<>" so I tried to use "none of" and selected the line of the value "1" on the list.

Using this option my view is bugged. The filter criteria and the sort is broken.
See below.

Exported view:
$view = new view();
$view->name = 'noticias_em_destaque';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Notícias em destaque';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Notícias em destaque';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Restaurar';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Campo: Conteúdo: Tipo */
$handler->display->display_options['fields']['type']['id'] = 'type';
$handler->display->display_options['fields']['type']['table'] = 'node';
$handler->display->display_options['fields']['type']['field'] = 'type';
$handler->display->display_options['fields']['type']['label'] = '';
$handler->display->display_options['fields']['type']['exclude'] = TRUE;
$handler->display->display_options['fields']['type']['element_label_colon'] = FALSE;
/* Campo: Conteúdo: Título */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['exclude'] = TRUE;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
$handler->display->display_options['fields']['title']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['title']['link_to_node'] = FALSE;
/* Campo: Path do conteúdo (link interno) */
$handler->display->display_options['fields']['path']['id'] = 'path';
$handler->display->display_options['fields']['path']['table'] = 'node';
$handler->display->display_options['fields']['path']['field'] = 'path';
$handler->display->display_options['fields']['path']['ui_name'] = 'Path do conteúdo (link interno)';
$handler->display->display_options['fields']['path']['label'] = '';
$handler->display->display_options['fields']['path']['exclude'] = TRUE;
$handler->display->display_options['fields']['path']['element_label_colon'] = FALSE;
/* Campo: Link externo */
$handler->display->display_options['fields']['field_notext_link']['id'] = 'field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['table'] = 'field_data_field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['field'] = 'field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['ui_name'] = 'Link externo';
$handler->display->display_options['fields']['field_notext_link']['label'] = '';
$handler->display->display_options['fields']['field_notext_link']['exclude'] = TRUE;
$handler->display->display_options['fields']['field_notext_link']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_notext_link']['click_sort_column'] = 'url';
$handler->display->display_options['fields']['field_notext_link']['type'] = 'link_plain';
/* Campo: Título e link - notícia interna */
$handler->display->display_options['fields']['views_conditional']['id'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['table'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['field'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['ui_name'] = 'Título e link - notícia interna';
$handler->display->display_options['fields']['views_conditional']['label'] = '';
$handler->display->display_options['fields']['views_conditional']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['views_conditional']['if'] = 'type';
$handler->display->display_options['fields']['views_conditional']['condition'] = '1';
$handler->display->display_options['fields']['views_conditional']['equalto'] = 'Notícias';
$handler->display->display_options['fields']['views_conditional']['then'] = '[title]';
/* Campo: Título e link - notícia externa */
$handler->display->display_options['fields']['views_conditional_1']['id'] = 'views_conditional_1';
$handler->display->display_options['fields']['views_conditional_1']['table'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional_1']['field'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional_1']['ui_name'] = 'Título e link - notícia externa';
$handler->display->display_options['fields']['views_conditional_1']['label'] = '';
$handler->display->display_options['fields']['views_conditional_1']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['views_conditional_1']['if'] = 'type';
$handler->display->display_options['fields']['views_conditional_1']['condition'] = '2';
$handler->display->display_options['fields']['views_conditional_1']['equalto'] = 'Notícias';
$handler->display->display_options['fields']['views_conditional_1']['then'] = '[title]';
/* Campo: Conteúdo: Posição da notícia */
$handler->display->display_options['fields']['field_noticias_posicao']['id'] = 'field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['field'] = 'field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['label'] = '';
$handler->display->display_options['fields']['field_noticias_posicao']['element_label_colon'] = FALSE;
/* Campo: Conteúdo: Data */
$handler->display->display_options['fields']['field_noticias_data']['id'] = 'field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['table'] = 'field_data_field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['field'] = 'field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['label'] = '';
$handler->display->display_options['fields']['field_noticias_data']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_noticias_data']['settings'] = array(
'format_type' => 'long',
'fromto' => 'both',
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'show_repeat_rule' => 'show',
);
/* Campo: Conteúdo: Updated date */
$handler->display->display_options['fields']['changed']['id'] = 'changed';
$handler->display->display_options['fields']['changed']['table'] = 'node';
$handler->display->display_options['fields']['changed']['field'] = 'changed';
$handler->display->display_options['fields']['changed']['date_format'] = 'long';
/* Sort criterion: Conteúdo: Posição da notícia (field_noticias_posicao) */
$handler->display->display_options['sorts']['field_noticias_posicao_value']['id'] = 'field_noticias_posicao_value';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['field'] = 'field_noticias_posicao_value';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['expose']['label'] = 'Posição da notícia (field_noticias_posicao)';
/* Sort criterion: Conteúdo: Data (field_noticias_data) */
$handler->display->display_options['sorts']['field_noticias_data_value']['id'] = 'field_noticias_data_value';
$handler->display->display_options['sorts']['field_noticias_data_value']['table'] = 'field_data_field_noticias_data';
$handler->display->display_options['sorts']['field_noticias_data_value']['field'] = 'field_noticias_data_value';
$handler->display->display_options['sorts']['field_noticias_data_value']['order'] = 'DESC';
/* Sort criterion: Conteúdo: Updated date */
$handler->display->display_options['sorts']['changed']['id'] = 'changed';
$handler->display->display_options['sorts']['changed']['table'] = 'node';
$handler->display->display_options['sorts']['changed']['field'] = 'changed';
$handler->display->display_options['sorts']['changed']['order'] = 'DESC';
/* Filter criterion: Conteúdo: Publicado */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Conteúdo: Tipo */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
'noticias' => 'noticias',
'noticias_externas' => 'noticias_externas',
);
/* Filter criterion: Remove notícias que estão na galeira */
$handler->display->display_options['filters']['field_noticias_posicao_value']['id'] = 'field_noticias_posicao_value';
$handler->display->display_options['filters']['field_noticias_posicao_value']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['filters']['field_noticias_posicao_value']['field'] = 'field_noticias_posicao_value';
$handler->display->display_options['filters']['field_noticias_posicao_value']['ui_name'] = 'Remove notícias que estão na galeira';
$handler->display->display_options['filters']['field_noticias_posicao_value']['operator'] = 'not';
$handler->display->display_options['filters']['field_noticias_posicao_value']['value'] = array(
1 => '1',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'noticias-em-destaque';
$translatables['noticias_em_destaque'] = array(
t('Master'),
t('Notícias em destaque'),
t('more'),
t('Apply'),
t('Restaurar'),
t('Sort by'),
t('Asc'),
t('Desc'),
t('Updated date'),
t('Posição da notícia (field_noticias_posicao)'),
t('Page'),
);

SQL generated:
SELECT node.type AS node_type, node.title AS node_title, node.nid AS nid, node.changed AS node_changed, field_data_field_noticias_posicao.field_noticias_posicao_value AS field_data_field_noticias_posicao_field_noticias_posicao_val, field_data_field_noticias_data.field_noticias_data_value AS field_data_field_noticias_data_field_noticias_data_value, 'node' AS field_data_field_notext_link_node_entity_type, 'node' AS field_data_field_noticias_posicao_node_entity_type, 'node' AS field_data_field_noticias_data_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_noticias_posicao} field_data_field_noticias_posicao ON node.nid = field_data_field_noticias_posicao.entity_id AND field_data_field_noticias_posicao.field_noticias_posicao_value = '1'
LEFT JOIN {field_data_field_noticias_data} field_data_field_noticias_data ON node.nid = field_data_field_noticias_data.entity_id AND (field_data_field_noticias_data.entity_type = 'node' AND field_data_field_noticias_data.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('noticias', 'noticias_externas')) AND (field_data_field_noticias_posicao.field_noticias_posicao_value IS NULL ) ))
ORDER BY field_data_field_noticias_posicao_field_noticias_posicao_val ASC, field_data_field_noticias_data_field_noticias_data_value DESC, node_changed DESC

The first left join is completely wrong: "field_data_field_noticias_posicao.field_noticias_posicao_value = '1'" should not be generated.

I believe this is a bug.

When I change the filter criteria for "one of" and select the other two values (2 and 3) the same view works perfectly.
See below.

Exported view:
$view = new view();
$view->name = 'noticias_em_destaque';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Notícias em destaque';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Notícias em destaque';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Restaurar';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Campo: Conteúdo: Tipo */
$handler->display->display_options['fields']['type']['id'] = 'type';
$handler->display->display_options['fields']['type']['table'] = 'node';
$handler->display->display_options['fields']['type']['field'] = 'type';
$handler->display->display_options['fields']['type']['label'] = '';
$handler->display->display_options['fields']['type']['exclude'] = TRUE;
$handler->display->display_options['fields']['type']['element_label_colon'] = FALSE;
/* Campo: Conteúdo: Título */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['exclude'] = TRUE;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
$handler->display->display_options['fields']['title']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['title']['link_to_node'] = FALSE;
/* Campo: Path do conteúdo (link interno) */
$handler->display->display_options['fields']['path']['id'] = 'path';
$handler->display->display_options['fields']['path']['table'] = 'node';
$handler->display->display_options['fields']['path']['field'] = 'path';
$handler->display->display_options['fields']['path']['ui_name'] = 'Path do conteúdo (link interno)';
$handler->display->display_options['fields']['path']['label'] = '';
$handler->display->display_options['fields']['path']['exclude'] = TRUE;
$handler->display->display_options['fields']['path']['element_label_colon'] = FALSE;
/* Campo: Link externo */
$handler->display->display_options['fields']['field_notext_link']['id'] = 'field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['table'] = 'field_data_field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['field'] = 'field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['ui_name'] = 'Link externo';
$handler->display->display_options['fields']['field_notext_link']['label'] = '';
$handler->display->display_options['fields']['field_notext_link']['exclude'] = TRUE;
$handler->display->display_options['fields']['field_notext_link']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_notext_link']['click_sort_column'] = 'url';
$handler->display->display_options['fields']['field_notext_link']['type'] = 'link_plain';
/* Campo: Título e link - notícia interna */
$handler->display->display_options['fields']['views_conditional']['id'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['table'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['field'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['ui_name'] = 'Título e link - notícia interna';
$handler->display->display_options['fields']['views_conditional']['label'] = '';
$handler->display->display_options['fields']['views_conditional']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['views_conditional']['if'] = 'type';
$handler->display->display_options['fields']['views_conditional']['condition'] = '1';
$handler->display->display_options['fields']['views_conditional']['equalto'] = 'Notícias';
$handler->display->display_options['fields']['views_conditional']['then'] = '[title]';
/* Campo: Título e link - notícia externa */
$handler->display->display_options['fields']['views_conditional_1']['id'] = 'views_conditional_1';
$handler->display->display_options['fields']['views_conditional_1']['table'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional_1']['field'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional_1']['ui_name'] = 'Título e link - notícia externa';
$handler->display->display_options['fields']['views_conditional_1']['label'] = '';
$handler->display->display_options['fields']['views_conditional_1']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['views_conditional_1']['if'] = 'type';
$handler->display->display_options['fields']['views_conditional_1']['condition'] = '2';
$handler->display->display_options['fields']['views_conditional_1']['equalto'] = 'Notícias';
$handler->display->display_options['fields']['views_conditional_1']['then'] = '[title]';
/* Campo: Conteúdo: Posição da notícia */
$handler->display->display_options['fields']['field_noticias_posicao']['id'] = 'field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['field'] = 'field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['label'] = '';
$handler->display->display_options['fields']['field_noticias_posicao']['element_label_colon'] = FALSE;
/* Campo: Conteúdo: Data */
$handler->display->display_options['fields']['field_noticias_data']['id'] = 'field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['table'] = 'field_data_field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['field'] = 'field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['label'] = '';
$handler->display->display_options['fields']['field_noticias_data']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_noticias_data']['settings'] = array(
'format_type' => 'long',
'fromto' => 'both',
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'show_repeat_rule' => 'show',
);
/* Campo: Conteúdo: Updated date */
$handler->display->display_options['fields']['changed']['id'] = 'changed';
$handler->display->display_options['fields']['changed']['table'] = 'node';
$handler->display->display_options['fields']['changed']['field'] = 'changed';
$handler->display->display_options['fields']['changed']['date_format'] = 'long';
/* Sort criterion: Conteúdo: Posição da notícia (field_noticias_posicao) */
$handler->display->display_options['sorts']['field_noticias_posicao_value']['id'] = 'field_noticias_posicao_value';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['field'] = 'field_noticias_posicao_value';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['expose']['label'] = 'Posição da notícia (field_noticias_posicao)';
/* Sort criterion: Conteúdo: Data (field_noticias_data) */
$handler->display->display_options['sorts']['field_noticias_data_value']['id'] = 'field_noticias_data_value';
$handler->display->display_options['sorts']['field_noticias_data_value']['table'] = 'field_data_field_noticias_data';
$handler->display->display_options['sorts']['field_noticias_data_value']['field'] = 'field_noticias_data_value';
$handler->display->display_options['sorts']['field_noticias_data_value']['order'] = 'DESC';
/* Sort criterion: Conteúdo: Updated date */
$handler->display->display_options['sorts']['changed']['id'] = 'changed';
$handler->display->display_options['sorts']['changed']['table'] = 'node';
$handler->display->display_options['sorts']['changed']['field'] = 'changed';
$handler->display->display_options['sorts']['changed']['order'] = 'DESC';
/* Filter criterion: Conteúdo: Publicado */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Conteúdo: Tipo */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
'noticias' => 'noticias',
'noticias_externas' => 'noticias_externas',
);
/* Filter criterion: Remove notícias que estão na galeira */
$handler->display->display_options['filters']['field_noticias_posicao_value']['id'] = 'field_noticias_posicao_value';
$handler->display->display_options['filters']['field_noticias_posicao_value']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['filters']['field_noticias_posicao_value']['field'] = 'field_noticias_posicao_value';
$handler->display->display_options['filters']['field_noticias_posicao_value']['ui_name'] = 'Remove notícias que estão na galeira';
$handler->display->display_options['filters']['field_noticias_posicao_value']['value'] = array(
2 => '2',
3 => '3',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'noticias-em-destaque';
$translatables['noticias_em_destaque'] = array(
t('Master'),
t('Notícias em destaque'),
t('more'),
t('Apply'),
t('Restaurar'),
t('Sort by'),
t('Asc'),
t('Desc'),
t('Updated date'),
t('Posição da notícia (field_noticias_posicao)'),
t('Page'),
);

SQL generated:
SELECT node.type AS node_type, node.title AS node_title, node.nid AS nid, node.changed AS node_changed, field_data_field_noticias_posicao.field_noticias_posicao_value AS field_data_field_noticias_posicao_field_noticias_posicao_val, field_data_field_noticias_data.field_noticias_data_value AS field_data_field_noticias_data_field_noticias_data_value, 'node' AS field_data_field_notext_link_node_entity_type, 'node' AS field_data_field_noticias_posicao_node_entity_type, 'node' AS field_data_field_noticias_data_node_entity_type
FROM
{node} node
INNER JOIN {field_data_field_noticias_posicao} field_data_field_noticias_posicao ON node.nid = field_data_field_noticias_posicao.entity_id AND (field_data_field_noticias_posicao.entity_type = 'node' AND field_data_field_noticias_posicao.deleted = '0')
LEFT JOIN {field_data_field_noticias_data} field_data_field_noticias_data ON node.nid = field_data_field_noticias_data.entity_id AND (field_data_field_noticias_data.entity_type = 'node' AND field_data_field_noticias_data.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('noticias', 'noticias_externas')) AND (field_data_field_noticias_posicao.field_noticias_posicao_value IN ('2', '3')) ))
ORDER BY field_data_field_noticias_posicao_field_noticias_posicao_val ASC, field_data_field_noticias_data_field_noticias_data_value DESC, node_changed DESC

Now if we compare only the filter and sort portion of the sql:

--- using "is none of" (1)
LEFT JOIN {field_data_field_noticias_posicao} field_data_field_noticias_posicao ON node.nid = field_data_field_noticias_posicao.entity_id AND field_data_field_noticias_posicao.field_noticias_posicao_value = '1'
LEFT JOIN {field_data_field_noticias_data} field_data_field_noticias_data ON node.nid = field_data_field_noticias_data.entity_id AND (field_data_field_noticias_data.entity_type = 'node' AND field_data_field_noticias_data.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('noticias', 'noticias_externas')) AND (field_data_field_noticias_posicao.field_noticias_posicao_value IS NULL ) ))
ORDER BY field_data_field_noticias_posicao_field_noticias_posicao_val ASC, field_data_field_noticias_data_field_noticias_data_value DESC, node_changed DESC

---> using "is one of" (2,3)
INNER JOIN {field_data_field_noticias_posicao} field_data_field_noticias_posicao ON node.nid = field_data_field_noticias_posicao.entity_id AND (field_data_field_noticias_posicao.entity_type = 'node' AND field_data_field_noticias_posicao.deleted = '0')
LEFT JOIN {field_data_field_noticias_data} field_data_field_noticias_data ON node.nid = field_data_field_noticias_data.entity_id AND (field_data_field_noticias_data.entity_type = 'node' AND field_data_field_noticias_data.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('noticias', 'noticias_externas')) AND (field_data_field_noticias_posicao.field_noticias_posicao_value IN ('2', '3')) ))
ORDER BY field_data_field_noticias_posicao_field_noticias_posicao_val ASC, field_data_field_noticias_data_field_noticias_data_value DESC, node_changed DESC

The "order by "is the same so the problem resides on the filter!

I believe the bug is at the left join for table "noticias_posicao" . The usage of a left join is probrably correct because "none of" should try to get null/empty values. But the condition on that left join is wrong since the "equal" operator should not be used here!

Perhaps the condition for the field "noticias_posicao" should be removed from the left join (otherwise the filter will not work) and moved to "where clausule". In this case the correct operator could be "<>" or "not in (list of value)". For this option of filtering I dont know how to make null/empty values valid records... perhaps using mysql/postgresql functions to convert null/empty to a "true" condition? (for example the value zero would produce true).

If I can detail or help any further to correct this bug I will be happy to help.

Comments

gilsbert’s picture

Hi.

Recently I got the very same situation in a different project: a list of integer values necessary for filter and sort criterias.
I'm using postgresql database and this issue might be restricted to it.

Is someone interested/capable to help me fix this issue?
I have no idea where to look in views's module to write a patch but I can point what should be done.

Generated SQL below!

SELECT node.nid AS nid, node.title AS node_title, field_data_field_parceria_peso.field_parceria_peso_value AS field_data_field_parceria_peso_field_parceria_peso_value, 'node' AS field_data_field_parceria_link_node_entity_type, 'node' AS field_data_field_parceria_logotipo_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_parceria_peso} field_data_field_parceria_peso ON node.nid = field_data_field_parceria_peso.entity_id AND field_data_field_parceria_peso.field_parceria_peso_value = '99'
WHERE (( (node.status = '1') AND (node.type IN ('parceria')) AND (field_data_field_parceria_peso.field_parceria_peso_value IS NULL ) ))
ORDER BY field_data_field_parceria_peso_field_parceria_peso_value ASC, node_title ASC
LIMIT 10 OFFSET 0

The bug is at two places:

1) LEFT JOIN {field_data_field_parceria_peso} field_data_field_parceria_peso ON node.nid = field_data_field_parceria_peso.entity_id AND field_data_field_parceria_peso.field_parceria_peso_value = '99'

The "none of" value should not be used here because this will make all valid records (everything different of 99) return "null" as the value of this field!

2) WHERE (( (node.status = '1') AND (node.type IN ('parceria')) AND (field_data_field_parceria_peso.field_parceria_peso_value IS NULL ) ))

The field is compared with null but this is a result of the previous bug!

In another words: the "none of" operation is working but the field became unavailable for others parts of the SQL (ordery by for example).

The generated SQL should be this one:

SELECT node.nid AS nid, node.title AS node_title, field_data_field_parceria_peso.field_parceria_peso_value AS field_data_field_parceria_peso_field_parceria_peso_value, 'node' AS field_data_field_parceria_link_node_entity_type, 'node' AS field_data_field_parceria_logotipo_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_parceria_peso} field_data_field_parceria_peso ON node.nid = field_data_field_parceria_peso.entity_id
WHERE (( (node.status = '1') AND (node.type IN ('parceria')) AND field_data_field_parceria_peso.field_parceria_peso_value <> '99' ))
ORDER BY field_data_field_parceria_peso_field_parceria_peso_value ASC, node_title ASC
LIMIT 10 OFFSET 0

In summary: the "none of" stops to act in the "left join" and is used only in the "where".

If more than one value is selected for "none of" the operator must be changed from "<>" to "not in ( value1, value2,...,valuen )" or a sequence of "<> with and".

I don't know if the new approach would bring others concerns but I'm avaliable to help improve it.

Regards,
Gilsberty

mattew’s picture

I found a fix, I'll submit a patch to see if tests pass.

mattew’s picture

Status: Active » Needs review
StatusFileSize
new1.37 KB

Status: Needs review » Needs work

The last submitted patch, 4: filter-criterion-is-none-of-wrong-query-2069269-4-D7.patch, failed testing.

mattew’s picture

Status: Needs work » Needs review
StatusFileSize
new1.52 KB
mattew’s picture

OK, the test works, but the query is still wrong...
I have a problem with the LEFT JOIN, it should not being used when dealing with a "Is none of" (not) and a OR condition...
LEFT JOIN is build using "AND" condition instead of OR, despite the fact that
$join->extra_type = 'OR';
is provided before building the join. See /includes/handlers.inc, see line 967 and more:
Without applying the patch:

        foreach ($this->handler->value as $value) {
          $join->extra[] = array(
            'field' => $this->handler->real_field,
            'value' => $value,
            'numeric' => !empty($this->handler->definition['numeric']),
          );

With the patch :

        foreach ($this->handler->value as $value) {
          $join->extra[] = array(
            'field' => $this->handler->real_field,
            'operator' => '!=',
            'value' => $value,
            'numeric' => !empty($this->handler->definition['numeric']),
          );
        }

If I don't want any fields in the LEFT JOIN, I have to empty the value instead of providing the operator:

$this->handler->value = FALSE;
        foreach ($this->handler->value as $value) {
          $join->extra[] = array(
            'field' => $this->handler->real_field,
            'value' => $value,
            'numeric' => !empty($this->handler->definition['numeric']),
          );
        }

What the point of this foreach loop ? In case of a "not", why should we build a LEFT JOIN with conditions, as the filters are provided in the WHERE clause...

I don't understand, a Views guru should have a look at this...

mattew’s picture

Status: Needs review » Needs work
mustanggb’s picture

Title: Filter criteria for an integer field with a list of value is not working for the option "none of" » "Is none of" doesn't play nicely
Version: 7.x-3.7 » 7.x-3.x-dev
Status: Needs work » Needs review
StatusFileSize
new1.19 KB

An example use-case with OR'd filter groups:

Field A is none of 1 AND
Field B is one of 2, 3
OR
Field C is equal to 0 AND
Field B is one of 4, 5

Expected sudo-SQL:

JOIN A ON nid
JOIN B ON nid
JOIN C ON nid
WHERE
(
  (A IS NULL OR A <> 1) AND
  B IN (2, 3)
)
OR
(
  C = 0 AND
  B IN (4, 5)
)

Observed sudo-SQL:

JOIN A ON nid AND A = 1
JOIN B ON nid
JOIN C ON nid
WHERE
(
  A IS NULL AND
  B IN (2, 3)
)
OR
(
  C = 0 AND
  B IN (4, 5)
)

The extra join condition is prematurely filtering out results that should be available to the second OR filter group.

I'm testing out the attached patch.

EDIT: Added null check back in.

mustanggb’s picture

StatusFileSize
new1.84 KB

Turn out the null condition is needed, but should be OR'd rather than AND'd.

chris matthews’s picture

Status: Needs review » Needs work
Issue tags: +Needs reroll

The 2 year old patch in #10 to handlers.inc does not apply to the latest views 7.x-3.x-dev and if still relevant needs to be rerolled.

Checking patch includes/handlers.inc...
Hunk #1 succeeded at 1038 (offset 71 lines).
error: while searching for:
    // add_condition determines whether a single expression is enough(FALSE) or the
    // conditions should be added via an db_or()/db_and() (TRUE).
    $add_condition = TRUE;
    if ($operator == 'not') {
      $value = NULL;
      $operator = 'IS NULL';
      $add_condition = FALSE;
    }
    elseif ($operator == 'or' && empty($options['reduce_duplicates'])) {
      if (count($value) > 1) {

error: patch failed: includes/handlers.inc:1009
error: includes/handlers.inc: patch does not apply
andrew answer’s picture

Issue tags: -Needs reroll

Because of much changes in Views I cannot reroll patch and recommend to rewrite it, if problem still exist.