Bug 42120 - optionally treat filter values as matching patterns
Summary: optionally treat filter values as matching patterns
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
3.3.4 release
Hardware: All Linux (All)
: low enhancement
Assignee: Not Assigned
Keywords: difficultyInteresting, needsDevEval, skillCpp
Depends on: 48807
Blocks: Database-Forms
  Show dependency treegraph
Reported: 2011-10-23 00:47 UTC by Alex Thurgood
Modified: 2017-11-05 23:21 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:

test db (9.46 KB, application/vnd.oasis.opendocument.database)
2011-10-23 00:47 UTC, Alex Thurgood
testdb2 (10.46 KB, application/vnd.oasis.opendocument.database)
2011-10-24 01:17 UTC, Alex Thurgood

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2011-10-23 00:47:39 UTC
Created attachment 52645 [details]
test db

On Ubuntu Oneiric 11.10 with distrib provided LibO 3.4.3

Steps to reproduce :
1) Open the sample db provided.
2) Open the form.
3) Click on the Filter button, the Filter Navigator and Toolbar appear.
4) Try entering a filter string in a field of the form, e.g. T*
5) Apply filter

Expected result :
Form should display all results filtered according to the criteria given.

Actual result :
No results are displayed at all.

Problem :
Filter criteria are not applied or incorrectly applied.

Comment 1 Julien Nabet 2011-10-23 14:15:43 UTC
Hi Alex,

There's an authentification required so I couldn't try to reproduce it.

Comment 2 Alex Thurgood 2011-10-24 01:17:06 UTC
Created attachment 52678 [details]

The first attachment I submitted was connected to a mysql db with authentication, which of course was inaccessible for everyone else, my bad, sorry.

This file is a native hsqldb ODB. Use this one for testing instead. The results are the same.

Comment 3 Zoltán Reizinger 2011-11-10 01:13:27 UTC
Alex in filters, the form filters is same, you most use some SQL compliant filtering condition.
The 'T*' is not that, usage of LIKE 'T*' will give a results what you look for.
Comment 4 sasha.libreoffice 2012-04-16 08:20:45 UTC
What is interesting, "Auto filter" adds this LIKE '' to search string, and "Form based filters" not adds, so nothing can find.
Expected: function on button "Form based filters" should add LIKE '' around search string automatically if not present, or understand search string without it.
Reproduced in 3.3.4 and 3.5.2 on Fedora 64 bit
Changing version to 3.3.4 as most early reproducible
Comment 5 Lionel Elie Mamane 2012-04-17 02:04:55 UTC
The "form by filter" works exactly like the "condition" lines of the query designer. That is, the default operator is "="; if you type a string like "T*" in field "fname" it means you are filtering for records that have exactly "T*" in fname, and not "anything that begins with T".

If you want to use a pattern, you need to write "LIKE 'T*'" (without the surrounding double quotes, but with the internal single quotes).

You may argue that both should automagically recognise patterns (I'm not sure I would agree), but that is at most an enhancement request, not a bug. In my opinion, this should be consistent between the query designer (condition column) and "filter by form". So if we change one, we should change both.

(In reply to comment #4)
> What is interesting, "Auto filter" adds this LIKE '' to search string,

I would consider this a bug. It should definitely use equality (or IS NULL for NULL values). Auto Filter asks for records with the same value in the specified field. This way of doing it mixes up the notion of pattern and of value: the field contains a *value*, not a pattern!
Comment 6 Lionel Elie Mamane 2012-04-18 02:30:55 UTC
As per request in comment 4 of bug 48807, changing to enhancement request. Here's my summary of what the enhancement could look like:

 - Introduce a way so that values typed in a field in "filter form by form" or in a "condition" line of query's design mode are treated as patterns to match instead of as values to match. That is, if one types "A", the generated filter (query WHERE clause) is "colName LIKE 'A'" and not "colName = 'A'".

 - That way should be *optional*. Should it be a global preference (in tools/options/base)? A per-form/query checkbox somewhere (e.g. next to "execute query directly" and in the navigator, respectively) ?

 - IMO the only sane way is that the decision to treat as value or as pattern is made at the time the value is typed; if the checkbox is changed, all typed values stay as they are. That is, if the option is enabled, and one types "A", then it is immediately (and visibly) changed to "LIKE 'A'".

Open questions:

 - Should this happen for all column (field) types or e.g. only for (var)char columns? Does it make much sense to filter a numeric column by pattern?

My opinion about this enhancement request: if people want it, I'm not really opposing it, but I'm also not quite championing it.

I *strongly* require that "filter by form" and "request design mode" behave coherently, that is the change should not be done for only one of them.

Also, could be an EasyHack.
Comment 7 Alex Thurgood 2012-04-18 03:57:18 UTC
Let's just set it to new as a RFE.

I agree with you though, Lionel, about making the behaviour consistent.

Comment 8 sasha.libreoffice 2012-04-18 04:51:17 UTC
> - Should this happen for all column (field) types
IMHO no. If I enter 12, it means =12 and not LIKE '12*'. So, for numbers should be "=" added. And similar for date/time. But from other hand, if date is seen so: 
12 jan 2012 , then when user entered "jan", expected that will be found all dates in January. But it more simply to add warning for user for not do such experiments with dates/times.

> Also, could be an EasyHack.
Then developers should enumerate all source files, where changes should/may be done for implementing this feature

> if people want it, I'm not really opposing it ...
usually CC field used as indicator how much people (roughly) interesting in feature. Currently CC=3 (may be original bugreporter not takes in account).
Comment 9 Joel Madero 2014-02-27 23:19:10 UTC
In order to limit the confusion between ProposedEasyHack and EasyHack and to make queries much easier we are changing ProposedEasyHack to NeedsDevEval.

Thank you and apologies for the noise
Comment 10 Alex Thurgood 2015-01-03 17:39:57 UTC Comment hidden (no-value)
Comment 11 Robinson Tryon (qubit) 2015-12-14 06:12:30 UTC Comment hidden (obsolete)