Incorrect Filtering on columns

[daryllabar]

If I run this query:

SELECT top 10 FullName, mad_ownerusername, contact.mad_specssalesrepusername
FROM mad_specsengagementletter
INNER JOIN contact ON mad_specsengagementletter.mad_client = contact.contactid
WHERE mad_ownerusername <> contact.mad_specssalesrepusername AND contact.mad_specssalesrepusername IS NULL

I get 0 results, but if I run this query where I remove the not equal to:

SELECT top 10 FullName, mad_ownerusername, contact.mad_specssalesrepusername
FROM mad_specsengagementletter
INNER JOIN contact ON mad_specsengagementletter.mad_client = contact.contactid
WHERE contact.mad_specssalesrepusername IS NULL

I get 10+ results, and for the records that are returned, mad_ownerusername is not null. mad_ownerusername and mad_specssalesrepusername are both FX Calculated columns that are just the user name of the owning user...

(using SQL 4 CDS 8.0.0.0)

[MarkMpn]

SQL 4 CDS follows the ANSI NULL behavior where <any value> <> NULL gives an UNKNOWN result which is filtered out by the WHERE clause.

Because of this, WHERE mad_ownerusername <> contact.mad_specssalesrepusername AND contact.mad_specssalesrepusername IS NULL is an impossible WHERE clause and it will always return 0 records.

[daryllabar]

So is WHERE mad_ownerusername <> contact.mad_specssalesrepusername OR contact.mad_specssalesrepusername IS NULL more likely what I want?

[MarkMpn]

Yes, assuming mad_ownerusername is guaranteed to be non-null. If that could be null as well you’ll need to extend it to:

WHERE mad_ownerusername <> contact.mad_specssalesrepusername OR
mad_ownerusername IS NOT NULL AND contact.mad_specssalesrepusername IS NULL OR
mad_ownerusername IS NULL AND contact.mad_specssalesrepusername IS NOT NULL

You can also use the newer IS DISTINCT FROM syntax:

WHERE mad_ownerusername IS DISTINCT FROM contact.mad_specsalesrepusername

This syntax isn’t currently supported in the TDS Endpoint however.