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 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 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 TEXT NOT 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
