SQLite Forum

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

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 [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

(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 [link] [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.

(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.