SQLite Forum

documenting SQLite's "IS" behavior
Login

documenting SQLite's "IS" behavior

(1.5) By Rico Mariani (rmariani) on 2021-07-28 14:27:58 edited from 1.4 [link] [source]

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;
1
sqlite> select null not null <= true;
1
sqlite> select null is not null <= true;
0

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.

(2) By Lifepillar (lifepillar) on 2021-07-28 20:17:45 in reply to 1.5 [link] [source]

I think that the simplest way to put it is:

  1. SQLite's is is the same as ISO SQL's is not distinct from;
  2. SQLite's is not is the same as ISO SQL's is distinct from.

I have not checked the formal semantics of is and is not in SQLite, so it is possible that there are subtle differences, but from your examples and my few tests (1) and (2) above appear to be true.

(3.1) By Rico Mariani (rmariani) on 2021-07-29 00:23:35 edited from 3.0 in reply to 2 [link] [source]

Well, almost, there's another wrinkle.

See if the right operand of IS (or IS NOT) is a boolean literal it changes its meaning.

So for instance

select 2 is true 
1

but

select 2 is not distinct from true
false

The is operator changes its meaning if its right operand is either of the literals true or false. So "IS" is distinct from "IS DISTINCT FROM" :D

(4.1) By Rico Mariani (rmariani) on 2021-07-29 00:33:33 edited from 4.0 in reply to 3.1 [link] [source]

examples:

sqlite> select 2 is true;  -- boolean literal changes the meaning
1
sqlite> select 2 is +true;  -- back to "2 is 1"
0
sqlite> select 2 is (1==0);  -- also "2 is 1"
0
sqlite> select 2 is (true);  -- redundant parens don't spoil it
1
sqlite> select null is true <= true; -- not a unitary operator
0

(5) By Lifepillar (lifepillar) on 2021-07-29 08:05:31 in reply to 4.1 [link] [source]

That's implicit casting at work.

sqlite> select 2 is true;

is like

psql=# select 2::boolean is not distinct from true;

In the second example, true must be cast to integer first, so:

sqlite> select 2 is +true;

is equivalent to PostgreSQL's SQL:

psql=#  select 2 is not distinct from +true::int;

And so on. So I stand by my claim above.

(6.1) By Rico Mariani (rmariani) on 2021-07-29 16:57:07 edited from 6.0 in reply to 5 [link] [source]

For implicit promotions one would expect the bool to be promoted to int rather than the int reduced to a bool.

Note that in SQLite the + operator does not change types. It can even be applied to a string:

select +'hi';
hi

But anyway, it's not that important.

Other versions of "true" don't work; well, there is no such thing as a boolean type in SQLite so it has kind of no hope. This kind of stuff isn't going to work:

sqlite> select 2 is (0==0);
0

Note that the ISO operator commutes, but:

sqlite> select true is 2;
0

"IS" really is just like the ISO operator except for that special case for boolean literals on the right. Which is not really a surprise because that's how the code is written.

    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
    }

(7) By Domingo (mingodad) on 2021-07-29 17:42:28 in reply to 6.1 [source]

Thank you for your persistence on clarify operators precedence/associativity in sqlite !

And it seems to me that some of the examples that you are giving should be considered a bug, like the plus unary for strings, and the boolean confusion.

select +'hi';
hi

(8) By Rico Mariani (rmariani) on 2021-07-30 05:14:10 in reply to 7 [link] [source]

select +'hi' is documented behavior for SQLite. The + operator is universal identity. It's perhaps strange but it's as advertised.