Relevance query question

Reporting
1

(imported topic written by birtandernie)

Is there a reason NULLs seem to defy logic in this relevance query clause?

whose ((Last Became Nonrelevant of it >= “Fri, 15 Feb 1980 00:00:00 +0000” as time) OR

(Last Became Relevant of it >= “Fri, 15 Feb 1980 00:00:00 +0000” as time))

Despite the OR the above query only returns rows that are not null in both columns. In order to truly get everything we want we need to check for NULLs before the comparison:

whose ((exists Last Became Nonrelevant of it AND Last Became Nonrelevant of it >= “Fri, 15 Feb 1980 00:00:00 +0000” as time) OR

(exists Last Became Relevant of it AND Last Became Relevant of it >= “Fri, 15 Feb 1980 00:00:00 +0000” as time))

It’s as if the NULLs short-circuit the whole filter

2

(imported comment written by BenKus)

In general, if the object doesn’t exist (what you are calling null) will cause a “Singular expression refers to a non-existent object”, which is an error.

In 8.0+, you can use the “|” operator to handle the error:

whose (((Last Became Nonrelevant of it >= “Fri, 15 Feb 1980 00:00:00 +0000” as time) | false) OR

((Last Became Relevant of it >= “Fri, 15 Feb 1980 00:00:00 +0000” as time) | false))

Ben