Introducing the boolean datatype
Posted on in firebird
The support for a BOOLEAN datatype was the third most voted feature in the Firebird tracker. The deal with it was about make it right and complete, having booleans expressions allowed where values are expected, and vice versa. Oracle did it wrong and they BOOLEAN is crap.
Yesterday I finished and committed this support for Firebird 3. You can use them like any other type. You can index it, aggregate by it and sort by it. If you have an index on a boolean field, you can also do indexed searches by its negated form (NOT field).
Three new literals are introduced: FALSE, TRUE and UNKNOWN (same as NULL).
Booleans are not implicitly convertible to any other datatype. But it's convertible to/from strings with CAST.
It's allowed to test booleans without compare with TRUE or FALSE. For example, "field1 OR field2" and "NOT field1" are valid expressions. It's also allowed to compare with others operators, including the new IS operator: "field1 IS FALSE".
Currently it has a terrible and bad hack. The new syntax would introduce a lot of parser conflicts related with trigger's INSERTING, UPDATING and DELETING expressions. These boolean expressions was non-reserved words and allowed to be used as column and variable names. The correct solution shall be to reserve these words and do not allow them to be used as names without double quotes.
As usual, this causes conflict opinions. I don't want to fight forever on this, so I make them work as triggers keywords in boolean expressions and as values in non-booleans expressions. This is much very compatible with previous versions, but is very confusing. For example, in "SELECT inserting, NOT inserting FROM test WHERE INSERTING and INSERTING IS TRUE" the INSERTING's are recognized as value, keyword, keyword and value.
I hope this clears people's minds and we have a better solution in the final version.