Bug 153057 - Calc and Base can not filter time values
Summary: Calc and Base can not filter time values
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) alpha1+
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Database-Forms Data-Filter
  Show dependency treegraph
Reported: 2023-01-17 06:55 UTC by Andreas Säger
Modified: 2024-04-20 11:16 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

database document with 2 forms (39.99 KB, application/vnd.oasis.opendocument.database)
2023-01-17 06:57 UTC, Andreas Säger
Spreadsheet with list range (66.06 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-01-17 07:00 UTC, Andreas Säger

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas Säger 2023-01-17 06:55:39 UTC
Calc's standard filter dialog displays the existing time values as formatted in the respective column. If you choose any of the values with operator =, the filter result is OK. In the attached song list, there are 4 songs with a lenght of 00:01:58. When you change the operator or when you manually enter some time value, the filter always fails with no resulting records. "Advanced filtering" works well.

The problem affects both forms of the attached database. The form reports an error and the form document needs to be reloaded in order to be usable again. I could reproduce the problem with both types of embedded databases.

Steps to Reproduce:
1. Open spreadsheet and test the standard filter with some value in the "Duration" column. 
2. Open the database and test filtering by time values in the "Duration" column. 

Actual Results:
1. fails in most cases with no records
2. always fails with no records, error message and unusable form.

Expected Results:
Properly filtered record sets

Reproducible: Always

User Profile Reset: No

Additional Info:
Comment 1 Andreas Säger 2023-01-17 06:57:56 UTC
Created attachment 184702 [details]
database document with 2 forms

Test the form/subform filtering and the built-in filters. Everything works properly unless you filter by time values in "Duration".
Comment 2 Andreas Säger 2023-01-17 07:00:01 UTC
Created attachment 184703 [details]
Spreadsheet with list range

menu:Data>filter>Standard filter...
Works with a selected time
Fails with entered time or with operator other than =
Comment 3 Stéphane Guillou (stragu) 2023-01-17 09:12:44 UTC
Can confirm that in the spreadsheet, the standard filter can use an equality condition with "00:01:58" on column E (which contains numeric data formatted as time).

Whereas in the database, the TITLES table has a Duration field of type TIME, formatted by default "HH:MM:SS", but the time range filter in the Filtered Titles form does not work as expected.

After applying a time range filter, it can't be reset and the form shows the error:

The data content could not be loaded. at /home/[...]/connectivity/source/commontools/dbtools.cxx:746
Incorrect type for setString at /home/[...]/connectivity/source/commontools/dbexception.cxx:462

Tested with:

Version: (X86_64) / LibreOffice Community
Build ID: d04c197b436094844abfa5fb7c12e095165d2bca
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded
Comment 4 Alex Thurgood 2023-01-17 09:42:20 UTC
Confirming on macOS with both the spreadsheet and the database form

Version: / LibreOffice Community
Build ID: 85569322deea74ec9134968a29af2df5663baa21
CPU threads: 8; OS: Mac OS X 13.0.1; UI render: Skia/Raster; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded
Comment 5 Alex Thurgood 2023-01-17 09:48:33 UTC
This seems to work correctly in

Version: / LibreOffice Community
Build ID: e1f30c802c3269a1d052614453f260e49458c82c
CPU threads: 8; OS: Mac OS X 10.16; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

Despite not having saved any changes to the database or the form, once that error message is displayed, there seems to be no way of recovering the original behaviour. I systematically get the setString error message (on macOS) on opening even in a different version of LO.
Comment 6 zcrhonek 2023-02-16 05:44:53 UTC
bisected to
 5bee333e16cc9879991346f7d6ee49e0b9b11315 is the first bad commit
commit 5bee333e16cc9879991346f7d6ee49e0b9b11315
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Tue Jun 15 19:25:40 2021 -0700

    source 1f755525189884e4b2824889a6b9dea8933402db


CC to Balazs Varga
Comment 7 Andreas Säger 2024-04-20 11:16:44 UTC
The filter field of a valid filter looks like:
IsNumeric     boolean           True                   
NumericValue  double            0.0013310185185185187  
StringValue   string            00:01:55               
FilterType    long              0                      
ColorValue    long              0                      

The failing filters have NumericValue 0.0.