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); >

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

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,

[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