I've updated Views to the latest version today and noticed that "in none of" filter operator no longer works. Here is a simple example how to reproduce that.
There is a content type Basic page with boolean field "Enabled". I have 3 nodes of that content type with values of 1, 0 and NULL (node created before a field was added). Now I add a filter with operator "in none of" - Off (0). My expectation is to get 2 results - with 1 and NULL values. It used to work this way before. But currently it returns 1 result - NULL.
Here is the SQL query that I get:
SELECT node.title AS node_title, node.nid AS nid
FROM
{node} node
LEFT JOIN {field_data_field_enabled} field_data_field_enabled ON node.nid = field_data_field_enabled.entity_id
WHERE (( (node.type IN ('page')) AND (field_data_field_enabled.field_enabled_value IS NULL ) ))
LIMIT 10 OFFSET 0
My expectation is that the SQL should be similar to this:
SELECT node.title AS node_title, node.nid AS nid
FROM
{node} node
LEFT JOIN {field_data_field_enabled} field_data_field_enabled ON node.nid = field_data_field_enabled.entity_id AND field_data_field_enabled.field_enabled_value = '0'
WHERE (( (node.type IN ('page')) AND (field_data_field_enabled.field_enabled_value IS NULL ) ))
LIMIT 10 OFFSET 0
Comments
Comment #2
temkin commentedHere is the patch that fixes this problem. I've changed the check from
!emptywhich would fail when a value is 0, toisset. Seems to be working now.Comment #3
temkin commentedComment #4
othermachines commentedThanks for the heads up @temkin.
Specifying in the title that the problem arises when field value is Off/0.
Confirming that the patch in #2 fixes the problem.
Here is the commit from which the problem originates: http://cgit.drupalcode.org/views/commit/?id=09adda610e96b38b411b3f1953cf...
Comment #5
othermachines commentedSeems #1090432: Allow additional field-to-field conditions with the default views_join handler may have broke more than this (see comment #37). A reverting patch is provided in #1090432-30: Allow additional field-to-field conditions with the default views_join handler.
Comment #6
othermachines commentedRelated issue. (For some reason wasn't added the first time. Sorry for the noise.)
Comment #7
chegor commentedConfirming that this patch fixes the issue.
Comment #8
rocket777 commentedConfirming that this patch fixes the issue.
Comment #9
chrisryan commentedWe also ran into this problem and the patch fixes the issue.
Comment #10
temkin commentedJust checking if it's going to be committed or there is a different solution to this problem. It's affecting a few sites and I'd like to understand how to handle this better. Thanks.
Comment #11
othermachines commented@temkin - Good question! I'm wondering the same thing.
I'm pretty sure this issue (and others) were caused by #1090432: Allow additional field-to-field conditions with the default views_join handler. Have you tried the reverting patch from that thread?
I actually rolled back to 3.13 and have been holding out for the next release (I manually patched the security issue fixed in 3.14). I was pretty sure this would be bad enough to warrant a quick roll-out of 3.15, but it doesn't look like that's happening.
Comment #12
nugten commentedApplied patch and worked very well for us. Don't get why isn't it getting rolled out. I find it a very important fix.
Comment #13
othermachines commentedMarked as duplicate: #2795991: can't join 0 value
Comment #14
othermachines commentedMarked as duplicate: #2791353: JOIN 'extra' fails with zero value
These are starting to pile up. :-/
Comment #15
dawehner@othermachines
Kudos for doing that!
Comment #16
dawehnerThat bugfix sounds totally logical for me
Nice!