|Undefined Values and And/Or Logic|
Constructing complex queries using Boolean logic (◊ and ◊ And ◊ Or ◊ tiles) requires careful planning to avoid logical errors that produce unexpected results.
|From The Helix Reference||
The ◊ And ◊ tile accepts only flag input. If both input values are true, then the tile returns true. If one or both of the inputs are false, then the tile returns false.
For example, if you want to find a dog owner who lives in Dallas, you could use [City] = "Dallas" And [Pet] = "Dog".
The ◊ Or ◊ tile accepts only flag input. If either or both of the input values are true, the tile returns true. If both input values are false, the tile returns false. Either hole can be undefined. If the defined hole is true, the tile returns true.
If, for example, you were a Chicago company evaluating prospective employees, you might look for one who [Owns A Car] Or [City] = "Chicago". The result is true if the candidate owns a car, lives in Chicago, or both.
What The Helix Reference does not explicitly state is that when one hole of an ◊ And ◊ tile is undefined, the tile returns false if the defined hole is false.
In the early days of Helix, a philosophical debate arose within the company as to the nature of ‘undefined’ data. Without delving too deeply into that, it may be useful to understand that the upshot of the debate was that ‘undefined’ means ‘we can’t tell’ as opposed to ‘there’s nothing there.’ This is the approach Helix takes when Boolean logic is concerned.
Therefore, when an undefined value is involved in conjunction with an ◊ And ◊ Or ◊ Or ◊ tile, the following logic applies:
These properties are commutative. That is, [true] Or [undefined] is the same as [undefined] Or [true]
The preceding statement (that these properties are commutative) is true except for the fact that there is a bug that causes one specific construction to fail. This bug exists in all versions of Helix since Helix Express 4.5.2b1.
Consider this example: your accounts receivable relation contains a flag field ‘paid’ that is used to indicate whether a payment has been received. To be certain that no unpaid invoices are overlooked, the query is constructed as follows:
Not [Paid] Or Undefined [Paid]
This will correctly locate the records where the ‘Paid’ field is false or undefined unless there is an index built in the field ‘Paid’ — the presence of an index causes this query to only return the records where ‘Paid’ is false. (The undefined records are excluded.)
There are four solutions to this problem:
The status of this bug can be tracked in techdb: Bug Report 8444.