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
, andis 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 [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 [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.
(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?