Bug 136838 - AutoFilter Improvement: Use "Not Equal" (i.e., <>) when we selected all but one item
Summary: AutoFilter Improvement: Use "Not Equal" (i.e., <>) when we selected all but o...
Status: RESOLVED DUPLICATE of bug 133835
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All Linux (All)
: medium enhancement
Assignee: Not Assigned
Whiteboard: target:7.3.0
Depends on:
Blocks: AutoFilter
  Show dependency treegraph
Reported: 2020-09-17 08:47 UTC by Kevin Suo
Modified: 2021-12-04 00:27 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

test ODS file with autofilter (68.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-09-17 08:47 UTC, Kevin Suo
Another test ods file (5.36 MB, application/vnd.oasis.opendocument.spreadsheet)
2021-11-21 15:53 UTC, Kevin Suo

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2020-09-17 08:47:07 UTC
Created attachment 165612 [details]
test ODS file with autofilter

Assume a column contains n items. When we open the autofilter and un-selected one, then we have n-1 items selected. Doing this filter with AutoFilter is slow because it seems to be using the "x in n-1" logic. However, doing this filter with Standard Filter is fast if we use "Not Equal" logic.

Steps to Reproduce:

1. Open the attached test ODS file

2. Click the autofilter in column A, and de-select the 1st item (i.e., #N/A), then click OK.
--> Slow, it takes more than 8s.

3. Use Standard Filter, and set condition "POMatch <> #N/A", then click OK.
--> Fast, it takes 1s.

Apparently, if we deselect only one item, the condition is the same as <> that item. It should be more expensive to compare the value to see whether it is in the n-1 list.

Actually, if we de-select several items (e.g., 3 items), it would still be faster to use "(x <> a) and (x <> b) and (x <> c)". However, if the deselected items are quite a lot, the "x in n-1" logic should be faster.
Comment 1 Kevin Suo 2020-09-17 12:48:37 UTC

*** This bug has been marked as a duplicate of bug 133835 ***
Comment 2 Kevin Suo 2021-11-21 15:53:24 UTC
Created attachment 176404 [details]
Another test ods file

Deselecting autofilter items in columns O, P, S will freeze Calc as there are many unique values in these columns.
Comment 3 Commit Notification 2021-11-25 13:28:32 UTC
Luboš Luňák committed a patch related to this issue.
It has been pushed to "master":


sort, cache and binary search query items if they're many (tdf#136838)

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:

Affected users are encouraged to test the fix and report feedback.