Taxonomy term field does not work when using taxonomy term filter with "Is none of" [#1836138]

I have a view with Taxonomy: Term as a field. Works fine.

I add the filter Taxonomy: Term (The taxonomy term ID), set it to "Is none of" and select a few terms.
The filter works as expected by now my term field is not displaying.

If I use "Is one of" instead of "Is none of" it is ok.

Here are a few SQL queries to demonstrate what the problem is:

View without filter:

SELECT DISTINCT(node.nid) AS nid,
       term_data.name AS term_data_name,
       term_data.vid AS term_data_vid,
       term_data.tid AS term_data_tid,
       node.created AS node_created,
       node.title AS node_title,
       node_revisions.teaser AS node_revisions_teaser,
       node_revisions.format AS node_revisions_format
  FROM node node
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
WHERE (node.status = 1)
  AND (node.type in ('article'))
GROUP BY nid
ORDER BY node_created DESC

View with filter:

SELECT DISTINCT(node.nid) AS nid,
       term_data.name AS 
       term_data_name,
       term_data.vid AS term_data_vid,
       term_data.tid AS term_data_tid,
       node.created AS node_created,
       node.title AS node_title,
       node_revisions.teaser AS node_revisions_teaser,
       node_revisions.format AS node_revisions_format
  FROM node node
LEFT JOIN term_node term_node ON node.vid = term_node.vid AND (term_node.tid = 107 OR term_node.tid = 106 OR term_node.tid = 36 OR term_node.tid = 93 OR term_node.tid = 94 OR term_node.tid = 91 OR term_node.tid = 92)
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
WHERE (node.status = 1)
  AND (node.type in ('article'))
  AND (term_node.tid IS NULL)
GROUP BY nid
ORDER BY node_created DESC

How I think the query should look (this will give the correct output):

SELECT DISTINCT(node.nid) AS nid,
       term_data.name AS 
       term_data_name,
       term_data.vid AS term_data_vid,
       term_data.tid AS term_data_tid,
       node.created AS node_created,
       node.title AS node_title,
       node_revisions.teaser AS node_revisions_teaser,
       node_revisions.format AS node_revisions_format
  FROM node node
LEFT JOIN term_node term_node ON node.vid = term_node.vid AND (term_node.tid != 107 AND term_node.tid != 106 AND term_node.tid != 36 AND term_node.tid != 93 AND term_node.tid != 94 AND term_node.tid != 91 AND term_node.tid != 92)
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
WHERE (node.status = 1)
  AND (node.type in ('article'))
GROUP BY nid
ORDER BY node_created DESC

The change is in the term_node LEFT JOIN and in the WHERE clause.
I haven't looked far enough in to know why it is doing the SQL the way it does.

Comments

Comment #1

rob230 commented 10 September 2013 at 12:09

Version:

6.x-2.16

» 7.x-3.7

I am seeing the same thing with the "is not one of" filter. What I would expect the query to be:

For 1 value chosen:

LEFT JOIN field_data_field_my_field ON node.nid = field_data_field_my_field.entity_id
...
WHERE (field_data_field_my_field.field_my_field_value <> value)

For more than one value chosen:

LEFT JOIN field_data_field_my_field ON node.nid = field_data_field_my_field.entity_id
...
WHERE (field_data_field_my_field.field_my_field_value NOT IN (value1, value2))

This is basically the exact opposite of the "is one of" filter, so instead of "=" it has "<>" and instead of "IN" it has "NOT IN".

What it actually does:

LEFT JOIN field_data_field_my_field ON node.nid = field_data_field_my_field.entity_id
field_data_field_my_field.field_my_field_value = value
...
WHERE (field_data_field_my_field.field_my_field_value = NULL)

I understand what is intended by this. The LEFT JOIN should return all of the results but with that particular field value being NULL where it doesn't match, and then you can use a WHERE query to select those that have NULL. But that isn't what happens. When I run the query myself but without the WHERE part, the LEFT JOIN is only returning rows that match the condition.

Comment #2

rob230 commented 10 September 2013 at 15:23

I have managed to get it working by adding the following to the query:

(
    field_data_field_my_field.entity_type = 'node'
AND field_data_field_my_field.deleted = '0'
)

This is something that appeared on another field I was filtering by. The field in question in which the LEFT JOIN isn't behaving as expected appears on both nodes and comments, i.e. it says Appears in: node:my_node_type, comment:comment_node_my_node_type. I assume this is why it's breaking, as views is skipping the part of the query I've put above. It seems to be turning the LEFT JOIN into an INNER JOIN.

I can stop this from happening by removing part of the WHERE clause, specifically a part relating to the other field it's joining to afterwards. I think the mistake is that for a query like this:

SELECT FROM node
LEFT JOIN field1
LEFT JOIN field2
WHERE field2 = something

My nodes all have a value for field1 and field2, but since field1 can also be applied to comments, and because Views is skipping the part above, it is also selecting rows for a comment with the same entity ID as a node, and then it is joining to field2, which does not have a comment with that entity ID, so the WHERE clause is turning it into an INNER JOIN. Note that it all worked fine, until the rare case where a comment with field1 set has the same ID as a node with field1 set, which causes it to break.

Is there a way to force Views to specify that the field be on a node? I've tried adding Content: My field (field_my_field) and Field: My field (field_my_field). They both result in the same filter being added.