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
