On 13-5-2019 19:36, Manuel Rigger wrote:
Hi everyone,
Consider the following test case:
CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID;
INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
REINDEX;
SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 ISNULL; --
returns 5|
I would expect that the SELECT statement fetches the five rows, however,
only one is fetched. Only when omitting the REINDEX are all five rows
fetched.
Best,
Manuel
___________
sqlite> SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1
ISNULL;
c0|c1
5|
sqlite> SELECT * FROM t0 WHERE (1 or t0.c0 IN (SELECT c0 FROM t0)) AND
(1 or t0.c1 ISNULL);
c0|c1
5|
4|
3|
2|
1|
sqlite> SELECT * FROM t0 WHERE (0 or t0.c0 IN (SELECT c0 FROM t0)) AND
(0 or t0.c1 ISNULL);
c0|c1
5|
4|
3|
2|
1|
sqlite>
The first attempt (adding '1 or') seems to be tooo obvious, but the
second ... ;-)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
