Incorrect results with the latest trunk version

(1) By ChiZhang on 2023-02-28 12:32:19 [source]

Consider the following test case:

CREATE TABLE t0 (c0);
INSERT INTO t0 VALUES (0), (0), (0), (' '), (0), (0), (0), (0), (1);
CREATE VIEW v0(c0) AS SELECT DISTINCT t0.c0 FROM t0;

SELECT COUNT(*) FROM t0, v0 WHERE t0.c0 COLLATE RTRIM = '';

For the query, SQLite3 3.41.0 returns 0, which, I believe, is unexpected. Since t0 contains one row for which the WHERE predicate t0.c0 COLLATE RTRIM = '' evaluates to true (namely ' '), and v0 contains three rows, I expect the result to be 3. Note that this reproduces also on the latest trunk version (c1f2a1d5).

(2) By Richard Hipp (drh) on 2023-02-28 14:34:44 in reply to 1 [link] [source]

This yet another bug in the Bloom-filter optimization. The string that consists of a single space (' ') and an empty string ('') hash to different values and so the Bloom filter thinks they are different, even though they are the same according to the RTRIM collating sequence.

Fixed by check-in 090304b870419acb which causes all strings and blobs to hash to the same value. That "fix" makes Bloom filters mostly useless for string or blob equality constraints. But at least they will get the correct answer, now. I will attempt to make Bloom filters useful again for string and blob constraints in follow-on changes.