Bug 106214 - Filtering on date columns doesn't work with autofilter
Summary: Filtering on date columns doesn't work with autofilter
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.6.1 rc
Hardware: All All
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:5.4.0 target:5.3.3
Keywords: bibisected, bisected, regression
: 106481 106870 107014 107130 107132 107245 107315 107472 107997 108047 108425 (view as bug list)
Depends on:
Blocks: AutoFilter
  Show dependency treegraph
 
Reported: 2017-02-27 11:08 UTC by Shadders
Modified: 2018-10-01 01:00 UTC (History)
22 users (show)

See Also:
Crash report or crash signature:


Attachments
Example Calc Spreadsheet of Autofilter Failing (9.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-02-27 12:52 UTC, Shadders
Details
extended spreadsheet to demostrate the result of my test (12.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-15 20:59 UTC, Stefan_Lange_KA@T-Online.de
Details
Not work filter on date (83.33 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-09-10 07:13 UTC, Mohn
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Shadders 2017-02-27 11:08:14 UTC
Hi,

Version 5.2.6.1 Calc - Autofilter does not select the entries, and all entries are not displayed.

Was working in 5.2.5.1.

Regards,

Shadders.
Comment 1 Aron Budea 2017-02-27 11:54:52 UTC
Hi, please attach a small example with reproduction steps.
Comment 2 Shadders 2017-02-27 12:50:52 UTC
Hi,

Responding to request :

1. Select the top row of the data.
2. Select from the Menu -> Data -> Autofilter
3. Data is selected.
4. Select one of the filter arrows on a column
5. Select the select "All" button - which deselects all entries.
6. Navigate to select the last entry - example i am using is the date
7. Once this data is selected, the cells required are not displayed and only the top row is visible. 

Warning - when you select Autofilter from the Data menu - and you do not state that the top row is the heading, hen using the Autofilter all data disappears. 

Will upload the example i am using.

Regards,

Shadders.
Comment 3 Shadders 2017-02-27 12:52:17 UTC
Created attachment 131507 [details]
Example Calc Spreadsheet of Autofilter Failing

Hi,

As requested - example spreadsheet attached. 

Regards,

Shadders.
Comment 4 Xavier Van Wijmeersch 2017-02-27 13:12:37 UTC
same for me in 5.3.2 and alpha 5.4.0+

Version: 5.3.2.0.0+
Build ID: 96b7d1d5d6e97078508f9366b340c65ab0a0cc75
CPU Threads: 8; OS Version: Linux 4.4; UI Render: default; VCL: kde4; Layout Engine: new; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-5-3, Time: 2017-02-25_11:43:12
Locale: nl-BE (en_US.UTF-8); Calc: group

Version: 5.4.0.0.alpha0+
Build ID: 73f2944618e637762125fabd214b09b4fa50fb90
CPU threads: 8; OS: Linux 4.4; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-02-25_03:28:00
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 5 Stefan_Lange_KA@T-Online.de 2017-02-27 23:33:41 UTC
Addition from test with my own documents and with the attached sheet document:
The Filter problem only exists for columns with data type = Date (columns H ... M in the document). For other data types the filter works properly (columns B ... G in the document).

same behavior with 

Version: 5.4.0.0.alpha0+
Build-ID: eb7b03b052ffe8c2c577b2349987653db6c53f76
CPU-Threads: 2; BS-Version: Windows 6.2; UI-Render: Standard; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2017-02-26_22:34:18
Gebietsschema: de-DE (de_DE); Calc: group

and with

Version: 5.3.1.1 (x64)
Build-ID: 72fee18f394a980128dc111963f2eefb05998eeb
CPU-Threads: 4; BS-Version: Windows 6.19; UI-Render: Standard; Layout-Engine: neu; 
Gebietsschema: de-DE (de_DE); Calc: group

but not with

Version: 5.3.0.3 (x64)
Build-ID: 7074905676c47b82bbcfbea1aeefc84afe1c50e1
CPU-Threads: 4; BS-Version: Windows 6.19; UI-Render: Standard; Layout-Engine: neu; 
Gebietsschema: de-DE (de_DE); Calc: group
Comment 6 Aron Budea 2017-02-28 04:28:06 UTC
Thanks for the sample and the confirmation. Using repo bibisect-win32-5.4 I can see this regression started from the commit referenced below.
Adding Cc: to Kohei Yoshida, please take a look.

https://cgit.freedesktop.org/libreoffice/core/commit/?id=42e472b5870278058537d43d03d457dc80b16166
author		Kohei Yoshida <kohei.yoshida@collabora.com>	2017-01-31 03:01:45 (GMT)
committer	Kohei Yoshida <libreoffice@kohei.us>	2017-02-02 05:01:53 (GMT)

"tdf#105629: Let's move the advanced-filter specific stuff ..."
Comment 7 Xisco Faulí 2017-03-10 18:36:02 UTC
*** Bug 106481 has been marked as a duplicate of this bug. ***
Comment 8 Stefan_Lange_KA@T-Online.de 2017-03-13 18:36:38 UTC
Hi,
is anybody looking for this problem? It would be good if it could be solved at least in the next version LO 5.3.2.
If it is too hard to find the reason and to solve the problem, I think one should think about to retire the patch for Bug 105629. IMHO it is more serious if filter on date columns doesn't work as if filter on time values doesn't work.
Comment 9 Stefan_Lange_KA@T-Online.de 2017-03-15 20:59:57 UTC
Created attachment 131914 [details]
extended spreadsheet to demostrate the result of my test

I have made a new test with several formats of the date cells. To demonstrate the results, I have attached the document "test-case+Datum.ods" (= modified file "test-case.ods").
- When date cells are formatted without time, the autofilter delivers no results. This is not influenced by the date format --> Columns C + D in the attached document 
- When date cells are formatted with time, the autofilter works properly. This is not influenced by the date format - also when a user defined format is used.  --> Columns E + F (user defined format) in the attached document
Comment 10 Chris Jackson 2017-03-20 08:54:01 UTC
I have the same problem and have discovered that it appears to be a mismatch between the date format used in the Autofilter and the format you use in the date column of your spreadsheet. This bug has been introduced in version 5.2.6, it was working fine before.

I found this out - and in the process found a temporary workaround - by selecting 'Standard filter...' from the dropdown box created by Autofilter on the date column I want to filter on.

The dates in this column of my spreadsheet are in DD/MM/YY format e.g. 19/03/17

However, when I click on 'Standard filter...' option, the resulting dialogue box displays 3 fields across the top - Field Name, Condition and Value. The 'Value' field contains the date in YYYY-MM-DD format e.g. 2017-03-19. When I select the date from this field, the autofilter works and displays all dates containing 19/03/17.

So - in my case anyway - the autofilter is using YYYY-MM-DD date format, whilst my date values in the spreadsheet are in DD/MM/YY format; hence there is a mismatch and that is why the autofilter is not working.

To get round this, you can either use the Standard filter function as described above, or you can reformat your date column into YYYY-MM-DD format (or whichever format is being used in your Standard filter option) and then use Autofilter as normal.

Not ideal, but it's a workaround. It's a bug that definitely needs fixing. I've tried altering language and date settings in Preferences but to no avail.

Hope this helps.

Chris
Comment 11 Stefan_Lange_KA@T-Online.de 2017-03-20 21:42:42 UTC
With my Comment 9 I was wrong: When the date is formated as date + time, the filter works. But in this case not the date autofilter with year-month-day is shown, but the "normal" text autofilter.
Comment 12 Commit Notification 2017-03-27 00:56:48 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b3d498755238cb0d9a7a3e33b6070c1e4c0e3482

handle date autofilter entries correctly, tdf#106214

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2017-03-27 00:58:26 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5d420856d432ff178560b2be37d33c6ddc439397

add test for tdf#106214

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 14 Stefan_Lange_KA@T-Online.de 2017-03-27 07:31:10 UTC
I have tested with spreadsheet "2017 02 27 LibreOffice AutoFiilter Bug.ods" and with my own spreadsheets.
Result: Autofilter works correctly also on date columns!
Many thanks!

Test with:
Version: 5.4.0.0.alpha0+ (x64)
Build-ID: 23282b476f094c735ecfae4f82798e36ac9cbbc9
CPU-Threads: 2; BS-Version: Windows 6.19; UI-Render: Standard; 
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2017-03-27_02:18:57
Gebietsschema: de-DE (de_DE); Calc: group
Comment 15 Jacques Guilleron 2017-03-30 14:27:10 UTC
*** Bug 106870 has been marked as a duplicate of this bug. ***
Comment 16 Commit Notification 2017-03-30 22:17:10 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=99744cb30435f2158d29967b77d08d0e4f79492c&h=libreoffice-5-3

handle date autofilter entries correctly, tdf#106214

It will be available in 5.3.3.

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

Affected users are encouraged to test the fix and report feedback.
Comment 17 Eike Rathke 2017-04-07 12:39:16 UTC
*** Bug 107014 has been marked as a duplicate of this bug. ***
Comment 18 Xisco Faulí 2017-04-12 20:33:16 UTC
*** Bug 107130 has been marked as a duplicate of this bug. ***
Comment 19 Xisco Faulí 2017-04-13 09:53:58 UTC
*** Bug 107132 has been marked as a duplicate of this bug. ***
Comment 20 Xisco Faulí 2017-04-18 16:52:53 UTC
*** Bug 107245 has been marked as a duplicate of this bug. ***
Comment 21 Xisco Faulí 2017-04-21 09:32:18 UTC
*** Bug 107315 has been marked as a duplicate of this bug. ***
Comment 22 Stefan_Lange_KA@T-Online.de 2017-04-24 21:18:41 UTC
Test with
Version: 5.3.3.1 (x64)
Build-ID: 46360c72c4823cefeaa85af537fba22bd568da7e
CPU-Threads: 4; BS-Version: Windows 6.19; UI-Render: Standard; Layout-Engine: neu; 
Gebietsschema: de-DE (de_DE); Calc: group
was succesful!
Tested with with spreadsheet "2017 02 27 LibreOffice AutoFiilter Bug.ods" and with my own spreadsheets.
Comment 23 Iván Baldo 2017-04-27 20:04:04 UTC
Any plans to fix it in 5.2? Debian Stretch will ship with that version...
Thanks a lot!
Comment 24 Aron Budea 2017-04-30 01:53:23 UTC
LibreOffice 5.2.7.2 has been tagged and built already, which should become 5.2.7 unless there's need for an emergency RC, and the lifecycle of 5.2 ends with the 5.2.7 update. I'm afraid it's too late now to get the fix in any official build of 5.2.
Comment 25 Jacques Guilleron 2017-05-22 14:38:30 UTC
*** Bug 107997 has been marked as a duplicate of this bug. ***
Comment 26 Mike Kaganski 2017-05-24 08:08:43 UTC
*** Bug 108047 has been marked as a duplicate of this bug. ***
Comment 27 Xisco Faulí 2017-06-09 08:46:08 UTC
*** Bug 108425 has been marked as a duplicate of this bug. ***
Comment 28 Xisco Faulí 2017-07-24 14:34:57 UTC
*** Bug 106870 has been marked as a duplicate of this bug. ***
Comment 29 Massimo 2017-07-26 16:35:10 UTC
Libre Office Version 5.2.7.2 x64 on Windows 7

Using the Auto Filter on a column filled with date (dd/mm/yy) and filtering by selecting the check box displayed on the pop-up window all data disappear.

Using the option Standard Filter and filling the dialog box with the same values the filter work correctly.

The same problem on Version 5.2.6.

The filter worked ok up to Version 5.2.5.1
Comment 30 Markus Mohrhard 2017-07-26 18:45:20 UTC
(In reply to Massimo from comment #29)
> Libre Office Version 5.2.7.2 x64 on Windows 7
> 
> Using the Auto Filter on a column filled with date (dd/mm/yy) and filtering
> by selecting the check box displayed on the pop-up window all data disappear.
> 
> Using the option Standard Filter and filling the dialog box with the same
> values the filter work correctly.
> 
> The same problem on Version 5.2.6.
> 
> The filter worked ok up to Version 5.2.5.1

Check the whiteboard. This bug was only fixed in 5.3 and later. Please don't reopen such bug reports until you clearly made sure that it should be reopened.
Comment 31 Mohn 2017-09-10 07:13:49 UTC
Created attachment 136143 [details]
Not work filter on date

Hi

In my file not work filter on date.

LO v, 5.4.0.3
OS: Windows 10
ID сборки: 7556cbc6811c9d992f4064ab9287069087d7f62c
Comment 32 Stefan_Lange_KA@T-Online.de 2017-09-10 08:52:56 UTC
IMHO the behavior I see with the attached file is the same as described in Bug 112258. It is already fixed in LO 6.0.0 by patch of Eike Rathke.
Comment 33 Stefan_Lange_KA@T-Online.de 2017-09-10 18:51:52 UTC
Sorry, I haven't seen until now that there is a way to reply a comment.
 
Comment 32 is the answer to Comment 31 and with "attached file" is meant attachment 136143 [details] from Comment 31.
Comment 34 Nikolay 2017-09-10 20:29:41 UTC
(In reply to Mohn from comment #31)
> Created attachment 136143 [details]
> Not work filter on date
> 
> Hi
> 
> In my file not work filter on date.
> 
> LO v, 5.4.0.3
> OS: Windows 10
> ID сборки: 7556cbc6811c9d992f4064ab9287069087d7f62c

Version: 5.4.1.2 (x64)
ID сборки: ea7cb86e6eeb2bf3a5af73a8f7777ac570321527
OS:Windows 6.19 (Windows 10)

"Filter on date" works in your file.
Update LibreOffice.
Comment 35 Stefan_Lange_KA@T-Online.de 2017-09-10 21:45:06 UTC
(In reply to Nikolay from comment #34)
> (In reply to Mohn from comment #31)
> > Created attachment 136143 [details]
> > Not work filter on date
> > 
> > Hi
> > 
> > In my file not work filter on date.
> > 
> > LO v, 5.4.0.3
> > OS: Windows 10
> > ID сборки: 7556cbc6811c9d992f4064ab9287069087d7f62c
> 
> Version: 5.4.1.2 (x64)
> ID сборки: ea7cb86e6eeb2bf3a5af73a8f7777ac570321527
> OS:Windows 6.19 (Windows 10)
> 
> "Filter on date" works in your file.
> Update LibreOffice.

Hi,
are you sure, filter on date works correct in LO 5.4.1.2 (x64)?

I have also tested with this version, but in german, means
Version: 5.4.1.2 (x64)
Build-ID: ea7cb86e6eeb2bf3a5af73a8f7777ac570321527
CPU-Threads: 4; Betriebssystem:Windows 6.19; UI-Render: GL; 
Gebietsschema: de-DE (de_DE); Calc: group

Some examples, how filter works:
- filter on "Месяц" only 05/2016 ... 08/2016 or only 05/2017 .... 08/2017: filtered are rows with month 05 ... 08, but from both years -> wrong
- filter on "Месяц" 01/2017 ... 04/2017: filtered are only rows with the selected months -> OK
- filter on "Месяц" 09/2016 ... 12/2016: filtered are only rows with the selected months -> OK 
- filter on the whole year 2017, but not 2016 (or on the whole year 2016, but not 2017): in both cases rows with dates in both years are filtered -> wrong

Same test with 

Version: 6.0.0.0.alpha0+ (x64)
Build-ID: fc670f637d4271246691904fd649358ce2e7be59
CPU-Threads: 4; Betriebssystem:Windows 6.19; UI-Render: GL; 
TinderBox: Win-x86_64@62-TDF, Branch:master, Time: 2017-09-08_00:38:22
Gebietsschema: de-DE (de_DE); Calc: CL

(or newer) delivers correct results - see Bug 112258! I hope patch for LO 5.4 comes soon.
Comment 36 Nikolay 2017-09-11 03:23:16 UTC
(In reply to Stefan_Lange_KA@T-Online.de from comment #35)
> (In reply to Nikolay from comment #34)
> > (In reply to Mohn from comment #31)
> > > Created attachment 136143 [details]
> > > Not work filter on date
> > > 
> > > Hi
> > > 
> > > In my file not work filter on date.
> > > 
> > > LO v, 5.4.0.3
> > > OS: Windows 10
> > > ID сборки: 7556cbc6811c9d992f4064ab9287069087d7f62c
> > 
> > Version: 5.4.1.2 (x64)
> > ID сборки: ea7cb86e6eeb2bf3a5af73a8f7777ac570321527
> > OS:Windows 6.19 (Windows 10)
> > 
> > "Filter on date" works in your file.
> > Update LibreOffice.
> 
> Hi,
> are you sure, filter on date works correct in LO 5.4.1.2 (x64)?
> 
> I have also tested with this version, but in german, means
> Version: 5.4.1.2 (x64)
> Build-ID: ea7cb86e6eeb2bf3a5af73a8f7777ac570321527
> CPU-Threads: 4; Betriebssystem:Windows 6.19; UI-Render: GL; 
> Gebietsschema: de-DE (de_DE); Calc: group
> 
> Some examples, how filter works:
> - filter on "Месяц" only 05/2016 ... 08/2016 or only 05/2017 .... 08/2017:
> filtered are rows with month 05 ... 08, but from both years -> wrong
> - filter on "Месяц" 01/2017 ... 04/2017: filtered are only rows with the
> selected months -> OK
> - filter on "Месяц" 09/2016 ... 12/2016: filtered are only rows with the
> selected months -> OK 
> - filter on the whole year 2017, but not 2016 (or on the whole year 2016,
> but not 2017): in both cases rows with dates in both years are filtered ->
> wrong
> 
> Same test with 
> 
> Version: 6.0.0.0.alpha0+ (x64)
> Build-ID: fc670f637d4271246691904fd649358ce2e7be59
> CPU-Threads: 4; Betriebssystem:Windows 6.19; UI-Render: GL; 
> TinderBox: Win-x86_64@62-TDF, Branch:master, Time: 2017-09-08_00:38:22
> Gebietsschema: de-DE (de_DE); Calc: CL
> 
> (or newer) delivers correct results - see Bug 112258! I hope patch for LO
> 5.4 comes soon.

Yes, you are right.
I checked for a month, which was only present in one year.
Your tests gave the same result.
Comment 37 Mike Kaganski 2017-09-11 04:21:06 UTC
Reproducible with 5.4.1.2

Not reproducible with Version: 6.0.0.0.alpha0+ (x64)
Build ID: 09122a537318f7ada075820f3b1ef83a64e56751
CPU threads: 4; OS: Windows 6.19; UI render: GL; 
Locale: ru-RU (ru_RU); Calc: CL

So, I suppose that comment 32 is correct, and reopening was wrong. The symptoms are different, anyway. Resetting as RESOLVED FIXED.

Also: please don't assign yourself to the bug, unless you are going to work on it.
Comment 38 Xisco Faulí 2017-10-28 18:22:56 UTC
*** Bug 107472 has been marked as a duplicate of this bug. ***