Thanks for the explanation and the quick fix! Best, Manuel
On Sat, May 4, 2019 at 7:41 PM Richard Hipp <d...@sqlite.org> 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 <rigger.man...@gmail.com> 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 <rigger.man...@gmail.com> > > 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
