V8 API - Bug in WHERE clause, when filtering email1 field

[csskevin]

Hey,
there seems to be a bug in the V8 API in version 7.11.19, (I am not sure if these affects older versions as well, I just have tested it for that version), where the filter function for the email1 field doesn't work.
This applies to the comment I posted here: #8617 (comment)

Issue

By trying:
https://<suitecrm_url>/Api/V8/module/Accounts?filter[email1][eq]=<some_email>

Please note, that <suitecrm_url> and <some_email> are just placeholder!

I always get as response, and it doesn't matter if the email address exists or not:

{
  "meta": {
    "message": "Request was successful, but there is no result"
  },
  "data": []
}

After digging in the code a bit and debugging the query I found some interesting SQL query statement:

SELECT accounts.id FROM email_addresses JOIN email_addr_bean_rel ON email_addresses.id=email_addr_bean_rel.email_address_id JOIN accounts ON accounts.id=email_addr_bean_rel.bean_id where (email_addresses.email_address='<some_email>' AND accounts.deleted='0') AND accounts.deleted=1

Which is a bit odd, if you look closely at the WHERE clause, which says: (email_addresses.email_address='<some_email>' AND accounts.deleted='0') AND accounts.deleted=1
From then on, it made sense why the query always fails.

After further debugging I found this interesting line:

SuiteCRM/Api/V8/Service/ModuleService.php

Lines 154 to 158 in 9cb957e

	if ($deleted === 0) {
	$whereAuto = '' . $bean->table_name . ' .deleted=0';
	} else {
	$whereAuto = '' . $bean->table_name . ' .deleted=1';
	}

And the variable $deleted is defined as:

SuiteCRM/Api/V8/Service/ModuleService.php

Line 123 in 9cb957e

$deleted = $params->getDeleted();

The issue now is, that the value of $deleted is false and due value and type check $deleted === 0 it obviously fails, due 0 === false is false.

So changing this line to:

if (!$deleted) {
    $whereAuto = '' . $bean->table_name . ' .deleted=0';
} else {
    $whereAuto = '' . $bean->table_name . ' .deleted=1';
}

solves this issue.