Right, Yinyue. Apologies. I actually thought I had built it. Thanks.
________________________________ From: sqlite-users <[email protected]> on behalf of Xinyue Chen <[email protected]> Sent: Monday, March 2, 2020 06:40 PM To: SQLite mailing list <[email protected]> Subject: Re: [sqlite] Report bug found in SQLite version 3.31.1 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 <[email protected]> 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 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 > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
