Product
Support
Everything Else
Undefined Values and And/Or Logic
Introduction

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

10.9.11 And

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".

10.9.12 Or

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.

Missing Information

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.

Philosophical Musings

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:

  • [false] And [undefined] = false. All that is required for and to be false is that one side or the other be false.

  • [false] Or [undefined] = undefined. Since we can not know the value of the undefined input, and we have no true value to positively rule that the result is true, we can not know the result.

  • [true] Or [undefined] = true. All that is required for or to be true is that one side or the other be true.

  • [true] And [undefined] = undefined. Since we can not know the value of the undefined input, and we have no false value to positively rule that the result is false, we can not know the result.

These properties are commutative. That is, [true] Or [undefined] is the same as [undefined] Or [true]

Known Problem

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:

  1. Reverse the order of the tests. Putting the undefined test first — as in Undefined [Paid] Or Not [Paid] — allows the query optimizer to correctly locate all of the records it should. (It is this test that most clearly shows that a bug is present.)
  2. Index the query. Building an index on the abacus Not [Paid] Or Undefined [Paid] works around the bug at the expense of an extra index.
  3. Change the test to Not Undefined [Paid] becomes [False] — this will have a detrimental impact on performance if there are many records, as the Undefined ◊ becomes ◊ tile can not be optimized.
  4. Remove the index on the field ‘Paid’ — this could also have a detrimental impact on performance, particularly if there are other functions that rely on that index.

The status of this bug can be tracked in techdb: Bug Report 8444.