Joins no longer have conditions that have integer zero as the value

I've just updated Views from 7.3x-3.11 to 7.x-3.14 and started to notice some subtle errors in some complicated views.

I've tracked this down to a change in handlers.inc -> views_join -> build_join().

The code on line 1591 tests if there is a value with !empty() and of course empty returns true for integer zero thus failing the test. This means all field tables joined to a node view will no longer have the deleted = 0 condition added.

Also any filters that are set to not equal to 0 will fail to work due to the fact that they use the join to do the filtering by adding a condition for "field_value" = 0 and then adding "field_value" IS NULL to the where statements.

Changing the test to isset() fixes this bug.

Here's an example of the SQL generated from a view with the !empty() test:

SELECT node.title AS node_title, node.nid AS nid, node.changed AS node_changed, field_data_field_sa_order.field_sa_order_value AS field_data_field_sa_order_field_sa_order_value, 'node' AS field_data_field_sa_introduction_node_entity_type
FROM 
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
LEFT JOIN {field_data_field_sa_visible} field_data_field_sa_visible ON node.nid = field_data_field_sa_visible.entity_id AND field_data_field_sa_visible.entity_type = 'node'
INNER JOIN {field_data_field_sa_archive} field_data_field_sa_archive ON node.nid = field_data_field_sa_archive.entity_id AND field_data_field_sa_archive.entity_type = 'node'
INNER JOIN {field_data_field_sa_live} field_data_field_sa_live ON node.nid = field_data_field_sa_live.entity_id AND field_data_field_sa_live.entity_type = 'node'
LEFT JOIN {field_data_field_sa_archive} field_data_field_sa_archive2 ON node.nid = field_data_field_sa_archive2.entity_id AND field_data_field_sa_archive2.field_sa_archive_value = '1'
LEFT JOIN {field_data_field_sa_live} field_data_field_sa_live2 ON node.nid = field_data_field_sa_live2.entity_id
INNER JOIN {field_data_field_sa_display} field_data_field_sa_display ON node.nid = field_data_field_sa_display.entity_id AND field_data_field_sa_display.entity_type = 'node'
LEFT JOIN {field_data_field_sa_display} field_data_field_sa_display2 ON node.nid = field_data_field_sa_display2.entity_id AND (field_data_field_sa_display2.field_sa_display_value = '1' OR field_data_field_sa_display2.field_sa_display_value = '2')
LEFT JOIN {field_data_field_sa_order} field_data_field_sa_order ON node.nid = field_data_field_sa_order.entity_id AND field_data_field_sa_order.entity_type = 'node'
WHERE (( (node.type IN  ('service_announcement')) AND (DATE_FORMAT(field_data_field_sa_visible.field_sa_visible_value, '%Y-%m-%d\T%H:%i') < '2016-11-25T11:01') AND (DATE_FORMAT(field_data_field_sa_visible.field_sa_visible_value2, '%Y-%m-%d\T%H:%i') > '2016-11-25T11:01') AND (field_data_field_sa_archive.field_sa_archive_value = '0') AND (node.status = '1') AND (field_data_field_sa_live.field_sa_live_value = '1') AND (field_data_field_sa_display2.field_sa_display_value IS NULL ) )OR( (node.type IN  ('service_announcement')) AND (field_data_field_sa_visible.field_sa_visible_value IS NULL ) AND (field_data_field_sa_archive2.field_sa_archive_value IS NULL ) AND (node.status = '1') AND (field_data_field_sa_live2.field_sa_live_value IS NULL ) AND (field_data_field_sa_display.field_sa_display_value = '0') ))
ORDER BY field_data_field_sa_order_field_sa_order_value ASC
LIMIT 10 OFFSET 0

And here's the same SQL with the test changes to isset():

SELECT node.title AS node_title, node.nid AS nid, node.changed AS node_changed, field_data_field_sa_order.field_sa_order_value AS field_data_field_sa_order_field_sa_order_value, 'node' AS field_data_field_sa_introduction_node_entity_type
FROM 
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
LEFT JOIN {field_data_field_sa_visible} field_data_field_sa_visible ON node.nid = field_data_field_sa_visible.entity_id AND (field_data_field_sa_visible.entity_type = 'node' AND field_data_field_sa_visible.deleted = '0')
INNER JOIN {field_data_field_sa_archive} field_data_field_sa_archive ON node.nid = field_data_field_sa_archive.entity_id AND (field_data_field_sa_archive.entity_type = 'node' AND field_data_field_sa_archive.deleted = '0')
INNER JOIN {field_data_field_sa_live} field_data_field_sa_live ON node.nid = field_data_field_sa_live.entity_id AND (field_data_field_sa_live.entity_type = 'node' AND field_data_field_sa_live.deleted = '0')
LEFT JOIN {field_data_field_sa_archive} field_data_field_sa_archive2 ON node.nid = field_data_field_sa_archive2.entity_id AND field_data_field_sa_archive2.field_sa_archive_value = '1'
LEFT JOIN {field_data_field_sa_live} field_data_field_sa_live2 ON node.nid = field_data_field_sa_live2.entity_id AND field_data_field_sa_live2.field_sa_live_value = '0'
INNER JOIN {field_data_field_sa_display} field_data_field_sa_display ON node.nid = field_data_field_sa_display.entity_id AND (field_data_field_sa_display.entity_type = 'node' AND field_data_field_sa_display.deleted = '0')
LEFT JOIN {field_data_field_sa_display} field_data_field_sa_display2 ON node.nid = field_data_field_sa_display2.entity_id AND (field_data_field_sa_display2.field_sa_display_value = '1' OR field_data_field_sa_display2.field_sa_display_value = '2')
LEFT JOIN {field_data_field_sa_order} field_data_field_sa_order ON node.nid = field_data_field_sa_order.entity_id AND (field_data_field_sa_order.entity_type = 'node' AND field_data_field_sa_order.deleted = '0')
WHERE (( (node.type IN  ('service_announcement')) AND (DATE_FORMAT(field_data_field_sa_visible.field_sa_visible_value, '%Y-%m-%d\T%H:%i') < '2016-11-25T11:02') AND (DATE_FORMAT(field_data_field_sa_visible.field_sa_visible_value2, '%Y-%m-%d\T%H:%i') > '2016-11-25T11:02') AND (field_data_field_sa_archive.field_sa_archive_value = '0') AND (node.status = '1') AND (field_data_field_sa_live.field_sa_live_value = '1') AND (field_data_field_sa_display2.field_sa_display_value IS NULL ) )OR( (node.type IN  ('service_announcement')) AND (field_data_field_sa_visible.field_sa_visible_value IS NULL ) AND (field_data_field_sa_archive2.field_sa_archive_value IS NULL ) AND (node.status = '1') AND (field_data_field_sa_live2.field_sa_live_value IS NULL ) AND (field_data_field_sa_display.field_sa_display_value = '0') ))
ORDER BY field_data_field_sa_order_field_sa_order_value ASC
LIMIT 10 OFFSET 0