Could not get expect result by Bloom Filter

(1) By Yafei (dakangger) on 2022-10-05 08:19:21 [link] [source]

SQLite Version 3.39.2

SELECT
    [Invoice].InvoiceID AS InvoiceID
  , [Invoice].CustomerID AS CustomerID
FROM [Invoice]
INNER JOIN [Tasks] ON ([Invoice].DeliveryTaskID = [Tasks].TaskID
OR [Invoice].SalesTaskID = [Tasks].TaskID)
  AND [Tasks].Active = 1
INNER JOIN [Events] ON [Tasks].EventID = [Events].EventID
  AND [Events].Active = 1
INNER JOIN [Plans] ON [Events].PlanID = [Plans].PlanID
WHERE ([Plans].PlanID = '14011')
GROUP BY
    [Invoice].InvoiceID

EXPLAIN QUERY PLAN:
SCAN Invoice
SEARCH Tasks USING INTEGER PRIMARY KEY (rowid=?)
SEARCH Events USING INTEGER PRIMARY KEY (rowid=?)
BLOOM FILTER ON Plans (PlanID=?)
SEARCH Plans USING INTEGER PRIMARY KEY (rowid=?)

After BLOOM FILTER intervened, Could not get expect result.

(2) By Ryan Smith (cuz) on 2022-10-05 09:06:11 in reply to 1 [link] [source]

That statement is devoid of any useful information.

All we can deduce is: You did a thing and it did not work as you expected, and also at some point it used a bloom filter.

We cannot tell what the output should be without a schema and/or data. Maybe your assumptions about the query is wrong? Maybe your expectation is wrong? Maybe the output is wrong? Maybe it's a bug? We cannot know this without more information.

What is the schema of the tables?
What is the output you expected?
What is the output you got?

With the above information it might be possible to reproduce the error, but adding some actual data to demonstrate the problem would help greatly.

(3) By Richard Hipp (drh) on 2022-10-05 09:35:58 in reply to 1 [link] [source]

In the CLI, if you add a line like the following before the query, then Bloom filters will be disabled:

.testctrl optimizations 0x80000

If you can provide us with a complete script (including CREATE TABLE and CREATE INDEX statements and INSERT statements to add appropriate data) that gives different answers with and without disabling Bloom filters, that would be very helpful.

(4) By Yafei (dakangger) on 2022-10-06 02:20:11 in reply to 3 [source]

Thanks for reply,

Please use the DB to test. https://cdn.e8.co/MessageFiles/5f37d7ce-80f9-47db-9c45-a532a9bcdeaa/BloomFilter.db

After change the '14011' to 14011, it works.

Maybe Bloom Filter is sensitive to Column types.

(5) By Dan Kennedy (dan) on 2022-10-06 14:12:34 in reply to 4 [link] [source]

Thanks for reporting this and supplying the db. Should now be fixed here:

https://sqlite.org/src/info/8e14c351b29bb434

Dan.

(6) By Yafei (dakangger) on 2022-10-13 22:38:57 in reply to 5 [link] [source]

Thanks for fix.