SQLite Forum

When is "IS" used in SQLite?
My point has very little, if anything, to do with how SQLite internally treats certain SQL constructs. My point is about the *logical* meaning of certain expressions.

It was claimed that the value of `5=TRUE` (`0`) being different from the value of `5 IS TRUE` (`1`) contradicts my assertion that `=` and `IS` evaluate differently only when the expressions contain `null`. My answer was that `5=TRUE` and `5 IS TRUE` are two totally unrelated expressions, because (as per the documentation I have cited):

1. `TRUE` is an alias for `1`, so that `5=TRUE` is actually `5=1`;
2. as an exception to (1), `is` followed by `true` becomes the unary operator `is true`, which (logically) takes a Boolean as an argument. So, `5 is true` is (logically) equivalent to `(true) is true`. The internal representation in SQLite source or inside CPU registers is irrelevant to my argument.

To elaborate further:

- `is [not]` behaves like `is [not] distinct from` is PostgreSQL;
- `is [not] distinct from` in PostgreSQL adheres to the rules of ISO SQL;
- the rules of ISO SQL can be summarized as follows: given the expression `V1 is distinct from V2`:

1. if both `V1` and `V2` evaluate to null, the result is False;
2. if `V1` is null and `V2` is not null, or vice versa, the result is True;
3. otherwise, `V1` and `V2` are both definite values, which can be compared with standard equality, so the result is True or False accordingly.

Hence, `=` and `is` can evaluate differently only when nulls are involved, because in cases (1) and (2), the result of `=` would be null, and in case (3), the result of `=` is the same as the result of `is` by definition.

I hope my explanatio is clear. I am not addressing the rest of your post (please attack the arguments, not the individual formulating them), except for this:

>As a conditional, a NULL value evaluates as False.

Most of the time, but now always:

create table (x int check (value > 0));
insert into T values (null); -- OK

Clearly, here NULL evaluates to True. Nulls are tricky!