SQLite Forum

When is "IS" used in SQLite?
Login

When is "IS" used in SQLite?

(1) By J.M. Aranda (JMAranda) on 2021-08-23 12:44:05 [link] [source]

When is "IS" used in SQLite?

(2) By Ryan Smith (cuz) on 2021-08-23 12:54:08 in reply to 1 updated by 2.1 [source]

When you need to test semantic equality between things that do not necessarily have mathematical equality.

```
Examples 
5 IS 5       --> TRUE
5 IS 6       --> FALSE
5 IS NULL    --> FALSE
NULL == NULL --> FALSE (not mathematically equal, but )
NULL IS NULL --> TRUE  (they are semantically equivalent)
5 == TRUE    --> FALSE
5 IS TRUE    --> TRUE

```

(2.1) By Ryan Smith (cuz) on 2021-08-23 13:06:52 edited from 2.0 in reply to 1 updated by 2.2 [link] [source]

When you need to test semantic equality between things that do not necessarily have mathematical equality.

```
Examples 
5 IS 5       --> TRUE
5 IS 6       --> FALSE
5 IS NULL    --> FALSE
NULL == NULL --> NULL  (not mathematically equal, but )
NULL IS NULL --> TRUE  (they are semantically equivalent)
1 -- TRUE    --> TRUE  (mathematically equal)
5 == TRUE    --> FALSE (not mathematically equal)
5 IS TRUE    --> TRUE  (semantically equal)

EDIT:
There is also testing semantic unequivalence (if that is a real word) using IS NOT:
5 IS NOT NULL    --> TRUE
NULL IS NOT NULL --> FALSE
NULL <> NULL     --> NULL

There is also "ISNULL" which is equivalent to "IS NULL" and sometimes a more easy statement to use, so that:
... WHERE a IS NULL;
is exactly the same as saying:
... WHERE a ISNULL;

```

(2.2) By Ryan Smith (cuz) on 2021-08-23 13:08:45 edited from 2.1 in reply to 1 [link] [source]

When you need to test semantic equality between things that do not necessarily have mathematical equality.

Examples 
5 IS 5       --> TRUE
5 IS 6       --> FALSE
5 IS NULL    --> FALSE
NULL == NULL --> NULL  (not mathematically equal, but )
NULL IS NULL --> TRUE  (they are semantically equivalent)
1 == TRUE    --> TRUE  (mathematically equal)
5 == TRUE    --> FALSE (not mathematically equal)
5 IS TRUE    --> TRUE  (semantically equal)

EDIT:
There is also testing semantic unequivalence (if that is a real word) using IS NOT:
5 IS NOT NULL    --> TRUE
NULL IS NOT NULL --> FALSE
NULL <> NULL     --> NULL

There is also "ISNULL" which is equivalent to "IS NULL" and sometimes a more easy statement to use, so that:
... WHERE a IS NULL;
is exactly the same as saying:
... WHERE a ISNULL;

(3) By J.M. Aranda (JMAranda) on 2021-08-23 12:56:50 in reply to 2.0 [link] [source]

Thank you. It's overwhelming.

(8) By Lifepillar (lifepillar) on 2021-08-24 11:50:29 in reply to 2.2 [link] [source]

Sorry for nitpicking, but I can't resist noticing that null next to “semantics” forms an oxymoron :)

To expand on the above: is differs from == only when the considered expression contains a null. The difference is that == obeys Codd's Substitution Principle, while is does not.

According to Codd's Substitution Principle, an expression has the truth value “unknown”, if and only if:

  1. each occurrence of null may be replaced by a (distinct or already used) non-null value so that the expression’s truth value becomes “true”, and

  2. each occurrence of null may be replaced by a (distinct or already used) non-null value so that the expression’s truth value becomes “false”.

This is a pretty ad-hoc principle, which (1) was formulated by Codd strictly under the “existing, but unknown value” interpretation of nulls (but it is logically incompatible with such an intepretation), and (2) leads to many unresolvable inconsistencies in the treatment of nulls in SQL. Unfortunately, that is cast in stone in the standard, so one has to live with it. The best way to live with it is to avoid using nulls altogether.

Re (1), I'll just note that null cannot be interpreted as “value at present unknown” in general, because, if that were the case, then select x=x from T should always return true, even when x is null.

IS has instead a clean semantics. Any expression that contains a null IS (the same) as any other expression that contains null and IS NOT (the same a s) any other expression not containing a null (so, it's either true or false). This rule is used, among the rest, for the elimination of duplicates:

create table T (x int);
insert into T values (1), (1), (null), (null);
select distinct x from T;

and for set operations:

select x from T intersect select x from T;
select x from T union select x from T;
select x from T except select x from T;

again, in accordance to what Codd himself warned being a "preliminary and in need of further research" proposal.

(9) By Ryan Smith (cuz) on 2021-08-24 12:46:45 in reply to 8 [link] [source]

Nice writeup and nitpicking is very much welcomed - it serves only to improve everyone's understanding, but you make false claims right off the bat during your nitpick, which achieves the opposite.

To expand on the above: is differs from == only when the considered expression contains a null.

Not True which is clear from my examples, but to reiterate:

SELECT (5 = TRUE) AS ' == ',  (5 IS TRUE) AS ' IS '

  --    ==  |     IS     
  -- -------|------------
  --     0  |      1     
    

Nowhere in that statement did NULL appear and yet "IS" produced different results than "==" did.

Not disagreeing with the theory btw - big fan of Codd myself, but reality rarely conforms to ideals.

(10) By Lifepillar (lifepillar) on 2021-08-24 18:51:34 in reply to 9 updated by 10.1 [link] [source]

>you make false claims

No. You are not taking implicit type casting into account. Let's make your query more explicit:

```
sqlite>select 5 = cast(true as int) as "==", cast(5 as boolean) is true as " is ";

==          cast(5 as boolean) is true
----------  --------------------------
0           1
```

From [SQLite's manual](https://www.sqlite.org/lang_expr.html):

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

So, `5 = true` is the same as `5 = 1`, and `5 is true` is the same as `true is true`.

(10.1) By Lifepillar (lifepillar) on 2021-08-24 19:26:48 edited from 10.0 in reply to 9 [link] [source]

you make false claims

No. You are not taking implicit type casting into account. Let's make your query more explicit:

sqlite>select 5 = cast(true as int) as "==", cast(5 as boolean) is true as " is ";

==           is       
----------  ----------
0           1

From SQLite's manual:

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.

So, 5 = true is the same as 5 = 1, and 5 is true is the same as true is true.

(13) By Keith Medcalf (kmedcalf) on 2021-08-24 22:01:39 in reply to 10.1 [link] [source]

The tests IS TRUE and IS FALSE test the ZERO flag.

If the ZERO flag is set in the CPU register when the value is loaded, then the value IS FALSE. If the ZERO flag is not set when the value is loaded into the accumulator, then the value IS TRUE.

In other words, any value that is 0 IS FALSE and any non-zero value IS TRUE.

This has been the state of affairs for about a decade shy of a century.

How people do not know this I cannot fathom.

NULL is neither True nor False (it is NULL). As a conditional, a NULL value evaluates as False.

This issue can probably be attributed to a lack of education and understanding of how digital computers work.

Also note that cast(something as boolean) does nothing because boolean is not an understood type (it means NUMERIC). Therefore a NULL or INTEGER value is unchanged, so cast(5 as boolean) is nothing more than a CPU and RAM intensive way to say 5.

However, in platforms that DO have a boolean type (SQLite3 not being one of them), then a cast to boolean becomes either 0 or 1 depending on whether the ZERO flag is set or not.

Claiming that cast(x as boolean) when x is already either integer or float does anything at all other than waist CPU and MEMORY resources is demonstrating their lack of understanding.

(14) By Lifepillar (lifepillar) on 2021-08-25 08:04:57 in reply to 13 updated by 14.1 [link] [source]

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!

(14.1) By Lifepillar (lifepillar) on 2021-08-25 09:41:32 edited from 14.0 in reply to 13 [link] [source]

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:

  • if both V1 and V2 evaluate to null, the result is False;

  • if V1 is null and V2 is not null, or vice versa, the result is True;

  • 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 = may 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!

(15) By anonymous on 2021-08-25 09:46:51 in reply to 14.0 [link] [source]

For check constraints there is an additional implicit CAST to NUMERIC involved. The outcome of this CAST expression decides whether a constraint is violated. In this case the value of the expression itself, which is NULL, is not directly used to determine result. The CAST value is interpreted in a specific way.

See https://www.sqlite.org/lang_createtable.html section 3.7:

"Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a CAST expression. If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation."

(11) By Lifepillar (lifepillar) on 2021-08-24 19:25:42 in reply to 9 [link] [source]

Not disagreeing with the theory btw - big fan of Codd myself, but reality rarely conforms to ideals.

On the contrary, you should disagree with Codd's “theory” (about the specific topic of nulls—not about the Relational Model, of course!), which is untenable, hence far from ideal (on the other hand, the problem he was trying to address is inherently intractable, as it entails detecting tautologies).

The reality is that SQL conforms really really well to what Codd proposed, despite his proposal being incoherent and, to his own admission, “preliminary and in need of further research”.

For the curious ones: read Codd's paper, §2.3, and compare the definitions therein with the behaviour of y̵o̵u̵r̵ ̵f̵a̵v̵o̵r̵i̵t̵e̵ ̵s̵y̵s̵t̵e̵m̵ SQLite :)

(12) By Lifepillar (lifepillar) on 2021-08-24 20:51:13 in reply to 9 [link] [source]

To be fair, I have not been entirely accurate when I wrote:

Any expression that contains a null IS (the same) as any other expression that contains null etc.

That should read “Any expression that evaluates (or reduces) to null IS (the same) as any other expression that evaluates (or reduces) to null etc.

For instance, true or null is obviously not the same as null.

Ok, enough. Nulls are tricky, avoid them if you can!

(4) By Larry Brasfield (larrybr) on 2021-08-23 13:00:24 in reply to 1 [link] [source]

See expression syntax and explanation of IS and IS NOT.

To answer your question: IS is used when applications or users determine that functionality is needed and act accordingly.

(5) By Richard Hipp (drh) on 2021-08-23 14:27:24 in reply to 1 [link] [source]

The "IS" operator in SQLite works like the "IS NOT DISTINCT FROM" operator in PostgreSQL. Similarly the "IS NOT" operator in SQLite works like the "IS DISTINCT FROM" operator of PostgreSQL.

SQLitePostgreSQL
ISIS NOT DISTINCT FROM
IS NOTIS DISTINCT FROM

(6) By J.M. Aranda (JMAranda) on 2021-08-23 19:39:09 in reply to 5 [link] [source]

SQLite reaches a surprising level of detail.
Greetings and thanks to all.

(7) By Harald Hanche-Olsen (hanche) on 2021-08-24 07:10:42 in reply to 6 updated by 7.1 [link] [source]

Given the fact that SQLite is old enough to drink legally everywhere in the US (I think), you should perhaps not be too surprised.

(7.1) By Harald Hanche-Olsen (hanche) on 2021-08-24 07:15:41 edited from 7.0 in reply to 6 [link] [source]

Given the fact that SQLite is old enough to drink legally everywhere in the US (I think), you should perhaps not be too surprised.

PS. The question reminds me of Bill Clinton, during his impeachment process I believe: It depends what the meaning of “is” is.

(16) By J.M. Aranda (JMAranda) on 2021-09-03 19:25:15 in reply to 7.1 [link] [source]

Amazing that being able to drink legally. Here vintage wine and Serrano ham are a whole culture.