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.
Best,
Xinyue Chen
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
