Bug in SQLite version 3.31.1 and 3.32?

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug in SQLite version 3.31.1 and 3.32?

Xinyue Chen
Hi,

I am not sure if I found a bug in SQLite version 3.31.1 and 3.32. Could you
please check?

CREATE TABLE t (
  textid TEXT
);
INSERT INTO t
VALUES ('12');
INSERT INTO t
VALUES ('34');
CREATE TABLE i (
  intid INTEGER PRIMARY KEY
);
INSERT INTO i
VALUES (12);
INSERT INTO i
VALUES (34);
CREATE TABLE e (
  x INTEGER PRIMARY KEY NOT NULL,
  y TEXT                NOT NULL
);
-- Original query
select t1.textid a, i.intid b
from t t1,
     i i
where ((t1.textid = i.intid) and (t1.textid = 12));
-- Buggy query
select t1.textid a, i.intid b
from t t1,
     i i
where (((t1.textid = i.intid) and (t1.textid in (12)) and (t1.textid =
i.intid)) or ((t1.textid = null) IS NOT FALSE))
group by i.intid, t1.textid;

The result for the original query is 12|12 but the result for the buggy one
is 12|12, 34|12, 12|34, 34|34. If I change the IS NOT FALSE to IS TRUE, the
result will be 12|12, same to the original query.

Thanks!

Best,
Xinyue Chen
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Richard Hipp-3
On 3/6/20, Xinyue Chen <[hidden email]> wrote:
> -- Buggy query
> select t1.textid a, i.intid b
> from t t1,
>      i i
> where (((t1.textid = i.intid) and (t1.textid in (12)) and (t1.textid =
> i.intid)) or ((t1.textid = null) IS NOT FALSE))
> group by i.intid, t1.textid;


(1) The expression "t1.textid=null" is always NULL.
(2) The expression "NULL IS NOT FALSE" is always true.
(3) The WHERE clause expression "... OR true" is always true.

Hence, the query above simplifies to just "SELECT * FROM t, i;".  That
query should return 4 rows, just as you show.  I think it is working
correctly.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Peter da Silva-2
In reply to this post by Xinyue Chen
Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no value, you have to check for it explicitly.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Xinyue Chen
Hi,

If I change IS NOT FALSE to IS TRUE, the results will be different. I
assume they should perform in the same way?
if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
also be always true. Then why doesn't that query also return 4 rows?
Thanks!

On Fri, Mar 6, 2020 at 5:45 PM Peter da Silva <[hidden email]> wrote:

> Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no
> value, you have to check for it explicitly.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Keith Medcalf
In reply to this post by Xinyue Chen

On Friday, 6 March, 2020 17:48 Xinyue Chen <[hidden email]> wrote:

...

>select t1.textid a, i.intid b
>  from t t1,
>       i i
> where (t1.textid = i.intid and t1.textid in (12) and t1.textid = i.intid)
>    or (t1.textid = null IS NOT FALSE)
>group by i.intid, t1.textid;

I got rid of all the extra brackets to make this easier to read.

The where clause wants either (a bunch of and joined conditions) to be true OR (another condition to be true).  We will ignore the first set of AND joined conditions since they appear to do what you want and instead deal with the handling of NULL values and tri-state logic from the second OR joined condition, which appears to be the one causing problems.

The expression (t1.textid = null) is always null (it is neither True nor False), no matter what the value of t1.textid because any value compared to NULL is NULL.  If you want to know whether t1.textid is null then you write "t1.textid is null" or (conversely) "t1.textid is not null"

NULL is FALSE     -> False (NULL is not False)
NULL is TRUE      -> False (NULL is not True either)
NULL is not FALSE -> True  (it is True that NULL is not FALSE)
NULL is not TRUE  -> True  (it is True that NULL is not TRUE)

Since the condition that you have specified (t1.textid = NULL) IS NOT FALSE will always be true, then the logic value of condition on the "other side" of the OR is irrelevant -- the WHERE clause will always be TRUE.  This condition holds for any not null value you use in place of TRUE or FALSE.  That is:

NULL is 1         -> False (NULL is not 1)
NULL is 0         -> False (NULL is not 0 either)
NULL is not 1     -> True  (NULL is indeed not 1)
NULL is not 2     -> True  (NULL is indeed not 2)

This result will be the same if you change the IS NOT FALSE to IS NOT TRUE.  However, if you specify IS TRUE or IS FALSE then this expression will always be FALSE and the value of the WHERE clause will depend on the result of the first set of AND joined conditions.

So your original query must and always devolves to:

select t1.textid a, i.intid b
from t t1,
     i i
group by i.intid, t1.textid;

for which the correct results are produced.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Peter da Silva-2
In reply to this post by Xinyue Chen
> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?
> if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
> also be always true.

"NULL IS NOT FALSE" is true because NULL is not a value therefor it is not FALSE, because FALSE is a value.
"NULL IS TRUE" is false because NULL is not a value so it's not TRUE.

You can't compare NULL with anything. All you can do is tell if it "IS NULL" or "IS NOT NULL".

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Simon Slavin-3
In reply to this post by Xinyue Chen
On 7 Mar 2020, at 1:52am, Xinyue Chen <[hidden email]> wrote:

> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?

No.  Because NULL is not TRUE and is not FALSE.

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT NULL IS TRUE;
0
sqlite> SELECT NULL IS FALSE;
0
sqlite> SELECT NULL IS NOT TRUE;
1
sqlite> SELECT NULL IS NOT FALSE;
1

Once you can have NULL values, you have to know the rules very well when you apply logic.  Other values make sense, but NULL is not logical.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Igor Tandetnik-2
In reply to this post by Xinyue Chen
On 3/6/2020 8:52 PM, Xinyue Chen wrote:
> If I change IS NOT FALSE to IS TRUE, the results will be different.

NULL IS TRUE is always false. NULL IS NOT FALSE is always true. So it's not surprising that the results are different.

SQL uses trivalent logic. NULL is neither FALSE nor TRUE.

> I assume they should perform in the same way?

You assume incorrectly.

> if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
> also be always true.

False.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Richard Damon
In reply to this post by Simon Slavin-3
On 3/6/20 9:00 PM, Simon Slavin wrote:

> On 7 Mar 2020, at 1:52am, Xinyue Chen <[hidden email]> wrote:
>
>> If I change IS NOT FALSE to IS TRUE, the results will be different. I
>> assume they should perform in the same way?
> No.  Because NULL is not TRUE and is not FALSE.
>
> SQLite version 3.28.0 2019-04-15 14:49:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> SELECT NULL IS TRUE;
> 0
> sqlite> SELECT NULL IS FALSE;
> 0
> sqlite> SELECT NULL IS NOT TRUE;
> 1
> sqlite> SELECT NULL IS NOT FALSE;
> 1
>
> Once you can have NULL values, you have to know the rules very well when you apply logic.  Other values make sense, but NULL is not logical.
It is sort of like NaN, where a Nan is neither less than, greater than
or equal to any value, including itself.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Keith Medcalf

On Friday, 6 March, 2020 19:25, Richard Damon <[hidden email]> wrote:

>It is sort of like NaN, where a Nan is neither less than, greater than
>or equal to any value, including itself.

NULL (as in SQL NULL) means "missing value" or "unknown".  NULL represents any value within the domain, we simply do not know what that value is.  That is, the value "NULL" for colour of a car means that we do not know the colour -- however, it still has one.

NaN, on the other hand, means that the value is outside the domain and that there is no possible value of the domain which well render the proposition true.

For example, the state of Schroedingers Cat is NULL.  It has a state, either dead or alive.  That state is merely unknown until one looks in the box.  However, if when you looked in the box there was no cat, then the cat would be a NaN since its state was outside of the domain of states for a cat in a box with a time release poison after the release of the poison, that is, the non-existance of a cat in the box precludes the possibility of the state of the cat in the box being either either dead or alive.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Richard Damon
On 3/6/20 9:54 PM, Keith Medcalf wrote:

> On Friday, 6 March, 2020 19:25, Richard Damon <[hidden email]> wrote:
>
>> It is sort of like NaN, where a Nan is neither less than, greater than
>> or equal to any value, including itself.
> NULL (as in SQL NULL) means "missing value" or "unknown".  NULL represents any value within the domain, we simply do not know what that value is.  That is, the value "NULL" for colour of a car means that we do not know the colour -- however, it still has one.
>
> NaN, on the other hand, means that the value is outside the domain and that there is no possible value of the domain which well render the proposition true.
>
> For example, the state of Schroedingers Cat is NULL.  It has a state, either dead or alive.  That state is merely unknown until one looks in the box.  However, if when you looked in the box there was no cat, then the cat would be a NaN since its state was outside of the domain of states for a cat in a box with a time release poison after the release of the poison, that is, the non-existance of a cat in the box precludes the possibility of the state of the cat in the box being either either dead or alive.
>
It may have a different meaning, but similar effects on logic.

As an aside, for the quantum effect Schrodinger's Cat is designed to
demonstrate, the cat ISN'T just one of dead or alive but not know which,
but exists as a probability wave between the two states. This is why the
photon which goes through one of two slits generates an interference
pattern unless you detect which slit it goes through, if you measure the
slit it went through, you get a different pattern of light, as the lack
of knowledge allows it to be less precise in its position and the
probability of going through the left slit interferes with the
probability of that same particle going through the right slit, so the
pattern implies it sort of went through both at once.

Detecting the state of Schrodinger's Cat actually changes its state,
collapsing the wave into one of the definitive states.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

R Smith-2
In reply to this post by Xinyue Chen
On 2020/03/07 03:52, Xinyue Chen wrote:
> Hi,
>
> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?
> if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
> also be always true. Then why doesn't that query also return 4 rows?

Some excellent answers were already given, but in case you still wonder...

In Boolean logic, a value can only be TRUE or FALSE. But in SQL (which
has to model the real World Algebraically) there is also the possibility
that the state is simply NOT KNOWN (or indeed that a
variable/placeholder/identifier can represent no value at all).

The expression (Null = Null) is always NULL - it's like asking if an
unknown person is exactly the same person as another unknown person? The
answer is obviously "We don't know. It might be, so we cannot say
definitively it ISN'T the case, but it might also NOT be the same
person, so the only correct answer is: We don't know".
Further, "We don't know" in logic terms is undefined, which in SQL we
write as "NULL".

While (Null = Null) in mathematical terms is always unknown, we can
however test if two values are of the same kind with "is", and more
specifically, test if they are both unknown, so the expression (NULL is
NULL) correctly returns True.

This whole "Three possible states" thing is no longer simply Boolean
logic, but indeed Trivalent logic with the possible values being
NULL/TRUE/FALSE.

Writing the matrix of states of (x IS [NOT] y) down and numbering them
we get 9 symantically distinct evaluations (there are more, like "FALSE
is TRUE", but they can be rearranged as one of these):

1.  NULL is NULL = Yes, True,
2.  NULL is FALSE = Nope, False.
3.  NULL is TRUE  = Nope, False.
4.  NULL is NOT NULL = Nope, False,
5.  NULL is NOT FALSE = Yep, True.
6.  NULL is NOT TRUE = Yep, True.
7. TRUE is FALSE  = Nope, False.
8. TRUE is NOT FALSE = Yep, True.
9. FALSE is NOT TRUE = Yep, True.

Thus when you ask:
"I assume they should perform in the same way?
if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE'
should also be always true. Then why doesn't that query also return 4 rows?"

You assume that state 5 (NULL IS NOT FALSE) and state 3 (NULL IS TRUE)
would mean the same thing, but as you can see from the list, in
Trivalent logic it clealy doesn't - one is False and the other is True.


The stuff of nightmares to a purist, I know. In the real World though,
some stuff simply isn't known and therefore cannot fit into the simple
Boolean logic of TRUE and FALSE.

Best of luck!
Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug in SQLite version 3.31.1 and 3.32?

Joe Mucchiello
In reply to this post by Xinyue Chen
 I just want to point something out that might help the original poster.

    On Saturday, March 7, 2020, 7:00:21 AM EST, [hidden email] <[hidden email]> wrote:  >
> 1.  NULL is NULL = Yes, True,
> 2.  NULL is FALSE = Nope, False.
> 3.  NULL is TRUE  = Nope, False.
> 4.  NULL is NOT NULL = Nope, False,
> 5.  NULL is NOT FALSE = Yep, True.
> 6.  NULL is NOT TRUE = Yep, True.
> 7. TRUE is FALSE  = Nope, False.
> 8. TRUE is NOT FALSE = Yep, True.
> 9. FALSE is NOT TRUE = Yep, True.
This explanation 100% correct and probably 80% confusing without the following, especially because "is" is not capitalized:IS and IS NOT are logical operators in SQL. NOT is not a unary operator when preceded by IS.

In most non-relational languages "NULL IS NOT TRUE" is parsed as:

value(NULL) operator(IS) (operator(NOT) value(TRUE)).

That is NOT how SQL works. In SQL, it is:

value(NULL) operator(IS NOT) value(TRUE).


 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users