What version are you using? Because it seems to be working fine for me on 3.28.0 Windows CLI.
SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t0 (c0 primary key desc, c1 unique default null) without rowid; QUERY PLAN `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) sqlite> insert into t0 (c0) values (1), (2), (3), (4), (5); QUERY PLAN `--SCAN 5 CONSTANT ROWS sqlite> reindex; sqlite> select * from t0 where t0.c0 in (select c0 from t0) and t0.c1 isnull; QUERY PLAN |--SEARCH TABLE t0 USING INDEX sqlite_autoindex_t0_2 (c1=? AND c0=?) `--USING INDEX sqlite_autoindex_t0_1 FOR IN-OPERATOR c0|c1 1| 2| 3| 4| 5| sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Luuk Sent: Monday, May 13, 2019 1:53 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
