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 <jic...@outlook.com> 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 > 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
