[sqlite] Report bug found in SQLite version 3.31.1

2020-03-02 Thread Xinyue Chen 
Hi,

I found a bug in the most recent SQLite release version 3.31.1 2020-01-27.
My initial test environment is macOS 10.14.6 (18G87) and I have tested in
https://sqliteonline.com/.

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 TEXTNOT 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 distinct t1.textid a, i.intid b
from t t1,
 i i,
 t vucjp
where ((t1.textid = i.intid) and (t1.textid = 12) and (t1.textid =
vucjp.textid) and (t1.textid = vucjp.textid));

The results for the two queries should be the same, but the result for the
first one is 12|12 and for the second one is 12|12, 34|12.

Best,
Xinyue Chen
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Report bug found in SQLite version 3.31.1

2020-03-02 Thread Xinyue Chen 
Hi josé,

This bug is found in 3.31.1 but you are running it in 3.30.1.

Best,
Xinyue Chen

On Mon, Mar 2, 2020 at 3:36 PM Jose Isaias Cabrera 
wrote:

> Xinyue Chen, on Monday, March 2, 2020 06:21 PM, wrote...
> >
> > Hi,
> >
> > I found a bug in the most recent SQLite release version 3.31.1
> 2020-01-27.
> > My initial test environment is macOS 10.14.6 (18G87) and I have tested in
> > https://sqliteonline.com/.
> >
> > 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 TEXTNOT 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 distinct t1.textid a, i.intid b
> > from t t1,
> >  i i,
> >  t vucjp
> > where ((t1.textid = i.intid) and (t1.textid = 12) and (t1.textid =
> > vucjp.textid) and (t1.textid = vucjp.textid));
> >
> > The results for the two queries should be the same, but the result for
> the
> > first one is 12|12 and for the second one is 12|12, 34|12.
> I get 12|12 for both query.
>
> 18:00:22.46>sqlite3
> SQLite version 3.30.1 2019-10-10 20:19:45
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE t (
>...>   textid TEXT
>...> );
> sqlite> INSERT INTO t
>...> VALUES ('12');
> sqlite> INSERT INTO t
>...> VALUES ('34');
> sqlite> CREATE TABLE i (
>...>   intid INTEGER PRIMARY KEY
>...> );
> sqlite> INSERT INTO i
>...> VALUES (12);
> sqlite> INSERT INTO i
>...> VALUES (34);
> sqlite> CREATE TABLE e (
>...>   x INTEGER PRIMARY KEY NOT NULL,
>...>   y TEXTNOT NULL
>...> );
> sqlite> -- Original query
> sqlite> select t1.textid a, i.intid b
>...> from t t1,
>...>  i i
>...> where ((t1.textid = i.intid) and (t1.textid = 12));
> 12|12
> sqlite> -- Buggy query
> sqlite> select distinct t1.textid a, i.intid b
>...> from t t1,
>...>  i i,
>...>  t vucjp
>...> where ((t1.textid = i.intid) and (t1.textid = 12) and (t1.textid =
>...> vucjp.textid) and (t1.textid = vucjp.textid));
> 12|12
> sqlite>
>
> Maybe I am not following your steps, but as you can see above, I am
> getting the same for both.
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

2020-03-06 Thread 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  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in SQLite version 3.31.1 and 3.32?

2020-03-06 Thread 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 TEXTNOT 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users