SQLite Forum

documenting SQLite's "IS" behavior
I first thought that "IS TRUE" and friends were missing from the grammar diagram but I realize now that I am mistaken.  

SQLite has made the choice not to have unique postix operators like "IS TRUE" or even "IS NOT NULL" except the few documented ones such as "NOTNULL" "ISNULL" and "NOT NULL".  (1)

Instead, all of the "IS" flavors are implemented as a SQLite extension to the language: the general purpose binary "IS" operator.  This choice results in considerable economy. (2)

I believe this choice needs to be documented clearly because it has compatibility consequences.

Here are some simple examples that illustrate what is going on:

sqlite> select null notnull <= true;
sqlite> select null not null <= true;
sqlite> select null is not null <= true;

As we see above, in the third example "is not null" is not a unitary operator but rather binary "is not" with null on the right.  Since <= has precedence, the inequality is evaluated first.  This leaves us with "null is not null" which is false.

In other SQL dialects all three expressions are "true" because "is not null" is a unitary operator so it of course behaves just like "not null".  You can try the above on PostgreSQL or MySQL or whatever.

Similar examples can be constructed for "IS TRUE" and the others to illustrate the difference.

So then it makes total sense that "IS TRUE", "IS NOT NULL", and so forth are omitted from the documentation.  In SQLite they do not exist.

However, I believe the documentation would be enhanced by explaining the important implementation choice described in (1) and (2) above and the subtle compatibility issues that this choice creates.  

The non-atomic nature of "IS TRUE" and "IS FALSE" is especially subtle because "IS" behaves slightly differently if its right operand is a boolean literal.  The documentation is especially unclear on this point, implying the existence of postfix operators that do not in fact exist.

It's safe to disregard my previous messages on these points because they were incomplete, the above explains the situation more economically and more completely than the previous messages.