SQLite Forum

operators IS TRUE and IS FALSE missing from diagrams and table plus possible evaluation errors

operators IS TRUE and IS FALSE missing from diagrams and table plus possible evaluation errors

(1.2) By Rico Mariani (rmariani) on 2021-07-22 13:53:11 edited from 1.1 [link] [source]

From Chapter 2 Section 14:

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.

Therefore we have to add the operators `IS TRUE` and `IS FALSE` to the priority table and to the railroad diagram.

And we get these lovely properties

``````select 2 is true;
1

select true is 2;
0
``````

Which are not wrong but are somehow disturbing :D

However...

``````-- I think this is wrong:
-- (true) cannot be reasonably considered a unary operator postfix
select 2 is (true);  -- I claim this should be equivalent to 2 is 1
1

-- this is as expected
select 2 is +true;
0
``````

Furthermore, neither the diagrams nor the documents prepare one for these:

``````select 2 is not true; -- implies existence of stealth "is not true" operator
0

select 2 is not 1;
1
``````

The above only make sense if "is not true" also forms a unary postfix operator. Which is fine but it is not mentioned anywhere.

So should we be adding the operators "is not true" and "is not false" to the list?

Well it turns out that "is false" and "is not false" do not need to be special

``````select 1 is false;
0
select 0 is false;
1
select null is false;
0

-- regular "is" is the same as "is false"
select 1 is +false;
0
select 0 is +false;
1
select null is +false;
0
``````

[edit notes

The previous text claimed that `is false` was the same as regular `is`, but as was pointed out later, `is false` is different than regular `is` because it forces the left side to be treated as a boolean hence

``````select '' is false;
1

select '' is 0
0
``````

The summary was also edited to reflect this. ]

Summary:

• we're missing docs on `is true`, `is false`, `is not true`, and `is not false`
• `x is (true)` seems broken (we may be stuck with this)
• same for `x is (false)`

(2) By David Raymond (dvdraymond) on 2021-07-22 12:44:43 in reply to 1.1 [link] [source]

```> Well it turns out that "is false" and "is not false" do not need to be special

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select '' is false;
1
sqlite> select '' is (false);
1
sqlite> select '' is (+false);
0
sqlite> select false;
0
sqlite> select '' is 0;
0
sqlite> select '' is (0);
0
sqlite> select '' is (+0);
0
```

(3.1) By Rico Mariani (rmariani) on 2021-07-22 13:54:08 edited from 3.0 in reply to 2 [link] [source]

Oops I forgot about strings... [inline edits to correct for the above have been made. The above reply might now look out of place but it was fair comment on the original text]

(4) By Keith Medcalf (kmedcalf) on 2021-07-22 18:50:36 in reply to 3.1 [link] [source]

The phrase `IS FALSE` means `== 0` while the phrase `IS TRUE` means `<> 0`.

The constant `FALSE` means 0, and the constant `TRUE` means `1`.

`x IS TRUE` means `x <> 0` -- `IS TRUE` means `<> 0`

`TRUE IS x` means `1 IS x` -- `TRUE IS` means `1 ==` (True is a constant in this case, not a test).

IS and TRUE are not commutative. `IS TRUE` is not the same as `TRUE IS`
IS and FALSE are commutative. `IS FALSE` is the same as `FALSE IS`

(5.2) By Rico Mariani (rmariani) on 2021-07-22 19:28:31 edited from 5.1 in reply to 2 [link] [source]

OK the situation is much worse than I thought it was.

Consider these cases:

``````-- example 1
sqlite> select 5 is true * 10;
0
-- example 2
sqlite> select (5 is true) * 10;
10
-- example 3
sqlite> select 5 is (true * 10);
0
``````

Compare with

``````-- example 4
sqlite> select null is not null;
0
-- example 5
sqlite> select null is (not null);
1
``````

Now referring to the documentation:

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.

Example 1 shows clearly that when `TRUE` is on the right side of `IS` it does not form an operator. If it did the binding order would have to be the order shown in example 2 not the order shown in example 3. Compare with example 4. NOT after is forms an operator "IS NOT" (this is correct). It is not allowed to bind like in example 5 (without parens) that would all wrong.

What seems to be actually happening is this:

If the right operand of the `IS` operator is the literal `TRUE` then `IS` is "improved" to the operator "IS TRUE".

So I'm now thinking there is no "IS TRUE" operator at all. This all happens in evaluation. As you can see this makes a difference.

SQLite parses "5 is true * 10" using the precedence of `*` then the precedence of `IS`.

It's not at all clear to me that this could be changed at this point.

Looking at the source code:

``````    case TK_IS:
case TK_ISNOT: {
Expr *pRight = sqlite3ExprSkipCollateAndLikely(pExpr->pRight);
assert( !ExprHasProperty(pExpr, EP_Reduced) );
/* Handle special cases of "x IS TRUE", "x IS FALSE", "x IS NOT TRUE",
** and "x IS NOT FALSE". */
if( ALWAYS(pRight) && (pRight->op==TK_ID || pRight->op==TK_TRUEFALSE) ){
int rc = resolveExprStep(pWalker, pRight);
if( rc==WRC_Abort ) return WRC_Abort;
if( pRight->op==TK_TRUEFALSE ){
pExpr->op2 = pExpr->op;
pExpr->op = TK_TRUTH;
return WRC_Continue;
}
}
/* no break */ deliberate_fall_through
}
``````

We see the evidence.

`IS` and `IS NOT` are improved to `IS TRUE` or `IS FALSE` if the right operand is `TK_TRUEFALSE`

This means among other things that if you are trying to parse SQLite you cannot have a lexeme for "IS TRUE" and be compatible that will be all kinds of wrong.

Here's another example:

``````sqlite> select false is true < false;
1

PostgreSQL> select false is true < false;
false
PostgreSQL 9.6.17 on x86_64-pc-linux-gnu (Debian 9.6.17-2.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
``````

I think SQLite is fundamentally wrong here.

(6.2) By Larry Brasfield (larrybr) on 2021-07-22 19:41:34 edited from 6.1 in reply to 4 [link] [source]

I appreciate seeing a good start on getting these wrinkles ironed out. (And seeing it sans extra diddling or other distraction is a bonus!)

What do you think of:``` sqlite> select 'zip' is false; 1 sqlite> select false is 'zip'; 0 ```, making it appear FALSE with "IS" is only commutative for numbers?

(7) By Rico Mariani (rmariani) on 2021-07-22 19:40:59 in reply to 6.1 [source]

Unfortunately `IS TRUE` is not the same as `<> 0`. There are the `NULL` cases to consider. Like regular `IS` that's its main reason for existing.

(8) By Keith Medcalf (kmedcalf) on 2021-07-22 20:35:37 in reply to 7 [link] [source]

Perhaps.

`NULL IS TRUE` is FALSE or 0.
`NULL <> 0` is NULL (which is False).

So "arithmetically" `IS TRUE` is not the same as `<> 0`
But logically, `IS TRUE` is the same as `<> 0`

(9) By Rico Mariani (rmariani) on 2021-07-22 22:14:07 in reply to 8 [link] [source]

For myself I find it confusing to appeal to definitions of `<>` other than the one that is in SQLite.

(10) By Rico Mariani (rmariani) on 2021-07-28 00:09:42 in reply to 5.2 [link] [source]

Would it help if I made a clean post describing only the document issue with no diddling?