I wrote about this some time ago but I never quite distilled it to the basics. The issue is that "IS TRUE" and "IS FALSE" are not their own unary operators but are instead implemented as special cases of "IS"

This is the minimal repro that shows what goes wrong:

PostgreSQL 9.3 (correct)

select null is true || 'x';
falsex

SQLite 3.31 (incorrect)

select null is true || 'x';
0

The docs say this:

The boolean identifiers TRUE and FALSE are usually just aliases for the integer values 1 and 0, respectively. However, if TRUE or FALSE occur on the right-hand side of an IS operator, then they form new unary postfix operators "IS TRUE" and "IS FALSE" which test the boolean value of the operand on the left.

This makes the situation worse. They most certainly do not form new unary postfix operators!

... if TRUE or FALSE occur on the right-hand side of an IS operator, then they form new unary postfix operators "IS TRUE" and "IS FALSE" which test the boolean value of the operand on the left.

They most certainly do not form new unary postfix operators!

Do you have a set of expressions which disprove that assertion in the doc?

Incidentally, the operator precedence table does not yet reflect that assertion, which anomaly I plan to correct soon. So I am quite interested in your counter-example.

SELECT 2 IS TRUE, 2 IS (TRUE), 2 IS +TRUE;
+-----------+-------------+------------+
| 2 IS TRUE | 2 IS (TRUE) | 2 IS +TRUE |
+-----------+-------------+------------+
| 1         | 1           | 0          |
+-----------+-------------+------------+

If IS TRUE had been an actual unary postfix operator, the second expression would have been equivalent to the third one rather than to the first one.

What actually happens is this:

1. Syntactically, only the binary IS operator exists.
2. Semantically, the right subtree of the IS operator is checked to see if it consists of a single TRUE or FALSE keyword.
3. TRUE and (TRUE) generate the exact same expression tree.
4. TRUE and +TRUE generate different expression trees.

The construct x IS TRUE is syntactic sugar for x != 0
The construct x IS FALSE is syntactic sugar for x == 0

IS TRUE and IS FALSE are NOT infix operators.

Without the IS preface, the True is the contstant value 1 and False is the constant value 0.

Were IS TRUE and IS FALSE were infix operators, then the result is simply the value of the NZ flag after loading the value into the accumulator.

If you like postfix IS TRUE can be sugar for IFNULL(x != 0, 0). But the above is incorrect.

If you care to experiment, you'll find that there is no wide agreement about whether IS NULL is a postfix unary operand or part of a binary IS operand. SQLite, PostgreSQL and MySQL give differing results. I couldn't get Oracle or SQL Server to take my expressions at all...

But in any case the SQLite docs had no weakness with regard to IS NULL--they describe the implementation correctly which is all we can ask for.

A reexamination of that language and the implementation has shown that {IS, IS NOT} paired with {TRUE, FALSE} is better not considered a unary postfix operator. This has led to an alteration of the language you quote. There are undoubtedly more comprehensive and wordier explanations, but this minor feature is an optional nicety¹ whose full description would be a distraction from the more important information about expressions.

1. ^{^} The operator is a shortcut for a slightly longer expression involving ISNULL variants.

It would depend if null is true should return False (0), or NULL.

IMHO, select null is true should be NULL, not 0 (false).

It should NOT be processessed as select null is not 0, though processing as select null != 0 is correct.

SQL has tri-state logic. It is very complicated.

IMHO both PostgreSQL and SQLite3 are incorrect and IS TRUE and IS FALSE should not be used (because they have incorrect results) and only the correct expression != 0 for IS TRUE and == 0 for IS FALSE should be used.

IMHO, select null is true should be NULL, not 0 (false).

You could try to sway the standards bodies to change it but I don't think they're wrong. The point of IS TRUE vs. != 0 is to normalize null. IFNULL(x != 0, 0) is hard on the eyes and it does come up a lot. At this point every system agrees that x IS TRUE and x IS FALSE never return null.

IMHO both PostgreSQL and SQLite3 are incorrect and IS TRUE and IS FALSE should not be used (because they have incorrect results) and only the correct expression != 0 for IS TRUE and == 0 for IS FALSE should be used.

I don't think the standard leaves room for interpretation on this. IS TRUE and IS FALSE are not tristate. Sqlite's issues stem from the fact that it doesn't actually have IS TRUE or IS FALSE operators, instead it uses its own (not exactly standard) "IS" with a special case of the boolean true or false as the right operand but that results in binding strength that is not standard. Hence the bug.

In practice this is a very minor issue because it takes skill to craft an example where SQLite's (expeditious?) choice makes a difference compared to the perfect choice.

However, as I've written previously the SQLite docs are not at all clear on what SQLite is actually doing. Maybe quirks is the best place to put this. It is worth it to be clear that SQLite only approximates unary postfix IS TRUE.

IMHO, it would not be good for SQLite to take a position of "don't use this well specified standard operator that is in our grammar" especially if the only reason to not use it is that in very quirky cases that almost never happen SQLite gets it wrong.

The docs seem to have been cleared up which I think is the only thing that was really necessary here.