Bug 144253 - Filtering does not work with formatted calculated values
Summary: Filtering does not work with formatted calculated values
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.2.0.4 release
Hardware: All All
: medium normal
Assignee: Balázs Varga
URL:
Whiteboard: target:7.3.0 target:7.2.3
Keywords: bibisected, bisected, regression
Depends on:
Blocks: AutoFilter
  Show dependency treegraph
 
Reported: 2021-09-02 00:52 UTC by dongshili
Modified: 2021-09-29 04:51 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
About the functionality of standard filters (6.73 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-09-02 00:52 UTC, dongshili
Details
The example file in Excel and Calc side by side when filtering them (114.58 KB, image/png)
2021-09-03 10:14 UTC, NISZ LibreOffice Team
Details
The example file in Excel and Calc after filtering them (64.08 KB, image/png)
2021-09-03 10:15 UTC, NISZ LibreOffice Team
Details
Minimal reproducer made in current Calc master (9.98 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-09-03 10:24 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description dongshili 2021-09-02 00:52:08 UTC
Description:
工作表显示两位小数的近似值,筛选器里显示真实值,导致无法筛选
The worksheet shows the approximate value of two decimal places, and the filter shows the real value, so it cannot be filtered

Steps to Reproduce:
1.Open my attachment and filter for quantity
2.
3.

Actual Results:
当筛选条件是一组非整数的值,单元格内设置保留2位小数,但是在筛选时显示的是一个保留13位小数的真实值,导致无法依据2位小数进行筛选。
When the filter condition is a group of non integer values, 2 decimal places are reserved in the cell setting, but a real value with 13 decimal places is displayed during filtering, so it is impossible to filter according to 2 decimal places.

Expected Results:
我认为这是个体验的问题,筛选这个事,从体验和逻辑上来看,就应该是所见即所得,应该采用看得见的相对值,而不是绝对值,因为到底筛选的精度如何,需要筛选人来通过小数位数来控制,在7.0.6和7.1.5版本的测试中,虽然可以筛选,但是筛选器也是绝对值,这个不是很合理。
I think this is a matter of experience. From the perspective of experience and logic, screening should be what you see is what you get, and the visible relative value should be used instead of the absolute value, because the accuracy of screening needs to be controlled by the filter through the decimal places. In the tests of versions 7.0.6 and 7.1.5, although it can be screened, the filter is also an absolute value, This is not very reasonable.

For example, in most work and life scenarios, we can keep two digits. Generally speaking, there is no difference between 95.554 and 95.553 for the data analysis of non military scientific research. After all rounding, it is 95.55, and the filter search 95.55 is two. If you use the absolute value to screen, you can only see one. Of course, you can check the similar ones yourself, But this is less practical.
举个例子,大多数工作和生活场景下,我们保留2位即可,一般来说,95.554和95.553 对于做非军工科研的数据分析来说,没有区别,所有四舍五入后就是95.55,筛选器搜95.55就是2条,如果用绝对值筛选,那么只能看到一条,当然你自己去挑看起来差不多的都勾上也行,但是这个就比较不实用了。


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
Version: 7.2.0.4 (x64) / LibreOffice Community
Build ID: 9a9c6381e3f7a62afc1329bd359cc48accb6435b
CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: zh-CN (zh_CN); UI: zh-CN
Calc: CL
Comment 1 dongshili 2021-09-02 00:52:45 UTC
Created attachment 174714 [details]
About the functionality of standard filters
Comment 2 NISZ LibreOffice Team 2021-09-03 10:14:15 UTC
Created attachment 174755 [details]
The example file in Excel and Calc side by side when filtering them
Comment 3 NISZ LibreOffice Team 2021-09-03 10:15:46 UTC
Created attachment 174756 [details]
The example file in Excel and Calc after filtering them

Confirming with:

Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: bc7baa18435000f47f90e47d3300710bcb4cf56b
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: CL

Filtering for formatted values makes all of them disappear.
Comment 4 NISZ LibreOffice Team 2021-09-03 10:18:56 UTC
Seems to have started with

https://cgit.freedesktop.org/libreoffice/core/commit/?id=3069df790cca2917e5aedd87bac1af65f9605d51

author	Balazs Varga <balazs.varga991@gmail.com>	2021-07-02 09:40:32 +0200
committer	Xisco Fauli <xiscofauli@libreoffice.org>	2021-07-08 09:50:09 +0200

tdf#142910 sc filter: fix "greater than" or "smaller than" etc
Comment 5 NISZ LibreOffice Team 2021-09-03 10:24:02 UTC
Created attachment 174757 [details]
Minimal reproducer made in current Calc master

This is not xlsx-specific. Filtering this file also reproduces the issue.

There is another issue: the Autofilter dropdown and the Standard filter dialog does not display these calculated number values (but not with constant values!) according to the cell format. This seems to be an independent problem, I'll file another bug for that.
Comment 6 dongshili 2021-09-04 00:17:00 UTC
Thank you for your careful test and reply! Look forward to libreoffice getting better and better!
Comment 7 Commit Notification 2021-09-21 09:06:55 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/51375b48378915b6e95c1ac26b2ccf8e39880f7e

tdf#144253 tdf#144324 sc filter: use formatted values in filters

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:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2021-09-21 12:26:51 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/73a0deeda1bc63e78de6aa3df9c3324b4ebe0d3b

tdf#144253 tdf#144324 sc filter: use formatted values in filters

It will be available in 7.2.2.

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:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 dongshili 2021-09-21 12:36:39 UTC
Thank you very much for solving this problem!
Comment 10 dongshili 2021-09-24 00:37:56 UTC
I have tested the 7.2.3 dev release and confirmed that this issue has been fixed. Thank you very much to the developers and everyone who worked on this issue!
Comment 11 Kevin Suo 2021-09-24 01:20:00 UTC
(In reply to dongshili from comment #10)
Mark as VEIFIED FIXED accordingly.
Comment 12 Commit Notification 2021-09-28 00:26:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b60b6bfaafa1315e07108dba50f016975b619c59

Resolves: tdf#144740 Fix broken compareByValue() query, tdf#142910 tdf#144253

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:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2021-09-28 09:29:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/825a96114a7693335be000b2aa661df88a02e273

Resolves: tdf#144740 Fix broken compareByValue() query, tdf#142910 tdf#144253

It will be available in 7.2.3.

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:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2021-09-29 04:33:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-2-2":

https://git.libreoffice.org/core/commit/dc1642c3225ff4aeada5df749946f406772eb1ff

Resolves: tdf#144740 Fix broken compareByValue() query, tdf#142910 tdf#144253

It will be available in 7.2.2.

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:
https://wiki.documentfoundation.org/Testing_Daily_Builds

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