Ok, well that's weird. The pre-compiled version is following your results. The version I compiled for myself is doing the right thing.
With pre-compiled version: 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> .timer off sqlite> .eqp on sqlite> .nullvalue NuLL sqlite> .version SQLite 3.28.0 2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50 zlib version 1.2.11 gcc-5.2.0 sqlite> CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT 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 5|NuLL sqlite> .exit With my compiled version: 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> .timer off sqlite> .eqp on sqlite> .nullvalue NuLL sqlite> .version SQLite 3.28.0 2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50 gcc-8.1.0 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|NuLL 2|NuLL 3|NuLL 4|NuLL 5|NuLL sqlite> pragma compile_options; compile_options ALLOW_COVERING_INDEX_SCAN COMPILER=gcc-8.1.0 DEFAULT_AUTOMATIC_INDEX DEFAULT_CACHE_SIZE=-65536 DEFAULT_FILE_FORMAT=4 DEFAULT_FOREIGN_KEYS DEFAULT_JOURNAL_SIZE_LIMIT=0 DEFAULT_LOCKING_MODE=0 DEFAULT_MEMSTATUS DEFAULT_MMAP_SIZE=0 DEFAULT_PAGE_SIZE=4096 DEFAULT_SYNCHRONOUS=0 DEFAULT_WAL_AUTOCHECKPOINT=1 DEFAULT_WAL_SYNCHRONOUS=0 DEFAULT_WORKER_THREADS=4 ENABLE_COLUMN_METADATA ENABLE_DBSTAT_VTAB ENABLE_MEMORY_MANAGEMENT ENABLE_RTREE ENABLE_STMT_SCANSTATUS ENABLE_UNKNOWN_SQL_FUNCTION LIKE_DOESNT_MATCH_BLOBS MAX_ATTACHED=125 MAX_EXPR_DEPTH=0 MAX_MMAP_SIZE=0 MAX_WORKER_THREADS=4 OMIT_SHARED_CACHE STMTJRNL_SPILL=4194304 THREADSAFE=0 USE_ALLOCA sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Luuk Sent: Monday, May 13, 2019 2:11 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 20:06, David Raymond wrote: > What version are you using? Because it seems to be working fine for me on > 3.28.0 Windows CLI. > D:\TEMP>sqlite3 SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. _______________________________________________ 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
