Re: [sqlite] Partial Index on "~" malfunctions when used with likely/unlikely
Thanks for the explanation and the quick fix! Best, Manuel On Sat, May 4, 2019 at 7:41 PM Richard Hipp wrote: > Here is another case: > > CREATE TABLE t1(a,b,c); > INSERT INTO t1 VALUES(NULL,8,'yes'); > CREATE INDEX t1b ON t1(b) WHERE a IS NOT NULL; > SELECT c FROM t1 WHERE b=8 AND (a OR 1); > > The problem was in the theorem prover that determines when a partial > index can be used. The problem goes all the way back to the initial > introduction of partial indexes in SQLite version 3.8.0 (2013-08-26). > The theorem prover was (incorrectly) assuming that if the expression > "a OR 1" is true, then "a IS NOT NULL" must also be true. And that > assumption is correct for most binary operators - just not for OR. > Fixed now. > > On 5/4/19, Manuel Rigger wrote: > > This similar test case, that I just found now, demonstrates that this > could > > be a pattern that is used in practice (TRUE can also be computed): > > > > CREATE TABLE t0 (c0); > > CREATE INDEX index_0 ON t0(c0) WHERE c0 NOTNULL; > > INSERT INTO t0(c0) VALUES (NULL); > > SELECT * FROM t0 WHERE (c0 OR TRUE); > > > > Also here, the row is not fetched. > > > > Best, > > Manuel > > > > On Sat, May 4, 2019 at 3:45 PM Manuel Rigger > > wrote: > > > >> Hi, > >> > >> I discovered a bug, which is demonstrated through the following test > case: > >> > >> CREATE TABLE t0(c0); > >> CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL; > >> INSERT INTO t0(c0) VALUES (NULL); > >> SELECT * FROM t0 WHERE (LIKELY(~c0) OR TRUE); > >> > >> No row is fetched, although the WHERE clause is always TRUE. I could > >> reproduce this bug only when creating a partial index, and when using > >> either LIKELY or UNLIKELY. The datatype of the c0 column seems to > >> irrelevant. PRAGMA integrity_check; and REINDEX could not detect this > >> error. > >> > >> Best, > >> Manuel > >> > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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
Re: [sqlite] Partial Index on "~" malfunctions when used with likely/unlikely
Here is another case: CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(NULL,8,'yes'); CREATE INDEX t1b ON t1(b) WHERE a IS NOT NULL; SELECT c FROM t1 WHERE b=8 AND (a OR 1); The problem was in the theorem prover that determines when a partial index can be used. The problem goes all the way back to the initial introduction of partial indexes in SQLite version 3.8.0 (2013-08-26). The theorem prover was (incorrectly) assuming that if the expression "a OR 1" is true, then "a IS NOT NULL" must also be true. And that assumption is correct for most binary operators - just not for OR. Fixed now. On 5/4/19, Manuel Rigger wrote: > This similar test case, that I just found now, demonstrates that this could > be a pattern that is used in practice (TRUE can also be computed): > > CREATE TABLE t0 (c0); > CREATE INDEX index_0 ON t0(c0) WHERE c0 NOTNULL; > INSERT INTO t0(c0) VALUES (NULL); > SELECT * FROM t0 WHERE (c0 OR TRUE); > > Also here, the row is not fetched. > > Best, > Manuel > > On Sat, May 4, 2019 at 3:45 PM Manuel Rigger > wrote: > >> Hi, >> >> I discovered a bug, which is demonstrated through the following test case: >> >> CREATE TABLE t0(c0); >> CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL; >> INSERT INTO t0(c0) VALUES (NULL); >> SELECT * FROM t0 WHERE (LIKELY(~c0) OR TRUE); >> >> No row is fetched, although the WHERE clause is always TRUE. I could >> reproduce this bug only when creating a partial index, and when using >> either LIKELY or UNLIKELY. The datatype of the c0 column seems to >> irrelevant. PRAGMA integrity_check; and REINDEX could not detect this >> error. >> >> Best, >> Manuel >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Partial Index on "~" malfunctions when used with likely/unlikely
This similar test case, that I just found now, demonstrates that this could be a pattern that is used in practice (TRUE can also be computed): CREATE TABLE t0 (c0); CREATE INDEX index_0 ON t0(c0) WHERE c0 NOTNULL; INSERT INTO t0(c0) VALUES (NULL); SELECT * FROM t0 WHERE (c0 OR TRUE); Also here, the row is not fetched. Best, Manuel On Sat, May 4, 2019 at 3:45 PM Manuel Rigger wrote: > Hi, > > I discovered a bug, which is demonstrated through the following test case: > > CREATE TABLE t0(c0); > CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL; > INSERT INTO t0(c0) VALUES (NULL); > SELECT * FROM t0 WHERE (LIKELY(~c0) OR TRUE); > > No row is fetched, although the WHERE clause is always TRUE. I could > reproduce this bug only when creating a partial index, and when using > either LIKELY or UNLIKELY. The datatype of the c0 column seems to > irrelevant. PRAGMA integrity_check; and REINDEX could not detect this error. > > Best, > Manuel > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Partial Index on "~" malfunctions when used with likely/unlikely
Hi, I discovered a bug, which is demonstrated through the following test case: CREATE TABLE t0(c0); CREATE INDEX index_0 ON t0(c0) WHERE (~c0) NOT NULL; INSERT INTO t0(c0) VALUES (NULL); SELECT * FROM t0 WHERE (LIKELY(~c0) OR TRUE); No row is fetched, although the WHERE clause is always TRUE. I could reproduce this bug only when creating a partial index, and when using either LIKELY or UNLIKELY. The datatype of the c0 column seems to irrelevant. PRAGMA integrity_check; and REINDEX could not detect this error. Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
