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:
(6) By Yafei (dakangger) on 2022-10-13 22:38:57 in reply to 5 [link] [source]
Thanks for fix.