REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

Manuel Rigger
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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

Luuk

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

David Raymond
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:[hidden email]] On Behalf Of Luuk
Sent: Monday, May 13, 2019 1:53 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

Luuk

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

David Raymond
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:[hidden email]] On Behalf Of Luuk
Sent: Monday, May 13, 2019 2:11 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users