SQLite inconsistency behavior with the NOT operator

user_13428065 0
Viewed 41

As for example, I am using this website: https://sqliteonline.com/.

I try to fuzz around with queries and did as such:

SELECT * FROM demo
WHERE Name="SQL " || "Online"

Which correctly retrieved:

1-SQL Online-for Data Science

Now I tried to use the AND operator:

SELECT * FROM demo
WHERE Name="SQL " || "Online" AND Hint=1 IS NOT 2

which retrieved the same answer as before, because 1 is indeed not 2 thus it is Query AND True=>True.

But if I changed it to something else, let's say:

SELECT * FROM demo
WHERE Name="SQL " || "Online" AND Hint=1 IS NOT 1

It returns the same answer as before! even though 1 IS NOT 1 is completely false, as I've seen in this query:

SELECT *, 1 IS NOT 1 AS isTrue FROM demo
WHERE Name="SQL " || "Online" AND Hint=1 IS NOT 1

where isTrue=0

So this query is basically this one:

SELECT *, 1 IS NOT 1 AS isTrue FROM demo
WHERE Name="SQL " || "Online" AND Hint=0

But when I execute this query explicitly, it does not retrieve any results!

What is going on here? I would appreciate your help! Thank you.

operator-precedence where-clause type-conversion sqlite sql
1 Answers

According to Operators, and Parse-Affecting Attributes, your condition is equivalent to:

(Name=("SQL " || "Online")) AND ((Hint=1) IS NOT 2)

because of the operators precedence.

This means that it is the boolean expression Hint=1 that is checked whether it is 2 or not.
A boolean expression like Hint=1 is evaluated as 0 for false or 1 for true.

The column Hint's values are strings and prior to the comparison with 1 or any numeric expression SQLite tries to convert them implicitly to numeric values, but since this is not possible they are converted to 0, so, your condition is equivalent to:

(Name=("SQL " || "Online")) AND ((0=1) IS NOT 2)

and since 0=1 evaluates to 0(= false):

(Name=("SQL " || "Online")) AND (0 IS NOT 2)

or:

(Name=("SQL " || "Online")) AND true

or:

Name="SQL " || "Online"

Note that you would get the same result with:

(Name=("SQL " || "Online")) AND ((Hint=1) IS NOT 1)

But, with

(Name=("SQL " || "Online")) AND ((Hint=1) IS NOT 0)

the result would be:

(Name=("SQL " || "Online")) AND false

which evaluates to just false.

user_104988280
Related